orcle存贮过程

于 2016-11-25 11:15:29 发布
阅读量359 收藏
点赞数
分类专栏： orcle
用了两年Oracle还没写过存储过程，真是十分惭愧，从今天开始学习Oracle存储过程，完全零起点，争取每日一篇学习笔记，可能开始认识的不全面甚至有错误，但坚持下来一定会有收获。

1.建立一个存储过程

        create or replace PROCEDURE firstPro

        IS

        BEGIN

        DBMS_OUTPUT.PUT_LINE('Hello World!');

        END;

其中IS关键字替换为AS关键字结果不会出现任何变化，大多认为他们是等同的，但也有一种说法解释为：一般PACKAGE或者单独的FUNCTION, PROCEDURE都用AS，PACKAGE中的FUNCTION, PROCEDURE用IS。

        DBMS_OUTPUT.PUT_LINE('Hello World!'); 是一个输出语句。

2.执行存储过程

Oracle返回结果需要使用包，那么存储过程似乎只能在数据库中执行或被其他调用，编程语言似乎并不能直接调用存储过程返回数据，是否能执行他有待研究。那么首先在数库中执行上面的存储过程。

        BEGIN

        FirstPro（）；//注意有括号

        END;

      运行后输出HelloWorld。

3.下面写一个稍复杂的存储过程，他定义了变量，进行了运算，输出一个count操作所用的时间。

      CREATE OR REPLACE procedure testtime  

     is  

     n_start  number;  

     n_end  number; 

     samplenum number;

     use_time number;

     begin  

            n_start:=dbms_utility.get_time;  

            select count(*) into samplenum from emp; 

            n_end:=dbms_utility.get_time;  

            use_time:=  n_end  -  n_start;  

            dbms_output.put_line('This  statement  cost  '|| use_time ||'  miliseconds');  

     end;  

