http://www.cnblogs.com/yangzhong/archive/2010/12/01/1893137.html

存储过程一般用于处理比较复杂的任务，基础ms这个平台，可以大大降低耗时，其编译机制也提高了数据库执行速度。
当然在系统控制方便方面，例如当系统进行调整时，这是只需要将后台存储过程进行更改，而不需要更改客户端程序。也无需重新安装客户端应用程序。
存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。其威力和优势主要体现在：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
（这涉及到原理性的问题，你记住就好！）
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
（尽可能少的连接数据库，可以减少时间损耗；事务方面在批量操作中非常重要，因为事务可以回溯，当出错时，可以进行回溯，保证数据的完整性！）
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
（体现在分页存储过程，以及下面这个例子：）
例子：create 
PROC [dbo].[jobs_public_select]
@TableName 
VARCHAR(2000),/*表名*/
@ParamName 
VARCHAR(2000),/*查询字段字符串*/
@ParamWhere 
NVARCHAR(2000)/*条件字符串*/
AS 
BEGIN
Declare 
@SQL varchar(500)
set @SQL='SELECT '+@ParamName+'
 from '+@TableName+' WHERE 1=1'
IF @ParamWhere<>''

BEGIN
SET @SQL=@SQL+@ParamWhere
END
exec(@SQL)
END
（这个例子主要作用就是公共查询功能，你只需要传递表名，查询的字段，条件即可。你可以以此类推，写个公共删除，公共更新的；操作无非也这几种哈。）
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
（这方面在赋权限，主要体现在，连接时采用哪个用户连接数据库，而对应的这个用户也有对应的数据库操作权限。）
优点：

1.速度快。尤其对于较为复杂的逻辑，减少了网络流量之间的消耗

我有的过程和函数达到了几百行，一个微型编译器，相信用程序就更麻烦了。
（在获取权限那个存储过程深有体会，你也可以写个C#的算法，然后与存储过程进行速度比较。）

2.写程序简单，采用存储过程调用类，调用任何存储过程都只要1-2行代码。
(我不知道别人怎么调用，我是深受其益）
3.升级、维护方便（你只需要更改存储过程就好，比如添加一个字段等）
4.调试其实也并不麻烦，可以用查询分析器（基础好，一般没有遇到很大的错误！）

5.如果把所有的数据逻辑都放在存储过程中，那么asp.net只需要负责界面的显示阿什么的，出错的可能性最大就是在存储过程。我碰到的就一般是这种情况。
（减少了排错的时间）
缺点：

1.可移植性差，我一直采用sql server开发，可是如果想卖自己的东西，发现自己简直就是在帮ms卖东西，呵呵。想换成mysql，确实移植麻烦。

2.采用存储过程调用类，需要进行两次调用操作，一次是从sql server中取到过程的参数信息，并且建立参数；第二次才是调用这个过程。多了一次消耗。

不过这个缺点可以在项目开发完成，过程参数完全确定之后，把所有过程参数信息倒入到一个xml文件中来提高性能。
当一个业务同时对多个表进行处理的时候采用存储过程比较合适。
1. 使用存储过程在一般情况下会提高性能，因为数据库优化了存储过程的数据访问计划并应用缓存方便以后的查询；
2. 存储过程单独保护存在于数据库中。客户端可以获取权限执行存储过程,而不需要对底层的具体表设置其他的访问权限；
3. 存储过程会使得维护起来更加方便，因为通常修改一个存储过程要比在一个已经发布的组件中修改SQL语句更加方便；
4. 存储过程给底层数据格式增添了额外的抽象层。使得使用存储过程的客户端对存储过程的实现细节以及对底层数据格式是隔离独立的；
5. 存储过程能够缓解网络带宽，因为可以批量执行SQL语句而不是从客户端发送超负载的请求。
复杂的数据处理用存储过程，如有些报表处理
多条件多表联合查询,并做分页处理
总结：
1． 当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
2． 当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；
3． 还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。
分页例子：
create procedure
[dbo].[sp_super_page]
@TableName 
varchar(5000),
--要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)
@Fields 
varchar(5000),
--表中的字段，可以使用*代替
@OrderField 
varchar(5000),
--要排序的字段
@sqlWhere 
varchar(5000),
--WHERE子句
@pageSize 
int,--分页的大小
@pageIndex 
int,--要显示的页的索引
@TotalPage 
int output,
--页的总数
@TotalRecords 
int output--信息总条数
as
begin
Begin Tran
Declare 
@sql nvarchar(4000);
Declare 
@totalRecord int;
--记录总数
if (@sqlWhere
IS NULL 
or @sqlWhere =
'')
--在没有WHERE子句的情况下得到表中所有的记录总数
set @sql
= 'select @totalRecord = count(*) from '
+ @TableName
else
--利用WHERE子句进行过滤
set @sql
= 'select @totalRecord = count(*) from '
+ @TableName 
+ ' where ' +
@sqlWhere
--执行sql语句得到记录总数
EXEC sp_executesql@sql,N'@totalRecord int OUTPUT',@totalRecord
OUTPUT
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
--根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序
if (@sqlWhere
IS NULL 
or @sqlWhere =
'')
set @sql
= 'select * from (select ROW_NUMBER() over(order by '
+ @OrderField 
+ ') as rowId,' +
@Fields + 
' from ' + @TableName
else
set @sql
= 'select * from (select ROW_NUMBER() over(order by '
+ @OrderField 
+ ') as rowId,' +
@Fields + 
' from ' + @TableName
+ ' where ' 
+ @SqlWhere
--确保当前页的索引在合理的范围之内
if @PageIndex<=0

Set @pageIndex
= 1
if @pageIndex>@TotalPage
Set @pageIndex
= @TotalPage

--得到当前页在整个结果集中准确的ROW_NUMBER值
Declare 
@StartRecord int
Declare 
@EndRecord int
set @StartRecord
=(@pageIndex-1)*@PageSize
+ 1
set @EndRecord
= @StartRecord 
+ @pageSize - 1
--输出当前页中的数据
set @Sql
= @Sql 
+ ') as t' +
' where rowId between '+
Convert(varchar(50),@StartRecord)
+ ' and ' 
+ Convert(varchar(50),@EndRecord)
Exec(@Sql)
If @@Error
<> 0
Begin
RollBack 
Tran
SET @TotalRecords=-1
End
Else
Begin
Commit Tran
SET @TotalRecords=@totalRecord
End 
end

MySQL中的存储过程

首先来看两个问题：

1.什么是存储过程？

存储过程(Stored Procedure)是在数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程有参数的话)来执行它。

2.为什么要使用存储过程？

《MySQL必知必会》这本书中给出了如下几条主要理由：

1）通过把处理封装在容易使用的单元中，简化复杂的操作；

2）由于不要求反复建立一系列处理步骤，这保证了数据的完整性。 如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性；

3）简化对变动的管理。如果表名、列名或业务逻辑（或别的内容） 有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会；

4）提高性能。存储过程只在创建时进行编译，以后每次执行存储过程都不需要再重新编译，而一般SQL语句每执行一次就编译一次，因此使用存储过程比使用单独的SQL语句要快；

5）存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

总的来说，使用存储过程有3个主要的好处，即简单、安全、高性能。同时，我们也要知道存储过程的一些缺陷：存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验；我们可能没有创建存储过程的安全访问权限，许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。尽管有这些缺陷，但存储过程还是非常有用的。

下面进入正题：

存储过程中变量的声明与分配

在存储过程中，我们通常用已声明的变量来保存直接/间接结果。 这些变量是存储过程中的的本地变量，只能用于存储过程中，无法在其他代码块中访问。

声明变量

要在存储过程中声明一个变量，可以使用declare语句，如下所示：

declare variable_name datatype(size) default default_value;

下面来更详细地解释上面的语句：

首先，在declare关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。

其次，指定变量的数据类型及其大小。变量可以有任何MySQL数据类型如int，varchar，datetime等。

最后，当声明一个变量时，它的初始值为null。但是可以使用default关键字为变量分配默认值。

例如，可以声明一个名为total_sale的变量，数据类型为int，默认值为0，如下所示：

