SQL语法整理[5]——存储过程

 

SQL语法整理[5]——存储过程

创建一个快速输入学生信息的存储过程

/*创建存储过程i_student*/

USE   school

GO

CREATE   PROCEDURE    i_student

@id INT,@name CHAR(30),@gender CHAR(2),@age INT,@grade  SMALLINT,

@class SMALLINT,@teacher_id INT,@parents_id INT,@graduation CHAR(20),

@city CHAR(10),@cardno CHAR(20)

AS

INSERT   INTO      student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)

VALUES

(@id,@name,@gender,@age,@grade,@class,@teacher_id,@parents_id,

@graduation,@city,@cardno)

GO

 

/*使用存储过程i_student录入信息*/

EXECUTE     i_student

2006012,'Lily','女',23,1,1,103,2003,'尚未毕业','天津','220200030304304043'

GO

什么是存储过程

存储过程是一套已经预先编译好的SQL代码,是SQL语句和可选控制语句的集合及一个独立的数据库对象.存储过程在数据库内可以由应用程序调用执行,而且允许用户声明变量、有条件执行以及其他强大的编程工程。由于存储过程是已经编译好的代码，所以执行的时候不需要分析也不需要再次编译，能够提高程序的运行效率。

存储过程可以包含程序流、逻辑以及对数据库的查询。可以接受参数、输出参数、返回单个或者多个结果集以及返回值。

 

存储过程的类型：

     a.系统存储过程：存储在master数据库(以SP_为前缀)，提供了有效的查询系统表  的方法，可以实现许多系统管理功能。

     b.本地存储过程：在每个数据库中用户创建的存储过程是通过SQL语句创建的。

     c.临时存储过程

     d.远程存储过程

     e.扩展存储过程

 

 

用户自定义存储过程

CREATE {PROC|PROCEDURE} procedure_name @parameter data_type

[WITH ENCRYPTION]

AS <sql_statement>[;][…n]

 

不带参数的存储过程

例如：现在每天学校需要将学生表中的数据，添加时间戳以后备份到学生备份表中。

/*创建备份学生表格信息的存储过程*/

/*创建一个名称为p_bak_student存储过程*/

USE   school

GO

CREATE   PROCEDURE  p_bak_student

AS

INSERT   INTO    student_bak

(id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,[sysdate])

SELECT

id,[name],gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno,getdate()

FROM   student

GO

 

使用存储过程来实现查询

/*创建名为s的存储过程来存储查询学生表格信息*/

USE    school

GO

CREATE      PROCEDURE   s

AS

SELECT   *    FROM   student

GO

 

/*不是用EXECUTE运行名称为s的存储过程*/

s

go

 

便于用户每次都不使用复杂的SELECT语句来进行查询。

可以直接运行存储过程名称。

 

 

带简单参数的存储过程

/*带学号参数的存储过程*/

CREATE   PROCEDURE  s

@id   int/*参数*/

AS

SELECT   *    FROM  student

WHERE id=@id

GO

 

/*输入参数2001002的学生号，查询学号2001002学生的信息*/

s  2001002/调用形式/

GO

 

带有通配符参数的存储过程

例如：通过输入学生的模糊名称，查找其相应的班主任。

/*创建名称为query_student的存储过程*/

CREATE   PROCEDURE   query_student

@student_name   varchar(20)=’%’

AS

SELECT     s.id    AS   ‘学生号’,

s.name       AS      ‘学生名’,

s.teacher_id   AS      ‘老师编号’,

t.name        AS        ‘老师姓名’

FROM

student  s

INNER  JOIN

teachers  t

on

s.teacher_id=t.id

WHERE  s.name    LIKE   @student_name

GO

/*执行query_student存储过程*/

EXECUTE    query_student   ‘小%’

GO

 

/*执行query_student存储过程*/

EXECUTE    query_student

GO/*返回所有行

*/

 

@student_name   varchar(20)=’%’是参数默认值的写法,因此执行EXECUTE    query_student返回所有行.

 

查看存储过程源代码

EXECUTE   SP_HELPTEXT   i_student

 

 

为存储过程加密

/*修改存储过程，将存储过程i_student加密*/

ALTER   PROCEDURE    i_student

@id INT,@name CHAR(30),@gender CHAR(2),@age INT,@grade  SMALLINT,

@class SMALLINT,@teacher_id INT,@parents_id INT,@graduation CHAR(20),

@city CHAR(10),@cardno CHAR(20)

WITH  ENCRYPTION

AS

INSERT   INTO      student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city,cardno)

VALUES

(@id,@name,@gender,@age,@grade,@class,@teacher_id,@parents_id,

@graduation,@city,@cardno)

GO

 

通过存储过程传递参数

CREATE {PROC|PROCEDURE} [schema_name.] procedure_name[;number]

[{@parameter[type_schema_name.]data_type}                   

[VARYING][=default][ [OUT[PUT]

default:定义参数的默认值.如果定义了参数的默认值,则在调用存储过程时可以不指定该值.默认值必须是一个常量或者是NULL.如果存储过程在LIKE字句中使用参数，则默认值中可以包括通配符(%,_,[],[^]).

OUTPUT:指出该参数是一个输出类型,是一个返回调用过程。

 

例如：输入老师编号参数，返回这个老师管理下的所有学生数.

/*返回老师管理下所有学生数*/

USE    school

GO

CREATE   PROCEDURE     sum_student

@teacher_id   INT,@sum_student   INT  OUTPUT

AS

SELECT  @sum_student=COUNT(ID)       ---COUNT是统计函数

FROM student

WHERE

teacher_id=@teacher_id

GO

 

/*调用存储过程sum_student,输入老师编号102*/

USE   school

GO

DECLARE  @sum_student   INT      ---@sum_student为声明的变量

EXECUTE  sum_student   102,@sum_student OUTPUT

GO

 

修改存储过程

ALTER {PROC|PROCEDURE} [schema_name.] procedure_name[;number]

[{@parameter[type_schema_name.]data_type}                   

[VARYING][=default][ [OUT[PUT]                              

[WITH ENCRYPTION]                                           

AS                                                          

<sql_statement>[…n]  

 

 

重新命名以及删除存储过程

重新命名存储过程的方法是使用系统存储过程SP_RENAME来实现的。

删除存储过程的语法如下：

DROP PROCEDURE procedure_name

 

 

——大二的期末考试整理 [e-mail]