4.下面试验下怎么能给存储过程赋值

      CREATE OR REPLACE procedure test(num in number)is

      begin

             dbms_output.put_line('The input numer is:' ||num);

      end ;

      今天的就到这，明天将调用这个存储过程，并试验一写对表的操作。

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

      iNUMBER;

      nvarchar(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 innumber,s in varchar)

      as

      begin

            UPDATE emp set salary =s whereemp_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 procedureupdate_yygzdbid

      as

            bs varchar(20);

            kgh varchar(20);

            bid number;

            cursor c_db is select b_id,b_bs,b_kgh frompmdcdb;

      begin

            for temp in c_db loop

        update yygz_db set b_id= temp.b_id where g_bs=temp.b_bs andg_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 whereemp_name=t;

      end;

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

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

      我们把第一行改为：procedure proc_test_Select_Single(tin 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;

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

三、

1.今天我们首先写一个涨工资的存储过程，给每个低于5k工资的人涨点钱。

      CREATE OR REPLACE PROCEDURE p_test(forRaise innumber)

      as

   begin

     for v_emp in (select * from emp) loop

        if(v_emp.salary<'5000')then

       update emp set salary =(v_emp.salary+forRaise) whereemp_id=v_emp.emp_id;

       end if;

     end loop;

      end;

      调用：

      DECLARE

            FORRAISE NUMBER;

      BEGIN

            FORRAISE :=1;

             P_TEST(FORRAISE =>FORRAISE);

      END;

      这里要注意两个地方：

（1）      循环中begin和end不是必须的

（2）      这里增加了if语句，其格式比较简单就不细说了。

（3）      这里没有定义游标，在游标的位置直接用select语句代替了。

      2．这里顺便介绍下另外一种循环，while循环，实现同样的功能

             CREATE OR REPLACE PROCEDURE p_test(forRaise innumber)

             as

                    cursor c is select * from emp;

                    v_row emp%rowtype;

             begin

            open c;

            fetch c into v_row;

            while c%found Loop

                if(v_row.salary<'5000')then

                      update emp set salary =(v_row.salary+forRaise) whereemp_id=v_row.emp_id;

                end if;

         fetch c into v_row;

            end loop;

            close c;

             end;

             说明：

（1）      这里需要定义一个游标，还要定义一个emp%rowtype类型的变量，%前面是表名，后面表示这个表的一行，

（2）      在使用游标前还要显示的打开游标，并将其赋值到row中，使用后关闭游标。

（3）      C%found表示只有row中有值的时候才会进行循环。

（4）      经过对比发现于while循环相比，for循环更像是C#中的foreach，使用起来方便很多。

（5）      另从9i开始提供的动态游标类型sys_refcursor，以前的版本必须要先创建一个ref cursor的类型，现在多个

3.       现在我们使用程序调用下涨工资的存储过程，这个存储过程是没有返回值的。

OracleConnection conn = new OracleConnection();  //创建一个新连接

conn.ConnectionString = "Data Source='ds';user id='id';password='pwd';"; OracleCommand cmd =new OracleCommand("P_TEST", conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter("forRaise",OracleType.UInt32);

p1.Value = 1;

p1.Direction =System.Data.ParameterDirection.Input;

cmd.Parameters.Add(p1);

conn.Open();

int r=cmd.ExecuteNonQuery();

   conn.Close();

   这样我们就可以给员工涨工资了，说明：

（1）        虽然给多个人涨了公司，但r的值是1，他只调用了1个存储过程，或者说受影响的只是1个。

（2）        参数P1的名字必须和存储过程中的一样否则会提示：调用'P_TEST' 时参数个数或类型错误。

4.        现在我们试着从存储过程中得到点结果吧，我先看看我给几个人涨了工资，我每个月一共要多付多少钱了。

        改动存储过程：

        CREATE OR REPLACE PROCEDURE p_test(forRaise in number,res outnumber)

        is

   begin

     res:=0;

     for v_emp in (select * from emp) loop

           if(v_emp.salary<'4000') then

         update emp set salary =(v_emp.salary+forRaise) whereemp_id=v_emp.emp_id;

         res:=res+1;

       end if;

     end loop;

        end;

        增加了一个out 的number型，记录改动的次数。然后相应的调整C#程序，获得这个改动的次数。

OracleCommand cmd = new OracleCommand("P_TEST", conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter("forRaise",OracleType.UInt32);

p1.Value = 4;

p1.Direction =System.Data.ParameterDirection.Input;

OracleParameter p2 = new OracleParameter("res",OracleType.UInt32);

p2.Value = 10;

p2.Direction =System.Data.ParameterDirection.Output;

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

conn.Open();

int r=cmd.ExecuteNonQuery();

 conn.Close();

MessageBox.Show(“你已经给:”+p2.Value.ToString()+“人涨了工资”);

好了，今天就到这，下次返回数据集。

Oracle使用存储过程返回结果集必须使用包，包包括包头和包体两部分，包头是定义部分包体是具体的实现

   包头：

   CREATE OR REPLACE

   PACKAGE pkg_test_select_mul

   AS

   TYPE myrctype IS REF CURSOR; 

   PROCEDURE proc(s number, res OUT myrctype);

ENDpkg_test_select_mul;

   这里定义了个一个游标和一个存储过程。

   包体：

   CREATE OR REPLACE

   PACKAGE BODY "PKG_TEST_SELECT_MUL" AS

      PROCEDURE proc(s in number,res OUT myrctype)

      IS       

      BEGIN       

         OPEN res FOR Select  emp_id,emp_Name,salary,birthday From         emp where salary> s;

      END proc;   

   END PKG_TEST_SELECT_MUL;

   这里实现里包头中定义的存储过程，实现了查询工资超过一定数额的人的信息，而游标则不用重新定义了，且存储过程中的参数名必须和定义中的一致。下面我们看一下C#的调用部分。

   OracleConnection conn =newOracleConnection();  //创建一个新连接

           conn.ConnectionString ="DataSource='" + "MyTest" +"';user id='" +"azkaser" + "';password='" +"sti" + "';";  //写连接串 

           OracleCommand cmd =new OracleCommand("PKG_TEST_SELECT_MUL.proc",conn);

           cmd.CommandType =CommandType.StoredProcedure;

           OracleParameter p1 =new OracleParameter("s",OracleType.Number);

           p1.Value =4000;

           p1.Direction =ParameterDirection.Input;

           OracleParameter p2 =new OracleParameter("res",OracleType.Cursor);

           p2.Direction =ParameterDirection.Output;

 

           cmd.Parameters.Add(p1);

           cmd.Parameters.Add(p2);

           conn.Open();

           OracleDataReader myReader =cmd.ExecuteReader();

           while(myReader.Read())

           {

               MessageBox.Show(myReader.GetString(1));

           }

           conn.Close();

   程序将得到的结果存放在OracleDataReader的对象中。

   到此简单的Oracle存储过程操作就此就全部完成了，程序写的很随便，目的就是实现功能，将来有时间会进一步

Z-Sample
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值