第九讲 存储过程与函数.ppt

第九讲 存储过程与函数 主讲：申友访 邮箱：10bigbull@163.com 本讲学习目标 熟练掌握： 存储过程； 函数。 子程序简介 子程序与PL/SQL匿名程序块类似，但又不同于PL/SQL匿名程序块，子程序有一个声明部分、一个可执行部分和一个可选异常处理部分。声明部分由类型、游标、常量、变量、异常和嵌套子程序的声明组成。这些对象为局部的且当从子程序中退出时，它们将终止。可执行部分由赋值、控制执行和操作Oracle数据的语句组成。异常处理部分由异常处理程序组成，用来处理执行中产生的异常。 可以使用支持PL/SQL的任何Oracle工具定义子程序。它们可以在PL/SQL程序块、过程、函数和数据包中声明。 存储过程 存储过程是一个执行指定操作的子程序。创建存储过程的语法如下所示： CREATE [OR REPLACE ] PROCEDURE 过程名（[参数]） AS [局部声明] BEGIN 可执行语句 [EXCEPTION 异常处理程序] END [过程名]； 其中的参数表示如下： 变量名 [IN|OUT|IN OUT] 数据类型 存储过程 示例：根据已知的编号显示出对应职工的姓名 create or replace procedure queryempname(sFindNo emp.empno%type) as sName emp.ename%type; sJob emp.job%type; begin select ename, job into sname,sjob from emp where Empno=sFindNo; dbms_output.put_line(编号为||sFindNO||的职工姓名为||sName|| 工作为:||sjob); Exception when NO_DATA_FOUND then dbms_output.put_line(没有符合的记录); when TOO_MANY_ROWS then dbms_output.put_line(返回值多于一行记录); when OTHERS then dbms_output.put_line(有意外错误发生!); end; 存储过程 调用方式一: execute queryempname(7369) 调用方式二: begin queryempname(7369); end; 数据字典 user_source desc user_source; select * from user_source where name = QUERYEMPNAME‘ 删除存储过程 drop procedure xiaojiujiu； 凡是create建立的,全部可以用drop删除。 存储过程 过程参数设置与传递 create or replace procedure runbyparameters ( iSal IN EMP.SAL%TYPE,--输入指定工资 sName OUT varchar, --输出查询结果:姓名,OUT不能定义精度 --sJob IN OUT emp.job%type --输入工作,输出找到!或没找到 sJob IN OUT varchar --若作为输出时,类型最好用长一些的字符以保证能保存下输出结果 ) AS iCount Number; --过程内变量声明,用于记录查询到符合条件的记录数目 begin select count(*) into icount from emp where salisal and job=sjob; --使用两个输入参数 if iCount=1 then select Ename into sName from emp where sal isal and job=sjob; --根据两个输入参数把结果给输出参数 sName:=姓名为: || sName || 的职工 工资:|| to_char(isal); sJob:=工作为:||sjob; else sName:=没查到工资为 || to_char(isal); sJob:=工作为:||sjob||的记录; end if; 存储过程 exception when too_many_rows then dbms_output.put_line(返回值多于1行); when others then dbms_output.put_line(在runbyparameters过程执行时出现意外); end; 执行: declare realsal emp.sal%type; realname var