Oracle PL/SQL编程详解

1   DECLARE   
2     --声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 
3   BEGIN 
4     -- 执行部分:  过程及SQL 语句  , 即程序的主要部分 
5   EXCEPTION 
6     -- 执行异常部分: 错误处理 
7   END;

1 DECLARE 
2    ename varchar2(20) :='EricHu'; 
3 BEGIN 
4   DELETE FROM scott.emp WHERE ename=ename; 
5 END;

 1 DECLARE 
 2    Row_id ROWID; 
 3    info    VARCHAR2(40); 
 4 BEGIN 
 5   INSERT INTO scott.dept VALUES (90, '财务室', '海口') 
 6   RETURNING rowid, dname||':'||to_char(deptno)||':'||loc 
 7   INTO row_id, info; 
 8   DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); 
 9   DBMS_OUTPUT.PUT_LINE(info); 
10 END;

 1 DECLARE 
 2    Row_id ROWID; 
 3    info   VARCHAR2(40); 
 4 BEGIN 
 5   UPDATE dept SET deptno=100 WHERE DNAME='财务室' 
 6   RETURNING rowid, dname||':'||to_char(deptno)||':'||loc 
 7   INTO row_id, info; 
 8   DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); 
 9   DBMS_OUTPUT.PUT_LINE(info); 
10 END;

 1 DECLARE 
 2    Row_id ROWID; 
 3    info   VARCHAR2(40); 
 4 BEGIN 
 5   DELETE dept WHERE DNAME='办公室' 
 6   RETURNING rowid, dname||':'||to_char(deptno)||':'||loc 
 7   INTO row_id, info; 
 8   DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id); 
 9   DBMS_OUTPUT.PUT_LINE(info); 
10 END;

 1 DECLARE  
 2    TYPE test_rec IS RECORD( 
 3          Name VARCHAR2(30) NOT NULL := '胡勇', 
 4          Info VARCHAR2(100)); 
 5    rec_book test_rec; 
 6 BEGIN 
 7    rec_book.Name :='胡勇'; 
 8    rec_book.Info :='谈PL/SQL编程;'; 
 9    DBMS_OUTPUT.PUT_LINE(rec_book.Name||'  ' ||rec_book.Info); 
10 END;

 1 DECLARE 
 2 --定义与hr.employees表中的这几个列相同的记录数据类型 
 3    TYPE RECORD_TYPE_EMPLOYEES IS RECORD( 
 4         f_name   hr.employees.first_name%TYPE, 
 5         h_date   hr.employees.hire_date%TYPE, 
 6         j_id     hr.employees.job_id%TYPE); 
 7 --声明一个该记录数据类型的记录变量 
 8    v_emp_record RECORD_TYPE_EMPLOYEES; 
 9 BEGIN 
10    SELECT first_name, hire_date, job_id INTO v_emp_record 
11    FROM employees 
12    WHERE employee_id = &emp_id; 
13    DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name 
14              ||'  雇佣日期：'||v_emp_record.h_date 
15              ||'  岗位：'||v_emp_record.j_id); 
16 END;

 1 DECLARE 
 2 --定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型 
 3    TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25); 
 4 --声明一个该VARRAY数据类型的变量 
 5    v_reg_varray REG_VARRAY_TYPE; 
 6 BEGIN 
 7 --用构造函数语法赋予初值 
 8    v_reg_varray := reg_varray_type 
 9          ('中国', '美国', '英国', '日本', '法国'); 
10    DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_varray(1)||'、' 
11                                     ||v_reg_varray(2)||'、' 
12                                     ||v_reg_varray(3)||'、' 
13                                     ||v_reg_varray(4)); 
14    DBMS_OUTPUT.PUT_LINE('赋予初值NULL的第5个成员的值：'||v_reg_varray(5)); 
15 --用构造函数语法赋予初值后就可以这样对成员赋值 
16    v_reg_varray(5) := '法国'; 
17    DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_varray(5)); 
18 END;

 1 DECLARE 
 2    -- 用%TYPE 类型定义与表相配的字段 
 3    TYPE T_Record IS RECORD( 
 4         T_no emp.empno%TYPE, 
 5         T_name emp.ename%TYPE, 
 6         T_sal emp.sal%TYPE ); 
 7    -- 声明接收数据的变量 
 8    v_emp T_Record; 
 9 BEGIN 
10    SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788; 
11    DBMS_OUTPUT.PUT_LINE 
12     (TO_CHAR(v_emp.t_no)||' '||v_emp.t_name||'  ' || TO_CHAR(v_emp.t_sal)); 
13 END;

 1 DECLARE 
 2    v_empno emp.empno%TYPE :=&no; 
 3    Type t_record is record ( 
 4         v_name   emp.ename%TYPE, 
 5         v_sal    emp.sal%TYPE, 
 6         v_date   emp.hiredate%TYPE); 
 7    Rec t_record; 
 8 BEGIN 
 9    SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno; 
10    DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date); 
11 END;

1 DECLARE 
2     v_empno emp.empno%TYPE :=&no; 
3     rec emp%ROWTYPE; 
4 BEGIN 
5     SELECT * INTO rec FROM emp WHERE empno=v_empno; 
6     DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);  
7 END;

1 VARIABLE result NUMBER; 
2 BEGIN 
3   SELECT (sal*10)+nvl(comm, 0) INTO :result FROM emp  
4   WHERE empno=7844; 
5 END; 
6 --然后再执行 
7 PRINT result

 1 DECLARE 
 2   TYPE dept_table_type IS TABLE OF  
 3        dept%ROWTYPE INDEX BY BINARY_INTEGER; 
 4   my_dname_table dept_table_type; 
 5   v_count number(2) :=4; 
 6 BEGIN 
 7   FOR int IN 1 .. v_count LOOP 
 8     SELECT * INTO my_dname_table(int) FROM dept WHERE deptno=int*10; 
 9   END LOOP; 
10   FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP 
11   DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).deptno); 
12   DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname); 
13   END LOOP; 
14 END;

 1 DECLARE 
 2 --定义记录表数据类型 
 3    TYPE reg_table_type IS TABLE OF varchar2(25) 
 4    INDEX BY BINARY_INTEGER; 
 5 --声明记录表数据类型的变量 
 6    v_reg_table REG_TABLE_TYPE; 
 7 BEGIN 
 8    v_reg_table(1) := 'Europe'; 
 9    v_reg_table(2) := 'Americas'; 
10    v_reg_table(3) := 'Asia'; 
11    v_reg_table(4) := 'Middle East and Africa'; 
12    v_reg_table(5) := 'NULL'; 
13    DBMS_OUTPUT.PUT_LINE('地区名称：'||v_reg_table (1)||'、' 
14                                     ||v_reg_table (2)||'、' 
15                                     ||v_reg_table (3)||'、' 
16                                     ||v_reg_table (4)); 
17    DBMS_OUTPUT.PUT_LINE('第5个成员的值：'||v_reg_table(5)); 
18 END;

 1 DECLARE 
 2 --定义记录表数据类型 
 3    TYPE emp_table_type IS TABLE OF employees%ROWTYPE 
 4    INDEX BY BINARY_INTEGER; 
 5 --声明记录表数据类型的变量 
 6    v_emp_table EMP_TABLE_TYPE; 
 7 BEGIN 
 8    SELECT first_name, hire_date, job_id INTO 
 9    v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id 
10    FROM employees WHERE employee_id = 177; 
11    SELECT first_name, hire_date, job_id INTO 
12    v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id 
13    FROM employees WHERE employee_id = 178; 
14    DBMS_OUTPUT.PUT_LINE('177雇员名称：'||v_emp_table(1).first_name 
15              ||'  雇佣日期：'||v_emp_table(1).hire_date 
16              ||'  岗位：'||v_emp_table(1).job_id); 
17    DBMS_OUTPUT.PUT_LINE('178雇员名称：'||v_emp_table(2).first_name 
18              ||'  雇佣日期：'||v_emp_table(2).hire_date 
19              ||'  岗位：'||v_emp_table(2).job_id); 
20 END;

1 DECLARE 
2   bDone BOOLEAN; 
3 BEGIN 
4   bDone := FALSE; 
5   WHILE NOT bDone LOOP 
6   Null; 
7   END LOOP; 
8 END;

1 DECLARE 
2   emp_id    emp.empno%TYPE :=7788; 
3   emp_name  emp.ename%TYPE; 
4   wages     emp.sal%TYPE; 
5 BEGIN 
6   SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages  
7   FROM emp WHERE empno = emp_id; 
8   DBMS_OUTPUT.PUT_LINE(emp_name||'----'||to_char(wages)); 
9 END;

 1 DECLARE 
 2    Emess char(80); 
 3 BEGIN 
 4    DECLARE 
 5       V1 NUMBER(4); 
 6    BEGIN 
 7       SELECT empno INTO v1 FROM emp WHERE LOWER(job)='president'; 
 8       DBMS_OUTPUT.PUT_LINE(V1); 
 9    EXCEPTION 
10       When TOO_MANY_ROWS THEN 
11          DBMS_OUTPUT.PUT_LINE ('More than one president'); 
12    END; 
13    DECLARE  
14       V1 NUMBER(4); 
15    BEGIN 
16       SELECT empno INTO v1 FROM emp WHERE LOWER(job)='manager'; 
17    EXCEPTION 
18       When TOO_MANY_ROWS THEN 
19           DBMS_OUTPUT.PUT_LINE ('More than one manager'); 
20    END; 
21 EXCEPTION 
22    When others THEN 
23       Emess:=substr(SQLERRM,1,80); 
24       DBMS_OUTPUT.PUT_LINE(emess); 
25 END;

 1 /***********************************************/ 
 2 /* 文件名： test.sql                  */ 
 3 /* 说 明: 
 4        一个简单的插入测试，无实际应用。*/ 
 5 /* 作 者： EricHu                     */ 
 6 /* 时 间： 2011-5-9                  */ 
 7 /***********************************************/ 
 8 DECLARE 
 9   v_ename   VARCHAR2(20) := 'Bill'; 
10   v_sal       NUMBER(7,2) :=1234.56; 
11   v_deptno   NUMBER(2) := 10; 
12   v_empno   NUMBER(4) := 8888; 
13 BEGIN 
14   INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )   
15   VALUES (v_empno, v_ename, 'Manager', v_sal, v_deptno,  
16             TO_DATE('1954.06.09','yyyy.mm.dd') ); 
17   COMMIT; 
18 END;

 1 /***********************************************/ 
 2 /* 文件名： test_deletedata.sql      */ 
 3 /* 说 明: 
 4        简单的删除例子，不是实际应用。 */ 
 5 /* 作 者： EricHu                     */ 
 6 /* 时 间： 2011-5-9                  */ 
 7 /***********************************************/ 
 8 DECLARE 
 9   v_ename   VARCHAR2(20) := 'Bill'; 
10   v_sal       NUMBER(7,2) :=1234.56; 
11   v_deptno   NUMBER(2) := 10; 
12   v_empno   NUMBER(4) := 8888; 
13 BEGIN 
14   INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )   
15 VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno,  
16 TO_DATE(’1954.06.09’,’yyyy.mm.dd’) ); 
17   COMMIT; 
18 END; 
19 DECLARE 
20   v_empno   number(4) := 8888; 
21 BEGIN 
22   DELETE FROM emp WHERE empno=v_empno; 
23   COMMIT; 
24 END;

 1 DECLARE 
 2     v_empno  employees.employee_id%TYPE :=&empno; 
 3     V_salary employees.salary%TYPE; 
 4     V_comment VARCHAR2(35); 
 5 BEGIN 
 6 SELECT salary INTO v_salary FROM employees  
 7 WHERE employee_id = v_empno; 
 8 IF v_salary < 1500 THEN 
 9        V_comment:= '太少了,加点吧~!'; 
10    ELSIF v_salary <3000 THEN 
11       V_comment:= '多了点,少点吧~!'; 
12 ELSE 
13       V_comment:= '没有薪水~!'; 
14 END IF; 
15    DBMS_OUTPUT.PUT_LINE(V_comment); 
16    exception 
17 when no_data_found then 
18         DBMS_OUTPUT.PUT_LINE('没有数据~!'); 
19 when others then 
20         DBMS_OUTPUT.PUT_LINE(sqlcode || '---' || sqlerrm);         
21 END;

 1 DECLARE 
 2    v_first_name  VARCHAR2(20); 
 3    v_salary NUMBER(7,2); 
 4 BEGIN 
 5 SELECT first_name, salary INTO v_first_name, v_salary FROM employees 
 6 WHERE employee_id = &emp_id; 
 7    DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的工资是'||v_salary); 
 8 IF v_salary < 10000 THEN 
 9       DBMS_OUTPUT.PUT_LINE('工资低于10000'); 
10 ELSE 
11 IF 10000 <= v_salary AND v_salary < 20000 THEN 
12          DBMS_OUTPUT.PUT_LINE('工资在10000到20000之间'); 
13 ELSE 
14          DBMS_OUTPUT.PUT_LINE('工资高于20000'); 
15 END IF; 
16 END IF; 
17 END;

 1 DECLARE 
 2    v_first_name  VARCHAR2(20); 
 3    v_hire_date DATE; 
 4    v_bonus NUMBER(6,2); 
 5 BEGIN 
 6 SELECT first_name, hire_date INTO v_first_name, v_hire_date FROM employees 
 7 WHERE employee_id = &emp_id; 
 8 IF v_hire_date > TO_DATE('01-1月-90') THEN 
 9       v_bonus := 800; 
