三个男吃我奶头一边一个视频,国产精品视频第一区二区,欧美一级毛片大片免费播放

http://wenku.baidu.com/view/42f3ed22bcd126fff7050b79.html

---basic table
select * from tbaadm.gam
--group by

select ACID,DEL_FLG,ACCT_NAME,count(DR_BAL_LIM) FROM tbaadm.gam group by ACID; --wrong suach as count min max avg do not toghter with three or three blow column

select ACID, count(DR_BAL_LIM) FROM tbaadm.gam group by ACID;--right

---group by
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES), sum(TOT_MOD_TIMES) FROM tbaadm.gam group by ACID;
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES), sum(TOT_MOD_TIMES) FROM tbaadm.gam group by FORACID;--WRONG? group by must following the select field

-- having condition group by
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES) as tot_mod_count, sum(TOT_MOD_TIMES) FROM tbaadm.gam having count(TOT_MOD_TIMES)>1 group by ACID;

-- ordery by
select ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID
select ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY FORACID

--MIN MAX
select ACID,DEl_FLG,MIN(TOT_MOD_TIMES) MIN,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID --wrong must have two column
select ACID,MIN(TOT_MOD_TIMES) MIN FROM tbaadm.gam group BY ACID
select ACID,MAX(TOT_MOD_TIMES) MAX1 FROM tbaadm.gam group BY ACID

--DISTINCT
select distinct ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID;
select ACID,distinct DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID --wrong the distinct must be put in the first postion.

--group by must be put in front of the order by and limit

--avg(distinct|all)

