sql创建练习库2存储过程

---------------------------------------------------------创建数据库-------------------USE masterGOIF EXISTS(SELECT * FROM sys.databases WHERE name = 'testDB')DROP DATABASE testDBGOCREATE DATABASE testDBGO-----------------------------------------------------------创建表---------------------USE testDBIF EXISTS(SELECT * FROM sys.objects WHERE name = 'testTable')DROP TABLE testTableGOCREATE TABLE testTable(tNum int IDENTITY(1,1) PRIMARY KEY,tName varchar(20) NOT NULL,tBirthDay date CHECK(tBirthDay < GETDATE() AND tBirthDay > '1914-1-1'),tAddress varchar(30) DEFAULT 'No Record',tID char(18),tSex char(1) CHECK(tSex = 'F' or tSex = 'M' or tSex = '?'),tDrop money,--CONSTRAINT tCon UNIQUE (tID))GOALTER TABLE testTableDROP COLUMN tDropALTER TABLE testTableADD tBalance int DEFAULT 0GO--字典表1USE testDBIF EXISTS(SELECT * FROM sys.objects WHERE name = 'nameDir')DROP TABLE nameDirGOCREATE TABLE nameDir(nID int NOT NULL PRIMARY KEY,fName varchar(4),lName1 char(2),lName2 char(2),)GOINSERT INTO nameDir VALUES(0,'赵',' ','龙')INSERT INTO nameDir VALUES(1,'钱','明','天')INSERT INTO nameDir VALUES(2,'孙','小','杰')INSERT INTO nameDir VALUES(3,'李','建','宝')INSERT INTO nameDir VALUES(4,'周','龙','明')INSERT INTO nameDir VALUES(5,'吴','思','芬')INSERT INTO nameDir VALUES(6,'郑','文','灵')INSERT INTO nameDir VALUES(7,'王','伊','茜')INSERT INTO nameDir VALUES(8,'陈','玲','雪')INSERT INTO nameDir VALUES(9,'唐',' ','玉')----------------------------------------随机生成信息表----------方式一：储存过程IF EXISTS(SELECT * FROM sys.objects WHERE name = 'proRomName')DROP PROC proRomNameGOCREATE PROC proRomName@name varchar(20) outputAS--DECLARE @name varchar(20)DECLARE @rnum intDECLARE @tnum intSET @rnum = CAST(floor(RAND() * 1000) AS int) --区别于caseSET @name = (SELECT fName FROM nameDir WHERE nID = @rnum / 100)SET @tnum = (@rnum / 10) % 10IF(@tnum != 0 AND @tnum != 9)BEGINSET @name = @name + (SELECT lName1 FROM nameDir WHERE nID = @tnum)ENDIF(@tnum < 5)BEGINSET @tnum = (@rnum % 10) /2SET @name = @name + (SELECT lName2 FROM nameDir WHERE nID = @tnum/2)SET @tnum = 1ENDELSE BEGINSET @tnum = (@rnum % 10)/2 + 5SET @name = @name + (SELECT lName2 FROM nameDir WHERE nID = @tnum)SET @tnum = 2ENDGOUSE testDBGODECLARE @i intSET @i = 0;DECLARE @n varchar(20)WHILE @i < 500BEGINEXEC proRomName @n outputINSERT INTO testTable(tName)VALUES(@n)SET @i = @i + 1ENDSELECT * FROM testTable--SELECT * FROM nameDir