数据库课件：第10章PLSQL程序开发

1、1第第10章章 PL/SQL命名对象命名对象2本章内容本章内容l存储过程l函数l包l触发器310.1 存储过程存储过程l创建存储过程l调用存储过程l案例数据库中存储过程的创建410.1.1 创建存储过程创建存储过程l基本语法CREATE OR REPLACE PROCEDURE procedure_name(parameter1_name mode datatype DEFAULT|:=value, parameter2_name mode datatype DEFAULT|:=value,)AS|IS /*Declarative section is here */BEGIN /*Execu

2、table section is here*/ EXCEPTION /*Exception section is here*/ ENDprocedure_name; 5l参数说明 参数的模式参数的模式 pIN（默认参数模式）表示当过程被调用时，实参值被传递给形参；在过程内，形参起常量作用，只能读该参数，而不能修改该参数；当子程序调用结束返回调用环境时，实参没有被改变。IN模式参数可以是常量或表达式。pOUT表示当过程被调用时，实参值被忽略；在过程内，形参起未初始化的PL/SQL变量的作用，初始值为NULL，可以进行读/写操作；当子程序调用结束后返回调用环境时，形参值被赋给实参。OUT模式参数只

3、能是变量，不能是常量或表达式。pIN OUT表示当过程被调用时，实参值被传递给形参；在过程内，形参起已初始化的PL/SQL变量的作用，可读可写；当子程序调用结束返回调用环境时，形参值被赋给实参。IN OUT模式参数只能是变量，不能是常量或表达式。 6参数的限制参数的限制p在声明形参时，不能定义形参的长度或精度、刻度，它们是作为参数传递机制的一部分被传递的，是由实参决定的。参数传递方式参数传递方式p当子程序被调用时，实参与形参之间值的传递方式取决于参数的模式。IN参数为引用传递，即实参的指针被传递给形参；OUT，IN OUT参数为值传递，即实参的值被复制给形参。参数默认值参数默认值p可以为参数设

4、置默认值，这样存储过程被调用时如果没有给该参数传递值，则采用默认值。需要注意，有默认值的参数应该放在参数列表的最后。 7l创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。lCREATE OR REPLACE PROCEDURE proc_show_emp(l p_deptno employees.department_id%TYPE)lASl v_sal employees.salary%TYPE;lBEGINl SELECT avg(salary) INTO v_sal FROM employees l WHERE department_i

5、d=p_deptno;l DBMS_OUTPUT.PUT_LINE(p_deptno| |l average salary is: |v_sal);l FOR v_emp IN (SELECT * FROM employees l WHERE department_id=p_deptno AND salaryv_sal)LOOPl DBMS_OUTPUT.PUT_LINE(v_emp.employee_id| |l v_emp.first_name| |v_emp.last_name);l END LOOP;lEXCEPTIONl WHEN NO_DATA_FOUND THENl DBMS_O

6、UTPUT.PUT_LINE(The department doesnt exists! );lEND proc_show_emp;8l通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用OUT或IN OUT模式参数来实现。9l创建一个存储过程，以部门号为参数，以部门编号为参数返回该部门的人数和平均工资。l lCREATE OR REPLACE PROCEDURE proc_return_deptinfo(l p_deptno employees.department_id%TYPE,l p_avgsal OUT employees.salar

7、y%TYPE,l p_count OUT NUMBER)lASlBEGINl SELECT avg(salary),count(*) INTO p_avgsal,p_count FROM employeesl WHERE department_id=p_deptno;lEXCEPTIONl WHEN NO_DATA_FOUND THENl DBMS_OUTPUT.PUT_LINE(The department dont exists! );lEND proc_return_deptinfo;1010.1.2 调用存储过程调用存储过程l在SQL*PLUS中调用EXEC procedure_nam

8、e(parameter_list)EXECUTE proc_show_emp(10)l在PL/SQL块中调用BEGIN procedure_name(parameter_list);END；l注意在在PL/SQL程序中，存储过程可以作为一个独立的程序中，存储过程可以作为一个独立的表达式被调用。表达式被调用。 11lDECLAREl v_avgsal emp.sal%TYPE;l v_count NUMBER;lBEGINl proc_show_emp(20);l proc_return_deptinfo(10,v_avgsal,v_count);l DBMS_OUTPUT.PUT_LINE(v

9、_avgsal| | l v_count);lEND; 1210.1.3 案例数据库中存储过程的创建案例数据库中存储过程的创建l创建名为“PROC_SECURE_DML”的存储过程，检查当前用户操作时间是否为工作时间，即非周六、周日，时间为08：00-18：00。lCREATE OR REPLACE PROCEDURE proc_secure_dmllISlBEGINl IF TO_CHAR (SYSDATE, HH24:MI) NOT BETWEEN 08:00 l AND 18:00 OR TO_CHAR (SYSDATE, DY, l NLS_DATE_LANGUAGE=AMERICAN

10、) IN (SAT, SUN) l THENl RAISE_APPLICATION_ERROR (-20205,只能在正常的工作时l 间内进行改变。);l END IF;lEND proc_secure_dml; 13l创建名为“PROC_JOB_CHANGE”的存储过程，实现员工职位的调动。lCREATE OR REPLACE PROCEDURE proc_job_change(l p_employee_id employees.employee_id%type,l p_new_job_title jobs.job_title%type)lASl v_old_job_id jobs.job_