select ACID,avg(TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
select ACID,avg(distinct TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
select ACID,avg(all TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;

--- userenv

select userenv('language') from dual;
select userenv('sessionid') from dual;
select user from dual;

--type use guide
create?? or?? replace?? type?? bartype?? as?? object(
??? name?? varchar2(20),
??? addr?? varchar2(20)
);
/
create?? table?? Bars?? of?? BarType;

-- create a procedure to farmat the string
CREATE OR REPLACE PACKAGE basp0099 AS

?TYPE ArrayType IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

?PROCEDURE formInputArr (inputStr ??IN??VARCHAR2,
????????? inputArr??OUT??ArrayType);

END basp0099;

CREATE OR REPLACE PACKAGE BODY basp0099 AS

-- ***********************************************************

?PROCEDURE formInputArr (inputStr ?IN? VARCHAR2,
??????????????????? inputArr?OUT? ArrayType
????????? ) AS

?inpArr?????ArrayType;
?tempStr ????VARCHAR2(500 char);
?i????????? NUMBER := 0;
?delimPos????NUMBER := 0;

?BEGIN? --{

--??DBMS_OUTPUT.PUT_LINE ('basp0099 : inside formInputArr');
--??DBMS_OUTPUT.PUT_LINE ('basp0099 : inputStr [' || inputStr || ']');

??tempStr := inputStr;

??LOOP -- {

???delimPos? := INSTR (tempStr, '!');

???IF ((delimPos = 0) OR (delimPos IS NULL)) THEN
????inpArr(i) := tempStr ;
????EXIT;
???ELSE
????inpArr(i) := SUBSTR (tempStr, 1, delimPos - 1);
????tempStr?? := SUBSTR (tempStr, delimPos + 1);
???END IF;

???i := i + 1;

??END LOOP; -- }

--??for j in 0..i LOOP
--???DBMS_OUTPUT.PUT_LINE ('basp0099 : inpArr(' || j || ') [' || inpArr(j) || ']');
--??END LOOP;

??inputArr := inpArr;

?END formInputArr;

-- ***********************************************************

END basp0099 ;

--------------------------------------------------------------------------------
-- Function : getCountOfChar()
-- Desc???? : Returns the no of times a character exists in a string(string, char)
--------------------------------------------------------------------------------
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR )
?RETURN NUMBER;
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR)
?RETURN NUMBER AS
lv_input_length??NUMBER;
lv_index??NUMBER := 1;
lv_count_of_char?NUMBER := 0;

BEGIN
?BEGIN
??lv_input_length := LENGTH(lv_string);
??WHILE lv_index <= lv_input_length
??LOOP
??? ??IF(SUBSTR(lv_string, lv_index, 1) = 'U') THEN
??? ??--{
??? ???lv_count_of_char := lv_count_of_char +1 ;
??? ??--}
??? ??END IF;
??? ??lv_index := lv_index + 1;
??END LOOP;
?END;
?RETURN lv_count_of_char;
END getCountOfChar;
-----------------------------------------------------------------------------------------------------------
--- Function : eabBal()
--- Desc???? : Takes Acid and As On Date as input and returns
---????????? : Outstanding Balance of the Acid based on the input Dates
-----------------------------------------------------------------------------------------------------------

FUNCTION eabBal(lv_acid? varchar2,
??????????????? lv_as_on_date?? DATE,bankId varchar2) RETURN NUMBER is
outstanding_amount? number;

BEGIN
--{
??? BEGIN
??????? SELECT? nvl(tran_date_bal,0)
??????? INTO??? outstanding_amount
??????? FROM??? tbaadm.EAB
??????? WHERE?? EAB.acid = lv_acid
??????? AND? eod_date <= lv_as_on_date
??????? AND? end_eod_date >= lv_as_on_date
??????? AND? bank_id = bankId ;
??????? EXCEPTION
??????? WHEN NO_DATA_FOUND THEN
??????? outstanding_amount := 0;
??? END;
??? RETURN outstanding_amount;
--}
END eabBal;

--create? a test table by gary qu
drop table test_gary1;
create table test_gary1
?????? (
????????? id number,
????????? name varchar2(20),
????????? password varchar2(20),
????????? account number(10,2),
????????? emailaddress varchar2(20),
????????? introduce varchar2(50)
??????? );
???????
???????

-- insert into data for table test_gary1

insert into test_gary1 values(1,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(2,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(3,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(4,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(5,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(6,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(7,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(8,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(9,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(10,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(11,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');

--inquiry record from table

select * from test_gary1;

--create oracle procedure

drop procedure gary_test1;
create or replace procedure gary_test1 (student_id?? in number,
?????????????????????????????????????? student_name out varchar2
?????????????????????????????????????? )
?????????????????????????????????????? IS
?????????????????????????????????????? --student_name:='kevin_xiong';
?????????????????????????????????????? x number:=1;
?????????????????????????????????????? Cursor cursor is select * from test_gary1;???
?????????????????????????????????????? --name varchar2(20);
?????????????????????????????????????? i number:=0;?????????????????????????????
??????????????????????????????????????
?????????????????????????????????????? begin
???????????????????????????????????????????? if x>0 then
??????????????????????????????????????????????? begin
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('holl world!');
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('holl baby!');
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('hello infosys!');
??????????????????????????????????????????????? end;
????????????????????????????????????????????? end if;
????????????????????????????????????????????? for test in cursor LOOP
????????????????????????????????????????????????? begin
???????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.id);
????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.name);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.password);
??????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.emailaddress);
???????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.introduce);?????????????????????????????????????????????????????????
????????????????????????????????????????????????? end;
?????????????????????????????????????????????? end LOOP;
?????????????????????????????????????????????? while i<20 LOOP
????????????????????????????????????????????????????? begin
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(i);
?????????????????????????????????????????????????????????? i:=i+1;????????????????????????????????????????????????
????????????????????????????????????????????????????? end;
?????????????????????????????????????????????? end LOOP;
???????????????????????????????????????????? DBMS_OUTPUT.put_line(x);
?????????????????????????????????????? end gary_test1;
??????????????????????????????????????
??????????????????????????????????????
??????????????????????????????????????
----------reserch the record through the student id????????????????????????????????????
create or replace procedure gary_test1 (student_id?? in number,
?????????????????????????????????????? student_name out varchar2
?????????????????????????????????????? )
?????????????????????????????????????? IS
?????????????????????????????????????? --student_name:='kevin_xiong';
?????????????????????????????????????? x number:=1;
?????????????????????????????????????? Cursor cursor is select name from test_gary1 where test_gary1.id=student_id;???
?????????????????????????????????????? --name varchar2(20);
?????????????????????????????????????? i number:=0;
?????????????????????????????????????? id number;
?????????????????????????????????????? name varchar2(20);?????????????????????????
??????????????????????????????????????
?????????????????????????????????????? begin
??????????????????????????????????????
??????????????????????????????????????? open cursor;
???????????????????????????????????????
????????????????????????????????????? loop
????????????????????????????????????????
???????????????????????????????????????????? begin
?????????????????????????????????????????????????? fetch cursor into gary_name
????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line(test.id);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line('teeesttt------stesrt');
????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line(test.password);
????????????????????????????????????????????????????? --DBMS_OUTPUT.put_line(test.emailaddress);
?????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line(test.introduce);
?????????????????????????????????????????????????????? exit when cursor%notfound;
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(gary_name.name);
??????????????????????????????????????????????????????
????????????????????????????????????????????? end;
??????????????????????????????????????? end loop;???????????????????????????????????????
??????????????????????????????????????
?????????????????????????????????????? if cursor%isopen then
????????????????????????????????????????? close cursor;
?????????????????????????????????????? end if;
????????????????????????????????????????????? for test in cursor LOOP
????????????????????????????????????????????????? begin
???????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.id);
????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.name);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.password);
??????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.emailaddress);
???????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.introduce);?????????????????????????????????????????????????????????
????????????????????????????????????????????????? end;
?????????????????????????????????????????????? end LOOP;??????????????????????????????????????????????
?????????????????????????????????????? end gary_test1;???
??????????????????????????????????????
??????????????????????????????????????
??????????????????????????????????????
??????????????????????????????????????
?create or replace procedure gary_test1 (student_id?? in number,
?????????????????????????????????????? student_name out varchar2
?????????????????????????????????????? )
?????????????????????????????????????? IS
?????????????????????????????????????? --student_name:='kevin_xiong';
?????????????????????????????????????? x number:=1;
?????????????????????????????????????? Cursor cursor is select name from test_gary1 where test_gary1.id=student_id;
?????????????????????????????????????? --name varchar2(20);
?????????????????????????????????????? gary_name varchar2(20);
?????????????????????????????????????? cur_gary cursor%rowtype;

?????????????????????????????????????? begin

?????????????????????????????????????? open cursor;
????????????????????????????????????? loop

????????????????????????????????????????????? end;
??????????????????????????????????????? end loop;

?????????????????????????????????????? if cursor%isopen then
????????????????????????????????????????? close cursor;
?????????????????????????????????????? end if;
?????????????????????????????????????? end gary_test1;
???????????????????????????
-- write a research procedure to search all data from test_gary1 with loop 1

?create or replace procedure gary_test2 (student_id?? in number,
?????????????????????????????????????? student_name out varchar2
?????????????????????????????????????? )
?????????????????????????????????????? IS
?????????????????????????????????????? --student_name:='kevin_xiong';
?????????????????????????????????????? x number:=1;
?????????????????????????????????????? Cursor cursor is select id,name,password,account,emailaddress,introduce from test_gary1 where test_gary1.id=student_id;
?????????????????????????????????????? --name varchar2(20);
?????????????????????????????????????? gary_name varchar2(20);
?????????????????????????????????????? cur_gary cursor%rowtype;

?????????????????????????????????????? begin

?????????????????????????????????????? open cursor;
????????????????????????????????????? loop

???????????????????????????????????????????? begin
?????????????????????????????????????????????????? fetch cursor into cur_gary;
????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line(test.id);
????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line('teeesttt------stesrt');
????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line(test.password);
????????????????????????????????????????????????????? --DBMS_OUTPUT.put_line(test.emailaddress);
?????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line(test.introduce);
?????????????????????????????????????????????????????? exit when cursor%notfound;
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(cur_gary.id);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(cur_gary.name);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(cur_gary.password);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(cur_gary.account);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(cur_gary.emailaddress);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(cur_gary.introduce);
?????????????????????????????????????????????????????? student_name:=cur_gary.name;
?????????????????????????????????????????????????????? --DBMS_OUTPUT.put_line(student_name);

????????????????????????????????????????????? end;
??????????????????????????????????????? end loop;

-- use for loop to resaerch data

create or replace procedure gary_test3 (student_id?? in number,
?????????????????????????????????????? student_name out varchar2
?????????????????????????????????????? )
?????????????????????????????????????? IS
?????????????????????????????????????? --student_name:='kevin_xiong';
?????????????????????????????????????? x number:=1;
?????????????????????????????????????? Cursor cursor is select * from test_gary1 where id=student_id;???
?????????????????????????????????????? --name varchar2(20);
?????????????????????????????????????? i number:=0;?????????????????????????????
??????????????????????????????????????
?????????????????????????????????????? begin
???????????????????????????????????????????? if x>0 then
??????????????????????????????????????????????? begin
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('holl world!');
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('holl baby!');
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('hello infosys!');
??????????????????????????????????????????????? end;
????????????????????????????????????????????? end if;
????????????????????????????????????????????? for test in cursor LOOP
????????????????????????????????????????????????? begin
???????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.id);
????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.name);
?????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.password);
??????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.emailaddress);
???????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(test.introduce);?????????????????????????????????????????????????????????
????????????????????????????????????????????????? end;
?????????????????????????????????????????????? end LOOP;
?????????????????????????????????????????????
?????????????????????????????????????? end gary_test3;?????????????????????????????????????????????????????????????
???????????????
-- use while loop to resaerch data

