数据库之创建存储过程

sql 版本

 

 

 

 

sql servre 帮助文档中对存储过程的解释

　　创建存储过程。存储过程是已保存的 Transact-SQL 语句集合，或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用，

　　可接收并返回用户提供的参数。可以创建过程供永久使用，或在一个会话（局部临时过程）中临时使用，或在所有会话（全局临时过程）中临时使用

创建存储过程可以用 proc或者procedure关键字 proc是简写

 

 

目前有个简单的库（TestInfo），库里面有个简单的表（Show） 表里面有些简单的数据

 

编写个简单的存储过程，没有任何参数和返回值的存储过程

USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL --存储过程是否存在 
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
AS
SELECT * FROM Show

执行sql语句成功后，在sql中可以看到存储过程已经创建

 

 

OBJECT_ID(object_name,'object_type);函数用于判断对象是否存在

object_type对应的类型如下
　　AF = Aggregate function (CLR)
　　C = CHECK constraint --检查约束
　　D = DEFAULT (constraint or stand-alone)
　　F = FOREIGN KEY constraint --外键约束
　　FN = SQL scalar function --函数
　　FS = Assembly (CLR) scalar-function
　　FT = Assembly (CLR) table-valued function
　　IF = SQL inline table-valued function
　　IT = Internal table
　　P = SQL Stored Procedure  --存储过程
　　PC = Assembly (CLR) stored-procedure --CLR存储过程
　　PG = Plan guide
　　PK = PRIMARY KEY constraint  --主键约束
　　R = Rule (old-style, stand-alone) --规则
　　RF = Replication-filter-procedure
　　S = System base table  --数据库
　　SN = Synonym
　　SQ = Service queue
　　TA = Assembly (CLR) DML trigger  --CLR触发器
　　TF = SQL table-valued-function
　　TR = SQL DML trigger --DML触发器
　　U = Table (user-defined)  --数据表
　　UQ = UNIQUE constraint  --唯一约束
　　V = View  --视图
　　X = Extended stored procedure

比如上面的判断存储过程是否存在 OBJECT_ID('proc_show01','P') 获取存储过程名为：proc_show01
判断表是否存在 OBJECT_ID('info','U') 获取表名为：info
或者：
select * from sysobjects where name='info' and type='u'

 SQL Server支持五种类型的完整性约束
　　NOT NULL (非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值.
　　CHECK (检查)--检查在约束中指定的条件是否得到了满足.
　　UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
　　PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束.
　　POREIGN KEY (外部键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外部键可以指向主键或者其他表的唯一键.

 

在.Net转编写测试代码。调用刚创建的存储过程

 /// 执行存储过程
        /// </summary>
        /// <param name="cmdText">存储过程的名称</param>
        /// <param name="param">存储过程参数</param>
        /// <returns></returns>

        public static DataTable GetPro2(string cmdText, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                // 指定执行语句为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null && param.Length != 0)
                {
                    cmd.Parameters.AddRange(param);
                }
                SqlDataAdapter dp = new SqlDataAdapter(cmd);
                dp.Fill(dt);
            }
            return dt;
        }

 

　　成功后的界面
 

 

 

编写一个有输入参数的存储过程

USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL--存储过程是否存在 
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
    @name nvarchar(20)
)
AS
SELECT * FROM Show where @name=Name

 

测试看结果，传入 name="张三"

 

 

编写存储过程的可空参数，当存储过程参数有默认值的时候，

那么.net在调用存储过程的时候。可以不用传参数。否则如果不传这会报错

所以：

除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值

USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL --存储过程是否存在 
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
    @name nvarchar(20) = null --默认值为空
)
AS
IF @name is null
begin
    set @name='刘德华' --当没有传值的时候。设置默认值
end
select * from Show where @name = name

 

测试。当不传值的时候，默认查询的是 “刘德华”

 

 

 

创建有输入参数和输出参数的存储过程

USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL--存储过程是否存在 
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
    @id int output, --输出参数，输出id
    @name nvarchar(20) = null --默认值为空
)
AS
IF @name is null
begin
    set @name='刘德华' --当没有传值的时候。设置默认值
    set @id=0
    select * from Show where name = @name
    --return
end
else
    begin
        set @id=(select ID from Show where name = @name)
        select * from Show where name = @name
    end

 

既然有输出参数。那么得修改上面的.Net代码。如下

/// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="v1">输出参数（这里是ID）</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public static DataTable GetPro1(string cmdText, out int v1, params SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                // 指定执行语句为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                if (param != null && param.Length != 0)
                {
                    cmd.Parameters.AddRange(param);
                }
                SqlDataAdapter dp = new SqlDataAdapter(cmd);
                //dp.SelectCommand.Parameters.AddRange(param);
                dp.Fill(dt);

                /*
                  获取输出参数。
                 * 这里在组装参数的时候。指定了数组的第一个数是输出参数
                 */
                v1 = Convert.ToInt32(param[0].Value); 
