Oracle 19C入门到精通之存储过程

ITPro进化论 2023-12-10

209

命名的PL/SQL块可以被独立编译并存储在数据库中，Oracle提供了4种可以存储的PL/SQL块，即过程、函数、触发器和包。

存储过程是一种命名的PL/SQL块，它既可以没有参数，也可以有若干个输入、输出参数，甚至可以有多个既作为输入又作为输出的参数，但它通常没有返回值。存储过程被保存在数据库中，它不可以被SQL语句直接执行或调用，只能通过EXECUT命令执行或在PL/SQL块内部被调用。由于存储过程是已经编译好的代码，因此在被调用或引用时，其执行效率非常高。

1. 创建存储过程

创建一个存储过程与编写一个普通的PL/SQL块有很多相似之处。例如，两者都包括声明部分、执行部分和异常处理3部分。但这两者之间的实现细节尚有很多差别，例如，创建存储过程需要使用PROCEDURE关键字，在关键字后面就是过程名和参数列表；创建存储过程不需要使用DECLARE关键字，而是使用CREATE或REPLACE关键字。其基本语法格式如下：

CREATE [OR REPLACE] PROCEDURE pro_name [(parameter1[,parameter2]…)] IS|AS 
BEGIN 
 plsql_sentences; 
[EXCEPTION] 
 [dowith _ sentences;] 
END [pro_name];

pro_name：存储过程的名称。如果数据库中已经存在此名称，则可以指定OR REPLACE关键字，这样新的存储过程将覆盖原来的存储过程。
parameter1：存储过程的参数。若是输入参数，则需要在其后指定IN关键字；若是输出参数，则需要在其后指定OUT关键字。在IN或OUT关键字的后面是参数的数据类型，但不能指定该类型的长度。
plsql_sentences：PL/SQL语句，它是存储过程功能实现的主体。
dowith _ sentences：异常处理语句，也是PL/SQL语句，这是一个可选项。

注意：上述语法中的parameter1是存储过程被调用／执行时用到的参数，而不是存储过程内定义的内部变量，内部变量要在IS|AS关键字后面定义，并使用分号(;)结束。

创建一个存储过程，该存储过程实现向dept表中插入一条记录，代码如下：

create procedure pro_insertDept is
begin
  insert into dept values(66,'营销部','JINAN');  --插入数据记录
  commit;  --提交数据
  dbms_output.put_line('插入新记录成功！'); --提示插入记录成功
end pro_insertDept;
/

在当前模式下，如果数据库中存在同名的存储过程，则要求新创建的存储过程覆盖已存在的存储过程；如果不存在同名的存储过程，则可直接创建，代码如下：

create or replace procedure pro_insertDept is
begin
  insert into dept values(66,'营销部','JINAN');  --插入数据记录
  commit;  --提交数据
  dbms_output.put_line('插入新记录成功！');  --提示插入记录成功
end pro_insertDept;
/

无论在数据库中是否存在名称为pro_insertDept的存储过程，上述代码都可以成功地创建一个存储过程。如果在创建存储过程中发生了错误，则用户还可以使用SHOW ERROR命令来查看错误信息。

上述两个存储过程中的主体代码都可以实现向数据表dept中插入一行记录，但主体代码INSERT语句仅仅是被编译了，并没有被执行。若要执行该INSERT语句，则需要在SQL* Plus环境中使用EXECUTE命令来执行该存储过程，或者在PL/SQL块中调用该存储过程。

使用EXECUTE命令的执行方式比较简单，只需要在该命令后面输入存储过程名即可；在SQL* Plus环境中，使用EXECUTE命令执行pro_insertDept存储过程，代码如下：

execute pro_insertDept;

代码中的EXECUTE命令也可简写为EXEC。有时候需要在一个PL/SQL块中调用某个存储过程，比如，在PL/SQL块中首先调用存储过程pro_insertDept，然后执行PL/SQL块，具体代码如下：

set serverout on
begin
  pro_insertDept;
end;
/

