oracle数据库深入（三）

原创

pianzif 2013-12-25 10:43:57 博主文章分类：oracle专题 ©著作权

文章标签 oracle 数据库触发器动态子程序 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者pianzif的原创作品，请联系作者获取转载授权，否则将追究法律责任

视图

概念：

一个或者是多个表的数据的简化描述

虚拟表查询仅仅存储oracle数据字典中，实际上没有存放在任何地方不用消耗其他空间

权限

隐藏复杂查询

建立视图

从视图中获取数据与其他表基本一样

增删改视图

删除视图

PL/SQL块结构

以块为单位

匿名块：动态生成。只能执行一次，不存储在数据库中
带名块：动态生成。只能执行一次，不存储在数据库中
子程序：可以存储在数据库中的过程、函数和包；多次执行，可以外部调用
触发器：多次执行，事件触发。

参见：http://book.51cto.com/art/201107/274032.htm

块结构

块头
声明部分
执行部分（必须）
异常处理部分

块头（Header）

只有命名块才会有这个单元。块头单元就确定了这个命名块或者程序的调用方式，这个单元是可选的。

声明单元（Declaration section）

这部分定义变量，游标，以及可以被后面的执行单元或异常处理单元引用的子块。这一单元也是可选的。

执行单元（Execution section）

这部分包含了PL/SQL引擎在运行时要执行的语句，这一单元是必须的。

异常处理单元（Exception section）

这部分处理的是正常处理过程中抛出的异常（根据警告和错误条件），这一单元也是可选的。

oracle数据库深入（三）_动态

oracle数据库深入（三）_子程序_02

词法单位

词法单位 ——块——PL/SQL

PL/SQL字符集

注释

单行注视 --

多行注释 /* */

PL/SQL控制结构

http://blog.csdn.net/rcom10002/article/details/1768765

http://www.cnblogs.com/huyong/archive/2011/05/13/2045407.html

if
case
循环
标号和goto
null语句

oracle数据库深入（三）_动态_03

IF <布尔表达式> THEN
  PL/SQL 和 SQL语句
END IF;
-----------------------
IF <布尔表达式> THEN
  PL/SQL 和 SQL语句
ELSE
  其它语句
END IF;
-----------------------
IF <布尔表达式> THEN
  PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
  其它语句
ELSIF < 其它布尔表达式> THEN
  其它语句
ELSE
  其它语句
END IF;

提示: ELSIF 不能写成 ELSEIF

例子

DECLARE
   v_first_name  VARCHAR2(20);
   v_salary NUMBER(7,2);
BEGIN
   SELECT first_name, salary INTO v_first_name, v_salary FROM employees
   WHERE employee_id = &emp_id;
   DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的工资是'||v_salary);
   IF v_salary < 10000 THEN
      DBMS_OUTPUT.PUT_LINE('工资低于10000');
   ELSE
      IF 10000 <= v_salary AND v_salary < 20000 THEN
         DBMS_OUTPUT.PUT_LINE('工资在10000到20000之间');
      ELSE
         DBMS_OUTPUT.PUT_LINE('工资高于20000');
      END IF;
   END IF;
END;
————————————————————————————————
DECLARE
   v_first_name  VARCHAR2(20);
   v_hire_date DATE;
   v_bonus NUMBER(6,2);
BEGIN
   SELECT first_name, hire_date INTO v_first_name, v_hire_date FROM employees
   WHERE employee_id = &emp_id;
   IF v_hire_date > TO_DATE('01-1月-90') THEN
      v_bonus := 800;
   ELSIF v_hire_date > TO_DATE('01-1月-88') THEN
      v_bonus := 1600;
   ELSE
      v_bonus := 2400;
   END IF;
   DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的雇佣日期是'||v_hire_date
                                    ||'、奖金是'||v_bonus);
END;

case

---------格式一---------
CASE 条件表达式
  WHEN 条件表达式结果1 THEN
     语句段1
  WHEN 条件表达式结果2 THEN
     语句段2
  ......
  WHEN 条件表达式结果n THEN
     语句段n
  [ELSE 条件表达式结果]
END;
---------格式二---------
CASE
  WHEN 条件表达式1 THEN
     语句段1
  WHEN 条件表达式2 THEN
     语句段2
  ......
  WHEN 条件表达式n THEN
     语句段n
  [ELSE 语句段]
END;

例子：

DECLARE
  V_grade char(1) := UPPER('&p_grade');
  V_appraisal VARCHAR2(20);
BEGIN
  V_appraisal :=
  CASE v_grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Very Good'
    WHEN 'C' THEN 'Good'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||'  Appraisal: '|| v_appraisal);
END;
————————————————————————————————
DECLARE
   v_first_name employees.first_name%TYPE;
   v_job_id employees.job_id%TYPE;
   v_salary employees.salary%TYPE;
   v_sal_raise NUMBER(3,2);
BEGIN
   SELECT first_name,   job_id,   salary INTO
          v_first_name, v_job_id, v_salary
   FROM employees WHERE employee_id = &emp_id;
   CASE
      WHEN v_job_id = 'PU_CLERK' THEN
         IF v_salary < 3000 THEN v_sal_raise := .08;
         ELSE v_sal_raise := .07;
         END IF;
      WHEN v_job_id = 'SH_CLERK' THEN
         IF v_salary < 4000 THEN v_sal_raise := .06;
         ELSE v_sal_raise := .05;
         END IF;
      WHEN v_job_id = 'ST_CLERK' THEN
         IF v_salary < 3500 THEN v_sal_raise := .04;
         ELSE v_sal_raise := .03;
         END IF;
      ELSE
         DBMS_OUTPUT.PUT_LINE('该岗位不涨工资: '||v_job_id);
   END CASE;
   DBMS_OUTPUT.PUT_LINE(v_first_name||'的岗位是'||v_job_id
                                    ||'、的工资是'||v_salary
                                    ||'、工资涨幅是'||v_sal_raise);
