SQL Procedure 建立

--*********************************************************************************************
-- ABOUT SQL PROCEDURE,
-- 关于SQL存储过程的介绍,建立和使用方法
--*********************************************************************************************


--set nocount on --系统结果消息的输出: 关闭,
--set nocount off --系统结果消息的输出: 开启(默认设置)
--(系统结果消息如: x 行受影响),关闭不必要的系统消息输出可以提高系统运行速度,节省系统资源


--SQL对变量赋值,可以用SET方法或SELECT方法
--[1]如果不涉及到数据表的查询,或对单一变量赋值推荐使用SET,减少查询可以省系统资源; 
--[2]如果涉及到数据表查询或对多个变量赋值操作则用SELECT;


--*********************************************************************************************
--思路:先检查服务器中有没有TESTDB01库,没有输出提示后结束;有时再检查指定的表是否有,没有则创建并插入部分
--数据,有则直接建立三个类型的存储过程:一种不带参数,一种带参数,一种带返回值,然后执行之.
--注意:本程序执行后,根据提示如果相关需要都建立后可以在别处调用存储过程(在别处运行后可以删除对应的表).
--*********************************************************************************************
--[1]检查数据库的有无
use master 
if not exists(select * from sysdatabases where upper([name])='TESTDB01')
print 'No TESTDB01 database in SQL Server 2000 system.'
--不存在指定TESTDB01数据库时
----------------------------------------------------------------------------------
use TESTDB01
go
----------------------------------------------------------------------------------
--创建需要的表
if not exists(select * from sysobjects where upper([xtype])='U' and upper([name])='PROCTABLE01')
begin
--创建指定表 procTable01 (工人信息,不存在时创建)
create table procTable01(
[recID] int identity(1,1), --记录号
[workerID] char(4) not null primary key, --工人工号
[workerName] varchar(20) null, --工人姓名
[jobTitle] varchar(20) null, --工作职位
[DepartmentID] char(4) null, --部门ID
[Sex] varchar(4) null, --性别
[Age] int default 18 check([Age]>0), --工人年龄
[Salary] money null check([Salary]>0), --工资
[addDate] datetime default getdate() --工人入厂日期
)
print 'Table: procTable01 is created. Time: ' + convert(varchar(30),getdate())
--go 会作用域分段,begin...end被分成不同作用域因此导致语法错误;
--提交执行以上命令,以便后面后面增加数据到表
-------------------------------------------------------------
--创建表后增加数据入表
-------------------------------------------------------------
insert procTable01(workerID,workerName,jobTitle,DepartmentID,Sex,Age,Salary,addDate) values ('W001','HCC.D','WEB','DP01','男',24,1050,convert(datetime,'2006-06-12'))
insert procTable01(workerID,workerName,jobTitle,DepartmentID,Sex,Age,addDate) values ('W002','KITER','ELECTRONIC','DP01','男',30,convert(datetime,'2004-06-12'))
insert procTable01(workerID,workerName,jobTitle,DepartmentID,Sex,Age,Salary) values ('W003','HCC.KD','Office','DP03','女',19,950)
print 'Three records insert into table. Time: ' + convert(varchar(30),getdate())
end


----------------------------------------------------------------------------------
--*********************************************************************************************
--存储过程相关问题(5点):
------------------------------
--[1]存储过程有无的判断:存储过程的信息保存在本库的系统表sysobjects表中,临时存储过程保存在tempdb库的系统表
--sysobjects表中,判断其有无可以如下:
--A: [if object_id('存储过程名') is not null ...]
--B: [if exists (select * from sysobjects where [xtype]='P' and [name]='存储过程名')]


