Oracle中如何写存储过程

2023-08-01 08:10:16

>存储过程是什么?

存储过程是一种命名的PL/SQL程序块,输入、输出参数数量【0个或多个】，通常没有返回值【也有特殊的情况】。

运行SQL语句时，Oracle是要先编译再执行的；而存储过程是已经编译好的代码，所以被调用或引用时，执行效率非常高。

存储过程被保存在数据库中，但是不能被SQL直接执行或调用，可以通过EXECUTE命令执行或在PL/SQL程序块内部被调用。

>创建存储过程的语法格式:

CREATE [OR REPLACE] procedure pro_name
    [(parameter1[,parameter2]...] is|as

begin

    plsql_sentences;

[exception]

    [dowith_sentences;]

end [pro_name];

-->pro_name:存储过程的名称,如果数据库中已经存在了此名称,则可以指定"OR PLACE"关键字来覆盖原来的存储过程;如果不想覆盖,可修改名称,这样就可以区分开了

-->parameter1: 存储过程被调用或执行时用到的参数。【注意：这个值不是存储过程内定义的内部变量，内部变量应该在is｜as之后定义】

parameter1如果是输入参数,则需要在后面指定IN关键字;如果是输出参数,则需要在后面指定OUT关键字,在IN和OUT后面加的是参数的数据类型,不需要指定长度;【不明白不要紧,后面会举例说明;】

-->plsql_sentences:PL/SQL语句,存储过程中功能实现的主要代码

-->dowith_sentences:异常处理语句,是可选的PL/SQL语句(不需要可以不写)

>创建和运行简单的存储过程

>举个栗子----【创建一个存储过程,该存储过程实现向dept表中插入一条记录】（首先记得自己建一张表，这里不再演示了）

create [or replace] procedure pro_insertDept is
begin
insert into dept values(77,'市场拓展部','8');
commit;
dbms_output.put_line('插入新纪录成功!');
end pro_insertDept;

执行Execute之后,

Oracle中如何写存储过程

从上面的结果看出,我们已经顺利的创建出了存储过程pro_insertDept。【如果没有创建成功的话,可以使用show error命令查看错误信息。】

》》如果在我们创建之前,就已经有这个同名的存储过程了，那么应该怎么处理呢？【两种方法】

　　①修改现有的存储过程名称，重新创建

　　②使用字段中“OR Replcae"关键字创建一个同名的存储过程覆盖已经存在的存储过程。实际使用不加中括号,这里只是表示可有可无;

　　　　表示已经存在的话覆盖后,创建存储过程；没有的话，直接创建存储过程。

-->如果现在使用SQL*PLUS去查询插入的记录，一定是不存在的

　　　 Oracle中如何写存储过程

原因是,我们只是创建了存储过程而并没有执行,若想要执行的话,使用Execute关键字来执行存储过程；也可以简写”EXEC“

　　 Oracle中如何写存储过程

-->接下里我们尝试在PL/SQL块中调用此存储过程，之前先将上一条记录删除，因为字段ID为主键；

然后按照下面的方式调用即可；

　　　　Oracle中如何写存储过程

注:set serverout on 意思是在窗口里显示服务器输出信息。

>带参数的存储过程

①IN模式参数-------->“输入类型参数,参数由调用者传入,只能被存储过程读取,是默认的参数模式,也是最常用的”

>举个栗子----【创建一个存储过程,并定义3个IN模式的变量,然后将这3个变量的值插入到dept表中,代码及运行结果如下。】

create or replace procedure insert_dept(

　　id in number,

　　dname in varchar2,

　　deptno in number) is
begin
insert into dept
values(id,dname,deptno);
commit;
end insert_dept;

创建存储过程成功！ 【注意点：参数类型不能指定长度。】

在调用或执行IN模式的存储过程时，用户需要自己传参数，你传什么，它就使用什么去执行。

>传参数的方式有三种：

1)指定名称传递-->参数名称在左,参数值在右,中间使用赋值符号"=>"连接:

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

还是用栗子比较容易懂,不太明白的,请看这里:(因为标注字段名称,所以,赋值时位置可以颠倒)

【在PL/SQL块中调用存储过程insert_dept,然后使用'指定名称'的方式向其传入参数数值,最后执行当前的PL/SQL块】　　

　　　  Oracle中如何写存储过程

2)按位置传递(这种方式不用写字段名称,所以赋值顺序必须与字段标准顺序一致)

Oracle中如何写存储过程

##因为有的时候参数过多,或者参数名称太长不好记,这种方法只要记住顺序就可以了,实在记不住,使用DESC查看;

3)混合方式传递(顾名思义:这是将前两者结合使用的)

执行方法,还可以使用Execute关键字执行,和上面使用的方法结果是一样的;

　　　 Oracle中如何写存储过程

