PL/SQL 基础

PL/SQL 基础

PL/SQL代码编写规则.

1、标识符命名规则（前缀，后缀）

* 变量，v_         例：v_count, v_number

* 常量，c_         例：c_rate

* 异常，e_         例：e_integrity_error

* 游标，_cursor    例：emp_cursor

* pl/sql表类型，  _table_type       例：emp_table_type

* pl/slq表变量，  _table            例：emp_table

* pl/sql记录类型，_record_type      例：emp_record_type

* pl/sql记录变量，_record           例：emp_record

2、大小写规则.

* SQL关键字大写，          例：SELECT , FROM , WHERE

* PL/SQL关键字大写，       例：DECLARE, BEGIN, EXCEPTION ,END

* 数据类型大写，           例：INT, VARCHAR2， DATE

* 标识符、参数小写         例：v_sal, c_rate

* 数据库对象、列小写       例：emp、sal、ename

变量和类型. 数字数据类型，除 NUMBER之外，还有：

PLS_INTEGER，是专门为PL/SQL程序使用的数据类型，他不可以在创建表的列中使用。

PLS_INTEGER 数据类型 表示一个有符号整数， 表示的范围为-2的31次方  到 2的31次方。

PLS_INTEGER 具有比 NUMBER变量更小的表示范围，因此会占用更少的内存。

PLS_INTEGER能更有效地利用CPU，因此其运算可以比 number 和binary_intege 更快。

.

BINARY_INTEGER ，与 PLS_INTEGER 相似，比 NUMBER 变量需要的内存少。

二者的区别是：PLS_INTEGER进行的运算发生溢出时，常会触发异常； 但是当 BINARY_INTEGER运算发生溢出时，如果可以指派给一个 NUMBER 变量（没有超出 NUMBER 的精度方位）则不会触发异常。

.

PL/SQL的注释：

单行：“—”.

多行：

.

条件分支语句：

1、 IF-THEN、 IF-THEN-ELSE、IF-THEN-ELSFI.

2、 CASE 语句

        当处理多重条件分支时， CASE 语句 更加简捷，而且执行效率也更好，所以建议使用 CASE 语句：

         CASE

         WHEN condition1 THEN statement1

         WHEN condition2 THEN statement2

         …

         WHEN conditionN-1 THEN statementN-1

         [ELSE statementsN]

         END CASE;

sys@SDROLUN>DECLARE
 2    grade      CHAR(1)     := 'B';
 3    appraisal   VARCHAR2 (20);
  4  BEGIN
 5    appraisal:=                                               --输出结果赋值
 6       CASE grade
 7          WHEN 'A'
 8             THEN '优'
 9          WHEN 'B'
10             THEN '良'
11          WHEN 'C'
12             THEN '中'
13          WHEN 'D'
14             THEN '及格'
15          WHEN 'F'
16             THEN '不及格'
17          ELSE '没有这样的等级'
18       END;
19    DBMS_OUTPUT.put_line ('Grade' || grade || 'is' || appraisal);
20  END;
21  /
GradeBis良

PL/SQL 过程已成功完成。

NULL结构

PL/SQL 中一类特殊的结构，又称为 空操作 或空值结构。NULL结构表示什么都不做，起到占位符的作用。

sys@SDROLUN>DECLARE
 2    n   INTEGER := 0.5;
  3  BEGIN
 4    IF n > 1
 5    THEN
 6       NULL;
 7    ELSE
 8       DBMS_OUTPUT.put_line ('n is smaller than 1----------------');
 9    END IF;
10  END;
11  /
n is smaller than 1----------------

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01
sys@SDROLUN>DECLARE
 2    n   INTEGER := 2;
  3  BEGIN
 4    IF n > 1
 5    THEN
 6       NULL;
 7    ELSE
 8       DBMS_OUTPUT.put_line ('n is smaller than 1');
 9    END IF;
10  END;
11  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.00

