--创建存储过程
--A. 创建使用参数的存储过程
CREATE PROC au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM table1
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
--B. 创建使用参数默认值的存储过程
CREATE PROC pub_info2
@pubname varchar(40) = 'Algodata Infosystems'
AS
SELECT au_lname, au_fname, pub_name
FROM table2
WHERE @pubname = p.pub_name
--C. 执行用显式值替代参数默认值的存储过程
--在下例中,存储过程 showind2 的 @table 参数默认值是 titles。
CREATE PROC showind2
@table varchar(30) = 'titles'
AS
SELECT
TABLE_NAME = table2.name,
INDEX_NAME = table1.name,
INDEX_ID = indid
FROM table1 INNER JOIN table2
ON table2.id = table1.id
WHERE table2.name = @table
--D. 使用参数默认值 NULL 创建存储过程
--参数默认值可以是 NULL 值。在这种情况下,如果未提供参数,则 SQL Server 将根据存储过程的其它语句执行存储过程。不会显示错误信息。
--过程定义还可指定当不给出参数时要采取的其它某种措施。例如:
CREATE PROC showind3
@table varchar(30) = NULL
AS
IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT
TABLE_NAME = table1.name,
INDEX_NAME = table2.name,
INDEX_ID = indid
FROM table2 INNER JOIN table1
ON table1.id = table2.id
WHERE table1.name = @table
--E. 使用包含通配符的参数默认值创建存储过程
--如果存储过程将参数用于 LIKE 关键字,那么默认值可包括通配符(%、_、[] 和 [^])。例如,可将 showind 修改为当不提供参数时显示有关系统表的信息:
CREATE PROC showind4
@table varchar(30) = 'sys%'
AS
SELECT
TABLE_NAME = table1.name,
INDEX_NAME = table2.name,
INDEX_ID = indid
FROM table2 INNER JOIN table1
ON table1.id = table2.id
WHERE table1.name LIKE @table
--创建游标
--新建游标
declare youbiao cursor
for
select SubID,SubType,SubMoney,SubYear,ChargeDep
from table2
where pid=@feeStandard
--打开游标
open youbiao
--从游标里取出数据给 变量 赋值
fetch next from youbiao
into @subID,@subType,@subMoney,@subYear,@chargeDep
begin
--判断游标的状态
-- 0 fetch语句成功
-- -1 fetch语句失败或此行不在结果集中
-- -2 被提取的行不存在
while @@fetch_status = 0
begin
set @stuPayData = (select isnull(count(*),0) from table1 where SubYear=@subYear and SubID=@subID and StuID=@studentID )
if(@stuPayData = 0)
begin
insert into table1(StuID, DepName, ClassName, FeeID, SubID, SubType, ShouldPayment, SubYear, ChargeDep, CUser, CTime)
select @studentID, DepName, ClassName, @feeStandard, @subID, @subType, @subMoney, @subYear, @chargeDep, @cuser, getdate() from StudentInfo where id=@studentID
end
--用游标去取下一条记录
fetch next from youbiao into @subID,@subType,@subMoney,@subYear,@chargeDep
end
end
--关闭游标
close youbiao
--撤销游标
deallocate youbiao
END
--在存储过程中使用游标调用其他存储过程
declare cur_name cursor for select type from table1; --定义游标
declare @type varchar(50);
open cur_name;
fetch next from cur_name into @type;
while @@fetch_status =0
begin
if @type = '其他存储过程'
begin
exec 其他存储过程;
end
else if @type = 函数
begin
select dbo.函数();
end
fetch next from cur_name into @type;
end
close cur_name;
deallocate cur_name;
--A. 创建使用参数的存储过程
CREATE PROC au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM table1
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
--B. 创建使用参数默认值的存储过程
CREATE PROC pub_info2
@pubname varchar(40) = 'Algodata Infosystems'
AS
SELECT au_lname, au_fname, pub_name
FROM table2
WHERE @pubname = p.pub_name
--C. 执行用显式值替代参数默认值的存储过程
--在下例中,存储过程 showind2 的 @table 参数默认值是 titles。
CREATE PROC showind2
@table varchar(30) = 'titles'
AS
SELECT
TABLE_NAME = table2.name,
INDEX_NAME = table1.name,
INDEX_ID = indid
FROM table1 INNER JOIN table2
ON table2.id = table1.id
WHERE table2.name = @table
--D. 使用参数默认值 NULL 创建存储过程
--参数默认值可以是 NULL 值。在这种情况下,如果未提供参数,则 SQL Server 将根据存储过程的其它语句执行存储过程。不会显示错误信息。
--过程定义还可指定当不给出参数时要采取的其它某种措施。例如:
CREATE PROC showind3
@table varchar(30) = NULL
AS
IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT
TABLE_NAME = table1.name,
INDEX_NAME = table2.name,
INDEX_ID = indid
FROM table2 INNER JOIN table1
ON table1.id = table2.id
WHERE table1.name = @table
--E. 使用包含通配符的参数默认值创建存储过程
--如果存储过程将参数用于 LIKE 关键字,那么默认值可包括通配符(%、_、[] 和 [^])。例如,可将 showind 修改为当不提供参数时显示有关系统表的信息:
CREATE PROC showind4
@table varchar(30) = 'sys%'
AS
SELECT
TABLE_NAME = table1.name,
INDEX_NAME = table2.name,
INDEX_ID = indid
FROM table2 INNER JOIN table1
ON table1.id = table2.id
WHERE table1.name LIKE @table
--创建游标
--新建游标
declare youbiao cursor
for
select SubID,SubType,SubMoney,SubYear,ChargeDep
from table2
where pid=@feeStandard
--打开游标
open youbiao
--从游标里取出数据给 变量 赋值
fetch next from youbiao
into @subID,@subType,@subMoney,@subYear,@chargeDep
begin
--判断游标的状态
-- 0 fetch语句成功
-- -1 fetch语句失败或此行不在结果集中
-- -2 被提取的行不存在
while @@fetch_status = 0
begin
set @stuPayData = (select isnull(count(*),0) from table1 where SubYear=@subYear and SubID=@subID and StuID=@studentID )
if(@stuPayData = 0)
begin
insert into table1(StuID, DepName, ClassName, FeeID, SubID, SubType, ShouldPayment, SubYear, ChargeDep, CUser, CTime)
select @studentID, DepName, ClassName, @feeStandard, @subID, @subType, @subMoney, @subYear, @chargeDep, @cuser, getdate() from StudentInfo where id=@studentID
end
--用游标去取下一条记录
fetch next from youbiao into @subID,@subType,@subMoney,@subYear,@chargeDep
end
end
--关闭游标
close youbiao
--撤销游标
deallocate youbiao
END
--在存储过程中使用游标调用其他存储过程
declare cur_name cursor for select type from table1; --定义游标
declare @type varchar(50);
open cur_name;
fetch next from cur_name into @type;
while @@fetch_status =0
begin
if @type = '其他存储过程'
begin
exec 其他存储过程;
end
else if @type = 函数
begin
select dbo.函数();
end
fetch next from cur_name into @type;
end
close cur_name;
deallocate cur_name;