1、/*存储过程*/*是在数据库服务器上创建、运行的一组sql语句的集合的过程程序通常只在首次运行时编译后驻留高速缓存中,以后调用直接在高速缓存中获取优点:提高查询性能,减少网络流量编译优化 执行速度快加强安全性类型用户定义的存储过程系统存储过程 系统存储过程以字符sp_开头扩展存储过程 系统扩展存储过程以字符sp_或xp_ 开头*/-系统存储过程- 通常返回0表示成功 失败为非0值exec sp_databasesexec sp_helpfile -返回当前数据库的文件信息-用户定义的存储过程-创建-规则 :-不能包含的 sql语句create default trigger view rule
2、 function等-不带参数的存储过程create procedure proc_empasselect * from emp;-调用exec dbo.proc_emp-带输入参数的存储过程IF OBJECT_ID ( dbo.proc_emp2, P ) IS NOT NULL DROP PROCEDURE dbo.proc_emp2;GOCREATE PROCEDURE dbo.proc_emp2 name varchar(20) =刘德华, -指定默认值id int =107 AS beginif(id100)select * from emp where empName=name o
3、r empId=idelseprint 编号必须大于100;end-调用方式exec dbo.proc_emp2exec dbo.proc_emp2 张无忌,44exec dbo.proc_emp2 name=张无忌,id=101-带通配符输入参数的存储过程if Object_id(dbo.proc_emp3,p) is not nulldrop proc dbo.proc_emp3gocreate proc proc_emp3name varchar(20) =张% -指定默认值asselect * from emp where empName like name;-调用exec proc_
4、emp3 %exec proc_emp3 name =刘 %exec proc_emp3-使用输出参数的存储过程if Object_id(dbo.proc_emp4,p) is not nulldrop proc dbo.proc_emp4gocreate procedure dbo.proc_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 dbo.pr
5、oc_emp4 m output,n outprint 薪水最高的员工: +n + 薪水:+cast(m as varchar(5)-综合例子 :if exists(select * from sys.sysobjects where name=bank)drop table bankGOcreate table bank(customerName varchar(20),currentMoney decimal(12,2) -当前余额)alter table bankadd constraint CK_currentMonalterey check(currentMoney=1.00)GOi
6、nsert 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=bcommit tran
7、saction set r=1end trybegin catchrollback transactionset 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-更新存储过程(和
8、创建存储过程一样)alter proc proc_name-变量声明as-T_SQL语句块-删除存储过程drop procedure dbo.proc_emp5;-查看存储过程信息exec sp_help proc_emp4select object_definition(object_id(proc_emp4) /*拓展函数*/-函数与存储过程区别-函数有返回结果可以返回单个标量值或结果集(table类型)-存储过程可以没有返回值 也可以返回多个值或结果集-函数可以在 sql语句中直接使用 -函数一般不针对特定的表 -创建函数if exists(select * from sys.sysob
9、jects 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 i
10、nt) 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=identityCard from emp where empName=nif(t is null)set t=没有记录身份证号码 return tend-调用select dbo.fun_emp(小龙女)select dbo.fun_emp(刘德华) as 身份证号码select dbo.fun_emp(default)