oracle----过程(procedure)介绍和使用

存储过程是一组为了完成特定功能的sql语句集合,经过编译后存储在数据库中。用户通过制定存储过程的名字并给出参数(如果该过程带有参数)来执行他。

存储过程是由流控制和sql语句书写的过程,这个过程经过编译和优化后存储数据库服务器中,应用程序使用时只要调用即可。

存储过程就是具有名字的一段代码,用来完成一个特定的功能

创建的存储过程保存在数据库的数据字典中

在oracle中,若干个有联系的过程可以组合在一起构成程序包。

procedure优点

  1. 存储过程只有在创造时进行编译,以后每次执行存储过程都不需要再重新编译,而一般sql语句没执行一次就编译一次,所以使用存储过程可以提高数据库执行速度

  2. 当对数据库进行复杂性操作时,如(对多个表进行Update\Inset\Query\Delete)能将这个复杂操作作用存储过程封装起来和数据库提供食物处理结合一起使用

  3. 存储过程可以重复使用,能减少数据库开发人员的工作量。

  4. 安全性高,可设定只有某用户才具有对制定存储过程的使用权

    实例

    1. 创建数据库,备份数据库用于示例操作:

      mysql> create database db1;
      Query OK, 1 row affected (0.01 sec)
      mysql> use db1;
      mysql> create table players as select * from 2022sqlstudy.student;
      Query OK, 8 rows affected (0.05 sec)
      Records: 8  Duplicates: 0  Warnings: 0
      mysql> create table score as select * from 2022sqlstudy.score;
      Query OK, 18 rows affected (0.05 sec)
      Records: 18  Duplicates: 0  Warnings: 0
      #若要删除表 则 drop table 表名
      
    创建型新数据库db1,将2022sqlstudy中的student表和score复制到players和score中,**表名字可以再改改**
    
    **mysql更改表名**
    
       ```sql
       mysql> alter table score rename to matches;
           -> ;
           -> ;
           -> ;
           -> $$
       Query OK, 0 rows affected (0.02 sec)
       #改了结束符号位$$但是没意识到
    
  5. 存储过程的例子,删除给定球员参加的所有比赛【删除给定学生的所有成绩信息】

       mysql> create procedure delete_matches(IN p_playerno INTEGER)
           -> begin
           ->  delete from matches
           ->  where S_ID=p_playerno;
           -> end $$
       Query OK, 0 rows affected (0.01 sec)
       mysql> delimiter ;
       mysql>
    

    重新将结束符换成;

    **解析:**在默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名称前面加上数据库名做前缀。在定义过程中,使用delimiter 命 令 将 语 句 结 束 符 换 为 命令将语句结束符换为 使得过程体中使用的分号被直接传递到服务器,更不会被客户端(如mysql)解释。

    调用存储过程:

       call sp_name[(传参)]
       mysql> call delete_matches(7);
       Query OK, 2 rows affected (0.01 sec)
       
       mysql> select * from matches;
       +------+------+---------+
       | s_id | c_id | s_score |
       +------+------+---------+
       |    1 |    1 |   80.00 |
       |    1 |    2 |   90.00 |
       |    1 |    3 |   99.00 |
       |    2 |    1 |   70.00 |
       |    2 |    2 |   60.00 |
       |    2 |    3 |   80.00 |
       |    3 |    1 |   80.00 |
       |    3 |    2 |   80.00 |
       |    3 |    3 |   80.00 |
       |    4 |    1 |   50.00 |
       |    4 |    2 |   30.00 |
       |    4 |    3 |   20.00 |
       |    5 |    1 |   76.00 |
       |    5 |    2 |   87.00 |
       |    6 |    1 |   31.00 |
       |    6 |    3 |   34.00 |
       +------+------+---------+
       16 rows in set (0.00 sec)
    

    **解析:**在存储过程中设置了需要传参的变量p_playerno,调用存储过程的时候,通过传递参数将s_id值为8的行进行删除操作,也就是进行存储过程里的sql操作。

  6. 存储过程体

    1. 存储过程体包含了在过程调用时必须执行的语句,如dml\ddl\if-then-else和while-do、声明变量的declare等

    2. 过程体格式:以begin开头,end结尾(能够嵌套)

         begin
         	begin
         		begin
         			statements;
         		end;
         	end;
         end;
      

      **注意:**每个嵌套块以及其中的每条语句,必须以分号结尾,表示过程体结束的begin-end块(又叫做复合语句compound statement)则不需要分号。

      为语句块贴标签

         [begin_label:]BEGIN
         	[statement_list]
         END [end_label]
      

      例如:

         label1:BEGIN
         	label2:BEGIN
         		label3:BEGIN
         			statements;
         		end label23;
         	end label2;
         end label1;
      

      标签的作用:

      1. 增强代码可读性
        2. 在某些语句(例如leave和iterate语句),需要用到标签
  7. 存储过程中的参数
    • mysql存储过程的参数用字存储过程的定义,主要三种参数类型,IN\OUT\INOUT,形式如:

         create procedure 存储过程名([[IN|OUT|INOUT]参数名 数据类型...])
      
      • IN输入参数:表示调用者先过程传入值(传入值可以是字面量或变量)

      • OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

      • INOUT传入传出参数:既可以表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

      • 1)in输入参数

           mysql> delimiter $$
           mysql> create procedure in_param(in p_in int)
               -> begin
               ->  select p_in;
               ->  set p_in=2;
               ->  select p_in;
               -> end $$
           mysql> set @p_in=1;
           Query OK, 0 rows affected (0.00 sec)
           
           mysql> call in_param(@p_in);
           +------+
           | p_in |
           +------+
           |    1 |
           +------+
           1 row in set (0.00 sec)
           
           +------+
           | p_in |
           +------+
           |    2 |
           +------+
           1 row in set (0.01 sec)
           
           Query OK, 0 rows affected (0.01 sec)
           
           mysql> select @p_in;
           +-------+
           | @p_in |
           +-------+
           |     1 |
           +-------+
           1 row in set (0.00 sec)
        

        p_in在存储过程中被修改,但是并不影响@p_in的值,因为p_in是局部变量,@p_in为全局变量

      • 2)out输出参数

           mysql> delimiter //
           mysql> create procedure out_param(out p_out int)
               -> begin
               ->  select p_out;
               ->  set p_out=2;
               ->  select p_out;
               -> end
               -> //
           Query OK, 0 rows affected (0.00 sec)
           
           mysql> delimiter ;
           mysql> set @p_out=1;
           Query OK, 0 rows affected (0.01 sec)
           
           mysql> call out_param(@p_out);
           +-------+
           | p_out |
           +-------+
           |  NULL |
           +-------+
           1 row in set (0.00 sec)
           	#这里为null,主要是因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
           +-------+
           | p_out |
           +-------+
           |     2 |
           +-------+
           1 row in set (0.00 sec)
           
           Query OK, 0 rows affected (0.01 sec)
           
           mysql> select @p_out;
           +--------+
           | @p_out |
           +--------+
           |      2 |
           +--------+
           1 row in set (0.00 sec)
           	#调用了out_param存储过程,输出参数,改变了p_out变量的值
        
        • 3)INOUT输入参数

             mysql> create procedure inout_param(inout p_inout int)
                 -> begin
                 ->  select p_inout;
                 ->  set p_inout=2;
                 ->  select p_inout;
                 -> end
                 -> $$
             Query OK, 0 rows affected (0.00 sec)
             mysql> delimiter ;
             mysql> set @p_inout=1;
             Query OK, 0 rows affected (0.00 sec)
             
             mysql> call inout_param(@p_inout);
             +---------+
             | p_inout |
             +---------+
             |       1 |
             +---------+
             1 row in set (0.00 sec)
             
             +---------+
             | p_inout |
             +---------+
             |       2 |
             +---------+
             1 row in set (0.01 sec)
             
             Query OK, 0 rows affected (0.02 sec)
             mysql> select @p_inout;
             +----------+
             | @p_inout |
             +----------+
             |        2 |
             +----------+
             1 row in set (0.00 sec)
             #调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
          

    注意

    1. 如果过程没有参数,也要在过程名后面加上小括号

         create procedure sp_name()....
      
    2. 确保参数的名字不等于列的名字,否则在过程体中,参数名被当作列名来处理

    建议

    1. 输入值使用in
      2. 返回值使用out
      3. inout尽可能少用

变量

  1. 变量声明

    局部变量声明一定要放在存储过程体的开始:

    declare variable_name [,variable_name…]datatype[default value];

    其中,datatype为Mysql的数据类型,如int,float,date,varchar(length)

查询Mysql中存在的存储过程

   mysql> select name from mysql.proc where db='db1';
   +----------------+
   | name           |
   +----------------+
   | delete_matches |
   | inout_param    |
   | in_param       |
   | out_param      |
   +----------------+
   4 rows in set (0.00 sec)

Mysql存储过程中的控制语句

  1. 变量作用域

       mysql> delimiter //
       mysql> create procedure proc3()
           ->  begin
           -> declare x1 varchar(5) default 'outer';
           -> begin
           -> declare x1 varchar(5) default 'inner';
           -> select x1;
           -> end;
           -> select x1;
           -> end;
           -> //
       Query OK, 0 rows affected (0.00 sec)
       
       mysql> call proc3();
           -> //
       +-------+
       | x1    |
       +-------+
       | inner |
       +-------+
       1 row in set (0.00 sec)
       
       +-------+
       | x1    |
       +-------+
       | outer |
       +-------+
       1 row in set (0.01 sec)
       
       Query OK, 0 rows affected (0.01 sec)
    

    内部的变量在其作用域范围内享有更高的优先权,当执行到 end。变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储过程外再也不能找到这个申明的变量,但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

  2. 条件语句

    1. if-then-else

         mysql> delimiter //
      mysql> create procedure proc2(IN parameter int)
             -> begin
          -> declare var int;
             -> set var=parameter+1;
             -> if var =0 then
             -> insert into t values(17);
             -> end if;
             -> if parameter=0 then
             -> update t set s1=s1+1;
             -> else
             -> update t set s1=s1+2;
             -> end if;
             -> end;
             -> //
         Query OK, 0 rows affected (0.02 sec)
      

      调用的时候报错:

      mysql> proc2(0);
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'proc2(0)' at line 1
      
      mysql> set @p_in = 0;
      Query OK, 0 rows affected (0.00 sec)
      mysql> call proc2(@p_in);
      ERROR 1054 (42S22): Unknown column 'parameter' in 'field list'
      

      如何解决?

      1. case when then

遇到问题:

  1. 用JES用户登录时create procedure显示权限不足

    create or replace procedure scott.p_orocedure_demo(i_a int number,
    *1 行出现错误:
    ORA-01031: 权限不足
    

    尝试解决 以超级用户免密登录

    C:\Users\罗蒙>sqlplus /@orcl as sysdba
    
  2. procedure简单例子【文韬给的】

    1. delimiter //
      create or replace procedure vttpc.vcinv_demo_01(pempno in number) is temvar varchar2(100);
      begin
      tmpvar:=0;
      dbms_output.put_line('将开始查询数据库:');
      select ename into tmpvar from scott.emp where empno=pempno;
      dbms_output.put_line('员工名称为:'||tmpvar);
      exception when no_date_found then
      dbms_output.put_line('没有找到该员工记录');
      when others then raise
      end vcinv_demo_01;
      //
      delimiter ;
      
    2. 对用户定义变量进行简单赋值

      首先初始化一定用户定义标量@tmpvar这里的:=0,我并不了解是什么意思

      mysql> set @tmpvar:=0;
      Query OK, 0 rows affected (0.01 sec)
      
      := 是覆盖之前的值。
      
      mysql> select @tmpvar;
      +---------+
      | @tmpvar |
      +---------+
      |       0 |
      +---------+
      1 row in set (0.00 sec)
      

      但是现实该值的时候为0

       select s_id into @tmpvar from players where s_id=1;
      Query OK, 1 row affected (0.01 sec)
      
      mysql> select @tempvar;
      +----------+
      | @tempvar |
      +----------+
      | NULL     |
      +----------+
      1 row in set (0.00 sec)
      # 这里很奇怪,当用select语句想将s_id=1的id赋值给@tmpvar时,得到的结果为null
      
      mysql> select s_name into @tmpvar from players where s_id=1;
      Query OK, 1 row affected (0.00 sec)
      
      mysql> select @tmpvar;
      +---------+
      | @tmpvar |
      +---------+
      | 赵雷    |
      +---------+
      1 row in set (0.00 sec)
      #但用s_name也就是varchar类型赋值时,可以看到@tmpvar被成功赋值了
      
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