declare total_sale int default 0;

MySQL允许使用单个declare语句声明共享相同数据类型的两个或多个变量，如下所示：

declare x, y int default 0;

我们声明了两个整数变量x和y，并将它们的默认值都设置为0。

分配变量值

当声明了一个变量后，就可以开始使用它了。要为变量分配一个值，可以使用set语句，例如：

declare total_count int default 0; 

set total_count = 10;

上面语句中，把10分配给变量total_count。

除了set语句之外，还可以使用select into语句将查询的结果分配给一个变量。 如下所示：

declare total_products int default 0;
select count(*) into total_products from products;

在上面的示例中：

首先，声明一个名为total_products的变量，并将其值初始化为0；

然后，使用select into语句将products表中选择的产品数量分配给total_products变量。

存储过程的三种参数

（1）IN型参数：它是默认模式。在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。 另外，IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。

tips：

1)如果你使用的是mysql命令行客户端程序，默认的MySQL语句分隔符为;如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使 存储过程中的SQL出现句法错误。解决办法是使用delimiter关键字临时更改命令行实用程序的语句分隔符。如delimiter //告诉命令行客户端程序使用//作为新的语句结束分隔符，这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用delimiter ;。除\符号外，任何字符都可以用作语句分隔符。

2）在创建一个存储过程前可以使用drop procedure if exists procedure_name语句，防止因为创建的新存储过程的名字已存在而出现错误。

示例：

建立存储过程IN_example：

delimiter //
drop procedure if exists IN_example//
create procedure IN_example (IN input_number int)
begin
set input_number = 2;
select input_number;
end//

把用户变量@input_number的初始值设为3：

set @input_number = 3//

调用存储过程IN_example,并将用户变量传入存储过程IN_example中：

call IN_example(@input_number)//

# 返回结果为：
+--------------+
| input_number |
+--------------+
|            2 |
+--------------+

select @input_number//

# 返回结果为：
+---------------+
| @input_number |
+---------------+
|             3 |
+---------------+

由上述结果可以看到，调用存储过程时，如果把用户变量@input_number作为IN型参数传给存储过程IN_example，因为IN参数在存储过程中被赋予了一个新值2，所以调用存储过程IN_example后的返回结果为2，但是用户变量@Input_number本身的值并没有改变，仍然是3。

（2）OUT型参数：可以在存储过程中更改OUT参数的值，并将其更改后的新值传递回调用程序。

示例：

建立一个求正整数算术平方根的存储过程my_sqrt：

drop procedure if exists my_sqrt//
create procedure my_sqrt(IN input_number int,OUT output_number float)
begin
set output_number = sqrt(input_number);
end//

把用户变量@number的初始值设为1：

set @number = 1//

调用存储过程my_sqrt：

# 把@number作为OUT型参数传递给存储过程my_sqrt
call my_sqrt(10,@number)//

#返回结果为空

select @number//

# 返回结果为：
+--------------------+
| @number            |
+--------------------+
| 3.1622776985168457 |
+--------------------+




由上述结果可以看到，调用存储过程后，用户变量@number的值由初始值1变成了10的算术平方根3.16227······

（3）INOUT型参数： INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，且存储过程可以修改INOUT参数并将更改后的新值传递回调用程序。

示例：

建立一个存储过程INOUT_example：

drop procedure if exists INOUT_example//
create procedure INOUT_example(INOUT number int)
begin
set number = 20;
select number;
end//


把用户变量@inout_number的初始值设为1：

set @inout_number = 1//

调用存储过程INOUT_example，并把用户变量@inout_number作为INOUT型参数传给存储过程INOUT_example：

call INOUT_example(@inout_number)//

# 返回结果为：
+--------+
| number |
+--------+
|     20 |
+--------+

select @inout_number//

# 返回结果为：
+---------------+
| @inout_number |
+---------------+
|            20 |
+---------------+


由上述结果可以看到，用户变量@inout_number作为INOUT型参数传给存储过程INOUT_example，当INOUT型参数在存储过程中被赋予了一个新值20时，用户变量@inout_number也由初始值1变成了20。此处的INOUT型参数的主要作用有两个：一是接受传入参数的值，二是保存传入的参数因在存储过程中经过一系列操作后而变成的新值。

补充说明：虽然存储过程有三种类型的参数，但是我们在创建存储过程的时候也可以没有参数，例如创建一个从商品表goods中查询所有商品的平均价格的存储过程，如下所示：

drop procedure if exists avg_price//
create avg_price()
begin
select avg(price) as avg_price from goods;
end//

返回多个值的存储过程

在创建返回多个值得存储过程之前，说明一下用到的数据表goods，它是一个商品信息记录表，详细字段如下所示：

+----------+-------------------------------+--------+----------+-----------+--------------+------------+-------------+
| goods_id | goods_name                    | cat_id | brand_id | goods_sn  | goods_number | shop_price | click_count |
+----------+-------------------------------+--------+----------+-----------+--------------+------------+-------------+
|        4 | HTCN85原装充电器              |      8 |        1 | ECS000004 |           17 |      58.00 |           0 |
|        3 | HTC原装5800耳机               |      8 |        1 | ECS000002 |           25 |      68.00 |           3 |
|        5 | 索爱原装M2卡读卡器            |     11 |        7 | ECS000005 |            8 |      20.00 |           3 |
|        6 | 胜创KINGMAX内存卡             |     11 |        0 | ECS000006 |           15 |      42.00 |           0 |
|        7 | HTCN85原装立体声耳机HS-82     |      8 |        1 | ECS000007 |           20 |     100.00 |           0 |
|        8 | 飞利浦9@9v                    |      3 |        4 | ECS000008 |           17 |     399.00 |           9 |
|        9 | HTCE66                        |      3 |        1 | ECS000009 |           13 |    2298.00 |          20 |
|       10 | 索爱C702c                     |      3 |        7 | ECS000010 |            7 |    1328.00 |          11 |
|       12 | 摩托罗拉A810                  |      3 |        2 | ECS000012 |            8 |     983.00 |          14 |
|       13 | HTC5320 XpressMusic           |      3 |        1 | ECS000013 |            8 |    1311.00 |          13 |
|       14 | HTC5800XM                     |      4 |        1 | ECS000014 |            4 |    2625.00 |           6 |
|       15 | 摩托罗拉A810                  |      3 |        2 | ECS000015 |            3 |     788.00 |           8 |
|       16 | 恒基伟业G101                  |      2 |       11 | ECS000016 |            0 |     823.33 |           3 |
|       17 | 夏新N7                        |      3 |        5 | ECS000017 |            1 |    2300.00 |           2 |
|       18 | 夏新T5                        |      4 |        5 | ECS000018 |            1 |    2878.00 |           0 |
|       19 | 三星SGH-F258                  |      3 |        6 | ECS000019 |            0 |     858.00 |           7 |
|       20 | 三星BC01                      |      3 |        6 | ECS000020 |           13 |     280.00 |          14 |
|       21 | 金立 A30                      |      3 |       10 | ECS000021 |           40 |    2000.00 |           4 |
|       22 | 多普达Touch HD                |      3 |        3 | ECS000022 |            0 |    5999.00 |          15 |
|       23 | HTCN96                        |      5 |        1 | ECS000023 |            8 |    3700.00 |          17 |
|       25 | 小灵通/固话50元充值卡         |     13 |        0 | ECS000025 |            2 |      48.00 |           0 |
|       26 | 小灵通/固话20元充值卡         |     13 |        0 | ECS000026 |            2 |      19.00 |           0 |
|       27 | 联通100元充值卡               |     15 |        0 | ECS000027 |            2 |      95.00 |           0 |
|       28 | 联通50元充值卡                |     15 |        0 | ECS000028 |            0 |      45.00 |           0 |
|       29 | 移动100元充值卡               |     14 |        0 | ECS000029 |            0 |      90.00 |           0 |
|       30 | 移动20元充值卡                |     14 |        0 | ECS000030 |            9 |      18.00 |           1 |
|       31 | 摩托罗拉E8                    |      3 |        2 | ECS000031 |            1 |    1337.00 |           5 |
|       32 | HTCN85                        |      3 |        1 | ECS000032 |            1 |    3010.00 |           9 |
+----------+-------------------------------+--------+----------+-----------+--------------+------------+-------------+

