Oracle PL/SQL

PL/SQL 简介

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言，是对 SQL 的扩展，它支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构，可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑，与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。

 

PL/SQL 的优点

• 支持 SQL，在 PL/SQL 中可以使用：

　　1.数据操纵命令

　　2.事务控制命令

　　3.游标控制

　　4.SQL 函数和 SQL 运算符

• 支持面向对象编程 (OOP)

• 可移植性，可运行在任何操作系统和平台上的Oralce 数据库

• 更佳的性能，PL/SQL 经过编译执行

 

　　

• 与 SQL 紧密集成，简化数据处理。

　　1.支持所有 SQL 数据类型

　　2.支持 NULL 值

　　3.支持 %TYPE 和 %ROWTYPE 属性类型

　　

 

• 安全性，可以通过存储过程限制用户对数据的访问

 

PL/SQL 的体系结构

PL/SQL 引擎驻留在 Oracle 服务器中，该引擎接受 PL/SQL 块并对其进行编译执行。

 

PL/SQL基础

PL/SQL 块简介

PL/SQL 块是构成 PL/SQL 程序的基本单元，它将逻辑上相关的声明和语句组合在一起。

PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分。

[DECLARE  
    declarations]   --声明部分定义变量、游标和自定义异常
    BEGIN
    executable statements  --包含 SQL 和 PL/SQL 语句的可执行部分
    [EXCEPTION 
    handlers]  --指定出现错误时需要执行的操作
    END;

DECLARE
  qty_on_hand NUMBER(5);    --声明部分定义变量、游标和自定义异常
BEGIN     --begin到end 包含 SQL 和 PL/SQL 语句的可执行部分
  SELECT quantity INTO qty_on_hand
  FROM Products
  WHERE product = '芭比娃娃'
  FOR UPDATE OF quantity;
  IF qty_on_hand > 0 THEN
    UPDATE Products SET quantity = quantity + 1
    WHERE product = '芭比娃娃';
    INSERT INTO purchase_record
    VALUES ('已购买芭比娃娃', SYSDATE);
  END IF;
  COMMIT;
EXCEPTION  /* 异常处理语句 */  --指定出现错误时需要执行的操作
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);  
END;

PL/SQL中的复合符号

　　:=        赋值操作符

　　||         连接操作符

　　--        单行注释

　　/**/      多行注释

　　<<>>  标签分隔符

　　..         范围操作符

　　**        求幂操作符

 

变量和常量

PL/SQL 块中可以使用变量和常量

　　1.在声明部分声明，使用前必须先声明

　　2.声明时必须指定数据类型，每行声明一个标识符

　　3.在可执行部分的 SQL 语句和过程语句中使用

 

声明变量和常量的语法：

identifier [CONSTANT] datatype [NOT NULL]   
        [:= | DEFAULT expr];

给变量赋值有两种方法：

1.使用赋值语句 :=

2.使用 SELECT INTO 语句

DECLARE
  icode VARCHAR2(6);
  p_catg VARCHAR2(20);
  p_rate NUMBER;
  c_rate CONSTANT NUMBER := 0.10;
BEGIN
  ...
  icode := 'i205';
  SELECT p_category, itemrate * c_rate
  INTO  p_catg, p_rate
  FROM itemfile WHERE itemcode = icode;
  ...
END;

 数据类型

PL/SQL 支持的内置数据类型

 

 指定数值的存储格式

 字符数据类型

字符数据类型包括：

1.CHAR

2.VARCHAR2

3.LONG

4.RAW

5.LONG RAW

PL/SQL 的数据类型与 SQL数据类型的比较

数据类型	SQL类型	PL/SQL类型
CHAR	1~2000	1~32767
LONG	1~2GB	1~32760
LONG RAW	1~2GB	1~32760
RAW	1~2000	1~32767
VARCHAR2	1~4000	1~32767

日期时间和布尔数据类型

日期时间类型

1.存储日期和时间数据

2.常用的两种日期时间类型

①DATE

②TIMESTAMP

 

布尔数据类型

1.此类别只有一种类型，即BOOLEAN类型

2.用于存储逻辑值(TRUE、FALSE和NULL)

3.不能向数据库中插入BOOLEAN数据

4.不能将列值保存到BOOLEAN变量中

5.只能对BOOLEAN变量执行逻辑操作

LOB  数据类型

用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。

LOB 数据类型可存储最大 4GB的数据。

LOB 类型包括：

　　1.BLOB   将大型二进制对象存储在数据库中

　　2.CLOB   将大型字符数据存储在数据库中

　　3.NCLOB   存储大型UNICODE字符数据

　　4.BFILE     将大型二进制对象存储在操作系统文件中

LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置

DBMS_LOB程序包用于操纵 LOB 数据