　　　　　　　　　 //也可以通过SqlCommand根据参数名称获取存储过程的返回值，跟param[0].Value是同样的效果
                object id = cmd.Parameters["@id"].Value;
            }
            return dt;
        }

 

在组装参数的时候。要指定哪些参数是输入参数。哪些是输出参数，默认是输入参数(Input)。

如果是输入(或者输入输出)参数。则必须要赋值。在存储过程中没有给默认值的情况下

在下图可以看出。有输入参数有两个 Input 和 InputOutput

.Net 有个ParameterDirection 枚举类

 

sql 中有out 输出参数，output 输入输出参数

CREATE PROC proc_show01
(
    @id int output, --输入输出参数，输出id
    @ck int out,
    @name nvarchar(20) = null --默认值为空 ,默认是输入参数
)

但在.Net都是output。因为output就是输入输入参数的总称

 param[3].Direction = ParameterDirection.Output

 

测试代码

 static void Main(string[] args)
        {
            //SqlParameter p = new SqlParameter("@name", "张三");

            //拼装参数
            SqlParameter[] param = { 
                                        new SqlParameter("@id",SqlDbType.Int),
                                        new SqlParameter("@name",SqlDbType.NVarChar)
                                   };

            //设置参数是输出参数
            param[0].Direction = ParameterDirection.Output;
            param[1].Value = "王五";
            int id;
            DataTable dt = SQLHelper.GetPro1("proc_show01", out id, param); //没有参数
        }

运行看结果

 

 

 

上面用 param[0].Direction = ParameterDirection.Output; 指定第一个参数是输出参数

然后通过 v1 = Convert.ToInt32(param[0].Value); 获取存储过程返回的值

 

从ParameterDirection枚举可以看出输入输出参数都测试过了。还有一个操作的返回值没有测试

 

那么来编写一个有操作类返回值的存储过程

USE TestInfo
GO
IF OBJECT_ID('proc_show01','P') IS NOT NULL--存储过程是否存在 
DROP PROCEDURE proc_show01 --删除存储过程
GO
CREATE PROC proc_show01
(
    @id int output, --输出参数，输出id
    @name nvarchar(20) = null --默认值为空
)
AS
/*
定义一个变量，返回一个值 
也可以不定义变量，直接用return 返回
*/
declare @returnValue int 
IF @name is null
begin
    set @name='刘德华' --当没有传值的时候。设置默认值
    set @id=0
    select * from Show where name = @name
    set @returnValue=10
    --return 0 --这里同样可以
end
else
    begin
        set @id=(select ID from Show where name = @name)
        select * from Show where name = @name
        set @returnValue=11
    end
return @returnValue --返回值

 

用 ParameterDirection.ReturnValue 指定是返回值

 

GetPro1则要加一个out 参数

 public static DataTable GetPro1(string cmdText, out int v1,out int v2, params SqlParameter[] param)

则： v2 = Convert.ToInt32(param[1].Value);

 

测试看效果

 

存储过程里面也可以执行一个新增操作。然后返回刚新增的ID，比如：

begin
    set @name='刘德华' --当没有传值的时候。设置默认值
    --set @id=0
    select * from Show where name = @name
    set @returnValue=10
    --return 0 --这里同样可以
    insert Show(Name,Remark)VALUES('新增','地球');
    set @id = @@IDENTITY --返回新增的ID
end

 

上面都是通过.net代码访问存储过程，那么通过Transact-SQL语句怎么执行呢？

Transact-SQL 语句用exec（简写）关键字 ，全称是execute关键字

因为存储过程的参数顺序是

 

所以可以这样直接传参数，但顺序必须根据存储过程定义参数顺序一样

 

 

 

上面只是获取结果集。那么怎获取输出参数(outPut)和返回值呢(ReturnValue)

因为返回值是方法的返回值。所以可以变量名=存储过程名称 是不是跟.Net中很相似？

declare @m int --定义变量接收值
declare @result int 
exec @result=[proc_show01] @name='赵六',@id=@m output --一定要注名是output
select @m as '输出参数',@result as '返回值'

 

执行结果

 

其实就是参数名= 值 @name='赵六'，@id=@m  的方式。因为

一旦使用了 '@name = value' 形式之后，所有后续的参数就必须以 '@name = value' 的形式传递。

 

用这种方式。顺序可以不用跟存储过程中定义参数的顺序相同

 

如果不用@name = value'的方式同样可以，

但这样直接传参数，但顺序必须根据存储过程定义参数顺序一样，返回值放在最前面

 

DECLARE @name varchar
--set @name='张三'
DECLARE @id int
DECLARE @result int
EXEC @result = proc_show01 @id output,'张三' --这里传参数的顺序一定要对应
--EXEC @result = proc_show01 @id output,@name 
select @id as '输出参数',@result as '返回值'

 

 

