【T-MAC学习笔记18之--浅谈存储过程】
这次讲得是 存储过程(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.关于参数的赋值 

SQL code

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启动时候创建这么一个全局临时表
做法:

SQL code

--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

误写系统存储过程那点事:

SQL code

用户在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