sql存储过程实例与相关基础知识

文章分享一篇对入门者有很大帮助的sql存储过程实现与相关基础知识，如果是学这方面的同学可以看看哈。

先看一下存储过程的相关知识吧

一、创建存储过程
　　create procedure 存储过程名称
　　参数列表
　　as
　　begin
　　　　……
　　end
二、调用存储过程
　　call 存储过程名称()
三、删除存储过程
　　drop procedure 存储过程名称
　　注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。
四、其他常用命令
　　1.show procedure status
　　显示中所有存储过程基本信息
　　2.show create procedure 存储过程名称
　　显示某个存储过程详细信息

五、注释
　　单行注释：--
　　多行注释：/*  注释内容*/
六、区块定义
　　begin
　　　　代码块
　　end
　　这里的begin和end相当于C语言中的{和}
七、执行其他存储过程EXEC
　　例如：EXEC dbo.[SalesByYear] @BeginDate='1/01/90',@EndDate='1/01/08';

------------------------------数据类型------------------------------
一、基本数据类型
　　可以在SQL Management中查看到相关的数据类型。
二、变量
　　1.自定义变量：declare @变量名  数据类型;
　　2.变量赋值：set @变量名=值;
三、运算符
　　1.算术运算符
　　　　+、-、*、/、DIV(整除  例如：10 DIV 3值为3)、%（取模）
　　2.比较运算符
　　　　>、=、between  and、not between and、in、not in、=、|!=、(严格比较两个NULL值是否相等   例如：NULLNULL true)、LIKE 、REGEXP、is null、IS NOT NULL
　　3.位运算符
　　　　|、&、>、~
------------------------------流程控制------------------------------
一、顺序结构
二、分支结构
　　if    else
　　case
三、循环结构
　　for循环
　　while循环
　　loop循环
　　repeat until循环
------------------------------输入和输出------------------------------
三种参数类型：INPUT、OUTPUT、INPUTOUTPUT
　　INPUT输入参数
　　　　表示参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
　　OUTPUT输出参数。
　　　　表示该参数的值在存储过程内部被改变后可返回。
　　INPUTOUTPUT输入输出参数
　　　　表示该参数的值在调用时指定，并且可被改变和返回。

学习开始了
要求1：查询表bankMoney的内容的存储过程

代码如下	复制代码
create procedure sp_query_bankMoney as * from bankMoney go exec sp_query_bankMoney

注* 在使用过程中只需要把中的SQL语句替换为存储过程名，就可以了很方便吧！

　　实例2（向存储过程中传递参数）：

加入一笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的总金额。

代码如下

复制代码

Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output with encryption ---------加密 as insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4) select @param5=sum(Money) from bankMoney where userID='Zhangsan' go 在SQL Server查询分析器中执行该存储过程的方法是： declare @total_price int exec insert_bank '004','Zhangsan','男',100,@total_price output print '总余额为'+convert(varchar,@total_price) go

在这里再??乱幌麓娲⒐?痰?种传回值（方便正在看这个例子的朋友不用再去查看语法内容）:

1.以Return传回整数
2.以output格式传回参数
3.Recordset

传回值的区别:

output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。

实例3：使用带有复杂 SELECT 语句的简单过程

　　下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。

　　

代码如下	复制代码
USE pubs IF EXISTS (SELECT name FROM sysobjects          WHERE name = 'au_info_all' AND type = 'P')    DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name    FROM authors a INNER JOIN titleauthor ta       ON a.au_id = ta.au_id INNER JOIN titles t       ON t.title_id = ta.title_id INNER JOIN publishers p       ON t.pub_id = p.pub_id GO

　　au_info_all 存储过程可以通过以下方法执行：

　　

代码如下	复制代码
EXECUTE au_info_all -- Or EXEC au_info_all

　　如果该过程是批处理中的第一条语句，则可使用：

　　

代码如下	复制代码
au_info_all

　　实例4：使用带有参数的简单过程

代码如下	复制代码
CREATE PROCEDURE au_info    @lastname varchar(40),    @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name    FROM authors a INNER JOIN titleauthor ta       ON a.au_id = ta.au_id INNER JOIN titles t       ON t.title_id = ta.title_id INNER JOIN publishers p       ON t.pub_id = p.pub_id    WHERE au_fname = @firstname       AND au_lname = @lastname GO

　　au_info 存储过程可以通过以下方法执行：

　

代码如下	复制代码
EXECUTE au_info 'Dull', 'Ann' -- Or EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull' -- Or EXEC au_info 'Dull', 'Ann' -- Or EXEC au_info @lastname = 'Dull', @firstname = 'Ann' -- Or EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

　　如果该过程是批处理中的第一条语句，则可使用：

　

代码如下	复制代码
au_info 'Dull', 'Ann' -- Or au_info @lastname = 'Dull', @firstname = 'Ann' -- Or au_info @firstname = 'Ann', @lastname = 'Dull'

实例5：使用带有通配符参数的简单过程

代码如下	复制代码
CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta    ON a.au_id = ta.au_id INNER JOIN titles t    ON t.title_id = ta.title_id INNER JOIN publishers p    ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname    AND au_lname LIKE @lastname GO

　　au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：

代码如下	复制代码
EXECUTE au_info2 -- Or EXECUTE au_info2 'Wh%' -- Or EXECUTE au_info2 @firstname = 'A%' -- Or EXECUTE au_info2 '[CK]ars[OE]n' -- Or EXECUTE au_info2 'Hunter', 'Sheryl' -- Or EXECUTE au_info2 'H%', 'S%' 　　= 'proc2'