10    ELSIF v_hire_date > TO_DATE('01-1月-88') THEN 
11       v_bonus := 1600; 
12 ELSE 
13       v_bonus := 2400; 
14 END IF; 
15    DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的雇佣日期是'||v_hire_date 
16 ||'、奖金是'||v_bonus); 
17 END;

 1 DECLARE 
 2   V_grade char(1) := UPPER('&p_grade'); 
 3   V_appraisal VARCHAR2(20); 
 4 BEGIN 
 5   V_appraisal := 
 6 CASE v_grade 
 7 WHEN 'A' THEN 'Excellent' 
 8 WHEN 'B' THEN 'Very Good' 
 9 WHEN 'C' THEN 'Good' 
10 ELSE 'No such grade' 
11 END; 
12   DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||'  Appraisal: '|| v_appraisal); 
13 END;

 1 DECLARE 
 2    v_first_name employees.first_name%TYPE; 
 3    v_job_id employees.job_id%TYPE; 
 4    v_salary employees.salary%TYPE; 
 5    v_sal_raise NUMBER(3,2); 
 6 BEGIN 
 7 SELECT first_name,   job_id,   salary INTO 
 8           v_first_name, v_job_id, v_salary 
 9 FROM employees WHERE employee_id = &emp_id; 
10 CASE 
11 WHEN v_job_id = 'PU_CLERK' THEN 
12 IF v_salary < 3000 THEN v_sal_raise := .08; 
13 ELSE v_sal_raise := .07; 
14 END IF; 
15 WHEN v_job_id = 'SH_CLERK' THEN 
16 IF v_salary < 4000 THEN v_sal_raise := .06; 
17 ELSE v_sal_raise := .05; 
18 END IF; 
19 WHEN v_job_id = 'ST_CLERK' THEN 
20 IF v_salary < 3500 THEN v_sal_raise := .04; 
21 ELSE v_sal_raise := .03; 
22 END IF; 
23 ELSE 
24          DBMS_OUTPUT.PUT_LINE('该岗位不涨工资: '||v_job_id); 
25 END CASE; 
26    DBMS_OUTPUT.PUT_LINE(v_first_name||'的岗位是'||v_job_id 
27 ||'、的工资是'||v_salary 
28 ||'、工资涨幅是'||v_sal_raise); 
29 END;

1 DECLARE 
2 int NUMBER(2) :=0; 
3 BEGIN 
4    LOOP 
5 int := int + 1; 
6       DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int); 
7 EXIT WHEN int =10; 
8 END LOOP; 
9 END;

1 DECLARE 
2   x NUMBER :=1; 
3 BEGIN 
4 WHILE x<=10 LOOP 
5       DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x); 
6        x:= x+1; 
7 END LOOP; 
8 END;

 1 CREATE TABLE temp_table(num_col NUMBER); 
 2 DECLARE 
 3     V_counter NUMBER := 10; 
 4 BEGIN 
 5 INSERT INTO temp_table(num_col) VALUES (v_counter ); 
 6 FOR v_counter IN 20 .. 25 LOOP 
 7 INSERT INTO temp_table (num_col ) VALUES ( v_counter ); 
 8 END LOOP; 
 9 INSERT INTO temp_table(num_col) VALUES (v_counter ); 
10 FOR v_counter IN REVERSE 20 .. 25 LOOP 
11 INSERT INTO temp_table (num_col ) VALUES ( v_counter ); 
12 END LOOP; 
13 END ; 
14 DROP TABLE temp_table;

 1 DECLARE 
 2    TYPE jobids_varray IS VARRAY(12) OF VARCHAR2(10); --定义一个VARRAY数据类型 
 3    v_jobids JOBIDS_VARRAY; --声明一个具有JOBIDS_VARRAY数据类型的变量 
 4    v_howmany NUMBER; --声明一个变量来保存雇员的数量 
 5 BEGIN 
 6 --用某些job_id值初始化数组 
 7    v_jobids := jobids_varray('FI_ACCOUNT', 'FI_MGR', 'ST_CLERK', 'ST_MAN'); 
 8 --用FOR...LOOP...END LOOP循环使用每个数组成员的值 
 9 FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP 
10 --针对数组中的每个岗位，决定该岗位的雇员的数量 
11 SELECT count(*) INTO v_howmany FROM employees WHERE job_id = v_jobids(i); 
12       DBMS_OUTPUT.PUT_LINE ( '岗位'||v_jobids(i)|| 
13 '总共有'|| TO_CHAR(v_howmany) || '个雇员'); 
14 END LOOP; 
15 END;

 1 /*求100至110之间的素数*/ 
 2 DECLARE 
 3    v_m NUMBER := 101; 
 4    v_i NUMBER; 
 5    v_n NUMBER := 0; 
 6 BEGIN 
 7 WHILE v_m < 110 LOOP 
 8       v_i := 2; 
 9       LOOP 
10 IF mod(v_m, v_i) = 0 THEN 
11             v_i := 0; 
12 EXIT; 
13 END IF; 
14          v_i := v_i + 1; 
15 EXIT WHEN v_i > v_m - 1;  
16 END LOOP; 
17 IF v_i > 0 THEN 
18          v_n := v_n + 1; 
19          DBMS_OUTPUT.PUT_LINE('第'|| v_n || '个素数是' || v_m); 
20 END IF; 
21       v_m := v_m + 2; 
22 END LOOP; 
23 END;

 1 DECLARE 
 2    V_counter NUMBER := 1; 
 3 BEGIN 
 4    LOOP  
 5      DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter); 
 6      V_counter := v_counter + 1; 
 7 IF v_counter > 10 THEN 
 8 GOTO labelOffLOOP; 
 9 END IF; 
10 END LOOP; 
11 <<labelOffLOOP>> 
12      DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter); 
13 END;

 1 DECLARE 
 2    v_i NUMBER := 0; 
 3    v_s NUMBER := 0; 
 4 BEGIN 
 5 <<label_1>> 
 6    v_i := v_i + 1; 
 7 IF v_i <= 1000 THEN 
 8       v_s := v_s + v_i; 
 9 GOTO label_1; 
10 END IF; 
11    DBMS_OUTPUT.PUT_LINE(v_s); 
12 END;

 1 DECLARE 
 2     ... 
 3 BEGIN 
 4     ... 
 5 IF v_num IS NULL THEN 
 6 GOTO labelPrint; 
 7 END IF; 
 8   … 
 9 <<labelPrint>> 
10 NULL; --不需要处理任何数据。 
11 END;

 1 DECLARE 
 2    v_emp_id employees.employee_id%TYPE; 
 3    v_first_name employees.first_name%TYPE; 
 4    v_salary employees.salary%TYPE; 
 5    v_sal_raise NUMBER(3,2); 
 6 BEGIN 
 7    v_emp_id := &emp_id; 
 8 SELECT first_name, salary INTO v_first_name, v_salary 
 9 FROM employees WHERE employee_id = v_emp_id; 
10 IF v_salary <= 3000 THEN 
11       v_sal_raise := .10; 
12       DBMS_OUTPUT.PUT_LINE(v_first_name||'的工资是'||v_salary 
13 ||'、工资涨幅是'||v_sal_raise); 
14 ELSE 
15 NULL; 
16 END IF; 
17 END;

 1 DECLARE 
 2 CURSOR c_cursor  
 3 IS SELECT first_name || last_name, Salary  
 4 FROM EMPLOYEES  
 5 WHERE rownum<11;    
 6    v_ename  EMPLOYEES.first_name%TYPE; 
 7    v_sal    EMPLOYEES.Salary%TYPE;    
 8 BEGIN 
 9 OPEN c_cursor; 
10 FETCH c_cursor INTO v_ename, v_sal; 
11 WHILE c_cursor%FOUND LOOP 
12      DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) ); 
13 FETCH c_cursor INTO v_ename, v_sal; 
14 END LOOP; 
15 CLOSE c_cursor; 
16 END;

 1 DECLARE 
 2   DeptRec    DEPARTMENTS%ROWTYPE; 
 3   Dept_name  DEPARTMENTS.DEPARTMENT_NAME%TYPE; 
 4   Dept_loc   DEPARTMENTS.LOCATION_ID%TYPE; 
 5 CURSOR c1 IS 
 6 SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS  
 7 WHERE DEPARTMENT_ID <= 30; 
 8 CURSOR c2(dept_no NUMBER DEFAULT 10) IS 
 9 SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS  
10 WHERE DEPARTMENT_ID <= dept_no; 
11 CURSOR c3(dept_no NUMBER DEFAULT 10) IS 
12 SELECT * FROM DEPARTMENTS  
13 WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no; 
14 BEGIN 
15 OPEN c1; 
16   LOOP 
17 FETCH c1 INTO dept_name, dept_loc; 
18 EXIT WHEN c1%NOTFOUND; 
19         DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc); 
20 END LOOP; 
21 CLOSE c1; 
22 OPEN c2; 
23     LOOP 
24 FETCH c2 INTO dept_name, dept_loc; 
25 EXIT WHEN c2%NOTFOUND; 
26         DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc); 
27 END LOOP; 
28 CLOSE c2; 
29 OPEN c3(dept_no =>20); 
30     LOOP 
31 FETCH c3 INTO deptrec; 
32 EXIT WHEN c3%NOTFOUND; 
33         DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID); 
34 END LOOP; 
35 CLOSE c3; 
36 END;

 1 DECLARE 
 2    v_empno  EMPLOYEES.EMPLOYEE_ID%TYPE; 
 3    v_sal      EMPLOYEES.Salary%TYPE; 
 4 CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;  
 5 BEGIN 
 6 OPEN c_cursor; 
 7    LOOP 
 8 FETCH c_cursor INTO v_empno, v_sal; 
 9 EXIT WHEN c_cursor%NOTFOUND;  
10 IF v_sal<=1200 THEN 
11 UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno; 
12             DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!'); 
13 END IF; 
14    DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor %ROWCOUNT); 
15 END LOOP; 
16 CLOSE c_cursor; 
17 END;

 1 DECLARE 
 2    v_f_name employees.first_name%TYPE; 
 3    v_j_id   employees.job_id%TYPE; 
 4 CURSOR c1       --声明游标,没有参数没有返回值 
 5 IS 
 6 SELECT first_name, job_id FROM employees  
 7 WHERE department_id = 20; 
 8 BEGIN 
 9 OPEN c1;        --打开游标 
10    LOOP 
11 FETCH c1 INTO v_f_name, v_j_id;    --提取游标 
12 IF c1%FOUND THEN 
13          DBMS_OUTPUT.PUT_LINE(v_f_name||'的岗位是'||v_j_id); 
14 ELSE 
15          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 
16 EXIT; 
17 END IF; 
18 END LOOP; 
19 CLOSE c1;   --关闭游标 
20 END;

 1 DECLARE 
 2    v_f_name employees.first_name%TYPE; 
 3    v_h_date employees.hire_date%TYPE; 
 4 CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值 
 5 IS 
 6 SELECT first_name, hire_date FROM employees 
 7 WHERE department_id = dept_id AND job_id = j_id; 
 8 BEGIN 
 9 OPEN c2(90, 'AD_VP');  --打开游标,传递参数值 
10    LOOP 
11 FETCH c2 INTO v_f_name, v_h_date;    --提取游标 
12 IF c2%FOUND THEN 
13          DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇佣日期是'||v_h_date); 
14 ELSE 
15          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 
16 EXIT; 
17 END IF; 
18 END LOOP; 
19 CLOSE c2;   --关闭游标 
20 END;

 1 DECLARE 
 2    TYPE emp_record_type IS RECORD( 
 3         f_name   employees.first_name%TYPE, 
 4         h_date   employees.hire_date%TYPE); 
 5    v_emp_record EMP_RECORD_TYPE; 
 6 CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数有返回值 
 7 RETURN EMP_RECORD_TYPE 
 8 IS 
 9 SELECT first_name, hire_date FROM employees 
10 WHERE department_id = dept_id AND job_id = j_id; 
11 BEGIN 
12 OPEN c3(j_id => 'AD_VP', dept_id => 90);  --打开游标,传递参数值 
13    LOOP 
14 FETCH c3 INTO v_emp_record;    --提取游标 
15 IF c3%FOUND THEN 
16          DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是' 
17 ||v_emp_record.h_date); 
18 ELSE 
19          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 
20 EXIT; 
21 END IF; 
22 END LOOP; 
23 CLOSE c3;   --关闭游标 
24 END;

 1 DECLARE 
 2 CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值 
 3 IS 
 4 SELECT first_name f_name, hire_date FROM employees 
 5 WHERE department_id = dept_id AND job_id = j_id; 
 6 --基于游标定义记录变量，比声明记录类型变量要方便，不容易出错 
 7     v_emp_record c4%ROWTYPE; 
 8 BEGIN 
 9 OPEN c4(90, 'AD_VP');  --打开游标,传递参数值 
10    LOOP 
11 FETCH c4 INTO v_emp_record;    --提取游标 
12 IF c4%FOUND THEN 
13          DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是' 
14 ||v_emp_record.hire_date); 
15 ELSE 
16          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了'); 
17 EXIT; 
18 END IF; 
19 END LOOP; 
20 CLOSE c4;   --关闭游标 
21 END;

 1 DECLARE 
 2 CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary 
 3 FROM employees ; 
 4 BEGIN 
 5 --隐含打开游标 
 6 FOR v_sal IN c_sal LOOP 
 7 --隐含执行一个FETCH语句 
 8       DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ; 
 9 --隐含监测c_sal%NOTFOUND 
10 END LOOP; 
11 --隐含关闭游标 
12 END;

 1 DECLARE 
 2 CURSOR c_cursor(dept_no NUMBER DEFAULT 10)  
 3 IS 
 4 SELECT department_name, location_id FROM departments WHERE department_id <= dept_no; 
 5 BEGIN 
 6     DBMS_OUTPUT.PUT_LINE('当dept_no参数值为30：'); 
 7 FOR c1_rec IN c_cursor(30) LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); 
 8 END LOOP; 
 9     DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no参数值10：'); 
