ORACLEPLSQL基础概念.ppt

* * * * * * 示例一 declare v_num1 px_1.v_num%type; begin v_num1 := no ; case v_num when 1 then update px_2 set v_char=a where v_num = v_num1; when 2 then update px_2 set v_char=b where v_num = v_num1; else dbms_output.put_line(ERROR) ; end case ; end ; 示例二 declare vn_num px_1.v_num%type; vn_char px_1.v_char%type; begin select v_num,v_char into vn_num,vn_char from px_1 where v_num = no ; case when vn_num 3 then update px_2 set v_char=a where v_num = vn_num; when vn_num 6 then update px_2 set v_char=b where v_num = vn_num; else dbms_output.put_line(ERROR) ; end case ; end ; * 基本循环 declare vn_num int := 0; begin loop insert into px_3(v_num) values(vn_num); exit when vn_num =10; vn_num := vn_num+1 ; end loop ; end ; WHILE 循环 declare vn_num int := 0; begin while vn_num = 10 loop insert into px_3(v_num) values(vn_num); vn_num := vn_num+1 ; end loop ; end ; * FOR循环 declare begin for i in 1..10 loop insert into px_3(v_num) values(i); end loop ; end ; 嵌套循环和标号 declare result int ; begin outer for i in 1..100 loop inter for j in 1..100 loop result := i*j; exit outer when result=1000; exit when result=500 ; end loop inter ; dbms_output.put_line(result); end loop outer; dbms_output.put_line(result); end ; * * * * -- 示例一 DECLARE VC_CHAR PX_1.V_CHAR%TYPE; VN_NUM PX_1.V_NUM%TYPE; CURSOR c_emp IS SELECT V_CHAR, V_NUM FROM PX_1; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO VC_CHAR, VN_NUM; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(V_CHAR = || VC_CHAR || AND VN_NUM = ||VN_NUM); END LOOP ; close c_emp; END; --示例二 declare type person is record( empno PX_1.V_NUM%TYPE, empzc PX_1.V_CHAR%TYPE ); person1 person; cursor mycur(vartype number) is select V_NUM, V_CHAR from PX_1 where V_NUM = vartype; begin if mycur%isopen = false then open mycur(1); end if; loop fetch m