Oracle修炼路程--存储过程/存储函数

一 存储过程
1 概念：
一条或多条SQL语句的集合；
当对数据库进行一系列的操作时，利用存储过程可将复杂的操作封装成一个代码块，以便重复使用。如同java中的方法，调用即可；

2 创建语法：
create or replace procedure 过程名（参数列表）
as
PL/SQL语句；

3 具体语法：

1) 无参数的存储过程
create or replace procedure 过程名（参数列表）
as
begin
end;

--创建存储过程test1
set serveroutput on;
create or replace procedure test1
as
 pnum number:=1;--定义变量pnum；
begin
DBMS_OUTPUT.PUT_LINE('helo'||pnum);
end;

--执行存储过程
--方法一：
exce test1；

--方法二：
begin
test1()；
test1()；
end;
区别：第二种方法可以循环调用；

2) 有参数的存储过程；
输入参数：in
输出参数：out

create or replace procedure raiseSalary(pid in number)
as
   psal emp.sal%type;
begin
    select sal into psal from emp where empno=pid; 
    update emp set sal=sal+500 where empno=pid;
    dbms_output.put_line(psal);
end;

二 存储函数
1 概念：
与存储过程最大的区别是：必须有return；
存储函数是一命名的存储程序，可以带参数并返回计算值；

2 创建语法：
create [or replace] function 函数名（参数列表）
return 返回值类型
as
–定义变量
begin
…
return 返回值
end；

–举例：查询某个员工的年薪；

--举例：查询某个员工的年薪；
create or replace function totalSalary (pid in number)
       return number
as
   psal emp.sal%type;
   pcomm emp.comm%type;
begin
   select sal,comm into psal,pcomm  from emp where empno=pid;
   return  psal*12+nvl(pcomm,0);

end;

3 调试
授权语句：
grant debug connect session to scott;
grant debug any procedure to scott;

存储过程/存储函数什么时候用呢？
建议：只有一个返回值，用存储函数：return；
没有或者有多个返回值 选用存储过程：out；

--举例：传入员工编号，返回姓名和工作；
create or replace procedure getEmpinfo (pempno in emp.empno%type,pename out emp.ename%type,pjob out emp.job%type)
as
begin
select ename,job into pename,pjob from emp where pempno=empno;

end;

create or replace function getEmpinfofunc (pempno in emp.empno%type,pename out emp.ename%type,pjob out emp.job%type)
   return varchar2
as
begin
select ename,job into pename,pjob from emp where pempno=empno;
return null;
end;