create or replace procedure gary_test4 (student1_id? in number,
?????????????????????????????????????? student1_name out varchar2
?????????????????????????????????????? )
?????????????????????????????????????? IS
?????????????????????????????????????? --student_name:='kevin_xiong';
?????????????????????????????????????? x number:=1;
?????????????????????????????????????? Cursor cursor1 is select * from test_gary1 where id=student1_id;???
?????????????????????????????????????? --name varchar2(20);
?????????????????????????????????????? i number:=0;??
?????????????????????????????????????? student_id? test_gary1.id%type;
?????????????????????????????????????? student_name? test_gary1.name%type;
?????????????????????????????????????? student_password? test_gary1.password%type;
?????????????????????????????????????? student_account? test_gary1.account%type;
?????????????????????????????????????? student_emailaddress? test_gary1.emailaddress%type;
?????????????????????????????????????? student_introduce? test_gary1.introduce%type;?????????????????????????
??????????????????????????????????????
?????????????????????????????????????? begin
???????????????????????????????????????????? open cursor1;
???????????????????????????????????????????? fetch cursor1 into student_id,
??????????????????????????????????????????????????????????????????????? student_name,
??????????????????????????????????????????????????????????????????????? student_password,
??????????????????????????????????????????????????????????????????????? student_account,
??????????????????????????????????????????????????????????????????????? student_emailaddress,
??????????????????????????????????????????????????????????????????????? student_introduce;
???????????????????????????????????????????? if x>0 then
??????????????????????????????????????????????? begin
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('holl world!');
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('holl baby!');
??????????????????????????????????????????????????? DBMS_OUTPUT.put_line('hello infosys!');
??????????????????????????????????????????????? end;
????????????????????????????????????????????? end if;
????????????????????????????????????????????? /*if cursor1%found then?
???????????????????????????????????????????????? begin?????????????????????????????????????????????
?????????????????????????????????????????????????? DBMS_OUTPUT.put_line('======='||'-cursor found-');
????????????????????????????????????????????????? end;
????????????????????????????????????????????? end if;
?????????????????????????????????????????????
?????????????????????????????????????????????? if cursor1%notfound then?
???????????????????????????????????????????????? begin?????????????????????????????????????????????
?????????????????????????????????????????????????? DBMS_OUTPUT.put_line('---==----'||'-cursor does not found --');
????????????????????????????????????????????????? end;
????????????????????????????????????????????? end if;*/
????????????????????????????????????????????? while cursor1%found LOOP
????????????????????????????????????????????????????? begin
?????????????????????????????????????????????????????????? --DBMS_OUTPUT.enable(500000);
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line('----------');
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(student_id);???????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(student_name);
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(student_password);
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(student_account);
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(student_emailaddress);
?????????????????????????????????????????????????????????? DBMS_OUTPUT.put_line(student_introduce);
???????????????????????????????????????????????????????? -- DBMS_OUTPUT.put_line(i);
????????????????????????????????????????????????????????? -- i:=i+1;?????
?????????????????????????????????????????????????????????? fetch cursor1 into student_id,
??????????????????????????????????????????????????????????????????????? student_name,
??????????????????????????????????????????????????????????????????????? student_password,
??????????????????????????????????????????????????????????????????????? student_account,
??????????????????????????????????????????????????????????????????????? student_emailaddress,
??????????????????????????????????????????????????????????????????????? student_introduce;??????????????????????????????????????????
????????????????????????????????????????????????????? end;
?????????????????????????????????????????????? end LOOP;????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????? end gary_test4;????????????????????????
--execute the procedure
--execute proc_name;

