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）为查询打开游标

（3）将结果提取出来，存入PL/SQL变量中

（4）关闭游标

 

(1)CURSOR cursor_name  IS SELECT * FROM...

(2)OPEN cursor_name;

(3)FETCH cursor_name INTO var1,var2...;

     FETCH cursor_name INTO record_var;

(4)CLOSE cursor_name;

 

游标的属性：

%FOUND               前面FETCH返回一行数据，则为TRUE，未打开为false

%NOTFOUND        和上面的相反，未打开也为FALSE

%ISOPEN         

%ROWCOUNT        指针位移量

 

 

 

  1  DECLARE

  2    CURSOR c_emp IS

  3         SELECT * FROM s_emp;

  4    v_emp s_emp%ROWTYPE;

  5  BEGIN

  6    OPEN c_emp;

  7    FETCH c_emp INTO v_emp;

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

  9* END;

 

 

  1  DECLARE

  2    CURSOR c_emp IS

  3         SELECT * FROM s_emp;

  4    v_emp s_emp%ROWTYPE;

  5  BEGIN

  6    OPEN c_emp;

  7    LOOP

  8    FETCH c_emp INTO v_emp;

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

 10    END LOOP;

 11* END;

 

 

  1  DECLARE

  2    CURSOR c_emp IS

  3         SELECT * FROM s_emp;

  4    v_emp s_emp%ROWTYPE;

  5  BEGIN

  6    OPEN c_emp;

  7    LOOP

  8      FETCH c_emp INTO v_emp;

  9      EXIT WHEN c_emp%FOUND=false;         依赖于游标的属性

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

 11    END LOOP;

 12* END;

 

 

1  DECLARE

  2       CURSOR c_emp IS

  3             SELECT * FROM s_emp;

  4       v_emp s_emp%ROWTYPE;

  5  BEGIN

  6       OPEN c_emp;

  7       LOOP

  8             FETCH c_emp INTO v_emp;

  9             EXIT WHEN c_emp%NOTFOUND=true;               与c_emp%FOUND=false; 的结果是一样的

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

 11    END LOOP;

 12* END;

 

 

  1  DECLARE

  2    CURSOR r_em IS

  3        SELECT last_name

  4        FROM s_emp e,s_dept d

  5        WHERE e.dept_id=d.id

  6        AND d.region_id=4;

  7    v_name s_emp.last_name%TYPE;

  8  BEGIN

  9       OPEN r_em;

 10      LOOP

 11          FETCH r_em INTO v_name;

 12          EXIT WHEN r_em%NOTFOUND;

 13          DBMS_OUTPUT.PUT_LINE(v_name);

 14    END LOOP;

 15    CLOSE r_em;

 16* END;

 

 

  1  DECLARE

  2       CURSOR r_em IS

  3           SELECT e.id,e.last_name

  4           FROM s_emp e,s_dept d

  5           WHERE e.dept_id=d.id

  6          AND d.region_id=4;

  7      v_name s_emp.last_name%TYPE;

  8      v_id s_emp.id%TYPE;

  9  BEGIN

 10    OPEN r_em;

 11          FETCH r_em INTO v_id,v_name;                 两次FETCH

 12           WHILE r_em%FOUND  LOOP

 13             DBMS_OUTPUT.PUT_LINE(v_id||':'||v_name);   先打印出来 ，再FETCH一次

 14             FETCH r_em INTO v_id,v_name;

 15          END LOOP;

 16    CLOSE r_em;

 17* END;

 

FOR 循环

  1  DECLARE

  2    CURSOR r_em IS

  3        SELECT e.id,e.last_name

  4        FROM s_emp e,s_dept d

  5        WHERE e.dept_id=d.id

  6        AND d.region_id=4;

  7  BEGIN

  8       FOR v_emp IN r_em LOOP

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

 10      END LOOP;

 11* END;

 

 

 1  DECLARE                 不用定义语句

  2  BEGIN

  3        FOR v_emp IN (                        用SELECT语句直接跟在FOR的IN语句后当作游标

  4            SELECT e.id,e.last_name

  5            FROM s_emp e,s_dept d

  6            WHERE e.dept_id=d.id

  7            AND d.region_id=4                   这里不用分号

  8         )LOOP

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

 10     END LOOP;

 11* END;

 

 

DECLARE

    CURSOR c_emp IS

       SELECT * FROM s_emp

       WHERE dept_id=32

       FOR UPDATE OF salary;                不加上OF的话，整个表都会加锁。有OF的话，只为这一列加锁

BEGIN

    FOR v_emp IN c_emp LOOP

         UPDATE s_emp set salary=salary*1.1

         WHERE id=v_emp.id;

    END LOOP;

END;

 

 

EXCEPTION

 

1  DECLARE

  2    my_exception EXCEPTION;

  3    v_emp s_emp%ROWTYPE;

  4    v_sal s_emp.salary%TYPE;

  5  BEGIN

  6    SELECT salary INTO v_sal FROM s_emp

  7    WHERE id=20;

  8    IF(v_sal<1000) THEN

  9         RAISE my_exception;

 10    END IF;

 11    DBMS_OUTPUT.PUT_LINE('END');

 12  EXCEPTION

 13    WHEN my_exception THEN

 14      UPDATE s_emp set salary=salary+500 where id=20;

 15* END;

 

 

 

 

SUB-PROGRAM

 

 

(1) PROCEDURE过程

CREATE[OR REPLACE]  PRODUCE    proc_name

                                   [(arg_name[{IN | OUT| IN OUT}]TYPE,

                                     arg_name[{IN | OUT| IN OUT}]TYPE]

         { IS | AS }

procedure_body

 

(2)FUNCTION函数

CREATE [OR REPLACE] FUNCTION    func_name

                              [(arg_name[{  IN |  OUT  |  IN OUT }]TYPE,

                               (arg_name[{  IN |  OUT  |   IN OUT}]TYPE)]

RETURN TYPE

            {IS|AS}

Func_body

 

 

 

PACKAGE 包

包头规范

CREATE [OR REPLACE] PACKAGE pack_name

{ IS | AS }

          procedure_specification|;

          function_specification|

          variable_declaration|

          type_definition|

          exception_declaration|

          cursor_declaration

END pack_name;

 

 

PACKAGE BODY 包主体

CREATE OR REPLACE PACKAGE BODY pac_name

{ IS | AS }

....

BEGIN

.....initialization code

END pac_name;

 

 

TRIGGER触发器，必须存在数据库中

都是带有名字的执行块

都有声明，执行体和异常处理部分

  1  CREATE OR REPLACE TRIGGER zh_trigger

  2   AFTER INSERT OR UPDATE OR DELETE ON s_emp

  3  DECLARE

  4       v_cnt NUMBER;

  5  BEGIN

  6      SELECT COUNT(*) INTO v_cnt FROM s_emp;

  7      DBMS_OUTPUT.PUT_LINE('something changed!');

  8* END;

  9  /

 

Trigger created.