sql生成练习库

USE yk_test
GO
DROP TABLE yt1
CREATE TABLE yt1
(
	yID int primary key IDENTITY,
	yname varchar(20),
	yaddress varchar(30),
	ymoney money
)
DROP TABLE nameTable
CREATE TABLE nameTable
(
	nId int primary key,
	fname char(2),
	lname1 char(2),
	lname2 char(2)
)
DROP TABLE nameTable
----------------------------
INSERT INTO nameTable 
VALUES(0,'朱','  ','芬')
INSERT INTO nameTable 
VALUES(1,'赵','  ','龙')
INSERT INTO nameTable 
VALUES(2,'钱','小','国')
INSERT INTO nameTable 
VALUES(3,'孙','大','杰')
INSERT INTO nameTable 
VALUES(4,'李','明','宝')
INSERT INTO nameTable 
VALUES(5,'周','近','芬')
INSERT INTO nameTable 
VALUES(6,'王','建','芬')
INSERT INTO nameTable 
VALUES(7,'郑','玉','芬')
INSERT INTO nameTable 
VALUES(8,'陈','文','芬')
INSERT INTO nameTable 
VALUES(9,'潘','伊','芬')
------------------------------
--SELECT * FROM nameTable
---------------------------------
DROP FUNCTION romName
CREATE FUNCTION romName(
@ornum int)
RETURNS varchar(20)  ---reruns注意有个s！
BEGIN
	DECLARE @name varchar(20)
	DECLARE @rnum int
	SET @rnum = @ornum
	SET @name = (SELECT fname FROM nameTable WHERE nId = @rnum%10)
	SET @rnum = (@rnum - @rnum%10)/10
	SET @name = @name + (SELECT lname1 FROM nameTable WHERE nId = @rnum%10)
	SET @rnum = (@rnum - @rnum%10)/10
	SET @name = @name + (SELECT lname2 FROM nameTable WHERE nId = @rnum%10)
	RETURN @name
END
-------------------------
---SELECT dbo.romName(CAST(FLOOR(RAND()*1000) AS int))
-------------------
DECLARE @cc int
SET @cc = 0
WHILE(@cc <500)
BEGIN
	SET @cc = @cc+1
	INSERT INTO yt1
	(yname,ymoney)
	VALUES
	(dbo.romName(CAST(FLOOR(RAND()*1000) AS int)),
	CAST(FLOOR(RAND()*1000) AS int))
END
SELECT * FROM yt1