set serveroutput on;

--execute gary_test1
declare
student_name varchar2(20);
begin
?????? gary_test1(1,student_name);
end;

--execute gary_test2
declare
student_name varchar2(20);
begin
?????? gary_test2(2,student_name);
?????? DBMS_OUTPUT.put_line('----studnet name-----');
?????? DBMS_OUTPUT.put_line(student_name);
end;
--execute gary_test3
declare
student_name varchar2(20);
begin
?????? gary_test3(3,student_name);
end;
--execute gary_test4
set serveroutput on;
declare
student_name varchar2(20);
begin
?????? gary_test4(1,student_name);
end;

-- trigger

--create table
drop table test;
drop table test_log;
CREATE TABLE test ( t_id NUMBER(4), t_name VARCHAR2(20), t_age NUMBER(2), t_sex CHAR );
CREATE TABLE test_log ( l_user VARCHAR2(15), l_type VARCHAR2(15), l_date VARCHAR2(30) );
---create trigger
drop trigger test_trigger;
create or replace trigger test_trigger
????????????????? after delete or insert or update on test
????????????????? declare v_type test_log.l_type%type:='none';
????????????????? begin
?????????????????? --insert
???????????????????? if INSERTING then
???????????????????? v_type:='insert';
???????????????????? --insert into test_log values('007','||||','||sysdate||');
???????????????????? Dbms_Output.put_line('????ò?3é1|2?è?￡?2￠????μ?è???');
???????????????????????????????????????
?????????????????? ---upate
???????????????????? elsif UPDATING then
???????????????????? v_type:='update';
???????????????????? --insert into test_log values('007','||v_type||','||sysdate||');
???????????????????? Dbms_Output.put_line('????ò?3é1|DT??￡?2￠????μ?è???');
????????????????????
???????????????????? --delete
???????????????????? elsif DELETING then
???????????????????? v_type:='delete';
???????????????????? --insert into test_log values('007',v_type,'||sysdate||');
???????????????????? Dbms_Output.put_line('????ò?3é1|é?3y￡?2￠????μ?è???');
????????????????????
???????????????????? end if;
???????????????????? insert into test_log values(user,v_type,TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
????????????????? end;
?????????????????
--execute the trigger
insert into test values(0001,'garyqu','20','f');
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
????

select * from test;
select * from test_log;
------trigger example 2 summary ---
drop table emp;
create table emp(empno varchar2(20),deptno varchar2(20),sal number(20,2));

drop table dept_sal;
create table dept_sal AS
???????????? select deptno,count(empno) AS total_emp,sum(sal) AS total_sal from emp group by deptno;
DESC dept_sal;

--create trigger--
drop trigger emp_info;
create or replace trigger emp_info
????????????????? after insert or update or delete on emp
????????????????? declare Cursor cur_emp is
????????????????? select deptno,count(empno) AS total_emp,sum(sal) AS total_sal from emp group by deptno;?????????????????
????????????????? begin
????????????????????? delete dept_sal;
????????????????????? for v_emp in cur_emp LOOP
????????????????????? DBMS_OUTPUT.put_line(v_emp.deptno||' '||v_emp.total_emp||' '||v_emp.total_sal);
????????????????????? insert into dept_sal values(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);
????????????????????? end LOOP;?????????????????????
????????????????? end;

--execute dml statement
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
?????????????????????????
---trigger example 3--
drop table employee;
CREATE TABLE employee ( id VARCHAR2(4) NOT NULL, name VARCHAR2(15) NOT NULL, age NUMBER(2) NOT NULL, sex CHAR NOT NULL );
DESC employee;
--2?è?êy?Y
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
INSERT INTO employee VALUES('e103','jian',21,'F');?
--′′?¨????±í
CREATE TABLE old_employee AS SELECT * FROM employee;
DESC old_employee;
--trigger--
drop trigger save_delete_date;
create or replace trigger save_delete_date
????????????????? after delete on employee
????????????????? for each row
????????????????? begin
????????????????? insert into old_employee values(:old.id,:old.name,:old.age,:old.sex);
????????????????? end;
?????????????????
?????????????????
---delete opreation--
DELETE employee;
delete old_employee;
SELECT * FROM old_employee
select * from employee;

----trigger exampel4---
--êμày6------------------------
--′′?¨′￥·￠?÷￡???2ù×÷CREATE?￠DROP′?′￠?úlog_info±í
--′′?¨±í
CREATE TABLE log_info ( manager_user VARCHAR2(15), manager_date VARCHAR2(15), manager_type VARCHAR2(15), obj_name VARCHAR2(15), obj_type VARCHAR2(15) );
--′′?¨′￥·￠?÷
drop trigger trig_log_info;
CREATE OR REPLACE TRIGGER trig_log_info
AFTER CREATE OR DROP ON SCHEMA
BEGIN INSERT INTO log_info
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE);
END;
/

