存储过程应用

 

 

--实例1:只返回单一记录集的存储过程
--要求1：查询表bankMoney的内容的存储过程
create proc query_bankMoney
as
select * from BankMoney
go
exec query_bankMoney

 

--实例2(向存储过程中传递参数)
-- 加入一笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的总金额。
if exists (select name from sysobjects where name = 'insert_bankMoney')
drop proc insert_bankMoney
create proc insert_bankMoney
@param1 varchar(20),
@param2 varchar(20),
@param3 int,
@param4 int output
with encryption--加密
as
insert into BankMoney(userID,Sex,Money) values(@param1,@param2,@param3)
select @param4=sum(Money) from BankMoney where userID='张三'
update BankMoney set Money=1000 where userID='张三'
delete from BankMoney where userID='王五'
go

--怎么调用??
declare @total_price int
exec insert_bankMoney '王五','男',300,@total_price output
print '总金额为'+convert(varchar,@total_price)
go

 

--实例3:使用带有复杂 SELECT 语句的简单过程
--下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数
USE pubs
IF EXISTS (SELECT name,type FROM sysobjects WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDUR 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

--实例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