Oracle Lesson 5 PL/SQL 编程

Oracle Lesson 5 PL/SQL 编程

                                                               Made by Zhangxu 2008.3.1

PL/SQL简介

PL/SQL是过程语言(Procedural Language)

与结构化查询语言(SQL)结合而成的编程语言，PL/SQL是对

SQL 的扩展，它支持多种数据类型，如大对象和集合类型

可以使用条件和循环等控制结构，还可以创建存储过程，触发器

和程序包，给SQL 语句的执行添加程序逻辑！他与Oracle服务器

和Oracle工具紧密集成，具备可移植，灵活性和安全性。

PL/SQL 的优点

支持 SQL

数据操纵命令

事务控制命令

游标控制

SQL 函数和 SQL 运算符

支持面向对象编程 (OOP)

可移植性

可运行在任何操作系统和平台上的Oralce 数据库。

更佳的性能

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

支持所有 SQL 数据类型

支持 NULL 值

支持 %TYPE 和 %ROWTYPE 属性类型

安全性

可以通过存储过程限制用户对数据的访问。

PL/SQL 的体系结构

PL/SQL引擎驻留在Oracle服务器中，该引擎接受PL/SQL块，并且对起

进行编译执行！！如下图所示：

PL/SQL 块简介

PL/SQL块是构成程序的基本单位，每一个块都包含有PL/SQL和SQL语句，典型的PL/SQL代码块包含如下结构:

(1).声明部分

(2)可执行部分

(3)异常处理

语法结构如下：

   [DECLARE

    declarations]—声明部分

    BEGIN

    executable statements---可执行部分

    [EXCEPTION –异常处理

    handlers]

    END;

代码示例：

DECLARE

 qty_on_hand NUMBER(5);--程序声明部分，定义变量，游标和自定义常量

BEGIN           ------------------------------------

 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 块中可以使用变量和常量

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

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

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

声明变量和常量的语法

identifier [CONSTANT] datatype [NOT NULL]  

              [:= | DEFAULT expr];

给变量赋值有两种方法

使用赋值语句 :=

使用 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支持的内置数据类型有如下几种：

如图：

标量类型

数字

字符

CHAR

VARCHAR2

LONG

RAW

LONG RAW

结构如图：

日期时间类型

日期时间类型

存储日期和时间数据

常用的两种日期时间类型

DATE

TIMESTAMP

 

布尔类型

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

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

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

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

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

 

如图：

LOB类型

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

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

LOB 类型包括：

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

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

NCLOB   存储大型UNICODE字符数据

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

 FROM my_book_text

 WHERE chapter_id=5;

 amount := 24; -- 要读取的字符数

 offset := 1;   -- 起始位置

 DBMS_LOB.READ(clob_var,amount,offset,output_var);

 DBMS_OUTPUT.PUT_LINE(output_var);

END;

/

 

如图：

属性类型

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

属性类型有两种：

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

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

使用属性类型的优点：

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

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

 

如图：

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

控制结构

条件控制

IF 语句

IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF

示例代码：

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 语句用于根据单个变量或表达式与多个值进行比较

执行 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;

 

循环控制

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

循环控制语句包括：

LOOP、EXIT 和 EXIT WHEN

循环控制的三种类型：

 

LOOP   -   无条件循环

LOOP

 sequence_of_statements

END LOOP;

 

WHILE - 根据条件循环

WHILE condition LOOP

 sequence_of_statements

END LOOP;

 

FOR - 循环固定的次数

FOR counter IN [REVERSE] value1..value2

LOOP

 sequence_of_statements

END LOOP;

 

顺序控制

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

顺序控制语句包括：

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

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

 

GOTO 语句

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;

 ELSE

    GOTO quit;

 END IF;

 <<updation>>

 UPDATE itemfile SET qty_hand = qty_hand + re_level

 WHERE itemcode = 'i201';

 <<quit>>

 NULL;

END;

 

动态 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;

 

错误处理

在运行程序时出现的错误叫做异常

发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型：

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

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

随后将专门介绍异常处理！

代码示例：

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;

引发应用程序错误

RAISE_APPLICATION_ERROR 过程

用于创建用户定义的错误信息

可以在可执行部分和异常处理部分使用

错误编号必须介于 –20000 和 –20999 之间

错误消息的长度可长达 2048 个字节

引发应用程序错误的语法：

   RAISE_APPLICATION_ERROR(error_number, error_message);

代码示例：

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;

 

总结

PL/SQL 是一种可移植的高性能事务处理语言

PL/SQL 引擎驻留在 Oracle 服务器中

PL/SQL 块由声明部分、可执行部分和异常处理部分组成

PL/SQL 数据类型包括标量数据类型、LOB 数据类型和属性类型

控制结构包括条件控制、循环控制和顺序控制

PL/SQL 支持动态 SQL

运行时出现的错误叫做异常

异常可以分为预定义异常和用户定义的异常

随后介绍异常处理问题！！！！