【T-MAC学习笔记18之--浅谈存储过程】

feixianxxx 2009-11-27 09:30:39
加精
这次讲得是 存储过程(proc)

存储过程分类:
1.用户自定义存储过程: 分成 TRANSACT-SQL存储过程和CLR存储过程
2.拓展存储过程(后续版本将删除这个,所以避免使用它了,用clr替代)
3.系统存储过程物理意义上讲,系统存储过程存储在源数据库中,并且带有 sp_ 前缀。
从逻辑意义上讲,系统存储过程出现在每个系统定义数据库和用户定义数据库的 sys 构架中。

存储过程的优点:
1.执行速度更快。
有两个原因:首先,在存储过程创建的时候,数据库已经对其进行了一次解析和优化。
其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用
2.存储过程允许模块化程序设计。
当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete 时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量
4.安全性高。
可设定只有某此用户才具有对指定存储过程的使用权。这里赋予的是对存储过程的权利,不是对存储过程里面的内部对象的权利
例;你要赋予PROC_s 的执行权利,但是不准用户user1有 PROC_S里面的k表的权限
exec deny select on k to user1
exec grant execute on PROC_S to user1
5.减少网络通信量。
调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别,
可是如果存储过程包含上百行SQL 语句,那么其性能绝对比一条一条的调用SQL 语句要高得多。
6.布式工作.
应用程序和数据库的编码工作可以分别独立进行,而不会相互压制。

存储过程的规则:(更多参考MSDN)
1.不要在主体重包含一些DML语句,比如 create(alter ) default,function,view,trigger ,SET SHOWPLAN_XML等等
2.其他数据库对象均可在存储过程中创建。 可以引用在同一存储过程中创建的对象,只要引用时已经创建了该对象即可。
3.可以在存储过程内引用临时表。
4.存储过程中的参数的最大数目为 2100。
5.根据可用内存的不同,存储过程最大可达 128 MB。
6.数据库引擎将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。在存储过程中出现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储过程的功能。

存储过程的参数:
1.参数按方向分2种: in->传入内部; out->传入内部和传出外部
当用In类型的时候,参数一般用作条件供内部使用,
当用Out类型的时候,参数一般是用作返回值传给应用程序的,在定义时候用output指定.
a.这里有个地方,当你的output参数传入后如果在proc内部进行了修改,它还是可以再次传出来的,不过值发生了变化,这个很像C等语言里面的参数地址传送。
b.proc本身也可以返回值 在它内部用return 指定返回值 然后用
DECLARE @result int;
EXECUTE @result = my_proc;
这种方式得到这个结果值 然后根据这个变量值 进行后续操作......(实际情况实际处理)
2.关于参数的赋值
example:
CREATE PROCEDURE dbo.my_proc
@first int = NULL, -- NULL default value
@second int = 2, -- Default value of 2
@third int = 3 -- Default value of 3
AS SELECT @first, @second, @third;
GO
--这里执行的方式很多
EXECUTE dbo.my_proc; --结果/*NULL 2 3*/
EXECUTE dbo.my_proc 10, 20, 30;--结果/*10 20 30*/
EXECUTE dbo.my_proc @second = 500;--结果/*NULL 500 3*/
EXECUTE dbo.my_proc 40, @third = 30;--结果/*40 2 30*/
EXECUTE dbo.my_proc 40,default,50;--结果/*40 2 50*/



执行存储过程
1.执行带有OUTPUT参数的PROC的时候 注意执行时候 对应的参数后面加上 output
2.执行一个不带架构名的PROC的时候 sql会自动按下面的顺序解析一个PROC
例:你连接在SALES 数据库 默认架构为sale 你的存储过程名为 p_s
a.在SALES数据库的SYS架构下寻找p_s 找不到转到下面一步
b.继续在默认架构下寻找 这里是在sale架构下寻找p_s.如果它位于另外一个存储过程(sSALES.sale2.poc2)内部
则在sale2下寻找p_s。没找到 转到下面一步
c.在dbo架构下寻找p_s..- -||找不到 就弹错了...
3.这里还有一个技巧,在某些场合也许有用:你需要一个全局临时表来存储一些全局变量,这样就需要在每次SQL启动时候创建这么一个全局临时表
做法:
--a.在MASTER库中创建:
create proc sp_quanju
as
create table #quanju (val sql_variant)

--b.将这个存储过程设置成自动启动
exec sp_procoption 'sp_quanju','StartUp','TRUE'--要关掉只要把最后一个参数设置为FALSE 或者NO