END;

循环

简单循环：

LOOP
    要执行的语句;
    EXIT WHEN <条件语句> --条件满足，退出循环语句
END LOOP;

例子：

DECLARE
    int NUMBER(2) :=0;
BEGIN
   LOOP
      int := int + 1;
      DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int);
      EXIT WHEN int =10;
   END LOOP;
END;

while循环

WHILE <布尔表达式> LOOP
    要执行的语句;
END LOOP;

例子：

DECLARE
  x NUMBER :=1;
BEGIN
   WHILE x<=10 LOOP
      DBMS_OUTPUT.PUT_LINE('X的当前值为:'||x);
       x:= x+1;
   END LOOP;
END;

数字式循环

[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
  要执行的语句;
END LOOP [循环标签];

每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT 退出循环

例子：

BEGIN
   FOR int  in 1..10 LOOP
       DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);
   END LOOP;
END;
——————————————————————————————
CREATE TABLE temp_table(num_col NUMBER);
DECLARE
    V_counter NUMBER := 10;
BEGIN
   INSERT INTO temp_table(num_col) VALUES (v_counter );
   FOR v_counter IN 20 .. 25 LOOP
      INSERT INTO temp_table (num_col ) VALUES ( v_counter );
   END LOOP;
   INSERT INTO temp_table(num_col) VALUES (v_counter );
   FOR v_counter IN REVERSE 20 .. 25 LOOP
      INSERT INTO temp_table (num_col ) VALUES ( v_counter );
   END LOOP;
END ;
DROP TABLE temp_table;
——————————————————————————————————
DECLARE
   TYPE jobids_varray IS VARRAY(12) OF VARCHAR2(10); --定义一个VARRAY数据类型
   v_jobids JOBIDS_VARRAY; --声明一个具有JOBIDS_VARRAY数据类型的变量
   v_howmany NUMBER; --声明一个变量来保存雇员的数量
BEGIN
   --用某些job_id值初始化数组
   v_jobids := jobids_varray('FI_ACCOUNT', 'FI_MGR', 'ST_CLERK', 'ST_MAN');
   --用FOR...LOOP...END LOOP循环使用每个数组成员的值
   FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP
   --针对数组中的每个岗位，决定该岗位的雇员的数量
      SELECT count(*) INTO v_howmany FROM employees WHERE job_id = v_jobids(i);
      DBMS_OUTPUT.PUT_LINE ( '岗位'||v_jobids(i)||
                       '总共有'|| TO_CHAR(v_howmany) || '个雇员');
   END LOOP;
END;
————————在While循环中嵌套loop循环——————————————
/*求100至110之间的素数*/
DECLARE
   v_m NUMBER := 101;
   v_i NUMBER;
   v_n NUMBER := 0;
BEGIN
   WHILE v_m < 110 LOOP
      v_i := 2;
      LOOP
         IF mod(v_m, v_i) = 0 THEN
            v_i := 0;
            EXIT;
         END IF;
                                                                                                                                                                                        
         v_i := v_i + 1;
         EXIT WHEN v_i > v_m - 1;
      END LOOP;
                                                                                                                                                                                          
      IF v_i > 0 THEN
         v_n := v_n + 1;
         DBMS_OUTPUT.PUT_LINE('第'|| v_n || '个素数是' || v_m);
      END IF;
      v_m := v_m + 2;
   END LOOP;
END;

标号和GOTO

PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下

GOTO label;
......
<<label>>　/*标号是用<<　>>括起来的标识符 */

注意，在以下地方使用是不合法的，编译时会出错误。

u 跳转到非执行语句前面。

u 跳转到子块中。

u 跳转到循环语句中。

u 跳转到条件语句中。

u 从异常处理部分跳转到执行。

u 从条件语句的一部分跳转到另一部分。

DECLARE
   V_counter NUMBER := 1;
BEGIN
   LOOP
     DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);
     V_counter := v_counter + 1;
   IF v_counter > 10 THEN
       GOTO labelOffLOOP;
   END IF;
   END LOOP;
   <<labelOffLOOP>>
     DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);
END;
————————————————————————————
DECLARE
   v_i NUMBER := 0;
   v_s NUMBER := 0;
BEGIN
   <<label_1>>
   v_i := v_i + 1;
   IF v_i <= 1000 THEN
      v_s := v_s + v_i;
      GOTO label_1;
   END IF;
   DBMS_OUTPUT.PUT_LINE(v_s);
END;

NULL 语句

在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。如：

DECLARE
    ...
BEGIN
    ...
    IF v_num IS NULL THEN
    GOTO labelPrint;
    END IF;
  …
  <<labelPrint>>
  NULL; --不需要处理任何数据。
END;

DECLARE
   v_emp_id employees.employee_id%TYPE;
   v_first_name employees.first_name%TYPE;
   v_salary employees.salary%TYPE;
   v_sal_raise NUMBER(3,2);
BEGIN
   v_emp_id := &emp_id;
   SELECT first_name, salary INTO v_first_name, v_salary
   FROM employees WHERE employee_id = v_emp_id;
   IF v_salary <= 3000 THEN
      v_sal_raise := .10;
      DBMS_OUTPUT.PUT_LINE(v_first_name||'的工资是'||v_salary
                                       ||'、工资涨幅是'||v_sal_raise);
   ELSE
      NULL;
   END IF;
END;