oracle学习笔记(二十七)：存储过程

---

什么是存储过程（procedure）？

事先运用 oracle 语法，写好的一段具有业务逻辑功能的片段，长期保存在 oracle 服务器中；

供 oracle 客户端和 程序语言远程访问；类似于 java 中的函数；

 

为什么要用存储过程？

1、PLSQL 每次执行都要整体运行一遍，才有结果；

2、PLSQL 不能将其封装起来，长期保持在 oracle 服务器中；

3、PLSQL 不能被其他应用程序调用，例如 java、C++；

存储过程就是用来弥补 PLSQL 的这些缺点的；

 

存储过程与 PLSQL 是什么关系？

存储过程是 PLSQL 一个方面的应用，而 PLSQL 是存储过程的基础；

即存储过程需要用到 PLSQL；

---

1、创建无参存储过程 hello_pro，无返回值，语法：create procedure 过程名 as PLSQL程序体；【begin … end;/】无 declare

SQL> create procedure hello_pro
  2  as
  3  begin
  4      dbms_output.put_line('hello procedure');
  5  end;
  6  /

过程已创建。

用 create 创建的存储过程，如果已经存在，再次创建就会报错：

SQL> create procedure hello_pro
  2  as
  3  begin
  4      dbms_output.put_line('hello procedure');
  5  end;
  6  /
create procedure hello_pro
                 *
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用

所以，可以使用 create or replace 创建，表示如果存储过程不存在，就创建；如果已经存在，就替换：

SQL> create or replace procedure hello_pro
  2  as
  3  begin
  4      dbms_output.put_line('hello procedure');
  5  end;
  6  /

过程已创建。

2、查看已经创建好的 存储过程：select * from user_source;

SQL> col name for a10;
SQL> col type for a10;
SQL> col line for 99;
SQL> col text for a50;
SQL> select * from user_source;

NAME       TYPE       LINE TEXT
---------- ---------- ---- --------------------------------------------------
HELLO_PRO  PROCEDURE     1 procedure hello_pro
HELLO_PRO  PROCEDURE     2 as
HELLO_PRO  PROCEDURE     3 begin
HELLO_PRO  PROCEDURE     4     dbms_output.put_line('hello procedure');
HELLO_PRO  PROCEDURE     5 end;

3、删除存储过程：

SQL> drop procedure hello_pro;

过程已删除。

4、调用存储过程：

方式一：exec 存储过程

SQL> exec hello_pro;
hello procedure

PL/SQL 过程已成功完成。

方式二：PLSQL 程序

SQL> begin
  2      -- 调用存储过程
  3      hello_pro;
  4  end;
  5  /
hello procedure

PL/SQL 过程已成功完成。

5、创建有参存储过程 raiseSalary_pro（编号），为 7369 号员工涨 10% 的工资；演示 in 的用法；

SQL> -- 创建存储过程 raiseSalary_pro，带一个参数 pempno，类型和 emp 表中的 empno 类型一致
SQL> -- in：表示传入参数；如果不写，该参数默认就是传入参数；
SQL> create or replace procedure raiseSalary_pro(pempno in emp.empno%type)
  2  as
  3  begin
  4      -- 更新语句
  5      update emp set sal=sal*1.2 where empno = pempno;
  6  end;
  7  /

过程已创建。

调用存储过程：

SQL> exec raiseSalary_pro(7369);

PL/SQL 过程已成功完成。

6、创建有参存储过程 findEmpNameAndSalAndJob_pro(编号)，查询7788号员工的的姓名，职位，月薪，演示out的用法；

SQL> -- 创建存储过程：pempno 参数为传入参数；后面三个参数有 out 关键字，表示传出参数；
SQL> create or replace procedure findEmpNameAndSalAndJob_pro(pempno in emp.empno%type,
  2      pename out emp.ename%type, pjob out emp.job%type, psal out emp.sal%type)
  3  as
  4  begin
  5      -- 查询数据，并将结果存入存储过程的 三个传出参数中
  6      select ename,job,sal into pename,pjob,psal from emp where empno = pempno;
  7  end;
  8  /

过程已创建。

调用存储过程：

SQL> -- 调用存储过程
SQL> declare
  2      -- 声明三个变量，用于接收存储过程返回的值
  3      pename emp.ename%type;
  4      pjob emp.job%type;
  5      psal emp.sal%type;
  6  begin
  7      -- 调用存储过程，将存储过程传出的值 存入变量中
  8      findEmpNameAndSalAndJob_pro(7788, pename, pjob, psal);
  9      -- 输出结果
 10      dbms_output.put_line('7788, ' || pename || ', ' || pjob || ', ' || psal);
 11  end;
 12  /
7788, SCOTT, ANALYST, 3000

PL/SQL 过程已成功完成。

注意：exec 只能调用 没有返回值的 存储过程；有返回值的存储过程只能用 PLSQL 调用；