SQL学习笔记之存储过程

    存储过程：是一种数据库对象，它是指服务器端需要反复使用的T-SQL语句的集合，在其它编程语言中相当于T-SQL字程序。

    我们先来写一个入门的小例子:

create procedure prhelloworld
as
select 'Hello Dejan’' --注意这里是单引号，用来输出文本Hello Dejan
alter procedure prhelloworld/*修改存储过程*/
as
select 'Hello again'
drop procedure prhelloworld/*删除存储过程*/

   小提示：在SQL中可以使用（--）二横来进行单行注释，可以用用/**/来进行多行注释

    在修改存储过程的时候有二种方法

   一种是：重建存储过程，要注意先要将已存在的存储过程删除

drop proc prhelloworld
go
create procedure prhelloworld
@Type varchar(50)—我们可以使用变量来接受外部值
as
select @Type
go

    另外我们也可以使用系统表sysobjects表来判断存储过程是否存在，如下:

if exists(select * from sysobjects
                   where id=object_id('prhelloworld') and
                         objectproperty (id,'isprocedure')=1)
drop proc get

    另一种是：使用alter procedure 修改存储过程 

alter procedure prhelloworld
@Type varchar(50)
as
select @Type
go

    小提示：存储过程的过程名最长为128字符，它最多可以带1024个输入输出参数，它的主体由一条至多条SQL语句组成，最大长度为128M。

    现我们先创建一个表:

if exists(select * from sysobjects where id=object_id(N'’[dbo].[test]’) and objectproperty (id,'isUserTable)=1)
drop table [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
             [Id] [int] IDENTITY (1, 1) NOT NULL ,
             [Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,--这里要注意了，这是定义了一种排序规则，系统会对char /varchar自动生成一种排序规则的。
             [***] [bit] NULL ,
             [Age] [int] NULL
) ON [PRIMARY]
GO

温习：表结构修改与排序规则

可使用 ALTER TABLE 语句更改列的排序规则：

CREATE TABLE test1 (PrimaryKey int PRIMARY KEY, Name varchar(20) COLLATE French_CI_AS NOT NULL )GOALTER TABLE test ALTER COLUMN Name            varchar(10)COLLATE Latin1_General_CI_AS NOT NULLGO

我们可以使用alter tabel来进行表的修改。它的语法如下

alter table test add height int null—添加一个整型height字段

alter table test alter column height char not null—修改字段height为char型，并且不可以为空

alter table test drop column height—删除字段height

我们先创建一个存储过程

create procedure pr_test
@id int,
@name char(20)
as
select age from test where [id]=@id and [name]=@name

    假设我们的test表有如下数据:

ID	NAME	***	AGE
1	zhangsan	0	20
2	lisi	1	21
3	wangwu	1	28
4	zhaolui	0	22
5	jiangqi	0	20

    我们可以使用如下语句调用过程

--这里只要在execute后面跟过程名就可以了,各参数用“，”分隔
execute pr_test 1,'zhangsan'

    结果显示

     age

     20

    小提示SQL有些命令关键字可以只写前面4个字母。如execute可写成exec,procedure可写成proc

下面我们再来个例子

alter procedure pr_test
@id int,
@name char(20),
@age int output
as
select @age=age from test where [id]=@id and [name]=@name

    我们可以使用下面的语句调用存储过程

declare @iage--这里使用declare来定义变量
execute pr_test 1,'zhangsan',@age output
select @iage '年龄'

     结果：

    年龄

     20

    注意在使用输出参数时，我们必有定义接受输出值的变量，作为输出参数时必有接关键字OUTPUT以便返回值赋给变量，并且后面用SELECT语句显示存储过程的返回结果

    接下来再做深一点的了解，我们看例子

create proc pr_test_1
@id int,
@name char(20)
as
declare @age int
select @age=age from test where [id]=@id and [name]=@name
return @age

    上面的可以必成

create proc pr_test_1
@id int,
@name char(20)
as
return (select @age=age from test where [id]=@id and [name]=@name)

     这里return跟的不是一个整型值而是一个整型表达式，return只可跟（int,smailint和tinyint），这种返回值方法一般只用来返回状态信息给调用程序

    我们可以使用下面语句调用存储过程

declare @age int
exec @age=pr_test_1 9,"zhangsan"
select @age '年龄'

     结果与上个一样

    下面我们来学习使用默认值

create proc pr_test_2
@id int=9,
@name char(20)='zhangsan’
as
select * from test where [id] like @id or [name] like @name
go
exec pr_test_2 10,'lisi

    结果:

    ID  NAME     ***  AGE

    2     lisi         1     21

exec pr_test_2 9
exec pr_test_2

    上二句的结果都是

    ID   NAME    ***  AGE

    1     zhangsan   0    20

    小提示：我们可以给字符型变量设置通配符，这橛就可以使用模糊调用了，不过要注意，字符一定要设为varchar类型才可以。如上面的可以改成@name varchar(20) = ‘%’,这样就可以用exec pr_test_2 9,’z%’调用了。

    上面的可以讲我们是按位置调用调和，现在我们用按名称的方法调用过程，这样的话就可以傅没调用的按默认值。

    看实例:

exec pr_test_2 @names='%l%'

   结果是:

    ID   NAME           ***  AGE

    1     zhangsan            0     20—这条是默认值输出的结果

    2     lisi                   1     21

    4     zhaolui               0     22

   那么现在我们来把存储过程的完整语法写出来:

CREATE PROC[EDURE]        procedure_name[;number]

[

             {@parameter daty_type} [VARYING] [=default] [OUTPUT]

]

[,…….n]

[WITH {RECOMPTLE

| ENCRYPTION

| RECOMFILE,ENCRYFTION}

]

[FROM REPLICATION]

AS

Sql_statement    [,…..n]

     当我们使用WITH Enycryption创建一个存储过程的时候，存储过程代码被加密，然后存储在数据库中。当要调用该存储过程的时候，系统会自动调用，加密的过程没人可以得到源码。当我们要每次使用时重编译存储过程我们可以加With Rcompile.用户可以使用[；number]来创建一组存储过程，当要删除这组存储过程时，只要用一条语句就可以了,看下面的例子:

    假设有pr_test;1,pr_test;2,pr_test;3三个一组存储过程我们可以用

Drop pr_test

    [Varying]可以处理输出参数是Cursor数据类型。

参考：