11、id%type;l v_old_job_title jobs.job_title%type;l v_new_job_id jobs.job_id%type;lBEGINl SELECT job_id INTO v_old_job_id FROM employees WHERE employee_id=p_employee_id;l SELECT job_title INTO v_old_job_title FROM jobs WHERE job_id=v_old_job_id;l IF v_old_job_title=p_new_job_title THENl RAISE_APPLICATIO

12、N_ERROR(-20001,the new job title is as same as before!);l END IF;l SELECT job_id INTO v_new_job_id FROM jobs WHERE job_title=p_new_job_title;l UPDATE employees SET job_id=v_new_job_id WHERE employee_id=p_employee_id;l COMMIT;lEXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20002,The job

13、 title does not exists!);lEND proc_job_change; 14l创建名为“PROC_DEPARTMENT_CHANGE”的存储过程，实现员工部门的调动。lCREATE OR REPLACE PROCEDURE proc_department_change(l p_employee_id employees.employee_id%type,l p_new_department_name departments.department_name%type)l ASl v_old_department_id departments.department_id%ty

14、pe;l v_old_department_name departments.department_name%type;l v_new_department_id departments.department_id%type;l BEGINl SELECT department_id INTO v_old_department_id FROM employees l WHERE employee_id=p_employee_id;l SELECT department_name INTO v_old_department_name FROM departments l WHERE depart

15、ment_id=v_old_department_name;l IF v_old_department_name=p_new_department_name THENl RAISE_APPLICATION_ERROR(-20001,the new department name is as same as before!);l END IF;l SELECT department_id INTO v_new_department_id FROM departments l WHERE department_name=p_new_department_name;l UPDATE employee

16、s SET department_id=v_new_department_id l WHERE employee_id=p_employee_id;l COMMIT;l EXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20002,The department name does not exists!);l END proc_department_change; 1510. 2函数函数l创建函数l调用l案例数据库中函数的创建1610.2.1 创建函数创建函数l基本语法为 CREATE OR REPLACE FUNCTIO

17、N function_name (parameter1_name mode datatype DEFAULT|:=value, parameter2_name mode datatype DEFAULT|:=value,)RETURN return_datatype AS|IS /*Declarative section is here */BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END function_name; 17l注意在函数定义的头部，参数列表之后，必须包含一个在函数定义

18、的头部，参数列表之后，必须包含一个RETURN语句来指明函数返回值的类型，但不能约束语句来指明函数返回值的类型，但不能约束返回值的长度、精度、刻度等。如果使用返回值的长度、精度、刻度等。如果使用%TYPE，则可以隐含地包括长度、精度、刻度等约束信息；则可以隐含地包括长度、精度、刻度等约束信息；在函数体的定义中，必须至少包含一个在函数体的定义中，必须至少包含一个RETURN 语语句，来指明函数返回值。也可以有多个句，来指明函数返回值。也可以有多个RETURN语句，语句，但最终只有一个但最终只有一个RETURN语句被执行。语句被执行。18l创建名为“FUNC_DEPT_MAXSAL”的函数，以部门

19、编号为参数，返回部门最高工资。l lCREATE OR REPLACE FUNCTION func_dept_maxsal(l p_deptno employees.department_id%TYPE)l RETURN employees.salary%TYPElASl v_maxsal employees.salary%TYPE;lBEGINl SELECT max(salary) INTO v_maxsal FROM employees l WHERE department_id=p_deptno;l RETURN v_maxsal;lEXCEPTION l WHEN NO_DATA_F

