第22章 存储过程.ppt

第22章存储过程,子程序指能够接受参数并可被其他程序调用用来执行特定操作的PL/SQL块。PL/SQL中，子程序包括两种类型，过程和函数。这一章将介绍PL/SQL中子程序的存储过程。存储过程可以认为是经过编译的，永久保存在数据库中的一组SQL语句。通过创建和使用存储过程，可以提高程序的重用性和扩展性，为程序提供模块化的功能，可以根据实际应用的需要编写用于特定功能的PL/SQL块。另外，使用存储过程还有利于对程序的维护和管理。这一章主要介绍存储过程的创建、调用以及参数传递方面的内容。,第22章存储过程,存储过程的创建；三种参数模式：IN、OUT、INOUT；存储过程的调用方法；存储过程的删除；参数传递的方式。,22.1创建存储过程,在PL/SQL中，存储过程可以认为是一个可以执行某一个特定操作的子程序。一个存储过程包括过程说明和过程体两个部分。其中，过程体可以包括声明部分、可执行部分和异常处理部分。以关键字PROCEDURE开头的是说明部分；以关键字IS或者AS开头，以关键字END结尾的是过程体部分。创建存储过程的语法规则如下：CREATEORREPLACEPROCEDUREprocedure_name(argument1modeldatatype1,argument2modeldatatype2.)IS|ASlocaldeclarationsBEGINexecutablestatementsEXCEPTIONexceptionhandlersENDprocedure_name;,22.2参数模式,在22.1节讲到的创建存储过程的语法规则中，在讲解存储过程的参数时提到了参数模式。在存储过程中通过使用参数模式来定义存储过程中参数（形式参数）的行为。形式参数模式包括3种：IN、OUT、INOUT，默认的参数模式为IN。本节就来介绍这三种参数模式的含义及其使用方法。,22.2.1IN模式,在创建存储过程时，其参数模式的默认值为IN模式。如果该存储过程中的参数模式为IN模式，那么调用该存储过程时，实际参数的值将传递给被调用的存储过程。在该存储过程中，被指定为IN模式的参数（形式参数）的作用相当于一个常量，其值只能被读取，不能为其进行赋值操作。,22.2.1IN模式,创建以标量变量作为输入参数的存储过程，将学生的成绩信息插入到新表中。CREATEORREPLACEPROCEDUREins_result(p_stuIDINt_t_result.stuID%TYPE,p_curIDINt_result.curID%TYPE)ASv_resultINT;-定义表示学生成绩的变量e_illegalValueEXCEPTION;BEGIN/*查询指定学生编号和课程编号的学生成绩*/SELECTresultINTOv_resultFROMt_resultWHEREstuID=p_stuIDANDcurID=p_curID;ENDins_result;,22.2.1IN模式,创建以记录变量作为输入参数的存储过程，向学生表中插入数据记录。CREATEORREPLACEPROCEDUREins_student(student_recordt_student%ROWTYPE)ASBEGIN/*插入学生信息*/INSERTINTOt_studentVALUESstudent_record;EXCEPTIONWHENDUP_VAL_ON_INDEXTHEN-捕获异常DBMS_OUTPUT.PUT_LINE(违反一致性约束);INSERTINTOt_log-将错误信息写入日志表中VALUES(stuIDisnotrepeated!,SYSDATE,admin);WHENOTHERSTHEN-捕获异常DBMS_OUTPUT.PUT_LINE(发生其他错误);INSERTINTOt_log-将错误信息写入日志表中VALUES(Oracleerrort!,SYSDATE,admin);ENDins_student;,22.2.1IN模式,创建以集合变量作为输入参数的存储过程，向学生表中插入数据记录。CREATEORREPLACEPROCEDUREins_studentTable(stuID_tablestuID_table_type,stuName_tablestuName_table_type,age_tableage_table_type,sex_tablesex_table_type,birth_tablebirth_table_type)ASBEGINFORiIN1.stuID_table.COUNTLOOP/*插入学生信息*/INSERTINTOt_studentVALUES(stuID_table(I),stuName_table(I),age_table(I),sex_table(I),birth_table(I);ENDLOOP;EXCEPTIONWHENDUP_VAL_ON_INDEXTHEN-捕获异常DBMS_OUTPUT.PUT_LINE(违反一致性约束);INSERTINTOt_log-将错误信息写入日志表中VALUES(stuIDisnotrepeated!,SYSDATE,admin);WHENOTHERSTHEN-捕获异常DBMS_OUTPUT.PUT_LINE(发生其他错误);INSERTINTOt_log-将错误信息写入日志表中VALUES(Oracleerrort!,SYSDATE,admin);ENDins_studentTable;,22.2.1IN模式,当使用PL/SQL嵌套表作为数据表中数据列的数据类型时，首先需要使有CREATETYPE命令创建一个嵌套表类型，而且还要在创建数据表时为以嵌套表作为数据类型的列指定一个存储表。所以在使用嵌套表作为输入参数之前，还需要分别创建嵌套表类型。CREATETYPEstuID_table_typeISTABLEOFVARCHAR(15);CREATETYPEstuName_table_typeISTABLEOFVARCHAR(10);CREATETYPEage_table_typeISTABLEOFINT;CREATETYPEsex_table_typeISTABLEOFVARCHAR(2);CREATETYPEbirth_table_typeSTABLEOFDATETIME;,22.2.2OUT模式,如果在创建存储过程时，存储过程中的参数模式指定为OUT模式，那么在完成存储过程的调用后，实际参数的值将返回给存储过程的调用者。在该存储过程中，被指定为OUT模式的参数（形式参数）的作用相当于一个变量，该形式参数必须被赋值。其值既可以被读取，也可以被写入。可以把它当作本地变量来使用。说明：如果存储过程中参数的模式定义为OUT，那么在该存储过程调用之前，其参数可以有值存在。当存储过程被调用时，该值就会被忽略。除非使用了NOCOPY编译器提示进行参数传递或是是因为存储过程中有未处理的异常而退出。,22.2.2OUT模式,创建以标量变量作为输出参数的存储过程，将原来没有津贴的教师津贴增加100。CREATEORREPLACEPROCEDUREupdate_pension(p_teaIDt_teacher.teaID%TYPE,-教师编号pensionOUTREAL-教师津贴)ASBEGIN/*查询指定教师编号的教师津贴*/SELECTpensionINTOpensionFROMt_teacherWHEREteaID=p_teaID;/*如果教师津贴为NULL*/IFpensionISNULLTHEN/*修改教师津贴*/pension:=pension+100;ENDIF;ENDupdate_pension;,22.2.2OUT模式,创建以记录变量作为输出参数的存储过程，查询教师记录。CREATEORREPLACEPROCEDUREselect_teacher(p_teaIDt_eacher.teaID%TYPE,p_teacher_recordOUTt_eacher%ROWTYPE)ASBEGIN/*查询教师信息*/SELECTteaName,dept,professionINTOp_teacher_recordFROMt_eacherWHEREteaID=p_teaID;ENDselect_teacher;,22.2.2OUT模式,创建以集合变量作为输出参数的存储过程，查询教师记录CREATEORREPLACEPROCEDUREselect_teacherTable(p_deptIDt_eacher.deptID%TYPE,teaID_tableOUTteaName_table_type,teaName_tableOUTteaName_table_type,dept_tableOUTdept_table_type,profession_tableOUTprofession_table_type,)ASBEGIN/*查询教师信息*/SELECTteaID,teaName,dept,professionINTOteaID_table,teaName_table,dept_table,profession_tableFROMt_eacherWHEREdeptID=p_deptID;ENDselect_teacherTable;,22.2.3INOUT模式,如果在创建存储过程时，存储过程中的参数模式指定为INOUT模式，那么它会向存储过程传递初始值，同时也会向调用者返回更新后的结果值。即在调用该存储过程时，实际参数的值将传递给被调用的存储过程；在完成存储过程的调用后，实际参数的值将返回给存储过程的调用者。在该存储过程中，被指定为INOUT模式的参数（形式参数）的作用相当于一个已初始化的变量，其值既可以被读取，也可以被写入。,22.2.3INOUT模式,计算两个数相除的余数。CREATEORREPLACEPROCEDUREdivide_mod(p_num1INOUTNUMBER,p_num2INOUTNUMBER)ASv_truncNUMBER;-表示除法结果的商v_modNUMBER;-表示除法结果的余数BEGINv_trunc=TRUNC(p_num1/p_num2);v_mod=MOD(p_num1,p_num2);p_num1:=v_trunc;p_num2:=v_mod;EXCEPTIONWHENZERO_DIVIDETHENDBMS_OUTPUT.PUT_LINE(除数值不能为0);WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(发生其他错误);,22.3调用存储过程,当存储过程创建完成之后，就可以调用该存储过程了。在PL/SQL中，可以直接对存储过程进行调用。由于创建的存储过程可以是无参数的，也可以是有参数的，所以存储过程的调用方法也有所不同。本节就来介绍有参数和无参数的情况下存储过程的调用方法。,22.3.1调用无参数的存储过程,在PL/SQL中，如果该存储过程没有参数，可以直接通过存储过程名对其进行调用。例如，对于例22.1中显示当前系统日期和时间的存储过程，在PL/SQL语句块中就可以使用下面的方法对其进行调用。BEGINsysdata_time;END;,22.3.2调用带有输入参数的存储过程,在一个存储过程中，如果该存储过程中带有输入参数，那么在调用该存储过程时，需要为存储过程中的输入参数提供数据值。BEGINins_result(s102203,t105);END;,22.3.2调用带有输入参数的存储过程,以记录变量作为输入参数的存储过程ins_student，在PL/SQL语句块中就可以使用下面的方法对其进行调用。DECLAREstudent_recordt_student%ROWTYPE;BEGINstudent_record.stuID=s131203;student_record.stuName=彭庄;student_record.age=23;student_record.sex=男;student_record.birth=19860212;ins_student(student_record);-调用存储过程END;,22.3.2调用带有输入参数的存储过程,以嵌套表变量作为输入参数的存储过程ins_studentTable，在PL/SQL语句块中就可以使用下面的方法对其进行调用。DECLAREstuID_tablestuID_table_type:=stuID_table_type(s131204,s131205);stuName_tablestuName_table_type:=stuName_table_type(张三,李四);age_tableage_table_type:=age_table_type(21,21);sex_tablesex_table_type:=sex_table_type(男,女);birth_tablebirth_table_type:=birth_table_type(19880313,19880218);BEGINins_studentTable(stuID_table,stuName_table,age_table,sex_table,birth_table);-调用存储过程END;,22.3.3调用带有输出参数的存储过程,在一个存储过程中，如果该存储过程中带有输出参数，那么在完成对该存储过程的调用之后，存储过程中指定为输出参数的形参的值会赋值给实参。,22.3.3调用带有输出参数的存储过程,DECLAREv_pensiont_teacher.pension%TYPE,BEGINupdate_pension(t156354,v_pension);DBMS_OUTPUT.PUT_LINE(v_pension的值为|v_pension);END;,22.3.3调用带有输出参数的存储过程,DECLAREteacher_recordt_teacher%ROWTYPE;BEGINselect_teacher(t156354，teacher_record);-调用存储过程select_teacherDBMS_OUTPUT.PUT_LINE(教师姓名为：|teacher_record.teaName);DBMS_OUTPUT.PUT_LINE(教师所在院系为：|teacher_record.dept);DBMS_OUTPUT.PUT_LINE(教师职称为：|teacher_fession);END;,22.3.3调用带有输出参数的存储过程,DECLAREteaID_tableteaID_table_type;teaName_tableteaName_table_type;dept_tabledept_table_type;profession_tableprofession_table_type;BEGINselect_teacherTable(t_15,teaID_table,teaName_table,dept_table,profession_table);-调用存储过程FORIIN1.teaID_table.COUNTLOOPDBMS_OUTPUT.PUT_LINE(教师编号为：|teaID_table(i);DBMS_OUTPUT.PUT_LINE(教师姓名为：|teaName_table(i);DBMS_OUTPUT.PUT_LINE(教师所在院系为：|dept_table(i);DBMS_OUTPUT.PUT_LINE(教师职称为：|profession_table(i);ENDLOOP;END;,22.3.4调用带有输入输出参数的存储过程,在一个存储过程中，如果该存储过程中带有输入输出参数，那么在调用该存储过程时，实参的值将传递给被调用的存储过程；在完成存储过程的调用后，实参的值将返回给存储过程的调用者。DECLAREn1:=13;n2:=5;BEGINdivide_mod(n1,n2)；DBMS_OUTPUT.PUT_LINE(13除5的商为：|n1);DBMS_OUTPUT.PUT_LINE(13除5的余数为：|n2);,22.4参数传递,存储过程通过使用参数传递信息。PL/SQL中参数传递有两种方式，一种是引用传递，一种是值传递。引用传递是将实参的指针传递给对应的形参中，而值传递是指将实参的值拷贝给了对应的形参。PL/SQL中，参数模式被指定为IN模式的是通过引用传递来传递参数，参数模式被指定为OUT或者INOUT模式的是通过值传递来传递参数。在调用存储过程时，既可以使用参数名称也可以使用参数位置来为实参提供数据。通过参数位置或者参数名称可以把实参和形参关联起来。这一节就来介绍有关存储过程参数传递的内容。,22.4.1使用参数位置传递参数值,在PL/SQL中，可以使用参数位置传递参数变量或者数据。通过使用参数位置传递变量或者数据时，PL/SQL编译器会将第一个实参的值与第一个形参的值相关联，第二个实参的值与第二个形参的值相关联，按照参数定义的顺序依次为形参赋值。例如，对于例22.3中将学生的成绩信息插入到新的数据表的这个存储过程ins_result，通过参照位置传递，可以使用下面的方法对其进行调用。DECLAREv_stuIDVARCHAR2(15)v_curIDVARCHAR22(15)BEGINins_result(v_stuID,v_curID);END;,22.4.2使用参数名称传递参数值,在PL/SQL中，可以使用参数名称传递参数变量或者数据。可以通过使用关联参照符=将实参和形参关联起来。例如，对于例22.3中将学生的成绩信息插入到新的数据表的这个存储过程ins_result，通过参数名称传递，可以使用下面的方法对其进行调用。DECLAREv_stuIDVARCHAR2(15);v_curIDVARCHAR2(15);BEGINins_result(p_stuID=v_stuID,p_curID=v_curID);END;,22.4.3使用位置传和递参数名称参数值,在PL/SQL中，也可以混合使用参数名称和参数位置来传递参数变量或者数据。在混合使用参数名称和参数位置来传递参数变量或者数据时，参数位置传递参数值的方法需要在参数名称传递参数值的方法之前使用。例如，对于例22.3中将学生的成绩信息插入到新的数据表的这个存储过程ins_result，混合使用参数名称和参数位置传递，可以使用下面的方法对其进行调用。DECLAREv_stuIDVARCHAR2(15);v_curIDVARCHAR2(15);BEGINins_result(v_curID,p_stuID=v_stuID);END;,22.4.4使用NOCOPY编译提示传递参数,在PL/SQL中，传递方式有两种，值传递和引用传递。当使用引用传递时，是将实参的指针传递给对应的形参中，因此使用引用传递会使程序的执行速度更快，而值传递是将实参的值拷贝给了对应的形参。对于使用集合或者记录这样的数据结构作为参数来说，如果使用值传递的话，会使程序的执行速缺渎敲炊杂诓问蕉逦OUT或者INOUT模式的集合或者记录来说，有没有什么办法可以让它通过引用的方式传递，从而加快程序的执行效率呢。这就需要使用NOCOPY编译提示。使用NOCOPY编译提示声明一个参数的语法规则如下：argumentmodelNOCOPYdatatype,22.4.4使用NOCOPY编译提示传递参数,其中，argument表示参数的名字；mode表示指定的参数模式，参数模式包括IN、OUT、INOUT三种，默认的参数模式为IN；datatype表示参数的数据类型。,22.4.4使用NOCOPY编译提示传递参数,CREATEORREPLACEPROCEDUREprocudurc_nocopy(p_teaIDINt_teacher.teaID%TYPE,-教师编号p_salaryOUTNOCOPYREAL,-教师工资p_pensionINOUTNOCOPYREAL-教师津贴)AS-过程体代码,22.4.5使用参数的默认值,在PL/SQL中，还可以为IN模式的参数指定一个初始化的默认值，调用存储过程时，就可以不为该参数其他数据。其设置参数默认值的语法规则如下：argumentmodeldatatypeDEFAULT|:=default_value;其中，argument表示的是存储过程的参数；mode表示指定的参数模式；参数模式包括IN、OUT、INOUT三种；datatype表示参数的数据类型；关键字DEFAULT或者是“:=”表示为参数设置一个默认值；default_value是为参数指定的默认值。,22.4.5使用参数的默认值,CREATEORREPLACEPROCEDUREinsert_teacher(p_teaIDt_teacher.teaIID%TYPE,p_teaNamet_teacher.teaIName%TYPE,p_aget_teacher.age%TYPE,p_sext_teacher.sex%TYPE,p_deptIDt_teacher.deptID%TYPEDEFAULTt_10,p_deptt_teacher.dept%TYPEDEFAULT计算机系,p_profes