oracle 存储过程的学习

oracle存储过程的学习。

 

 

1.创建一个简单的存储过程。

 

create or replace procedure pro_test

is

id number;

begin

id:=1;

end pro_test;

 

这是最简单的一个啥都没干的存储过程，虽然设了一个变量id并赋了一个值，但没有任何意义。当然，这个只是用来说明基本语法的，没有其他作用。

首先可以看到基本结构就是

 

 

create or replace procedure 存储过程名

is

变量的声明

begin

存储过程执行主体

end 存储过程名;

 

 

变量的赋值要注意使用":="

 

 -------------------------------------------------------------------------------------------------------------

2.创建一个带传入参数的存储过程。

这里我们创建一个给某张表插入记录的存储过程

假设我们有一个数据表mytb，里面有两个字段:id number(10), username varchar2(10)，具体存储过程如下

 

 

create or replace procedure pro_insert(id in number, username in varchar2)

is

insert_sql varchar2(100);

begin

    insert_sql:='insert into mytb values(||id||','''||username||''')';

    execute immediate insert_sql;

end pro_insert;

 

 

调用的时候写：call pro_insert(1,'test');

 

 

这里就是传入要插入的两个字段的值，然后生成sql语句并执行。

这里我们可以看到带参数的存储过程格式是：

 

 

 

create or replace procedure 存储过程名(参数1 in/out 类型, 参数2 in/out 类型...)

is

变量的声明

begin

存储过程执行主体

end 存储过程名;

 

 

 

 

这个存储过程里有几点要学习的：

1.存储过程中貌似是不能直接写insert啊drop table等语句，都要有个sql语句，然后execute才行。

2.字符串与变量的连接。用||

3.字符串中带单引号的处理。用两个单引号。这里，由于username 是字符串，在插入的时候要用单引号引出来 ，所以在sql语句中加了两个单引号。

4.动态sql不用加分号，所以在给insert_sql赋值的时候并没有在语句的最后附上分号。

 

 ---------------------------------------------------------------------------------------------------------

3.创建一个带输出参数的存储过程。

 这里我们创建一个做加法的存储过程。

 

 

create or replace procedure pro_plus(number1 in number, number2 in number, plus_result out number)

is

begin

plus_result:=number1+number2;

end pro_plus;

 

调用的时候写

 

SQL>var plus_result number;

SQL>call pro_plus(1,2,:plus_result);

显示调用完成，然后你可以打印下结果看看

SQL>print plus_result;

 

这里可以看到一个是输出的参数用out，在调用的时候要先声明这个参数，且在调用存储过程的时候用':参数名'来表示为输出参数。

 

 --------------------------------------------------------------------------------------------------------

4.创建一个返回表中数据的存储过程。

 假设就返回刚才那个表mytb1的第一行username字段的数据。

 

 

create or replace procedure pro_get(uname out varchar2)

is

begin

     select username into uname from mytb1 where rownum=1;

end pro_get;

 

将记录中某个值赋给变量或者参数用into

 

-----------------------------------------------------------------------------------------------------------

5.带循环的存储过程。

 计算1一直加到10的结果

 

 

create or replace procedure pro_loop(loop_result out number)

is

i number:=1;

begin

loop_result:=0;

while i<=50 loop

   begin

     loop_result:= loop_result+i;

     i:=i+1;

   end;

end loop;

end pro_loop;

 

 

------------------------------------------------------------------------------------------------------------

6.带游标的存储过程。

oracle必须用游标来存储select返回的记录集。

这里写一个删除某个用户下所有表的存储过程

 

 

 

create or replace procedure pro_droptableis

cursor cur is select table_name from user_tables;

drop_sql varchar2(1000);

begin

for tbname in cur loop

     begin

       drop_sql:='drop table '||tbname.table_name;

       execute immediate drop_sql;

    end;

end loop;

end pro_droptable;

 

这里就是定义了一个cur游标来接住select结果集。

select table_name from user_tables 指的是返回该用户下的表名称。

循环中将指定记录行赋给tbname，然后在后面的sql中用tbname.table_name来得到该记录中table_name的值。

 

--------------------------------------------------------------------------------------------------------------

7.带判断的存储过程

 我们这里写一个存储过程判断是否为偶数。

 

 

create or replace procedure pro_iseven(num in number, iseven out varchar2)

is

begin

iseven:='false';

if num mod 2  == 0 then

   begin

      iseven:='true';

   end;

end if;

end pro_iseven;

 

没有将iseven定义为boolean，因为oracle貌似没有boolean这种类型。

 

--------------------------------------------------------------------------------------------------------------

8.带异常的存储过程。

在执行存储过程中有可能会出现异常，但为了能让存储过程正常执行。在存储过程中添加一个异常的处理。

关于异常还在研究中，以后再补充。