20、OUND THENl DBMS_OUTPUT.PUT_LINE(The deptno is invalid! );lEND func_dept_maxsal;19l如果需要函数返回多个值，可以使用OUT或IN OUT模式参数。20l创建一个名为“FUNC_DEPT_INFO”的函数，以部门号为参数，返回部门名、部门人数及部门平均工资。 lCREATE OR REPLACE FUNCTION func_dept_info(l p_deptno departments.department_id%TYPE,lp_num OUT NUMBER,lp_avg OUT NUMBER)l RETURN d

21、epartments.department_name%TYPElASl v_dname departments.department_name%TYPE;lBEGINl SELECT department_name INTO v_dname FROM departments lWHERE department_id=p_deptno;l SELECT count(*),avg(salary) INTO p_num,p_avg FROM employees lWHERE department_id=p_deptno;l RETURN v_dname;lEND func_dept_info; 21

22、10.2.2 调用函数调用函数l在SQL语句中调用函数l在PL/SQL中调用函数l注意函数只能作为表达式的一部分被调用。函数只能作为表达式的一部分被调用。l示例通过通过func_dept_maxsal函数的调用，输出各个部函数的调用，输出各个部门的最高工资；门的最高工资；通过通过func_dept_info函数调用，输出各个部门名、函数调用，输出各个部门名、部门人数及平均工资。部门人数及平均工资。 22lDECLAREl v_maxsal employees.salary%TYPE;l v_avgsal employees.salary%TYPE;l v_num NUMBER;l v_dnam

23、e departments.department_name%TYPE;lBEGINl FOR v_dept IN (SELECT DISTINCT department_id FROM l employees WHERE department_id IS NOT NULL) LOOPl v_maxsal:=func_dept_maxsal(v_dept.department_id); l v_dname:=func_dept_info(v_dept.department_id,l v_num, v_avgsal);l DBMS_OUTPUT.PUT_LINE(v_dname| |v_maxsa

24、l| |l v_avgsal| |v_num);l END LOOP;lEND;23l函数可以在SQL语句的以下部分调用：SELECT语句的目标列；语句的目标列；WHERE和和HAVING子句；子句；CONNECT BY，START WITH，ORDER BY，GROUP BY子句；子句；INSERT语句的语句的VALUES子句中；子句中；UPDATE语句的语句的SET子句中。子句中。 2410.2.3 案例数据库中函数的创建案例数据库中函数的创建l创建名为“FUNC_EMP_SALARY”的函数，以员工编号为参数，返回员工的工资。lCREATE OR REPLACE FUNCTION fun

25、c_emp_salary(l p_empno employees.employee_id%type)lRETURN employees.salary%typelASl v_sal employees.salary%type;lBEGINl SELECT salary INTO v_sal FROM employees l WHERE employee_id=p_empno;l RETURN v_sal;lEXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20000,There is not l such an employ

26、ee!);lEND func_emp_salary;25l创建名为“FUNC_EMP_DEPT_AVGSAL”的函数，以员工编号为参数，返回该员工所在部门的平均工资。lCREATE OR REPLACE FUNCTION func_emp_dept_avgsal(lp_empno employees.employee_id%type)lRETURN employees.salary%typelASl v_deptno employees.department_id%type;l v_avgsal employees.salary%type;lBEGINl SELECT department_i

27、d INTO v_deptno FROM employees l WHERE employee_id=p_empno;l SELECT avg(salary) INTO v_avgsal FROM employees l WHERE department_id=v_deptno;l RETURN v_avgsal;lEXCEPTIONl WHEN NO_DATA_FOUND THENl RAISE_APPLICATION_ERROR(-20000,There is not such l an employee!);lEND func_emp_dept_avgsal;2610.3 包包l创建包l

28、调用包27l包是包含一个或多个子程序单元（过程、函数等）的容器。l包构成包规范：声明了软件包中所有内容，如过程、函数、游包规范：声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。信息，不包含任何子程序代码。 包体：包含了在包头中的过程和函数的实现代码。包体包体：包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，但是它们是私有元素，只能由同一异常、过程和函数，但是它们是私有元素，

29、只能由同一包体中其他过程和函数使用。包体中其他过程和函数使用。2810.3.1 创建包创建包l创建包规范 l创建包体 29（1）创建包规范）创建包规范l语法CREATE OR REPLACE PACKAGE package_name IS|ASPRAGMA SERIALLY_RESUABLE type_definition|variable_declaration| exception_declaration|cursor_declaration| procedure_ declaration|function_ declarationEND package_name;30l注意：元素声明的顺

