数据库 存储过程的建立 调用 加密

** 如果对数据库的基本增、删、改、查不了解请先阅读另一篇博客**

** **数据库的建立、增、删、改、查_做自己的靠山的博客-CSDN博客

一、存储过程的建立

（1）语法：

 create  procedure(proce)  名字  参数1,参数2……
  as
       基本的增删改查都可以接在as后

as后可以可跟sql的 增删改查

（2）参数定义

定义参数、变量必须有@符号

• @X 局部变量
• @@X全局变量

（3）引出存储过程的使用

思考下面问题：

在学生表中查询指定学号的学生的全部信息？

这时我们可以先简化问题:

查询学号为 201215121 的学生信息 ，那就是一个简单的单表查询问题了

代码如下：

select *
  from Student
  where  Sno='201215121'

回到最初的问题指定学号，

• 我们可以先建立一个存储过程
• 在调用存储过程时输入指定的实参赋给形参即可解决

存储过程的定义：

存储过程就是把已知的换成形参，并定义用到的形参

create procedure select_name @Sno char(9)
as
  select *
  from Student
  where  Student.Sno=@Sno 

 

调用实参：

这里调用就不详讲，下面会详细讲解

exec select_name1 '201215121'

（4）存储过程定义建立小结

存储过程定义好，就像是鞭炮做好了，要点火才会有效果，所以存储过程定义好要想有结果，一定要调用

二，存储过程的调用

（1）对应赋值调用

execute(exec)  存储过程名  '实参1','实参2',……

eg：调用前面定义的存储过程

exec select_name1 '201215121'

（2）直接调用

execute(exec)  存储过程名  形参1='实参1',形参2='实参2',……

eg：调用前面定义的存储过程

exec select_name1 @Sno='201215121'

（3）间接调用

Declare 参数
Set 参数 =‘存储过程名’
Exectue（exec）参数  +（上述两种调用方法都可以）

eg：调用前面定义的存储过程

declare @str char(20)
set @str='select_name1'
exec @str @Sno='201215121'

三、加密

使用WITH ENCRYPTION选项。WITH ENCRYPTION子句用于对用户隐藏存储过程的文本。

eg：创建一个加密的存储过程和一个不加密的存储过程

//加密存储过程
create procedure select_name1 @Sno char(9) WITH ENCRYPTION

as
  select *
  from Student
  where  Student.Sno=@Sno 

//不加密的存储过程
 create procedure select_name @Sno char(9)
as
  select *
  from Student
  where  Student.Sno=@Sno 
  

使用 sp_helptext 系统存储过程获取关于加密过程的信息和没加密的存储过程，运行结果如下：

  EXEC sp_helptext select_name1
  EXEC sp_helptext select_name

四、特殊的存储过程

（1）带有output参数的存储过程

创建一个存储过程do_insert，作用是向student表中插入一行数据。创建另外一个存储过程do_action，在其中调用第一个存储过程，并根据条件处理该行数据，处理后输出相应的信息。

  CREATE PROCEDURE do_insert
  AS
    INSERT INTO Student 
    VALUES('091201', '陶伟', '男', 21, 'ma');

 
  
  CREATE PROCEDURE do_action @X char(2), @STR CHAR(8) OUTPUT
AS
    BEGIN 
        EXEC do_insert
        IF @X='女'
        BEGIN
            UPDATE student SET sname='刘英', ssex='女' WHERE sno='091201'
            SET @STR='修改成功'
        END
        ELSE
            IF @X='男'
            BEGIN
                DELETE FROM student WHERE sno='091201'
                SET @STR='删除成功'
            END
    END

可以类比c语言中函数调用来理解

调用该存储过程：

declare @str char(8)
exec do_action @X='女',@str=@str output
select @str

注意：

•      这个里面的begin可以理解为c语言里面的{
  

•      End可以理解为 }  也就是这个语句的作用范围
  

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

查询某个姓 的学生信息

CREATE PROCEDURE st_info @sname varchar(30) = '李%'
    AS 
        SELECT student.sno,sname,cname,grade
            FROM  student   JOIN  sc  JOIN course 
            ON course.cno= sc.cno 
                                            ON student.sno =sc.sno  
            WHERE sname LIKE @sname

当存储过程定义的时候就直接给参数赋值了（就相当于默认参数，如上述例子就是默认为姓李的学生信息）

调用这种类型的存储过程的时候就有几种情况了

1. 有默认，调用没有传实参 （就用默认参数）
2. 有默认，调用也传实参 （就用实参）
3. 都没得，（查不出来报不报错我没试）

五、存储过程的修改、删除

（1）修改

修改和创建类似，就是把create 改为 alter 可参照基本表的相关操作（查看文末链接）

eg:将定义好的的存储过程student_info1的第一个参数进行修改

ALTER PROCEDURE student_info1
    @sno char(6),@cname char(16) 
    AS 
        SELECT student.sno, cname, grade
            FROM  sc, course            
WHERE  sno=@sno AND cname=@cname

（2）存储过程的删除

eg：

删除XSCJ数据库中的student_info存储过程。

IF EXISTS(SELECT name FROM sysobjects WHERE name='student_info')
    DROP PROCEDURE student_info

六、常用存储过程示例

（1）as后跟 增加 语句

eg：建立一个向SC表中插入指定学号、指定课程号、指定成绩的存储过程

create proc insert_sc @sno char(12),@cno char(4),@grade smallint
as
insert into SC 
values(@sno,@cno,@grade)

//调用

exec insert_sc @sno='201230402101',@cno='7',@grade=100

（2）as后跟删除语句

eg：定义删除SC中指定学号的学生信息的存储过程

create proc delete_sc @sno char(12)
as
delete from sc
where Sno=@sno

//调用
exec delete_sc @sno='201230402109'

（3）as后跟修改语句

eg：定义将指定学号的学生成绩加10分的存储过程

create proc update_sc @sno char(12)
as
update  sc
set
Grade=Grade+10
where Sno=@sno

//调用
exec update_sc @sno='201230402108'

（4）as后给查寻语句

eg：定义查询Student表中指定学号的学生信息的存储过程

create proc select_student @sno char(12)
as
select *
from Student
where Sno=@sno

//调用
exec select_student @sno='201230402101'

结束：