作者:peace.zhao 關于 游標 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); elsif temp.course_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 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'); 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=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('qita'); end case ; end loop; end; ---關于for 循環的例子 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 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 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;