30、序可以是任意的，但必须先声明元素声明的顺序可以是任意的，但必须先声明后使用；后使用；所有元素是可选的；所有元素是可选的；过程和函数的声明只包括原型，不包括具体实过程和函数的声明只包括原型，不包括具体实现。现。31l创建一个软件包，包括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(

31、p_empno NUMBER,p_sal NUMBER);END pkg_emp; 32l语法CREATE OR REPLACE PACKAGE BODY package_name IS|ASPRAGMA SERIALLY_RESUABLE type_definition|variable_declaration| exception_declaration| cursor_declaration| procedure_definition | function_definitionEND package_name; （2）创建包体）创建包体 33l注意：包体中函数和过程的原型必须与包规范中的

32、声包体中函数和过程的原型必须与包规范中的声明完全一致；明完全一致；只有在包规范已经创建的条件下，才可以创建只有在包规范已经创建的条件下，才可以创建包体；包体；如果包规范中不包含任何函数或过程，则可以如果包规范中不包含任何函数或过程，则可以不创建包体。不创建包体。 34lCREATE OR REPLACE PACKAGE BODY pkg_emplASl PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)l ASl BEGINl SELECT min(salary), max(salary) INTO minsal,maxsal l FROM e

33、mployees;l IF p_sal BETWEEN minsal AND maxsal THENl UPDATE employees SET salary=p_sal l WHERE employee_id=p_empno;l IF SQL%NOTFOUND THENl RAISE_APPLICATION_ERROR(-20000,l The employee doesnt exist);l END IF;l ELSEl RAISE e_beyondbound;l END IF;l EXCEPTIONl WHEN e_beyondbound THENl DBMS_OUTPUT.PUT_LI

34、NE(The salary is beyond bound! );l END update_sal; 35lPROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)l ASl BEGINl SELECT min(salary), max(salary) INTO minsal,maxsal l FROM employees;l IF p_sal BETWEEN minsal AND maxsal THENl INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,sal

35、ary)lVALUES(p_empno,Smith,sysdate, ST_MAN,p_sal);l ELSEl RAISE e_beyondbound;l END IF;l EXCEPTIONl WHEN e_beyondbound THENl DBMS_OUTPUT.PUT_LINE(The salary is beyond bound! );l END add_employee; lEND pkg_emp;3610.3.2调用包调用包l在包规范声明的任何元素是公有的，在包外都是可见的包外：通过包外：通过package.element形式调用；形式调用；包内：直接通过元素名进行调用。包内：

36、直接通过元素名进行调用。 l在包体中定义而没有在包头中声明的元素是私有的，只能在包体中引用 37l调用包pkg_emp中的过程update_sal，修改150号员工工资为8000。l调用add_employee添加一个员工号为2011，工资为9000的员工 BEGIN pkg_emp.update_sal(150,8000); pkg_emp.add_employee(2011,9000);END;3810.4 触触 发发 器器l触发器概述 lDML触发器概述l创建DML触发器l变异表触发器 l案例数据库触发器的创建3910.4.1 DML触发器概述触发器概述l触发器是一种特殊类型的存储过程，

37、编译后存储在数据库服务器中。l当特定事件发生时，由系统自动调用执行，而不能由应用程序显式地调用执行。l触发器主要用于维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束，并对数据库中特定事件进行监控和响应。40触发器的类型触发器的类型 lDML触发器建立在基本表上的触发器建立在基本表上的触发器响应基本表的响应基本表的INSERT，UPDATE，DELETE操作。操作。lINSTEAD OF触发器建立在视图上的触发器建立在视图上的触发器响应视图上的响应视图上的INSERT，UPDATE，DELETE操作。操作。l系统触发器建立在数据库系统或模式上的触发器建立在数据库系统或模式上的触发器响应

38、系统事件响应系统事件(STARTUP，SHUTDOWN，SERVERERROR，LOGON，LOGOFF)和和DDL（CREATE，ALTER，DROP）操作。）操作。 41触发器组成触发器组成 l触发器由触发器头部和触发器体两个部分组成l触发器头部（触发器何时被调用）作用对象：触发器作用的对象包括表、视图、数据库作用对象：触发器作用的对象包括表、视图、数据库和模式。和模式。触发事件：激发触发器执行的事件。如触发事件：激发触发器执行的事件。如DML、DDL、数据库系统事件等。数据库系统事件等。触发时间：用于指定触发器在触发事件完成之前还是触发时间：用于指定触发器在触发事件完成之前还是之后执行。

