PL/SQL 存储过程介绍六)

oracle 存储过程(procedure)：

一、介绍
   存储过程，简单的认为就是SQL + 一些简单逻辑 的综合体。
   这里大概记录一下优点：
   1.复杂业务(同时对多张表进行CRUD操作)，我们如果执行多条SQL，可能会有多个连接。过程可以放在一个事务里面，时间快
   2.程序处理的业务，有可能会出现无法预判的BUG ，而过程只要数据没错，就不容易出错，安全性高
   3.利用数据内置的操作，数据与数据的交互会更加快捷。
   4.创建的时候就已经编译了，传入SQL执行，有些会执行一次编译一次，执行效率就高了。
   5.可以重复使用，并行开发，分布测试。
   6.可以设置权限，安全性高
   
   缺点：
   1.调试麻烦，估计是我们用好！
   2.数据库迁移麻烦，可能从oracle到mysql迁移等等。
   3.维护麻烦，过多的过程，一旦久远，需求变化，维护人员看着都头疼！

 

二、简单学习：
    1.语法：
    -- 名字有可读性，OR REPLACE 默认会覆盖同名的
    CREATE OR REPLACE PROCEDURE PROCEDURE_NAME
    IS      -- 后面PL/SQL 开始
    Begin   -- 开始
    NULL;   -- PL/SQL 体，什么都不做，必须要NULL
    END;    -- 结束

    1.1 带参数的过程
    -- in 为输入参数，可以省略，out 为输出参数，不可省略
    -- 用逗号隔开，不限定长度
    CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(参数名1 in 类型，参数名2 out 类型)
    as
    -- 可以设置变量，参考前面的PL/SQL
    变量1  类型(值范围); -- 比如：v_name varchar2(20)    
    Begin
    null;
    end;
从总体来看，过程 就是 CREATE OR REPLACE PROCEDURE PROCEDURE_NAME ，加上了 前2节讲的PL/SQL 语法，没啥特别的.

 

三、简单实例
    table:test_table: user_id number, password varchar(20),user_name varchar(20)
    
    1.业务： 把所有user_name为ran 的改为Qi,并且把user_name 为ran2 的删除
    
     create or replace procedure update_delete_pro
	is
	begin
  	  update test_table set user_name='Qi'  where user_name ='ran';
  	  delete from test_table where user_name = 'ran2';
	end;

     在pl/sql 工具中：可以exec update_delete_pro;也可以在pl/sql 块儿中：
     declare
     begin
  	update_delete_pro; 
     end;
 
   2. 业务:根据传入user_id 的奇偶，判断查询  user_name,还是passsword;
     create or replace procedure select_by_id_pro(v_user_id number,val out varchar2)
	is
	begin
  		if  v_user_id mod 2 = 1 then
     	select user_name into val from test_table where user_id = v_user_id;
 		elsif  v_user_id mod 2 = 0 then
     	select password into val from test_table where user_id = v_user_id;
 		end if;
	end;

    调用：
    set serveroutput on;
	declare
		parm varchar2(20);
	begin
		select_by_id_pro(5,parm);
		dbms_output.put_line(parm);
	end;
  
    3.业务：查询user_name = ran 和人数，以及全部的结果集，返回两个结果
     	
        -- 这里默认是系统游标变量
	create or replace procedure getList_pro(num out number,cur_arg out sys_refcursor)
	is
	begin
  		open cur_arg for select * from test_table;
  		select count(*) into num from test_table where user_name = 'ran';
	end;
     调用：

      declare
       args Sys_RefCursor; -- 定义游标类型
       rw_stu test_table%rowtype;-- 定义表类型
       num number;
      begin
        getList_pro(num,args);
        dbms_output.put_line(num);
        -- 这里不能直接用for ,容易报“不是过程或尚未定义”
        loop
          fetch args into rw_stu;
          exit when args%notfound;
          dbms_output.put_line('name:'||rw_stu.user_name);
      end loop;
    end;

 

   小结： 

    过程就是编译好的PL/SQL 块，加快执行和方便调用。一般情况 要加上 包（package） 配合使用。

    这里先介绍简单语法，做个基本了解，上手就行，后续再加