存储过程(Stored Procedure)

概述

—-定义：

• 应用在大型数据库系统中，是SQL语句和流程控制语句的集合，经编译后存储在数据库系统中，用户通过指定存储过程的名字并给出参数(如果带有参数的话)来执行，类似高级语言中的函数。在创建时编译一次，以后执行时运行很快。

—-类型：

• a.系统存储过程，以sp_开头,用来进行系统的各项设定，取得信息及相关管理工作, 如 sp_help就是取得指定对象的相关信息。
• b.本地存储过程，由用户创建的存储过程，一般所说的存储过程就是指本地存储过程。
• c.扩展存储过程，以XP_开头，用户可以使用外部程序语言编写的存储过程，用来调用操作系统提供的功能。

—-优点：

• a.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
  b.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
• c.存储过程可以重复使用,可减少数据库开发人员的工作量。
• d.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

—-缺点：

• a. 调试麻烦。
• b. 移植问题，与具体数据库相关，需要考虑移植问题。在梅安森项目中需要考虑从SQLServer到PostgreSQL移植工作。
• c. 如果在应用程序中大量使用存储过程时，到程序交付给客户的时候，需要考虑系统的相关问题，维护代价大。

存储过程格式：

—-创建存储过程

create proc or procedure sp_name
@[参数名][类型],@[参数名][类型][output]
[with]{recompile|encryption}
as
begin
end;

—-调用存储过程

exec/call sp_name[参数名];

—-删除存储过程

drop procedure sp_name;

—-注释:

• output：表示此参数是可传回的;
• with {recompile|encryption};
• recompile:表示每次执行此存储过程时都重新编译一次;
• encryption:所创建的存储过程的内容会被加密 sql_statement。

不同数据库对应的存储过程

大多数常用数据都支持存储过程，但是各类数据库都有自己的过程语言或者语法格式，也就是访问不同类型的数据库，需要考虑移植问题。下面简单描述了各种常见数据库中使用存储过程的语法格式。

—-MySQL:

• —-创建存储过程
  CREATE PROCEDURE pro_name (OUT param1 INT)，参数类型(in、out、inout);

• —–调用存储过程
  CALL pro_name;

—-SQL Server:

SQL Server数据库提供的过程语言是Transact-SQL，简称T-SQL。

• —-创建语法
  create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] as
SQL_statements
• 2.调用、执行
  exec pro_name

—-Oracle:

Oracle数据库提供工程语言PL/SQL来构建存储过程。

• —–创建语法：
  CREATE [OR REPLACE] PROCEDURE pro_name
(
[arg1 [ IN | OUT | IN OUT ]] type1 [DEFAULT value1],
[arg2 [ IN | OUT | IN OUT ]] type2 [DEFAULT value1]],
......
[argn [ IN | OUT | IN OUT ]] typen [DEFAULT valuen]
)
[ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
<声明部分>
BEGIN <执行部分> EXCEPTION
<可选的异常错误处理程序>
END pro_name;
• —-调用存储过程,
  <EXECUTE | EXEC pro_name(param1,param2);

—-PostgreSQL:

PostgreSQL数据库提供多种过程语言，PL/pgSQL, PL/Tcl, PL/Perl, PL/Python，下面是PL/gpSQL过程语言的一个示例。

• —-创建表
  create table test(id int,name text);
• —-创建存储过程
  create function ins_data_test1(int,text) returns bool as' declare
id alias for $1;
name alias for $2;
begin
insert into test values(id,name);
return true;
end;
language 'plpgsql'
• —-执行存储过程
  select ins_data_test1(1,'shengch');

PostgreSQL的存储过程及示例

—-介绍

在Oracle数据库中，存储过程和函数统称为PL/SQL子程序，他们的唯一区别是函数总向调用者返回数据，而过程则不返回数据，过程的参数可以有三种模式(IN、OUT、IN OUT)，而函数只有一种(IN)。而在PostgreSQL数据库中不区分函数和存储过程，或者说它把存储过程当做函数来处理，因此在用PL/gpSQL创建的存储过程中，必须返回数据，类型可以为VOID。

—-示例

给出如下条件进行批处理编排
— - - 开始日期时间
— - - 重复间隔(分钟)
— - - 重复次数
需求：
要求在档期内重复安排节目播出, 比如: 2003.01.01 08:00 开始每隔240分钟 播出一次, 一共播出100次, 或者用户自行设定的其他时间。

---

—-创建表
create table co_schedule
(
n_progid int,
dt_starttime timestamp,
dt_endtime timestamp
);
—-创建函数(存储过程)
create function add_program_time(int4,timestamp,int4,int4,int4) returns bool as '
declare
prog_id alias for $1;
duration_min alias for $3;
period_min alias for $4;
repeat_times alias for $5;
i int;
starttime timestamp;
ins_starttime timestamp;
ins_endtime timestamp;

begin

starttime :=$2;
i := 0;
while i<repeat_times
loop
ins_starttime := starttime;
ins_endtime := timestamp_pl_interval(ins_starttime, CAST(duration_min || ''mins'' AS interval));
starttime := timestamp_pl_interval(ins_starttime, CAST(period_min || ''mins'' AS interval));
insert into co_schedule values(prog_id,ins_starttime,ins_endtime);
i := i+1;
end loop;

if i<repeat_times then
return false;
else
return true;
end if;

end;

'language 'plpgsql';