10 FOR c1_rec IN c_cursor LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); 
11 END LOOP; 
12 END;

1 BEGIN 
2 FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP        DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id); 
3 END LOOP; 
4 END;

1 DECLARE 
2     V_deptno department_id%TYPE :=&p_deptno; 
3 BEGIN 
4 DELETE FROM employees WHERE department_id=v_deptno; 
5 IF SQL%NOTFOUND THEN 
6 DELETE FROM departments WHERE department_id=v_deptno; 
7 END IF; 
8 END;

 1 DECLARE 
 2    v_rows NUMBER; 
 3 BEGIN 
 4 --更新数据 
 5 UPDATE employees SET salary = 30000 
 6 WHERE department_id = 90 AND job_id = 'AD_VP'; 
 7 --获取默认游标的属性值 
 8    v_rows := SQL%ROWCOUNT; 
 9    DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资'); 
10 --回退更新，以便使数据库的数据保持原样 
11 ROLLBACK; 
12 END;

 1 DECLARE 
 2     V_deptno employees.department_id%TYPE :=&p_deptno; 
 3 CURSOR emp_cursor  
 4 IS 
 5 SELECT employees.employee_id, employees.salary  
 6 FROM employees WHERE employees.department_id=v_deptno 
 7 FOR UPDATE NOWAIT; 
 8 BEGIN 
 9 FOR emp_record IN emp_cursor LOOP 
10 IF emp_record.salary < 1500 THEN 
11 UPDATE employees SET salary=1500 
12 WHERE CURRENT OF emp_cursor; 
13 END IF; 
14 END LOOP; 
15 --    COMMIT; 
16 END;

 1 DECLARE 
 2    v_emp_record employees%ROWTYPE; 
 3 CURSOR c1 
 4 IS 
 5 SELECT * FROM employees FOR UPDATE; 
 6 BEGIN 
 7 OPEN c1; 
 8    LOOP 
 9 FETCH c1 INTO v_emp_record; 
10 EXIT WHEN c1%NOTFOUND; 
11 IF v_emp_record.department_id = 90 AND 
12          v_emp_record.job_id = 'AD_VP' 
13 THEN 
14 UPDATE employees SET salary = 20000 
15 WHERE CURRENT OF c1;  --更新当前游标行对应的数据行 
16 END IF; 
17 END LOOP; 
18 COMMIT;   --提交已经修改的数据 
19 CLOSE c1; 
20 END;

 1 DECLARE 
 2     TYPE deptrecord IS RECORD( 
 3         Deptno departments.department_id%TYPE, 
 4         Dname departments.department_name%TYPE, 
 5         Loc departments.location_id%TYPE 
 6     ); 
 7     TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE; 
 8     TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord; 
 9     TYPE curtype IS REF CURSOR; 
10     Dept_c1 deptcurtype; 
11     Dept_c2 deptcurtyp1; 
12     Cv curtype;

 1 DECLARE 
 2     TYPE emp_job_rec IS RECORD( 
 3         Employee_id employees.employee_id%TYPE, 
 4         Employee_name employees.first_name%TYPE, 
 5         Job_title employees.job_id%TYPE 
 6     ); 
 7     TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec; 
 8     Emp_refcur emp_job_refcur_type ; 
 9     Emp_job emp_job_rec; 
