Oracle数据库存储过程函数触发器和包

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,第10章 存储过程、函数、触发器和包,本章要点：,掌握存储过程的创建。,熟练掌握带参数的存储过程的使用。,掌握存储过程的管理。,掌握函数的创建与使用。,了解触发器的类型。,理解触发器的作用。,熟练掌握各种类型的触发器。,了解程序包的创建与使用。,2,10.1 存 储 过 程,存储过程是一组为了完成特定功能的SQL语句集，它大大提高了SQL语句的功能和灵活性。,存储过程经编译后存储在数据库中，所以执行存储过程要比执行存储过程中封装的SQL语句更有效率。,3,10.1.1 创建与调用存储过程,创建存储过程需要使用CREATE PROCEDURE语句，其语法如下：,CREATE OR REPLACE PROCEDURE procedure_name,(parameter IN|OUT|IN OUT data_type),.,IS|AS,declaration_section;,BEGIN,procedure_body;,END procedure_name ;,4,10.1.1 创建与调用存储过程,【例10.1】,创建一个简单的存储过程update_emp，该过程用于将emp表中empno为6500的员工的姓名修改为CANDY，如下：,SQL CREATE PROCEDURE update_emp AS,2 BEGIN,3 UPDATE emp SET ename=CANDY WHERE empno=6500;,4 END update_emp;,SQL/,过程已创建。,【例10.2】,使用EXECUTE语句与CALL语句调用存储过程update_emp，分别如下：,SQL EXECUTE update_emp;,PL/SQL 过程已成功完成。,5,10.1.2 带参数的存储过程,1IN参数的使用,IN参数是指输入参数，由存储过程的调用者为其赋值(也可以使用默认值)。如果不为参数指定模式，则其模式默认为IN。,在调用上述存储过程update_emp2时，就需要为该过程的两个输入参数赋值，赋值的形式主要有如下两种。,(1)不指定参数名,(2)指定参数名,2OUT参数的使用,OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户。使用这种模式的参数，必须在参数后面添加OUT关键字。,3IN OUT参数的使用,IN OUT参数同时拥有IN与OUT参数的特性，它既接受用户的传值，又允许在过程体中修改其值，并可以将值返回。使用这种模式的参数需要在参数后面添加IN OUT关键字。不过，IN OUT参数不接受常量值，只能使用变量为其传值。,6,10.1.2 带参数的存储过程,【例10.3】,创建带IN参数的存储过程update_emp2，为该过程设置两个IN参数，分别用于接受用户提供的empno与ename值，如下：,SQL CREATE PROCEDURE update_emp2,2 (emp_num IN NUMBER,emp_name IN VARCHAR2)AS,3 BEGIN,4 UPDATE emp SET ename=emp_name,5 WHERE empno=emp_num;,6 END update_emp2;,7 /,过程已创建。,7,10.1.2 带参数的存储过程,【例10.4】,调用update_emp2过程，通过该过程将empno为6500的员工的ename修改为XIAOQI，如下：,SQL EXEC update_emp2(6500,XIAOQI);,PL/SQL 过程已成功完成。,【例10.5】,使用指定参数名的形式调用update_emp2过程，如下：,SQL EXEC update_emp2(emp_name=XIAOQI,emp_num=6500);,PL/SQL 过程已成功完成。,8,10.1.2 带参数的存储过程,【例10.6】,创建存储过程select_emp，为该过程设置一个IN参数和一个OUT参数，其中IN参数接受用户提供的empno值，然后在过程体中将该empno对应的ename值传递给OUT参数，如下：,SQL CREATE PROCEDURE select_emp,2 (emp_num IN NUMBER,emp_name OUT VARCHAR2)AS,3 BEGIN,4 SELECT ename INTO emp_name,5 FROM emp WHERE empno=emp_num;,6 END select_emp;,7 /,过程已创建。,9,10.1.2 带参数的存储过程,【例10.7】,调用存储过程select_emp,，为其IN参数赋值为6500，并声明变量employee_ name接受与输出其OUT参数的返回值，如下：,SQL VARIABLE employee_name VARCHAR2(10);,SQL EXEC select_emp(6500,:employee_name);,PL/SQL 过程已成功完成。,然后，需要使用PRINT命令查看变量employee_name中的值，如下：,SQL PRINT employee_name;,EMPLOYEE_NAME,-,XIAOQI,也可以使用SELECT语句查看变量employee_name中的值，语句如下：,SQL SELECT:employee_name FROM dual;,10,带,带参数的,存,存储过程,【例10.8】,创建存储,过,过程exchange_value,，,，通过该,过,过程交换,两,两个变量,中,中的值，,过,过程创建,如,如下：,参见教材P227,调用exchange_value,过,过程，调,用,用前声明,为,为INOUT参,数,数赋值的,变,变量，调,用,用后使用SELECT语句,输,输出交换,值,值后的结,果,果，如下,：,：,参见教材P227,11,修,修改与删,除,除存储过,程,程,修改存储,过,过程是在CREATE PROCEDURE,语,语句中添,加,加ORREPLACE关,键,键字，其,他,他内容与,创,创建存储,过,过程一样,，,，其实质,是,是删除原,有,有过程，,然,然后创建,一,一个全新,的,的过程，,只,只不过前,后,后两个过,程,程的名称,相,相同而已,。,。,删除存储,过,过程需要,使,使用DROP PROCEDURE,语,语句，其,语,语法形式,如,如下：,DROPPROCEDURE procedure_name;,12,查,查询存储,过,过程的定,义,义信息,对于创建,好,好的存储,过,过程，如,果,果想要了,解,解其定义,信,信息，可,以,以查询数,据,据字典user_source。,【例10.9】,通过数据,字,字典user_source查询存,储,储过程select_emp的定义,信,信息，如,下,下：,参见教材P228,其中，name表,示,示对象名,称,称；type表示,对,对象类型,；,；line表示定,义,义信息中,文,文本所在,的,的行数；text,表,表示对应,行,行的文本,信,信息。,13,10.2,函,函,数,数,创建函数,需,需要使用CREATE FUNCTION语,句,句，其语,法,法如下：,CREATE ORREPLACE FUNCTION function_name,(parameter IN|OUT|INOUT data_type),.,RETURN data_type,IS|AS,declaration_section;,BEGIN,function_body;,END function_name;,14,10.3,实验指导,使用,存,存储过程,与,与函数查,询,询图书信,息,息,实验指导10-1,：,：使用存,储,储过程和,函,函数查询,图,图书信息,1创建,函,函数get_prompt,首先创建,函,函数get_prompt,，,，如下：,参见教材P230,2创建,存,存储过程get_book_information,3调用,过,过程,存储过程,和,和函数都,已,已经创建,好,好了，需,要,要查询某,图,图书的信,息,息时就可,以,以直接调,用,用get_book_information存储过,程,程。例如,获,获取bookid,为,为2的图,书,书的信息,，,，如下：,参见教材P231,15,10.4,触,触,发,发,器,器,触发器是,一,一种特殊,的,的存储过,程,程，它在,发,发生某种,数,数据库事,件,件时由Oracle系统自,动,动触发。,触发器通,常,常用于加,强,强数据的,完,完整性约,束,束和业务,规,规则等，,对,对于表来,说,说，触发,器,器可以实,现,现比CHECK约,束,束更为复,杂,杂的约束,。,。,16,触,触发器的,类,类型,DML触,发,发器：DML触发,器,器由DML语句触,发,发，例如INSERT、UPDATE和DELETE,语,语句。,INSTEADOF触发,器,器：INSTEAD OF,触,触发器又,称,称替代触,发,发器，用,于,于执行一,个,个替代操,作,作来代替,触,触发事件,的,的操作。,系统事件,触,触发器：,系,系统事件,触,触发器在,发,发生如数,据,据库启动,或,或关闭等,系,系统事件,时,时触发。,DDL触,发,发器：DDL触发,器,器由DDL语句触,发,发，例如CREATE、ALTER,和,和DROP语句。DDL触,发,发器同样,可,可以分为BEFORE触发,器,器与AFTER触,发,发器。,17,创,创建触发,器,器,创建触发,器,器需要使,用,用CREATETRIGGER语,句,句，其语,法,法如下：,CREATE ORREPLACE TRIGGER trigger_name,BEFORE|AFTER|INSTEAD OF trigger_event,ONtable_name|view_name|DATABASE,FOR EACH ROW,ENABLE|DISABLE,WHEN trigger_condition,DECLARE declaration_statements;,BEGIN,trigger_body;,END trigger_name;,18,10.4.3DML触,发,发器,DML触,发,发器由DML语句,触,触发，其,对,对应的trigger_event,具,具体内容,如,如下：,INSERT|DELETE|UPDATE OFcolumn,.,关于DML触发器,的,的说明如,下,下：,DML操,作,作主要包,括,括INSERT、DELETE和UPDATE操作，,通,通常根据,触,触发器所,针,针对的具,体,体事件将DML触,发,发器分为INSERT触发,器,器、UPDATE,触,触发器和DELETE触发,器,器。,可以将DML操作,细,细化到列,，,，即针对,某,某列进行DML操,作,作时激活,触,触发器。,任何DML触发器,都,都可以按,触,触发时间,分,分为BEFORE,触,触发器与AFTER触发器,。,。,在行级触,发,发器中，,为,为了获取,某,某列在DML操作,前,前后的数,据,据，Oracle,提,提供了两,种,种特殊的,标,标识符,:OLD和:NEW，通,过,过:OLD.column_name的形式,可,可以获取,该,该列的旧,数,数据，而,通,通过:NEW.column_name则可,以,以获取该,列,列的新数,据,据。,19,10.4.3DML触,发,发器,【例10.11】,为了演示,触,触发器的,效,效果，下,面,面首先创,建,建两个简,单,单的示例,表,表：student(学