字段说明：goods_id表示商品编号，goods_name表示商品名称，cat_id表示商品所属类别编号，brand_id表示商品所属品牌编号，goods_sn表示商品货号，goods_number表示商品库存量，shop_price表示商品的销售价格，click_count表示商品的点击量。

创建一个能够返回商品表goods中所有商品的最低价格、最高价格和平均价格的存储过程：

drop procedure if exists goods_price//
create procedure goods_price(
OUT pl decimal(6,2),
OUT ph decimal(6,2),
OUT pa decimal(6,2)
)
begin
select min(shop_price) into pl from goods;
select max(shop_price) into ph from goods;
select avg(shop_price) into pa from goods;
select pl;
select ph;
select pa;
end//

# 调用存储过程goods_price:

call goods_price(@low_price,@high_price,@average_price)//

# 返回结果为：
+-------+
| pl    |
+-------+
| 18.00 |
+-------+
1 row in set (0.00 sec)

+---------+
| ph      |
+---------+
| 5999.00 |
+---------+
1 row in set (0.02 sec)

+---------+
| pa      |
+---------+
| 1197.15 |
+---------+
1 row in set (0.02 sec)


存储过程中的流程控制：

可以利用if或者case语句控制存储过程中的执行流程。

利用if语句创建一个根据金额大小判断是否打折的存储过程discounted_price：

drop procedure if exists discounted_price//
create procedure discounted_price(
IN normal_price decimal(8,2),
OUT discount_price decimal(8,2)
)
begin 
    if (normal_price>1000) then
        set discount_price = normal_price*0.8;
    elseif (normal_price>500) then
        set discount_price = normal_price*0.9;
    else
        set discount_price = normal_price;
    end if;
    select discount_price as new_price;
end//

调用discounted_price并观察其返回结果：

call discounted_price(2000,@discount_price)//

# 返回结果为：
+-----------+
| new_price |
+-----------+
|   1600.00 |
+-----------+

利用case语句创建一个根据金额大小判断是否打折的存储过程case_example：

drop procedure if exists case_example//
create procedure case_example(IN normal_price decimal(8,2),OUT discount_price decimal(8,2))
begin
    case 
        when normal_price > 1000 then
            set discount_price = normal_price * 0.8;
        when normal_price > 500 then
            set discount_price = normal_price * 0.9;
        else
            set discount_price = normal_price;
    end case;
    select discount_price as new_price;

end//

调用存储过程case_example并观察其返回结果：

call case_example(2000,@discount_price)//

# 返回结果为：
+-----------+
| new_price |
+-----------+
|   1600.00 |
+-----------+

if语句和case语句的比较：

MySQL提供if和case语句用于流程控制，使我们能够根据某些条件(称为流程控制)执行一个SQL代码块。那么我们应该使用什么语句？ 对于大多数开发人员，在if和case之间进行选择只是个人偏好的问题。但是，当决定使用if或case时，应该考虑以下几点：

1）当将单个表达式与唯一值的范围进行比较时，简单case语句比if语句更易读。另外，简单case语句比if语句更有效率。

关于存储过程中case语句的基本用法可以参考：https://blog.csdn.net/qq_41080850/article/details/84851263

2）当根据多个值检查复杂表达式时，if语句更容易理解。

3）如果选择使用case语句，则必须确保至少有一个case条件匹配。否则，需要定义一个错误处理程序来捕获错误。if语句则不需要处理错误。关于存储过程中的错误处理，可以参考：

https://www.yiibai.com/mysql/error-handling-in-stored-procedures.html、

https://www.cnblogs.com/shijiaqi1066/p/3435037.html

4）在某些情况下，if和case混合使用反而使存储过程更加可读和高效。

存储过程中的循环

loop循环

有两个语句可以用于控制loop循环：

1）leave语句用于立即退出循环，而无需等待检查条件。leave语句的工作原理类似于C/C++，java等语言中的break语句。

2）iterate语句允许跳过剩下的整个代码并开始新的迭代。iterate语句类似C/C++，Java等语言中的continue语句。

示例1：

创建存储过程loop_example1，它可以用于条件计数：

drop procedure if exists loop_example1//
create procedure loop_example1()
begin
    declare counter int default 0;
    my_loop:loop
        if counter < 10 then
            set counter = counter + 1;
        else
            leave my_loop;
        end if;
    end loop my_loop;
    select counter;
end//

调用存储过程loop_example1并观察其返回结果：

call loop_example1()//

# 返回结果为
+---------+
| counter |
+---------+
|      10 |
+---------+

示例2：

创建存储过程loop_example2，它的功能是找出20以内不为0的偶数：

drop procedure if exists loop_example2//
create procedure loop_example2()
begin
    declare x int default 0;
    declare strings varchar(100) default '';
    my_loop:loop
        if x > 20 then
            leave my_loop;
        end if;
        set x = x + 1;
        if mod(x,2) then
            iterate my_loop;
        else
            set strings = concat(strings,x,',');
            iterate my_loop;
        end if;
    end loop;
    select strings;
end//

调用存储过程loop_example2并观察其返回结果：

call loop_example2()//

# 返回结果为：
+----------------------------+
| strings                    |
+----------------------------+
| 2,4,6,8,10,12,14,16,18,20, |
+----------------------------+

while循环

while语句的语法如下：

while expression do
    statements; 
end while

while循环在每次迭代开始时检查表达式。 如果expression为True，MySQL将执行while和end while之间的语句，直到expression为false。 while循环称为预先测试条件循环，因为它总是在执行前检查语句的表达式。

示例：

创建存储过程while_example，它也可以用于条件计数：

drop procedure if exists while_example//
create procedure while_example()
begin
    declare counter int default 0;
    while counter < 10 do
        set counter = counter + 1;
    end while;
    select counter;
end//

调用存储过程while_example并观察其返回结果：

call while_example()//

# 返回结果为：
+---------+
| counter |
+---------+
|      10 |
+---------+



repeat循环

repeat循环语句的语法如下：

repeat 
    statements;  
until expression  # 注意until语句后面是没有标点符号的 
end repeat

首先，MySQL执行语句statements，然后评估求值表达式expression。如果表达式expression的计算结果为false，则MySQL将重复执行该语句，直到该表达式计算结果为True。因为repeat循环语句在执行语句后检查表达式expression，因此repeat循环语句也称为测试后循环。

示例：

创建存储过程repeat_example，它也可以用于条件计数：

drop procedure if exists repeat_example//
create procedure repeat_example()
begin
    declare counter int default 0;
    repeat
        set counter = counter + 1;
    until counter > 10
    end repeat;
    select counter;
end//

调用存储过程repeat_example并观察其返回结果：

call repeat_example()//

# 返回结果为：
+---------+
| counter |
+---------+
|      11 |
+---------+


MySQL中的游标

说明：不像其他DBMS，MySQL中的游标只能用于存储过程和存储函数。

关于MySQL游标的理解：

MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象)，它可以用来存储select 语句查询到的结果集，这个结果集可以包含多行数据，从而使我们可以使用迭代的方法从游标中依次取出每行数据。

MySQL游标的特点：

1）只读：无法通过光标更新基础表中的数据。

2）不可滚动：只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。 此外，不能跳过行或跳转到结果集中的特定行。

3）敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。MySQL游标是敏感的。

MySQL游标的使用方法：

首先，必须使用declare语句声明游标：

declare cursor_name cursor for select_statement;

游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。游标必须始终与select语句相关联。

接下来，使用open语句打开游标。open语句初始化游标的结果集，因此必须在从结果集中提取行之前调用open语句。

open cursor_name;


然后，使用fetch语句来检索游标指向的一行数据，并将游标移动到结果集中的下一行。

fetch cursor_name into variable_name;

之后，可以检查是否有任何行记录可用，然后再提取它。

最后，调用close语句来停用游标并释放与之关联的内存，如下所示：

close cursor_name