39、如果指定为之后执行。如果指定为AFTER，则表示先执行触发事，则表示先执行触发事件，然后再执行触发器；如果指定为件，然后再执行触发器；如果指定为BEFORE，则表，则表示先执行触发器，然后再执行触发事件。示先执行触发器，然后再执行触发事件。10.4.2 DML触发器概述触发器概述lDML触发器包括语句级前触发器、语句级后触发器、行级前触发器、行级后触发器4大类，其执行的顺序如下。（1）如果存在，则执行语句级前触发器。）如果存在，则执行语句级前触发器。（2）对于受触发事件影响的每一个记录：）对于受触发事件影响的每一个记录：p如果存在，则执行行级前触发器；p执行当前记录的DML操作（触发事件）；p

40、如果存在，则执行行级后触发器。（3）如果存在，则执行语句级后触发器。）如果存在，则执行语句级后触发器。4310.4.3 创建创建DML触发器触发器lDML触发器语法 CREATE OR REPLACE TRIGGER trigger_nameBEFORE|AFTER triggering_event OF column_nameON table_nameFOR EACH ROWWHEN trigger_conditionDECLARE /*Declarative section is here */BEGIN /*Exccutable section si here*/ EXCEPTION /

41、*Exception section is here*/ END trigger_name;Trigger_body44l创建触发器，保证非工作时间禁止对EMPLOYEES表进行DML操作。CREATE OR REPLACE TRIGGER trg_secure_empBEFORE INSERT OR UPDATE OR DELETEON employeesBEGINIF TO_CHAR (SYSDATE, HH24:MI) NOT BETWEEN 08:00 AND 18:00 OR TO_CHAR (SYSDATE, DY, NLS_DATE_LANGUAGE=AMERICAN) IN (

42、SAT, SUN) THENRAISE_APPLICATION_ERROR (-20005,只能在正常的工只能在正常的工作时间内进行改变。作时间内进行改变。);END IF;END trg_secure_emp; （1）创建语句级创建语句级DML触发器触发器45p如果触发器响应多个DML事件，而且需要根据事件的不同进行不同的操作，则可以在触发器体中使用3个条件谓词。谓词行为INSERTING 如果触发语句是INSERT，则为TRUE；否则为FALSEUPDATING如果触发语句是UPDATE，则为TRUE；否则为FALSEDELETING如果触发语句是DELETE，则为TRUE；否则为FALS

43、E46l为employees表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行插入操作时，统计操作后员工人数；当执行更新员工工资操作时，统计更新后员工平均工当执行更新员工工资操作时，统计更新后员工平均工资；资；当执行删除员工操作时，统计删除后各个部门的员工当执行删除员工操作时，统计删除后各个部门的员工人数。人数。 47lCREATE OR REPLACE TRIGGER trg_emp_dmllAFTER INSERT OR UPDATE OR DELETE ON employeeslDECLAREl v_count NUMBER;l v_sal NUMBER(6,2);lBEGI

44、Nl IF INSERTING THEN l SELECT count(*) INTO v_count FROM employees;l DBMS_OUTPUT.PUT_LINE(v_count);l ELSIF UPDATING THENl SELECT avg(salary) INTO v_sal FROM employees;l DBMS_OUTPUT.PUT_LINE(v_sal);l ELSEl FOR v_dept IN (SELECT department_id,count(*) num l FROM employees GROUP BY department_id) LOOPl

45、 DBMS_OUTPUT.PUT_LINE(v_dept.department_id| |l v_dept.num);l END LOOP;l END IF;lEND trg_emp_dml; 48（2）创建行级创建行级DML触发器触发器l行级触发器是指执行DML操作时，每操作一个记录，触发器就执行一次，一个DML操作涉及多少个记录，触发器就执行多少次。l在行级触发器中可以使用WHEN条件，进一步控制触发器的执行。l在行级触发器中引入了:old和:new 两个标识符，来访问和操作当前被处理记录中的数据。 49p:old和:new标识符n:old和:new作为triggering_table%R

46、OWTYPE类型的两个变量n在不同触发事件中，:old和:new的意义不同触发事件:old:newINSERT未定义，所有字段都为NULL当语句完成时，被插入的记录UPDATE更新前原始记录当语句完成时，更新后的记录DELETE记录被删除前的原始值未定义，所有字段都为NULL50l引用方式： :old.field和:new.field （执行部分） old.field 和new.field (WHEN条件中)l注意事项：是伪记录，不能作为整个记录进行赋值或引用是伪记录，不能作为整个记录进行赋值或引用 不能传递给带不能传递给带triggering_table%ROWTYPE参数参数的过程和函数的过程和函数 如果触发器是建立在嵌套表上，如果触发器是建立在嵌套表上，;old和和;new都执行嵌都执行嵌套表的行，套表的行，: :parent指向父表中的当前行。指向父表中的当前行。 51l为employees表创建一个触发器，当插入新员工时显示新员工的员工号、员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号、员