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 ONDECLARE  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_statementsEND LOOP;

　　2.WHILE - 根据条件循环

WHILE condition LOOP   sequence_of_statementsEND LOOP;

　　3.FOR - 循环固定的次数

FOR counter IN [REVERSE] value1..value2LOOP   sequence_of_statementsEND 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 worlddeclare    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-10declare    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-5Declare    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;