------------------------------
--[2]存储过程创建的语法格式如下:
-- CREATE PROCEDURE [#或##]存储过程名 [;组号(或称版本号)]
-- @参数1 参数类型1 =缺省值 [OUTPUT],
-- @参数2 参数类型2 =缺省值 [OUTPUT]
-- [...]
-- [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]
-- AS
-- SQL语句(组,如果是语句组时用 BEGIN...END括起来)


------------------------------
-- [3]存储过程的执行: EXEC 存储过程名 @参数1=值1,@参数2=值2[,...] [;组号/版本号]


------------------------------
-- [4]存储过程的删除: DROP PROCEDURE 存储过程名


------------------------------
-- [5]存储过程的一些注意事项:
-- A:  当创建的是临时存储过程时名称前加一个井号#,当创建的全局临时存储过程(即不局限于一个表或一个库)时名称前
-- 加两个井号##,临时存储过程信息保存在tempdb库中;B:  默认情况下,参数只能代替常量,而不能用于代替表名,列名或
-- 其它数据库对象的名称;C:  在创建和调用时组号前面用分号,在调用时组号应放在参数之后;D:  在创建同名多个组(或
-- 版本)的存储过程时必得从1开始,连续递增,不能跨越,例如:组1没有时不能创建组2或其它组;E:  当删除一个存储过程时,
-- 其名下多个组(如有)将被同时删除;F:  选项RECOMPILE指示每次都编译,即不缓存,选项ENCRYPTION是使过程加密;
-- G:  创建存储过程语句的前后都加GO语句,否则出现"CREATE PROCEDURE 必是第一句"的错误,注GO有作用域分段作用,
-- 同时也会把标识符等分成不同的作用域;H:  有些命令简化:EXEC = EXECUTE, PROC = PROCEDURE
--*********************************************************************************************
--创建存储过程Proc01,不带参数的存储过程,先检查其有无,有则删除后建,
--存储过程功能:仅显示表中的信息
if exists(select * from sysobjects where upper([xtype])='P' and upper([name])='PROC01')
drop proc Proc01
go
create procedure Proc01;1 --同名组(版本)1
as
begin
select * from procTable01 --仅仅显示表procTable01信息
end


go
create procedure Proc01;2 --同名组(版本)2
as
begin
--select * from Table02 --仅仅显示表Table02信息,该表这不提供需事先建立
select top 2 * from procTable01 --代替仅仅显示procTable01表中的前2条信息
end


go
exec Proc01;1 --调用同一个存储过程的不同版本
execute Proc01;2


----------------------------------------------------------------------------------
--创建存储过程Proc02,带参数的存储过程,先检查其有无,有则删除后建,
--存储过程功能:据指定的工人ID号和性别找出相关信息,
if exists(select * from sysobjects where upper([xtype])='P' and upper([name])='PROC02')
drop proc Proc02
go
create procedure Proc02
@workerID char(4),
@sex varchar(4)
as
begin
--if len(ltrim(rtrim(@workerID)))<=0 select @workerID='W000'
--isnull([@workerID],'W001')
--if (len(ltrim(rtrim(@sex)))<=0) select @sex='%%'
--isnull([@sex],'%%')
if len(ltrim(rtrim(@workerID)))<4
select * from procTable01 where [workerID]>=@workerID and [sex] like @sex
else
select * from procTable01 where [workerID]=@workerID and [sex] like @sex
end 
go


exec Proc02 @workerID='',@sex='男'
exec Proc02 @workerID='w003',@sex='女'


----------------------------------------------------------------------------------
--创建存储过程Proc03,带返回值的存储过程,先检查其有无,有则删除后建;
--功能:根据指定的工号查询返回相关提示信息
--参考:http://hi.baidu.com/ligb2006/blog/item/d7f4a7afe10aa8c97dd92afb.html
--参考:http://www.study-code.com/database/sql-server/77276.htm
if exists(select * from sysobjects where upper([xtype])='P' and upper([name])='PROC03')
drop proc Proc03
go
create proc Proc03
@workerID char(4),
@msg varchar(50) output
as
begin
select 'This worker: ' +upper(@workerID)+' age is [ '+convert(varchar(10),[Age])+' ]'+' years.' as [Output message] from procTable01 where workerID=@workerID
--declare @temp int
--select @temp=[age] from procTable01 where workerID=@workerID
--return @temp --RETURN 语句必须返回的是数值型数据,非数值型数据会导致错误;
end
go


declare @msg varchar(50)
exec proc03 @workerID='w003',@msg=''


--*********************************************************************************************
-- 在不需要时可以删除 procTable01 表,本程序可以自动生成.


print 'This is the END.'




---------------------------------------------------------------------------------------------
--模拟建立16位银行卡
--http://blog.csdn.net/achaocai/archive/2006/04/13/662427.aspx
--http://topic.csdn.net/u/20090423/19/00d9c020-2a39-46ae-97ed-13e3dfe28ed1.html


if exists(select * from sysobjects where upper([xtype])='P' and upper([name])='GETCARDNO')
drop proc GetCardNo
go


create proc GetCardNo
@cardNo varchar(19) output
as
begin
declare @x char(1)
set @cardNo=''
set @x=''


createNum: --标号
select @x=char(convert(int,(rand()*10 + 48)))


if(len(@cardNo)=4 or len(@cardNo)=9 or len(@cardNo)=14)
@cardNo=@cardNo+@x+' '
else
@cardNo=@cardNo+@x
if(len(@cardNo)<19) goto createNum
return @cardNo
end










--释放资源
---------------------------------------------------------------------------------------------
drop table procTable01
drop proc Proc01
drop proc Proc02
drop proc Proc03
drop proc GetCardNo