另外，在PL/SQL中含有NULL的条件表达式，其运算结果总是NULL。

     对 NULL 施加逻辑运算符 NOT，其结果也总是 NULL。

     如果在 选择结构中条件表达式的值是 NULL，则相应的 THEN 语句不会被执行

     在 CASE 结构中，不能出现 WHEN NULL 这样的结构，而应该使用 IS NULL 子句

如果两个变量的值都为NULL，它们也是不相等的.

.

%TYPE变量

%ROWTYPE变量

PL/SQL记录：

sys@SDROLUN>   DECLARE
 2               TYPE emp_record_type IS RECORD (
 3                  NAME    emp.ename%TYPE,
 4                  salary   emp.sal%TYPE,
 5                  deptno   emp.deptno%TYPE
 6               );
 7               emp_record  emp_record_type;
 8            BEGIN
 9               SELECT ename,
10                      sal,
11                      deptno
12                 INTO emp_record
13                 FROM emp
14                WHERE empno = 7788;
15
16               DBMS_OUTPUT.put_line ('emp_record.name=' || emp_record.NAME);
17            END;
18  /
emp_record.name=SCOTT

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.00

 

sys@SDROLUN>   DECLARE
 2               TYPE emp_record_type IS RECORD (
 3                  NAME    emp.ename%TYPE,
 4                  salary   emp.sal%TYPE,
 5                  deptno   emp.deptno%TYPE
 6               );
  7
 8               emp_record  emp_record_type;
 9            BEGIN
10               SELECT ename, sal
11                 INTO emp_record.name, emp_record.salary
12                 FROM emp
13                WHERE empno = 7788;
14
15               DBMS_OUTPUT.put_line ('emp_record.name=' || emp_record.NAME);
16            END;
17  /
emp_record.name=SCOTT

PL/SQL 过程已成功完成。

.

PL/SQL集合

1、索引表.

      也称 PL/SQL表，用户处理 PL/SQL 数组的数据类型。

      索引表的高级之处在于，其元素个数没有限制，并且下标可以为 负值。

TYPE type_name IS TABLE OF element_type

[NOT NULL] INDEX BY key_type;

identifier type_name

1.1、索引表中使用 binary_integer 和pls_integer

DECLARE
   TYPE ename_table_type IS TABLEOF emp.ename%TYPE
     INDEX BY BINARY_INTEGER;

  ename_table  ename_table_type;
  c_empno      PLS_INTEGER     := 7788;
  c_empno2     PLS_INTEGER     := 7369;
BEGIN
   SELECT ename
    INTO ename_table (-1)
    FROM emp
    WHERE empno= c_empno;
   DBMS_OUTPUT.put_line(   '雇员号为'
                        || c_empno
                        || '的雇员名为：'
                        || ename_table (-1)
                       );
   SELECT ename
    INTO ename_table (2)
    FROM emp
    WHERE empno= c_empno2;
   DBMS_OUTPUT.put_line(   '雇员号为'
                        || c_empno2
                        || '的雇员名为：'
                        || ename_table (2)
                       );
END;

雇员号为7788的雇员名为：SCOTT
雇员号为7369的雇员名为：SMITH

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01

1.2、索引表中使用 varchar2

sys@SDROLUN>   DECLARE
 2               type area_table_type is table of number
 3               index by varchar2(10);
 4               area_table area_table_type;
 5            BEGIN
 6               area_table('北京'):=1;
 7               area_table('上海'):=2;
 8               area_table('广州'):=3;
 9               dbms_output.put_line('第一个元素：'||area_table.first);
10               dbms_output.put_line('最后一个元素：'||area_table.last);
11               dbms_output.put_line('下一个元素：'||area_table.next('北京'));
12            END;
13  /
第一个元素：北京
最后一个元素：上海
下一个元素：广州

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01

2、嵌套表.   ——下标从 0 开始，并且元素个数 无限制。可以 无序。

TYPE type_name IS TABLE OF element_type;

