欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle中如何写存储过程

程序员文章站 2022-05-24 19:27:56
>存储过程是什么? 存储过程是一种命名的PL/SQL程序块,输入、输出参数数量【0个或多个】,通常没有返回值【也有特殊的情况】。 运行SQL语句时,Oracle是要先编译再执行的;而存储过程是已经编译好的代码,所以被调用或引用时,执行效率非常高。 存储过程被保存在数据库中,但是不能被SQL直接执行或 ......

>存储过程是什么?

   存储过程是一种命名的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中如何写存储过程

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

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

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