Oracle-PL/SQL使用

概述：PL/SQL（Procedural Language/SQL）一种过程化语言，通过增加编程语言的特点，实现对SQL的扩展。

PL/SQL的特点

1. 支持所有SQL的语法
2. 支持case语句，方便的实现循环
3. 通过继承，实现子类具有父类的属性和方法
4. 设置了新的日期类型

PL/SQL语句块

• PL/SQL程序是按照块结构进行划分
• 块是PL/SQL程序的基本单位

DECLARE
    v_name varchar2(30) := 'Jack'; --定义变量并赋值
    v_age number := 6; 
BEGIN
    dbms_output.put_line(1/0);  --异常语句，注释后就不会跳入后面的异常代码了
    dbms_output.put_line('姓名：'||v_name||',年龄：'||v_age);--输出语句
Exception
    When others then
        DBMS_OUTPUT.PUT_LINE('代码异常');
END;

declare关键字：用于定义变量或者常量。

变量命名规则

• 变量名首字母必须是英文字母，其后可以是字母、数字或者特殊字符$、#和下划线
• 变量名长度不超过30个字符
• 变量名中不能有空格
• 一般有自己的规范最好，例如 字符型 v_xx，日期 d_xx, 数值 n_xx等

条件结构
IF-THEN-ELSIF语句 表达式 是bool布尔类型的值

IF '表达式1' THEN  
    '输出1'
ELSIF '表达式2' THEN
    '输出2'
ELSE
    '输出3'
END IF;

CASE语句 Oracle 9i 后引入

CASE variable
    WHEN value1 THEN statements1
    WHEN value2 THEN statements2
    ……
    WHEN valuen THEN statementsn
    [ELSE else_statements]
END CASE;

DECLARE
       grade char:='A';
       remark varchar2(20);
BEGIN
       CASE grade
            WHEN 'A' THEN remark:="is Excellent";
            WHEN 'B' THEN remark:="is Good";
            WHEN 'C' THEN remark:="is Normal";
            WHEN 'D' THEN remark:="is Bad";
            ELSE remark:="big Problem"
       END CASE
END;

循环结构
LOOP循环

DECLARE
    v_count integer := 1;
BEGIN
    LOOP  --开始循环
        v_count:=v_count+1;
        IF v_count>=10 THEN
            EXIT;  --退出循环
        END IF;
    END LOOP;  --结束循环
END;

WHILE循环语句

DECLARE
   a number(2) := 10;
BEGIN
   WHILE a < 20 LOOP
      dbms_output.put_line('value of a: ' || a);
      a := a + 1;
   END LOOP;
END;

FOR循环语句

DECLARE
   a number(2);
BEGIN
   FOR a in 10 .. 20 LOOP
       dbms_output.put_line('value of a: ' || a);
  END LOOP;
END;

动态SQL：编译期间SQL 语句是不确定的，并且在运行时允许发生变化。

动态SQL应用场合

• 要执行一个DDL 语句时
• 需要增加程序的灵活性时
• 使用包DBMS_SQL动态执行SQL语句时

--语法：EXCEUTE IMMEDIATE dynamic_sql;
DECLARE
v_table varchar2(30) := 'tb_table'; --定义变量并赋值
BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM '||v_table ;
END;

--绑定变量：通过占位符绑定参数；参数类型可以是集合、对象等；
DECLARE
    plsql varchar2(200);
    t_name varchar2(20):='John';
    t_id integer:='1002';
BEGIN
    plsql:='insert into temp_table values(:1,:2)';
    EXECUTE IMMEDIATE plsql using t_id,t_name;
END;

异常处理
PL/SQL的预定义异常
access_into_null：视图给一个没有初始化的对象赋值
case_not_found：在CASE语句中没有WHEN子句被选择，并且没有ELSE子句
invalid_number：视图将一个非有效的字符串转换成数字
loggin_denied：使用无效的用户名和口令登录Oracle
no_data_found：产寻语句无返回数据，或者引用了一个被删除的元素，或者引用了一个没有被初始化的元素
timeout_on_resource：Oracle在等待资源时发生超时的现象

--当id=1031存在时就抛出异常temp_ex，EXCEPTION处理异常
DECLARE
     temp_ex exception;
     t_num integer;
BEGIN
    select count(id) into t_num from temp_table where   id='1031';
    IF t_num>=1 THEN
         RAISE temp_ex;  --抛出异常
    END IF;
    DBMS_OUTPUT.PUT_LINE('该用户不存在');
    EXCEPTION            --处理异常
        WHEN temp_ex THEN
              DBMS_OUTPUT.PUT_LINE ('该用户已存在');
END;