PLSql語句學習(一)

PL/SQL(PROCEDURAL LANGUAGE/SQL) 是標準的SQL的基礎上增加了過程化處理的語言

ORACLE 用戶端工具訪問ORACLE伺服器的操作語言，對SQL的擴充

 

還有其他的用戶端編程軟體，例如pro*c/c++，ODBC,OCI,JDBC,SQLJ

程式結構：

申明部分：declare

執行部分：begin

異常處理：exception

對大小寫不敏感

注釋：-- 或者 /*  */

 

 

DECLARE

   v_firstName  varchar2(32);

   v_firstName studengs.first_name%TYPE;

   v_TempVar Number(7,3) NOT NULL :=12.3;

   v_StuRec student%ROWTYPE;

 

   TYPE record_name IS RECORD(

         field1   type1 [NOT NULL] [:=EXPR1],

         field2   type2 [NOT NULL] [:=EXPR1]);

         限定NOT NULL，那麼它必須擁有一個初始值。

   TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;

 

TABLE類型的例子

  DECLARE

        TYPE t_StuTable IS TABLE OF Student%ROWTYPE

               INDEX BY BINARY_INTEGER;

         v_Student  t_StuTable;

BEGIN

        SELECT * INTO  v_Student（1001）

        FROM Student

        WHERE id = 1001;

END;

 

變數的範圍與可見度和PL/SQL控制語句（不作介紹，只顯樣本子）

 

 DECLARE

     v_str VARCHAR2(20);

     v_num NUMBER:=199.01;

     v_int BINARY_INTEGER:=220;

  BEGIN

     v_str:='well done leo!';

     DBMS_OUTPUT.PUT_LINE(v_str);

     DBMS_OUTPUT.PUT_LINE(v_int); 

  END；

 

 

  1  DECLARE

  2     v_str VARCHAR2(20);

  3     v_num NUMBER:=199.01;

  4     v_int BINARY_INTEGER:=220;

  5     v_bool BOOLEAN;

  6  BEGIN

  7     v_str:='well done leo!';

  8     DBMS_OUTPUT.PUT_LINE(v_str);

  9     DBMS_OUTPUT.PUT_LINE(v_int);

 10     v_bool:=false;

 11     IF(v_bool) THEN

 12      DBMS_OUTPUT.PUT_LINE('FALSE');

 13     END IF;

 14* END;

 

 

DECLARE

    TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER;

     v_emp t_emp;

 BEGIN

    SELECT * INTO v_emp(100) FROM s_emp WHERE ID=12;

    SELECT * INTO v_emp(200) FROM s_emp WHERE ID=15;

    DBMS_OUTPUT.PUT_LINE(v_emp(100).id||':'||v_emp(100).last_name);

    DBMS_OUTPUT.PUT_LINE(v_emp(200).id||':'||v_emp(200).last_name);

END;

 

 

  1  DECLARE

  2       TYPE t_emp_r IS RECORD(

  3              v_id s_emp.id%TYPE,

  4              v_last_name s_emp.last_name%TYPE);

  5       TYPE t_emp_t IS TABLE OF t_emp_r INDEX BY BINARY_INTEGER;

  6        v_emp_t t_emp_t;

  7  BEGIN

  8       FOR cnt in  1..25 LOOP

  9             SELECT id,last_name

 10            INTO v_emp_t(cnt).v_id,v_emp_t(cnt).v_last_name

 11            FROM  s_emp

 12           WHERE id=cnt;

 13       END LOOP;

 14       FOR cnt IN 1..25 LOOP

 15             DBMS_OUTPUT.PUT_LINE(v_emp_t(cnt).v_id||':'||v_emp_t(cnt).v_last_name);

 16       END LOOP;

 17       DBMS_OUTPUT.PUT_LINE('bye!');

 18* END;

 

 

 

DECLARE

    v1 NUMBER;

    v2 VARCHAR2(10);

 BEGIN

      v1:=100;

      v2:='hello';

      DECLARE

          v3 NUMBER;

          v2 NUMBER;

      BEGIN

          v3:=300;

          v2:=200;

      END;

    DBMS_OUTPUT.PUT_LINE(v1);

    DBMS_OUTPUT.PUT_LINE(v2);

 END;

結果

100

hello

 

 

 

  1  <<outer>>

  2  DECLARE

  3      v1 NUMBER;

  4      v2 VARCHAR2(10);

  5  BEGIN

  6        v1:=100;

  7        v2:='hello';

  8        DECLARE

  9            v3 NUMBER;

 10            v2 NUMBER;

 11        BEGIN

 12            v3:=300;

 13            v2:=200;

 14            DBMS_OUTPUT.PUT_LINE(outer.v2);

 15        END;

 16      DBMS_OUTPUT.PUT_LINE(v1);

 17      DBMS_OUTPUT.PUT_LINE(v2);

 18* END;

 19  /