当游标不再使用时，应该关闭它。

当使用MySQL游标时，还必须声明一个not found处理程序来处理当游标找不到任何行时的情况。 因为每次调用fetch语句时，游标会尝试依次读取结果集中的每一行数据。 当游标到达结果集的末尾时，它将无法获得数据，并且会产生一个条件。 处理程序用于处理这种情况。

要声明一个not found处理程序，参考以下语法：

declare continue handler for not found set finished = 1;

finished是一个变量，指示游标到达结果集的结尾。注意，处理程序声明必须出现在存储过程中的变量和游标声明之后。

示例：

# 先创建一个根据商品名称获取商品价格的存储过程get_shop_price

drop procedure if exists get_shop_price//

create procedure get_shop_price(IN name varchar(20),OUT price decimal(6,2))
begin
select shop_price into price from goods where goods_name = name;
end//

# 再创建一个获取商品表goods中所有价格大于指定值的商品名称和价格，并把结果存入一张新建的goodsnames表中

drop procedure if exists build_goodsname_list//

create procedure build_goodsname_list(IN input_price decimal(6,2))
begin
    declare finished int default 0;
    declare name varchar(20) default '';
    declare price decimal(6,2) default 0;

    -- declare the cursor
    declare goods_cursor cursor for select goods_name from goods where shop_price>input_price;

    -- declare continue handler
    declare continue handler for not found set finished = 1;

    -- drop table goodsnames
    drop table goodsnames;

    -- create a table to store the results
    create table goodsnames(
    goods_name varchar(20) not null default '',
    shop_price decimal(6,2) not null default 0
    )engine myisam charset utf8;

    -- open the cursor
    open goods_cursor;

    -- fetch all rows in the cursor
    repeat
        -- get goods_name
        fetch goods_cursor into name;
    
        if not finished then

        -- get the shop_price for this goods_name
        call get_shop_price(name,price);

        -- insert name and price into goodsnames
        insert into goodsnames values(name,price);

        end if;

    -- end of repeat loop
    until finished
    end repeat;

    -- close the cursor
    close goods_cursor;
    
    select * from goodsnames;
end//

调用存储过程build_goodsname_list，并观察其返回结果：

call build_goodsname_list(1000)//

# 返回结果为：
+--------------------------+------------+
| goods_name               | shop_price |
+--------------------------+------------+
| HTCE66                   |    2298.00 |
| 索爱C702c                |    1328.00 |
| HTC5320 XpressMusic      |    1311.00 |
| HTC5800XM                |    2625.00 |
| 夏新N7                   |    2300.00 |
| 夏新T5                   |    2878.00 |
| 金立 A30                 |    2000.00 |
| 多普达Touch HD           |    5999.00 |
| HTCN96                   |    3700.00 |
| 摩托罗拉E8               |    1337.00 |
| HTCN85                   |    3010.00 |
+--------------------------+------------+


call build_goodsname_list(2000)//

# 返回结果为：
+-------------------------+------------+
| goods_name              | shop_price |
+-------------------------+------------+
| HTCE66                  |    2298.00 |
| HTC5800XM               |    2625.00 |
| 夏新N7                  |    2300.00 |
| 夏新T5                  |    2878.00 |
| 多普达Touch HD          |    5999.00 |
| HTCN96                  |    3700.00 |
| HTCN85                  |    3010.00 |
+-------------------------+------------+

MySQL中的存储函数

存储的函数是返回单个值的特殊类型的存储程序。我们使用存储的函数来封装在SQL语句或存储的程序中可重用的常用公式或业务规则。

与存储过程不同，我们可以在SQL语句中使用存储的函数，也可以在表达式中使用。 这有助于提高程序代码的可读性和可维护性。

存储函数语法

create function function_name(param1,param2,…)
    returns datatype
   [not] deterministic
 statements;

上述代码的详细解释：

首先，在create function语句之后指定存储函数的名称。

其次，列出括号内存储函数的所有参数。 默认情况下，所有参数均为IN参数。不能为参数指定IN，OUT或INOUT修饰符。

第三，必须在returns语句中指定返回值的数据类型。它可以是任何有效的MySQL数据类型。

第四，对于相同的输入参数，如果存储的函数返回相同的结果，这样则被认为是确定性的，否则存储的函数不是确定性的。必须决定一个存储函数是否是确定性的。 如果声明不正确，则存储的函数可能会产生意想不到的结果，或者不能使用可用的优化，从而降低性能。

第五，将代码写入存储函数的主体中。 它可以是单个语句或复合语句。 在主体部分中，必须至少指定一个return语句。return语句用于返回一个值给调用者。 每当到达return语句时，存储函数的执行将立即终止。

 示例：

创建一个存储函数goods_level，它的功能是根据给定的商品价格确定商品所属的价格分类：

drop function if exists goods_level//

create function goods_level(p_shop_price decimal(6,2))
    returns varchar(20)
    deterministic
begin
    declare g_level varchar(20);
    if p_shop_price > 2000 then
        set g_level = 'high_level';
    elseif (p_shop_price>=1000 and p_shop_price<=2000) then
        set g_level = 'middle_level';
    else
        set g_level = 'low_level';
    end if;
    return (g_level);
end//

在select语句中调用goods_level函数：

select goods_id,goods_name,shop_price,goods_level(shop_price) as price_level from goods//

# 返回结果为：
+----------+-------------------------------+------------+--------------+
| goods_id | goods_name                    | shop_price | price_level  |
+----------+-------------------------------+------------+--------------+
|        4 | HTCN85原装充电器              |      58.00 | low_level    |
|        3 | HTC原装5800耳机               |      68.00 | low_level    |
|        5 | 索爱原装M2卡读卡器            |      20.00 | low_level    |
|        6 | 胜创KINGMAX内存卡             |      42.00 | low_level    |
|        7 | HTCN85原装立体声耳机HS-82     |     100.00 | low_level    |
|        8 | 飞利浦9@9v                    |     399.00 | low_level    |
|        9 | HTCE66                        |    2298.00 | high_level   |
|       10 | 索爱C702c                     |    1328.00 | middle_level |
|       12 | 摩托罗拉A810                  |     983.00 | low_level    |
|       13 | HTC5320 XpressMusic           |    1311.00 | middle_level |
|       14 | HTC5800XM                     |    2625.00 | high_level   |
|       15 | 摩托罗拉A810                  |     788.00 | low_level    |
|       16 | 恒基伟业G101                  |     823.33 | low_level    |
|       17 | 夏新N7                        |    2300.00 | high_level   |
|       18 | 夏新T5                        |    2878.00 | high_level   |
|       19 | 三星SGH-F258                  |     858.00 | low_level    |
|       20 | 三星BC01                      |     280.00 | low_level    |
|       21 | 金立 A30                      |    2000.00 | middle_level |
|       22 | 多普达Touch HD                |    5999.00 | high_level   |
|       23 | HTCN96                        |    3700.00 | high_level   |
|       25 | 小灵通/固话50元充值卡         |      48.00 | low_level    |
|       26 | 小灵通/固话20元充值卡         |      19.00 | low_level    |
|       27 | 联通100元充值卡               |      95.00 | low_level    |
|       28 | 联通50元充值卡                |      45.00 | low_level    |
|       29 | 移动100元充值卡               |      90.00 | low_level    |
|       30 | 移动20元充值卡                |      18.00 | low_level    |
|       31 | 摩托罗拉E8                    |    1337.00 | middle_level |
|       32 | HTCN85                        |    3010.00 | high_level   |
+----------+-------------------------------+------------+--------------+


建立存储过程get_price_level，在存储过程中调用存储函数goods_level，使得存储过程get_goods_level能根据给定的goods_id确定对应商品的价格所属的分类：

drop procedure if exists get_price_level//

create procedure get_price_level(IN g_goods_id int,OUT g_price_level varchar(20))
begin
    declare g_shop_price decimal(6,2);
    select shop_price into g_shop_price from goods where goods_id = g_goods_id;
    select goods_level(g_shop_price) into g_price_level;
    select g_price_level as price_level;
end//

测试存储过程get_price_level：

