|
第10章PLSQL程序开发 |
|
|
1第10章 PL/SQL命名对象2本章内容存储过程函数包触发器310.1 存储过程创建存储过程调用存储过程案例数据库中存储过程的创建4 10.1.1 创建存储过程基本语法CREATE [OR REPLACE] PROCEDURE procedure_name(pa rameter1_name [mode] datatype [DEFAULT|:=value][, parameter2_ name [mode] datatype [DEFAULT|:=value],…])AS|IS /Declarati ve section is here /BEGIN /Executable section is here/ EXCEP TION /Exception section is here/ END[procedure_name]; 5参数说明 参 数的模式 IN(默认参数模式)表示当过程被调用时,实参值被传递给形参;在过程内,形参起常量作用,只能读该参数,而不能修改该参数;当 子程序调用结束返回调用环境时,实参没有被改变。IN模式参数可以是常量或表达式。OUT表示当过程被调用时,实参值被忽略;在过程内,形 参起未初始化的PL/SQL变量的作用,初始值为NULL,可以进行读/写操作;当子程序调用结束后返回调用环境时,形参值被赋给实参。O UT模式参数只能是变量,不能是常量或表达式。IN OUT表示当过程被调用时,实参值被传递给形参;在过程内,形参起已初始化的PL/S QL变量的作用,可读可写;当子程序调用结束返回调用环境时,形参值被赋给实参。IN OUT模式参数只能是变量,不能是常量或表达式。 6参数的限制在声明形参时,不能定义形参的长度或精度、刻度,它们是作为参数传递机制的一部分被传递的,是由实参决定的。参数传递方式当子 程序被调用时,实参与形参之间值的传递方式取决于参数的模式。IN参数为引用传递,即实参的指针被传递给形参;OUT,IN OUT参数为 值传递,即实参的值被复制给形参。参数默认值可以为参数设置默认值,这样存储过程被调用时如果没有给该参数传递值,则采用默认值。需要注意 ,有默认值的参数应该放在参数列表的最后。 7创建一个存储过程,以部门号为参数,查询该部门的平均工资,并输出该部门中比平均工资高的员 工号、员工名。CREATE OR REPLACE PROCEDURE proc_show_emp( p_deptno emplo yees.department_id%TYPE)AS v_sal employees.salary%TYPE;BEGIN SE LECT avg(salary) INTO v_sal FROM employees WHERE department_id= p_deptno; DBMS_OUTPUT.PUT_LINE(p_deptno||'' ''|| ''average salary is: ''||v_sal); FOR v_emp IN (SELECT FROM employees WHERE department_id=p_deptno AND salary>v_sal)LOOP DBMS_OUTPUT.PUT_LINE(v_emp.employee_id||'' ''| | v_emp.first_name||'' ''||v_emp.last_name); END LOOP;EXCEPTIO N WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(''The departmen t doesn"t exists! '');END proc_show_emp;8通常,存储过程不需要返回值,如果需要返回一个值可以 通过函数调用实现。但是,如果希望返回多个值,可以使用OUT或IN OUT模式参数来实现。9创建一个存储过程,以部门号为参数,以部门 编号为参数返回该部门的人数和平均工资。 CREATE OR REPLACE PROCEDURE proc_return_depti nfo( p_deptno employees.department_id%TYPE, p_avgsal OUT employ ees.salary%TYPE, p_count OUT NUMBER)ASBEGIN SELECT avg(salary) ,count() INTO p_avgsal,p_count FROM employees WHERE department_ id=p_deptno;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT _LINE(''The department don''''t exists! '');END proc_return_deptinfo; 1010.1.2 调用存储过程在SQLPLUS中调用EXEC procedure_name(parameter_list)E XECUTE proc_show_emp(10)在PL/SQL块中调用BEGIN procedure_name(para meter_list);END;注意在PL/SQL程序中,存储过程可以作为一个独立的表达式被调用。 11DECLARE v_av gsal emp.sal%TYPE; v_count NUMBER;BEGIN proc_show_emp(20); proc_return_deptinfo(10,v_avgsal,v_count); DBMS_OUTPUT.PUT_LINE (v_avgsal||'' ''|| v_coun t);END; 1210.1.3 案例数据库中存储过程的创建创建名为“PROC_SECURE_DML”的存储过程,检查当前用户操 作时间是否为工作时间,即非周六、周日,时间为08:00-18:00。CREATE OR REPLACE PROCEDURE pro c_secure_dmlISBEGIN IF TO_CHAR (SYSDATE, ''HH24:MI'') NOT BETWEEN ''08:00'' AND ''18:00'' OR TO_CHAR (SYSDATE, ''DY'', ''NLS_DA TE_LANGUAGE=AMERICAN'') IN (''SAT'', ''SUN'') THEN RAISE_APPLICATIO N_ERROR (-20205,‘只能在正常的工作时 间内进行改变。''); END I F;END proc_secure_dml; 13创建名为“PROC_JOB_CHANGE”的存储过程,实现员工职位的调动。CRE ATE OR REPLACE PROCEDURE proc_job_change( p_employee_id employees .employee_id%type, p_new_job_title jobs.job_title%type)AS v_old_j ob_id jobs.job_id%type; v_old_job_title jobs.job_title%type; v_ne w_job_id jobs.job_id%type;BEGIN SELECT job_id INTO v_old_job_id FROM employees WHERE employee_id=p_employee_id; SELECT job_title INTO v_old_job_title FROM jobs WHERE job_id=v_old_job_id; IF v_ old_job_title=p_new_job_title THEN RAISE_APPLICATION_ERROR(-20 001,''the new job title is as same as before!''); END IF; SELECT job_id INTO v_new_job_id FROM jobs WHERE job_title=p_new_job_titl e; UPDATE employees SET job_id=v_new_job_id WHERE employee_id=p_ employee_id; COMMIT;EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_A PPLICATION_ERROR(-20002,''The job title does not exists!'');END pro c_job_change; 14创建名为“PROC_DEPARTMENT_CHANGE”的存储过程,实现员工部门的调动。CREAT E OR REPLACE PROCEDURE proc_department_change( p_employee_id em ployees.employee_id%type, p_new_department_name departments.dep artment_name%type) AS v_old_department_id departments.depart ment_id%type; v_old_department_name departments.department_name %type; v_new_department_id departments.department_id%type; B EGIN SELECT department_id INTO v_old_department_id FROM employe es WHERE employee_id=p_employee_id; SELECT department_name I NTO v_old_department_name FROM departments WHERE department_id =v_old_department_name; IF v_old_department_name=p_new_departme nt_name THEN RAISE_APPLICATION_ERROR(-20001,''the new departm ent name is as same as before!''); END IF; SELECT department_i d INTO v_new_department_id FROM departments WHERE department_n ame=p_new_department_name; UPDATE employees SET department_id=v _new_department_id WHERE employee_id=p_employee_id; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(- 20002,''The department name does not exists!''); END proc_departme nt_change; 1510. 2函数创建函数调用案例数据库中函数的创建1610.2.1 创建函数基本语法为 CREATE [ OR REPLACE] FUNCTION function_name (parameter1_name [mode] dataty pe [DEFAULT|:=value][, parameter2_name [mode] datatype [D EFAULT|:=value],…])RETURN return_datatype AS|IS /Declarative section is here /BEGIN /Executable section is here/ EXCEPT ION /Exception section is here/ END [function_name]; 17注意在函数 定义的头部,参数列表之后,必须包含一个RETURN语句来指明函数返回值的类型,但不能约束返回值的长度、精度、刻度等。如果使用%TY PE,则可以隐含地包括长度、精度、刻度等约束信息;在函数体的定义中,必须至少包含一个RETURN 语句,来指明函数返回值。也可以有 多个RETURN语句,但最终只有一个RETURN语句被执行。18创建名为“FUNC_DEPT_MAXSAL”的函数,以部门编号为参 数,返回部门最高工资。 CREATE OR REPLACE FUNCTION func_dept_maxsal( p_deptn o employees.department_id%TYPE) RETURN employees.salary%TYPEAS v_maxsal employees.salary%TYPE;BEGIN SELECT max(salary) INTO v_m axsal FROM employees WHERE department_id=p_deptno; RETURN v_m axsal;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LIN E(''The deptno is invalid! '');END func_dept_maxsal;19如果需要函数返回多个值,可 以使用OUT或IN OUT模式参数。20创建一个名为“FUNC_DEPT_INFO”的函数,以部门号为参数,返回部门名、部门人数及 部门平均工资。 CREATE OR REPLACE FUNCTION func_dept_info( p_deptno depa rtments.department_id%TYPE,p_num OUT NUMBER,p_avg OUT NUMBER) RE TURN departments.department_name%TYPEAS v_dname departments.depa rtment_name%TYPE;BEGIN SELECT department_name INTO v_dname FROM departments WHERE department_id=p_deptno; SELECT count(),avg(sa lary) INTO p_num,p_avg FROM employees WHERE department_id=p_deptn o; RETURN v_dname;END func_dept_info; 2110.2.2 调用函数在SQL语句中调用函数在 PL/SQL中调用函数注意函数只能作为表达式的一部分被调用。示例通过func_dept_maxsal函数的调用,输出各个部门的最高 工资;通过func_dept_info函数调用,输出各个部门名、部门人数及平均工资。 22DECLARE v_maxsal em ployees.salary%TYPE; v_avgsal employees.salary%TYPE; v_num N UMBER; v_dname departments.department_name%TYPE;BEGIN FOR v_de pt IN (SELECT DISTINCT department_id FROM employees WHERE depa rtment_id IS NOT NULL) LOOP v_maxsal:=func_dept_maxsal(v_dep t.department_id); v_dname:=func_dept_in fo(v_dept.department_id, v_num, v_avgsal); DBMS_OUTPUT.PUT_LINE(v_dname||'' ''||v_maxs al||'' ''|| v_avgsal||'' ''|| v_num); END LOOP;END;23函数可以在SQL语句的以下部分调用:SELECT语句的目标列;WHERE和HAVI NG子句;CONNECT BY,START WITH,ORDER BY,GROUP BY子句;INSERT语句的VALUES子句中 ;UPDATE语句的SET子句中。 2410.2.3 案例数据库中函数的创建创建名为“FUNC_EMP_SALARY”的函数,以 员工编号为参数,返回员工的工资。CREATE OR REPLACE FUNCTION func_emp_salary( p_empno employees.employee_id%type)RETURN employees.salary%type AS v_sal employees.salary%type;BEGIN SELECT salary INTO v_sal FROM employees WHERE employee_id=p_empno; RETUR N v_sal;EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLIC ATION_ERROR(-20000,''There is not such an employee!'');EN D func_emp_salary;25创建名为“FUNC_EMP_DEPT_AVGSAL”的函数,以员工编号为参数,返回该员工所 在部门的平均工资。CREATE OR REPLACE FUNCTION func_emp_dept_avgsal(p_empno employees.employee_id%type)RETURN employees.salary%typeAS v_dept no employees.department_id%type; v_avgsal employees.salary%type; BEGIN SELECT department_id INTO v_deptno FROM employees WHERE employee_id=p_empno; SELECT avg(salary) INTO v_avgsal FROM empl oyees WHERE department_id=v_deptno; RETURN v_avgsal;EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,''Ther e is not such an employee!'');END func_emp_dept_avgsal;261 0.3 包创建包调用包27包是包含一个或多个子程序单元(过程、函数等)的容器。包构成包规范:声明了软件包中所有内容,如过程、函数 、游标、类型、异常和变量等,其中过程和函数只包括原型信息,不包含任何子程序代码。 包体:包含了在包头中的过程和函数的实现代码。包 体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数,但是它们是私有元素,只能由同一包体中其他过程和函数使用。281 0.3.1 创建包创建包规范 创建包体 29(1)创建包规范语法CREATE OR REPLACE PACKAGE packag e_name IS|AS[PRAGMA SERIALLY_RESUABLE] type_definition|variable_ declaration| exception_declaration|cursor_declaration| procedu re_ declaration|function_ declarationEND [package_name];30注意:元素声明 的顺序可以是任意的,但必须先声明后使用;所有元素是可选的;过程和函数的声明只包括原型,不包括具体实现。31创建一个软件包,包括2个 变量、2个过程和1个异常。CREATE OR REPLACE PACKAGE pkg_empAS minsal NUMBER ; maxsal NUMBER; e_beyondbound EXCEPTION; PROCEDURE update_ sal( p_empno NUMBER, p_sal NUMBER); PROCEDURE add_employee ( p_empno NUMBER,p_sal NUMBER);END pkg_emp; 32语法CR EATE OR REPLACE PACKAGE BODY package_name IS|AS[PRAGMA SERIALLY_R ESUABLE] type_definition|variable_declaration| exception_declar ation| cursor_declaration| procedure_definition | function_de finitionEND [package_name]; (2)创建包体 33注意:包体中函数和过程的原型必须与包规范中的声明完全一 致;只有在包规范已经创建的条件下,才可以创建包体;如果包规范中不包含任何函数或过程,则可以不创建包体。 34CREATE OR R EPLACE PACKAGE BODY pkg_empAS PROCEDURE update_sal(p_empno NUMBE R, p_sal NUMBER) AS BEGIN SELECT min(salary), max(salary) IN TO minsal,maxsal FROM employees; IF p_sal BETWEEN minsal A ND maxsal THEN UPDATE employees SET salary=p_sal WHERE employee_id=p_empno; IF SQL%NOTFOUND THEN RAISE_APPL ICATION_ERROR(-20000, ''The employee doesn''''t exist'' ); END IF; ELSE RAISE e_beyondbound; END IF; EXC EPTION WHEN e_beyondbound THEN DBMS_OUTPUT.PUT_LINE(''The salary is beyond bound! ''); END update_sal; 35PROCEDURE add_empl oyee(p_empno NUMBER,p_sal NUMBER) AS BEGIN SELECT min(salary ), max(salary) INTO minsal,maxsal FROM employees; IF p_s al BETWEEN minsal AND maxsal THEN INSERT INTO employees ( employee_id,last_name,email,hire_date,job_id,salary)VALUES(p_empn o,''Smith'',''smith@neusoft.edu.cn'',sysdate, ''ST_MAN'',p_sal); E LSE RAISE e_beyondbound; END IF; EXCEPTION WHEN e_beyondbound THEN DBMS_OUTPUT.PUT_LINE(''The salary is beyon d bound! ''); END add_employee; END pkg_emp;3610.3.2调用包在包规范声明的任何 元素是公有的,在包外都是可见的包外:通过package.element形式调用;包内:直接通过元素名进行调用。 在包体中定义而没有 在包头中声明的元素是私有的,只能在包体中引用 37调用包pkg_emp中的过程update_sal,修改150号员工工资为8000 。调用add_employee添加一个员工号为2011,工资为9000的员工 BEGIN pkg_emp.update_sal( 150,8000); pkg_emp.add_employee(2011,9000);END;3810.4 触 发 器触发 器概述 DML触发器概述创建DML触发器变异表触发器 案例数据库触发器的创建3910.4.1 DML触发器概述触发器是一种特殊类 型的存储过程,编译后存储在数据库服务器中。当特定事件发生时,由系统自动调用执行,而不能由应用程序显式地调用执行。触发器主要用于维护 那些通过创建表时的声明约束不可能实现的复杂的完整性约束,并对数据库中特定事件进行监控和响应。40触发器的类型 DML触发器建立在基 本表上的触发器响应基本表的INSERT,UPDATE,DELETE操作。INSTEAD OF触发器建立在视图上的触发器响应视图上的 INSERT,UPDATE,DELETE操作。系统触发器建立在数据库系统或模式上的触发器响应系统事件(STARTUP,SHUTDO WN,SERVERERROR,LOGON,LOGOFF)和DDL(CREATE,ALTER,DROP)操作。 41触发器组成 触发 器由触发器头部和触发器体两个部分组成触发器头部(触发器何时被调用)作用对象:触发器作用的对象包括表、视图、数据库和模式。触发事件: 激发触发器执行的事件。如DML、DDL、数据库系统事件等。触发时间:用于指定触发器在触发事件完成之前还是之后执行。如果指定为AFT ER,则表示先执行触发事件,然后再执行触发器;如果指定为BEFORE,则表示先执行触发器,然后再执行触发事件。10.4.2 DM L触发器概述DML触发器包括语句级前触发器、语句级后触发器、行级前触发器、行级后触发器4大类,其执行的顺序如下。(1)如果存在,则 执行语句级前触发器。(2)对于受触发事件影响的每一个记录:如果存在,则执行行级前触发器;执行当前记录的DML操作(触发事件);如果 存在,则执行行级后触发器。(3)如果存在,则执行语句级后触发器。4310.4.3 创建DML触发器DML触发器语法 CREATE [OR REPLACE] TRIGGER trigger_nameBEFORE|AFTER triggering_event [ OF column_name]ON table_name[FOR EACH ROW][WHEN trigger_condition ]DECLARE /Declarative section is here /BEGIN /Exccutab le section si here/ EXCEPTION /Exception section is here/ END [trigger_name];Trigger_body44创建触发器,保证非工作时间禁止对EMPLOYEES表进行DML操 作。CREATE OR REPLACE TRIGGER trg_secure_empBEFORE INSERT OR UPDATE OR DELETEON employeesBEGINIF TO_CHAR (SYSDATE, ''HH24:MI'') NOT BE TWEEN ''08:00'' AND ''18:00'' OR TO_CHAR (SYSDATE, ''DY'', ''NLS_DATE_LA NGUAGE=AMERICAN'') IN (''SAT'', ''SUN'') THENRAISE_APPLICATION_ERROR ( -20005,''只能在正常的工作时间内进行改变。'');END IF;END trg_secure_emp; (1)创建语句级DML 触发器45如果触发器响应多个DML事件,而且需要根据事件的不同进行不同的操作,则可以在触发器体中使用3个条件谓词。46为emplo yees表创建一个触发器,当执行插入操作时,统计操作后员工人数;当执行更新员工工资操作时,统计更新后员工平均工资;当执行删除员工操 作时,统计删除后各个部门的员工人数。 47CREATE OR REPLACE TRIGGER trg_emp_dmlAFTER I NSERT OR UPDATE OR DELETE ON employeesDECLARE v_count NUMBER; v _sal NUMBER(6,2);BEGIN IF INSERTING THEN SELECT count() I NTO v_count FROM employees; DBMS_OUTPUT.PUT_LINE(v_count); EL SIF UPDATING THEN SELECT avg(salary) INTO v_sal FROM employees ; DBMS_OUTPUT.PUT_LINE(v_sal); ELSE FOR v_dept IN (SELECT department_id,count() num FROM employees GROUP BY departme nt_id) LOOP DBMS_OUTPUT.PUT_LINE(v_dept.department_id||'' ''|| v_dept.num); END LOOP; END IF;END trg_emp_dml; 48(2)创建行级DML触发器行级触发器是指执行DML操作时,每操作 一个记录,触发器就执行一次,一个DML操作涉及多少个记录,触发器就执行多少次。在行级触发器中可以使用WHEN条件,进一步控制触发器 的执行。在行级触发器中引入了:old和:new 两个标识符,来访问和操作当前被处理记录中的数据。 49:old和:new标识符:o ld和:new作为triggering_table%ROWTYPE类型的两个变量在不同触发事件中,:old和:new的意义不同50 引用方式: :old.field和:new.field (执行部分) old.field 和new.fiel d (WHEN条件中)注意事项:是伪记录,不能作为整个记录进行赋值或引用 不能传递给带triggering_table%ROW TYPE参数的过程和函数 如果触发器是建立在嵌套表上,;old和;new都执行嵌套表的行,:parent指向父表中的当前行。 51 为employees表创建一个触发器,当插入新员工时显示新员工的员工号、员工名;当更新员工工资时,显示修改前后员工工资;当删除员工 时,显示被删除的员工号、员工名。 52CREATE OR REPLACE TRIGGER trg_emp_dml_rowBEFOR E INSERT OR UPDATE OR DELETE ON employeesFOR EACH ROWBEGIN IF INS ERTING THEN DBMS_OUTPUT.PUT_LINE(:new.employee_id||'' ''|| :new.first_name||'' ''||:new.last_name); ELSIF UPDAT ING THEN DBMS_OUTPUT.PUT_LINE(:old.salary||'' ''||:new.salary); ELSE DBMS_OUTPUT.PUT_LINE(:old.employee_id||'' ''|| :old.first_name||'' ''||:old.last_name);END IF;E ND trg_emp_dml_row;53在行级触发器中,可以使用WHEN子句进一步控制触发器的执行。例如,修改员工工资时,保证修 改后的工资高于修改前的工资。CREATE OR REPLACE TRIGGER trg_emp_update_rowBEFORE UPDATE OF salary ON employeesFOR EACH ROWWHEN(new.salary<=old.sal ary)BEGIN RAISE_APPLICATION_ERROR(-20001, ''The salary is lower! '');END trg_emp_update_row;5410.4 .4 变异表触发器概念变异表是指激发触发器的DML语句所操作的表,即触发器为之定义的表,或者由于DELETE CASCADE操作 而需要修改的表,即当前表的子表。约束表是指由于引用完整性约束而需要从中读取或修改数据的表,即当前表的父表。55当对一个表创建行级触 发器,或创建由DELETE CASCADE操作而激发的语句级触发器时,有下列两条限制:不能读取或修改任何触发语句的变异表;不能读取 或修改触发表的一个约束表的PRIMARY KEY,UNIQUE 或FOREIGN KEY关键字的列, 但 可以修改其他列。 注意如果INSERT…VALUES语句只影响一行,那么该语句的行级前触发器不会把触发表当做变异表对待。INSERT INTO ta ble SELECT…等语句总是把触发表当做变异表,即使子查询仅仅返回一条记录。 56如果既想更新变异表,同时又需要查询变异表,那 么如何处理呢?将行级触发器与语句级触发器结合起来,在行级触发器中获取要修改的记录的信息,存放到一个软件包的全局变量中,然后在语句级 后触发器中利用软件包中全局变量信息对变异表的查询,并根据查询的结果进行业务处理。57为了实现在更新员工所在部门或向部门插入新员工时 ,部门中员工人数不超过20人,可以在employees表上创建两个触发器,同时创建一个共享信息的包。 58CREATE OR RE PLACE PACKAGE mutate_pkg AS v_deptno NUMBER(2); END;CREATE OR REP LACE TRIGGER trg_rmutateBEFORE INSERT OR UPDATE OF department_id ON employees FOR EACH ROWBEGIN mutate_pkg.v_deptno:=:new.depart ment_id; END;59CREATE OR REPLACE TRIGGER trg_smutateAFTER INSERT OR UPDATE OF department_id ON employeesDECLARE v_num number(3); BEGIN SELECT count() INTO v_num FROM employees WHERE department_id=mutate_pkg.v_deptno; IF v_num>20 THENRAISE_APPLICATION_ERROR(-20003,''TOO MANY EMPLOYEES IN DEPARTMENT ''||mutate_pkg.v_deptno); END IF;END;10.4.5 案例数据库触发器的创建为EMPLOYEES表创建触发器,保证插入新员工或修改员工工资时,员工的最新工资在其工作职位所允许的工资范围之内。为EMPLOYEES表创建触发器,当员工职位变动或部门变动时,相关信息写入job_history表。61CREATE OR REPLACE TRIGGER trg_dml_emp_salary AFTER INSERT OR UPDATE OF salary ON employees FOR EACH ROW DECLARE v_maxsal employees.salary%type; v_minsal employees.salary%type; BEGIN SELECT max_salary,min_salary INTO v_maxsal,v_minsal FROM jobs WHERE job_id=:new.job_id; IF :new.salary NOT BETWEEN v_minsal AND v_maxsal THEN RAISE_APPLICATION_ERROR(-20000,''The salary is not between ''||v_minsal||'' AND ''||v_maxsal); END IF; END trg_dml_emp_salary;62CREATE OR REPLACE TRIGGER trg_update_job_historyAFTER UPDATE OF job_id, department_id ON employeesFOR EACH ROWBEGIN INSERT INTO job_history VALUES(:old.employee_id,:old.hire_date, sysdate, :old.job_id, :old.department_id);END trg_update_job_history;63总结存储过程函数包触发器 |
|
|
|
|
|
|
|
|
|
|