identifier type_name;

sys@SDROLUN>   DECLARE
 2               TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
 4               ename_table  ename_table_type;
 5            BEGIN
 6               ename_table := ename_table_type ('MARY', 'LILI', 'TOM');--使用构造方法初始化嵌套表变量
 9               SELECT ename
10                 INTO ename_table (2)
11                 FROM emp
12                WHERE empno = 7788;
13
14               DBMS_OUTPUT.put_line ('雇员名：' || ename_table (2));
15            END;
16  /
雇员名：SCOTT

PL/SQL 过程已成功完成。

3、变长数组.

sys@>DECLARE
 2    TYPE ename_table_type IS VARRAY (20) OF emp.ename%TYPE;
  3
 4    ename_table  ename_table_type;
  5  BEGIN
 6    ename_table := ename_table_type ('MARY', 'LILI', 'KAR');
  7
 8    SELECT ename
 9      INTO ename_table (2)
10      FROM emp
11     WHERE empno = 7788;
12
13    DBMS_OUTPUT.put_line ('雇员名：' || ename_table (2));
14  END;
15  /
雇员名：SCOTT

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01

4、PL/SQL记录表.

sys@>DECLARE
 2    TYPE emp_table_type IS TABLE OF emp%ROWTYPE
 3       INDEX BYBINARY_INTEGER;                              --定义索引表类型
  4
 5    emp_table   emp_table_type;
  6  BEGIN
 7    SELECT *
 8      INTO emp_table (1)
 9      FROM emp
10     WHERE empno = 7788;
11
12    DBMS_OUTPUT.put_line ('雇员名：' || emp_table (1).ename);
13    DBMS_OUTPUT.put_line ('雇员工资：' || emp_table (1).sal);
14  END;
15  /
雇员名：SCOTT
雇员工资：3000

PL/SQL 过程已成功完成。.

5、集合方法.

函数：Exists()、count(), limit(),first(), next(), prior(), last()

过程：extend(), trim(),delete()

1. Exists()

      --用户确定集合元素是否存在，若存在，返回 TRUE； 不存在， 返回 FALSE。

sys@>DECLARE
 2    TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
  3
 4    ename_table  ename_table_type;
  5  BEGIN
 6    IF ename_table.EXISTS (1)
 7    THEN                                      --判断ename_table中是否存在元素
 8       ename_table (1) := 'SCOTT';
 9    ELSE
10       DBMS_OUTPUT.put_line ('必须初始化集合元素');
11    END IF;
12  END;
13  /
必须初始化集合元素

PL/SQL 过程已成功完成。.

2.COUNT() 或 COUNT

sys@SDROLUN>DECLARE
 2    TYPE ename_table_type IS TABLE OF emp.ename%TYPE index bybinary_integer;
 3    ename_table  ename_table_type;
  4  BEGIN
 5    IF ename_table.EXISTS (1)
 6    THEN                                      --判断ename_table中是否存在元素
 7       ename_table (1) := 'SCOTT';
 8    ELSE
 9       DBMS_OUTPUT.put_line ('必须初始化集合元素');
10    END IF;
11
12    ename_table(-5) := 'SCOTT';
13    ename_table(1) := 'SMITH';
14    ename_table(5) := 'MARY';
15    ename_table(10):='BLACK';
16          DBMS_OUTPUT.put_line('该集合元素总个数为：'||ename_table.count());
17  END;
18  /
必须初始化集合元素
该集合元素总个数为：4

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01
sys@SDROLUN>DECLARE
 2    TYPE ename_table_type IS TABLE OF emp.ename%TYPE index bybinary_integer;
 3    ename_table  ename_table_type;
  4  BEGIN
 5    IF ename_table.EXISTS (1)
 6    THEN                                      --判断ename_table中是否存在元素
 7       ename_table (1) := 'SCOTT';
 8    ELSE
 9       DBMS_OUTPUT.put_line ('必须初始化集合元素');