結果：hello

100

hello

 

 

  1  DECLARE

  2      bool BOOLEAN;

  3      v_int BINARY_INTEGER;

  4  BEGIN

  5      bool := null;

  6      IF(bool)THEN

  7          DBMS_OUTPUT.PUT_LINE('WELL DONE');

  8      END IF;

  9     v_int:=3;

 10     IF(v_int=1) then

 11          DBMS_OUTPUT.PUT_LINE('1');

 12     ELSIF(v_int=2) then

 13          DBMS_OUTPUT.PUT_LINE('2');

 14     ELSIF(v_int=3) then

 15          DBMS_OUTPUT.PUT_LINE('3');

 16     ELSE

 17          DBMS_OUTPUT.PUT_LINE('5');

 18     END IF;

 19* END;

 

 

 

  1  DECLARE

  2      v_id s_emp.id%TYPE;

  3      v_last_name s_emp.last_name%TYPE;

  4      v_count NUMBER:=1;

  5  BEGIN

  6      LOOP

  7        IF(v_count>25) THEN          通常loop之前一定要加上 IF語句來控制

  8            EXIT;

  9        END IF;

 10        SELECT id,last_name

 11        INTO v_id,v_last_name

 12        FROM s_emp

 13        WHERE id=v_count;

 14        DBMS_OUTPUT.PUT_LINE(v_id||':'||v_last_name);

 15        v_count :=v_count +1;

 16     END LOOP;

 17        DBMS_OUTPUT.PUT_LINE('END');

 18* END;

 

 

  1  DECLARE

  2       v_id s_emp.id%TYPE;

  3       v_last_name s_emp.last_name%TYPE;

  4       v_salary s_emp.salary%TYPE;

  5       v_cnt NUMBER:=1;

  6       v_grade VARCHAR2(5);

  7  BEGIN

  8      LOOP

  9       SELECT id,last_name,salary

 10       INTO v_id,v_last_name,v_salary

 11       FROM s_emp

 12       where id=v_cnt;

 13       IF (v_salary >=2000) THEN  v_grade:='a';

 14       ELSIF (v_salary >=1500) THEN  v_grade:='b';

 15       ELSIF (v_salary >=1000) THEN  v_grade:='c';

 16       ELSE v_grade:='d';

 17       END IF;

 18       DBMS_OUTPUT.PUT_LINE(v_last_name||':'||v_grade);

 19        v_cnt:=v_cnt+1;

 20        EXIT WHEN v_cnt>25;                      當v_cnt大於25時就退出迴圈

 21      END LOOP;

 22* END;

 

 

  1  DECLARE

  2       v_id s_emp.id%TYPE;

  3       v_last_name s_emp.last_name%TYPE;

  4       v_salary s_emp.salary%TYPE;

  5       v_cnt NUMBER:=1;

  6       v_grade VARCHAR2(5);

  7  BEGIN

  8     WHILE(v_cnt<=25) LOOP                     每一次迴圈 都要執行一次  WHILE裡面的條件判斷句

  9       SELECT id,last_name,salary

 10       INTO v_id,v_last_name,v_salary

 11       FROM s_emp

 12       where id=v_cnt;

 13       IF (v_salary >=2000) THEN  v_grade:='a';

 14       ELSIF (v_salary >=1500) THEN  v_grade:='b';

 15       ELSIF (v_salary >=1000) THEN  v_grade:='c';

 16       ELSE v_grade:='d';

 17       END IF;

 18       DBMS_OUTPUT.PUT_LINE(v_last_name||':'||v_grade);

 19       v_cnt:=v_cnt+1;

 20      END LOOP;

 21* END;

 

 

  1  DECLARE

  2      v_id s_emp.id%TYPE;

  3      v_last_name s_emp.last_name%TYPE;

  4  BEGIN

  5     FOR cnt IN REVERSE 1..5 LOOP         加了REVERSE ，CNT由5開始減1

  6     SELECT id,last_name

  7     INTO v_id,v_last_name

  8     FROM s_emp

  9     WHERE id=cnt;

 10     DBMS_OUTPUT.PUT_LINE(v_id||':'||v_last_name);

 11     END LOOP;

 12* END;

 

 

CURSOR遊標

遊標用於提取多行資料集

遊標的使用：

（1）聲明遊標

（2）為查詢開啟遊標