oracle存储过程和函数例⼦关于游标if,for的例⼦
create or replace procedure peace_if
is
cursor var_c is select*from grade;
begin
for temp in var_c loop
urse_name ='OS'then
dbms_output.put_line('Stu_name = '||temp.stu_name);
urse_name ='DB'then
dbms_output.put_line('DB');
else
dbms_output.put_line('feng la feng la ');
end if;
end loop;
end;
---关于游标 for,case 的例⼦1
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
when1then
dbms_output.put_line('haha1');
when2then
dbms_output.put_line('haha2');
when3then
dbms_output.put_line('haha3');
when4then
dbms_output.put_line('haha4');
else
dbms_output.put_line('qita');
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=1then
dbms_output.put_line('haha1');
when temp.vol=2then
dbms_output.put_line('haha2');
when temp.vol=3then
dbms_output.put_line('haha3');
when temp.vol=4then
dbms_output.put_line('haha4');
else
dbms_output.put_line('qita');
end case ;
end loop;
end;
-
--关于for 循环的例⼦
create or replace procedure peace_for
is
sum1 number :=0;
temp varchar2(500);
begin
for i in1..9 loop
temp :='';
for j in1 .. 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 peace_loop
is
sum1 number :=0;
temp number :=0 ;
begin
loop
exit when temp>=10 ;
sum1 := sum1+temp;
temp :=temp+1;
end loop;
dbms_output.put_line(sum1 );
end;
---关于游标和loop循环的例⼦
create or replace procedure loop_cur
is
oracle游标的使用stu_name varchar2(100);
course_name varchar2(100);
cursor var_cur is select*from grade ;
begin
open var_cur;
loop
fetch var_cur into stu_name,course_name;
exit when var_cur%notfound;
dbms_output.put_line(stu_name|| course_name);
end loop;
close var_cur;
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 course (course_name) values (c_n);
elsif c_n ='NG'then
insert into course (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('ERRO');
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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。