10    END IF;
11
12    ename_table(-5) := 'SCOTT';
13    ename_table(1) := 'SMITH';
14    ename_table(5) := 'MARY';
15    ename_table(10):='BLACK';
16          DBMS_OUTPUT.put_line ('该集合元素总个数为：'||ename_table.count);
17  END;
18  /
必须初始化集合元素
该集合元素总个数为：4

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01

3. LIMIT()、LIMIT          --返回集合元素的最大个数。

sys@>declare
  2   typeename_table_type is varray(25) of emp.ename%TYPE;
  3  ename_table ename_table_type := ename_table_type('BOXUAN');
  4  begin
 5   DBMS_OUTPUT.PUT_LINE('-----该集合元素的最大个数：'||ename_table.limit);
  6  end;
  7  /
-----该集合元素的最大个数：25

PL/SQL 过程已成功完成。

4.first()   ： 返回集合变量的第一个元素的下标

  last()    ： 返回最后一个元素的下标

  prior()   ： 返回当前集合元素的前一个元素的下标

  next()   :  返回当前集合元素的后一个元素的下标

sys@SDROLUN>DECLARE
 2    TYPE ename_table_type IS TABLE OF emp.ename%TYPE
 3       INDEX BY BINARY_INTEGER;
  4
 5    ename_table  ename_table_type;
  6  BEGIN
 7    IF ename_table.EXISTS (1)
 8    THEN                                      --判断ename_table中是否存在元素
 9       ename_table (1) := 'SCOTT';
10    ELSE
11       DBMS_OUTPUT.put_line ('必须初始化集合元素');
12    END IF;
13
14    ename_table (-5) := 'SCOTT';
15    ename_table (1) := 'SMITH';
16    ename_table (5) := 'MARY';
17    ename_table (10) := 'BLACK';
18    DBMS_OUTPUT.put_line ('第一个元素：' || ename_table.first);
19    DBMS_OUTPUT.put_line ('最后一个元素：' || ename_table.last);
20    DBMS_OUTPUT.put_line ('元素5的前一个元素：' || ename_table.prior(5));
21    DBMS_OUTPUT.put_line ('元素5的后一个元素：' || ename_table.next(5));
22  END;
23  /
必须初始化集合元素
     第一个元素：-5
     最后一个元素：10
     元素5的前一个元素：1
     元素5的后一个元素：10

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01

5、EXTEND().

该方法用于扩展集合变量的尺寸，并为它们增加元素。

注意，该方法只适用于 嵌套表 和 VARRAY。

该方法有 EXTEND、EXTEND(n)、EXTEND(n, i)3中调用格式。其中：

EXTEND  用于为集合变量增加一个NULL元素。

EXTEND(n) 用于为集合变量添加 n 个NULL元素。

EXTEND(n, i)用于为集合变量添加 n 个元素（元素值与第 i个元素相同）。

sys@>DECLARE
 2    TYPE ename_table_type IS VARRAY (20) OF VARCHAR2 (10);
  3
 4    ename_table  ename_table_type;
  5  BEGIN
 6    ename_table := ename_table_type ('MARY');
 7    ename_table.EXTEND (5,1);                        --为集合变量添加5个元素
 8    DBMS_OUTPUT.put_line ('元素总个数：' || ename_table.COUNT);
 9    DBMS_OUTPUT.put_line ('第一个元素：' || ename_table.FIRST);
10    DBMS_OUTPUT.put_line ('最后一个元素：' || ename_table.LAST);
11  END;
12  /
元素总个数：6
第一个元素：1
最后一个元素：6

PL/SQL 过程已成功完成。

TRIM()   TRIM(n).

sys@>DECLARE
 2    TYPE ename_table_type IS VARRAY (20) OF VARCHAR2 (10);
  3
 4    ename_table  ename_table_type;
  5  BEGIN
 6    ename_table := ename_table_type ('MARY');
 7    ename_table.EXTEND (5,1);                        --为集合变量添加5个元素
 8    DBMS_OUTPUT.put_line ('元素总个数：' || ename_table.COUNT);
 9    ename_table.trim(2); --删除两个元素