call get_price_level(3,@price_level)//

# 返回结果为：
+-------------+
| price_level |
+-------------+
| low_level   |
+-------------+


call get_price_level(9,@price_level)//

# 返回结果为：
+-------------+
| price_level |
+-------------+
| high_level  |
+-------------+


call get_price_level(21,@price_level)//

# 返回结果为：
+--------------+
| price_level  |
+--------------+
| middle_level |
+--------------+

# 将以上测试结果与前文在select语句中调用goods_level后的返回结果比较可知测试结果是正确的。

存储过程与存储函数的比较

可以参考：https://blog.csdn.net/qq_32444825/article/details/79170109

 

其他参考：

https://www.cnblogs.com/gavin110-lgy/p/5772577.html

https://blog.csdn.net/JQ_AK47/article/details/52087484

https://blog.csdn.net/myweishanli/article/details/41245923

https://www.yiibai.com/mysql/stored-procedure.html#article-start

https://blog.csdn.net/Maple1997/article/details/79390797

《MySQL必知必会》——Ben·Forta

 

 

PostgreSQL存储过程

PostgreSQL存储过程几年前写过很多，但是几年不碰又陌生了，今天给客户写了一个存储过程，查了一些资料，记录一下：
–创建测试表

create table student (id integer, name varchar(64));

create table employees (id integer, age integer);
–table_new 需要在外部创建

create table table_new (id integer, name varchar(64), age integer);
–插入测试数据

insert into student select generate_series(1, 100), ‘lili_’ || cast(random()*100 as varchar(2));

insert into employees select generate_series(1, 50), random()*100;
注：generate_series函数：

http://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html
select count() from student;

select count() from employees;
–存储过程

create or replace function P_DWA_ERP_LEDGER_JQ_MONTH_NEW( v_mouth varchar(8),  out v_retcode text,  out v_retinfo text,  out v_row_num integer)

AS

&BODY&

declare

begin

insert into table_new(id, name, age) select t.id, t.name, m.age from student t, employees m where t.id=m.id;
GET DIAGNOSTICS V_ROW_NUM := ROW_COUNT;

-- 执行成功后的返回信息
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';

--异常处理
EXCEPTION
WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;

end;
$BODY$

language plpgsql;
–调用存储过程

select * from P_DWA_ERP_LEDGER_JQ_MONTH_NEW(‘12’);
–查看结果

select count(*) from table_new;

delete from table_new;

vacuum  table_new;
create or replace function test() returns integer

AS

$BODY$

declare

v_mouth varchar(8);

v_retcode text;

v_retinfo text;

v_row_num integer;

begin

v_mouth := 12;

select * from P_DWA_ERP_LEDGER_JQ_MONTH_NEW(v_mouth) into v_retcode, v_retinfo, v_row_num;

raise notice ‘P_DWA_ERP_LEDGER_JQ_MONTH_NEW result is: %, %, %, %’, v_mouth, v_retcode, v_retinfo, v_row_num;

return 0;

end;
$BODY$

language plpgsql;
select test();
一、写法示例
http://panyongzheng.iteye.com/blog/2194815
PostgreSQL的存储过程简单入门 http://blog.csdn.net/rachel_luo/article/details/8073458

存储过程事物 http://www.php100.com/manual/PostgreSQL8/tutorial-transactions.html

PL/pgSQL - SQL存储过程语言 https://wiki.postgresql.org/wiki/9.1第三十九章
postgreSQL存储过程写法示例http://blog.sina.com.cn/s/blog_448574810101f64u.html

结构

PL/pgSQL是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。基本上是这样的
二、处理异常
转自：https://www.cnblogs.com/lottu/p/7410978.html

异常错误处理

　　在PL/pgSQL函数中，如果没有异常捕获，函数会在发生错误时直接退出，与其相关的事物也会随之回滚。我们可以通过使用带有EXCEPTION子句的BEGIN块来捕获异常并使其从中恢复。见如下声明形式：

[ <> ]

[ DECLARE

declarations ]

BEGIN

statements

EXCEPTION

　　WHEN condition [ OR condition … ] THEN

handler_statements

　　WHEN condition [ OR condition … ] THEN

　　       handler_statements

END;
如果没有错误发生，只有BEGIN块中的statements会被正常执行，然而一旦这些语句中有任意一条发生错误，其后的语句都将被跳过，直接跳转到 EXCEPTION块的开始处。此时系统将搜索异常条件列表，寻找匹配该异常的第一个条件，如果找到匹配，则执行相应的 handler_statements，之后再执行END的下一条语句。如果没有找到匹配，该错误就会被继续向外抛出，其结果与没有EXCEPTION子 句完全等同。如果此时handler_statements中的语句发生新错误，它将不能被该EXCEPTION子句捕获，而是继续向外传播，交由其外层 的EXCEPTION子句捕获并处理。
在PostgreSQL中可以利用RAISE语句报告信息和抛出错误，其声明形式为：
RAISE level ‘format’ [, expression [, …]];

这里包含的级别有DEBUG(向服务器日志写信息)、LOG(向服务器日志写信息，优先级更高)、INFO、NOTICE和WARNING(把信息写到服务器日志以及转发到客户端应用，优先级逐步升高)和EXCEPTION抛出一个错误(通常退出当前事务)。某个优先级别的信息是报告给客户端还是写到服务器日志，还是两个均有，是由log_min_messages和client_min_messages这两个系统初始化参数控制的。

在format部分中，%表示为占位符，其实际值仅在RAISE命令执行时由后面的变量替换，如果要在format中表示%自身，可以使用%%的形式表示，见如下示例：

RAISE NOTICE ‘Calling cs_create_job(%)’,v_job_id;–v_job_id变量的值将替换format中的%。

RAISE EXCEPTION ‘Inexistent ID --> %’,user_id;
简单来说：
–抛出异常

RAISE EXCEPTION ‘你出问题了。该修修！’;

–使用SQLERRM 来显示错误信息。

RAISE EXCEPTION ‘(%)’, SQLERRM;

见如下示例：
CREATE OR REPLACE FUNCTION GETEXCEPTION(v_phone text) RETURNS void AS

$BEGIN
    IF v_phone = &#x27;iphone&#x27; THEN
       RAISE EXCEPTION &#x27;你出问题了。该修修！&#x27;; 
    ELSIF  v_phone = &#x27;samsung&#x27; THEN  
       RAISE EXCEPTION &#x27;你会爆炸，离你远点！&#x27;; 
    else 
       RETURN;    
    END IF;
    EXCEPTION
    WHEN others THEN    
    RAISE EXCEPTION &#x27;(END$ LANGUAGE PLPGSQL;
参考文献：http://www.postgres.cn/docs/9.3/plpgsql-errors-and-messages.html
三、里面有如何调用一个有out参数的存储过程
原创文章，转载请务必将下面这段话置于文章开头处（保留超链接）。
本文转发自Jason’s Blog，原文链接　http://www.jasongj.com/2015/12/27/SQL4_存储过程_Store Procedure/
存储过程简介

什么是存储过程

　　百度百科是这么描述存储过程的：存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，首次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果有）来执行它。它是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

　　

　　维基百科是这样定义的：A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDMS). Such procedures are stored in the database data dictionary。
PostgreSQL对存储过程的描述是：存储过程和用户自定义函数（UDF）是SQL和过程语句的集合，它存储于数据库服务器并能被SQL接口调用。
总结下来存储过程有如下特性：
存储于数据库服务器

一次编译后可多次调用

设计良好的数据库应用程序很可能会用到它

由SQL和过程语句来定义

应用程序通过SQL接口来调用

使用存储过程的优势及劣势

　　首先看看使用存储过程的优势
减少应用与数据库服务器的通信开销，从而提升整体性能。笔者在项目中使用的存储过程，少则几十行，多则几百行甚至上千行（假设一行10个字节，一千行即相当于10KB），如果不使用存储过程而直接通过应用程序将相应SQL请求发送到数据库服务器，会增大网络通信开销。相反，使用存储过程能降低该开销，从而提升整体性能。尤其在一些BI系统中，一个页面往往要使用多个存储过程，此时存储过程降低网络通信开销的优势非常明显