结果一样，其实不一样。因为传的参数不一样，哈哈哈

 

 

 

存储过程与SQL语句对比 （知识来源于网络）

优势:

1、提高性能
　　SQL语句在创建过程时进行分析和编译。 存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、

　　优化，并给出最终被存在系统表中的存储计划，这样，在执行过程时便可节省此开销。

2、降低网络开销
　　存储过程调用时只需用提供存储过程名和必要的参数信息，从而可降低网络的流量。

3、便于进行代码移植
　　数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。

4、更强的安全性
　　1）系统管理员可以对执行的某一个存储过程进行权限限制，避免非授权用户对数据的访问
　　2）在通过网络调用过程时，只有对执行过程的调用是可见的。 因此，恶意用户无法看到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
　　3）使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码，所以，攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。
　　4）可以对过程进行加密，这有助于对源代码进行模糊处理。 

 

劣势：

1、存储过程需要专门的数据库开发人员进行维护，但实际情况是，往往由程序开发员人员兼职

2、设计逻辑变更，修改存储过程没有SQL灵活

为什么在实际应用中，存储过程用到相对较少呢？

在通常的项目研发中，用存储过程却相对较少，这是为什么呢？
分析原因如下：
　　1）没有特定的数据库开发人员，普通程序员兼职进行数据库操作
　　2）程序员往往只需操作程序，即可完成数据访问，无需再在数据库上进行开发
　　3）项目需求变动比较频繁，修改SQL语句比较方便，特别是涉及逻辑变更 

存储过程与SQL语句如何抉择？

基于实际应用的经验，给予如下建议：

　　1、在一些高效率或者规范性要求比较高的项目，建议采用存储过程
　　2、对于一般项目建议采用参数化命令方式，是存储过程与SQL语句一种折中的方式
　　3、对于一些算法要求比较高，涉及多条数据逻辑，建议采用存储过程

 

 

//新增字段前判断字段是否存在

 

IF NOT EXISTS (select name from syscolumns where id=object_id(N'tb') AND NAME='name')

 BEGIN

 ALTER TABLE tb

 ADD name VARCHAR(100) NULL

 END

GO

--新增字段
ALTER TABLE dbo.info ADD test bit NULL

--添加描述
EXECUTE sp_addextendedproperty N'MS_Description', '这里是描述', N'user', N'dbo', N'table', N'info', N'column', N'test' 


--更新说明
EXEC sp_updateextendedproperty 'MS_Description','字段1','user',dbo,'table','info','column',test  

--删除属性
EXEC sp_dropextendedproperty 'MS_Description','user',dbo,'table','info','column',test  

1、添加字段

alter table 表名 add 字段名 type not null default 0

2、修改字段名

alter table 表名 rename column A to B

3、修改字段类型

alter table 表名 modify column UnitPrice decimal(18, 4) not null 

4、修改字段默认值

alter table 表名 drop constraint 约束名字   ------说明：删除表的字段的原有约束

alter table 表名 add constraint 约束名字 DEFAULT 默认值 for 字段名称 -------说明：添加一个表的字段的约束并指定默认值

5、删除字段

alter table 表名 drop column 字段名;

 

收集网络删的一个查询表的信息

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_SelectTable]
    @tableName nvarchar(500)
AS
BEGIN
    SET NOCOUNT ON;
        SELECT 
        表名       = case when a.colorder=1 then d.name else '' end,
        表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
        字段序号   = a.colorder,
        字段名     = a.name,
        标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
        主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                         SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
        类型       = b.name,
        占用字节数 = a.length,
        长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
        小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
        允许空     = case when a.isnullable=1 then '√'else '' end,
        默认值     = isnull(e.text,''),
        字段说明   = isnull(g.[value],'')
    FROM 
        syscolumns a
    left join 
        systypes b 
    on 
        a.xusertype=b.xusertype
    inner join 
        sysobjects d 
    on 
        a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
    left join 
        syscomments e 
    on 
        a.cdefault=e.id
    left join 
    sys.extended_properties   g 
    on 
        a.id=G.major_id and a.colid=g.minor_id  
    left join
    sys.extended_properties f
    on 
        d.id=f.major_id and f.minor_id=0
    where 
        d.name in(@tableName)    --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
    order by 
        a.id,a.colorder
END
GO

调用：exec sp_SelectTable @tableName='表名'

 

 源码

参考资料

   http://www.cnblogs.com/cwyblog/archive/2013/04/21/3034281.html

   http://www.cnblogs.com/yank/p/4235609.html

 http://www.cnblogs.com/SkySoot/archive/2012/02/10/2345185.html

http://www.cnblogs.com/sosoft/p/3535696.html