plSql引见

/* * PL/SQL 程序设计学习笔记 * 学习plSql介绍.pdf * 时间：2010-10-05*/--创建DEPT表create table DEPT(  DEPTNO NUMBER(10),  DNAME  NVARCHAR2(255),  LOC    NVARCHAR2(255))delete dept;select * from dept;--插入一条记录并显示DECLARE       Row_id UROWID;       info VARCHAR2(40);BEGIN     INSERT INTO dept VALUES (90, 'SERVICE', 'BEIJING')     RETURNING rowid, dname||':'||to_char(deptno)||':'||loc     INTO row_id, info;     DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);     DBMS_OUTPUT.PUT_LINE(info);END;--修改一条记录并显示DECLARE       Row_id UROWID;       info VARCHAR2(40);BEGIN     UPDATE dept SET deptno=80 WHERE DNAME='SERVICE'     RETURNING rowid, dname||':'||to_char(deptno)||':'||loc     INTO row_id, info;     DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);     DBMS_OUTPUT.PUT_LINE(info);END;--删除一条记录并显示DECLARE       Row_id UROWID;       info VARCHAR2(40);BEGIN     DELETE dept WHERE DNAME='SERVICE'     RETURNING rowid, dname||':'||to_char(deptno)||':'||loc     INTO row_id, info;     DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);     DBMS_OUTPUT.PUT_LINE(info);END;--复合类型-记录类型DECLARE       TYPE test_rec IS RECORD(       Code VARCHAR2(10),       Name VARCHAR2(30) NOT NULL :='a book');       V_book test_rec;BEGIN     V_book.code :='123';     V_book.name :='C++ Programming';     DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.name);END;--复合类型-使用%TYPE--创建emp表create table emp(  empno NUMBER(10),  ename  NVARCHAR2(255),  sal    NUMBER(10,2),  hiredate Timestamp,  comm NUMBER(10,2),  job NVARCHAR2(255),  deptno NUMBER(10))insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(100,'Test',30.5,sysdate,10,'president',1);insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(7788,'Test2',100.23,sysdate,10.2,'manager',2);--drop table emp;select * from emp;DECLARE--用%TYPE 类型定义与表相配的字段          TYPE t_Record IS RECORD(          T_no emp.empno%TYPE,          T_name emp.ename%TYPE,          T_sal emp.sal%TYPE );          --声明接收数据的变量          v_emp t_Record;BEGIN     SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788;     DBMS_OUTPUT.PUT_LINE     (TO_CHAR(v_emp.t_no)||v_emp.t_name||TO_CHAR(v_emp.t_sal));END;DECLARE       v_empno emp.empno%TYPE :=&empno;       Type r_record is record (       v_name emp.ename%TYPE,       v_sal emp.sal%TYPE,       v_date emp.hiredate%TYPE);       Rec r_record;BEGIN     SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno;     DBMS_OUTPUT.PUT_LINE(Rec.v_name||'--'||Rec.v_sal||'--'||Rec.v_date);END;--使用%ROWTYPEDECLARE       v_empno emp.empno%TYPE :=&empno;       rec emp%ROWTYPE;BEGIN     SELECT * INTO rec FROM emp WHERE empno=v_empno;     DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);END;--以下脚本只能在sqlplus命令行中执行----start-----VARIABLE result2 NUMBER         BEGIN              SELECT (sal*12)+nvl(comm, 0) INTO :result2 FROM emp WHERE empno=7788;         END;PRINT result2;-----end-------INDEX BY TABLESselect * from dept;insert into dept(deptno,dname,loc) values(1,'Test1','BEIJING');insert into dept(deptno,dname,loc) values(10,'Test10','SHENGZHEN');insert into dept(deptno,dname,loc) values(20,'Test20','CHANGSHA');insert into dept(deptno,dname,loc) values(30,'Test30','ZHUHAI');insert into dept(deptno,dname,loc) values(40,'Test40','SHAOYANG');DECLARE       TYPE dept_table_type IS TABLE OF       dept%ROWTYPE INDEX BY BINARY_INTEGER;       my_dname_table dept_table_type;       v_count number(2) :=4;BEGIN     FOR i IN 1 .. v_count LOOP         SELECT * INTO my_dname_table(i) FROM dept WHERE deptno=i*10;     END LOOP;     FOR i IN my_dname_table.FIRST.. my_dname_table.LAST LOOP         DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(i).deptno);         DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(i).dname);     END LOOP;END;--BOOLEAN 赋值DECLARE       done BOOLEAN;       /* the following statements are legal: */BEGIN     done := FALSE;     WHILE NOT done LOOP           Null;     END LOOP;END;SELECT * FROM EMP--数据库赋值DECLARE       emp_id emp.empno%TYPE :=7788;       emp_name emp.ename%TYPE;       wages emp.sal%TYPE;BEGIN     SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages     FROM emp WHERE empno = emp_id;     Dbms_output.put_line(emp_name||'----'||to_char(wages));END;SELECT * FROM emp;insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(8,'Test3',10.23,sysdate,1.2,'manager',3);insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(12,'Test4',10.23,sysdate,1.2,'president',4);delete emp where empno = 8;delete emp where empno = 12;--变量作用范围及可见性DECLARE       Emess char(80);BEGIN     DECLARE     V1 NUMBER(4);     BEGIN          SELECT empno INTO v1 FROM emp WHERE LOWER(job)='president';          DBMS_OUTPUT.PUT_LINE(V1);     EXCEPTION     When TOO_MANY_ROWS THEN          DBMS_OUTPUT.PUT_LINE ('More than one president');     END;     DECLARE     V1 NUMBER(4);     BEGIN          SELECT empno INTO v1 FROM emp WHERE LOWER(job)='manager';          DBMS_OUTPUT.PUT_LINE(V1);     EXCEPTION     When TOO_MANY_ROWS THEN          DBMS_OUTPUT.PUT_LINE ('More than one manager');     END;EXCEPTIONWhen others THEN     Emess:=substr(SQLERRM,1,80);     DBMS_OUTPUT.PUT_LINE (emess);END;select * from emp;/* 本例子仅是一个简单的插入，不是实际应用。*/DECLARE       v_ename VARCHAR2(20) := 'Bill';       v_sal NUMBER(7,2) :=1234.56;       v_deptno NUMBER(2) := 10;       v_empno NUMBER(4) := 8888;BEGIN     INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )     VALUES ( v_empno, v_ename, 'Manager', v_sal, v_deptno,TO_DATE('1954.06.09','yyyy.mm.dd') );     COMMIT;END;/* 本例子仅是一个简单的删除例子，不是实际应用。*/DECLARE       v_empno number(4) := 8888;BEGIN     DELETE FROM emp WHERE empno=v_empno;     COMMIT;END;select * from emp;--条件语句DECLARE       v_empno emp.empno%TYPE :=&empno;       V_salary emp.sal%TYPE;       V_comment VARCHAR2(35);BEGIN     SELECT sal INTO v_salary FROM emp WHERE empno=v_empno;     IF v_salary<1500 THEN        V_comment:= 'Fairly less';     ELSIF v_salary <3000 THEN        V_comment:= 'A little more';     ELSE        V_comment:= 'Lots of salary';     END IF;     DBMS_OUTPUT.PUT_LINE(V_comment);END;--CASE 表达式DECLARE       V_grade char(1) := UPPER('&p_grade');       V_appraisal VARCHAR2(20);BEGIN     V_appraisal :=     CASE v_grade     WHEN 'A' THEN 'Excellent'     WHEN 'B' THEN 'Very Good'     WHEN 'C' THEN 'Good'     ELSE 'No such grade'     END;     DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||' Appraisal: '|| v_appraisal);END;--简单循环DECLARE       int NUMBER(2) :=0;BEGIN     LOOP         int := int + 1;         DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int);         EXIT WHEN int =10;     END LOOP;END;--WHILE 循环DECLARE       x NUMBER;BEGIN     x:= 1;     WHILE x<10 LOOP           DBMS_OUTPUT.PUT_LINE('X 的当前值为:'||x);           x:= x+1;     END LOOP;END;--数字式循环BEGIN     FOR int in 1..10 LOOP         DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);     END LOOP;END;--数字式循环-循环变量自动减1BEGIN     FOR int in REVERSE 1..10 LOOP         DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);     END LOOP;END;CREATE TABLE temp_table(num_col NUMBER);DECLARE       V_counter NUMBER := 10;BEGIN     INSERT INTO temp_table(num_col)VALUES (v_counter );     FOR v_counter IN 20 .. 25 LOOP         INSERT INTO temp_table (num_col ) VALUES ( v_counter );     END LOOP;     INSERT INTO temp_table(num_col)VALUES (v_counter );     FOR v_counter IN REVERSE 20 .. 25 LOOP         INSERT INTO temp_table (num_col ) VALUES ( v_counter );     END LOOP;END;DROP TABLE temp_table;select * from temp_table;--标号和GOTODECLARE       V_counter NUMBER := 1;BEGIN     LOOP         DBMS_OUTPUT.PUT_LINE('V_counter 的当前值为:'||V_counter);         V_counter := v_counter + 1;         IF v_counter > 10 THEN            GOTO l_ENDofLOOP;         END IF;     END LOOP;<<l_ENDofLOOP>>     DBMS_OUTPUT.PUT_LINE('V_counter 的当前值为:'||V_counter);END;select * from dept;--游标参数的传递方法DECLARE     DeptRec dept%ROWTYPE;     Dept_name dept.dname%TYPE;     Dept_loc dept.loc%TYPE;CURSOR c1 IS       SELECT dname, loc FROM dept WHERE deptno <= 30;CURSOR c2(dept_no NUMBER DEFAULT 10) IS       SELECT dname, loc FROM dept WHERE deptno <= dept_no;CURSOR c3(dept_no NUMBER DEFAULT 10) IS       SELECT * FROM dept WHERE deptno <=dept_no;BEGIN     OPEN c1;     LOOP         FETCH c1 INTO dept_name, dept_loc;         EXIT WHEN c1%NOTFOUND;         DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);         END LOOP;     CLOSE c1;     DBMS_OUTPUT.PUT_LINE('cl closed!');     OPEN c2;     LOOP         FETCH c2 INTO dept_name, dept_loc;         EXIT WHEN c2%NOTFOUND;         DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);     END LOOP;     CLOSE c2;     DBMS_OUTPUT.PUT_LINE('c2 closed!');          OPEN c3(dept_no =>20);     LOOP         FETCH c3 INTO deptrec;         EXIT WHEN c3%NOTFOUND;         DBMS_OUTPUT.PUT_LINE(deptrec.deptno||'---'||deptrec.dname||'---'||deptrec.loc);     END LOOP;     CLOSE c3;     DBMS_OUTPUT.PUT_LINE('c3 closed!');END;SELECT * FROM dept WHERE deptno <=30;select * from emp;--给工资低于1200的员工增加工资50DECLARE       v_empno emp.empno%TYPE;       v_sal emp.sal%TYPE;       CURSOR c IS SELECT empno, sal FROM emp;BEGIN     OPEN c;     LOOP         FETCH c INTO v_empno, v_sal;         EXIT WHEN C%NOTFOUND;         IF v_sal<=1200 THEN            UPDATE emp SET sal=sal+50 WHERE empno=v_empno;            DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');         END IF;         DBMS_OUTPUT.PUT_LINE('记录数:'||C%ROWCOUNT);     END LOOP;CLOSE c;END;--游标的FOR循环DECLARE       CURSOR c_sal IS SELECT empno, ename, sal FROM emp;BEGIN     --隐含打开游标     FOR v_sal IN c_sal LOOP     --隐含执行一个FETCH 语句                         DBMS_OUTPUT.PUT_LINE( to_char(v_sal.empno)||'---'||v_sal.ename||'---'||to_char(v_sal.sal));     --隐含监测c_sal%NOTFOUND     END LOOP;     --隐含关闭游标END;--当所声明的游标带有参数时,通过游标FOR 循环语句为游标传递参数DECLARE       CURSOR c1(dept_no NUMBER DEFAULT 10) IS       SELECT dname, loc FROM dept WHERE deptno <= dept_no;BEGIN     DBMS_OUTPUT.PUT_LINE('dept_no 参数值为30：');     FOR c1_rec IN c1(30) LOOP         DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc);     END LOOP;     DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no 参数值10：');     FOR c1_rec IN c1 LOOP         DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc);     END LOOP;END;--PL/SQL 还允许在游标FOR 循环语句中使用子查询来实现游标的功能。BEGIN     FOR c1_rec IN (SELECT dname, loc FROM dept) LOOP         DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc);     END LOOP;END;--删除EMP 表中某部门的所有员工，如果该部门中已没有员工，则在DEPT 表中删除该部门DECLARE       V_deptno emp.deptno%TYPE :=&p_deptno;BEGIN     DELETE FROM emp WHERE deptno=v_deptno;     IF SQL%NOTFOUND THEN        DELETE FROM dept WHERE deptno=v_deptno;     END IF;END;select * from emp;select * from dept;--从EMP表中查询某部门的员工情况,将其工资最低定为1500DECLARE       V_deptno emp.deptno%TYPE :=&p_deptno;       CURSOR emp_cursor IS SELECT empno, sal FROM emp WHERE deptno=v_deptno FOR UPDATE OF sal NOWAIT;BEGIN     FOR emp_record IN emp_cursor LOOP     IF emp_record.sal< 1500 THEN        UPDATE emp SET sal=1500 WHERE CURRENT OF emp_cursor;     END IF;     END LOOP;     --COMMIT;END;select * from emp;--预定义的异常处理-更新指定员工工资，如工资小于1500，则加100DECLARE       v_empno emp.empno%TYPE :=&empno;       v_sal emp.sal%TYPE;BEGIN     SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;     IF v_sal<=1400 THEN        UPDATE emp SET sal=sal+100 WHERE empno=v_empno;        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');     ELSE        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');     END IF;EXCEPTION     WHEN NO_DATA_FOUND THEN          DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');     WHEN TOO_MANY_ROWS THEN          DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');     WHEN OTHERS THEN          DBMS_OUTPUT.PUT_LINE('发生其它错误!');END;select * from dept;--删除指定部门的记录信息，以确保该部门没有员工。INSERT INTO dept VALUES(50, 'FINANCE', 'CHICAGO');DECLARE       v_deptno dept.deptno%TYPE :=&deptno;       e_deptno_remaining EXCEPTION;       PRAGMA EXCEPTION_INIT(e_deptno_remaining,2292);       /* 2292 是违反一致性约束的错误代码*/BEGIN     DELETE FROM dept WHERE deptno=v_deptno;EXCEPTION     WHEN e_deptno_remainingTHEN         DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');     WHEN OTHERS THEN         DBMS_OUTPUT.PUT_LINE('发生其它错误!');END;select * from emp;--更新指定员工工资，增加100DECLARE       v_empno emp.empno%TYPE :=&empno;       no_result EXCEPTION;BEGIN     UPDATE emp SET sal=sal+100 WHERE empno=v_empno;     IF SQL%NOTFOUND THEN     RAISE no_result;     END IF;EXCEPTION     WHEN no_result THEN         DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');     WHEN OTHERS THEN          DBMS_OUTPUT.PUT_LINE('发生其它错误!');END;--创建一个函数get_salary, 该函数检索指定部门的工资总和，其中定义了20991和20992号错误，分别处理参数为空和非法部门代码两种错误CREATE TABLE errlog(Errcode NUMBER,Errtext CHAR(40));CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER)     RETURN NUMBER AS     V_sal NUMBER;BEGIN     IF p_deptno IS NULL THEN        RAISE_APPLICATION_ERROR(-20991,'部门代码为空');     ELSIF p_deptno<0 THEN        RAISE_APPLICATION_ERROR(-20992,'无效的部门代码');     ELSE        SELECT SUM(sal) INTO v_sal FROM EMP WHERE deptno=p_deptno;        RETURN V_sal;     END IF;END;select * from emp;select * from errlog;delete errlog;DECLARE       V_salary NUMBER(7,2);       V_sqlcode NUMBER;       V_sqlerr VARCHAR2(512);       Null_deptno EXCEPTION;       Invalid_deptno EXCEPTION;       PRAGMA EXCEPTION_INIT(null_deptno,-20991);       PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);BEGIN     V_salary :=get_salary(1);     DBMS_OUTPUT.PUT_LINE('1 号部门工资：'||TO_CHAR(V_salary));     BEGIN          V_salary :=get_salary(-10);     EXCEPTION         WHEN invalid_deptno THEN              V_sqlcode :=SQLCODE;              V_sqlerr :=SQLERRM;              INSERT INTO errlog(errcode,errtext) VALUES(v_sqlcode, v_sqlerr);              COMMIT;     END inner1;          V_salary :=get_salary(20);     DBMS_OUTPUT.PUT_LINE('20 号部门工资：'||TO_CHAR(V_salary));     BEGIN          V_salary :=get_salary(NULL);     END inner2;          V_salary :=get_salary(30);     DBMS_OUTPUT.PUT_LINE('30 号部门工资：'||TO_CHAR(V_salary));     EXCEPTION     WHEN null_deptno THEN          V_sqlcode :=SQLCODE;          V_sqlerr :=SQLERRM;          INSERT INTO errlog(errcode,errtext) VALUES(v_sqlcode, v_sqlerr);          COMMIT;    WHEN OTHERS THEN          DBMS_OUTPUT.PUT_LINE('发生其它错误!');END outer;--将ORACLE 错误代码及其信息存入错误代码表CREATE TABLE errors(errnum NUMBER(4), errmsg VARCHAR2(100));select * from errors;DECLARE       err_msg VARCHAR2(100);BEGIN     /* 得到所有ORACLE 错误信息*/     FOR err_num IN -100.. 0 LOOP     err_msg := SQLERRM(err_num);     INSERT INTO errors VALUES(err_num, err_msg);     END LOOP;END;DROP TABLE errors;--查询ORACLE 错误代码select * from emp;BEGIN     INSERT INTO emp(empno,ename, hiredate, deptno) VALUES(2222, 'Jerry', SYSDATE, 20);     DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');     INSERT INTO emp(empno,ename, hiredate, deptno) VALUES(2222, 'Jerry', SYSDATE, 20);     DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');EXCEPTION         WHEN OTHERS THEN         DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END;--获取某部门的工资总和CREATE OR REPLACE FUNCTION get_salaryf(       Dept_no NUMBER,       Emp_count OUT NUMBER)       RETURN NUMBER IS       V_sum NUMBER;BEGIN     SELECT SUM(sal), count(*) INTO V_sum, emp_count FROM emp WHERE deptno=dept_no;     RETURN v_sum;EXCEPTION     WHEN NO_DATA_FOUND THEN          DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');     WHEN TOO_MANY_ROWS THEN          DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');           WHEN OTHERS THEN          DBMS_OUTPUT.PUT_LINE('发生其它错误!');END get_salaryf;select * from emp;--内嵌函数的调用-第一种参数传递格式称为位置表示法--计算某部门的工资总和DECLARE     V_num NUMBER;     V_sum NUMBER;BEGIN     V_sum := get_salaryf(3, v_num);     DBMS_OUTPUT.PUT_LINE('3 号部门工资总和：'||v_sum||',人数：'||v_num);END;--内嵌函数的调用-第二种参数传递格式称为名称表示法DECLARE     V_num NUMBER;     V_sum NUMBER;BEGIN     V_sum :=get_salaryf(emp_count=> v_num, dept_no => 3);     DBMS_OUTPUT.PUT_LINE('3 号部门工资总和：'||v_sum||'，人数：'||v_num);END;--内嵌函数的调用-第三种参数传递格式称为混合表示法CREATE OR REPLACE FUNCTION demo_fun(     Name VARCHAR2,     Age INTEGER,     Sex VARCHAR2)     RETURN VARCHAR2     AS     V_var VARCHAR2(32);BEGIN     V_var := name||'：'||TO_CHAR(age)||'岁，'||sex;     RETURN v_var;END;DECLARE     Var VARCHAR(32);BEGIN     Var := demo_fun('user1', 30, sex => '男');     DBMS_OUTPUT.PUT_LINE(var);          Var := demo_fun('user2', age => 40, sex => '男');     DBMS_OUTPUT.PUT_LINE(var);          Var := demo_fun('user3', sex => '女', age => 20);     DBMS_OUTPUT.PUT_LINE(var);END;--参数默认值CREATE OR REPLACE FUNCTION demo_fun2(       Name VARCHAR2,       Age INTEGER,       Sex VARCHAR2 DEFAULT '男')       RETURN VARCHAR2       AS       V_var VARCHAR2(32);BEGIN     V_var := name||'：'||TO_CHAR(age)||'岁，'||sex;     RETURN v_var;END;DECLARE       Var VARCHAR(32);BEGIN     Var := demo_fun2('user1', 30);     DBMS_OUTPUT.PUT_LINE(var);     Var := demo_fun2('user2', age => 40);     DBMS_OUTPUT.PUT_LINE(var);     Var := demo_fun2('user3', sex => '女', age => 20);     DBMS_OUTPUT.PUT_LINE(var);END;--存储过程--用户连接登记记录CREATE table logtable (userid VARCHAR2(10), logdate date);CREATE OR REPLACE PROCEDURE logexecution ISBEGIN     INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);END;--删除指定员工记录CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN emp.empno%TYPE)AS       No_result EXCEPTION;BEGIN     DELETE FROM emp WHERE empno=v_empno;     IF SQL%NOTFOUND THEN        RAISE no_result;     END IF;     DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');EXCEPTION     WHEN no_result THEN          DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');     WHEN OTHERS THEN          DBMS_OUTPUT.PUT_LINE('发生其它错误!');END DelEmp;--计算指定部门的工资总和，并统计其中的职工数量CREATE OR REPLACE PROCEDURE proc_demo(    Dept_no NUMBER DEFAULT 10,    Sal_sum OUT NUMBER,    Emp_count OUT NUMBER)ISBEGIN     SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no;EXCEPTION     WHEN NO_DATA_FOUND THEN          DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');     WHEN OTHERS THEN          DBMS_OUTPUT.PUT_LINE('发生其它错误!');END proc_demo;select * from emp;DECLARE     V_num NUMBER;     V_sum NUMBER(8, 2);BEGIN     Proc_demo(1, v_sum, v_num);          DBMS_OUTPUT.PUT_LINE('1 号部门工资总和：'||v_sum||'，人数：'||v_num);     Proc_demo(sal_sum=> v_sum, emp_count => v_num);          DBMS_OUTPUT.PUT_LINE('10 号部门工资总和：'||v_sum||'，人数：'||v_num);END;--建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量DECLARE    V_num NUMBER;    V_sum NUMBER(8, 2);    PROCEDURE proc_demo(        Dept_no NUMBER DEFAULT 10,        Sal_sum OUT NUMBER,        Emp_count OUT NUMBER)    IS    BEGIN        SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no;    EXCEPTION        WHEN NO_DATA_FOUND THEN            DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');         WHEN OTHERS THEN            DBMS_OUTPUT.PUT_LINE('发生其它错误!');    END proc_demo;BEGIN    Proc_demo(1, v_sum, v_num);    DBMS_OUTPUT.PUT_LINE('1 号部门工资总和：'||v_sum||'，人数:'||v_num);    Proc_demo(sal_sum=> v_sum, emp_count => v_num);    DBMS_OUTPUT.PUT_LINE('10 号部门工资总和：'||v_sum||'，人数:'||v_num);END;--授权执行权给相关的用户或角色CREATE OR REPLACE PUBLIC SYNONYM DBMS_JOB FOR DBMS_JOB;GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION;--与过程相关数据字典select * from USER_SOURCE;select * from ALL_SOURCE;select * from DBA_SOURCE;select * from USER_ERRORS;DESC proc_demo;desc emp;DROP PROCEDURE logexecution;select * from dept;--包的创建和应用--包定义的说明--创建的包为demo_pack, 该包中包含一个记录变量DeptRec、两个函数和一个过程CREATE OR REPLACE PACKAGE demo_pack   IS   DeptRec dept%ROWTYPE;   V_sqlcode NUMBER;   V_sqlerr VARCHAR2(2048);   FUNCTION add_dept(       dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)       RETURN NUMBER;   FUNCTION remove_dept(dept_no NUMBER)       RETURN NUMBER;   PROCEDURE query_dept(dept_no IN NUMBER);END demo_pack;--包主体的创建CREATE OR REPLACE PACKAGE BODY demo_pack   IS   Flag INTEGER;   FUNCTION check_dept(dept_no NUMBER)        RETURN INTEGER;   FUNCTION add_dept(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)        RETURN NUMBER        IS             BEGIN                  IF check_dept(dept_no)=0 THEN                       INSERT INTO dept VALUES(dept_no, dept_name, location);                       RETURN 1;                  ELSE                       RETURN 0;             END IF;   EXCEPTION        WHEN OTHERS THEN             V_sqlcode := SQLCODE;             V_sqlerr := SQLERRM;             RETURN 1;   END add_dept;      FUNCTION remove_dept(dept_no NUMBER)        RETURN NUMBER        IS            BEGIN                V_sqlcode := 0;                V_sqlerr := NULL;                IF check_dept(dept_no)= 1 THEN                   DELETE FROM dept WHERE deptno=dept_no;                   RETURN 1;                ELSE                   RETURN 0;                END IF;            EXCEPTION WHEN OTHERS THEN                V_sqlcode := SQLCODE;                V_sqlerr := SQLERRM;                RETURN 1;   END remove_dept;      PROCEDURE query_dept(dept_no IN NUMBER)   IS       BEGIN            IF check_dept(dept_no)=1 THEN                 SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;            END IF;   END query_dept;      FUNCTION check_dept(dept_no NUMBER)       RETURN INTEGER       IS           BEGIN                SELECT COUNT(*) INTO flag FROM dept WHERE deptno=dept_no;                IF flag > 0 THEN                   Flag := 1;                END IF;                RETURN flag;   END check_dept;                BEGIN   V_sqlcode := NULL;   V_sqlerr := NULL;END demo_pack;select * from dept;delete dept where deptno=90;--对包内共有元素的调用格式为：包名.元素名称DECLARE    Var NUMBER;BEGIN    Var := demo_pack.add_dept(90,'Administration', 'Beijing');    IF Var =-1 THEN        DBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);    ELSIF Var =0 THEN        DBMS_OUTPUT.PUT_LINE('该部门记录已经存在！');    ELSE        DBMS_OUTPUT.PUT_LINE('添加记录成功！');        Demo_pack.query_dept(90);        DBMS_OUTPUT.PUT_LINE(demo_pack.DeptRec.deptno||'---'||demo_pack.DeptRec.dname||'---'||demo_pack.DeptRec.loc);        Var := demo_pack.remove_dept(90);        IF Var =-1 THEN            DBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);        ELSE            DBMS_OUTPUT.PUT_LINE('删除记录成功！');        END IF;    END IF;END;select * from emp;--创建包emp_packageCREATE OR REPLACE PACKAGE emp_package   IS   TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;   PROCEDURE read_emp_table(p_emp_table OUT emp_table_type);END emp_package;CREATE OR REPLACE PACKAGE BODY emp_package    IS    PROCEDURE read_emp_table(p_emp_table OUT emp_table_type)    IS    I BINARY_INTEGER := 0;    BEGIN         FOR emp_record IN ( SELECT * FROM emp ) LOOP         P_emp_table(i) := emp_record;         I := I + 1;         END LOOP;    END read_emp_table;END emp_package;DECLARE    E_table emp_package.emp_table_type;BEGIN    Emp_package.read_emp_table(e_table);    FOR I IN e_table.FIRST .. e_table.LAST LOOP        DBMS_OUTPUT.PUT_LINE(e_table(i).empno||'---'||e_table(i).ename);    END LOOP;END;--子程序重载--PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同--的名称，但拥有不同的参数变量、参数顺序或参数数据类型。CREATE OR REPLACE PACKAGE demo_pack1    IS    DeptRec dept%ROWTYPE;    V_sqlcode NUMBER;    V_sqlerr VARCHAR2(2048);    FUNCTION query_dept(dept_no IN NUMBER)         RETURN INTEGER;    FUNCTION         query_dept(dept_no IN VARCHAR2)         RETURN INTEGER;END demo_pack1;CREATE OR REPLACE PACKAGE BODY demo_pack1    IS    FUNCTION check_dept(dept_no NUMBER)         RETURN INTEGER    IS    Flag INTEGER;    BEGIN         SELECT COUNT(*) INTO flag FROM dept WHERE deptno=dept_no;         IF flag > 0 THEN            RETURN 1;         ELSE            RETURN 0;         END IF;    END check_dept;        FUNCTION check_dept(dept_no VARCHAR2)             RETURN INTEGER    IS    Flag INTEGER;    BEGIN         SELECT COUNT(*) INTO flag FROM dept WHERE deptno=dept_no;         IF flag > 0 THEN            RETURN 1;         ELSE            RETURN 0;         END IF;    END check_dept;        FUNCTION query_dept(dept_no IN NUMBER)         RETURN INTEGER    IS    BEGIN         IF check_dept(dept_no)=1 THEN            SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;            RETURN 1;         ELSE            RETURN 0;         END IF;    END query_dept;        FUNCTION query_dept(dept_no IN VARCHAR2)         RETURN INTEGER    IS    BEGIN         IF check_dept(dept_no)=1 THEN            SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;            RETURN 1;         ELSE            RETURN 0;         END IF;    END query_dept;END demo_pack1;--删除包DROP PACKAGE demo_pack1;--包的管理select * from DBA_SOURCE;select * from USER_SOURCE;select * from USER_ERRORS;select * from DBA_OBJECTS;select * from emp;select * from emp_his;insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(100,'Test',30.5,sysdate,10,'president',1);insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(7788,'Test2',100.23,sysdate,10.2,'manager',2);CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;CREATE OR REPLACE TRIGGER del_emp     BEFORE DELETE ON HAIYA1.emp FOR EACH ROWBEGIN     --将修改前数据插入到日志记录表del_emp ,以供监督使用。     INSERT INTO emp_his(deptno, empno, ename, job, sal, comm, hiredate)            VALUES( :old.deptno, :old.empno, :old.ename , :old.job, :old.sal, :old.comm, :old.hiredate );END;--删除记录触发触发器DELETE emp WHERE empno=7788;DROP TABLE emp_his;DROP TRIGGER del_emp;--创建替代(Instead_of)触发器--我们可以创建INSTEAD_OF 触发器来为DELETE 操作执行所需的处理，即删除EMP表中所有基准行CREATE OR REPLACE VIEW emp_view AS       SELECT deptno, count(*) total_employeer, sum(sal) total_salary FROM emp GROUP BY deptno;       select * from emp_view;CREATE OR REPLACE TRIGGER emp_view_delete       INSTEAD OF DELETE ON emp_view FOR EACH ROWBEGIN     DELETE FROM emp WHERE deptno= :old.deptno;END emp_view_delete;DELETE FROM emp_view WHERE deptno=1;DROP TRIGGER emp_view_delete;DROP VIEW emp_view;--改变一个触发器的状态ALTER TRIGGER del_emp DISABLE;ALTER TRIGGER del_emp ENABLE;--使表EMP上的所有TRIGGER失效/生效ALTER TABLE emp DISABLE ALL TRIGGERS;ALTER TABLE emp ENABLE ALL TRIGGERS;--触发器和数据字典SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,STATUS, ACTION_TYPEFROM user_triggers;--数据库触发器的应用举例BEGIN  --创建用于记录事件日志的数据表  DBMS_UTILITY.EXEC_DDL_STATEMENT('    CREATE TABLE eventlog(    Eventname VARCHAR2(20) NOT NULL,    Eventdate date default sysdate,    Inst_num NUMBER NULL,    Db_name VARCHAR2(50) NULL,    Srv_error NUMBER NULL,    Username VARCHAR2(30) NULL,    Obj_type VARCHAR2(20) NULL,    Obj_name VARCHAR2(30) NULL,    Obj_owner VARCHAR2(30) NULL    )  ');  --创建DDL 触发器trig4_ddl  DBMS_UTILITY.EXEC_DDL_STATEMENT('    CREATE OR REPLACE TRIGGER trig_ddl    AFTER CREATE OR ALTER OR DROP    ON DATABASE    DECLARE    Event VARCHAR2(20);    Typ VARCHAR2(20);    Name VARCHAR2(30);    Owner VARCHAR2(30);    BEGIN        --读取DDL 事件属性    Event := SYSEVENT;    Typ := DICTIONARY_OBJ_TYPE;    Name := DICTIONARY_OBJ_NAME;    Owner := DICTIONARY_OBJ_OWNER;        --将事件属性插入到事件日志表中    INSERT INTO HAIYA1.eventlog(eventname,obj_type, obj_name, obj_owner) VALUES(event, typ, name, owner);    END;  ');    --创建LOGON、STARTUP 和SERVERERROR 事件触发器  DBMS_UTILITY.EXEC_DDL_STATEMENT('    CREATE OR REPLACE TRIGGER trig_after    AFTER LOGON OR STARTUP OR SERVERERROR    ON DATABASE    DECLARE    Event VARCHAR2(20);    Instance NUMBER;    Err_num NUMBER;    Dbname VARCHAR2(50);    User VARCHAR2(30);    BEGIN    Event := SYSEVENT;    IF    event = ''LOGON'' THEN    User := LOGIN_USER;    INSERT INTO eventlog(eventname,username)    VALUES(event, user);    ELSIF event = ''SERVERERROR'' THEN    Err_num := SERVER_ERROR(1);    INSERT INTO eventlog(eventname,srv_error)    VALUES(event, err_num);    ELSE    Instance := INSTANCE_NUM;    Dbname := DATABASE_NAME;    INSERT INTO eventlog(eventname,inst_num, db_name) VALUES(event, instance, dbname);    END IF;    END;  ');    --创建LOGOFF 和SHUTDOWN 事件触发器  DBMS_UTILITY.EXEC_DDL_STATEMENT('    CREATE OR REPLACE TRIGGER trig_before    BEFORE LOGOFF OR SHUTDOWN    ON DATABASE    DECLARE    Event VARCHAR2(20);    Instance NUMBER;    Dbname VARCHAR2(50);    User VARCHAR2(30);    BEGIN    Event := SYSEVENT;    IF event = ''LOGOFF'' THEN    User := LOGIN_USER;    INSERT INTO eventlog(eventname,username)    VALUES(event, user);    ELSE    Instance := INSTANCE_NUM;    Dbname := DATABASE_NAME;    INSERT INTO eventlog(eventname,inst_num, db_name) VALUES(event, instance, dbname);    END IF;    END;  ');END;CREATE TABLE mydata(mydate NUMBER);drop table mydata;select * from mydata;SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_errorFROM eventlog;select * from eventlog;drop table eventlog;delete eventlog;--删除触发器DROP TRIGGER trig_ddl;DROP TRIGGER trig_before;DROP TRIGGER trig_after;--删除记录事件日志的数据表DROP TABLE eventlog;