SQLServer2005_存储过程.doc

/*存储过程*/*是在数据库服务器上创建、运行的一组sql语句的集合的过程程序 通常只在首次运行时编译后驻留高速缓存中，以后调用直接在高速缓存中获取优点： 提高查询性能，减少网络流量 编译优化 执行速度快 加强安全性类型 用户定义的存储过程 系统存储过程 系统存储过程以字符sp_开头 扩展存储过程系统扩展存储过程以字符sp_或xp_开头*/-系统存储过程-通常返回0表示成功 失败为非0值exec sp_databasesexec sp_helpfile -返回当前数据库的文件信息-用户定义的存储过程-创建 -规则:-不能包含的sql语句create default trigger view rule function等-不带参数的存储过程create procedure proc_empasselect * from emp;-调用exec c_emp-带输入参数的存储过程IF OBJECT_ID ( c_emp2, P ) IS NOT NULL DROP PROCEDURE c_emp2;GOCREATE PROCEDURE c_emp2 name varchar(20) =刘德华, -指定默认值 id int =107 AS beginif(id100)select * from emp where empName=name or empId=idelseprint 编号必须大于100;end-调用方式exec c_emp2exec c_emp2 张无忌,44exec c_emp2 name=张无忌,id=101-带通配符输入参数的存储过程if Object_id(c_emp3,p) is not null drop proc c_emp3gocreate proc proc_emp3name varchar(20) =张%-指定默认值asselect * from emp where empName like name;-调用exec proc_emp3 %exec proc_emp3 name =刘%exec proc_emp3-使用输出参数的存储过程if Object_id(c_emp4,p) is not null drop proc c_emp4gocreate procedure c_emp4max int output,name varchar(20) outputasselect top 1 name=empName,max=pay from emp4 order by pay desc;GO-调用declare m int,n varchar(20)execute c_emp4 m output,n outprint 薪水最高的员工：+n + 薪水:+cast(m as varchar(5)-综合例子:if exists(select * from sys.sysobjects where name=bank) drop table bankGO create table bank(customerName varchar(20),currentMoney decimal(12,2) -当前余额)alter table bankadd constraint CK_currentMonalterey check(currentMoney=1.00)GOinsert into bank values(张三,1000)insert into bank values(李四,1)GOCreate proc proc_banka varchar(20),b varchar(20),m decimal,r int outputasbegin transactionbegin tryupdate bank set currentMoney=currentMoney-m where customerName=aupdate bank set currentMoney=currentMoney+m where customerName=b commit transaction set r=1end trybegin catch rollback transaction set r=0end catch-调用declare a intexec proc_bank 张三,李四, 1000,a outputprint a Go-with encryption-加密存储过程的定义阻止返回存储过程的定义文本create proc proc_emp5with encryptionasselect * from emp;exec sp_helptext proc_emp5 -返回定义的存储过程的文本exec sp_helptext proc_emp4-更新存储过程（和创建存储过程一样）alter proc proc_name-变量声明as-T_SQL语句块-删除存储过程drop procedure c_emp5;-查看存储过程信息exec sp_help proc_emp4select object_definition(object_id(proc_emp4) /*拓展函数*/-函数与存储过程区别-函数有返回结果可以返回单个标量值或结果集(table类型)-存储过程可以没有返回值 也可以返回多个值或结果集-函数可以在sql语句中直接使用 -函数一般不针对特定的表-创建函数if exists(select * from sys.sysobjects where name=fun_sum)drop function fun_sumGocreate function fun_sum (a int,b int) returns intasbegindeclare result intset result=a+breturn resultendGO-调用declare a intset a=dbo.fun_sum(4,8)-注意调用函数时需要在函数名前加上架构名print aselect dbo.fun_sum(4,8) as numsum-求圆柱体的体积的函数create function fun_square (r int,h int) returns intasbegindeclare result intset result=PI()*square(r)*hreturn resultendGO-调用select dbo.fun_square(2,5)-创建函数if exists(select * from sys.sysobjects where name=fun_emp)drop function fun_empGocreate function fun_emp(n varchar(30)=刘德华) returns varchar(30) -可以指定参数默认值asbegindeclare t varchar(30)select t=identi