CREATE OR REPLACE TRIGGER trig_log_info
AFTER CREATE OR DROP ON SCHEMA
BEGIN INSERT INTO log_info
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE);
END;
/

--2aê?ó???
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);

SELECT * FROM log_info;

---study package--
create or replace package test_package
as
type outlist is ref cursor;
procedure getSeniorHighSchool(
????????? maxrow in number,
????????? minrow in number,
????????? return_list out outlist);
end test_package;

----create package body---
create or replace body test_package
is
? procedure getSeniorHighSchool(
??????????? maxrow in number,
??????????? minrow in number,
??????????? return_list out outlist)
??????????? as
??????????? begin
??????????? for
????????
?????????? select * from (select a.*,rownum rnum from
?????????? (
????????????--òμ??ó???
??????????) a where rownum<=maxrow) where rnum >=minrow;

????? end;

---test
create table employee_gary(employee_id number, last_name varchar2(2),salary number(10,2),department_id number);

insert into employee_gary values(1,'qu',10000,001);
insert into employee_gary values(1,'qu',10000,002);
insert into employee_gary values(1,'qu',10000,003);
insert into employee_gary values(1,'q1',50,001);
insert into employee_gary values(2,'q2',100,002);
insert into employee_gary values(3,'q3',100,003);

delete from employee_gary;