一次编译多次调用，提高性能。存储过程存于数据库服务器中，第一次被调用后即被编译，之后再调用时无需再次编译，直接执行，提高了性能

同一套业务逻辑可被不同应用程序共用，减少了应用程序的开发复杂度，同时也保证了不同应用程序使用的一致性

保护数据库元信息。如果应用程序直接使用SQL语句查询数据库，会将数据库表结构暴露给应用程序，而使用存储过程是应用程序并不知道数据库表结构

更细粒度的数据库权限管理。直接从表读取数据时，对应用程序只能实现表级别的权限管理，而使用存储过程是，可在存储过程中将应用程序无权访问的数据屏蔽

将业务实现与应用程序解耦。当业务需求更新时，只需更改存储过程的定义，而不需要更改应用程序

可以通过其它语言并可及其它系统交互。比如可以使用PL/Java与Kafka交互，将存储过程的参数Push到Kafka或者将从Kafka获取的数据作为存储过程的结果返回给调用方

　　当然，使用存储过程也有它的劣势
不便于调试。尤其在做性能调优时，以PostgreSQL为例，可使用EXPLAIN ANALYZE检查SQL查询计划，从而方便的进行性能调优。而使用存储过程时，EXPLAIN ANALYZE无法显示其内部查询计划

不便于移植到其它数据库。直接使用SQL时，SQL存于应用程序中，对大部分标准SQL而言，换用其它数据库并不影响应用程序的使用。而使用存储过程时，由于不同数据库的存储过程定义方式不同，支持的语言及语法不同，移植成本较高

存储过程在PostgreSQL中的使用

PostgreSQL支持的过程语言

　　PostgreSQL官方支持PL/pgSQL，PL/Tcl，PL/Perl和PL/Python这几种过程语言。同时还支持一些第三方提供的过程语言，如PL/Java，PL/PHP，PL/Py，PL/R，PL/Ruby，PL/Scheme，PL/sh。
基于SQL的存储过程定义

CREATE OR REPLACE FUNCTION add(a INTEGER, b NUMERIC)

RETURNS NUMERIC

AS $SELECT a+b;$ LANGUAGE SQL;

　　调用方法
SELECT add(1,2);

add
3

(1 row)
SELECT * FROM add(1,2);

add
3

(1 row)
上面这种方式参数列表只包含函数输入参数，不包含输出参数。下面这个例子将同时包含输入参数和输出参数
CREATE OR REPLACE FUNCTION plus_and_minus

(IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)

AS $SELECT a+b, a-b;$ LANGUAGE SQL;

　调用方式
SELECT plus_and_minus(3,2);

add_and_minute
(5,1)

(1 row)
SELECT * FROM plus_and_minus(3,2);

c | d

—±–

5 | 1

(1 row)
该例中，IN代表输入参数，OUT代表输出参数。这个带输出参数的函数和之前的add函数并无本质区别。事实上，输出参数的最大价值在于它为函数提供了返回多个字段的途径。
在函数定义中，可以写多个SQL语句，不一定是SELECT语句，可以是其它任意合法的SQL。但最后一条SQL必须是SELECT语句，并且该SQL的结果将作为该函数的输出结果。
CREATE OR REPLACE FUNCTION plus_and_minus

(IN a INTEGER, IN b NUMERIC, OUT c NUMERIC, OUT d NUMERIC)

AS $SELECT a+b, a-b;
    INSERT INTO test VALUES(&#x27;test1&#x27;);
    SELECT a-b, a+b;$ LANGUAGE SQL;
其效果如下
SELECT * FROM plus_and_minus(5,3);

c | d

—±–

2 | 8

(1 row)
SELECT * FROM test;

a
test1

(1 row)
基于PL/PgSQL的存储过程定义
PL/pgSQL是一个块结构语言。函数定义的所有文本都必须是一个块。一个块用下面的方法定义：
[ <> ]

[DECLARE

declarations]

BEGIN

statements

END [ label ];
中括号部分为可选部分
块中的每一个declaration和每一条statement都由一个分号终止

块支持嵌套，嵌套时子块的END后面必须跟一个分号，最外层的块END后可不跟分号

BEGIN后面不必也不能跟分号

END后跟的label名必须和块开始时的标签名一致

所有关键字都不区分大小写。标识符被隐含地转换成小写字符，除非被双引号包围

声明的变量在当前块及其子块中有效，子块开始前可声明并覆盖（只在子块内覆盖）外部块的同名变量

变量被子块中声明的变量覆盖时，子块可以通过外部块的label访问外部块的变量

　　声明一个变量的语法如下：
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];
使用PL/PgSQL语言的函数定义如下：
CREATE FUNCTION somefunc() RETURNS integer AS $$

DECLARE

quantity integer := 30;

BEGIN

– Prints 30

RAISE NOTICE ‘Quantity here is %’, quantity;

quantity := 50;
-- Create a subblock
DECLARE
    quantity integer := 80;
BEGIN
    -- Prints 80
    RAISE NOTICE 'Quantity here is %', quantity;
    -- Prints 50
    RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;
END;

-- Prints 50
RAISE NOTICE 'Quantity here is %', quantity;
RETURN quantity;

END;

$$ LANGUAGE plpgsql;
声明函数参数
如果只指定输入参数类型，不指定参数名，则函数体里一般用$1，$n这样的标识符来使用参数。
CREATE OR REPLACE FUNCTION discount(NUMERIC)

RETURNS NUMERIC

AS KaTeX parse error: Can't use function '$' in math mode at position 19: …GIN
    RETURN $̲1 * 0.8;
END;
 LANGUAGE PLPGSQL;
但该方法可读性不好，此时可以为$n参数声明别名，然后可以在函数体内通过别名指向该参数值。
CREATE OR REPLACE FUNCTION discount(NUMERIC)

RETURNS NUMERIC

AS KaTeX parse error: Can't use function '$' in math mode at position 30: …otal ALIAS FOR $̲1;
BEGIN
    RE… LANGUAGE PLPGSQL;
笔者认为上述方法仍然不够直观，也不够完美。幸好PostgreSQL提供另外一种更为直接的方法来声明函数参数，即在声明参数类型时同时声明相应的参数名。
CREATE OR REPLACE FUNCTION discount(total NUMERIC)

RETURNS NUMERIC

AS $BEGIN
    RETURN total * 0.8;
END;$ LANGUAGE PLPGSQL;
返回多行或多列
使用自定义复合类型返回一行多列

　　PostgreSQL除了支持自带的类型外，还支持用户创建自定义类型。在这里可以自定义一个复合类型，并在函数中返回一个该复合类型的值，从而实现返回一行多列。
CREATE TYPE compfoo AS (col1 INTEGER, col2 TEXT);
CREATE OR REPLACE FUNCTION getCompFoo

(in_col1 INTEGER, in_col2 TEXT)

RETURNS compfoo

AS $DECLARE result compfoo;
BEGIN
    result.col1 := in_col1 * 2;
    result.col2 := in_col2 || &#x27;_result&#x27;;
    RETURN result;
END;$ LANGUAGE PLPGSQL;
SELECT * FROM getCompFoo(1,‘1’);

col1 |   col2

------±---------

2 | 1_result

(1 row)
使用输出参数名返回一行多列
在声明函数时，除指定输入参数名及类型外，还可同时声明输出参数类型及参数名。此时函数可以输出一行多列。
CREATE OR REPLACE FUNCTION get2Col

(IN in_col1 INTEGER,IN in_col2 TEXT,

OUT out_col1 INTEGER, OUT out_col2 TEXT)

AS $BEGIN
    out_col1 := in_col1 * 2;
    out_col2 := in_col2 || &#x27;_result&#x27;;
END;$ LANGUAGE PLPGSQL;
SELECT * FROM get2Col(1,‘1’);

out_col1 | out_col2

----------±---------

2 | 1_result

(1 row)
实际项目中，存储过程经常需要返回多行记录，可以通过SETOF实现。使用SETOF返回多行记录
CREATE TYPE compfoo AS (col1 INTEGER, col2 TEXT);
CREATE OR REPLACE FUNCTION getSet(rows INTEGER)