因为前面已经执行过一次pro_insertDept存储过程，已经向dept表插入一条deptno=66的数据了，不能再插入deptno=66的数据，提示违反唯一约束；

修改一下pro_insertDept存储过程插入的内容，然后再次执行PL/SQL块；

create or replace procedure pro_insertDept is
begin
  insert into dept values(77,'运营部','QINGDAO');  --插入数据记录
  commit;  --提交数据
  dbms_output.put_line('插入新记录成功！');  --提示插入记录成功
end pro_insertDept;
/

注意：在创建存储过程的语法中，对于IS关键字，也可以使用AS关键字来替代，效果是相同的。

2. 存储过程的参数

前面所创建的存储过程都是简单的存储过程，都没有涉及参数。Oracle为了增强存储过程的灵活性，提供向存储过程传入参数的功能。参数是一种向程序单元输入和输出数据的机制，存储过程可以接受多个参数，参数模式包括IN、OUT和IN OUT共3种；

2.1. IN模式参数

IN模式参数是一种输入类型的参数，参数值由调用方传入，并且只能被存储过程读取。这种参数模式是最常用的，也是默认的参数模式，关键字IN位于参数名称之后。

创建一个存储过程，并定义3个IN模式的变量，然后将这3个变量的值插入dept表中，代码如下：

create or replace procedure insert_dept(
  num_deptno in number,   --定义IN模式的变量，它存储部门编号
  var_dname in varchar2,  --定义IN模式的变量，它存储部门名称
  var_loc in varchar2) is --定义IN模式的变量，它存储部门位置
begin
  insert into dept
  values(num_deptno,var_dname,var_loc);  --向dept表中插入记录
  commit;    --提交数据库
end insert_dept;
/

上述代码成功创建了一个存储过程，需要注意的是，参数的类型不能指定长度。

在调用或执行这种IN模式的存储过程时，用户需要向存储过程传递若干参数值，以保证执行部分（即BEGIN部分）有具体的数值参与数据操作。向存储过程传入参数可以有如下3种方式：

2.1.1. 指定名称传递

指定名称传递是指在向存储过程传递参数时，需要指定参数名称，即参数名称在左侧，中间是赋值符号“=>”，右侧是参数值，其语法格式如下：

pro_name(parameter1=>value1[,parameter2=>value2]…)

parameter1：参数名称。在传递参数值时，这个参数名称与存储过程中定义的参数顺序无关。
value1：参数值。在它的左侧不是常规的赋值符号“=”，而是一种新的赋值符号“=>”，需要注意参数值的类型要与参数的定义类型兼容。

在PL/SQL块中调用存储过程insert_dept，然后使用“指定名称”的方式传入参数值，最后执行当前的PL/SQL块，代码及运行结果如下：

begin
  insert_dept(var_dname=>'财务部',var_loc=>'烟台',num_deptno=>15);
end;
/

在创建存储过程时，其参数的定义顺序是num_deptno、var_dname、var_loc；而在执行存储过程时，参数的传递顺序是var_dname、var_loc、num_deptno。通过对比可以看到，使用“指定名称”的方式传递参数值与参数的定义顺序无关，但与参数个数有关。

2.1.2. 按位置传递

指定名称传递参数虽然直观易读，但也有缺点，就是参数过多时，会显得代码冗长，反而变得不容易阅读。可以采取按位置传递参数，采用这种方式时，提供的参数值顺序必须与存储过程中定义的参数顺序相同。

在PL/SQL块中调用存储过程insert_dept，然后使用“按位置传递”的方式向其传入参数值，最后执行当前的PL/SQL块，代码及运行结果如下：

begin
  insert_dept(11,'工程部','威海');
end;
/

有时候参数过多，不容易记住参数的顺序和类型，可以通过DESC命令来查看存储过程中参数的定义信息，这些信息包括参数名、参数定义顺序、参数类型和参数模式等。

2.1.3. 混合方式传递

混合方式就是将前两种方式结合到一起，可以兼顾二者的优点。

