第七章_PLSQL子程序设计.ppt

Oracle数据库基础,贾素玲王强主编许珂卢泉张国强编著,第七章PL/SQL子程序设计,本章结构,子程序过程函数包触发器数据字典本章小结习题,子程序,子程序PL/SQL程序块可以是一个匿名程序块也可以是一个命名的程序块。Oracle提供了四种类型的子程序：过程、函数、包和触发器。,过程,过程概述createorreplaceprocedure(,)is|aslocaldeclarationbeginexecutablestatementsexceptionexceptionhandlerendprocedurename,过程,参数模式inin表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，当过程结束时，控制会返回控制环境，实际参数的值不会改变。同时也是参数的缺省模式。,参数模式outout在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数。,参数模式inoutinout这种模式是in和out的组合，在过程内部实际参数的值会传递给形式参数，形式参数的值可读也可写，过程结束后，形式参数的值将赋予实际参数。,过程,createorreplaceprocedureemp_empno(v_empnoemp.empno%type)asv_empemp%rowtype;empno_out_of_rangeexception;beginifv_empno=8000thenraiseempno_out_of_range;endif;select*intov_empfromempwhereempno=v_empno;ifSQL%foundthendbms_output.put_line(v_emp.ename);endif;exceptionwhenempno_out_of_rangethenraise_application_error(-20010,empnoisoutofrange);whenno_data_foundthendbms_output.put_line(notfoundrecord);whentoo_many_rowsthendbms_output.put_line(toomanyrecord);endemp_empno;/,过程,createorreplaceprocedureget_ename(v_empnoinemp.empno%type,v_enameoutemp.ename%type)ascursorc_empisselectempno,enamefromempwhereempno=v_empno;v_empc_emp%rowtype;beginopenc_emp;fetchc_empintov_emp;v_ename:=v_emp.ename;closec_emp;endget_ename;/,declarenameemp.ename%type;beginget_ename(7369,name);dbms_output.put_line(name);end;/,过程,declarecursorc_empisselectsalfromemporderbyempno;typesal_typeistableofnumber(7,2)indexbybinary_integer;sal_listsal_type;v_countbinary_integerdefault1;procedureset_sal(sal_listinoutsal_type)asbeginforiin1.sal_list.countloopsal_list(i):=sal_list(i)*1.05;endloop;endset_sal;beginopenc_emp;loopfetchc_empintosal_list(v_count);exitwhenc_emp%notfound;v_count:=v_count+1;endloop;set_sal(sal_list);foriin1.sal_list.countloopdbms_output.put_line(sal_list(i);endloop;end;/,函数,函数,createorreplacefunction(,)returnis|aslocaldeclarationbeginexecutablestatementsexceptionexceptionhandlerendfunctionname,函数,createorreplacefunctionget_sal_percent(v_jobemp.job%type)returnnumberascursorc_empisselectsalfromempwherejob=v_job;v_empc_emp%rowtype;v_salemp.sal%type:=0;v_sal_sumemp.sal%type;beginopenc_emp;loopfetchc_empintov_emp;exitwhenc_emp%notfound;v_sal:=v_sal+v_emp.sal;endloop;closec_emp;selectsum(sal)intov_sal_sumfromemp;v_sal:=v_sal/v_sal_sum;returnv_sal;endget_sal_percent;/,包,包概述包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，它具有面向对象程序设计语言的特点，是对PL/SQL程序设计元素的封装。一个包由两个分开的部分组成，包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中。,包,包概述包定义,createorreplacepackagepackage_nameas|ispublic_variable_declarations|public_type_declarations|public_exception_declarations|public_cursor_declarations|function_declarations|procedure_specificationsendpackage_name,包,包概述包主体,createorreplacepackagebodypackage_nameas|isprivate_variable_declarations|private_type_declarations|private_exception_declarations|private_cursor_declarations|function_declarations|procedure_specificationsendpackage_name,包,创建包,createorreplacepackagep_empasversionvarchar2(11):=version1.0;authorvarchar2(15):=buaasem108xk;procedureemp_info(v_deptnoemp.deptno%type);functionbirthday(v_empnoemp.empno%type)returnbinary_integer;endp_emp;/,createorreplacepackagebodyp_empasprocedureemp_info(v_deptnoemp.deptno%type)ascursorc_empisselect*fromempwheredeptno=v_deptno;v_empc_emp%rowtype;beginopenc_emp;loopfetchc_empintov_emp;exitwhenc_emp%notfound;dbms_output.put_line(v_emp.empno|,|v_emp.ename|,|v_emp.job|,|to_char(v_emp.hiredate,YYYY-MM-DD)|,|v_emp.sal|,|v_m);endloop;closec_emp;end;functionbirthday(v_empnoemp.empno%type)returnbinary_integerassys_datevarchar2(5);emp_datevarchar2(5);tempdate;lenbinary_integer;beginselecthiredateintotempfromempwhereempno=v_empno;emp_date:=substr(to_char(temp,YYYY-MM-DD),6,5);sys_date:=substr(to_char(SYSDATE,YYYY-MM-DD),6,5);foriin1.5loopifsubstr(emp_date,i,1)substr(sys_date,i,1)thenreturn-1;endif;endloop;return1;endbirthday;endp_emp;/,包,使用DBMS_SQL包先将要执行的SQL语句或一个语句块放到一个字符串变量中。使用DBMS_SQL包的parse过程来分析该字符串。使用DBMS_SQL包的bind_variable过程来绑定变量。使用DBMS_SQL包的execute函数来执行语句。,包,createorreplaceprocedureproc_dbms_sql(table_namevarchar2,-表名field_name1varchar2,-字段名datatype1varchar2,-字段类型field_name2varchar2,-字段名datatype2varchar2-字段类型)asv_cursornumber;-定义光标v_stringvarchar2(200);-定义字符串变量v_rownumber;-行数beginv_cursor:=dbms_sql.open_cursor;-为处理打开光标v_string:=createtable|table_name|(|field_name1|datatype1|,|field_name2|datatype2|);dbms_sql.parse(v_cursor,v_string,dbms_sql.native);-分析语句v_row:=dbms_sql.execute(v_cursor);-执行语句dbms_sql.close_cursor(v_cursor);-关闭光标end;/,触发器,触发器概述触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如insert、update和delete操作时，Oracle就会自动执行触发器所定义的SQL语句，从而确保对数据的处理符合由这些SQL语句所定义的规则。,createorreplacetrigger触发器名触发时间触发事件ontable_or_view_name|databasereferencingoldasnewasforeachrowwhenconditiontrigger_body,触发器,createtableemp1asselect*fromemp;truncatetableemp1;,createsequenceseq_emp1startwith1incrementby1maxvalue9999999cycle;,createtableemp1_log(act_idnumber(7),act_timedate,act_descptionvarchar2(20);,createorreplacetriggert_emp1afterinsertonemp1foreachrowbegininsertintoemp1_logvalues(seq_emp1.nextval,SYSDATE,InsetedOK!);end;/,insertintoemp1(empno)values(7369);insertintoemp1(empno)values(7370);insertintoemp1(empno)values(7371);insertintoemp1(empno)values(7372);insertintoemp1(empno)values(7373);,数据字典,数据字典视图名称：user_source属性：type属性值：procedure,function,package,trigger,数据字典,selecttextfromuser_sourcewherename=upper(set_sal)andtype=upper(procedure);,数据字典,selecttextfromuser_sourcewherename=upper(get_sal_percent)andtype=upper(function);,数据字典,selecttextfromuser_sourcewherename=upper(p_emp)andtype=upper(package);,selecttextfromuser_sourcewherename=upper(t_emp_dept)andtype=upper(trigger);,本章小结,过程是一个PL/SQL程序块，接受零个或多个参数作为输入或输出、或既作输入又作输