RETURNS SETOF compfoo

AS $BEGIN
    RETURN QUERY SELECT i * 2, i || &#x27;_text&#x27; 
    FROM generate_series(1, rows, 1) as t(i);
END;$ LANGUAGE PLPGSQL;
SELECT col1, col2 FROM getSet(2);

col1 |  col2

------±-------

2 | 1_text

4 | 2_text

(2 rows)
本例返回的每一行记录是复合类型，该方法也可返回基本类型的结果集，即多行一列。
使用RETURN TABLE返回多行多列
CREATE OR REPLACE FUNCTION getTable(rows INTEGER)

RETURNS TABLE(col1 INTEGER, col2 TEXT)

AS $BEGIN
    RETURN QUERY SELECT i * 2, i || &#x27;_text&#x27;
    FROM generate_series(1, rows, 1) as t(i);
END;$ LANGUAGE PLPGSQL;
SELECT col1, col2 FROM getTable(2);

col1 |  col2

------±-------

2 | 1_text

4 | 2_text

(2 rows)
使用EXECUTE语句执行动态命令　　此时从函数中读取字段就和从表或视图中取字段一样，可以看此种类型的函数看成是带参数的表或者视图。
有时在PL/pgSQL函数中需要生成动态命令，这个命令将包括他们每次执行时使用不同的表或者字符。EXECUTE语句用法如下：
EXECUTE command-string [ INTO [STRICT] target] [USING expression [, …]];

　　此时PL/plSQL将不再缓存该命令的执行计划。相反，在该语句每次被执行的时候，命令都会编译一次。这也让该语句获得了对各种不同的字段甚至表进行操作的能力。
command-string包含了要执行的命令，它可以使用参数值，在命令中通过引用如$1，$2等来引用参数值。这些符号的值是指USING字句的值。这种方法对于在命令字符串中使用参数是最好的：它能避免运行时数值从文本来回转换，并且不容易产生SQL注入，而且它不需要引用或者转义。
CREATE TABLE testExecute

AS

SELECT

i || ‘’ AS a,

i AS b

FROM

generate_series(1, 10, 1) AS t(i);
CREATE OR REPLACE FUNCTION execute(filter TEXT)

RETURNS TABLE (a TEXT, b INTEGER)

AS KaTeX parse error: Can't use function '$' in math mode at position 78: …cute where a = $̲1'
    USING fi… LANGUAGE PLPGSQL;
SELECT * FROM execute(‘3’);

a | b

—±–

3 | 3

(1 row)
SELECT * FROM execute(‘3’’ or ‘‘c’’=’‘c’);

a | b

—±–

(0 rows)
当然，也可以使用字符串拼接的方式在command-string中使用参数，但会有SQL注入的风险。
CREATE TABLE testExecute

AS

SELECT

i || ‘’ AS a,

i AS b

FROM

generate_series(1, 10, 1) AS t(i);
CREATE OR REPLACE FUNCTION execute(filter TEXT)

RETURNS TABLE (a TEXT, b INTEGER)

AS $BEGIN
    RETURN QUERY EXECUTE
        &#x27;SELECT * FROM testExecute where b = &#x27;&#x27;&#x27;
        || filter || &#x27;&#x27;&#x27;&#x27;;
END;$ LANGUAGE PLPGSQL;
SELECT * FROM execute(3);

a | b

—±–

3 | 3

(1 row)
SELECT * FROM execute(‘3’’ or ‘‘c’’=’‘c’);

a  | b

----±—

1  |  1

2  |  2

3  |  3

4  |  4

5  |  5

6  |  6

7  |  7

8  |  8

9  |  9

10 | 10

(10 rows)
从该例中可以看出使用字符串拼接的方式在command-string中使用参数会引入SQL注入攻击的风险，而使用USING的方式则能有效避免这一风险。
PostgreSQL中的UDF与存储过程

　　本文中并未区分PostgreSQL中的UDF和存储过程。实际上PostgreSQL创建存储与创建UDF的方式一样，并没有专用于创建存储过程的语法，如CREATE PRECEDURE。在PostgreSQL官方文档中也暂未找到这二者的区别。倒是从一些资料中找对了它们的对比，如下表如示，仅供参考。

UDF VS. Stored Precedure
多态SQL函数

　　SQL函数可以声明为接受多态类型（anyelement和anyarray）的参数或返回多态类型的返回值。
函数参数和返回值均为多态类型。其调用方式和调用其它类型的SQL函数完全相同，只是在传递字符串类型的参数时，需要显示转换到目标类型，否则将会被视为unknown类型。
CREATE OR REPLACE FUNCTION get_array(anyelement, anyelement)

RETURNS anyarray

AS KaTeX parse error: Can't use function '$' in math mode at position 19: …  SELECT ARRAY[$̲1, $2];
 LANGUAGE SQL;
SELECT get_array(1,2), get_array(‘a’::text,‘b’::text);

get_array | get_array

-----------±----------

{1,2}     | {a,b}

(1 row)
函数参数为多态类型，而返回值为基本类型
CREATE OR REPLACE FUNCTION is_greater(anyelement, anyelement)

RETURNS BOOLEAN

AS KaTeX parse error: Can't use function '$' in math mode at position 13: 
    SELECT $̲1 > $2;
 LANGUAGE SQL;
SELECT is_greater(7.0, 4.5);

is_greater
t

(1 row)
SELECT is_greater(2, 4);

is_greater
f

(1 row)
输入输出参数均为多态类型。这种情况与第一种情况一样。
CREATE OR REPLACE FUNCTION get_array

(IN anyelement, IN anyelement, OUT anyelement, OUT anyarray)

AS KaTeX parse error: Can't use function '$' in math mode at position 13: 
    SELECT $̲1, ARRAY[$1, $2… LANGUAGE SQL;
SELECT get_array(4,5), get_array(‘c’::text, ‘d’::text);

get_array  |  get_array

-------------±------------

(4,"{4,5}") | (c,"{c,d}")

(1 row)
函数重载（Overwrite）

　　在PostgreSQL中，多个函数可共用同一个函数名，但它们的参数必须得不同。这一规则与面向对象语言（比如Java）中的函数重载类似。也正因如此，在PostgreSQL删除函数时，必须指定其参数列表，如：
1

DROP FUNCTION get_array(anyelement, anyelement);
另外，在实际项目中，经常会用到CREATE OR REPLACE FUNCTION去替换已有的函数实现。如果同名函数已存在，但输入参数列表不同，会创建同名的函数，也即重载。如果同名函数已存在，且输入输出参数列表均相同，则替换。如果已有的函数输入参数列表相同，但输出参数列表不同，则会报错，并提示需要先DROP已有的函数定义。

SQL Server 2008中SQL应用系列--目录索引
最近对SQL Server 2008的安全入门略作小结，以作备忘。本文涉及两个应用：存储过程加密和安全上下文。
<一>存储过程加密
其实，用了这十多年的SQL server，我已经成了存储过程的忠实拥趸。在直接使用SQL语句还是存储过程来处理业务逻辑时，我基本会毫不犹豫地选择后者。
理由如下：
1、使用存储过程，至少在防非法注入(inject)方面提供更好的保护。至少，存储过程在执行前，首先会执行预编译，（如果由于非法参数的原因）编译出错则不会执行，这在某种程度上提供一层天然的屏障。
我至今还记得大约八、九年前采用的一个权限控制系统就是通过拼凑一个SQL语句，最终得到了一个形如“ where 1=1 and dataID in (1,2) and ModelID in (2,455) And ShopID in (111) and departID in ( 1,3) and ([Name] like %myword%) ”的where条件子句来获取符合条件的结果集。
注意：这个参数是通过地址栏web应用的地址栏或Winform的UI界面来输入的，所以对恶意注入需要花费一定的成本来维护。因为一些常用的关键字（或敏感词）很难区分是恶意或非恶意。
2、使用存储过程而不是直接访问基表，可以提供更好的安全性。你可以在行级或列级控制数据如何被修改。相对于表的访问，你可以确认有执行权限许可的用户执行相应的存储过程。这也是访问数据服务器的惟一调用途径。因此，任何偷窥者将无法看到你的SELECT语句。换句话说，每个应用只能拥有相应的存储过程来访问基表，而不是“SLEECT *”。
3、存储过程可以加密。（这点非常实用，设想一下，您的数据库服务器是托管的或租用的，你是否能心安理得的每天睡个安稳觉。如果竞争对手“一不小心”登上你的SQL Server,或通过注入得到了你的存储过程，然后相应的注入恶意的SQL，将您的业务逻辑乱改一通，而恰巧您五分钟前又没做备份，那会怎么样？）
（注意：加密存储过程前应该备份原始存储过程，且加密应该在部署到生产环境前完成。）
存储过程的加密非常简单，我们看一个例子：
插入测试表
use testDb2
go
/**********测试表*****************/
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_demo](
[id] [int] NOT NULL,
[submitdate] [datetime] NULL,
[commment] [nvarchar](200) NULL,
)
GO
SET ANSI_PADDING OFF
GO
Insert into [tb_demo]
select 1024, getdate(),REPLICATE('A',100);
WAITFOR DELAY '00:00:04';
Insert into [tb_demo]
select 1024, getdate(),REPLICATE('B',50);
go
插入存储过程：
/***************创建未加密的存储过程*******************/
Create Procedure CPP_test_Original
AS
select * from [tb_demo]
go
/***************创建加密的存储过程*******************/
Create Procedure CPP_test_Encryption
with encryption
AS
----可以换成任意的逻辑
execute CPP_test_Original
go
未加密的存储过程： 
加密的存储过程：
此时，至少，存储过程的内容不会被轻易看到（虽然解密也是有可能的）。应用这个，我们可以对某些关键的存储过程进行加密。但此时，存储过程仍然能被execute、alter和drop。
<二>安全上下文

