过程化SQL、存储过程与函数

过程化SQL

1. 基本结构

过程化SQL（也称PL/SQL），是对SQL的扩展。

• 基本结构是块（Block)
  • 块可以嵌套（调用）
  • 每个块完成一个逻辑操作
• 增加了变量、常量等的定义语句
• 增加了变量的赋值语句
• 增加了流程控制语句

综合实例：

2. 变量的定义

语法：

1. 变量名 数据类型[[NOT NULL]:=初值表达式]；
2. 变量名 数据类型[[NOT NULL]] 初值表达式]；

实例

DECLARE 
	total FLOAT := 0;
	intAccount INT NOT NULL;

3. 常量的定义

语法：常量名 数据类型 CONSTANT := 常量表达式；

• 常量必须要给一个值。
• 该值存在期间，或常量的作用域内不能改变。
• 如果试图改变它，过程化SQL将返回一个异常。

实例：

errorMessage string CONSTANT := "ERROR";

4. 赋值语句

语法：

1. 变量名称 := 表达式;
2. SET 变量名称 = 表达式

实例：

SET sno = son + 1;

5. 流程控制语句

5.1 条件控制语句

1. IF - THEN

IF condition THEN
	statements;
END IF;

2. IF - THEN - ELSE

IF condition THEN
	statements1;
ELSE 
	statements2;
END IF;

3. 嵌套的IF语句
   在THEN和ELSE子句中，还可以再包含IF语句， 即IF语句可以嵌套。
4. BEGIN - END
   用来设定一个程序块，将在BEGIN … END内的所有程序视为一个单元执行。
   经常在条件语句，如IF - END中使用。
   在BEGIN - END中可嵌套另外的BEGIN - END来定义另一个程序块。

BEGIN
	<命令行或程序块>
END

5. CASE

-- 格式一：
CASE <表达式>
	WHEN <表达式> THEN <表达式>
	....
	WHEN <表达式> THEN <表达式>
	[ELSE <表达式>]
END

--格式二：
CASE 
	WHEN <表达式> THEN <表达式>
	....
	WHEN <表达式> THEN <表达式>
	[ELSE <表达式>]
END	

-- 实例
USE StudentDatabase
SELECT 
	stuno AS 学号，
	cno AS 课程号,
	CASE
		WHEN totalsc >= 90 THEN "优"
		WHEN totalsc >= 80 THEN "良"
		WHEN totalsc >= 70 THEN "中"
		WHEN totalsc >= 60 THEN "及格"
		ELSE "不及格"
	END 
	AS 成绩等级
FROM SC;

5.2 循环控制语句

1. 简单的循环语句LOOP
   大多数数据库服务器的过程化SQL都提供EXIT、BREAK、或者LEAVE等循环结束语句，保证LOOP语句块能够结束。

LOOP
	statements；
END LOOP;

2. WHILE - LOOP

WHILE condition LOOP
	statements；
END LOOP;

3. FOR - LOOP

FOR count IN[REVERSE]bound1...bound2 LOOP
	statements;
END LOOP;

5.3 错误处理

• 如果过程化SQL在执行时出现异常，则应该让程序在产生异常的语句处停下来，根据异常的类型去执行异常处理语句。
• SQL标准对数据库服务器提供什么样的异常处理做出了建议，要求过程化SQL管理器，提供完善的异常处理机制。

---

存储过程

1. 定义

• 由过程化SQL 语句书写的一段程序，经编译和优化后，命名并存储在数据库服务器中，使用时只要调用即可。
• 是一组能完成特定功能的SQL语句集

---

2. 优点

1. 运行效率高
2. 降低了客户机和服务器之间的通信量
   调用存储过程时，只需一个执行语句即可实现操作，而不需要在网络上发送大量的SQL代码，减少网络流量。
3. 方便实施企业规划
4. 安全性
   可以授权给，无直接执行存储过程语句权限的用户，执行该存储过程。

---

3. 作用

• 向用户返回数据
• 向表中插入新数据
• 修改、执行系统和管理任务

---

