数据库编程之过程化SQL

SQL99标准支持过程化和函数的概念，SQL可以使用过程设计语言来定义过程和函数，也可以用关系数据库管理系统自己的过程语言来定义。

一、过程化SQL的块结构

基本的SQL是高度非过程化的语言。嵌入式SQL将SQL语句嵌入程序设计语言，借助高级语言的控制功能实现过程化。过程化SQL是对SQL的扩展，使其增加了过程化语句功能。
过程化SQL程序的基本结构是块。所有的过程化SQL程序都是由块组成的。这些块之间可以相互嵌套，每个块完成一个逻辑操作。

1.过程化SQL块的基本结构图示：

2. 变量和常量的定义

1. 变量定义
   变量名 数据类型 [[NOT NULL] :=初值表达式]
   或者
   变量名 数据类型 [[NOT NULL] 初值表达式]
2. 常量定义
   常量名 数据类型 CONSTANT:=常量表达式
   常量必须要给一个值，并且该值在存在期间或常量的作用域内不能改变。如果试图修改它，过程化SQL将返回一个异常。
3. 赋值语句
   变量名 :=表达式

3. 流程控制

过程化SQL提供了流程控制语句，主要有条件控制语句和循环控制语句。这些语句的语法、定义和一般的高级语言类似。
1. 条件控制语句
1)IF语句

IF  condition  THEN
    Sequence_of_statements;     /*条件为真时语句序列才被执行*/
END IF      /*条件为假或NULL时什么也不做，控制转移至下一个语句*/

2)IF-THEN语句

IF  condition  THEN
    Sequence_of_statements1;     /*条件为真时语句序列才被执行*/
ELSE
    Sequence_of_statements2;     /*条件为假或NULL时才被执行*/  
END IF

3)嵌套的IF语句
在THEN和ELSE子句中还可以包含IF语句，IF语句可以嵌套。
2. 循环控制语句
1)最简单的循环语句LOOP

LOOP
    Sequence_of_statements；    /*循环体，一组过程化SQL语句*/
END LOOP;

多数数据库服务器的过程化SQL都提供EXIT、BREAK或LEAVE等循环结束语句以保证LOOP语句块能够在适当的条件下提前结束。
2)WHERE-LOOP循环语句

WHERE condition LOOP
    Sequence_of_statements；    /*条件为真时执行循环体内的语句序列*/
END LOOP;

每次执行循环体语句之前首先对条件进行求值，如果条件为真则执行循环体内的语句序列，如果条件为假则跳过循环并把控制传递给下一个语句。
3)FOR-LOOP

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

FOR循环的基本执行过程:
将count设置为循环的下界bound1，检查它是否小于上界bound2。当指定REVERSE时则将count设置为循环的上界bound2，检查count是否大于下界bound1。如果越界则执行跳出循环，否则执行循环体，然后按照步长（+1或-1）更新count的值，重新判断条件。
3. 错误处理
如果过程化SQL在执行时出现异常，则应该让程序在产生异常的语句处停下来，根据异常的类型去执行异常处理语句。
SQL标准对数据库服务器提供什么样的异常处理作出了建议，要求过程化SQL管理器提供完善的一次处理机制。

二、存储过程和函数

过程化SQL块主要有命名块和匿名块。匿名块每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用。过程和函数是命名块，他们被编译后保存在数据库中，称为持久性存储模块（PSM），可以 被反复调用，运行速度较快。

1.存储过程

存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。
1. 存储过程的优点
1）由于存储过程不像解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作，因而运行效率高，它提供了在服务器端快速执行SQL语句的有效途径。
2）存储过程降低了客户机和服务器之间的通信量。客户机上的应用程序只要通过网络向服务器发出调用存储过程的名字和参数，就可以让关系数据库管理系统执行其中的多条SQL语句并进行数据处理，只有最终的处理结果才返回客户端。
3）方便实施企业规则。可以把企业规则的运算程序写成存储过程放入数据库服务器中，由关系数据库管理系统管理，既有利于集中控制，又能够方便地进行维护。当企业规则发生变化时只要修改存储过程即可，无需修改其他应用程序。
2. 存储过程的用户接口
1）创建存储过程

CREATE OR REPLACE PROCEDURE 过程名（[参数1，参数2,,,]）   /*存储过程首部*/
AS <过程化SQL块>  /*存储过程体，描述该存储过程的操作*/

存储过程包括过程首部和过程体，在过程首部，“过程名”是数据库服务器合法的对象标识；参数列表[参数1，参数2,,,]用名字来标识调用时给出的参数值，必须指定值的数据类型。
过程体是一个<过程化SQL块>，包括声明部分和可执行语句部分。
2）执行存储过程

CALL/PERFORM PROCEDURE 过程名（[参数1，参数2,,,]）

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

ALTER PROCEDURE 过程名1 RENAME TO 过程名2；（重新命名一个存储过程）
ALTER PROCEDURE 过程名 COMPILEL;（重新编译一个存储过程）

4）删除存储过程

DROP PROCEDURE 过程名()；

2. 函数

函数必须指定返回的类型。
1. 函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名([参数名1，参数名2，...]) RETURNS<类型> AS <过程化SQL块>

1. 函数的执行语句格式

CALL/SELECT  函数名([参数名1，参数名2，...])

1. 修改函数
   重命名一个自定义函数

ALTER FUNCTION 函数名1 RENAME TO 函数名2

重新编译一个函数

ALTER FUNCTION 函数名 COMPILE;

三、过程化SQL中的游标

和嵌入式SQL一样，在过程化SQL中如果SELECT语句只返回一条记录，可以将该结果存放到变量中。当查询返回多条记录时，就要使用游标对结果集进行处理。一个游标与一个SQL语句相关联。在存储过程中可以定义普通 游标、REFCURSOR类型游标、带参数的游标等。

存储过程和自定义函数用的比较多，因此对存储过程和函数要加深了解。