oracle 存储过程= 1,oracle_存储过程例子_1

--关于游标 if,for 的例子

create or replace procedure peace_if

is

cursor var_c is select * from grade;

begin

for temp in var_c loop

if temp.course_name=‘OS‘ then

dbms_output.put_line(‘Stu_name=‘||temp.stu_name);

else if temp.course_name=‘DB‘ then

dbms_output.put_line(‘DB‘);

else

dbms_output.put_line(‘fengla fengla‘);

end if;

end loop;

end;

--关于游标，for,case得例子

create or replace procedure peace_case1

is

cursor var_c is select * from test_case;

begin

for temp in var_c loop

case temp.vol

when 1 then

dbms_output.put_line(‘haha1‘);

when 2 then

dbms_output.put_line(‘haha2‘);

when 3 then

dbms_output.put_line(‘haha3‘);

when 4 then

dbms_output.put_line(‘haha4‘);

when 5 then

dbms_output.put_line(‘haha5‘);

else

dbms_output.put_line(‘haha‘);

end case;

end loop;

end;

--关于游标 for,case,的例子2

create or replace procedure peace_case2

is

cursor var_c is select * from test_case;

begin

for temp in var_c loop

case

when temp.vol=1 then

dbms_output.put_line(‘haha1‘);

when temp.vol=2 then

dbms_output.put_line(‘haha2‘);

when temp.vol=3 then

dbms_output.put_line(‘haha3‘);

when temp.vol=4 then

dbms_output.put_line(‘haha4‘);

else

dbms_output.put_line(‘haha5‘);

end case;

end loop;

end;

--关于for 循环的例子

--to_char();实现其他数据类型向字符类型转换

create or replace procedure peace_for

is

sum1 number :=0;

temp varchar2(500);

begin

for i in 1..9 loop

temp :=‘‘;

for j in 1..i loop

sum1 :=i*j;

temp:=temp||‘ ‘||to_char(i)||‘*‘||to_char(j)||‘=‘||to_char(sum1)||‘‘;

end loop;

dbms_output.put_line(temp);

end loop;

end;

--关于loop循环

create or replace procedure loop_cur

is

stu_name varchar2(100);

course_name varchar2(100);

cursor var_c is select * from grade;

begin

open var_c;

loop

fetch var_c into stu_name,course_name;

exit when var_c%notfound;

dbms_output.put_line(stu_name||‘ ‘|| course_name);

end loop;

close var_c;

end;

--关于异常处理的例子

create or replace procedure peace_exp(in1 in varchar2)

is

c_n varchar2(100);

begin

select course_name into c_n from grade where stu_name=in1;

dbms_output.put_line(c_n);

exception

when no_data_found

then

dbms_output.put_line(‘try‘);

when TOO_MANY_ROWS

then

dbms_output.put_line(‘more‘);

end;

--异常处理例子2

create or replace procedure peace_insert(c_n in varchar2)

is

error EXCEPTION;

begin

if c_n =‘OK‘ then

insert into grade(course_name) values(c_n);

elsif c_n=‘NG‘ then

insert into grade(course_name) values(c_n);

raise error;

else

dbms_output.put_line(‘c_n‘||‘ ‘||c_n);

end if;

commit;

exception

when error then

rollback;

dbms_output.put_line(‘error‘);

end;

--定义包头

create or replace package peace_pkg

as

function test1(in1 in varchar2)

return number;

procedure test2(in2 in varchar2);

end peace_pkg;

--定义包体

create or replace package body peace_pkg

as

function test1(in1 in varchar2)

return number

as

temp number;

begin

temp:=0;

return temp;

end;

procedure test2(in2 in varchar2)

is

begin

dbms_output.put_line(in2);

end;

end peace_pkg;

oracle_存储过程例子_1

标签：ror   cti   from   roc   back   char   turn   ret   proc

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://www.cnblogs.com/sujingnuli/p/6549472.html