10    DBMS_OUTPUT.put_line ('元素总个数：' || ename_table.COUNT);
11  END;
12  /
元素总个数：6
元素总个数：4

PL/SQL 过程已成功完成。

DELETE()  DELETE(n)   DELETE(m,n).

sys@>DECLARE
 2    TYPE ename_table_type IS TABLE OF emp.ename%TYPE
 3       INDEX BY BINARY_INTEGER;
  4
 5    ename_table  ename_table_type;
  6  BEGIN
 7    ename_table (-5) := 'SCOTT';
 8    ename_table (1) := 'SMITH';
 9    ename_table (5) := 'MARY';
10    ename_table (10) := 'BLACK';
11    DBMS_OUTPUT.put_line ('第一个元素：' || ename_table.FIRST);
12    DBMS_OUTPUT.put_line ('最后一个元素：' || ename_table.LAST);
13    DBMS_OUTPUT.put_line ('元素5的前一个元素：' || ename_table.PRIOR (5));
14    DBMS_OUTPUT.put_line ('元素5的后一个元素：' || ename_table.NEXT (5));
15    ename_table.DELETE (5);
16    DBMS_OUTPUT.put_line ('元素总个数：' || ename_table.COUNT);
17  END;
18  /
第一个元素：-5
最后一个元素：10
元素5的前一个元素：1
元素5的后一个元素：10
元素总个数：3

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.01

游标.

1、使用 %ISOPEN 属性

DECLARE
   CURSOR emp_cursor
   IS
     SELECT *
       FROM emp;
BEGIN
   IF emp_cursor%ISOPEN
   THEN
     CLOSE emp_cursor;
   END IF;
END;

2、使用 %FOUND 属性

3、使用 %NOTFOUND 属性

4、使用 %ROWCOUNT 属性.

sys@>select count(*) from emp;

  COUNT(*)
----------
       14

已用时间:  00: 00: 00.00
sys@>DECLARE
 2    CURSOR emp_cursor
 3    IS
 4       SELECT *
 5         FROM emp;
  6
 7    rec_emp   emp%ROWTYPE;
  8  BEGIN
 9    OPEN emp_cursor;
10
11    LOOP
12       FETCH emp_cursor
13        INTO rec_emp;
14
15       EXIT WHEN emp_cursor%NOTFOUND;
16    END LOOP;
17
18    DBMS_OUTPUT.put_line ('rowcount:' || emp_cursor%ROWCOUNT);
19
20    CLOSE emp_cursor;
21  END;
22  /
rowcount:14

PL/SQL 过程已成功完成。

游标 FOR 循环.

sys@>DECLARE
 2    CURSOR emp_cursor
 3    IS
 4       SELECT *
 5         FROM emp;
  6  BEGIN
 7    FOR emp_record IN emp_cursor
 8    LOOP
 9       DBMS_OUTPUT.put_line ('第' ||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.ename);
10    END LOOP;
11  END;
12  /
第1个雇员：SMITH
第2个雇员：ALLEN
第3个雇员：WARD
第4个雇员：JONES
第5个雇员：MARTIN
第6个雇员：BLAKE
第7个雇员：CLARK
第8个雇员：SCOTT
第9个雇员：KING
第10个雇员：TURNER
第11个雇员：ADAMS2
第12个雇员：JAMES2
第13个雇员：FORD2
第14个雇员：MILLER

PL/SQL 过程已成功完成。.

使用游标变量.

1、声明游标变量

TYPE cursor_variable_type IS REF CURSOR[RETURN return_type];

2、使用游标变量

OPEN cursor_variable FORSELECT;

3、异常函数

SQLERRM---错误信息

.

.

.

.

前一篇： 子查询—&—高级查询

后一篇： PL/SQL 高级应用