10 BEGIN 
11 OPEN emp_refcur FOR 
12 SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id  
13 FROM employees  
14 ORDER BY employees.department_id; 
15 FETCH emp_refcur INTO emp_job; 
16 WHILE emp_refcur%FOUND LOOP 
17        DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title); 
18 FETCH emp_refcur INTO emp_job; 
19 END LOOP; 
20 END;

 1 PROMPT 
 2 PROMPT 'What table would you like to see?' 
 3 ACCEPT tab PROMPT '(D)epartment, or (E)mployees:' 
 4 DECLARE 
 5     Type refcur_t IS REF CURSOR; 
 6     Refcur refcur_t; 
 7     TYPE sample_rec_type IS RECORD ( 
 8         Id number, 
 9         Description VARCHAR2 (30) 
10     ); 
11     sample sample_rec_type; 
12     selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1)); 
13 BEGIN 
14 IF selection='D' THEN 
15 OPEN refcur FOR 
16 SELECT departments.department_id, departments.department_name FROM departments; 
17         DBMS_OUTPUT.PUT_LINE('Department data'); 
18     ELSIF selection='E' THEN 
19 OPEN refcur FOR 
20 SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees; 
21         DBMS_OUTPUT.PUT_LINE('Employee data'); 
22 ELSE 
23         DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E'''); 
24 RETURN; 
25 END IF; 
26     DBMS_OUTPUT.PUT_LINE('----------------------'); 
27 FETCH refcur INTO sample; 
28 WHILE refcur%FOUND LOOP 
29         DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description); 
30 FETCH refcur INTO sample; 
31 END LOOP; 
32 CLOSE refcur; 
33 END;

 1 DECLARE 
 2 --定义一个游标数据类型 
 3    TYPE emp_cursor_type IS REF CURSOR; 
 4 --声明一个游标变量 
 5    c1 EMP_CURSOR_TYPE; 
 6 --声明两个记录变量 
 7    v_emp_record employees%ROWTYPE; 
 8    v_reg_record regions%ROWTYPE; 
 9 BEGIN 
10 OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20; 
11    LOOP 
12 FETCH c1 INTO v_emp_record; 
13 EXIT WHEN c1%NOTFOUND; 
14       DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是' 
15 ||v_emp_record.hire_date); 
16 END LOOP; 
17 --将同一个游标变量对应到另一个SELECT语句 
18 OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）; 
19    LOOP 
20 FETCH c1 INTO v_reg_record; 
21 EXIT WHEN c1%NOTFOUND; 
22       DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示' 
23 ||v_reg_record.region_name); 
24 END LOOP; 
25 CLOSE c1; 
26 END;

 1 DECLARE 
 2 --定义一个与employees表中的这几个列相同的记录数据类型 
 3    TYPE emp_record_type IS RECORD( 
 4         f_name   employees.first_name%TYPE, 
 5         h_date   employees.hire_date%TYPE, 
 6         j_id     employees.job_id%TYPE); 
 7 --声明一个该记录数据类型的记录变量 
 8    v_emp_record EMP_RECORD_TYPE; 
 9 --定义一个游标数据类型 
10    TYPE emp_cursor_type IS REF CURSOR 
11 RETURN EMP_RECORD_TYPE; 
12 --声明一个游标变量 
13    c1 EMP_CURSOR_TYPE; 
14 BEGIN 
15 OPEN c1 FOR SELECT first_name, hire_date, job_id 
16 FROM employees WHERE department_id = 20; 
17    LOOP 
18 FETCH c1 INTO v_emp_record; 
19 EXIT WHEN c1%NOTFOUND; 
20       DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name 
21 ||'  雇佣日期：'||v_emp_record.h_date 
22 ||'  岗位：'||v_emp_record.j_id); 
23 END LOOP; 
24 CLOSE c1; 
25 END;

1 EXCEPTION 
2 WHEN first_exception THEN <code to handle first exception > 
3 WHEN second_exception THEN <code to handle second exception > 
4 WHEN OTHERS THEN <code to handle others exception > 
5 END;

 1 DECLARE 
 2    v_empno employees.employee_id%TYPE := &empno; 
 3    v_sal   employees.salary%TYPE; 
 4 BEGIN 
 5 SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno; 
 6 IF v_sal<=1500 THEN 
 7 UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno;  
 8         DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');      
 9 ELSE 
10         DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!'); 
11 END IF; 
12 EXCEPTION 
13 WHEN NO_DATA_FOUND THEN 
14       DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工'); 
15 WHEN TOO_MANY_ROWS THEN 
16       DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标'); 
17 WHEN OTHERS THEN 
18       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
19 END;

 1 INSERT INTO departments VALUES(50, 'FINANCE', 'CHICAGO'); 
 2 DECLARE 
 3    v_deptno departments.department_id%TYPE := &deptno; 
 4    deptno_remaining EXCEPTION; 
 5    PRAGMA EXCEPTION_INIT(deptno_remaining, -2292); 
 6 /* -2292 是违反一致性约束的错误代码 */ 
 7 BEGIN 
 8 DELETE FROM departments WHERE department_id = v_deptno; 
 9 EXCEPTION 
10 WHEN deptno_remaining THEN 
11       DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!'); 
12 WHEN OTHERS THEN 
13       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
14 END;

 1 DECLARE 
 2    v_empno employees.employee_id%TYPE :=&empno; 
 3    no_result  EXCEPTION; 
 4 BEGIN 
 5 UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno; 
 6 IF SQL%NOTFOUND THEN 
 7       RAISE no_result; 
 8 END IF; 
 9 EXCEPTION 
10 WHEN no_result THEN 
11       DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!'); 
12 WHEN OTHERS THEN 
13       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
14 END;

 1 CREATE TABLE errlog( 
 2   Errcode NUMBER, 
 3   Errtext CHAR(40)); 
 4 CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER) 
 5 RETURN NUMBER 
 6 AS 
 7   v_sal NUMBER; 
 8 BEGIN 
 9 IF p_deptno IS NULL THEN 
10     RAISE_APPLICATION_ERROR(-20991, ’部门代码为空’); 
11   ELSIF p_deptno<0 THEN 
12     RAISE_APPLICATION_ERROR(-20992, ’无效的部门代码’); 
13 ELSE 
14 SELECT SUM(employees.salary) INTO v_sal FROM employees  
15 WHERE employees.department_id=p_deptno; 
16 RETURN v_sal; 
17 END IF; 
18 END; 
19 DECLARE 
20   V_salary NUMBER(7,2); 
21   V_sqlcode NUMBER; 
22   V_sqlerr VARCHAR2(512); 
23   Null_deptno EXCEPTION; 
24   Invalid_deptno EXCEPTION; 
25   PRAGMA EXCEPTION_INIT(null_deptno,-20991); 
26   PRAGMA EXCEPTION_INIT(invalid_deptno, -20992); 
27 BEGIN 
28   V_salary :=get_salary(10); 
29   DBMS_OUTPUT.PUT_LINE('10号部门工资：' || TO_CHAR(V_salary)); 
30 BEGIN 
31     V_salary :=get_salary(-10); 
32   EXCEPTION 
33 WHEN invalid_deptno THEN 
34       V_sqlcode :=SQLCODE; 
35       V_sqlerr  :=SQLERRM; 
36 INSERT INTO errlog(errcode, errtext)  
37 VALUES(v_sqlcode, v_sqlerr); 
38 COMMIT; 
39 END inner1; 
40   V_salary :=get_salary(20); 
41   DBMS_OUTPUT.PUT_LINE('部门号为20的工资为：'||TO_CHAR(V_salary)); 
42 BEGIN 
43     V_salary :=get_salary(NULL); 
44 END inner2; 
45   V_salary := get_salary(30); 
46   DBMS_OUTPUT.PUT_LINE('部门号为30的工资为：'||TO_CHAR(V_salary)); 
47   EXCEPTION 
48 WHEN null_deptno THEN 
49       V_sqlcode :=SQLCODE; 
50       V_sqlerr  :=SQLERRM; 
51 INSERT INTO errlog(errcode, errtext) VALUES(v_sqlcode, v_sqlerr); 
52 COMMIT; 
53 WHEN OTHERS THEN 
54          DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
55 END outer;

1 CREATE OR REPLACE TRIGGER tr_insert_emp 
2 BEFORE INSERT ON employees 
3 FOR EACH ROW 
4 BEGIN 
5 IF :new.first_name IS NULL OR :new.last_name is null THEN 
6     RAISE_APPLICATION_ERROR(-20000,'Employee must have a name.'); 
7 END IF; 
8 END;

1 DECLARE 
2     name varchar2(12):='EricHu'; 
3     其它语句 
4 BEGIN 
5     其它语句 
6 EXCEPTION 
7 WHEN OTHERS THEN 
8     其它语句 
9 END;

 1 BEGIN 
 2 DECLARE 
 3     name varchar2(12):='EricHu'; 
 4     其它语句 
 5 BEGIN 
 6     其它语句 
 7    EXCEPTION 
 8 WHEN OTHERS THEN 
 9     其它语句 
10 END; 
11 EXCEPTION 
12 WHEN OTHERS THEN 
13     其它语句 
14 END;

 1 CREATE TABLE errors (errnum NUMBER(4), errmsg VARCHAR2(100)); 
 2 DECLARE 
 3    err_msg  VARCHAR2(100); 
 4 BEGIN 
 5 /*  得到所有 ORACLE 错误信息  */ 
 6 FOR err_num IN -100 .. 0 LOOP 
 7       err_msg := SQLERRM(err_num); 
 8 INSERT INTO errors VALUES(err_num, err_msg); 
 9 END LOOP; 
10 END; 
11 DROP TABLE errors;

 1 BEGIN 
 2 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 
 3 VALUES(2222, 'Eric','Hu', SYSDATE, 20); 
 4    DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 
 5 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 
 6 VALUES(2222, '胡','勇', SYSDATE, 20); 
 7    DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 
 8 EXCEPTION 
 9 WHEN OTHERS THEN 
10       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
11 END;

 1 DECLARE 
 2    empno_remaining EXCEPTION; 
 3    PRAGMA EXCEPTION_INIT(empno_remaining, -1); 
 4 /* -1 是违反唯一约束条件的错误代码 */ 
 5 BEGIN 
 6 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 
 7 VALUES(3333, 'Eric','Hu', SYSDATE, 20); 
 8    DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 
 9 INSERT INTO employees(employee_id, first_name,last_name,hire_date,department_id) 
10 VALUES(3333, '胡','勇',SYSDATE, 20); 
11    DBMS_OUTPUT.PUT_LINE('插入数据记录成功!'); 
12 EXCEPTION 
13 WHEN empno_remaining THEN 
14       DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!'); 
15 WHEN OTHERS THEN 
16       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
17 END;

 1 CREATE [OR REPLACE] FUNCTION function_name 
 2 (arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1], 
 3 [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]], 
 4 ...... 
 5 [argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]]) 
 6 [ AUTHID DEFINER | CURRENT_USER ] 
 7 RETURN return_type  
 8 IS | AS 
 9 <类型.变量的声明部分> 
10 BEGIN 
11     执行部分 
12 RETURN expression 
13 EXCEPTION 
14     异常处理部分 
15 END function_name;

 1 --获取某部门的工资总和 
 2 CREATE OR REPLACE 
 3 FUNCTION get_salary( 
 4   Dept_no NUMBER, 
 5   Emp_count OUT NUMBER) 
 6 RETURN NUMBER 
 7 IS 
 8   V_sum NUMBER; 
 9 BEGIN 
10 SELECT SUM(SALARY), count(*) INTO V_sum, emp_count 
11 FROM EMPLOYEES WHERE DEPARTMENT_ID=dept_no; 
12 RETURN v_sum; 
13 EXCEPTION 
14 WHEN NO_DATA_FOUND THEN 
15       DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 
16 WHEN OTHERS THEN 
17       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
18 END get_salary;

1 DECLARE 
2   V_num NUMBER; 
3   V_sum NUMBER; 
4 BEGIN 
5   V_sum :=get_salary(10, v_num); 
6   DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num); 
7 END;

1 DECLARE 
2   V_num NUMBER; 
3     V_sum NUMBER; 
4 BEGIN 
5     V_sum :=get_salary(emp_count => v_num, dept_no => 10); 
6     DBMS_OUTPUT.PUT_LINE('部门号为:10的工资总和：'||v_sum||'，人数为：'||v_num); 
7 END;

 1 CREATE OR REPLACE FUNCTION demo_fun( 
 2   Name VARCHAR2,--注意VARCHAR2不能给精度，如：VARCHAR2(10)，其它类似 
 3   Age INTEGER, 
 4   Sex VARCHAR2) 
 5 RETURN VARCHAR2 
 6 AS 
 7   V_var VARCHAR2(32); 
 8 BEGIN 
 9   V_var := name||'：'||TO_CHAR(age)||'岁.'||sex; 
10 RETURN v_var; 
11 END; 
12 DECLARE 
13 Var VARCHAR(32); 
14 BEGIN 
15 Var := demo_fun('user1', 30, sex => '男'); 
16   DBMS_OUTPUT.PUT_LINE(var); 
17 Var := demo_fun('user2', age => 40, sex => '男'); 
18   DBMS_OUTPUT.PUT_LINE(var); 
19 Var := demo_fun('user3', sex => '女', age => 20); 
20   DBMS_OUTPUT.PUT_LINE(var); 
21 END;

 1 CREATE OR REPLACE FUNCTION demo_fun( 
 2   Name VARCHAR2, 
 3   Age INTEGER, 
 4   Sex VARCHAR2 DEFAULT '男') 
 5 RETURN VARCHAR2 
 6 AS 
 7   V_var VARCHAR2(32); 
 8 BEGIN 
 9   V_var := name||'：'||TO_CHAR(age)||'岁.'||sex; 
10 RETURN v_var; 
11 END;

 1 DECLARE
 2 
 3 var VARCHAR(32);
 4 
 5 BEGIN
 6 
 7 Var := demo_fun('user1', 30);
 8 
 9 DBMS_OUTPUT.PUT_LINE(var);
10 
11 Var := demo_fun('user2', age => 40);
12 
13 DBMS_OUTPUT.PUT_LINE(var);
14 
15 Var := demo_fun('user3', sex => '女', age => 20);
16 
17 DBMS_OUTPUT.PUT_LINE(var);
18 
19 END;

 1 CREATE [OR REPLACE] PROCEDURE procedure_name 
 2 ([arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1], 
 3 [arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]], 
 4 ...... 
 5 [argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen]) 
 6 [ AUTHID DEFINER | CURRENT_USER ] 
 7 { IS | AS } 
 8 <声明部分> 
 9 BEGIN 
10 <执行部分> 
11 EXCEPTION 
12 <可选的异常错误处理程序> 
13 END procedure_name;

1 CREATE TABLE logtable (userid VARCHAR2(10), logdate date); 
2 CREATE OR REPLACE PROCEDURE logexecution  
3 IS 
4 BEGIN 
5 INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE); 
6 END;

 1 CREATE OR REPLACE 
 2 PROCEDURE DelEmp 
 3 (v_empno IN employees.employee_id%TYPE)  
 4 AS 
 5 No_result EXCEPTION; 
 6 BEGIN 
 7 DELETE FROM employees WHERE employee_id = v_empno; 
 8 IF SQL%NOTFOUND THEN 
 9       RAISE no_result; 
10 END IF; 
11    DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被删除!'); 
12 EXCEPTION 
13 WHEN no_result THEN 
14       DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 
15 WHEN OTHERS THEN 
16       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
17 END DelEmp;

 1 CREATE OR REPLACE 
 2 PROCEDURE InsertEmp( 
 3    v_empno     in employees.employee_id%TYPE, 
 4    v_firstname in employees.first_name%TYPE, 
 5    v_lastname  in employees.last_name%TYPE, 
 6    v_deptno    in employees.department_id%TYPE 
 7    )  
 8 AS 
 9    empno_remaining EXCEPTION; 
10    PRAGMA EXCEPTION_INIT(empno_remaining, -1); 
11 /* -1 是违反唯一约束条件的错误代码 */ 
12 BEGIN 
13 INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE,DEPARTMENT_ID) 
14 VALUES(v_empno, v_firstname,v_lastname, sysdate, v_deptno); 
15    DBMS_OUTPUT.PUT_LINE('温馨提示:插入数据记录成功!'); 
16 EXCEPTION 
17 WHEN empno_remaining THEN 
18       DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!'); 
19 WHEN OTHERS THEN 
20       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
21 END InsertEmp;

 1 CREATE OR REPLACE 
 2 PROCEDURE insert_dept 
 3   (v_dept_id IN departments.department_id%TYPE, 
 4    v_dept_name IN departments.department_name%TYPE, 
 5    v_mgr_id IN departments.manager_id%TYPE, 
 6    v_loc_id IN departments.location_id%TYPE) 
 7 IS 
 8    ept_null_error EXCEPTION; 
 9    PRAGMA EXCEPTION_INIT(ept_null_error, -1400); 
10    ept_no_loc_id EXCEPTION; 
11    PRAGMA EXCEPTION_INIT(ept_no_loc_id, -2291); 
12 BEGIN 
13 INSERT INTO departments 
14    (department_id, department_name, manager_id, location_id) 
15 VALUES 
16    (v_dept_id, v_dept_name, v_mgr_id, v_loc_id); 
17    DBMS_OUTPUT.PUT_LINE('插入部门'||v_dept_id||'成功'); 
18 EXCEPTION 
19 WHEN DUP_VAL_ON_INDEX THEN 
20       RAISE_APPLICATION_ERROR(-20000, '部门编码不能重复'); 
21 WHEN ept_null_error THEN 
22       RAISE_APPLICATION_ERROR(-20001, '部门编码、部门名称不能为空'); 
23 WHEN ept_no_loc_id THEN 
24       RAISE_APPLICATION_ERROR(-20002, '没有该地点'); 
25 END insert_dept; 
26 /*调用实例一: 
27 DECLARE 
28    ept_20000 EXCEPTION; 
29    PRAGMA EXCEPTION_INIT(ept_20000, -20000); 
30    ept_20001 EXCEPTION; 
31    PRAGMA EXCEPTION_INIT(ept_20001, -20001); 
32    ept_20002 EXCEPTION; 
33    PRAGMA EXCEPTION_INIT(ept_20002, -20002); 
34 BEGIN 
35    insert_dept(300, '部门300', 100, 2400); 
36    insert_dept(310, NULL, 100, 2400); 
37    insert_dept(310, '部门310', 100, 900); 
38 EXCEPTION 
39    WHEN ept_20000 THEN 
40       DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复'); 
41    WHEN ept_20001 THEN 
42       DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空'); 
43    WHEN ept_20002 THEN 
44       DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点'); 
45    WHEN OTHERS THEN 
46       DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); 
47 END; 
48 调用实例二： 
49 DECLARE 
50    ept_20000 EXCEPTION; 
51    PRAGMA EXCEPTION_INIT(ept_20000, -20000); 
52    ept_20001 EXCEPTION; 
53    PRAGMA EXCEPTION_INIT(ept_20001, -20001); 
54    ept_20002 EXCEPTION; 
55    PRAGMA EXCEPTION_INIT(ept_20002, -20002); 
56 BEGIN 
57    insert_dept(v_dept_name => '部门310', v_dept_id => 310,  
58                v_mgr_id => 100, v_loc_id => 2400); 
59    insert_dept(320, '部门320', v_mgr_id => 100, v_loc_id => 900); 
60 EXCEPTION 
61    WHEN ept_20000 THEN 
62       DBMS_OUTPUT.PUT_LINE('ept_20000部门编码不能重复'); 
63    WHEN ept_20001 THEN 
64       DBMS_OUTPUT.PUT_LINE('ept_20001部门编码、部门名称不能为空'); 
65    WHEN ept_20002 THEN 
66       DBMS_OUTPUT.PUT_LINE('ept_20002没有该地点'); 
67    WHEN OTHERS THEN 
68       DBMS_OUTPUT.PUT_LINE('others出现了其他异常错误'); 
69 END; 
70 */

 1 CREATE OR REPLACE 
 2 PROCEDURE QueryEmp 
 3 (v_empno IN  employees.employee_id%TYPE, 
 4 v_ename OUT employees.first_name%TYPE, 
 5 v_sal   OUT employees.salary%TYPE)  
 6 AS 
 7 BEGIN 
 8 SELECT last_name || last_name, salary INTO v_ename, v_sal  
 9 FROM employees  
10 WHERE employee_id = v_empno;  
11        DBMS_OUTPUT.PUT_LINE('温馨提示:编码为'||v_empno||'的员工已经查到!'); 
12 EXCEPTION 
13 WHEN NO_DATA_FOUND THEN 
14       DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 
15 WHEN OTHERS THEN 
16       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
17 END QueryEmp; 
18 --调用 
19 DECLARE 
20     v1 employees.first_name%TYPE; 
21     v2 employees.salary%TYPE; 
22 BEGIN 
23    QueryEmp(100, v1, v2); 
24    DBMS_OUTPUT.PUT_LINE('姓名:'||v1); 
25    DBMS_OUTPUT.PUT_LINE('工资:'||v2); 
26    QueryEmp(103, v1, v2); 
27    DBMS_OUTPUT.PUT_LINE('姓名:'||v1); 
28    DBMS_OUTPUT.PUT_LINE('工资:'||v2); 
29    QueryEmp(104, v1, v2); 
30    DBMS_OUTPUT.PUT_LINE('姓名:'||v1); 
31    DBMS_OUTPUT.PUT_LINE('工资:'||v2); 
32 END;

 1 CREATE OR REPLACE 
 2 PROCEDURE proc_demo 
 3 ( 
 4   dept_no NUMBER DEFAULT 10, 
 5     sal_sum OUT NUMBER, 
 6     emp_count OUT NUMBER 
 7   ) 
 8 IS 
 9 BEGIN 
10 SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count 
11 FROM employees WHERE department_id = dept_no; 
12 EXCEPTION 
13 WHEN NO_DATA_FOUND THEN 
14       DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 
15 WHEN OTHERS THEN 
16       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
17 END proc_demo; 
18 DECLARE 
19 V_num NUMBER; 
20 V_sum NUMBER(8, 2); 
21 BEGIN 
22   Proc_demo(30, v_sum, v_num); 
23 DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和：'||v_sum||',人数：'||v_num); 
24   Proc_demo(sal_sum => v_sum, emp_count => v_num); 
25 DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和：'||v_sum||',人数：'||v_num); 
26 END;

 1 DECLARE 
 2 V_num NUMBER; 
 3 V_sum NUMBER(8, 2); 
 4 PROCEDURE proc_demo 
 5   ( 
 6     Dept_no NUMBER DEFAULT 10, 
 7     Sal_sum OUT NUMBER, 
 8     Emp_count OUT NUMBER 
 9   ) 
10 IS 
11 BEGIN 
12 SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count  
13 FROM employees WHERE department_id=dept_no; 
14 EXCEPTION 
15 WHEN NO_DATA_FOUND THEN 
16       DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 
17 WHEN OTHERS THEN 
18       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
19 END proc_demo; 
20 --调用方法： 
21 BEGIN 
22     Proc_demo(30, v_sum, v_num); 
23 DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num); 
24     Proc_demo(sal_sum => v_sum, emp_count => v_num); 
25 DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num); 
26 END;

 1 Connect HR/qaz 
 2 DROP TABLE logtable; 
 3 CREATE table logtable (userid VARCHAR2(10), logdate date); 
 4 CREATE OR REPLACE PROCEDURE logexecution  
 5     AUTHID DEFINER 
 6 IS 
 7 BEGIN 
 8 INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE); 
 9 END; 
10 GRANT EXECUTE ON logexecution TO PUBLIC; 
11 CONNECT / AS SYSDBA 
12 GRANT CONNECT TO testuser1 IDENTIFIED BY userpwd1; 
13 CONNECT testuser1/userpwd1 
14 INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE); 
15 EXECUTE HR.logexecution 
16 CONNECT HR/qaz 
17 SELECT * FROM HR.logtable;

 1 CONNECT HR/qaz 
 2 CREATE OR REPLACE PROCEDURE logexecution  
 3   AUTHID CURRENT_USER 
 4 IS 
 5 BEGIN 
 6 INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE); 
 7 END; 
 8 GRANT EXECUTE ON logexecution TO PUBLIC; 
 9 CONNECT testuser1/userpwd1 
10 INSERT INTO HR.LOGTABLE VALUES (USER, SYSDATE); 
11 EXECUTE HR.logexecution

 1 DROP TABLE logtable; 
 2 CREATE TABLE logtable( 
 3   Username varchar2(20), 
 4   Dassate_time date, 
 5   Mege varchar2(60) 
 6 ); 
 7 CREATE TABLE temp_table( N number ); 
 8 CREATE OR REPLACE PROCEDURE log_message(p_message varchar2) 
 9 AS 
10   PRAGMA AUTONOMOUS_TRANSACTION; 
11 BEGIN 
12 INSERT INTO logtable VALUES ( user, sysdate, p_message ); 
13 COMMIT; 
14 END log_message; 
15 BEGIN 
16   Log_message (‘About to insert into temp_table‘); 
17 INSERT INTO temp_table VALUES (1); 
18   Log_message (‘Rollback to insert into temp_table‘); 
19 ROLLBACK; 
20 END; 
21 SELECT * FROM logtable; 
22 SELECT * FROM temp_table;

 1 CREATE OR REPLACE PROCEDURE log_message(p_message varchar2) 
 2 AS 
 3 BEGIN 
 4 INSERT INTO logtable VALUES ( user, sysdate, p_message ); 
 5 COMMIT; 
 6 END log_message; 
 7 BEGIN 
 8   Log_message ('About to insert into temp_table'); 
 9 INSERT INTO temp_table VALUES (1); 
10   Log_message ('Rollback to insert into temp_table'); 
11 ROLLBACK; 
12 END; 
13 SELECT * FROM logtable; 
14 SELECT * FROM temp_table;

1 GRANT system_privilege | role  
2 TO user | role | PUBLIC [WITH ADMIN OPTION] 
3 GRANT object_privilege | ALL ON schema.object  
4 TO user | role | PUBLIC [WITH GRANT OPTION] 
5 --例子： 
6 CREATE OR REPLACE PUBLIC SYNONYM dbms_job FOR dbms_job 
7 GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION

 1 DROP FUNCTION [user.]Function_name; 
 2 --删除上面实例创建的存储过程与函数 
 3 DROP PROCEDURE logexecution; 
 4 DROP PROCEDURE delemp; 
 5 DROP PROCEDURE insertemp; 
 6 DROP PROCEDURE fireemp; 
 7 DROP PROCEDURE queryemp; 
 8 DROP PROCEDURE proc_demo; 
 9 DROP PROCEDURE log_message; 
10 DROP FUNCTION demo_fun; 
11 DROP FUNCTION get_salary;

1 CREATE [OR REPLACE] PACKAGE package_name 
2   [AUTHID {CURRENT_USER | DEFINER}] 
3   {IS | AS} 
4   [公有数据类型定义[公有数据类型定义]…] 
5   [公有游标声明[公有游标声明]…] 
6   [公有变量、常量声明[公有变量、常量声明]…] 
7   [公有函数声明[公有函数声明]…] 
8   [公有过程声明[公有过程声明]…] 
9 END [package_name];

 1 CREATE [OR REPLACE] PACKAGE BODY package_name 
 2   {IS | AS} 
 3   [私有数据类型定义[私有数据类型定义]…] 
 4   [私有变量、常量声明[私有变量、常量声明]…] 
 5   [私有异常错误声明[私有异常错误声明]…] 
 6   [私有函数声明和定义[私有函数声明和定义]…] 
 7   [私有函过程声明和定义[私有函过程声明和定义]…] 
 8   [公有游标定义[公有游标定义]…] 
 9   [公有函数定义[公有函数定义]…] 
10   [公有过程定义[公有过程定义]…] 
11 BEGIN 
12   执行部分(初始化部分) 
13 END package_name;

 1 CREATE OR REPLACE PACKAGE  DEMO_PKG 
 2 IS 
 3   DEPTREC DEPT%ROWTYPE; 
 4   --Add dept... 
 5   FUNCTION add_dept( 
 6            dept_no    NUMBER,  
 7            dept_name VARCHAR2,  
 8            location  VARCHAR2) 
 9   RETURN NUMBER; 
10   --delete dept... 
11   FUNCTION delete_dept(dept_no NUMBER) 
12   RETURN NUMBER; 
13   --query dept... 
14   PROCEDURE query_dept(dept_no IN NUMBER); 
15 END DEMO_PKG;

 1 CREATE OR REPLACE PACKAGE BODY DEMO_PKG 
 2 IS  
 3 FUNCTION add_dept 
 4 ( 
 5    dept_no NUMBER,  
 6    dept_name VARCHAR2,  
 7    location VARCHAR2 
 8 ) 
 9 RETURN NUMBER 
10 IS  
11   empno_remaining EXCEPTION; --自定义异常 
12   PRAGMA EXCEPTION_INIT(empno_remaining, -1); 
13    /* -1 是违反唯一约束条件的错误代码 */ 
14 BEGIN 
15   INSERT INTO dept VALUES(dept_no, dept_name, location); 
16   IF SQL%FOUND THEN 
17      RETURN 1; 
18   END IF; 
19 EXCEPTION 
20      WHEN empno_remaining THEN  
21         RETURN 0; 
22      WHEN OTHERS THEN 
23         RETURN -1; 
24 END add_dept; 
25 FUNCTION delete_dept(dept_no NUMBER) 
26 RETURN NUMBER 
27 IS  
28 BEGIN 
29   DELETE FROM dept WHERE deptno = dept_no; 
30   IF SQL%FOUND THEN 
31     RETURN 1; 
32   ELSE 
33     RETURN 0; 
34    END IF; 
35 EXCEPTION 
36   WHEN OTHERS THEN 
37     RETURN -1; 
38 END delete_dept; 
39 PROCEDURE query_dept 
40 (dept_no IN NUMBER) 
41 IS 
42 BEGIN 
43       SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no; 
44 EXCEPTION 
45        WHEN NO_DATA_FOUND THEN   
46           DBMS_OUTPUT.PUT_LINE('温馨提示:数据库中没有编码为'||dept_no||'的部门'); 
47        WHEN TOO_MANY_ROWS THEN 
48           DBMS_OUTPUT.PUT_LINE('程序运行错误,请使用游标进行操作!'); 
49        WHEN OTHERS THEN 
50            DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM); 
51 END query_dept; 
52 BEGIN  
53     Null; 
54 END DEMO_PKG;

 1 DECLARE 
 2     Var NUMBER; 
 3 BEGIN 
 4     Var := DEMO_PKG.add_dept(90,'HKLORB', 'HAIKOU'); 
 5     IF var =-1 THEN 
 6         DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM); 
 7     ELSIF var =0 THEN 
 8         DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录已经存在！'); 
 9     ELSE 
10         DBMS_OUTPUT.PUT_LINE('温馨提示:添加记录成功！'); 
11         DEMO_PKG.query_dept(90); 
12         DBMS_OUTPUT.PUT_LINE(DEMO_PKG.DeptRec.deptno||'---'|| 
13          DEMO_PKG.DeptRec.dname||'---'||DEMO_PKG.DeptRec.loc); 
14         var := DEMO_PKG.delete_dept(90); 
15         IF var =-1 THEN 
16             DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM); 
17         ELSIF var=0 THEN 
18             DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录不存在！'); 
19         ELSE 
20             DBMS_OUTPUT.PUT_LINE('温馨提示:删除记录成功！'); 
21         END IF; 
22     END IF; 
23 END;

 1 --创建包说明 
 2 CREATE OR REPLACE PACKAGE EMP_PKG  
 3 IS 
 4   TYPE emp_table_type IS TABLE OF emp%ROWTYPE  
 5   INDEX BY BINARY_INTEGER; 
 6   PROCEDURE read_emp_table (p_emp_table OUT emp_table_type); 
 7 END EMP_PKG; 
 8 --创建包体 
 9 CREATE OR REPLACE PACKAGE BODY EMP_PKG  
10 IS 
11 PROCEDURE read_emp_table (p_emp_table OUT emp_table_type)  
12 IS 
13 I BINARY_INTEGER := 0; 
14 BEGIN 
15    FOR emp_record IN ( SELECT * FROM emp ) LOOP 
16       P_emp_table(i) := emp_record; 
17       I := I + 1; 
18     END LOOP; 
19   END read_emp_table; 
20 END EMP_PKG; 
21 --执行 
22 DECLARE  
23   E_table EMP_PKG.emp_table_type; 
24 BEGIN 
25   EMP_PKG.read_emp_table(e_table); 
26   FOR I IN e_table.FIRST ..e_table.LAST LOOP 
27     DBMS_OUTPUT.PUT_LINE(e_table(i).empno||'  '||e_table(i).ename); 
28   END LOOP; 
29 END;

  1 --创建序列从100开始,依次增加1 
  2 CREATE SEQUENCE empseq  
  3 START WITH 100  
  4 INCREMENT BY 1  
  5 ORDER NOCYCLE; 
  6 --创建序列从100开始,依次增加10 
  7 CREATE SEQUENCE deptseq 
  8 START WITH 100 
  9 INCREMENT BY 10  
 10 ORDER NOCYCLE; 
 11 -- ******************************************* 
 12   -- 创建包说明 
 13   -- 包   名：MANAGE_EMP_PKG  
 14   -- 功能描述：对员工进行管理(新增员工,新增部门 
 15   --            ,删除员工,删除部门,增加工资与奖金等) 
 16   -- 创建人员：胡勇 
 17   -- 创建日期：2010-05-19 
 18   -- Q     Q: 80368704 
 19   -- E-mail : [email protected] 
 20   -- WebSite: http://www.cnblogs.com/huyong 
 21 -- ****************************************** 
 22 CREATE OR REPLACE PACKAGE MANAGE_EMP_PKG  
 23 AS 
 24   --增加一名员工      
 25   FUNCTION hire_emp 
 26     (ename VARCHAR2, job VARCHAR2 
 27     , mgr NUMBER, sal NUMBER 
 28     , comm NUMBER, deptno NUMBER) 
 29   RETURN NUMBER; 
 30   --新增一个部门 
 31   FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2) 
 32   RETURN NUMBER; 
 33   --删除指定员工 
 34   PROCEDURE remove_emp(empno NUMBER); 
 35   --删除指定部门 
 36   PROCEDURE remove_dept(deptno NUMBER); 
 37   --增加指定员工的工资 
 38   PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER); 
 39   --增加指定员工的奖金 
 40   PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER); 
 41 END MANAGE_EMP_PKG;--创建包说明结束 
 42 -- ******************************************* 
 43   -- 创建包体 
 44   -- 包   名：MANAGE_EMP_PKG  
 45   -- 功能描述：对员工进行管理(新增员工,新增部门 
 46   --            ,删除员工,删除部门,增加工资与奖金等) 
 47   -- 创建人员：胡勇 
 48   -- 创建日期：2010-05-19 
 49   -- Q     Q: 80368704 
 50   -- E-mail : [email protected] 
 51   -- WebSite: http://www.cnblogs.com/huyong 
 52 -- ****************************************** 
 53 CREATE OR REPLACE PACKAGE BODY MANAGE_EMP_PKG  
 54 AS 
 55     total_emps  NUMBER; --员工数 
 56     total_depts NUMBER; --部门数 
 57     no_sal    EXCEPTION; 
 58     no_comm   EXCEPTION; 
 59   --增加一名员工  
 60   FUNCTION hire_emp(ename VARCHAR2, job VARCHAR2, mgr NUMBER, 
 61                        sal NUMBER, comm NUMBER, deptno NUMBER) 
 62   RETURN NUMBER  --返回新增加的员工编号 
 63   IS 
 64     new_empno NUMBER(4); 
 65   BEGIN 
 66 SELECT empseq.NEXTVAL INTO new_empno FROM dual; 
 67 SELECT COUNT(*) INTO total_emps FROM emp;--当前记录总数 
 68     INSERT INTO emp  
 69     VALUES (new_empno, ename, job, mgr, sysdate, sal, comm, deptno); 
 70     total_emps:=total_emps+1; 
 71   RETURN(new_empno); 
 72   EXCEPTION 
 73      WHEN OTHERS THEN 
 74         DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 
 75   END hire_emp; 
 76   --新增一个部门 
 77   FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2) 
 78   RETURN NUMBER  
 79   IS 
 80     new_deptno NUMBER(4); --部门编号 
 81   BEGIN 
 82     --得到一个新的自增的员工编号 
 83     SELECT deptseq.NEXTVAL INTO new_deptno FROM dual; 
 84     SELECT COUNT(*) INTO total_depts FROM dept;--当前部门总数 
 85     INSERT INTO dept VALUES (new_deptno, dname, loc); 
 86     total_depts:=total_depts; 
 87   RETURN(new_deptno); 
 88   EXCEPTION 
 89      WHEN OTHERS THEN 
 90         DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 
 91   END add_dept; 
 92   --删除指定员工 
 93   PROCEDURE remove_emp(empno NUMBER)  
 94   IS 
 95     no_result EXCEPTION; --自定义异常 
 96   BEGIN  
 97     DELETE FROM emp WHERE emp.empno=remove_emp.empno; 
 98     IF SQL%NOTFOUND THEN 
 99         RAISE no_result; 
100     END IF; 
101     total_emps:=total_emps - 1; --总的员工数减1 
102   EXCEPTION 
103      WHEN no_result THEN  
104         DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 
105      WHEN OTHERS THEN 
106         DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 
107   END remove_emp; 
108   --删除指定部门 
109   PROCEDURE remove_dept(deptno NUMBER)  
110   IS 
111      no_result EXCEPTION; --自定义异常 
112      exception_deptno_remaining EXCEPTION; --自定义异常 
113      /*-2292 是违反一致性约束的错误代码*/ 
114      PRAGMA EXCEPTION_INIT(exception_deptno_remaining, -2292); 
115   BEGIN 
116     DELETE FROM dept WHERE dept.deptno=remove_dept.deptno; 
117     IF SQL%NOTFOUND THEN 
118         RAISE no_result; 
119     END IF; 
120     total_depts:=total_depts-1; --总的部门数减1 
121   EXCEPTION 
122      WHEN no_result THEN  
123         DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 
124      WHEN exception_deptno_remaining THEN  
125         DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!'); 
126      WHEN OTHERS THEN 
127         DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 
128   END remove_dept; 
129   --给指定员工增加指定数量的工资 
130   PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER) 
131   IS 
132     curr_sal NUMBER(7, 2); --当前工资 
133   BEGIN 
134     --得到当前工资 
135     SELECT sal INTO curr_sal FROM emp WHERE emp.empno=increase_sal.empno; 
136     IF curr_sal IS NULL THEN  
137        RAISE no_sal; 
138     ELSE 
139        UPDATE emp SET sal = sal + increase_sal.sal_incr --当前工资加新增的工资  
140        WHERE emp.empno = increase_sal.empno; 
141     END IF; 
142     EXCEPTION 
143        WHEN NO_DATA_FOUND THEN  
144           DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 
145        WHEN no_sal THEN  
146           DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的工资不存在!'); 
147        WHEN OTHERS THEN  
148           DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 
149   END increase_sal; 
150   --给指定员工增加指定数量的奖金 
151   PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER)  
152   IS 
153     curr_comm NUMBER(7,2); 
154   BEGIN  
155     --得到指定员工的当前资金 
156     SELECT comm INTO curr_comm  
157     FROM emp WHERE emp.empno = increase_comm.empno; 
158     IF curr_comm IS NULL THEN  
159        RAISE no_comm; 
160     ELSE 
161       UPDATE emp SET comm = comm + increase_comm.comm_incr 
162       WHERE emp.empno=increase_comm.empno; 
163     END IF; 
164   EXCEPTION 
165      WHEN NO_DATA_FOUND THEN  
166         DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!'); 
167      WHEN no_comm THEN  
168         DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的奖金不存在!'); 
169      WHEN OTHERS THEN  
170         DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!'); 
171   END increase_comm; 
172 END MANAGE_EMP_PKG;--创建包体结束 

  1 -- ******************************************* 
  2   -- 创建包体 
  3   -- 包   名：CURROR_VARIBAL_PKG  
  4   -- 功能描述：在包中引用游标变量 
  5   -- 创建人员：胡勇 
  6   -- 创建日期：2010-05-19 
  7   -- Q     Q: 80368704 
  8   -- E-mail : [email protected] 
  9   -- WebSite: http://www.cnblogs.com/huyong 
 10 -- ****************************************** 
 11 CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS 
 12   TYPE DeptCurType IS REF CURSOR  
 13   RETURN dept%ROWTYPE; --强类型定义 
 14   TYPE CurType IS REF CURSOR;-- 弱类型定义 
 15   PROCEDURE OpenDeptVar( 
 16     Cv IN OUT DeptCurType, 
 17     Choice INTEGER DEFAULT 0, 
 18     Dept_no NUMBER DEFAULT 50, 
 19     Dept_name VARCHAR DEFAULT '%'); 
 20 END; 
 21 -- ******************************************* 
 22   -- 创建包体 
 23   -- 包   名：CURROR_VARIBAL_PKG  
 24   -- 功能描述：在包中引用游标变量 
 25   -- 创建人员：胡勇 
 26   -- 创建日期：2010-05-19 
 27   -- Q     Q: 80368704 
 28   -- E-mail : [email protected] 
 29   -- WebSite: http://www.cnblogs.com/huyong 
 30 -- ****************************************** 
 31 CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG 
 32 AS 
 33   PROCEDURE OpenDeptvar( 
 34     Cv IN OUT DeptCurType, 
 35     Choice INTEGER DEFAULT 0, 
 36     Dept_no NUMBER DEFAULT 50, 
 37     Dept_name VARCHAR DEFAULT ‘%’) 
 38   IS  
 39   BEGIN 
 40     IF choice =1 THEN 
 41       OPEN cv FOR SELECT * FROM dept WHERE deptno <= dept_no; 
 42     ELSIF choice = 2 THEN 
 43       OPEN cv FOR SELECT * FROM dept WHERE dname LIKE dept_name; 
 44     ELSE 
 45       OPEN cv FOR SELECT * FROM dept; 
 46     END IF; 
 47   END OpenDeptvar; 
 48 END CURROR_VARIBAL_PKG; 
 49 --定义一个过程 
 50 CREATE OR REPLACE PROCEDURE UP_OpenCurType( 
 51   Cv IN OUT CURROR_VARIBAL_PKG.CurType, 
 52   FirstCapInTableName CHAR)  
 53 AS 
 54 BEGIN 
 55   --CURROR_VARIBAL_PKG.CurType采用弱类型定义 
 56   --所以可以使用它定义的游标变量打开不同类型的查询语句 
 57   IF FirstCapInTableName = 'D' THEN 
 58     OPEN cv FOR SELECT * FROM dept; 
 59   ELSE 
 60     OPEN cv FOR SELECT * FROM emp; 
 61   END IF; 
 62 END UP_OpenCurType; 
 63 --定义一个应用 
 64 DECLARE  
 65   DeptRec Dept%ROWTYPE; 
 66   EmpRec Emp%ROWTYPE; 
 67   Cv1 CURROR_VARIBAL_PKG.deptcurtype; 
 68   Cv2 CURROR_VARIBAL_PKG.curtype; 
 69 BEGIN 
 70   DBMS_OUTPUT.PUT_LINE('游标变量强类型定义应用'); 
 71   CURROR_VARIBAL_PKG.OpenDeptVar(cv1, 1, 30); 
 72   FETCH cv1 INTO DeptRec; 
 73   WHILE cv1%FOUND LOOP 
 74     DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname); 
 75     FETCH cv1 INTO DeptRec; 
 76   END LOOP; 
 77   CLOSE cv1; 
 78   DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用'); 
 79   CURROR_VARIBAL_PKG.OpenDeptvar(cv2, 2, dept_name => 'A%'); 
 80   FETCH cv2 INTO DeptRec; 
 81   WHILE cv2%FOUND LOOP 
 82     DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname); 
 83     FETCH cv2 INTO DeptRec; 
 84   END LOOP; 
 85   DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—dept表'); 
 86   UP_OpenCurType(cv2, 'D'); 
 87   FETCH cv2 INTO DeptRec; 
 88   WHILE cv2%FOUND LOOP 
 89     DBMS_OUTPUT.PUT_LINE(deptrec.deptno||':'||deptrec.dname); 
 90     FETCH cv2 INTO deptrec; 
 91   END LOOP; 
 92   DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—emp表'); 
 93   UP_OpenCurType(cv2, 'E'); 
 94   FETCH cv2 INTO EmpRec; 
 95   WHILE cv2%FOUND LOOP 
 96     DBMS_OUTPUT.PUT_LINE(emprec.empno||':'||emprec.ename); 
 97     FETCH cv2 INTO emprec; 
 98   END LOOP; 
 99   CLOSE cv2; 
100 END; 

 1 -- ******************************************* 
 2   -- 创建包说明 
 3   -- 包   名：DEMO_PKG1  
 4   -- 功能描述：创建包对子程序重载进行测试 
 5   -- 创建人员：胡勇 
 6   -- 创建日期：2010-05-22 
 7   -- Q     Q: 80368704 
 8   -- E-mail : [email protected] 
 9   -- WebSite: http://www.cnblogs.com/huyong 
10 -- ****************************************** 
11 CREATE OR REPLACE PACKAGE DEMO_PKG1 
12 IS 
13     DeptRec dept%ROWTYPE; 
14     V_sqlcode NUMBER; 
15     V_sqlerr VARCHAR2(2048); 
16   --两个子程序名字相同,但参数类型不同 
17     FUNCTION query_dept(dept_no IN NUMBER) 
18     RETURN INTEGER; 
19     FUNCTION query_dept(dept_no IN VARCHAR2) 
20     RETURN INTEGER; 
21 END DEMO_PKG1; 
22 -- ******************************************* 
23   -- 创建包体 
24   -- 包   名：DEMO_PKG1  
25   -- 功能描述：创建包对子程序重载进行测试 
26   -- 创建人员：胡勇 
27   -- 创建日期：2010-05-22 
28   -- Q     Q: 80368704 
29   -- E-mail : [email protected] 
30   -- WebSite: http://www.cnblogs.com/huyong 
31 -- ****************************************** 
32 CREATE OR REPLACE PACKAGE BODY DEMO_PKG1 
33 IS  
34   FUNCTION check_dept(dept_no NUMBER) 
35   RETURN INTEGER 
36   IS 
37     deptCnt INTEGER; --指定部门号的部门数量 
38   BEGIN 
39     SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno = dept_no; 
40     IF deptCnt > 0 THEN 
41       RETURN 1; 
42     ELSE 
43       RETURN 0; 
44     END IF; 
45   END check_dept; 
46   FUNCTION check_dept(dept_no VARCHAR2) 
47   RETURN INTEGER 
48   IS 
49     deptCnt INTEGER; 
50   BEGIN 
51     SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno=dept_no; 
52     IF deptCnt > 0 THEN 
53       RETURN 1; 
54     ELSE 
55       RETURN 0; 
56     END IF; 
57   END check_dept; 
58   FUNCTION query_dept(dept_no IN NUMBER) 
59   RETURN INTEGER 
60   IS 
61   BEGIN 
62     IF check_dept(dept_no) =1 THEN 
63       SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no; 
64       RETURN 1; 
65     ELSE 
66       RETURN 0; 
67     END IF; 
68   END query_dept; 
69   FUNCTION query_dept(dept_no IN VARCHAR2) 
70     RETURN INTEGER 
71   IS 
72   BEGIN 
73     IF check_dept(dept_no) =1 THEN 
74       SELECT * INTO DeptRec FROM dept WHERE deptno = dept_no; 
75       RETURN 1; 
76     ELSE 
77       RETURN 0; 
78     END IF; 
79   END query_dept; 
80 END DEMO_PKG1;

1 DROP PACKAGE [BODY] [user.]package_name; 
2 DROP PROCEDURE OpenCurType; --删除存储过程 
3 --删除我们实例中创建的各个包 
4 DROP PACKAGE demo_pack; 
5 DROP PACKAGE demo_pack1; 
6 DROP PACKAGE emp_mgmt; 
7 DROP PACKAGE emp_package;

1 CREATE [OR REPLACE] TRIGGER trigger_name 
2 {BEFORE | AFTER } 
3 {INSERT | DELETE | UPDATE [OF column [, column …]]} 
4 [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] 
5 ON [schema.]table_name | [schema.]view_name  
6 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] 
7 [FOR EACH ROW ] 
8 [WHEN condition] 
9 PL/SQL_BLOCK | CALL procedure_name;

 1 CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;  
 2 CREATE OR REPLACE TRIGGER tr_del_emp  
 3    BEFORE DELETE --指定触发时机为删除操作前触发 
 4 ON scott.emp  
 5 FOR EACH ROW   --说明创建的是行级触发器  
 6 BEGIN 
 7 --将修改前数据插入到日志记录表 del_emp ,以供监督使用。 
 8 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) 
 9 VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate ); 
10 END; 
11 DELETE emp WHERE empno=7788; 
12 DROP TABLE emp_his; 
13 DROP TRIGGER del_emp;

1 CREATE OR REPLACE TRIGGER tr_dept_time 
2 BEFORE INSERT OR DELETE OR UPDATE 
3 ON departments 
4 BEGIN 
5 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN 
6      RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表'); 
7 END IF; 
8 END;

 1 CREATE OR REPLACE TRIGGER tr_emp_sal_comm 
 2 BEFORE UPDATE OF salary, commission_pct 
 3 OR DELETE 
 4 ON HR.employees 
 5 FOR EACH ROW 
 6 WHEN (old.department_id = 80) 
 7 BEGIN 
 8 CASE 
 9 WHEN UPDATING ('salary') THEN 
10 IF :NEW.salary < :old.salary THEN 
11            RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降'); 
12 END IF; 
13 WHEN UPDATING ('commission_pct') THEN 
14 IF :NEW.commission_pct < :old.commission_pct THEN 
15            RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降'); 
16 END IF; 
17 WHEN DELETING THEN 
18           RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录'); 
19 END CASE; 
20 END;  
21 /* 
22 实例： 
23 UPDATE employees SET salary = 8000 WHERE employee_id = 177; 
24 DELETE FROM employees WHERE employee_id in (177,170); 
25 */

 1 CREATE OR REPLACE TRIGGER tr_reg_cou 
 2 AFTER update OF region_id 
 3 ON regions 
 4 FOR EACH ROW 
 5 BEGIN 
 6 DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id 
 7 ||'、新的region_id值是'||:new.region_id); 
 8 UPDATE countries SET region_id = :new.region_id 
 9 WHERE region_id = :old.region_id; 
10 END;

 1 CREATE OR REPLACE PROCEDURE add_job_history 
 2 ( p_emp_id          job_history.employee_id%type 
 3    , p_start_date      job_history.start_date%type 
 4   , p_end_date        job_history.end_date%type 
 5    , p_job_id          job_history.job_id%type 
 6    , p_department_id   job_history.department_id%type 
 7    ) 
 8 IS 
 9 BEGIN 
10 INSERT INTO job_history (employee_id, start_date, end_date, 
11                            job_id, department_id) 
12 VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); 
13 END add_job_history; 
14 --创建触发器调用存储过程... 
15 CREATE OR REPLACE TRIGGER update_job_history 
16 AFTER UPDATE OF job_id, department_id ON employees 
17 FOR EACH ROW 
18 BEGIN 
19 add_job_history(:old.employee_id, :old.hire_date, sysdate, 
20                   :old.job_id, :old.department_id); 
21 END;

1 CREATE [OR REPLACE] TRIGGER trigger_name 
2 INSTEAD OF 
3 {INSERT | DELETE | UPDATE [OF column [, column …]]} 
4 [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] 
5 ON [schema.] view_name --只能定义在视图上 
6 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}] 
7 [FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定 
8 [WHEN condition] 
9 PL/SQL_block | CALL procedure_name;

1 CREATE OR REPLACE VIEW emp_view AS 
2 SELECT deptno, count(*) total_employeer, sum(sal) total_salary  
3 FROM emp GROUP BY deptno;

1 CREATE OR REPLACE TRIGGER emp_view_delete 
2    INSTEAD OF DELETE ON emp_view FOR EACH ROW 
3 BEGIN 
4 DELETE FROM emp WHERE deptno= :old.deptno; 
5 END emp_view_delete;  
6 DELETE FROM emp_view WHERE deptno=10;  
7 DROP TRIGGER emp_view_delete; 
8 DROP VIEW emp_view;

1 CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME") 
2 AS 
3 SELECT r.region_id, 
4     r.region_name, 
5     c.country_id, 
6     c.country_name 
7 FROM regions r, 
8     countries c 
9 WHERE r.region_id = c.region_id;

 1 CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" INSTEAD OF 
 2 INSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER; 
 3 BEGIN 
 4 SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id; 
 5 IF v_count = 0 THEN 
 6 INSERT INTO regions 
 7       (region_id, region_name 
 8       ) VALUES 
 9       (:new.r_id, :new.r_name 
10       ); 
11 END IF; 
12 SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id; 
13 IF v_count = 0 THEN 
14 INSERT 
15 INTO countries 
16       ( 
17         country_id, 
18         country_name, 
19         region_id 
20       ) 
21 VALUES 
22       ( 
23         :new.c_id, 
24         :new.c_name, 
25         :new.r_id 
26       ); 
27 END IF; 
28 END;

1 CREATE OR REPLACE TRIGGER [sachema.]trigger_name 
2 {BEFORE|AFTER}  
3 {ddl_event_list | database_event_list} 
4 ON { DATABASE | [schema.]SCHEMA } 
5 [WHEN condition] 
6 PL/SQL_block | CALL procedure_name;

 1 DESC ora_sysevent 
 2 DESC ora_login_user 
 3 --创建用于记录事件用的表 
 4 CREATE TABLE ddl_event 
 5 (crt_date timestamp PRIMARY KEY, 
 6 event_name VARCHAR2(20),  
 7 user_name VARCHAR2(10), 
 8 obj_type VARCHAR2(20), 
 9 obj_name VARCHAR2(20)); 
10 --创建触犯发器 
11 CREATE OR REPLACE TRIGGER tr_ddl 
12 AFTER DDL ON SCHEMA 
13 BEGIN 
14 INSERT INTO ddl_event VALUES 
15    (systimestamp,ora_sysevent, ora_login_user,  
16     ora_dict_obj_type, ora_dict_obj_name); 
17 END tr_ddl;

 1 CREATE TABLE log_event 
 2 (user_name VARCHAR2(10), 
 3 address VARCHAR2(20),  
 4 logon_date timestamp, 
 5 logoff_date timestamp);  
 6 --创建登录触发器 
 7 CREATE OR REPLACE TRIGGER tr_logon 
 8 AFTER LOGON ON DATABASE 
 9 BEGIN 
10 INSERT INTO log_event (user_name, address, logon_date) 
11 VALUES (ora_login_user, ora_client_ip_address, systimestamp); 
12 END tr_logon; 
13 --创建退出触发器 
14 CREATE OR REPLACE TRIGGER tr_logoff 
15 BEFORE LOGOFF ON DATABASE 
16 BEGIN 
17 INSERT INTO log_event (user_name, address, logoff_date) 
18 VALUES (ora_login_user, ora_client_ip_address, systimestamp); 
19 END tr_logoff;

 1 CREATE TABLE dept_summary( 
 2 Deptno NUMBER(2), 
 3 Sal_sum NUMBER(9, 2), 
 4 Emp_count NUMBER);  
 5 INSERT INTO dept_summary(deptno, sal_sum, emp_count) 
 6 SELECT deptno, SUM(sal), COUNT(*)  
 7 FROM emp  
 8 GROUP BY deptno; 
 9 --创建一个PL/SQL过程disp_dept_summary 
10 --在触发器中调用该过程显示dept_summary标中的数据。 
11 CREATE OR REPLACE PROCEDURE disp_dept_summary 
12 IS 
13 Rec dept_summary%ROWTYPE; 
14 CURSOR c1 IS SELECT * FROM dept_summary; 
15 BEGIN 
16 OPEN c1; 
17 FETCH c1 INTO REC; 
18 DBMS_OUTPUT.PUT_LINE('deptno    sal_sum    emp_count'); 
19 DBMS_OUTPUT.PUT_LINE('-------------------------------------'); 
20 WHILE c1%FOUND LOOP 
21     DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)|| 
22       To_char(rec.sal_sum, '$999,999.99')|| 
23       LPAD(rec.emp_count, 13)); 
24 FETCH c1 INTO rec; 
25 END LOOP; 
26 CLOSE c1; 
27 END; 
28 BEGIN 
29 DBMS_OUTPUT.PUT_LINE('插入前'); 
30 Disp_dept_summary(); 
31 DBMS_UTILITY.EXEC_DDL_STATEMENT(' 
32     CREATE OR REPLACE TRIGGER trig1 
33       AFTER INSERT OR DELETE OR UPDATE OF sal ON emp 
34     BEGIN 
35       DBMS_OUTPUT.PUT_LINE(''正在执行trig1 触发器…''); 
36       DELETE FROM dept_summary; 
37       INSERT INTO dept_summary(deptno, sal_sum, emp_count) 
38       SELECT deptno, SUM(sal), COUNT(*)  
39       FROM emp GROUP BY deptno; 
40     END; 
41 '); 
42 INSERT INTO dept(deptno, dname, loc)  
43 VALUES(90, ‘demo_dept’, ‘none_loc’); 
44 INSERT INTO emp(ename, deptno, empno, sal) 
45 VALUES(USER, 90, 9999, 3000); 
46 DBMS_OUTPUT.PUT_LINE('插入后'); 
47 Disp_dept_summary(); 
48 UPDATE emp SET sal=1000 WHERE empno=9999; 
49 DBMS_OUTPUT.PUT_LINE('修改后'); 
50 Disp_dept_summary(); 
51 DELETE FROM emp WHERE empno=9999; 
52 DELETE FROM dept WHERE deptno=90; 
53 DBMS_OUTPUT.PUT_LINE('删除后'); 
54 Disp_dept_summary();  
55 DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’); 
56 EXCEPTION 
57 WHEN OTHERS THEN 
58       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
59 END;

 1 BEGIN 
 2   DBMS_OUTPUT.PUT_LINE('插入前'); 
 3   Disp_dept_summary(); 
 4   DBMS_UTILITY.EXEC_DDL_STATEMENT( 
 5 'CREATE OR REPLACE TRIGGER trig2_update 
 6       AFTER UPDATE OF sal ON emp 
 7       REFERENCING OLD AS old_emp NEW AS new_emp 
 8       FOR EACH ROW 
 9       WHEN (old_emp.sal != new_emp.sal) 
10     BEGIN 
11       DBMS_OUTPUT.PUT_LINE(''正在执行trig2_update 触发器…''); 
12       DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); 
13       DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); 
14       UPDATE dept_summary 
15         SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal 
16         WHERE deptno = :new_emp.deptno; 
17     END;' 
18   ); 
19   DBMS_UTILITY.EXEC_DDL_STATEMENT( 
20 'CREATE OR REPLACE TRIGGER trig2_insert 
21       AFTER INSERT ON emp 
22       REFERENCING NEW AS new_emp 
23       FOR EACH ROW 
24     DECLARE 
25       I NUMBER; 
26     BEGIN 
27       DBMS_OUTPUT.PUT_LINE(''正在执行trig2_insert 触发器…''); 
28       SELECT COUNT(*) INTO I  
29       FROM dept_summary WHERE deptno = :new_emp.deptno; 
30       IF I > 0 THEN 
31         UPDATE dept_summary  
32         SET sal_sum=sal_sum+:new_emp.sal, 
33         Emp_count=emp_count+1 
34         WHERE deptno = :new_emp.deptno; 
35       ELSE 
36         INSERT INTO dept_summary 
37         VALUES (:new_emp.deptno, :new_emp.sal, 1); 
38       END IF; 
39     END;' 
40   ); 
41   DBMS_UTILITY.EXEC_DDL_STATEMENT( 
42 'CREATE OR REPLACE TRIGGER trig2_delete 
43       AFTER DELETE ON emp 
44       REFERENCING OLD AS old_emp 
45       FOR EACH ROW 
46     DECLARE 
47       I NUMBER; 
48     BEGIN 
49       DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…''); 
50       SELECT emp_count INTO I  
51       FROM dept_summary WHERE deptno = :old_emp.deptno; 
52       IF I >1 THEN 
53         UPDATE dept_summary  
54         SET sal_sum=sal_sum - :old_emp.sal, 
55         Emp_count=emp_count - 1 
56         WHERE deptno = :old_emp.deptno; 
57       ELSE 
58         DELETE FROM dept_summary WHERE deptno = :old_emp.deptno; 
59       END IF; 
60     END;' 
61   ); 
62 INSERT INTO dept(deptno, dname, loc)  
63 VALUES(90, 'demo_dept', 'none_loc'); 
64 INSERT INTO emp(ename, deptno, empno, sal) 
65 VALUES(USER, 90, 9999, 3000); 
66 INSERT INTO emp(ename, deptno, empno, sal) 
67 VALUES(USER, 90, 9998, 2000); 
68   DBMS_OUTPUT.PUT_LINE('插入后'); 
69   Disp_dept_summary(); 
70 UPDATE emp SET sal = sal*1.1 WHERE deptno=90; 
71   DBMS_OUTPUT.PUT_LINE('修改后'); 
72   Disp_dept_summary(); 
73 DELETE FROM emp WHERE deptno=90; 
74 DELETE FROM dept WHERE deptno=90; 
75   DBMS_OUTPUT.PUT_LINE('删除后'); 
76   Disp_dept_summary(); 
77   DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update'); 
78   DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert'); 
79   DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete'); 
80 EXCEPTION 
81 WHEN OTHERS THEN 
82       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
83 END;

 1 BEGIN 
 2     DBMS_OUTPUT.PUT_LINE('插入前'); 
 3     Disp_dept_summary(); 
 4     DBMS_UTILITY.EXEC_DDL_STATEMENT( 
 5 'CREATE OR REPLACE TRIGGER trig2 
 6             AFTER INSERT OR DELETE OR UPDATE OF sal 
 7 ON emp 
 8             REFERENCING OLD AS old_emp NEW AS new_emp 
 9             FOR EACH ROW 
10         DECLARE 
11             I NUMBER; 
12         BEGIN 
13             IF UPDATING AND :old_emp.sal != :new_emp.sal THEN 
14             DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…''); 
15                 DBMS_OUTPUT.PUT_LINE(''sal 旧值：''|| :old_emp.sal); 
16                 DBMS_OUTPUT.PUT_LINE(''sal 新值：''|| :new_emp.sal); 
17                 UPDATE dept_summary 
18                     SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal 
19                 WHERE deptno = :new_emp.deptno; 
20             ELSIF INSERTING THEN 
21                 DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); 
22                 SELECT COUNT(*) INTO I  
23         FROM dept_summary  
24         WHERE deptno = :new_emp.deptno; 
25                 IF I > 0 THEN 
26                     UPDATE dept_summary  
27           SET sal_sum=sal_sum+:new_emp.sal, 
28               Emp_count=emp_count+1 
29           WHERE deptno = :new_emp.deptno; 
30             ELSE 
31           INSERT INTO dept_summary 
32             VALUES (:new_emp.deptno, :new_emp.sal, 1); 
33         END IF; 
34       ELSE 
35         DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…''); 
36         SELECT emp_count INTO I  
37         FROM dept_summary WHERE deptno = :old_emp.deptno; 
38       IF I > 1 THEN 
39         UPDATE dept_summary  
40         SET sal_sum=sal_sum - :old_emp.sal, 
41         Emp_count=emp_count - 1 
42         WHERE deptno = :old_emp.deptno; 
43       ELSE 
44           DELETE FROM dept_summary  
45           WHERE deptno = :old_emp.deptno; 
46       END IF; 
47     END IF; 
48     END;' 
49   ); 
50 INSERT INTO dept(deptno, dname, loc)  
51 VALUES(90, 'demo_dept', 'none_loc'); 
52 INSERT INTO emp(ename, deptno, empno, sal) 
53 VALUES(USER, 90, 9999, 3000); 
54 INSERT INTO emp(ename, deptno, empno, sal) 
55 VALUES(USER, 90, 9998, 2000); 
56   DBMS_OUTPUT.PUT_LINE('插入后'); 
57   Disp_dept_summary(); 
58 UPDATE emp SET sal = sal*1.1 WHERE deptno=90; 
59   DBMS_OUTPUT.PUT_LINE('修改后'); 
60   Disp_dept_summary(); 
61 DELETE FROM emp WHERE deptno=90; 
62 DELETE FROM dept WHERE deptno=90; 
63   DBMS_OUTPUT.PUT_LINE('删除后'); 
64   Disp_dept_summary(); 
65   DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2'); 
66 EXCEPTION 
67 WHEN OTHERS THEN 
68       DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM); 
69 END;

 1 DECLARE 
 2     No NUMBER; 
 3     Name VARCHAR2(20); 
 4 BEGIN 
 5     DBMS_UTILITY.EXEC_DDL_STATEMENT(' 
 6         CREATE OR REPLACE VIEW myview AS 
 7             SELECT empno, ename, ''E'' type FROM emp 
 8             UNION 
 9             SELECT dept.deptno, dname, ''D'' FROM dept 
10 '); 
11 -- 创建INSTEAD OF 触发器trigger3; 
12     DBMS_UTILITY.EXEC_DDL_STATEMENT(' 
13         CREATE OR REPLACE TRIGGER trig3 
14             INSTEAD OF INSERT ON myview 
15             REFERENCING NEW n 
16             FOR EACH ROW 
17         DECLARE 
18             Rows INTEGER; 
19         BEGIN 
20             DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…''); 
21             IF :n.type = ''D'' THEN 
22                 SELECT COUNT(*) INTO rows 
23                     FROM dept WHERE deptno = :n.empno; 
24                 IF rows = 0 THEN 
25                     DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…''); 
26                     INSERT INTO dept(deptno, dname, loc) 
27                         VALUES (:n.empno, :n.ename, ''none’’); 
28                 ELSE 
29                     DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| 
30 ''的部门已存在，插入操作失败！''); 
31                  END IF; 
32             ELSE 
33                 SELECT COUNT(*) INTO rows 
34                     FROM emp WHERE empno = :n.empno; 
35                 IF rows = 0 THEN 
36                     DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’); 
37 INSERT INTO emp(empno, ename) 
38 VALUES(:n.empno, :n.ename); 
39 ELSE 
40                     DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno|| 
41 ''的人员已存在，插入操作失败!''); 
42 END IF; 
43 END IF; 
44 END; 
45 '); 
46     INSERT INTO myview VALUES (70, 'demo', 'D'); 
47     INSERT INTO myview VALUES (9999, USER, 'E'); 
48     SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70; 
49     DBMS_OUTPUT.PUT_LINE('员工编号：'||TO_CHAR(no)||'姓名：'||name); 
50     SELECT empno, ename INTO no, name FROM emp WHERE empno=9999; 
51     DBMS_OUTPUT.PUT_LINE('部门编号：'||TO_CHAR(no)||'姓名：'||name); 
52   DELETE FROM emp WHERE empno=9999; 
53   DELETE FROM dept WHERE deptno=70; 
54     DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3'); 
55 END;

  1 BEGIN 
  2 -- 创建用于记录事件日志的数据表 
  3     DBMS_UTILITY.EXEC_DDL_STATEMENT(' 
  4         CREATE TABLE eventlog( 
  5             Eventname VARCHAR2(20) NOT NULL, 
  6             Eventdate date default sysdate, 
  7             Inst_num NUMBER NULL, 
  8             Db_name VARCHAR2(50) NULL, 
  9             Srv_error NUMBER NULL, 
 10             Username VARCHAR2(30) NULL, 
 11             Obj_type VARCHAR2(20) NULL, 
 12             Obj_name VARCHAR2(30) NULL, 
 13             Obj_owner VARCHAR2(30) NULL 
 14         ) 
 15 '); 
 16 -- 创建DDL触发器trig4_ddl 
 17     DBMS_UTILITY.EXEC_DDL_STATEMENT(' 
 18         CREATE OR REPLACE TRIGGER trig4_ddl 
 19             AFTER CREATE OR ALTER OR DROP  
 20 ON DATABASE 
 21         DECLARE 
 22             Event VARCHAR2(20); 
 23             Typ VARCHAR2(20); 
 24             Name VARCHAR2(30); 
 25             Owner VARCHAR2(30); 
 26         BEGIN 
 27             -- 读取DDL事件属性 
 28             Event := SYSEVENT; 
 29             Typ := DICTIONARY_OBJ_TYPE; 
 30             Name := DICTIONARY_OBJ_NAME; 
 31             Owner := DICTIONARY_OBJ_OWNER; 
 32             --将事件属性插入到事件日志表中 
 33             INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner) 
 34                 VALUES(event, typ, name, owner); 
 35         END; 
 36 '); 
 37 -- 创建LOGON、STARTUP和SERVERERROR 事件触发器 
 38     DBMS_UTILITY.EXEC_DDL_STATEMENT(' 
 39         CREATE OR REPLACE TRIGGER trig4_after 
 40             AFTER LOGON OR STARTUP OR SERVERERROR  
 41       ON DATABASE 
 42         DECLARE 
 43             Event VARCHAR2(20); 
 44             Instance NUMBER; 
 45             Err_num NUMBER; 
 46             Dbname VARCHAR2(50); 
 47             User VARCHAR2(30); 
 48         BEGIN 
 49             Event := SYSEVENT; 
 50             IF event = ''LOGON'' THEN 
 51                 User := LOGIN_USER; 
 52                 INSERT INTO eventlog(eventname, username) 
 53                     VALUES(event, user); 
 54             ELSIF event = ''SERVERERROR'' THEN 
 55                 Err_num := SERVER_ERROR(1); 
 56                 INSERT INTO eventlog(eventname, srv_error) 
 57                     VALUES(event, err_num); 
 58             ELSE 
 59                 Instance := INSTANCE_NUM; 
 60                 Dbname := DATABASE_NAME; 
 61                 INSERT INTO eventlog(eventname, inst_num, db_name) 
 62                     VALUES(event, instance, dbname); 
 63       END IF; 
 64     END; 
 65 '); 
 66 -- 创建LOGOFF和SHUTDOWN 事件触发器 
 67   DBMS_UTILITY.EXEC_DDL_STATEMENT(' 
 68     CREATE OR REPLACE TRIGGER trig4_before 
 69       BEFORE LOGOFF OR SHUTDOWN  
 70       ON DATABASE 
 71     DECLARE 
 72       Event VARCHAR2(20); 
 73       Instance NUMBER; 
 74       Dbname VARCHAR2(50); 
 75       User VARCHAR2(30); 
 76     BEGIN 
 77       Event := SYSEVENT; 
 78       IF event = ''LOGOFF'' THEN 
 79         User := LOGIN_USER; 
 80         INSERT INTO eventlog(eventname, username) 
 81           VALUES(event, user); 
 82       ELSE 
 83         Instance := INSTANCE_NUM; 
 84         Dbname := DATABASE_NAME; 
 85         INSERT INTO eventlog(eventname, inst_num, db_name) 
 86           VALUES(event, instance, dbname); 
 87       END IF; 
 88     END; 
 89 '); 
 90 END; 
 91 CREATE TABLE mydata(mydate NUMBER); 
 92 CONNECT SCOTT/TIGER 
 93 COL eventname FORMAT A10 
 94 COL eventdate FORMAT A12 
 95 COL username FORMAT A10 
 96 COL obj_type FORMAT A15 
 97 COL obj_name FORMAT A15 
 98 COL obj_owner FORMAT A10 
 99 SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error 
100 FROM eventlog; 
101 DROP TRIGGER trig4_ddl; 
102 DROP TRIGGER trig4_before; 
103 DROP TRIGGER trig4_after; 
104 DROP TABLE eventlog; 
105 DROP TABLE mydata;

 1 CREATE TABLE audit_table( 
 2     Audit_id     NUMBER, 
 3 User_name VARCHAR2(20), 
 4     Now_time DATE, 
 5     Terminal_name VARCHAR2(10), 
 6     Table_name VARCHAR2(10), 
 7     Action_name VARCHAR2(10), 
 8     Emp_id NUMBER(4)); 
 9 CREATE TABLE audit_table_val( 
10     Audit_id NUMBER, 
11     Column_name VARCHAR2(10), 
12     Old_val NUMBER(7,2), 
13     New_val NUMBER(7,2)); 
14 CREATE SEQUENCE audit_seq 
15     START WITH 1000 
16     INCREMENT BY 1 
17     NOMAXVALUE 
18     NOCYCLE NOCACHE; 
19 CREATE OR REPLACE TRIGGER audit_emp 
20     AFTER INSERT OR UPDATE OR DELETE ON emp 
21 FOR EACH ROW 
22 DECLARE 
23     Time_now DATE; 
24     Terminal CHAR(10); 
25 BEGIN 
26     Time_now:=sysdate; 
27     Terminal:=USERENV('TERMINAL'); 
28 IF INSERTING THEN 
29 INSERT INTO audit_table 
30 VALUES(audit_seq.NEXTVAL, user, time_now,  
31            terminal, 'EMP', 'INSERT', :new.empno); 
32     ELSIF DELETING THEN 
33 INSERT INTO audit_table 
34 VALUES(audit_seq.NEXTVAL, user, time_now,  
35            terminal, 'EMP', 'DELETE', :old.empno); 
36 ELSE 
37 INSERT INTO audit_table 
38 VALUES(audit_seq.NEXTVAL, user, time_now,  
39            terminal, 'EMP', 'UPDATE', :old.empno); 
40 IF UPDATING('SAL') THEN 
41 INSERT INTO audit_table_val 
42 VALUES(audit_seq.CURRVAL, 'SAL', :old.sal, :new.sal); 
43 ELSE UPDATING('DEPTNO')  
44 INSERT INTO audit_table_val 
45 VALUES(audit_seq.CURRVAL, 'DEPTNO', :old.deptno, :new.deptno); 
46 END IF; 
47 END IF; 
48 END;

 1 CREATE SEQUENCE update_sequence  
 2     INCREMENT BY 1 
 3     START WITH 1000 
 4     MAXVALUE 5000 CYCLE; 
 5 ALTER TABLE emp 
 6 ADD update_id NUMBER; 
 7 CREATE OR REPLACE PACKAGE integritypackage AS 
 8     Updateseq NUMBER; 
 9 END integritypackage; 
10 CREATE OR REPLACE PACKAGE BODY integritypackage AS 
11 END integritypackage; 
12 CREATE OR REPLACE TRIGGER dept_cascade1 
13     BEFORE UPDATE OF deptno ON dept 
14 DECLARE 
15 Dummy NUMBER; 
16 BEGIN 
17 SELECT update_sequence.NEXTVAL INTO dummy FROM dual; 
18     Integritypackage.updateseq:=dummy; 
19 END; 
20 CREATE OR REPLACE TRIGGER dept_cascade2 
21     AFTER DELETE OR UPDATE OF deptno ON dept 
22 FOR EACH ROW 
23 BEGIN 
24 IF UPDATING THEN 
25 UPDATE emp SET deptno=:new.deptno,  
26      update_id=integritypackage.updateseq 
27 WHERE emp.deptno=:old.deptno AND update_id IS NULL; 
28 END IF; 
29 IF DELETING THEN 
30 DELETE FROM emp 
31 WHERE emp.deptno=:old.deptno; 
32 END IF; 
33 END; 
34 CREATE OR REPLACE TRIGGER dept_cascade3 
35     AFTER UPDATE OF deptno ON dept  
36 BEGIN 
37 UPDATE emp SET update_id=NULL 
38 WHERE update_id=integritypackage.updateseq; 
39 END; 
40 SELECT * FROM EMP ORDER BY DEPTNO; 
41 UPDATE dept SET deptno=25 WHERE deptno=20;

 1 CREATE TABLE company_holidays(day DATE); 
 2 INSERT INTO company_holidays  
 3 VALUES(sysdate); 
 4 INSERT INTO company_holidays  
 5 VALUES(TO_DATE('21-10月-01', 'DD-MON-YY')); 
 6 CREATE OR REPLACE TRIGGER emp_permit_change 
 7     BEFORE INSERT OR DELETE OR UPDATE ON emp 
 8 DECLARE 
 9 Dummy NUMBER; 
10     Not_on_weekends EXCEPTION; 
11     Not_on_holidays EXCEPTION; 
12     Not_working_hours EXCEPTION; 
13 BEGIN 
14 /* check for weekends */ 
15 IF TO_CHAR(SYSDATE, 'DAY') IN ('星期六', '星期日') THEN 
16     RAISE not_on_weekends; 
17 END IF; 
18 /* check for company holidays */ 
19 SELECT COUNT(*) INTO dummy FROM company_holidays 
20 WHERE TRUNC(day)=TRUNC(SYSDATE); 
21 IF dummy >0 THEN 
22     RAISE not_on_holidays; 
23 END IF; 
24 /* check for work hours(8:00 AM to 18:00 PM */ 
25 IF (TO_CHAR(SYSDATE,'HH24')<8 OR TO_CHAR(SYSDATE, 'HH24')>18) THEN 
26   RAISE not_working_hours; 
27 END IF; 
28 EXCEPTION 
29 WHEN not_on_weekends THEN 
30     RAISE_APPLICATION_ERROR(-20324,  
31 'May not change employee table during the weekends');  
32 WHEN not_on_holidays THEN 
33     RAISE_APPLICATION_ERROR(-20325,  
34 'May not change employee table during a holiday');  
35 WHEN not_working_hours THEN 
36     RAISE_APPLICATION_ERROR(-20326,  
37 'May not change employee table during no_working hours');  
38 END;