从概念到示例—Oracle中操作存储过程-优快云博客

本文深入探讨PL/SQL中的子程序概念，包括过程和函数的定义与使用，重点讲解如何通过子程序实现代码的模块化、重用性和维护性。文章提供了创建存储过程的具体步骤和示例代码，演示了如何利用参数模式执行存储过程，并展示了多个实际应用案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前讨论的PL/SQL块都是匿名块,即并不能永久存储于数据库,那如何让我们的
PL/SQL块持久化到数据库中了?,以此来达到反复的调用.这就是我们马上要说的
子程序，即带有名字的程序块如：过程、函数...

一、子程序
1.命名的PL/SQL块
2.子程序的包括3个部分
   -声明部分
   -可执行部分
   -异常部分（可选）
3.子程序的优点
    -模块化
       将程序分解为多个逻辑模块
    -可重用性
        子程序在被执行之后，可以再任意数目的应用程序中使用
    -可维护性
         子程序简化了维护,可以随时取出进行修改
4.子程序的类型
    - 过程   用于执行某项操作
    - 函数   用于执行某项操作并返回值
       两者区别大致与SQL类似

二、存储过程
- 使用Create Prodecure 语句创建
- 语法
    Create [or replace] Procedure 过程名[参数列表]
    IS|AS --代替了Declare声明关键字
         局部声明<local declarations>
     Begin
         可执行语句<executable statements>
     Exception [可选部分]
         异常部分
     End
-参数模式
    1. in 接受值默认模式
    2. out 将值返回给子程序的调用程序
    3. in out 接受值并返回已更新的值
-执行过程
    -使用Execute 语句简写为exec
    -语法
      方法1： . Execute 过程名（参数列表）如：execute items('1002'); --注意SQL中是没有括号的
     方法2：位于匿名块中
      begin
         xiaojiujiu;
      end;
示例1：打印乘法表

Code
1

Code
2

Create or replace Procedure PrintJiujiu AS
3

i integer;
4

j integer;
5

Begin
6

DBMS_output.put_line('打印九九乘法表');
7

for i in 1..9 loop
8

for j in 1..9 loop
9

if i>=j then
10

DBMS_output.put(To_Char(j)||'*'||to_char(i)||'='||to_char(i*j)|' ');
11

end if;
12

end loop;
13

DBMS_output.put_line('');
14

end loop;
15

end;
16

--调用:
17

execute PrintJiujiu ;
18

--或者
19

begin
20

PrintJiujiu ; --注意没有exec了哦;
21

end；
22

示例2:根据员工编号显示出对应职工的姓名
Code
Create or replace Procedure QueryEmpName(sFindNo emp.empNo%type) as
sName emp.ename%type;
sJob emp.job%type;
Begin
   select ename,job into sName,sJob  from emp where EmpNo=sFindNo;
    DBMS_output.put_line('编号为'||sFindNo||'的职工姓名为:'||sName||'工作为:'||sjob);
Exception
   when No_data_found then
      DBMS_output.put_line('没有符合条件的记录!');
   when Too_many_rows then
      DBMS_output.put_line('返回值多余一条记录!');
   when Others then
      DBMS_output.put_line('执行存储过程时发生意外错误!');
End;

综合示例：带输入，输出，输入输出参数的练习
Code
1create or replace Procedure RunByParameters
2(
3   iSal  in emp.sal%type,  --输入指定工资
4   sname out varchar,     --输出查询结果：姓名注意数据类型没有精度,确保能够存储值
5   sjob in out varchar     --若作为输出时，
6)
7as
8  iCount number;  --变量声明，用于记录查询到符合条件的记录数目
9Begin
10   select count(*) into iCount from emp where sal>iSal and job=sjob;  --使用输入参数
11   if iCount=1 then
12    select ename into sname from emp where sal>isal and job=sjob; --根据两个输入参数把结果输出
13      sname:='姓名为:'||sname||'的职工:'||to_char(iSal);  --输出sname
14     sjob:='工作为:'||sjob;                              --输出sjob
15   else
16     sname:='没查到工资为:'||to_char(iSal);              --输出sname
17     sjob:='工作为:'||sjob||'  的记录！';                 --输出sjob
18   end if;
19Exception
20   when too_many_rows then
21     DBMS_output.put_line('返回值多余一行!');
22   when others then
23     DBMS_output.put_line('在RunByParameters过程执行时出现意外情况!');
24End;
25
26调用
27declare
28  realSal emp.Sal%type;
29  realName varchar(40);
30  realJob varchar(40);
31Begin
32  realSal:=1100;
33  realName:='';
34  realJob:='CLERK';
35 /**//*第一次调用*/
36  RunByParameters(realSal,realName,realJob);
37  DBMS_output.put_line(realName||' '||realJob);
38 /**//*第二次调用*/
39  realJob:='MANAGER';
40  RunByParameters(2900,realName,realJob);
41  DBMS_output.put_line(realName||' '||realJob);
42 /**//*第三次调用*/
43  RunByParameters(isal=>realSal,sName=>realname,sjob=>realjob);
44   DBMS_output.put_line('带联合符号'||realName||''||realJob);
45end;
46/
47