SET SERVEROUTPUT ON
DECLARE
  clob_var   CLOB;
  amount     INTEGER;
  offset     INTEGER;
  output_var VARCHAR2(100);
BEGIN
  SELECT chapter_text INTO clob_var   --从表中选择 CLOB 定位符到 clob_var变量中
  FROM my_book_text
  WHERE chapter_id=5;
  amount := 24;  -- 要读取的字符数
  offset := 1;   -- 起始位置
  DBMS_LOB.READ(clob_var,amount,offset,output_var);  --从CLOB数据中读取24个字符存储到 output_var 变量中
  DBMS_OUTPUT.PUT_LINE(output_var);  --显示读到的信息
END;

  属性类型

用于引用数据库列的数据类型，以及表示表中一行的记录类型。

 

属性类型有两种：

1.%TYPE  -  引用变量和数据库列的数据类型

2.%ROWTYPE  -  提供表示表中一行的记录类型

 

使用属性类型的优点：

1.不需要知道被引用的表列的具体类型

2.如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变

icode itemfile.itemcode%TYPE;
emp_rec scott.emp%ROWTYPE;

 逻辑比较

逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式，布尔表达式由关系运算符与变量或常量组成。

关系运算符	说明
=	比较两个变量是否相等，如果值相当，则返回 True
<>, !=	比较两个变量，如果不相等，则返回 True
<	比较两个变量，检查值 1 是否小于值 2
>	比较两个变量，检查值 1 是否大于 值 2
<=	比较两个变量，检查变量 1 是否小于等于变量 2
>=	比较两个变量，检查变量 1 是否大于等于变量 2

 

布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接。

布尔表达式有三种类型：

　　1.数字布尔型

　　2.字符布尔型

　　3.日期布尔型

 

控制结构

PL/SQL 支持的流程控制结构：

　　条件控制

　　　　1.IF 语句

　　　　2.CASE 语句

　　循环控制

　　　　1.LOOP 循环

　　　　2.WHILE 循环

　　　　3.FOR 循环

　　顺序控制

　　　　1.GOTO 语句

　　　　2.NULL 语句

 

条件控制

DECLARE
    icode VARCHAR2(4);
    irate NUMBER;
BEGIN
    icode := 'i203';
  SELECT itemrate INTO irate FROM itemfile 
  WHERE  itemcode = icode;
  IF irate > 200 THEN      --条件判断
    UPDATE itemfile SET itemrate = itemrate - 200
    WHERE itemcode = icode;
  ELSE
    UPDATE itemfile SET itemrate = itemrate - 50
    WHERE itemcode = icode;
  END IF;    --结束
  DBMS_OUTPUT.PUT_LINE('itemrate='|| irate);
END;

CASE 语句用于根据单个变量或表达式与多个值进行比较，执行 CASE 语句前，先计算选择器的值。

BEGIN
    CASE ‘&grade’
      WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’);
      WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’);
      WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’);
      WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’);
      WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’较差’);
      ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’);
    END CASE;
END;

 循环控制

循环控制用于重复执行一系列语句。

循环控制语句包括：

　　1.LOOP、EXIT 和 EXIT WHEN

 

循环控制的三种类型：

　　1.LOOP   -   无条件循环

LOOP 
  sequence_of_statements
END LOOP;

　　2.WHILE  -  根据条件循环

WHILE condition LOOP 
  sequence_of_statements
END LOOP;

　　3.FOR  -  循环固定的次数

FOR counter IN [REVERSE] value1..value2
LOOP 
  sequence_of_statements
END LOOP;

loop...exit...when...end loop循环控制

采用loop...exit...when...end loop循环控制的语法结构如下所示：

loop
  循环体；
  exit when循环条件;
end loop

while...loop...end loop循环控制

这种循环控制的语法如下：

while 条件
loop
  执行语句段;
end loop;

循环例子：

--while循环
--循环输出Hello world
declare
    var_i int := 10;
begin
    loop
        dbms_output.put_line('Hello world');
        if var_i = 0 then
            exit;
        end if;
        var_i := var_i - 1;
    end loop;
end;

--loop输出1-10
declare
    var_i int := 1;
begin
    loop
        dbms_output.put_line('i = ' || var_i);
        exit when var_i = 10;
        var_i := var_i + 1;
    end loop;
End

--while循环1-5
Declare
    var_i int:=1;
Begin
    while var_i<=10 loop
        Dbms_output.put_line('I='||var_i);
        Var_i:=var_i+1;
    End loop;
End;



--for 循环 : for 变量 in 范围 loop
--1-10输出
begin
    for i in 1..10 loop
        dbms_output.put_line('i = ' || i);
    end loop;
end

--对员工的工资判断，如果少于2000则加50，直到工资满足为止！
declare
    var_sal int;