查看存储过程:(详见MSDN)
这里就写几个系统存储过程或者目录视图
1.查询存储过程定义;
sys.sql_modules ; OBJECT_DEFINITION(); sp_helptext
2.查询存储过程相关信息
sys.objects sys.procedures sp_help

误写系统存储过程那点事:
用户在MASTER数据库中创建以sp_开头的存储过程会有意外(in book)
USE master;
GO
CREATE PROC dbo.sp_Proc1
AS
PRINT 'master.dbo.sp_Proc1 executing in ' + DB_NAME();
EXEC('SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'';');
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
GO
---这里切换到另外一个数据库
USE Northwind;
EXEC dbo.sp_Proc1;
--最后DB_NAME()返回的是Northwind 动态查询返回的Northwind的表 而不是MASTER库 ,静态返回的确实MASTER的表
--这个存储过程里也没有明显切换数据库的语句 自动转化

---这个时候我去姜它变成真正的系统存储过程---
USE master;
EXEC sp_MS_marksystemobject 'dbo.sp_Proc1';
GO
--接着去执行检验 发现都是在切换后的数据库下
USE Northwind;
EXEC dbo.sp_Proc1;
USE pubs;
EXEC dbo.sp_Proc1;

--如果我们在Northwind创建了一个相同名字的存储过程 就是和系统PROC一样的 怎么办?SQL是会调用用户定义的
USE Northwind;
GO
IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL
DROP PROC dbo.sp_Proc1;
GO
CREATE PROC dbo.sp_Proc1
AS
PRINT 'Northwind.dbo.sp_Proc1 executing in ' + DB_NAME();
GO

--更加神奇的地方是 如果你在master用sp_开头建立的表
USE master;
GO
IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL
DROP TABLE dbo.sp_Globals;
GO

CREATE TABLE dbo.sp_Globals
(
var_name sysname NOT NULL PRIMARY KEY,
val SQL_VARIANT NULL
);
USE Northwind;
INSERT INTO dbo.sp_Globals(var_name, val)
VALUES('var1', 10);
USE pubs;
INSERT INTO dbo.sp_Globals(var_name, val)
VALUES('var2', CAST(1 AS BIT));
USE tempdb;
SELECT var_name, val FROM dbo.sp_Globals;
GO
/*
var_name val
------------ ----------
var1 10
var2 1
*/
----上面的实例告诉我们不要在MASTER里面随便建立以SP_开头的对象
...全文
3628 115 打赏 收藏 转发到动态 举报
写回复
用AI写文章
115 条回复
切换为时间正序
请发表友善的回复…
发表回复
Xjt11235813 2012-08-17
  • 打赏
  • 举报
回复
学习了,慢慢研究下!
摸着石头前行 2011-10-04
  • 打赏
  • 举报
回复
学习了,讲的不错。
wjlxsyy 2010-10-13
  • 打赏
  • 举报
回复
顶了............
Rotel-刘志东 2010-09-09
  • 打赏
  • 举报
回复
学习了。
wlong777 2010-08-16
  • 打赏
  • 举报
回复
学习学习
人狼911 2010-08-06
  • 打赏
  • 举报
回复
To study!
Ding Ding!!!1
NHSS2010 2010-06-01
  • 打赏
  • 举报
回复
看贴,回贴,糊啦
Tanhualin 2010-04-25
  • 打赏
  • 举报
回复
学习一下
zx_wyy 2010-04-23
  • 打赏
  • 举报
回复
怎么只有18呀。没有全集吗。
xy502742540 2010-04-21
  • 打赏
  • 举报
回复
收藏了 谢谢分享
liaowei380982526 2010-04-20
  • 打赏
  • 举报
回复
受教了
archer1214 2010-03-07
  • 打赏
  • 举报
回复
谢谢,刚开始学习SQL,学习。。。了
meijin5 2010-01-25
  • 打赏
  • 举报
回复
额没看懂
duanguangxin8 2010-01-13
  • 打赏
  • 举报
回复

很有用啊 ...
micky_ya 2010-01-08
  • 打赏
  • 举报
回复
学习了! 感激
bdx808 2009-12-30
  • 打赏
  • 举报
回复
学习了
心中的彩虹 2009-12-22
  • 打赏
  • 举报
回复
谢谢 正需要
xiaoxiangqing 2009-12-03
  • 打赏
  • 举报
回复
謝謝。
meander 2009-12-03
  • 打赏
  • 举报
回复
up
梦槲寄生 2009-12-03
  • 打赏
  • 举报
回复
up
加载更多回复(95)

6,129

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 新技术前沿
社区管理员
  • 新技术前沿社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