oracle10g 存储过程学习

存储过程：

create or replace procedure pro_me is begin null end

create or replace procedure pro_me (param1 in varchar2(200),param2 out number)

as vs_msg int;

   vs_content varchar2(300);

begin select count(*) into v_msg from sq_user where username=param1;

　　　if(v_msg>0) then

 　　select birthday into v_content from sq_user where username=param1;

　　　dbms_output.put_line('find user's birthday'+v_content);

     elseif(v_msg==0)then

 　　dbms_output.put_line('no user find');

    else

　　raise exception_name(NO_DATA_FOUND);

   endif;

   Exception when others then

   rollback;

end;

--定义游标（简单的说是一个可以遍历的结果集）
cursor cur_1 is select * from .. where.. group by..

begin vs_ym_beg:=substr(is_ym,1,6);

 vs_ym_end:=substr(is_ym,7,6);       vs_ym_sn_beg:=to_char(add_months(to_date(vs_ym_beg,'yyyymm'),-12),'yyyymm');

vs_ym_sn_end:=to_char(add_months(to_date(vs_ym_end,'yyyymm'),-12),'yyyymm');

delete from sq_user where ym=is_ym;

dbms_output.put_line('del  ='|||SQL%rowcount||' 条');

insert into sq_user (username,sex,birthday）select username,sex,birthday from sq_user from  . . where ym>=vs_ym_beg and ym<=vs_ym_end

group by username;

dbms_output.put_line('ins 当月记录='||SQL%rowcount||'条');

for rec in cur_1 loop

update sq_user set username=rec.username,sex=rec.sex where ym=is_ym;

end loop;

commit;

exception when others then vs_msg:='ERROR IN XXXXX_P('||is_ym||'):'||substr(sqlerrm,1,500);

rollback

insert into log_info(proc_name,error_info,op_date) values('xxxxxx_p',vs_msg,sysdate);

commit;

return;

end;

 

oracle存储过程语法

1.判断语句：

if 比较式 then begin end;end if;

create or replace procedure test(x in number) is

begin

 if x>0 then begin x:=0-x; end;

end if;

if x=0 then begin x:=1;end;

end if;

end test;

2. for 循环

 for ...in ....loop

..执行语句

end loop;

(1)循环遍历游标

create or replace procedure test() as

Cursor cursor is select name from student;name varchar(20);

begin

for name in curosr LOOP

begin

dbms_output.put_line(name);

end;

end LOOP;

end test;

(2)循环遍历数组

create or replace procedure test(varArray in myPackage.TestArray) as

i number;

begin

i:=1;

for i in 1.count LOOP

dbms_output.put_line('The no'||i||'in varArray is'||varArray(i);

end LOOP;

end test;

3.while 循环

while 条件语句 loop

begin

end;

end loop;

e.g

create or replace procedure test(i in number) as begin

while i<10 LOOP

begin i:=i+1;

end;

end LOOP;

end test;

 

4.数组

(1)使用oracle自带的数组类型

 x array;--使用时需要进行初始化

e.g:

create or replace procedure  test(y out array) is

x array;

begin

x:=new array();

y:=x;

end test;

(2) 自定义的数组类型(自定义数组类型时,建议通过创建package的方式实现,以便于管理)

create or replace package myPackage is

public type declarations type info is record( name varchar(20), y number); type TestArray is table of info index by binary_integer;

此处使用了index by binary_integer编制该table的索引项,也可以不写使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray:=new myPackage.TestArray();

end TestArray;

5.游标的使用 oracle 的cursor是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性页很多，现仅就常用的方法做一二介绍:

(1)Cursor型游标(不能用于参数传递)

create or replace procedure test() is cursor_1 Cursor is

select std_name from student where ...;

 

实例

下面写一个简单的例子来对以上所说的存储过程的用法做一个应用：

现假设存在两张表，一张是学生成绩表(studnet) ，字段为：stdId,math,article,language,music,sport,total,average,step              

一张是学生课外成绩表(out_school), 字段为:stdId,parctice,comment

通过存储过程自动计算出每位学生的总成绩和平均成绩，同时，如果学生在课外课程中获得的评价为A ，就在总成绩上加20 分。

create table student(satid int not null primary key,math number,article number,language number,music number,sport number,,total number,average number,step number)

create table out_school(stdid int not null,practice number,comment char(1));

 

create or replace procedure autocomputer(step in number) is

rsCursor sys_refcursor;

commentArray myPackage.myArray;

math number;

article number;

language number;

music number;

sport number;

total number;

average number;

stdId varchar(30);

record myPackage.stdInfo;

i number;

begin

i:=1;

get_comment(commentArray);--调用名为get_comment()的存储过程获取学生课外评分信息

open rsCursor for select stdId,math,article,language,music,sport from student t where t.step=step;

loop

fetch rsCursor into staid,math,article,language,music,sport;exit when rsCursor%NOTFOUND;

total:=math+article+language+music+sport;

for i in 1..commentArray.count loop

record:=commentArray(i);

if stdId=record.stdId then

begin

if record.comment='A' then

begin total:=total+20;

go to next;--使用go to 跳出for 循环

end;

end if ;

end;

end if;

end loop;

<<continue>> average:=total/5;

update student t set t.total=total and t.average=average where t.stdId=stdId;

end loop;

end;

end autocomputer;

--取得学生评论信息的存储过程

create or replace procedure get_comment(commentArray out myPackage.myArray)

is

rs SYS_REFCURSOR;

record myPackage.stdInfo;

stdId varchar(30);

comment varchar(1);

i number;

begin

open rs for select stdId,comment from out_school

i:=1;

loop

fetch rs into stdId,comment;exit when rs%NOTFOUND;

record.stdId:=stdId;

record.comment:=comment;

recommentArray(i):=record;

i:=i+1;

end loop;

end get_comment;

--定义数组类型myArray

create or replace package myPackage is begin

type stdInfo is record(stdId varchar(30),comment varchar(1));

type myArray is table of stdInfo index by binary_integer;

end myPackage;