pl/sql-存储过程与存储函数与包

存储过程

将某一个功能的代码,封装在一起。相当于 JAVA中的方法,JS中的函数

CREATE  [or replace]  PROCEDURE  存储过程名(参数列表) 
AS -- 没有 declare,as后面直接定义变量
PLSQL语句块 -- 没有return

①创建无参的存储过程

create or replace procedure test1
-- 存储过程没有return
as 
begin
  dbms_output.put_line('hello world') ; 
end;

调用存储过程

set SERVEROUTPUT ON ; -- 打开打印服务
exec test();  -- 调用存储过程

或

begin
	test1();
    test1();
    test1();
end;

②有参 （输入参数:in,输出参数:out,相当于返回值）
涨工资:传入员工编号,涨500

create or replace procedure raiseSalaray(id in number)  -- in 就是以前的形参  out 就是返回值
as
    psal employee.sal%type ;--保存涨之前的工资
begin 
    select sal into psal from employee where empno = id;
    update employee set sal =sal+500 where  empno = id;
    dbms_output.put_line(psal || '--' || (psal+500));--不能写sal,因为不是变量
end ;

调用

begin
    raiseSalaray(7788);
    raiseSalaray(7499);
end;

③多个参数

create or replace procedure raiseSalaray(id in number, rate in number,… )
as
    ...
end ;

函数 FUNCTION

CREATE  [or replace] function 函数名(参数列表) 
return 值类型 -- 不能加;分号
AS
PLSQL语句(包含reutrn值)

示例：查询某个员工的年收入

create or replace function getTotalSal(id in number)
return number
as
    empSal emp.sal%type;
    empComm emp.comm%type;
begin
    select sal,comm into empSal,empComm 
    from emp where empno = id;
    return empSal*12+nvl(empComm,0);
end;

运行与调试函数

1.使用sqldeveloper运行函数
在需要运行的函数上右键-->运行 弹出的框上，修改两处，输入 和 输出

点击运行之后，sqldeveloper 提供了用一个 pl/sql块 调用存储函数。

这里的ID就是传入参数，给ID赋一个值，点击运行

运行结果

2.调试函数
开启调试权限：

sqlplus / as sysdba
grant debug connect session,debug any procedure to scott ;

在函数里打断点，编译以进行调试-->调试
监控变量 选中变量 --》右键--》监测

存储函数 和 存储过程 区别

存储函数必须有return 。(存储过程用out)

存储函数 可以通过out和reutrn返回值。 但一般建议:如果返回值 有1个,则用retrun;否则 (多个 或0个) 用out.

存储函数 可以在sql中 select to_number_or_null ( '123') from dual; 这样调用存储函数

如果只有一个返回值,好用存储函数；否则用存储过程。

out参数：用于返回值

返回值：
存储过程：out
存储过程：return (返回一个)、out

示例：存储过程中使用out

根据员工编号，返回员工的姓名、工作

create or replace procedure  getEmpInfo(peno 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 empno=peno ;
end;

一般是在JAVA中调用存储过程、存储函数，现在可以通过sqldeveloper将参数打印出来

示例：存储函数中使用out （修改上例）

create or replace function  getEmpInfoFunction (peno 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 empno=peno ;
    return null ;
end;

光标作为out参数（包）

例题：查询某个部门中的所有员工信息

思考:返回某个部门中的所有员工信息 --->java中 返回 对象数组或集合对象List
--> SQL中返回集合 ：光标

在out参数中 用光标，需要用到“包”,包分为：包头+包体
包头：相当于方法的定义, public void a(…)
包体：相当于方法体 ,{…}

右键“程序包”-->新建-->命名MYPACKAGE
包头

CREATE OR REPLACE PACKAGE MYPACKAGE AS 

  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 empList for select * from emp where deptno=dno; --  将结果存储到光标中
  END queryEmpList;
END MYPACKAGE;

以上，通过包，就可以将返回的集合存入到out输出参数中。

查看包：desc mypackage ;

调用包中的存储过程：
包名.存储过程名(参数)
mypackage.queryEmplist( …);
SQL developer 不支持，以后会在 java程序中运行。