select * from employee_gary;

select e.department_id, employee_id, last_name, min(salary)
? from employee_gary e
?where e.department_id = (select distinct department_id from employee_gary? group by department_id);
?
?
--example--
select e.employee_id, e.last_name, e.salary
? from employee_gary e,
?????? (select department_id, min(salary) m
???????? from employee_gary
???????? group by department_id) d
?where e.department_id = d.department_id
?? and e.salary = d.m;

---create package and procdure--
-----------------------
--Creating the object type
------------------------
DROP TYPE CLOANS_RPT01345_TYPE_TABLE;
DROP TYPE CLOANS_RPT01345_TYPE;
CREATE OR REPLACE TYPE CLOANS_RPT01345_TYPE AS OBJECT
(??
?????? FORACID????????????? VARCHAR2(16),
????? ADDRESS_LINE1??????? VARCHAR2(200),
?????? ADDRESS_LINE2??????? VARCHAR2(200),
?????? ADDRESS_LINE3??????? VARCHAR2(200),
?????? CITY???????????????? VARCHAR2(200),
?????? STATE??????????????? VARCHAR2(200),
?????? COUNTRY????????????? VARCHAR2(200),
?????? ZIP????????????????? VARCHAR2(200),
?????? PEG_REVIEW_DATE????? DATE
?);

-------------------------------------------------
-- Creating the TABLE for the TYPE object
-------------------------------------------------
CREATE OR REPLACE TYPE CLOANS_RPT01345_TYPE_TABLE AS TABLE OF CLOANS_RPT01345_TYPE;

----------------------------------
--Creating the Function
-----------------------------------
CREATE OR REPLACE FUNCTION CLOANS_RPT01345_FUNC(lv_asondate IN VARCHAR2)
? RETURN CLOANS_RPT01345_TYPE_TABLE
? PIPELINED AS
?
??????
?????? ADDRESS_LINE1??????? VARCHAR2(200);
?????? ADDRESS_LINE2??????? VARCHAR2(200);
?????? ADDRESS_LINE3??????? VARCHAR2(200);
?????? CITY???????????????? VARCHAR2(200);
?????? STATE??????????????? VARCHAR2(200);
?????? COUNTRY????????????? VARCHAR2(200);
?????? ZIP????????????????? VARCHAR2(200);
??????
?????? ENTITY_ID??????????? VARCHAR2(12);
?????? INT_TBL_CODE_SRL_NUM VARCHAR2(3);
?????? INT_TBL_CODE???????? VARCHAR2(5);
?????? PEG_REVIEW_DATE????? DATE;
?????? FORACID????????????? VARCHAR2(16);
?????? CIF_ID?????????????? VARCHAR2(50);??????

