存储过程函数
存储过程
语法
create [or replace] procedure 过程名(name in 数据类型,name out 数据类型,...) as begin PLSQL子程序体; end [过程名];
范例(带参数)
--给指定的员工涨100,并且打印涨前和涨后的薪水 create or replace procedure raiseSalary(eno in number) --可以使用as也可以使用is is psal emp.sal%type; begin select sal into psal from emp where empno = eno; update emp set sal = sal + 100 where empno = eno; dbms_output.put_line('涨前:'||psal||' 涨后:'||(psal+100)); end raiseSalary;
调用存储过程
--方法一 set serveroutput on begin --调用存储过程,名称不区分大小写 raisesalary(7369); end;
--方法二 set serveroutput on --exec是execute缩写 exec raisesalary(7369);
存储函数
语法
create [or replace] 函数名(name in type,name out type,...) return 数据类型 is [结果变量 数据类型;] begin PLSQL语句体; return 结果变量; end [函数名];
存储函数和存储过程区别
一般来讲,过程和函数的区别在于函数可以有一个返回值;二过程没有返回值
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数,在过程和函数实现返回多个值。
范例
--查询某个员工的年收入 create or replace function queryEmpIncome(eno in number) return number is 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 queryEmpIncome;
存储函数调用
--方法一 declare v_sal number; begin vsal := queryEmpSalary(7934); dbms_output.put_line('salary is:'||v_sal); end;
--方法二 begin dbms_output.put_line('salary is:'||queryEmpSalary(7934)); end;
过程和函数参数中的in和out
in表示输入参数,默认是in
out指定一个或多个输出参数
范例
--查询某个员工的姓名 薪水和职位 create or replace procedure queryEmpInformation(eno in number, pename out varchar2, psal out number, pjob out varchar2) is begin select ename,sal,job into pename,psal,pjob from emp where empno = eno; end queryEmpInformation;
--调用 declare pename emp.ename%type; psal emp.sal%type; pjob emp.job%type; begin queryEmpInformation(7369,pename,psal,pjob); dbms_output.put_line('姓名:'||pename||'工资:'||psal||'职位:'||pjob); end;
out参数中使用游标
--创建包头 create or replace package mypackage is --定义一个cursor类型变量 type empcursor is ref cursor; procedure queryEmpList(dno in number,empList out empcursor); end mypackage;
--创建包体 create or replace package body mypackage is procedure queryEmpList(dno in number,empList out empcursor) as begin open empList for select * from emp where deptno = dno; end; end mypackage;
什么时候使用函数什么时候使用过程
原则:如果只有一个返回值,用存储函数;否则就用存储过程。