除了加密sql文本的内容，我们还可以使用EXECUTE AS 子句设定存储过程的安全上下文，以满足不同的安全级别需求。
如果你对这些不感兴趣，请直接路过带下划线的段落。
(关于EXECUTE AS 子句的详细用法，请参看MSDN:http://msdn.microsoft.com/zh-cn/library/ms188354.aspx)
此处，我们需要了解的是：
1、在 SQL Server 中，可以定义以下用户定义模块的执行上下文：函数（内联表值函数除外）、过程、队列和触发器。
通过指定执行模块的上下文，可以控制数据库引擎使用哪一个用户帐户来验证对模块引用的对象的权限。这有助于人们更灵活、有力地管理用户定义的模块及其所引用对象所形成的对象链中的权限。必须而且只需授予用户对模块自身的权限，而无需授予用户对被引用对象的显式权限。只有运行模块的用户必须对模块访问的对象拥有权限。
针对函数、过程、队列和触发器，对应的参数也不同。存储过程对应的参数包括（CALLER | SELF | OWNER | 'user_name'）。
 
■CALLER      指定模块内的语句在模块调用方的上下文中执行。执行模块的用户不仅必须对模块本身拥有适当的权限，还要对模块引用的任何数据库对象拥有适当权限。 CALLER 是除队列外的所有模块的默认值，与 SQL Server 2005 行为相同。 CALLER 不能在 CREATE QUEUE 或 ALTER QUEUE 语句中指定。 ■SELF         EXECUTE AS SELF 与 EXECUTE AS user_name 等价，其中指定用户是创建或更改模块的用户。创建或更改模块的用户的实际用户 ID 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。SELF 是队列的默认值。 ■OWNER     指定模块内的语句在模块的当前所有者上下文中执行。如果模块没有指定的所有者，则使用模块架构的所有者。不能为 DDL 或登录触发器指定 OWNER。注意：OWNER 必须映射到单独帐户，不能是角色或组。 ■'user_name'   指定模块内的语句在 user_name 指定的用户的上下文中执行。将根据 user_name 来验证对模块内任意对象的权限。不能为具有服务器作用域的 DDL 触发器或登录触发器指定 user_name。请改用 login_name。user_name 必须存在于当前数据库中，并且必须是单独帐户。user_name 不能是组、角色、证书、密钥或内置帐户，如 NT AUTHORITY/LocalService、NT AUTHORITY/NetworkService 或 NT AUTHORITY/LocalSystem。执行上下文的用户 ID 存储在元数据中，可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。 
2、所有权链具有以下限制：
仅适用于 DML 语句：SELECT、INSERT、UPDATE 和 DELETE。
调用和被调用对象的所有者必须相同。
不适用于模块内的动态查询。 
我们看一个示例： 第一步、创建一个测试存储过程，用来delete表tb_Demo的所有数据 USE testDb2
GO
CREATE PROCEDURE dbo.[CPP_DEL_ALL_Tb_Demo]
AS
-- Deletes all rows prior to the data feed
DELETE dbo.[tb_Demo]
GO

第二步：创建一个账号TonyZhang,并赋于该账号对该存储过程的exec权限USE master
GO
CREATE LOGIN TonyZhang WITH PASSWORD = '123b3b4'
USE testDb2
GO
CREATE USER TonyZhang
GO
GRANT EXEC ON dbo.[CPP_DEL_ALL_Tb_Demo] to TonyZhang

以该账号登录SQL Server，并执行： EXECUTE dbo.CPP_DEL_ALL_Tb_Demo
/**
(4 row(s) affected)
**/

注意：此时，虽然TonyZhang除了执行存储过程[CPP_DEL_ALL_Tb_Demo]之外没有任何其他权限，但仍然执行了存储过程，并删除了表记录。 如果我们修改存储过程为： Alter PROCEDURE dbo.[CPP_DEL_ALL_Tb_Demo]
AS
-- Deletes all rows prior to the data feed
truncate table dbo.[tb_Demo]
GO

此时，再以TonyZhang登录，并执行存储过程，会提示：
这是因为所有者权链只限定在SELECT、INSERT、UPDATE 和 DELETE。而不包括Truncate，换句话说，系统授于的Exec只既定于SELECT、INSERT、UPDATE 和 DELETE 
有人可能会问：如果在存储过程内部调用动态语句，而不是明确的表名，我们如何限定权限呢？ 第三步：我们建立一个存储过程，功能是传入一个参数表名，查询该表的记录数。 CREATE PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO

授于Tonyzhang 以执行该存储过程的权限： GRANT EXEC ON dbo.[CPP_SEL_CountRowsFromAnyTable] to TonyZhang
go

此时，以Tonyzhang登录，执行存储过程，会提示： 
注意，此时，tonyzhang虽然有执行存储过程的权限，但是没有参数表的slect权限，所以执行失败。 第四步：修改存储过程的上下文 创建一个新账号jackwang，赋于表tb_Demo的select权限 USE master
GO
CREATE LOGIN JackWang WITH PASSWORD = '123b3b4'
USE Testdb2
GO
CREATE USER JackWang
GRANT SELECT ON OBJECT::dbo.[tb_Demo] TO JackWang
GO
/*******
注意：此时，JackWang 可以执行dbo.[tb_Demo的Select
*******/

修改存储的执行者 USE Testdb2
GO
alter PROCEDURE dbo.[CPP_SEL_CountRowsFromAnyTable]
@SchemaAndTable nvarchar(255)
WITH EXECUTE AS 'JackWang'
AS
EXEC ('SELECT COUNT(1) FROM ' + @SchemaAndTable)
GO

注意：这样，我们再调用存储过程[CPP_SEL_CountRowsFromAnyTable]时，会自动以JackWang的身份运行该存储过程。 此时，我们仍以Tonyzhang登录，再执行： 

小结： 本文通过简单的两个示例开始SQL server代码的安全之旅， 1、存储过程的加密，（注意：加密存储过程前应该备份原始存储过程，且加密应该在部署到生产环境前完成。） 2、存储过程的安全上下文。可以通过上下文设置更加严格的数据访问级别。（主要是对SELECT、INSERT、UPDATE 和 DELETE语句的访问限制）

后续部分将会涉及SQL server 2008新增的透明加密(TDE)功能。
 
邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   3w@live.cn