Oracle存储过程和自定义函数

新博客文章链接,欢迎大家评论探讨

概述

　　存储过程和存储函数是指存储在数据库*所有用户程序调用的子程序叫存储过程、存储函数。

异同点：

• 存储过程和存储函数的相同点：完成特定功能的程序。
• 存储过程和存储函数的区别：是否用return语句返回值。

存储过程的创建和调用

第一个存储过程：打印 hello world

create or replace procedure sayhelloword
as
  -- 说明部分,as一定要写
begin
    dbms_output.put_line('Hello World');
end;
/

调用存储过程

1. exec sayhelloworld()

-- 调用两次
begin
   sayhelloworld();
   sayhelloworld();
end;
/

oracle 带参数的存储过程

-- 创建一个带参数的存储过程
-- 给指定的员工涨100块钱的工资，并且打印涨前后涨后的薪水
create or replace procedure raisesalary(eno in number) -- in 这是一个输入参数
as
  -- 定义一个变量保存涨前的薪水
    psal emp.sal%type;
begin
   -- 得到员工涨前的薪水
    select sal into psal from emp where empno=eno;
   -- 给该员工涨100块钱
    update emp set sal=sal+100 where empno=eno;
   -- 一般，这里不需要 commit ！ 也不需要 rollback
   -- 注意：一般不在存储过程或者存储函数中，commit 和 rollback
   -- 打印
    dbms_output.put_line('涨前：'||psal||'，涨后：'||(psal+100));
end;
/
-- 调用：
begin
   raisesalary(7839);
   raisesalary(7566);
end;
/

如何调试存储过程

1. 调试存储过程最好放到Oracle数据库所在的系统或虚拟机上，解压SQL developer ，双击运行。
2. 为了确保存储过程或函数是可调试的，右键“以编译并进行调试”，点击红色按钮“调试”
3. 利用已写好的调用函数进行调试。
4. 给调试账户授权

grant DEBUG CONNECT SESSION ,DEBUG ANY PROCEDURE to scott;

存储函数

函数的定义

　　是一个命名的存储程序，可带参数，并返回一个计算值。必须有return 子句，用于返回函数值。

创建存储函数语法

  create or replace function 函数名(参数列表)
  return 函数值类型
  as
  begin
    PLSQL子程序体;
  end;

注意 表达式中某个字段为空时，表达式返回值为空。为防止含有表达式的返回值错误，在可能为空的字段上加上NVL(字段名，0)。

--查询某个员工的年收入
create or replace function queryemp_income(eno in number)
return number
as
    --定义变量接收薪水和奖金
    p_sal emp.sal%type;
    p_comm emp.comm%type;
begin
  select sal,comm into p_sal,p_comm from emp where empno=eno;
  --nvl为遇空函数，如果p_comm为空则返回0
  return nvl(p_comm,0)+p_sal*12;
end;
/

out 参数

　　存储过程和存储函数都可以有多个输入（in）和输出参数（out），都可以通过out参数实现返回多个值。

-- out参数：查询某个员工姓名、月薪和职位
-- 原则： 如果只有一个返回值，用存储函数；否则，就用存储过程。
create or replace procedure queryempinfor(eno in number,pename out varchar2,
  psal out number,pjob out varchar2)
as
begin
   -- 得到该员工的姓名、月薪和职位
    select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;
/

在 out 参数中访问光标

申明包结构

• 包头(申明)
• 包体(实现)

案例

-- 查询某个部门中所有员工的所有信息  //ref(reference引用) cursor(光标)
#包头
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;
***********包体需要实现包头中声明的所有方法*********************

在应用程序中访问

　　在java应用程序中访问存储过程和存储函数以及访问包下的存储过程，可以查看java API文档。

Reference:

• 慕课网－oracle 存储过程和自定义函数