【1】存储过程的格式
create or replace procedure procedure_test
is
..........
begin
.........
end procedure_test;
大概的格式就是这样,当然在存储过程名后面也可以添加参数。is(也可以用as)后面是定义需要使用的参数,begin是方法主题即功能代码
【2】存储过程简单示例
1、不带任何参数,仅仅打印系统时间
create or replace procedure procedure_test1
is
begin
dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD'));
end procedure_test1;
2、带有传入参数
create or replace procedure procedure_test2(v_id in number,v_name in varchar2,v_age in number,v_address in varchar2)
is
begin
insert into user_test values(v_id,v_name,v_age,v_address);
end procedure_test2;
存储过程名后可以传入参数,使用关键字in,当然也可以不写,如果需要往外输入一个参数,将in换成out
create or replace procedure procedure_test3(v_name out varchar2,v_age out number,v_address out varchar2,v_id number)
as
begin
select name,age,address into v_name,v_age,v_address from user_test where id = v_id;
dbms_output.put_line(v_name);
end procedure_test3;
3、定义变量
create or replace procedure procedure_test4(num1 in out number,num2 in out number)
is
v1 number;
v2 number;
begin
v1:=num1/num2;
v2:=num1+num2;
num1:=v1;
num2:=v2;
end procedure_test4;
存储过程中,is后面定义变量的地方,begin后面才是数据处理的地方。给变量赋值:变量名:= 值
4、使用for循环
create or replace procedure procedure_test5(num1 in number,sum1 out number)
is
tempsum number(16);
begin
tempsum:=0;
for tempval in 0..num1 loop
begin
tempsum:=tempsum+tempval;
end;
end loop;
sum1:=tempsum;
end procedure_test5;
5、使用oracle自带的异常
create or replace procedure procedure_test6(Svalue out varchar2)
is
tempvalue varchar2(128);
begin
select id||name||age||address into tempvalue from user_test where id = 0;
Svalue:=tempvalue;
exception
when no_data_found then
dbms_output.put_line('no person');
when too_many_rows then
dbms_output.put_line('the news is too more');
when others then
dbms_output.put_line('error');
end procedure_test6;
上面||在存储过程中表示连接符,把几个数据连接在一起。
同时对于异常,除了可以使用oracle自带的异常,我们也可以自己定义异常处理。
errormessage exception;--这是定义异常处理
raise errormessage;--这是抛出异常方法
exception
when errormessage then
dbms_output.put_line('there is a exception');
end;
6、存储过程中使用游标
create or replace procedure grandateprocess(
tempzhengzhi in graduate.zhengzhi%type,
tempyingyu in graduate.yingyu%type,
tempzhuanye1 in graduate.zhuanye1%type,
tempzhuanye2 in graduate.zhuanye2%type,
tempzhuanye3 in graduate.zhuanye3%type,
temptotalscore in result.totalscore%type
)
is
graduaterecord graduate%rowtype;
graduatetotalscore result.totalscore%type;
graduateflag varchar2(20);
--定义游标
cursor graduatecursor is select * from graduate;
--定义异常处理
errormessage exception;
open graduatecursor;
if graduatecursor%not found then
--如果没有数据,就抛出异常
raise errormessage;
end if;
if graduatecursor is not null then
--如果有数据,将数据循环出来
loop
fetch graduatecursor into graduaterecord;
exit when graduatecursor%notfound;
--计算总分
graduatetotalscore:=graduaterecord.zhengzhi+graduaterecord.yingyu+graduaterecord.zhuanye1+graduaterecord.zhuanye2+graduaterecord.zhuanye3;
--比较判断条件,看是否将数据插入到result表中
if(graduaterecord.yingyu>=tempyingyu and
graduaterecord.zhengzhi>=tempzhengzhi and
graduaterecord.zhuanye1>=tempzhuanye1 and
graduaterecord.zhuanye2>=tempzhuanye2 and
graduaterecord.zhuanye3>=tempzhuanye3 and
graduaterecord.totalscore>=temptotalscore
)
then
graduateflag:='录取';
else
graduateflag:='落选';
end if;
insert into result(BH,xm,lb,yingyu,zhengzhi,zhuanye1,zhuanye2,zhuanye3,totalscore,flag)
values(graduaterecord.BH,graduaterecord.XM,graduaterecord.LB,graduaterecord.YINGYU,graduaterecord.ZHENGZHI,
graduaterecord.ZHUANYE1,graduaterecord.ZHUANYE2,graduaterecord.ZHUANYE3,graduatetotalscore,graduateflag);
end loop;
end if;
close graduatecursor;--关闭游标
commit;--提交
--异常处理
exception
when errormessage then
dbms_output.put_line('无法打开数据表');
end;
end graduateprocess; --结束存储过程