Oracle中如何写存储过程

>存储过程是什么?

   存储过程是一种命名的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之后,

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

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

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

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

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

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

    

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

        

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

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

    

注: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块】  

     

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

              

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

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

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

     

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

 ②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参数的返回值】

    

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

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

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

       

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

  #>print命令

    

 #>select语句

               

③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;

 执行上述存储过程:

    

 

--->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;

 执行执行过程:

    

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

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

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

 

posted @ 2019-03-30 19:30  我是白小白  阅读(32418)  评论(2编辑  收藏  举报