sql创建练习库2存储过程

-------------------------------------------
--------------创建数据库-------------------
USE master
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'testDB')
	DROP DATABASE testDB
GO

CREATE DATABASE testDB
GO
-------------------------------------------
----------------创建表---------------------
USE testDB
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'testTable')
	DROP TABLE testTable
GO

CREATE 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)
)
GO

ALTER TABLE testTable
DROP COLUMN tDrop
ALTER TABLE testTable
ADD tBalance int DEFAULT 0
GO
--字典表1
USE testDB
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'nameDir')
	DROP TABLE nameDir
GO

CREATE TABLE nameDir
(
	nID int NOT NULL PRIMARY KEY,
	fName varchar(4),
	lName1 char(2),
	lName2 char(2),
)
GO

INSERT 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 proRomName
GO
CREATE PROC proRomName
@name varchar(20) output
AS
	--DECLARE @name varchar(20)
	DECLARE @rnum int
	DECLARE @tnum int
	SET @rnum = CAST(floor(RAND() * 1000) AS int) --区别于case
	SET @name = (SELECT fName FROM nameDir WHERE nID = @rnum / 100)
	SET @tnum = (@rnum / 10) % 10
	IF(@tnum != 0 AND @tnum != 9)
	BEGIN
		SET @name = @name + (SELECT lName1 FROM nameDir WHERE nID = @tnum)
	END
	IF(@tnum < 5)
	BEGIN
		SET @tnum = (@rnum % 10) /2
		SET @name = @name + (SELECT lName2 FROM nameDir WHERE nID = @tnum/2)
		SET @tnum = 1
	END
	ELSE 
	BEGIN
		SET @tnum = (@rnum % 10)/2 + 5
		SET @name = @name + (SELECT lName2 FROM nameDir WHERE nID = @tnum)
		SET @tnum = 2
	END	
	
GO

USE testDB
GO






DECLARE @i int
SET @i = 0;
DECLARE @n varchar(20)
WHILE @i < 500
BEGIN
	EXEC proRomName @n output
	INSERT INTO testTable
	(tName)
	VALUES
	(@n)
	SET @i = @i + 1
END

SELECT * FROM testTable



--SELECT * FROM nameDir