数据库系统之PL/SQL-1

什么是PL/SQL ？

PL/SQL是SQL的过程扩展。
PL/SQL = procedural Programming Language + SQL。
我们需要PL/SQL来填补高级声明性查询语言和过程性编程语言之间的空白。
PL/SQL是编程语言Ada的一个子集。
PL/SQL包含：

1. Data Manipulation statements of SQL
2. SELECT statement
3. variables
4. assignment statement
5. conditional control statements
6. repetition statement
7. exception handling
8. procedure and function statements
9. packages

程序结构

PL/SQL是一种块结构语言。
这意味着它的基本单元，如匿名块、过程和函数都是逻辑块。
匿名块仅对单个处理是持久的，即它不存储在数据字典中。
命名块（过程或函数）对于许多处理是持久的，即它可以存储在数据字典中。
逻辑块可以嵌套到任何级别。
逻辑块由声明性、可执行和异常组件组成。
声明性组件由常量、变量、类型、方法、游标等的声明组成，并且是可选的。
可执行组件由可执行代码组成，并且必须至少有一条语句 。
异常组件由处理异常的可执行代码组成，它是可选的（可以没有）。
匿名块示例：

-- 一个单行注释示例
DECLARE -- 声明性组件开头的关键字
/* 声明性组件:
多行注释的示例 */

BEGIN -- 关键字，可执行组件的开始
/* 执行组件 */
NULL; -- 它必须包含至少一条语句，
-- NULL是可选的空语句

EXCEPTION -- 关键字，异常组件的开始
/* 异常组件 */
END; -- 关键字，匿名块的结束
/ --斜线表示:执行此过程（必须加斜线）

声明式组件 （Declarative components）

声明性组件包含变量、常量、cursors、过程和函数的声明。

DECLARE 
stock_num NUMBER(5); 
stock_name VARCHAR(30); 
stock_date DATE;
stock_required NUMBER(5) := 30; 
limit CONSTANT NUMBER(11,2) := 2.45; 
stock_value STOCK.value%TYPE 
stock_row STOCK%ROWTYPE 

CURSOR Q IS
SELECT snum
FROM STUDENT
WHERE name ='Jo';

执行组件 （Executable components）

执行组件包括赋值语句、条件控制语句、迭代语句、过程和函数调用、SQL语句。

--赋值语句
student_num := 910000; 
--条件控制语句
SELECT name
INTO student_name
FROM STUDENT
WHERE s# = student_num;
--if语句
IF (a > b)
THEN
a := a + 1;
c := c + 2
ELSIF (a < b)
c := c - 2
ELSE
b:= b + 1
END IF;
--For语句
FOR i IN 1..100 LOOP
b := b - i
END LOOP;

异常组件 （Exception components）

异常组件由可执行语句组成，这些语句在执行过程中为异常情况提供服务。

--为异常情况提供处理方法
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO AUDIT_TABLE VALUES( SYSDATE, snum )
WHEN OTHERS
i: = i + 1
UPDATE DEPARTMENT
SET budget = i * budget;
END;

--第二种异常处理
DECLARE
too_large EXCEPTION;
BEGIN
IF a > 100000 THEN
RAISE too_large;
END IF;
EXCEPTION
WHEN too_large THEN
DBMS_OUTPUT.PUT_LINE ('Too large ! ');
END;

Anonymous block的结构

结构如下：

DECLARE 
-- 可选的声明
BEGIN 
-- 可执行语句，至少需要一条语句
EXCEPTION 
-- 可选的异常处理程序
END; 
/ -- 处理命令，别忘了在END后面加左斜杠

输出Hello world !的匿名块：

SET SERVEROUTOUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello world !');--DBMS_OUTPUT.PUT_LINE('XXXX');为输出格式
END;
/

举一个完整的例子：

DECLARE
average NUMBER(8,2); --声明一个变量average，它的数据类型是NUMBER，有效位数是8，精确到小数点后两位
BEGIN 
SELECT avg(budget)  --将表DEPARTMENT中budget计算平均数，并把计算后的结果给我们声明的average
INTO average 
FROM DEPARTMENT; 

IF average < 3000 THEN --如果average < 3000，那么我们升级表，让表中的所有budget 的值+100
UPDATE DEPARTMENT
SET budget = budget+100; 
END IF 

END;
/ --执行

Procedure结构

结构如下：

PROCEDURE procedure_name ( parameters ) IS 
-- 可选的PL / SQL声明
BEGIN 
-- 可执行语句，至少需要一条语句
EXCEPTION 
-- 可选的异常处理程序
END procedure_name; 

输出Hello world ! 的Procedure结构：

PROCEDURE hello_world ( hello IN VARCHAR2,
world IN VARCHAR2,
hello_world OUT VARCHAR2 ) IS

BEGIN
hello_world := hello || ' ' || world || ' !';--通过字符串链接赋值后输出
END hello_world;

举一个完整的例子：

PROCEDURE raise_budget(
department_name IN VARCHAR, 
budget_ limit IN NUMBER ) IS 

current_budget DEPARTMENT.budget%TYPE;-- current_budget 这个变量的数据类型与DEPARTMENT表中的budget类型一致
BEGIN
SELECT budget INTO current_budget FROM DEPARTMENT WHERE name = department_name;

IF current_budget < budget_limit THEN 
UPDATE DEPARTMENT SET budget := budget_limit WHERE name = department_name; 
ELSE
INSERT INTO AUDIT VALUES( 'Math budget OK', current_budget); 
END IF; 

COMMIT; 
END raise_budget;

Function的结构

结构如下：

FUNCTION function_name ( parameters ) 
RETURN type-specification IS
-- optional declarations 
BEGIN 
-- 可执行语句，至少需要一条PL/SQL语句
EXCEPTION P
-- 可选异常处理
END function_name; 

输出Hello world ! 的Function结构：

FUNCTION hello_world ( hello IN VARCHAR2,
                       world IN VARCHAR2 ) IS
RETURN VARCHAR2 IS
BEGIN
RETURN hello || ' ' || world || ' !';
END hello_world;

举一个完整的例子：

FUNCTION raise_budget( 
department_name IN VARCHAR, 
budget_ limit IN NUMBER ) 
RETURN NUMBER IS 
current_budget DEPARTMENT.budget%TYPE; 
BEGIN 
SELECT budget INTO current_budget FROM DEPARTMENT WHERE name = department_name;

IF current_budget < budget_limit THEN 
UPDATE DEPARTMENT SET budget = budget_limit WHERE name = department_name; 
RETURN budget_limit; 
ELSE 
INSERT INTO AUDIT VALUES( 'Math budget OK', current_budget);
RETURN current_budget; 
END IF; 
COMMIT; 
END raise_budget; 

我们在实际使用时要分清三种结构的区别。

References

1. T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 8 Advanced SQL, Pearson Education Ltd, 2015.