在PL/SQL块中调用存储过程insert_dept，然后使用按位置传递方式传入第一个参数值，使用指定名称传递方式传入剩余的两个参数值，最后执行当前的PL/SQL块，代码及运行结果如下：

exec insert_dept(12,var_loc=>'济南',var_dname=>'测试部');

注意：在某个位置使用指定名称传递方式传入参数值后，其后面的参数值也要使用指定名称传递，因为指定名称传递方式有可能已经破坏了参数原始的定义顺序。

2.2. OUT模式参数

OUT模式参数是一种输出类型的参数，表示这个参数在存储过程中已经被赋值，并且这个参数值可以被传递到当前存储过程以外的环境中，关键字OUT位于参数名称之后。

创建一个存储过程，要求定义两个OUT模式的字符类型的参数，将在dept表中检索到的一行部门信息存储到这两个参数中，代码如下：

create or replace procedure select_dept(
  num_deptno in number,          --定义IN模式变量，要求输入部门编号
  var_dname out dept.dname%type, --定义OUT模式变量，可以存储部门名称并输出
  var_loc out dept.loc%type) is  --定义OUT模式变量，可以存储部门位置并输出
begin
  select dname,loc
  into var_dname,var_loc
  from dept
  where deptno = num_deptno;  --检索某个部门编号的部门信息
exception
  when no_data_found then     --若SELECT语句无返回记录
     dbms_output.put_line('该部门编号不存在');  --输出信息
end select_dept;
/

在上述存储过程（即select_dept）中，定义了两个OUT参数，由于存储过程要通过OUT参数返回值，因此当调用或执行这个存储过程时，需要定义变量来保存这两个OUT参数值；

①在PL/SQL块中调用OUT模式的存储过程

这种方式需要在PL/SQL块的DECLARE部分定义与存储过程中OUT参数兼容的若干变量。

在PL/SQL块中声明若干变量，然后调用select_dept存储过程，并将定义的变量传入该存储过程中，以便接收OUT参数的返回值，代码如下：

set serverout on
declare
  var_dname dept.dname%type;  --声明变量，对应过程中的OUT模式的var_dname
  var_loc dept.loc%type;      --声明变量，对应过程中的OUT模式的var_loc
begin
  select_dept(77,var_dname,var_loc); --传入部门编号，然后输出部门名称和位置信息
  dbms_output.put_line(var_dname||'位于：'||var_loc); --输出部门信息
end;
/

在上述代码中，把声明的两个变量传入存储过程中，当存储过程执行时，其中的OUT参数会被赋值；当存储过程执行完毕，OUT参数的值会在调用处返回，这样定义的两个变量就可以得到OUT参数被赋予的值，最后这两个值就可以在存储过程外任意使用了。

②使用EXEC命令执行OUT模式的存储过程

当使用EXEC命令时，需要在SQL* Plus环境中使用VARIABLE关键字声明两个变量，用以存储OUT参数的返回值。

使用VARIABLE关键字声明两个变量，分别用来存储部门名称和位置信息，然后使用EXEC命令执行存储过程，并传入声明的两个变量来接收OUT参数的返回值，代码如下：

variable var_dname varchar2(50);
variable var_loc varchar2(50);
exec select_dept(11,:var_dname,:var_loc);

使用SELECT语句检索并输出变量var_dname和var_loc的值，代码如下:

select :var_dname,:var_loc from dual;

注意：如果在存储过程中声明了OUT模式的参数，则在执行存储过程时，必须为OUT参数提供变量，以便接收OUT参数的返回值；否则，程序执行后将出现错误。

2.3. IN OUT模式参数

在执行存储过程时，IN参数不能被修改，它只能根据被传入的指定值（或是默认值）为存储过程提供数据；而OUT类型的参数只能等待被赋值，它不能像IN参数那样为存储过程本身提供数据。但IN OUT参数可以兼顾其他两种参数的特点，在调用存储过程时，可以从外界向该类型的参数传入值；在执行完存储过程之后，可以将该参数的返回值传给外界。

创建一个存储过程，其中定义一个IN OUT参数，该存储过程用来计算这个参数的平方或平方根，代码如下：

