存储过程:
一、 基本知识
1 存储过程格式
CREATE PROC存储过程名
[ 参数1数据类型[=默认值], 参数2数据类型[=默认值], … 参数n数据类型[=默认值] ]
AS
[ DECLARE变量1数据类型, DECLARE变量2数据类型, … DECLARE变量n数据类型 ]
BEGIN
T-SQL语句
END
GO
注:
〈1〉方括号内语句根据需要可以有,也可没有。
〈2〉T-SQL中自定义之全局变量,前缀为@@,具体有那些全局变量可参阅有关书藉,存储过程自定义之局部变量,前缀为@
〈3〉基本数据类型种类:
numeric(m,n) 浮点数据类型,m:总位数,n:小数位数
int 整数数据类型
char(m) 字符串数据类型,当m为一时,表示单个字符
〈4〉默认值:当调用此存储过程时如未向此参数传递值,则此参数取此默认值,如果默认值为null时,可返回定制的消息
2 基本语法、命令
IF…ELSE 条件执行语句
WHILE 循环执行语句
GOTO标号 跳转语句
RETURN[返回数] 退出命令
PRINT[参数] 输出命令
BREAK 退出循环
CONTINUE 跳至下一循环
RAISERROR 显示自定义错误信息
WAITFOR 等待某一事件发生后执行
BEGIN…END 将一组SQL语句作为一个语句块
GO 发送批命令,必须输在行首
欲知详情,请参阅有关书藉。
3 游标
为处理大量数据,常需使用游标,使用方法如下:
〈1〉定义
declare 游标名 cursor for
t-sql语句
〈2〉打开
open 游标名
〈3〉使用:
fetch 游标名 [into变量1 变量n]
〈4〉关闭:
close 游标名
deallocate cursor 游标名
二、 实例
if object_id('Change_node') is not NULL /*检查此存储过程是否存在*/
drop proc Change_node /*删除此存储过程*/
go
create proc Change_node
as
begin
//局部变量
declare @m_rowid numeric(10,2), @m_terminal char(3), @m_node_no_char(4)
declare another cursor for
select rowid,terminal
from授权流水表
where node_no=‘行号’ /*此处为各行全国联行行号的后四位* /
open another
while@@sqlstatus!=2
begin
fetch another into @m_rowid,@m_terminal
select@m_node_no=convert(char(1),0)+@m_terminal
print“终端号:%1!节点号:%2!”@m_terminal,@m_node_no
update 授权流水表
set node_no=@m_node_no
where rowid=@m_rowid
end
end
close another
deallocate cursor another
end
go
2 常用全局变量
@@CONNECTIONS
返回自上次启动以来连接或试图连接的次数。
@@CURSOR_ROWS
返回连接上最后打开的游标中当前存在的合格行的数量。
@@DATEFIRST
返回每周第一天的数字
@@ERROR
返回最后执行的SQL 语句的错误代码。
@@FETCH_STATUS
返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。
@@IDENTITY
返回最后插入的标识值
@@LANGID
返回当前所使用语言的本地语言标识符(ID)。
@@LANGUAGE
返回当前使用的语言名。
@@LOCK_TIMEOUT
返回当前会话的当前锁超时设置,单位为毫秒。
@@PROCID
返回当前过程的存储过程标识符 (ID) 。
@@ROWCOUNT
返回受上一语句影响的行数。
@@SERVERNAME
返回运行 的本地服务器名称。
@@SPID
返回当前用户进程的服务器进程标识符 (ID)。
@@TRANCOUNT
返回当前连接的活动事务数。
@@VERSION
返回当前安装的日期、版本和处理器类型
3 存储过程好处
1.存储过程可以使得程序执行效率更高、安全性更好,因为过程建立之后 已经编译并且储存到数据库,直接写sql就需要先分析再执行因此过程效率更高,直接写sql语句会带来安全性问题,如:sql注入
2.建立过程不会很耗系统资源,因为过程只是在调用才执行。
1、预编译,存储过程预先编译好放在数据库内,减少编译语句所花的时间。
2、缓存,编译好的存储过程会进入缓存,所以对于经常执行的存储过程,除了第一次执行外,其他次执行的速度会有明显提高。
3、减少网络传输,特别对于处理一些数据的存储过程,不必像直接用sql语句实现那样多次传送数据到客户端。
4、更好的利用服务器内存,特别对于处理中间数据量不大的情况,存储过程中可以利用存放在内存的表变量。
4 java调用存储过程
CallableStatement proc = null;
Connection conn = null;
try {
conn = DBUtil.getConn();
proc = conn.prepareCall("{ ? = call cwnew_pack.addCwform(?) }");
proc.registerOutParameter(1, Types.CHAR);
proc.setString(2, 参数);
proc.execute();
String str = proc.getString(1);
return str;
}
finally{
try {
proc.close();
}
}
触发器:
触发器是一种特殊类型的存储过程,触发器可以查询其它表,而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求
优点如下:
触发器是自动的:它们在对表的数据作了任何修改之后立即被激活。
触发器可以通过数据库中的相关表进行层叠更改
触发器可以强制限制,这些限制比用 CHECK 约束所定义的更复杂