begin
    loop
        select sal into var_sal from emp where empno = 7499;
        exit when var_sal >= 2000;
        update emp set sal = sal + 50 where empno = 7499;
        dbms_output.put_line('加50');
    end loop;
end;

顺序控制

顺序控制用于按顺序执行语句。

顺序控制语句包括：

　　1.GOTO 语句 -  无条件地转到标签指定的语句

　　2.NULL 语句 -  什么也不做的空语句

DECLARE
  qtyhand itemfile.qty_hand%type;
  relevel itemfile.re_level%type;
BEGIN
  SELECT qty_hand,re_level INTO qtyhand,relevel
  FROM itemfile WHERE itemcode = 'i201';
  IF qtyhand < relevel THEN
    GOTO updation;  --跳转到<<updation>>语句块
  ELSE
    GOTO quit;  --跳转到<<quit>>
  END IF;
  <<updation>>
  UPDATE itemfile SET qty_hand = qty_hand + re_level
  WHERE itemcode = 'i201';
  <<quit>>
  NULL;
END;

 动态 SQL

动态 SQL 是指要执行的SQL语句需要在执行的时候才能确定的SQL语句，是在PL/SQL程序执行时生成的 SQL 语句，编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行。DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行。

 

执行动态 SQL 的语法：

   EXECUTE IMMEDIATE dynamic_sql_string

      [INTO  define_variable_list]

      [USING bind_argument_list];

 例：

DECLARE
  sql_stmt VARCHAR2(200);
  emp_id NUMBER(4) := 7566;
  emp_rec emp%ROWTYPE;
BEGIN
    EXECUTE IMMEDIATE 
    'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';

    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
END;

--显示员工编号为7499的所有信息
declare
    Var_row emp%rowtype;
begin
    select * into var_row from emp where empno = 7499;
    dbms_output.put_line('编号=' || var_row.empno || '姓名=' || var_row.ename);
end;

--动态执行
declare
    var_row emp%rowtype;
    var_sql varchar(100);
    var_empno int;
begin
    var_sql := 'select * from emp where empno = :num' ;
    execute immediate var_sql into var_row using var_empno;
    dbms_output.put_line('编号=' || var_row.empno || '姓名=' || var_row.ename);
end;

--用动态SQL来实现分页
begin
    execute immediate 'select * from (select rownum rn, d.* from (select * from :tableName order by sal desc) d) where rn > :min and rn <= :max' using 'emp' , 1, 5;
End;

 错误处理

 

在运行程序时出现的错误叫做异常，发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分。

 

异常有两种类型：

1.预定义异常 -  当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

2.用户定义异常  -  用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

 

 

处理预定义异常

access_into_null   --在未初化对象时出现
case_not_found    --在CASE语句中的选项与用户输入的数据不匹配时出现
collection_is_null    --在给尚未初始化的表或数组赋值时出现
cursor_already_open    --在用户试图打开已经打开的游标时出现
dup_val_on_index    --在用户试图将重复的值存在使用唯一索引的数据库列中时出现
invalid_cursor        --在执行非法游标运算（如打开一个尚未打开的游标）时出现
invalid_number     --在将字符串转换为数字时出现
login_denied        --在输入的用户名或密码无效时出现
no_data_found     --在表中不存在的请求的行时出现，此外，当程序引用已经删除的元素时
storage_error       --在内存损坏或PL/SQL耗尽内存时出现
too_many_rows    --在执行SELECT INTO语句后返回多行时出现
value_error    --在产生大小限制错误时出现
zero_divide    --以零作除数时出现
Others          --针对所有异常

 处理用户定义异常

DECLARE 
  invalidCATEGORY EXCEPTION;
  category VARCHAR2(10);
BEGIN
  category := '&Category';
  IF category NOT IN ('附件','顶盖','备件') THEN
    RAISE invalidCATEGORY;
  ELSE
    DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);
  END IF;
EXCEPTION
  WHEN invalidCATEGORY THEN
    DBMS_OUTPUT.PUT_LINE('无法识别该类别');
END;

 引发应用程序错误

DECLARE
  rate itemfile.itemrate%TYPE;
  rate_exception EXCEPTION;  --定义异常
BEGIN
  SELECT NVL(itemrate,0) INTO rate FROM itemfile 
  WHERE  itemcode = 'i207';
  IF rate = 0 THEN
    RAISE rate_exception;  --引发异常
  ELSE
    DBMS_OUTPUT.PUT_LINE('项费率为：' || rate);
  END IF;
EXCEPTION
  WHEN rate_exception THEN  --扑捉异常
    RAISE_APPLICATION_ERROR(-20001, '未指定项费率');
END;

引发系统级错误

begin
    if 10 > 5 then
        raise_application_error(-20001, '这个是自定义错误显示');
    end if;
end;