Oracle 存储过程学习笔记(二)

 

1. 首先把昨天带参的存储过程执行一下

       declare

       n number;

       begin

              n:=1;

              test(num=>n);

       end;

       注;在调用存储过程时，=>前面的变量为存储过程的形参且必须于存储过程中定义的一致，而=>后的参数为实际参数。当然也不可以不定义变量保存实参，可写成如下形式：

       Begin

              test(num=>1);

       end;

       这样我们就能更清楚得看到给存储过程赋值的格式了。后面打算用存储过程操作一些表，按照增删改查的顺序依次建立存储过程。

2. 插入

       CREATE OR REPLACE

       procedure proc_test_Insert_Single(e_no in number,e_name in varchar ,s in varchar,d in               varchar)

       as

       begin

              insert into emp (emp_id,emp_name,salary,birthday) values (e_no,e_name,s,d);

       end;

       调用：

       DECLARE

       i NUMBER;

       n varchar(5);

             s varchar(11);

      d varchar(10);

       BEGIN

             i:=10;

             n := 'text11';

             s:='3998';

             d:='1998-02-02';

             PROc_TEST_Insert_single(e_no => i,e_name=>n,s=>s,d=>d);

       END;

       注：调用存储过程声明varchar时，必须限定长度，即斜体的部分不能少。同时如果给变量赋值时大于限定的长度了，则会提示ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小。

 

3． 更新

       create or replace procedure proc_test_update_Single(e_no in number,s in varchar)

       as

       begin

              UPDATE emp set salary =s where emp_id=e_no;

       end;

       调用：

       DECLARE

             n NUMBER;

             s varchar(11);

       BEGIN

             n := 2;

             s:=3998;

             PROc_TEST_UPdate_single(e_no => n,s=>s);

       END;

4. 号外，今天在开发过程中正好有个数据库更新操作可用存储过程实现，顺便练习一下，需求是将一个表中的ID字段，查出来更新到另一个表中，两个表通过b_bs和b_kgh关联。存储过程如下：

       create or replace procedure update_yygzdbid

       as

             bs varchar(20);

             kgh varchar(20);

             bid number;

             cursor c_db is select b_id,b_bs,b_kgh from pmdcdb;

       begin

             for temp in c_db loop

          update yygz_db set b_id= temp.b_id where g_bs=temp.b_bs and g_bh=temp.b_kgh;

             end loop;

       end;

       运行这个存储过程：

       Begin

              update_yygzdbid();

       end;

       说明：

       (1).在没有参数的存储过程定义时存储过程的名称不需要括号，写成update_yygzdbid()是错误的，

       (2). cursor c_db是定义一个游标，获得查询语句的结果集，

       (3). For temp in c_bd loop

                     Begin

                     End；

              End loop

              是循环游标，其形式类似于C#中的foreach,  获得字段：temp.b_id。

5. 查询

       最后我们做一个查询的存储过程，能够返回一个值，注意不是结果集，结果集是明天的目标。

       CREATE OR REPLACE

       procedure proc_test_Select_Single(t in varchar,r out varchar )

       as

       begin

              select salary into r from emp where emp_name=t;

       end;

       这个存储过程使用了2个参数，并分别出现了IN和OUT，in代表输入，out用于输出，从下面的语句也可以看到salary写入到变量r中了，这个r我们可以在调用存储过程后得到。

       这时编译后会出现一个Warning(1,48): PLW-07203: 使用 NOCOPY 编译器提示可能对参数 'R' 有所帮助，那么nocopy是什么呢，nocopy主要是针对in|out record/index-by table/varray/varchar2提高效率使用的, 对于number使用nocopy与否基本没有影响.所以在'enable:performance'情况下不会对number提示warning.

       我们把第一行改为：procedure proc_test_Select_Single(t in varchar,r out nocopy varchar )

现在即使对in的varchar没有使用nocopy也不会提示警告，

       DECLARE

             T varchar2(4);

             R VARCHAR2(4);

       BEGIN

             T := 'zz';

             PROC_TEST_SELECT_SINGLE(T => T,R => R );

             DBMS_OUTPUT.PUT_LINE('R = ' || R);

       END;

       运行后即可在输出中看到结果了。