create or replace procedure pro_square(
  num in out number,  --计算它的平方或平方根，这是一个IN OUT参数
  flag in boolean) is --计算平方或平方根的标识，这是一个IN参数
  i int := 2;         --表示计算平方，这是一个内部变量
begin
  if flag then           --若为TRUE
    num := power(num,i); --计算平方
  else
    num := sqrt(num);    --计算平方根
  end if;
end;

在上述存储过程中，首先定义了一个IN OUT参数，该参数在存储过程被调用时会传入一个数值，然后与另一个IN参数相结合来判断所进行的运算方式（平方或平方根），最后将计算后的平方或平方根保存到此IN OUT参数中。

调用存储过程pro_square，计算某个数的平方或平方根，代码如下：

set serverout on
declare
  var_number number;  --存储要进行运算的值和运算后的结果
  var_temp number;    --存储要进行运算的值
  boo_flag boolean;   --平方或平方根的逻辑标记
begin
  var_temp :=9;       --变量赋值
  var_number :=var_temp;
  boo_flag := false;  --FALSE表示计算平方根；TRUE表示计算平方
  pro_square(var_number,boo_flag);  --调用存储过程
  if boo_flag then
    dbms_output.put_line(var_temp ||'的平方是：'||var_number); --输出计算结果
  else
    dbms_output.put_line(var_temp ||'平方根是：'||var_number);
  end if;
end;
/

变量var_number在调用存储过程之前是9，而在存储过程被执行完毕之后，该变量的值变为其平方根，这是因为该变量作为存储过程的IN OUT参数被传入和返回。

3. IN参数的默认值

IN参数的值是在调用存储过程中被传入的，实际上，Oracle支持在声明IN参数的同时给其初始化默认值，这样在存储过程调用时，如果没有向IN参数传入值，则存储过程可以使用默认值进行操作。

创建一个存储过程，定义3个IN参数，并将其中的两个参数各设置一个初始默认值，然后将这3个IN参数的值插入dept表中，代码如下：

create or replace procedure insert_dept(
  num_deptno in number,                    --定义存储部门编号的IN参数
  var_dname in varchar2 default '行政部',   --定义存储部门名称的IN参数，并初始化默认值
  var_loc in varchar2 default '青岛') is
begin
  insert into dept values(num_deptno,var_dname,var_loc);  --插入一条记录
end;
/

在上述存储过程中，IN参数var_dname和var_loc都有默认值，所以在调用insert_dept存储过程时，可以不向这两个参数传入值，而是使用其默认值（当然也可以传入值）。这种方法建议使用“指定名称传递”的方式传值，这样就不会因为顺序不固定的问题出现混乱。

在PL/SQL块中调用insert_dept存储过程，并且只向该存储过程中传入两个参数值，代码如下：

set serverout on
declare
  row_dept dept%rowtype;      --定义行变量，与dept表的一行类型相同
begin
  insert_dept(57,var_loc=>'太原');  --调用insert_dept存储过程，传入参数
  commit;                           --提交数据库
  select *  into row_dept from dept where deptno=57; --查询插入的记录
  dbms_output.put_line('部门名称是：《'||row_dept.dname||'》,位置是：《'||row_dept.loc||'》');
end;
/

存储过程insert_dept有3个IN参数，这里只传入两个参数（num_deptno和var_loc）的值，而var_dname参数的值使用默认值“行政部”。

4. 删除存储过程

当一个过程不再被需要时，要将此过程从内存中删除，以释放相应的内存空间，代码如下：

DROP PROCEDURE count_num;

删除存储过程insert_dept，代码如下:

DROP PROCEDURE insert_dept;

当一个存储过程已经过时，想重新定义时，不必先删除再创建，只需在CREATE语句后面加上OR REPLACE关键字即可，代码如下:

CREATE OR REPLACE PROCEDURE count_num

今天的文章就到这里，如果对你有用，记得点个【赞】和【在看】，感谢阅读~

oracle 存储过程 oracle函数命令模式 num

文章转载自ITPro进化论，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。