语法:
--创建或替换一个存储过程 参数列表需要指明输入或者输出参数 create [or replace] procedure 过程名(Name in out type, Name in out type, ...) as | is --相当于PL/SQL块的declare,这里不可省略 PLSQL子程序体;
create or replace procedure sayhelloWorld as --相当于PL/SQL中declare说明部分,不过这里即使没有说明部分也需要写 begin dbms_output.put_line('helloWorld'); end sayhelloWorld;
如果使用PL/SQL Developer 工具,如果执行成功可以在左边的procedure区域看到我们执行后编译后的存储过程(成功一般显示为绿色,如果不对会有一个小红x)
--创建一个带参数的存储过程,给指定的员工涨100员工资,并打印涨前和涨后的薪水
--in表示是一个输入参数,如果带参数,需要指明是输入参数还是输出参数
create or repalce procedure raisesalary(eno in number) as --定义一个变量保存涨前的薪水 psal emp.sal%type; begin --得到员工的涨前的薪水 select sal into psal emp where empno=eno; --给员工涨100 update emp set sal = sal+100 where empno = eno; --这里进行了update,不过我们一般不在存储过程和存储函数中进行提交事务,一般由调用者进行提交 --打印涨前和涨后的薪水 dbms_output.put_line('涨前:'||psal||'涨后'||(psal+100)); end;
begin raisesalary(7839); --给员工号为7839涨工资 raisesalary(7566); --给员工号为7566涨工资 end;
有时候有一些存储过程比较大,需要进行debug调试,看是否符合我们的逻辑需求,使用PL/SQL Developer可以对存储过程进行调试
1.3.1选中需要调试的存储过程--》test进入debug调试模式
1.3.2设置断点,可以单步运行
函数(Function) 为一命名的存储程序,可带参数,并返回一计算值
函数和过程结构类似,但必需要有一个return子句,用于返回函数数值。
--假如不带参数,不能带()
create or replace function 函数名(Name in out type, Name in out type, ...) return 函数值类型 as | is PL/SQL子程序体;
--存储函数:查询某个员工的年收入
create or replace function queryempincomme(eno in number) return number as --定义变量保存员工的薪水和奖金 psal emp.sal%type; pcomm emp.comm%type; begin --得到员工的月薪和奖金 select sal,comm into psal,pcomm from emp where empno=eno; --直接返回年收入 return psal*12+nvl(pcomm,0); end;
declare ypsal number; begin --得到员工7891的年收入 ypsal:=queryempincomme(7891); dbms_output.put_line(ypsal); end;
create or replace procedure queryempinfo(eno in number,pename out varchar2,psal out nubmer,pjob out varchar2) as begin --得到员工的姓名,月薪,职位 select ename,sal,empjob into pename,psal,pjob from emp where empno=eno; end;
declare eno number; pename varchar2(30); psal number; pjob varchar2(200); begin eno := 7839; --调用存储过程,我们可以得到out参数的返回值 queryempinfo(eno,pename,psal,pjob); dbms_output.put_line(pename); dbms_output.put_line(psal); dbms_output.put_line(pjob); end;
oracle中的程序包分为包头和包体,包头负责声明,包体负责实现(者很像java中的接口与实现类的关系)
包头语法:
create [or replace] package package_name is | as --定义公用常量、变量、游标、类型 --定义公用的过程和函数 end package_name;
create [or replace] package body package_name is | as --定义私有常量、变量、类型、游标、过程和函数 --实现公用的过程和函数 end package_name案例:查询某个部门中所有员工的所有信息,这里使用如下方案,实现第4条留下的几个问题out参数很多显然不可取,我们是使用cursor光标实现
create or replace package mypackage as --定义公用的类型 自定义类型empcursor 为 cursor类型 type empcursor is ref cursor; --定义公用的过程和函数 --之后需要在包体中实现 procedure queryEmpList(dno in number,empList out empcursor); end mypackage;
创建包体
create or replace package body mypackage as procedure queryEmpList(dno in number,empList out empcursor) as begin --打开光标 open empcursor from select * from emp where deptno=dno; end queryEmpList; end mypackage;
下边两个图是我截取《精通Oracle10g SQL和PL/SQL》的片段