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 运算符
可运行在任何操作系统和平台上的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
是指在
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
运行时出现的错误叫做异常
异常可以分为预定义异常和用户定义的异常
随后介绍异常处理问题!!!!