特别注意:因为在中间使用了"指名方式"传值,所以后面的参数都要使用指名方式;因为指名方式可能已经破坏了参数原始的定义顺序了.

 ②OUT模式参数----->"输出类型参数,表示这个参数在存储过程中已经被赋值,并且参数值可以传递到当前存储过程以外的环境中"

老规矩--看栗子(栗子说:"为什么老是看我 ~_~!!")

【创建一个存储过程，要求定义两个OUT模式的字符类型的参数，然后在dept表中检索到的一行部门信息存储到这两个参数中】

create or replace procedure select_dept_out(

    m_id in number,                            --定义IN模式变量,要求输入人员编号

    m_dname out  dept.dname%type,    --定义OUT模式变量,可以存储部门名称并输出

    m_deptno out dept.deptno%type) is
begin
select dname,deptno
into m_dname,m_deptno
from dept
where id=m_id;                           --检索某个员工号的员工信息
exception
when no_data_found then                   --如果select语句没有结果返回

      dbms_output.put_line('该员工编号不存在');   --输出信息
end select_dept_out;

　　当调用或者执行以上存储过程时,都需要定义变量来保存这两个out参数，下面对OUT模式如何调用或执行分别举例子说明：

一》在PL/SQL块中调用OUT模式的存储过程:在PL/SQL块的DECLARE部分定义与存储过程中out参数兼容的若干变量

【首先在PL/SQL块中声明若干变量，然后调用select_dept_out存储过程,并将定义的变量传入该存储过程,以便接收out参数的返回值】

　　　　Oracle中如何写存储过程

具体过程：执行上述代码时，声明的两个变量会被传入到存储过程中，但存储过程执行时，其中的out参数会被赋值，存储过程执行完毕后，OUT参数的值会在调用处（begin）返回，之后定义的两个变量(declare)就能得到传回来的值，就可以在存储过程之外任意使用了。

二》使用Exec执行OUT模式的存储过程:使用Exec命令需要在SQL*Plus环境中使用variable关键字声明两个变量,用来存储out参数的返回值

 【使用variable关键字声明两个变量,分别用来存储部门名称和部门编号,然后使用exec命令执行存储过程,并传入声明的两个变量来接收out参数的返回值】

  　　　  Oracle中如何写存储过程

Exec执行的方式是无法直接看到结果的,可以通过两种方式输出变量的值: ①print命令 ②select语句

#>print命令

　　　　Oracle中如何写存储过程

#>select语句

Oracle中如何写存储过程

③IN OUT模式参数

开始之前咱们现总结一下IN和OUT的特性：

在执行存储过程时,

IN参数只能根据调用者传入的值去执行存储过程,不能被修改;

OUT参数只能等待存储过程执行完毕为其赋值再供外界使用,不能像IN一样为存储过程提供数据;

到這里,大家想一想:如果我要是想【计算一个数的平方或者平方根】,这种存储过程怎么写呢？

岂不是要是用IN传入一个数,再用OUT定义一个变量来接收了？不过大家仔细想一下，我们想要计算的值传进去后，就没用了，如果再原路将计算结果返还回来，那该多好，就不用单独定义OUT参数了，结果就有了IN OUT模式参数

IN OUT就是解决这个问题的;兼顾了IN和OUT的参数特性调用存储过程时，上面的分析如果看懂了，这里就不详细解释定义了。就是给定一个参数，在存储过程执行过程中，发生了改变，之后再将该参数原路返还给调用者；

创建存储过程，计算一个数的平方或平方根：

create or replace procedure pro_square(

    num in out number,

    flag in boolean) is

    i int:=2;                --表示计算平方

begin

    if flag then             --if语句,如果为true

      num:=power(num,i);     --计算平方

    else                     --否则

      num:=sqrt(num);        --计算平方根

      end if;

end pro_square;

执行上述存储过程：

　　  Oracle中如何写存储过程

--->IN参数的默认值(IN类型是可以设定默认值的,上面忘了说了,放在最后吧,重新建了表dept2)

IN模式的参数是可以设定默认值的,当执行时,调用者没有传值的话,可以使用默认值赋值:看栗子

【创建一个存储过程，定义3个IN参数，并将其中的两个参数设置为初始默认值；然后将这三个IN参数的值插入到dept中】

create or replace procedure insert_dept2_default(

      num_deptno in number,

      var_dname in varchar2 default '综合部',        --定义部门名称并设定默认值

      var_loc in varchar2 default '北京') is

begin

  insert into dept2 values(num_deptno,var_dname,var_loc);  --插入一条记录

end insert_dept2_default;

　执行执行过程：

Oracle中如何写存储过程

边学习边总结了存储过程的基本写法规则也就这些,希望能给大家一些启发~~~~

以上代码都是个人亲自敲的,执行过后都是没有问题的。如果哪位大牛觉得代码有不妥的地方,希望不吝赐教，万分感谢！

每天学习一点点,总有一天你的知识会堆积成巍峨的高山!!加油!

上一篇：省赛13 Alice and Bob（二进制，找规律）


下一篇：Oracle GoldenGate常用参数