????? cursor CLOANS_PRT01345_cur01 is??????
??????????????????????????????? SELECT a.entity_id,
?????????????????????????????????????? a.INT_TBL_CODE_SRL_NUM,
?????????????????????????????????????? a.INT_TBL_CODE,
?????????????????????????????????????? a.peg_review_date,
?????????????????????????????????????? g.foracid,
?????????????????????????????????????? g.cif_id,
?????????????????????????????????????? ad.address_line1,
?????????????????????????????????????? ad.address_line2,
?????????????????????????????????????? ad.address_line3,
?????????????????????????????????????? ad.CITY,
?????????????????????????????????????? ad.state,
?????????????????????????????????????? ad.country,
?????????????????????????????????????? ad.zip
????????????????????????????????? FROM tbaadm.ITC?????? a,
?????????????????????????????????????? tbaadm.gam?????? g,
?????????????????????????????????????? crmuser.address? ad,
?????????????????????????????????????? crmuser.accounts ac
???????????????????????????????? WHERE a.entity_id IN
?????????????????????????????????????? (SELECT acid FROM tbaadm.lam WHERE entity_cre_flg = 'Y')
?????????????????????????????????? AND g.acid = a.entity_id
?????????????????????????????????? and ad.accountid = ac.accountid
?????????????????????????????????? and ac.orgkey = g.cif_id
?????????????????????????????????? and ad.ADDRESSCATEGORY = 'Mailing'
?????????????????????????????????? AND a.entity_type = 'ACCNT'
?????????????????????????????????? AND a.start_date <= to_date(lv_asondate, 'mm-dd-yyyy')
?????????????????????????????????? AND a.end_date >= to_date(lv_asondate, 'mm-dd-yyyy')
?????????????????????????????????? AND a.del_flg != 'Y'
?????????????????????????????????? AND a.entity_cre_flg = 'Y'
?????????????????????????????????? AND a.bank_id = '01'
?????????????????????????????????? AND add_months(a.peg_review_date, -1) =
?????????????????????????????????????? to_date(lv_asondate, 'mm-dd-yyyy')
?????????????????????????????????? AND a.INT_TBL_CODE_SRL_NUM =
?????????????????????????????????????? (SELECT MAX(INT_TBL_CODE_SRL_NUM)
????????????????????????????????????????? FROM tbaadm.ITC b
???????????????????????????????????????? WHERE b.entity_id = a.entity_id
?????????????????????????????????????????? AND b.entity_type = 'ACCNT'
?????????????????????????????????????????? AND b.del_flg != 'Y'
?????????????????????????????????????????? AND b.entity_cre_flg = 'Y'
?????????????????????????????????????????? AND b.bank_id = '01');??????????????????????????????????????????
?????
?????????????????????????????????
BEGIN
? IF (NOT CLOANS_PRT01345_cur01%ISOPEN) THEN
???? OPEN CLOANS_PRT01345_cur01;
?END IF;

? IF (CLOANS_PRT01345_cur01%ISOPEN) THEN
???? LOOP
??????????? FETCH CLOANS_PRT01345_cur01
??????????? INTO? ENTITY_ID,
???????????????????? INT_TBL_CODE_SRL_NUM,
???????????????????? INT_TBL_CODE,
???????????????????? PEG_REVIEW_DATE,
???????????????????? FORACID,
???????????????????? CIF_ID,
???????????????????? ADDRESS_LINE1,
???????????????????? ADDRESS_LINE2,
???????????????????? ADDRESS_LINE3,
???????????????????? CITY,
???????????????????? STATE,
???????????????????? COUNTRY,
???????????????????? ZIP;?????????????????????????????????????

?????????? IF (CLOANS_PRT01345_cur01%NOTFOUND) THEN
??????? ????CLOSE CLOANS_PRT01345_cur01;
??????? ????RETURN;
??????? ???END IF;

??????????? PIPE ROW(CLOANS_RPT01345_TYPE(
???????????????????? FORACID,
?????????? ADDRESS_LINE1,
???????????????????? ADDRESS_LINE2,
???????????????????? ADDRESS_LINE3,
???????????????????? CITY,
???????????????????? STATE,
???????????????????? COUNTRY,
???????????????????? ZIP,
???????????????????? PEG_REVIEW_DATE????????????????????
???????????????????????? )
??????? );
??????? END LOOP;
? END IF;
END CLOANS_RPT01345_FUNC;

-------------------------------------------
-- test the Function
------------------------------------------
--SELECT * FROM TABLE(CLOANS_RPT01345_FUNC('11-30-2099'))where rownum<50

?????

常見問題1000例 Oracle

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频