7存储过程和包.

1存储过程和包Oracle数据库应用技术2教学要求掌握过程，函数的概念掌握过程，函数的定义和使用掌握动态SQL的定义和使用掌握包的定义和使用3重点难点过程和函数的定义和使用（难点）动态SQL的定义和使用（难点）包的定义和使用（难点）4过程（存储过程）前面编写执行的PL/SQL程序，共同的特点是没有名称，只能存储为文件，然后通过执行文件的方式执行，因此称为无名块。与此对应的是在PL/SQL中也引入了高级程序设计的一些概念，其中最重要的就是过程。过程就是高级程序设计语言中的模块的概念，将一些内部联系的命令组成一个个过程，通过参数在过程之间传递数据是模块化设计思想的重要内容。5过程的语法结构完整的过程结构如下：CREATEORREPLACEPROCEDURE过程名IS声明语句段;BEGIN执行语句段;EXCEPTION异常处理语句段;END;6过程的特点过程是有名称的程序块，is关键词代替了无名块的Declare。因此在过程中不能使用Declare子句7setserveroutputoncreateorreplaceproceduretempuser.tempprocedureistempdatetempuser.testtable.currentdate%type;beginselectcurrentdateintotempdatefromtesttablewhererecordnumber=88;dbms_output.put_line(to_char(tempdate));end;创建过程实例8创建过程并不会直接输出结果，只是和创建其他数据库对象一样，是一个数据定义命令。创建过程实例9查询过程1.使用项目管理器登录【企业管理器】，在【管理目标导航树】里选择【数据库】/【ORA】/【方案】/【源类型】/【过程】选项。2.使用SQL语句使用系统表查询：selecttextfromuser_sourcewherename=‘P1’10执行过程要执行创建的过程，必须通过主程序来调用过程。setserveroutputonbegintempprocedure;end;11带参数的过程前面介绍的过程没有参数，主程序和过程没有数据的传递，下面介绍带参数的过程的设计和使用。12在PL/SQL过程中，可以有3种类型的参数。in参数：读入参数，主程序向过程传递参数值。out参数：读出参数，过程向主程序传递参数值。inout参数：双向参数，过程与主程序双向交流数据。参数类型13CREATEORREPLACEPROCEDUREshow_line(ip_line_lengthINNUMBER,ip_separatorINVARCHAR2)ISactual_lineVARCHAR2(150);BEGINFORidxin1..ip_line_lengthLOOPactual_line:=actual_line||ip_separator;ENDLOOP;DBMS_OUTPUT.PUT_LINE(actual_line);EXCEPTIONWHENOTHERSTHENdbms_output.put_line(SQLERRM);END;带参数的过程14DECLAREv_lengthNUMBER:=50;v_separatorVARCHAR2(1):=‘*';BEGINshow_line(v_length,v_separator);END;使用带参数的过程15DECLAREv_lengthNUMBER:=50;v_separatorVARCHAR2(1):='=';BEGINshow_line(ip_line_length=v_length,ip_separator=v_separator);END;使用带参数的过程16函数的定义与过程的定义主要的不同就在于签名。函数的签名比过程的签名多了一个Return子句，该子句指定了函数的返回值的类型。定义函数时必须指定其返回类型函数的创建和使用17CREATEORREPLACEFUNCTIONf_line(ip_line_lengthINNUMBER,ip_separatorINVARCHAR2)RETURNVARCHAR2ISactual_lineVARCHAR2(150);BEGINFORidxin1..ip_line_lengthLOOPactual_line:=actual_line||ip_separator;ENDLOOP;RETURN(actual_line);EXCEPTIONWHENOTHERSTHENdbms_output.put_line(SQLERRM);RETURN(null);END;18DECLAREv_lengthNUMBER:=50;v_separatorVARCHAR2(1):='=';BEGINdbms_output.put_line(f_line(ip_line_length=v_length,ip_separator=v_separator));END;使用函数19CREATEORREPLACEPROCEDUREshow_line2(ip_line_lengthINNUMBER,ip_separatorINVARCHAR2,op_lineOUTVARCHAR2)ISactual_lineVARCHAR2(150);BEGINFORidxin1..ip_line_lengthLOOPactual_line:=actual_line||ip_separator;ENDLOOP;op_line:=actual_line;EXCEPTIONWHENOTHERSTHENdbms_output.put_line(SQLERRM);op_line:=null;END;返回结果的存储过程20DECLAREv_lengthNUMBER:=50;v_separatorVARCHAR2(1):='=';v_lineVARCHAR2(150);BEGINshow_line2(v_length,v_separator,v_line);dbms_output.put_line(v_line);END;调用返回结果的存储过程21CREATEORREPLACEPROCEDUREshow_line3(ip_line_lengthINNUMBERDEFAULT50,ip_separatorINVARCHAR2DEFAULT'=')ISactual_lineVARCHAR2(150);BEGINFORidxin1..ip_line_lengthLOOPactual_line:=actual_line||ip_separator;ENDLOOP;DBMS_OUTPUT.PUT_LINE(actual_line);EXCEPTIONWHENOTHERSTHENdbms_output.put_line(SQLERRM);END;定义带缺省值的存储过程22练习作一存储过程和函数，完成下面的功能：输入姓名，课程名，成绩该过程完成对SC表的插入或修改操作，若插入成功，返回成功信息，若该选课信息已经存在，则修改其成绩为输入的成绩，若遇系统错误，返回错误信息。23--传入学生学号，得出学生的相关信息createorreplacefunctionf_get_strM(vsnovarchar2)returnvarchar2isv_strMvarchar2(300);beginselectrpad('学号：'||sno,12,'')||rpad('姓名：'||sname,15,'')||rpad('系别：'||sdept,20,'')intov_strmfromstudentwheresno=vsno;return(v_strm);exceptionwhenno_data_foundthenv_strm:='没有对应的学生';return(v_strm);whenothersthenv_strm:=sqlerrm;return(v_strm);end;--在查询中调用上面函数selectf_get_strm(sno)学生基本信息fromstudent;函数的应用实例24--在System下创建如下过程createorreplaceprocedurep1isvsnamevarchar2(20);beginselectsnameintovsnamefromstudentwheresno='001';dbms_output.put_line(vsname);end;--创建u1用户createuseru1identifiedbyu1;grantconnecttou1;--在System下创建如下过程Grantexecuteonp1tou1;U1在没有对Student的访问权时，是否能通过执行P1，查看查询结果？存储过程权限25动态sql简介静态SQL与动态SQL1静态SQLOracle编译PL/SQL程序块为前期联编（earlybinding），即SQL语句在程序编译期间就已经确定2动态SQLOracle编译PL/SQL程序块为后期联编（latebinding），即SQL语句只有在运行阶段才能建立26动态sql简介为什么用动态SQL：举例：当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。执行DDL语句27动态sql开发动态SQL既然是一种”不确定”的SQL，因此其执行就有其相应的特点Oracle中提供了Executeimmediate语句来执行动态SQL，语法如下：28动态sql简介Excuteimmediate动态SQL语句into输出参数列表using绑定参数列表29动态sql简介对这一语句作如下说明：1)动态SQL是指DDL和不确定的DML（即带参数的DML）2)绑定参数列表为输入参数列表，即其类型为in类型，在运行时刻与动态SQL语句中的参数（可以理解为函数里面的形式参数）进行绑定。3)输出参数列表为动态SQL语句执行后返回的参数列表。4)由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性。30动态sql简介设数据库中表STUDENT其数据为如下：SNOSNAMESAGE001S0121002S0221003S032231动态sql简介要求：1．创建该表并输入相应的数据。2．根据SNO可以查询到SNAME和SAGE的信息。3．根据大于SAGE的查询相应的信息。根据要求，可使用动态SQL创建三个过程实现：32动态sql简介1.创建该表并输入相应的数据。createorreplaceprocedurecreate_tableasbeginexecuteimmediate'createtableSTUDENT(SNOvarchar2(3),SNAMEvarchar2(20),SAGEnumber)';--动态SQLexecuteimmediate'insertintoSTUDENTvalues(''001'',''S01'',21)';executeimmediate'insertintoSTUDENTvalues(''002'',''S02'',21)';executeimmediate'insertintoSTUDENTvalues(''003'',''S03'',22)';Commit;endcreate_table;33动态sql简介思考：下面的程序对吗？createorreplaceprocedurecreate_tableasbeginexecuteimmediate'createtableSTUDENT(SNOvarchar2(3),SNAMEvarchar2(20),AGEnumber)';--动态SQLinsertintoSTUDENTvalues(001,'S01',21);insertintoSTUDENTvalu