4. 使用场景

1. 不需要任何客户端信息，而在服务器端完成的操作。
2. 涉及大量行的操作、运算
3. 频繁的、周期性的操作
4. 被多个模块共同执行的操作

---

5. 分类

在SQL server中，存储过程分为3类：系统提供的存储过程、用户自定义的存储过程以及扩展存储过程。

5.1. 系统提供的存储过程

• 主要存放在master数据库中，并以“sp_”为前缀名。
• 主要从系统表中获取信息，从而为系统管理员管理SQL server提供支持。
  通过系统存储过程， 可以执行一些操作，SQL server中的许多管理活动都是通过它完成的。
• 其他数据库可调用系统存储过程
  尽管这些存储过程被存放在master数据库中，但是仍可以在其他数据库中对其进行调用。
  当创建新数据库时，一些系统存储过程会在新数据库中被自动创建。

5.2. 用户自定义的存储过程 ★

• 由用户创建的
• 能完成某一特定功能，如查询用户所需数据信息的存储过程

5.3. 扩展存储过程

• 允许使用编程语言（如C++）创建自己的外部例程。
• 是指数据库实例可以动态加载和运行DLL。
• 直接在SQL server实例的地址空间中运行，可以使用SQL server扩展存储过程API进行编程。

---

6. 应用

6.1. 创建存储过程

语法：

CREATE PROCEDURE 过程名([参数1,参数2,....]) AS <过程化SQL语句块>

• 过程名：要创建的存储过程名称，数据库服务器合法的对象标识
  • 必须符合标识符命名规则
  • 对数据库即所有者必须唯一
  • 全局临时存储过程，名称前加“##”
  • 局部临时存储过程，名称前加“#”
• 参数列表： 用名字来标识调用时给出的参数值，必须指定值的数据类型
  参数可以定义输入参数、输出参数或输入/输出参数， 默认为输入参数。
• 过程体：是一个<过程化SQL块>， 包含声明部分和可执行语句部分。

实例：
【例1】利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

CREATE PROCEDURE transfer(inAccount INT,outAcount INT, amount FLOAT) 
AS DECLARE 
	totalOut Float;
	totalIn Float;
	inAccout INT;
BEGIN 
   SELECT Total INTO totalOut FROM Acount WHERE Acount = outAcount;
   IF totalOut IS NULL THEN -- 如果转出账户不存在或者账户中没有存款
   		ROLLBACK; --回滚事务
   		RETURN;
   END IF;
   IF totalOut < amount THEN --如果账户存款不足
   		ROLLBACK;
   		RETURN;
   END IF;
   
   UPDATE Account SET total = total - amount WHERE accout = outAcount; -- 修改转出账户余额
   UPDATE Account SET total = total + amount WHERE accout = inAcount; -- 修改转入账户余额
   
   COMMIT;
END;

6.2. 执行存储过程

使用CALL或者PERFORM等方式激活存储过程的执行。
在过程化的SQL中， 数据库服务器支持在过程体中调用其他的存储过程。

语法：

CALL PROCEDURE 过程名([参数1,参数2,....]);
PERFORM PROCEDURE 过程名([参数1,参数2,....]);

实例：
【例2】利用存储过程来实现下面的应用：从account1 转1000 到account2 中。

CALL transfer(account2,account1, 1000);

6.3. 修改存储过程

重命名

ALTER PROCEDURE name1 RENAME TO name2;

6.4. 删除存储过程

DROP PROCEDURE 过程名();

函数

1. 语法

1.1 定义

CREATE FUNCTION 函数名([参数1,参数2,....]) RETURN <类型> AS <过程化SQL语句块>

1.2 执行

CALL 函数名([参数1,参数2,....]);
SELECT 函数名([参数1,参数2,....])

1.3 修改

• 重命名

ALTER FUNCTION name1 RENAME TO name2;

• 重新编译

ALTER FUNCTION 函数名 COMPILE;

2. 函数和存储过程的异同

• 同：
  • 都是持久性存储模块
• 异：
  • 函数必须指定返回的类型