/* @(#) generic/localization/scripts/sjis/installjpubs	195.1	9/6/95 */
/* Has primary and foreign keys, plus text and image tables*/
/* primary  foreign L[gp, text  image e[u̒ǉ */

/*
** raiserror Messages for installjpubs [Total 3]
**
** 19527, "The '%1!' database cannot be created. Terminating the installation."
** 19528, "The '%1!' database does not exist. Terminating the installation."
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
*/

use master
go
set nocount on

if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	drop database jpubs
end
go
print 'f[^x[X "jpubs" 쐬Ă܂B'
if (@@maxpagesize = 2048)
	create database jpubs on default=4
else
	create database jpubs on default
go
if not exists (select * from master.dbo.sysdatabases
	        where name = "jpubs")
begin
	/*
	** 19527, "The '%1!' database cannot be created. Terminating the installation."
	*/
	raiserror 19527, "jpubs"
	select syb_quit()
end
go
sp_dboption jpubs, "trunc log on chkpt", true
go
set dateformat mdy
go
use jpubs
go
if (db_name() != "jpubs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "jpubs"
	select syb_quit()
end
go
checkpoint
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	execute sp_addtype id, "varchar(11)", "not null"
	execute sp_addtype tid, "varchar(6)", "not null"
end
else
begin
	/*
	** 19528, "The '%1!' database does not exist. Terminating the installation."
	*/
	raiserror 19528, "jpubs"
	select syb_quit()
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table 
	(҃R[h id not null,
	 varchar(40) not null,
	߂ varchar(60) not null,
	O varchar(20) not null,
	Ȃ܂ varchar(30) not null,
	db char(17) not null,
	Z varchar(40) null,
	イ varchar(60) null,
	s撬 varchar(20) null,
	傤 varchar(30) null,
	s{ varchar(20) null,
	Ƃǂӂ varchar(30) null,
	country varchar(12) null,
	 varchar(12) null,
	X֔ԍ char(10) null)
end
go
grant select on  to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table oŎ
	(oŃR[h char(4) not null,
	oŌ varchar(40) null,
	ς varchar(60) null,
	s撬 varchar(20) null,
	傤 varchar(30) null,
	s{ varchar(20) null,
	Ƃǂӂ varchar(30) null)
end
go
grant select on oŎ to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table 쌠
	(^CgR[h tid not null,
	l int null,
	l int null,
	 int null)
end
go
grant select on 쌠 to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table ̔
	(XR[h char(4) not null,
	ԍ varchar(20) not null,
	t datetime not null)
end
go
grant select on ̔ to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table ̔ڍ
	(XR[h char(4) not null,
	ԍ varchar(20) not null,
	^CgR[h tid not null,
	 smallint not null,
	 float not null)
end
go
grant select on ̔ڍ to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table ҃^Cg
	(҃R[h id not null,
	^CgR[h tid not null,
	ғ tinyint null,
	ŗ int null)
end
go
grant select on ҃^Cg to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table ^Cg
	(^CgR[h tid not null,
	薼 varchar(80) not null,
	 char(12) not null,
	oŃR[h char(4) null,
	i money null,
	O money null,
	̔ int null,
	e varchar(200) null,
	oœ datetime not null,
	_ bit not null )
end
go
grant select on ^Cg to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table X
	(XR[h char(4) not null,
	X varchar(40) null,
	Ă߂ varchar(60) null,
	XZ varchar(40) null,
	݂イ varchar(60) null,
	s撬 varchar(20) null,
	傤 varchar(30) null,
	s{ varchar(20) null,
	Ƃǂӂ varchar(30) null,
	 varchar(12) null,
	country varchar(12) null,
	X֔ԍ char(10) null,
	x varchar(12) null)
end
go
grant select on X to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table 
	(   varchar(40) not null,
	XR[h         char(4) null,
	          smallint null,
	         smallint null,
	        float not null)
end
go
grant select on  to public
go
if exists (select * from master.dbo.sysdatabases
                where name = "jpubs")
begin
	create table ҏё
	(҃R[h	char(11) not null,
	ʐ^		image null,
	f[^`	char(11) null,
	oCgTCY	int null,
	cf	char(14) null,
	f	char(14) null)
end
go
grant select on ҏё to public
go
if exists (select * from master.dbo.sysdatabases 
                where name = "jpubs") 
begin 
	create table `
	(҃R[h	id not null,
	 Rs[	text null)
end
go 
grant select on ` to public 
go 
execute sp_primarykey ^Cg, ^CgR[h
execute sp_primarykey ҃^Cg, ҃R[h, ^CgR[h
execute sp_primarykey , ҃R[h
execute sp_primarykey oŎ, oŃR[h
execute sp_primarykey 쌠, ^CgR[h
execute sp_primarykey ̔, XR[h, ԍ
execute sp_primarykey ̔ڍ, XR[h, ԍ
execute sp_primarykey X, XR[h
execute sp_primarykey , , XR[h	 /* problem? H */
execute sp_primarykey ҏё, ҃R[h
execute sp_primarykey `, ҃R[h
go
execute sp_foreignkey ҃^Cg, ^Cg, ^CgR[h
execute sp_foreignkey ҃^Cg, , ҃R[h
execute sp_foreignkey 쌠, ^Cg, ^CgR[h
execute sp_foreignkey ̔, X, XR[h
execute sp_foreignkey ̔ڍ, ^Cg, ^CgR[h
execute sp_foreignkey ̔ڍ, ̔, XR[h, ԍ
execute sp_foreignkey ^Cg, oŎ, oŃR[h
execute sp_foreignkey , X, XR[h
execute sp_foreignkey ҏё, , ҃R[h
execute sp_foreignkey `, , ҃R[h
go

create unique clustered index oŌo
on oŎ (oŃR[h)
go
create unique clustered index ҃R[ho
on  (҃R[h)
go
create nonclustered index Җo
on  (, O)
go
create unique clustered index ^CgR[ho
on ^Cg (^CgR[h)
go
create nonclustered index ^Cgo
on ^Cg (薼)
go
create unique clustered index ҃^Cgo
on ҃^Cg (҃R[h, ^CgR[h)
go
create nonclustered index ҃R[ho
on ҃^Cg (҃R[h)
go
create nonclustered index ^CgR[ho
on ҃^Cg (^CgR[h)
go
create unique clustered index ̔o
on ̔ (XR[h, ԍ)
go
create nonclustered index ^CgR[ho
on ̔ڍ (^CgR[h)
go
create nonclustered index ̔ڍ׌o
on ̔ڍ (XR[h, ԍ)
go
create nonclustered index ^CgR[ho
on 쌠 (^CgR[h)
go
create default ftHg
as ""
go
sp_bindefault ftHg, "^Cg."
go
create default ftHgt
as getdate()
go
sp_bindefault ftHgt, "^Cg.oœ"
go
create default ftHgdb
as "s"
go
sp_bindefault ftHgdb, ".db"
go
insert 
values("409-56-7008", "Ε", "͂", "TY",
"񂽂낤", "03-3508-0727", "scJ摾zPuP|PQ|U",
"Ƃ傤Ƃ₭悤",
"cJ", "₭", "s", "Ƃ傤", "Japan",
"{", "104")
go
insert  values("409-56-9004", "SGWjAO",
"񂺂񂦂񂶂ɂ", "", "Ԃ",
"044-193-3323", "RsKx쒬PU|Q",
"ɂ܂킢ق肩킿傤", "Rs", "ɂ܂",
"_ސ쌧", "Ȃ킯", "Japan", "{", "210")
go
insert 
values("213-46-8915", "gL", "悵Ђ", "Lq",
"悱", "0424-36-2882", "_ސ쌧tRQ|R|U",
"Ȃ킯͂܂傤", "tR", "͂܂傤",
"_ސ쌧", "Ȃ킯", "Japan", "{", "241")
go
insert 
values("238-95-7766", "r", "܂", "G", "Ђ낵",
"03-3773-2366", "scRQ|PPQ",
"Ƃ傤Ƃ̂傤", "c", "",
"s", "Ƃ傤", "Japan", "{", "102")
go
insert 
values("998-72-3567", "J", "", "", "",
"045-473-1976", "_ސ쌧ls`kVlQ|Q|PO",
"Ȃ킯悱͂܂ق悱͂",
"ls", "悱͂܂", "_ސ쌧", "Ȃ킯", "Japan",
"{", "222")
go
insert 
values("899-46-2035", "x", "ق", "", "񂢂",
"0473-23-1234", "ʌYasxVP|Q|S",
"܂񂤂킵ق̂", "Yas", "킵",
"ʌ", "܂", "Japan", "{", "264")
go
insert 
values("722-51-5454", "t@[u", "ӂ[Ԃ",
"iD|g", "[", "", "", NULL, NULL, NULL, NULL,
NULL, "France", "tX", NULL)
go
insert 
values("807-91-6654", "{", "", "OY",
"Ԃ낤", "03-3868-4799",
"scJGRP|U|X",
"Ƃ傤Ƃ₭炷",
"cJ", "₭", "s", "Ƃ傤", "Japan",
"{", "104")
go
insert 
values("893-72-1158", "Xc", "肽", "v",
"", "0433-26-4354", "_ސ쌧qsRl12-6",
"Ȃ킯񂩂܂炵䂢͂", "qs", "܂炵",
"_ސ쌧", "Ȃ킯", "Japan", "{", "236")
go
insert 
values("724-08-9931", "{", "݂₴", "O",
"܂Ђ", "0423-37-2332", "s~sHR|S|P",
"Ƃ傤Ƃ߂͂ނ", "~s", "߂", "s",
"Ƃ傤", "Japan", "{", "256")
go
insert 
values("274-80-9391", "ؑ", "ނ", "H",
"܂", "0988-23-1198", "ꌧߔesߔeQ|RST",
"Ȃ킯Ȃ͂", "ߔes", "Ȃ͂", "ꌧ",
"Ȃ킯", "Japan", "{", "996")
go
insert 
values("756-30-7391", "WFTbv", "",
"[X", "[肷", "415-658-9932",
"6223 Bateman St. Berkeley CA", "ӂɂイ΁[ꂢ",
"Berkeley", "΁[ꂢ", "CA", "ӂɂ",
"USA", "AJ", "94705")
go
insert 
values("724-80-9391", "", "Ƃ", "O",
"܂Ђ", "044-544-2331", "_ސ쌧sË捂ÂQRS|Q",
"Ȃ킯񂩂킳", "s", "킳",
"_ސ쌧", "Ȃ킯", "Japan", "{", "211")
go
insert 
values("427-17-2319", "Kc", "", "NN", "₷Ƃ",
"0862-12-1234", "RRsRQ|R",
"܂񂨂܂", "Rs", "܂", "R",
"܂", "Japan", "{", "629")
go
insert 
values("672-71-3249", "匎", "", "`F", "悵Ђ",
"0428-21-3457", "s{snQRP",
"Ƃ傤Ƃӂイ΂傤", "{s", "ӂイ", "s",
"Ƃ傤", "Japan", "{", "183")
go
insert 
values("267-41-2394", "gt[", "Ƃӂ[", "Ar", "т",
"212-355-9856", "350 Park Avenue New York, NY",
"ɂ[[イɂ[[", "New York", "ɂ[[", "NY",
"ɂ[[", "USA", "AJ", "10022")
go
insert 
values("472-27-2349", "R", "Ƃ", "Y", "낤",
"03-3503-4753", "s䓌擌R|PR|P",
"Ƃ傤ƂƂЂ", "䓌", "Ƃ",
"s", "Ƃ傤", "Japan", "{", "110")
go
insert 
values("527-72-3246", "u", "Ă炨", "o[o",
"΁[΂", "03-3241-1049", "s{S|V|PT",
"Ƃ傤Ƃイɂق΂", "", "イ",
"s", "Ƃ傤", "Japan", "{", "103")
go
insert 
values("172-32-1176", "", "", "Iq", "݂",
"022-398-0412", "{錧stRQ|U|P",
"݂€񂹂񂾂΂", "s", "񂾂",
"{錧", "݂€", "Japan", "{", "981")
go
insert 
values("712-45-1867", "JC[", "[", "_",
"", "415-938-6445", "5420 College Av. Oakland CA",
"ӂɂイ[", "Oakland", "[",
"CA", "ӂɂ", "USA", "AJ", "95428")
go
insert 
values("846-92-7186", "g", "Ђ", "h", "",
"0426-22-1346", "sqsUO|P",
"Ƃ傤Ƃ͂Ă܂", "qs", "͂",
"s", "Ƃ傤", "Japan", "{", "135")
go
insert 
values("486-29-1786", "NXeB[", "肷Ă[",
"AKT", "", "", "", NULL, NULL, NULL, NULL, NULL,
"England", "CMX", NULL)
go
insert 
values("648-92-1872", "", "ނ܂", "h", "",
"0196-25-9845", "茧sP|X|PU",
"Ă肨イ", "s", "肨", "茧",
"Ă", "Japan", "{", "020")
go
insert oŎ
values("1389", "HЏoŋ", "Ԃ񂵂Ⴕς񂫂傭",
"", "Ԃ񂫂傤", "s", "Ƃ傤")
go
insert oŎ
values("0736", "E", "Ԃ񂩂", "Vh",
"񂶂キ", "s", "Ƃ傤")
go
insert oŎ
values("0877", "^X", "Ƃ܂Ă", "`", "݂ȂƂ",
"s", "Ƃ傤")
go
insert oŎ
values("1622", "܏o", "܂ς", "s", "Ђ̂",
"s", "Ƃ傤")
go
insert oŎ
values("1756", "toŎ", "񂵂ς񂵂",
"c", "悾", "s", "Ƃ傤")
go
insert oŎ
values("9987", "tWWIo", "ӂ炶ς",
"`", "݂ȂƂ", "s", "Ƃ傤")
go
insert oŎ
values("9911", "ړ`", "傤ł񂵂",
"s", "킳", "_ސ쌧", "Ȃ킯")
go
insert oŎ
values("9934", "쏑[", "͂₩킵ڂ",
"aJ", "Ԃ₭", "s", "Ƃ傤")
go
insert oŎ
values("9952", "HcX", "Ă",
"Hcs", "", "Hc", "")
go
insert oŎ
values("9922", "~", "ւڂ񂵂",
"c", "悾", "s", "Ƃ傤")
go
insert oŎ
values("9964", "wk", "񂵂",
"aJ", "Ԃ₭", "s", "Ƃ傤")
go
insert oŎ
values("9977", "z[", "܂ڂ",
"", "Ԃ񂫂傤", "s", "Ƃ傤")
go
insert 쌠
values("BU1349", 0, 5000, 10)
go
insert 쌠
values("BU1349", 5001, 50000, 12)
go
insert 쌠
values("BU1360", 0, 2000, 10)
go
insert 쌠
values("BU1360", 2001, 3000, 12)
go
insert 쌠
values("BU1360", 3001, 4000, 14)
go
insert 쌠
values("BU1360", 4001, 10000, 16)
go
insert 쌠
values("BU1360", 10001, 50000, 18)
go
insert 쌠
values("PC3250", 0, 1000, 10)
go
insert 쌠
values("PC3250", 1001, 3000, 12)
go
insert 쌠
values("PC3250", 3001, 5000, 14)
go
insert 쌠
values("PC3250", 5001, 7000, 16)
go
insert 쌠
values("PC3250", 7001, 10000, 18)
go
insert 쌠
values("PC3250", 10001, 12000, 20)
go
insert 쌠
values("PC3250", 12001, 14000, 22)
go
insert 쌠
values("PC3250", 14001, 50000, 24)
go
insert 쌠
values("PC3256", 0, 1000, 10)
go
insert 쌠
values("PC3256", 1001, 5000, 12)
go
insert 쌠
values("PC3256", 5001, 10000, 14)
go
insert 쌠
values("PC3256", 10001, 50000, 16)
go
insert 쌠
values("PC3389", 0, 2000, 10)
go
insert 쌠
values("PC3389", 2001, 5000, 12)
go
insert 쌠
values("PC3389", 5001, 10000, 14)
go
insert 쌠
values("PC3389", 10001, 50000, 16)
go
insert 쌠
values("PC3679", 0, 1000, 10)
go
insert 쌠
values("PC3679", 1001, 2000, 12)
go
insert 쌠
values("PC3679", 2001, 4000, 14)
go
insert 쌠
values("PC3679", 4001, 6000, 16)
go
insert 쌠
values("PC3679", 6001, 8000, 18)
go
insert 쌠
values("PC3679", 8001, 10000, 20)
go
insert 쌠
values("PC3679", 10001, 12000, 22)
go
insert 쌠
values("PC3679", 12001, 50000, 24)
go
insert 쌠
values("PC1363", 0, 2000, 10)
go
insert 쌠
values("PC1363", 2001, 4000, 12)
go
insert 쌠
values("PC1363", 4001, 6000, 14)
go
insert 쌠
values("PC1363", 6001, 8000, 16)
go
insert 쌠
values("PC1363", 8001, 10000, 18)
go
insert 쌠
values("PC1363", 10001, 12000, 20)
go
insert 쌠
values("PC1363", 12001, 14000, 22)
go
insert 쌠
values("PC1363", 14001, 50000, 24)
go
insert 쌠
values("MC0738", 0, 5000, 10)
go
insert 쌠
values("MC0738", 5001, 10000, 12)
go
insert 쌠
values("MC0738", 10001, 15000, 14)
go
insert 쌠
values("MC0738", 15001, 50000, 16)
go
insert 쌠
values("BU9196", 0, 5000, 10)
go
insert 쌠
values("BU9196", 5001, 50000, 12)
go
insert 쌠
values("BU9185", 0, 5000, 10)
go
insert 쌠
values("BU9185", 5001, 10000, 12)
go
insert 쌠
values("BU9185", 10001, 15000, 14)
go
insert 쌠
values("BU9185", 15001, 50000, 16)
go
insert 쌠
values("MC2836", 0, 4000, 10)
go
insert 쌠
values("MC2836", 4001, 8000, 12)
go
insert 쌠
values("MC2836", 8001, 12000, 14)
go
insert 쌠
values("MC2836", 12001, 16000, 16)
go
insert 쌠
values("MC2836", 16001, 20000, 18)
go
insert 쌠
values("MC2836", 20001, 24000, 20)
go
insert 쌠
values("MC2836", 24001, 28000, 22)
go
insert 쌠
values("MC2836", 28001, 50000, 24)
go
insert 쌠
values("GL1331", 0, 2000, 10)
go
insert 쌠
values("GL1331", 2001, 4000, 12)
go
insert 쌠
values("GL1331", 4001, 8000, 14)
go
insert 쌠
values("GL1331", 8001, 12000, 16)
go
insert 쌠
values("GL1331", 8001, 12000, 16)
go
insert 쌠
values("GL1331", 12001, 20000, 18)
go
insert 쌠
values("GL1331", 20001, 50000, 20)
go
insert 쌠
values("PS2089", 0, 5000, 10)
go
insert 쌠
values("PS2089", 5001, 15000, 12)
go
insert 쌠
values("PS2089", 15001, 50000, 14)
go
insert 쌠
values("MS4021", 0, 2000, 10)
go
insert 쌠
values("MS4021", 2001, 8000, 12)
go
insert 쌠
values("MS4021", 8001, 16000, 14)
go
insert 쌠
values("MS4021", 16001, 24000, 16)
go
insert 쌠
values("MS4021", 24001, 32000, 18)
go
insert 쌠
values("MS4021", 32001, 40000, 20)
go
insert 쌠
values("MS4021", 40001, 50000, 22)
go
insert 쌠
values("TD2365", 0, 5000, 10)
go
insert 쌠
values("TD2365", 5001, 10000, 12)
go
insert 쌠
values("TD2365", 10001, 15000, 14)
go
insert 쌠
values("TD2365", 15001, 20000, 16)
go
insert 쌠
values("TD2365", 20001, 25000, 18)
go
insert 쌠
values("TD2365", 25001, 30000, 20)
go
insert 쌠
values("TD2365", 30001, 35000, 22)
go
insert 쌠
values("TD2365", 35001, 50000, 24)
go
insert 쌠
values("PC3856", 0, 10000, 10)
go
insert 쌠
values("PC3856", 10001, 20000, 12)
go
insert 쌠
values("PC3856", 20001, 30000, 14)
go
insert 쌠
values("PC3856", 30001, 40000, 16)
go
insert 쌠
values("PC3856", 40001, 50000, 18)
go
insert 쌠
values("MP9231", 0, 5000, 10)
go
insert 쌠
values("MP9231", 5001, 15000, 12)
go
insert 쌠
values("MP9231", 15001, 50000, 14)
go
insert 쌠
values("PC3100", 0, 5000, 10)
go
insert 쌠
values("PC3100", 5001, 15000, 12)
go
insert 쌠
values("PC3100", 15001, 50000, 14)
go
dump transaction jpubs with truncate_only
go
insert ̔ values("7066", "QA7442.3", "1990/11/13")
go
insert ̔ values("7072", "D4482", "1990/12/04")
go
insert ̔ values("7078", "N914008", "1990/12/12")
go
insert ̔ values("7078", "N914014", "1991/01/07")
go
insert ̔ values("6032", "423LL922", "1991/02/04")
go
insert ̔ values("6032", "423LL930", "1991/03/08")
go
insert ̔ values("8029", "722a", "1991/07/03")
go
insert ̔ values("8029", "6871", "1991/07/04")
go
insert ̔ values("7078", "P723", "1991/07/12")
go
insert ̔ values("7298", "X999", "1991/07/12")
go
insert ̔ values("7298", "QQ2299", "1991/07/13")
go
insert ̔ values("7298", "TQ456", "1991/07/15")
go
insert ̔ values("7078", "QA879.1", "1991/07/15")
go
insert ̔ values("7066", "A2976", "1991/07/16")
go
insert ̔ values("7896", "P3087a.1", "1991/07/17")
go
insert ̔ values("7896", "P3087a.2", "1991/07/17")
go
insert ̔ values("7896", "P3087a.3", "1991/07/17")
go
insert ̔ values("7896", "P3087a.4", "1991/07/17")
go
insert ̔ values("7072", "P2121.1", "1991/07/18")
go
insert ̔ values("7072", "P2121.2", "1991/07/18")
go
insert ̔ values("7072", "P2121.3", "1991/07/18")
go
insert ̔ values("6032", "423LL942.1", "1991/08/02")
go
insert ̔ values("6032", "423LL942.2", "1991/08/02")
go
insert ̔ values("6032", "423LL942.3", "1991/08/02")
go
insert ̔ values("6032", "423LL942.4", "1991/08/02")
go
insert ̔ values("7078", "QB567.1", "1991/08/05")
go
insert ̔ values("7078", "QB567.2", "1991/08/05")
go
insert ̔ values("8029", "8765", "1991/08/07")
go
insert ̔ values("6032", "423LL945.1", "1991/08/08")
go
insert ̔ values("6032", "423LL945.2", "1991/08/08")
go
insert ̔ڍ values("7066", "QA7442.3", "PC1363", 50, 3)
go
insert ̔ڍ values("7066", "QA7442.3", "MC0738", 10, 3)
go
insert ̔ڍ values("7066", "QA7442.3", "PS2089", 30, 3)
go
insert ̔ڍ values("7072", "D4482", "MC0738", 5, 5)
go
insert ̔ڍ values("7072", "D4482", "PC1363", 20, 5)
go
insert ̔ڍ values("7072", "D4482", "PS2089", 20, 5)
go
insert ̔ڍ values("7072", "D4482", "PC3856", 5, 5)
go
insert ̔ڍ values("7072", "D4482", "MS4021", 15, 10)
go
insert ̔ڍ values("7072", "D4482", "DC1437", 15, 5)
go
insert ̔ڍ values("7078", "N914008", "BU9196", 2000, 10)
go
insert ̔ڍ values("7078", "N914008", "MC0738", 5, 3)
go
insert ̔ڍ values("7078", "N914008", "PC1363", 20, 5)
go
insert ̔ڍ values("7078", "N914008", "PS2089", 30, 5)
go
insert ̔ڍ values("7078", "N914008", "PC3856", 30, 5)
go
insert ̔ڍ values("7078", "N914008", "MS4021", 20, 5)
go
insert ̔ڍ values("7078", "N914014", "BU1349", 30, 5)
go
insert ̔ڍ values("7078", "N914014", "BU1360", 30, 5)
go
insert ̔ڍ values("7078", "N914014", "PC3100", 30, 5)
go
insert ̔ڍ values("7078", "N914014", "BU9185", 30, 5)
go
insert ̔ڍ values("7078", "N914014", "MC2836", 30, 5)
go
insert ̔ڍ values("7078", "N914014", "TD2365", 10, 3)
go
insert ̔ڍ values("6032", "423LL922", "DC1437", 5, 3)
go
insert ̔ڍ values("6032", "423LL922", "PC3100", 20, 5)
go
insert ̔ڍ values("6032", "423LL922", "MP9231", 20, 3)
go
insert ̔ڍ values("6032", "423LL922", "MC2836", 20, 5)
go
insert ̔ڍ values("6032", "423LL922", "GL1331", 20, 3)
go
insert ̔ڍ values("6032", "423LL930", "PS2089", 2, 3)
go
insert ̔ڍ values("6032", "423LL930", "PC3250", 20, 5)
go
insert ̔ڍ values("6032", "423LL930", "PC3256", 20, 3)
go
insert ̔ڍ values("6032", "423LL930", "BU1360", 10, 3)
go
insert ̔ڍ values("6032", "423LL930", "PC3679", 10, 3)
go
insert ̔ڍ values("6032", "423LL930", "TD2365", 5, 3)
go
insert ̔ڍ values("6032", "423LL930", "PC3856", 5, 3)
go
insert ̔ڍ values("8029", "722a", "PC3679", 10, 3)
go
insert ̔ڍ values("8029", "722a", "MS4021", 20, 3)
go
insert ̔ڍ values("8029", "722a", "DC1437", 20, 3)
go
insert ̔ڍ values("8029", "722a", "MP9231", 10, 3)
go
insert ̔ڍ values("8029", "722a", "BU1360", 10, 3)
go
insert ̔ڍ values("8029", "6871", "BU1349", 30, 5)
go
insert ̔ڍ values("8029", "6871", "BU9196", 200, 10)
go
insert ̔ڍ values("8029", "6871", "PC1363", 30, 5)
go
insert ̔ڍ values("8029", "6871", "MC0738", 2, 3)
go
insert ̔ڍ values("8029", "6871", "BU9235", 80, 10)
go
insert ̔ڍ values("8029", "6871", "PS2089", 3, 3)
go
insert ̔ڍ values("7078", "P723", "PC3856", 8, 3)
go
insert ̔ڍ values("7078", "P723", "BU1349", 40, 5)
go
insert ̔ڍ values("7078", "P723", "PC3100", 20, 3)
go
insert ̔ڍ values("7078", "P723", "MP9231", 20, 3)
go
insert ̔ڍ values("7078", "P723", "TD2365", 10, 3)
go
insert ̔ڍ values("7078", "P723", "MS4021", 80, 10)
go
insert ̔ڍ values("7298", "X999", "PC3679", 10, 3)
go
insert ̔ڍ values("7298", "X999", "GL1331", 5, 3)
go
insert ̔ڍ values("7298", "X999", "PC3256", 10, 3)
go
insert ̔ڍ values("7298", "X999", "PC3389", 10, 3)
go
insert ̔ڍ values("7298", "X999", "PC3100", 10, 3)
go
insert ̔ڍ values("7298", "X999", "PC3856", 10, 3)
go
insert ̔ڍ values("7298", "X999", "DC1437", 10, 3)
go
insert ̔ڍ values("7298", "QQ2299", "DC1437", 30, 5)
go
insert ̔ڍ values("7298", "QQ2299", "GL1331", 10, 3)
go
insert ̔ڍ values("7298", "QQ2299", "PC3100", 30, 5)
go
insert ̔ڍ values("7298", "QQ2299", "BU1349", 30, 5)
go
insert ̔ڍ values("7298", "QQ2299", "BU1360", 30, 5)
go
insert ̔ڍ values("7298", "QQ2299", "PC3679", 10, 3)
go
insert ̔ڍ values("7298", "QQ2299", "MP9231", 5, 3)
go
insert ̔ڍ values("7298", "TQ456", "PS2089", 2, 3)
go
insert ̔ڍ values("7298", "TQ456", "MC2836", 20, 5)
go
insert ̔ڍ values("7298", "TQ456", "BU9285", 40, 3)
go
insert ̔ڍ values("7298", "TQ456", "PC1363", 1, 3)
go
insert ̔ڍ values("7298", "TQ456", "MC0738", 2, 3)
go
insert ̔ڍ values("7298", "TQ456", "PC3256", 5, 3)
go
insert ̔ڍ values("7078", "QA879.1", "BU1349", 20, 5)
go
insert ̔ڍ values("7078", "QA879.1", "MP9231", 20, 5)
go
insert ̔ڍ values("7078", "QA879.1", "PC3100", 20, 5)
go
insert ̔ڍ values("7078", "QA879.1", "PC3856", 5, 3)
go
insert ̔ڍ values("7078", "QA879.1", "PC3389", 20, 5)
go
insert ̔ڍ values("7078", "QA879.1", "PC3256", 10, 3)
go
insert ̔ڍ values("7066", "A2976", "TD2365", 5, 3)
go
insert ̔ڍ values("7066", "A2976", "BU1349", 5, 3)
go
insert ̔ڍ values("7066", "A2976", "BU9196", 100, 10)
go
insert ̔ڍ values("7066", "A2976", "BU9185", 5, 3)
go
insert ̔ڍ values("7066", "A2976", "MC2836", 30, 5)
go
insert ̔ڍ values("7066", "A2976", "DC1437", 5, 3)
go
insert ̔ڍ values("7896", "P3087a.1", "BU1360", 5, 3)
go
insert ̔ڍ values("7896", "P3087a.2", "MP9231", 3, 3)
go
insert ̔ڍ values("7896", "P3087a.2", "GL1331", 5, 3)
go
insert ̔ڍ values("7896", "P3087a.2", "PS2089", 3, 3)
go
insert ̔ڍ values("7896", "P3087a.3", "TD2365", 5, 3)
go
insert ̔ڍ values("7896", "P3087a.3", "PC3856", 5, 3)
go
insert ̔ڍ values("7896", "P3087a.3", "PC3100", 10, 3)
go
insert ̔ڍ values("7896", "P3087a.4", "MS4021", 7, 3)
go
insert ̔ڍ values("7072", "P2121.1", "PC3250", 5, 3)
go
insert ̔ڍ values("7072", "P2121.1", "BU1349", 5, 3)
go
insert ̔ڍ values("7072", "P2121.1", "PC3679", 5, 3)
go
insert ̔ڍ values("7072", "P2121.2", "PC3389", 3, 3)
go
insert ̔ڍ values("7072", "P2121.3", "BU9185", 3, 3)
go
insert ̔ڍ values("6032", "423LL942.1", "PS2089", 2, 3)
go
insert ̔ڍ values("6032", "423LL942.1", "PS3256", 10, 3)
go
insert ̔ڍ values("6032", "423LL942.2", "BU9196", 50, 7)
go
insert ̔ڍ values("6032", "423LL942.3", "PC3100", 20, 5)
go
insert ̔ڍ values("6032", "423LL942.4", "PC3856", 10, 3)
go
insert ̔ڍ values("6032", "423LL942.4", "DC1437", 10, 3)
go
insert ̔ڍ values("6032", "423LL942.4", "PC3679", 10, 3)
go
insert ̔ڍ values("7078", "QB567.1", "BU1349", 5, 3)
go
insert ̔ڍ values("7078", "QB567.1", "MC0738", 1, 3)
go
insert ̔ڍ values("7078", "QB567.2", "BU9185", 2, 3)
go
insert ̔ڍ values("7078", "QB567.2", "PC1363", 1, 3)
go
insert ̔ڍ values("7078", "QB567.2", "PS2089", 2, 3)
go
insert ̔ڍ values("8029", "8765", "BU9196", 30, 7)
go
insert ̔ڍ values("8029", "8765", "MP9231", 10, 3)
go
insert ̔ڍ values("8029", "8765", "PC3100", 20, 7)
go
insert ̔ڍ values("8029", "8765", "PC3256", 30, 7)
go
insert ̔ڍ values("8029", "8765", "BU1360", 5, 3)
go
insert ̔ڍ values("6032", "423LL945.1", "MC0738", 2, 3)
go
insert ̔ڍ values("6032", "423LL945.1", "PS2089", 2, 3)
go
insert ̔ڍ values("6032", "423LL945.1", "MS2365", 2, 3)
go
insert ̔ڍ values("6032", "423LL945.2", "BU9196", 30, 7)
go
insert ҃^Cg
values("409-56-7008", "BU1349", 1, 50)
go
insert ҃^Cg
values("893-72-1158", "BU1349", 2, 50)
go
insert ҃^Cg
values("409-56-9004", "BU9196", 1, 100)
go
insert ҃^Cg
values("238-95-7766", "DC1437", 1, 65)
go
insert ҃^Cg
values("807-91-6654", "DC1437", 2, 35)
go
insert ҃^Cg
values("998-72-3567", "PC3856", 1, 100)
go
insert ҃^Cg
values("899-46-2035", "MP9231", 1, 100)
go
insert ҃^Cg
values("724-08-9931", "PC1363", 1, 100)
go
insert ҃^Cg
values("722-51-5454", "PC3100", 1, 30)
go
insert ҃^Cg
values("807-91-6654", "PC3100", 2, 70)
go
insert ҃^Cg
values("724-08-9931", "BU1360", 1, 100)
go
insert ҃^Cg
values("274-80-9391", "PC3250", 1, 100)
go
insert ҃^Cg
values("756-30-7391", "PC3256", 1, 70)
go
insert ҃^Cg
values("724-80-9391", "PC3256", 2, 30)
go
insert ҃^Cg
values("427-17-2319", "PC3389", 1, 100)
go
insert ҃^Cg
values("672-71-3249", "PC3679", 1, 100)
go
insert ҃^Cg
values("267-41-2394", "BU9185", 1, 70)
go
insert ҃^Cg
values("472-27-2349", "BU9185", 2, 30)
go
insert ҃^Cg
values("527-72-3246", "MC2836", 1, 100)
go
insert ҃^Cg
values("172-32-1176", "GL1331", 1, 100)
go
insert ҃^Cg
values("213-46-8915", "MC0738", 1, 100)
go
insert ҃^Cg
values("712-45-1867", "PS2089", 1, 70)
go
insert ҃^Cg
values("846-92-7186", "PS2089", 2, 30)
go
insert ҃^Cg
values("486-29-1786", "MS4021", 1, 20)
go
insert ҃^Cg
values("648-92-1872", "MS4021", 2, 80)
go
insert ^Cg
values("BU1349", "łumnvƌ{", "rWlX", "1839",
\850, \500000, 4095,
"{͐̐̈IH  \j[XcƐΕTYAčő唽Nuumnvƌ{ v̕MŁB",
"1990/05/30", 1)
go
insert ^Cg
values("BU1360", "ꂩ̐Eoϒn}", "rWlX", "0736",
\1360, \400000, 8070,
"ouoςAÎƂ}悤łB{ɂ炸AJłiCނ̔g͖KĂB̐Eoϒn}́Aǂh肩Ă̂||BSo`t̎r{ƃhCc̊\B",
"1991/04/18", 1)
go
insert ^Cg
values("PC3250", '[嗤͗ɂI  [C꒲ł킩gזvh̐^',
"l", "0877", \1300, \360000, 312,
"[嗤̓`́Ał̓XP[̑傫ȃ^bƂĂBA`̍ƂȂ[嗤͎݂A݂͌̉ӂɂIɂ͗j̓ǎ҂̃}ĂĂB",
"1991/06/30", 1)
go
insert ^Cg
values("PC3256", "VwƂten", "Ȋw", "1622", \1600, \120000, 702,
"{̓v̓Vw҂ɂtenƂĒڂׂłBFŊϑꂽsȌۂɊ֐SA炪ten̏ƍlnOmI̎dƂł͂Ȃƍl@ĂB",
"1991/03/10", 1)
go
insert ^Cg
values("PC3389", "T͂ȂqgɂȂȂ̂  ilȂ",
"Ȋw", "1839", \770, \80000, 286,
"̐íAR̐񂾂̂ŁAlԂ𐶂ނ߂ɂƂȂ̂ڎwĂ̂̂ł͂ȂBlԁiƂlԒS`ɁAm炸m炸NĂXɂƂāA҂̍l͐VNłB",
"1991/01/30", 1)
go
insert ^Cg
values("PC3679", "ȊwŕςlԂ̂TNPON  Ă͂ȂA͌",
"Ȋw", "1756", \750, \60000, 833,
"I痈IɂĐlނlł낤VZpݏoЉ_ɗ\B",
"1991/04/01", 1)
go
insert ^Cg
values("PC1363", "ܐRs[^@{̒", "rWlX", "9987",
\1600, \450000, 1342,
	"lԂ͕𐶎YĂAꂩ͕VlԂn͂߂̂낤B{̑ܐv́AčɂƂĎԂȂǂƂׂ̂ɂȂȂ炢[ȖƂȂĂB",
"1983/08/01",
1)
go
insert ^Cg
values("MC0738", "JNe", "", "9922", \580, \30000, 328,
"JNeȂł͊yp[eB[̉o͂łȂIvɂA}`AɂJNȅ}i[ԗ",
"1979/06/20", 1)
go
insert ^Cg
values("BU9196", "ZpWcumIYv̍ߕ",
"rWlX", "9911", \1200, \20000, 7362,
"ul̔\̓AbvƑgDyvƂ傫[Ɏg񂾃GWjÁAH̋L^łBނ炪iASV^Cv̏WcƂ͂ǂ̂悤Ȃ̂̂BĐʂ́I",
"1987/04/24", 1)
go
insert ^Cg
values("BU9185", "p[Vtg  E",
"rWlX", "9987", \1600, \120000, 4023,
"܂ł̐ExĂ͍\ׂ̂ĂuԂɂ܉X͐ĂB݂͍͌\ωiVtgj鎞Ap[Vtg̎Ȃ̂łB",
"1990/11/20", 1)
go
insert ^Cg
values("MC2836", "uSPΎv𐁂΂  H߂̎",
"H", "0877", \760, \60000, 653,
"ŋߘb́uSPΎviېkƒj́A{l̐uŃKX[ɂ悤Ȃ́vƌxĂB{͐]IƂ󋵂̒ŁǍN邩e[}ƂȂĂB",
"1990/10/31", 1)
go
insert ^Cg
values("GL1331", "̖̓AJ哝̕vl^41̈Ɩ]",
"", "0877", \1100, \40000, 859,
"TNԕčŕ炵҂́A͂̕čl哝̕vl̔AsAꂱꋓ꓊ɂ܂œ̘bɂ邱ƂɋAvl̒n߂ČƁAɂ͋̐sȂh}B",
"1990/01/30", 1)
go
insert ^Cg
values("PS2089", "s|^|EpEVh|@ȂAނ͑lɂȂȂ̂",
"Sw", "9911", \1600, \30000, 898,
"s|^|EṕAȉNłBlЉ̒ԓfA̍uȂȂvŕ炷BN͂Ƃ邪As|^|EṕAlɂȂ肽ȂB̐S̏ǌQs[^[EpEVh[łB",
"1984/05/05", 1)
go
insert ^Cg
values("MS4021", "AKTENXeB[aPOONLOubN",
"~Xe", "9934", \1500, \20000, 412,
"m~XȅnAKTENXeB[̐aPOONɂBޏ̐UAޏlXAn̕䗠Ai̊SXgfESXgȂǁAAKT̑S̑mɂ͊iD̈ƂB",
"1990/11/30", 1)
go
insert ^Cg
values("TD2365", "VE{`POOI",
"`", "9952", \1700, \15000, 226,
'{enɎcĂ`́AÂ{񓇂̖LȎR̒ɕ炵AƎ̕Y݈łlX́AmbƎRhS`̂łBXɁggÂǂ{hւ̋DтĂB',
"1990/10/25", 1)
go
insert ^Cg
values("DC1437", "E哮}Ӂ@z",
"}", "9922", \20000, \30000, 412,
"ʂ̃zނ̕ޖ@Ƃ͂āAlԂgbvɂƂ낪A̐}ӂƂ͂_łBł͋ߑ㓮wrˋ󓮕AƂΓVɂĂڍׂȋLqȂĂB",
"1988/05/27", 1)
go
insert ^Cg
values("PC3856", "CJׂ͂邵A",
"Ȋw", "9964", \640, \5000, 612,
"CJ̑̂̍\ِ̓́AԂł̓ɂ݂ȂB́AɂCĴ͂ȂłB",
"1989/10/27", 1)
go
insert ^Cg
values("MP9231", "n}̃CRmW[", "n}", "9977",
\3300, \20000, 327,
"[bp̌Òn}߂邱̖{́ALY킹Ďn߂ĂBꂵȂ悤Ȓn}ڂĂłB",
"1989/11/24", 1)
go
insert ^Cg
values("PC3100", "t@[uL", "", "0736",
\1300, \20000, 532,
"ƂAt@[uLŁAN𑗂tXw҂ASł͂킩ȂȂAN̊ɂ߂Â悤ƂJBlǂłȂȂB",
"1991/06/21", 0)
go
dump transaction jpubs with truncate_only
go
insert X
values("7066", "FQX", "䂤قǂĂ", "sVhvۂR|Q|S", "Ƃ傤Ƃ񂶂キ", "Vh", "񂶂キ", "s", "Ƃ傤", "{", "Japan", "106", "QO")
go
insert X
values("7072", "IOe", "傹񂮂", "sc_ےP|U|QO", "Ƃ傤Ƃ悾ڂ傤", "c", "悾", "s", "Ƃ傤", "{", "Japan", "104", "RO")
go
insert X
values("7078", "Lѓ", "䂤ǂ", "lsɐ蒬Q|S|U", "悱͂܂Ȃ傤", "ls", "悱͂܂", "_ސ쌧", "Ȃ킯", "{", "Japan", "223", "RO")
go
insert X
values("6032", "lȓX", "ǂĂ", "sc_ےQ|R|PO", "Ƃ傤Ƃ悾ڂ傤", "c", "悾", "s", "Ƃ傤", "{", "Japan", "104", "RO")
go
insert X
values("8029", "΂̍X", "Ђ̂ɂ₵Ă", "sVhVhR|S|PP", "Ƃ傤Ƃ񂶂キ񂶂キ", "Vh", "񂶂キ", "s", "Ƃ傤", "{", "Japan", "106", "RO")
go
insert X
values("7896", "dubNX", "₦ԂĂ", "s攪dFU|T|V", "Ƃ傤Ƃイ₦", "", "イ", "s", "Ƃ傤", "{", "Japan", "104", "RO")
go
insert X
values("7298", "pPX", "񂵂Ă", "s`搼VP|PQ|W", "Ƃ傤Ƃ݂ȂƂɂ΂", "`", "݂ȂƂ", "s", "Ƃ傤", "{", "Japan", "105", "QO")
go
insert 
values("VKʊ", NULL, NULL, NULL, 10.5)
go
insert 
values("ʊ", "7072", 100, 1000, 6.7)
go
insert 
values("ӓʊ", "7078", NULL, NULL, 5)
go
insert 
values("ӃLy[ʊ", "7298", NULL, NULL, 3)
go
insert ` values("409-56-7008",
"aVNXRO_ː܂BÓ쒆wEÓ썂Z𑲋ƂꋴwwB
aRONAQuz̋G߁v𔭕\ARPNɑRSH܂܁B
NwƂƂƂɍƐɓBaSRNɎQc@ISɗA
IBϋɓIȐŒmBoD̐Ε[Y͔ނ̎łB")
insert ` values("274-80-9391",
"wBU͊CmnwBOOR̔Aɉ_x
n◬o\Đ̒ڂW߂B`̍ƂȂ[嗤͎݂A
݂͌̉ӂɂƎ咣w҂łB")
insert ` values("998-72-3567",
"CJmƌĂ΂铌Yw̋łBuǂȐłɂł
ĂƂ΂܂łłAق̖Ғœ̓Ƃ͂aV
fUC@\܂vƃCJm͌B̂ɁAḿsn
̒łAȂOqIȐ_l̎Ő݌vꂽ̂ł͂Ȃ낤HtƏ
ɏĂB")
insert ` values("722-51-5454",
"PXĨtX̍wҁBƂAt@[uL񂾎҂́A
ɗr̃N\ŋʂAɗ𐶂݂XJx̐
ɂӂāA̕svcɂ܂Ƃ낤BĺAɍ
NɂȂȂB܂Ɏqǂɖ^ĂĂB")
insert ` values("267-41-2394",
'PXVOŃw̏ՌxAPXWOŃwO̔gxɑPONԂ
wp[Vtgx\ꂽB̂R͂ƗiƂĂǂ
̂ł邪A܂Ƃ߂ƁAmIɈтǂݕƂȂ悤łB
发ł邪A傫ϓÂ鐢Ȅǂݎ邽߂ɂ͂R
ʂēǂݐi߂̂xXg낤B')
go

create rule oŃR[hK
as @oŃR[h in ("1389", "0736", "0877", "1622", "1756")
or @oŃR[h like "99[0-9][0-9]"
go
sp_bindrule oŃR[hK, "oŎ.oŃR[h"
go
create rule ^CgR[hK
as
@^CgR[h like "BU[0-9][0-9][0-9][0-9]" or	/* rWlX */
@^CgR[h like "M[CPS][0-9][0-9][0-9][0-9]" or
				/* EHAn}A~Xe[ */
@^CgR[h like "P[SC][0-9][0-9][0-9][0-9]" or
				/* SwAlEȊwEL */
@^CgR[h like "TD[0-9][0-9][0-9][0-9]" or	/* `E` */
@^CgR[h like "GL[0-9][0-9][0-9][0-9]" or	/*  */
@^CgR[h like "DC[0-9][0-9][0-9][0-9]" or	/* E} */
@^CgR[h like "[A-Z][A-Z]xxxx" or
@^CgR[h like "[A-Z][A-Z]yyyy"

/*Ll:  BU, MC, MP, MS, PS, PC TD, GL, DC + 4̐
**邢
**Q̔p啶̃At@xbgɑ xxxx  yyyy ō\
*/
go
sp_bindrule ^CgR[hK, "^Cg.^CgR[h"
go
sp_bindrule ^CgR[hK, "̔ڍ.^CgR[h"
go
create trigger ^Cg폜
on ^Cg
for delete
as
if (select count(*) from deleted, ̔ڍ
where ̔ڍ.^CgR[h = deleted.^CgR[h) >0
begin
	rollback transaction
 	print "'̔ڍ'ɊY'^CgR[h'邽߁A^Cg폜鎖͂ł܂B"
end
go
create trigger ̔gK
on ̔ڍ
for insert, update, delete
as
/* ̕ۑ:  esȂ͕A */
if @@rowcount = 0
	begin
		return
	end

/* ׂĂ̐Vl̒ǉ */
/* isnull gp: ^Cge[u null l̈Ӗ
**		"̔"łA"̔s"ł͂Ȃ
*/
update ^Cg
        set ̔ = isnull(̔, 0) + (select sum()
		from inserted
		where ^Cg.^CgR[h = inserted.^CgR[h)

/* deleted ܂ updated ̑SĂ̒l̍폜 */
update ^Cg
        set ̔ = isnull(̔, 0) - (select sum()
		from deleted
		where ^Cg.^CgR[h = deleted.^CgR[h)
go
create view ^Cgr[
as
select 薼, ғ, ,
i, ̔, oŃR[h
from , ^Cg, ҃^Cg
where .҃R[h = ҃^Cg.҃R[h
and ^Cg.^CgR[h = ҃^Cg.^CgR[h
go
create procedure ŗ @p[Zg int
as
select ҃R[h from ҃^Cg
where ҃^Cg.ŗ = @p[Zg
go
grant execute on ŗ to public
go
/* APT ̗ Sales p̃vVW쐬 */
create proc  @XR[h char(4)
as
select t,
       ̔.ԍ,
       ,
       ̔ڍ.^CgR[h,
       ,
       i,
       v =  * i * (1 - /100)
from ̔, ̔ڍ, ^Cg
where ̔.XR[h = @XR[h
and ̔.ԍ = ̔ڍ.ԍ
and ^Cg.^CgR[h = ̔ڍ.^CgR[h
order by t desc, ̔.ԍ
go
create proc  as
select , XR[h, , , 
from 
go
create proc X
@X      varchar(40)
as
declare @ varchar(40)
select  @ = lower(@X) + "%"
select  X,
        XR[h,
        XZ,
        s撬,
        s{,
        X֔ԍ,
        country
from    X
where   lower(X) like @
return  @@rowcount
go
create proc XR[h
@XR[h	char(4)
as
select X,
        XR[h,
        XZ,
        s撬,
        s{,
        X֔ԍ,
        country
from X
where XR[h = @XR[h
return @@rowcount
go
create proc ̔͏
	@XR[h	char(4),
	@ԍ	varchar(20),
	@	varchar(40)
as
insert ̔
values(@XR[h, @ԍ, @)
go
create proc ̔ڍד͏
	@XR[h        char(4),
	@ԍ        varchar(20),
	@^CgR[h       tid,
	@            smallint,
	@       float
as
insert ̔ڍ
values(@XR[h, @ԍ, @^CgR[h, @, @)
go
create proc ^Cg ( @薼 varchar(40))
as
select @薼 = lower( @薼 ) + "%"
select  薼, ^CgR[h, i
from    ^Cg
where   lower(薼) like @薼
return @@rowcount
go
create proc ^CgR[h ( @^CgR[h varchar(80))
as
select @^CgR[h = lower( @^CgR[h ) + "%"
select  薼, ^CgR[h, i
from    ^Cg
where   lower(^CgR[h) like @^CgR[h
return @@rowcount
go
/*
**	ւ̕ϊpvVW
**	exec  @ money, @^Cv tinyint[, @o varchar(60)]
**	o͗
**	@^Cv:
**	0 = ꖜOSl\ (default)
**	1 = ݓQSlE
**	2 = SO\l
**	3 = SQEl
*/
create procedure 
	@ money,
	@^Cv tinyint = null,
	@o	varchar(60) = null OUTPUT
as
declare
	@Ɨp	varchar(30),		/* Ɨp */
	@ʎ char(12),		/* ʎp̊ */
	@	char(18),		/* ̃Tv */
	@1	char(2),		/* i݁j̔ */
	@SJEg	tinyint,	/* ׂĂ̌̃JEg */
	@JEg1	tinyint,	/* ܂ł̌JEg */
	@JEg2	tinyint,	/* AǍJEg */
	@l	tinyint,		/* eX̌ɂ鐔l */
	@o̓tO	bit

set nocount on
if @o = null
begin
	select @o̓tO = 1
end
if @ = 0
begin
	select @o = "O"
	if @o̓tO = 1
	begin
		print @o
	end
	return (0)
end
/* ̓G[ */
if (@^Cv < 0 and @^Cv > 3) or @^Cv is null
begin
	select @^Cv = 0
end
select @Ɨp = ltrim(convert(char, @))
select @SJEg = charindex(".", @Ɨp) - 1
if @^Cv = 0 or @^Cv = 2
begin	/* @^Cv = 0: ꖜOSl\ */
	select @ʎ = "\S疜", @ = "OlܘZ"
end
else
begin	/* @^Cv = 1: ݓQSlE */
	select @ʎ = "ES疜", @ = "QlܘZ"
end
select 	@JEg1 = 0, @JEg2 = 3, @o = null

while(@SJEg > 0)
begin
	select @l = convert(tinyint, substring(@Ɨp, @SJEg, 1))
	if @JEg1 = 0
	begin	/* ̈ */
		if @JEg2 > 3
		begin	/* AA ̏ */
			select @1 = substring(@o, 1, 1)
			select @1 = @1 where
				@1 =
				substring(@ʎ, @JEg2 - 1, 1)
			if @@rowcount = 0 or @JEg2 = 4
			begin
				select @o =
				substring(@ʎ, @JEg2, 1)
				+ @o
			end
			else
			begin
				select @o =
				stuff(@o, 1, 1,
				substring(@ʎ, @JEg2, 1))
			end
		end
		if @l != 0
		begin	/* ̏ */
			select @o = substring(@, @l, 1)
				+ @o
		end
	end
	else
	begin	/* \ASÄ */
		if @l != 0
		begin
			if @l != 1
				or (@^Cv in (2, 3) and @JEg1 = 3)
			begin
				select @o =
					substring(@, @l, 1)
				+ substring(@ʎ, @JEg1, 1)
				+ @o
			end
			else
			begin
				select @o =
				substring(@ʎ, @JEg1, 1)
				+ @o
			end
		end
	end
	select @JEg1 = @JEg1 + 1,
		@SJEg = @SJEg - 1
	if @JEg1 > 3
	begin
		select @JEg1 = 0, @JEg2 = @JEg2 + 1
	end
end	/* while ̏I */
if @ < 0
begin
	select @o = "|" + @o
end
if @o̓tO = 1
begin
	print @o
end
return (0)
go
/*
**	z\
**	exec z\ @, @[, @o = varchar(60)]
**	:
**	0 = \12345 (default)		3 = ݓQSlE܉~
**	1 = 12345~			4 = 12345~67K
**	2 = ꖜOSl\܉~ 
*/
create procedure z\
	@ money,
	@ tinyint = null,
	@o	varchar(60) = null OUTPUT
as
declare
	@work	varchar(30),
	@i1	tinyint,
	@o̓tO bit

set nocount on
if @o = null
begin
	select @o̓tO = 1
end
/* ̓G[ */
if (@ < 0 and @ > 4) or @ is null
begin
	select @ = 0
end
select @work = ltrim(convert(char, @))
select @i1 = charindex(".", @work), @o = ""
/* \12345 */
if @ = 0
begin
	select @o = "\" + substring(@work, 1, @i1 -1)
end
else
begin
	/* 12345~ */
	if @ = 1
	begin
		select @o = substring(@work, 1, @i1 -1) + "~"
	end
	else
	begin
		if @ = 2
		begin
			exec  @, 0, @o output
			select @o = @o + "~"
		end
		else
		begin
			if @ = 3
			begin
				exec  @, 1, @o output
				select @o = "" + @o + "~"
			end
			else
			begin
				/* 12345~67K */
				if @ = 4
				begin
					select @o = stuff(@work,
						@i1, 1, "~")
						+ "K"
				end
			end
		end
	end
end
if @o̓tO = 1
begin
	print @o
end
return (0)
go
/*
**	t\
**	exec t\ @ datetime, @ tinyint
**		[, @o varchari(36)]
**	:
**	= 0 1991N 8 30 j
**	= 1 ON  O\ j
*/
create procedure t\
	@	datetime = null,
	@	tinyint = null,
	@o	varchar(36) = null
as
declare	@Ni	smallint,
	@Nc	varchar(8),
	@	varchar(4),
	@	varchar(6),
	@i	int,
	@o̓tO	bit
set nocount on
if @ is null
begin
	select @ = getdate()
end
if @ is null
begin
	select @ = 0
end
if @o is null
begin
	select @o̓tO = 1
end
select @Ni = datepart(year, @)
if @ = 1
begin	/* aɕϊ */
	if @Ni <= 1911
	begin
		select @Ni = @Ni - 1867
		select @o = ""
	end
	else
	begin
		if @Ni <= 1925
		begin
			select @Ni = @Ni - 1911
			select @o = "吳"
		end
		else
		begin
			if @Ni <= 1988
			begin
				select @Ni = @Ni - 1925
				select @o = "a"
			end
			else
			begin
				select @Ni = @Ni - 1988
				select @o = ""
			end
		end
	end
	select @Nc = "1", @ = "1"
	exec  @Ni, 0, @Nc output
	select @i = datepart(day, @)
	exec  @i, 0, @ output
	select @o = @o + @Nc + "N "
		+ datename(month, @) + " "
		+ @ + " "
		+ datename(weekday, @) + "j"
end
else
begin
	select @o = ""
		+ ltrim(convert(char, datepart(year, @))) + "N "
		+ ltrim(convert(char, datepart(month, @))) + " "
		+ ltrim(convert(char, datepart(day, @))) + " "
		+ datename(weekday, @) + "j"
end
if @o̓tO = 1
begin
	print @o
end
return (0)
go
/*
**	\
**	exec \ [@ datetime[, @ tinyint
**		[, @o output]]]
**	:
**	= 0 17 13 39b
**	= 1 ߌ5 13 39b
**	= 2 \ \O O\b
**	= 3 ߌ܎ \O O\b
*/
create procedure \
	@	datetime = null,
	@	tinyint = null,
	@o	varchar(32) = null
as
declare	@	varchar(6),
	@	varchar(6),
	@b	varchar(6),
	@i	tinyint,
	@o̓tO	bit
set nocount on
if @ is null
begin
	select @ = getdate()
end
if (@ < 0 or @ > 3) or @ is null
begin
	select @ = 0
end
if @o is null
begin
	select @o̓tO = 1
end
/*  */
select @i = datepart(hour, @), @o = null
if @ in (1, 3)
begin
	if @i > 11
	begin
		select @o = "ߌ ", @i = @i - 12
	end
	else
	begin
		select @o = "ߑO "
	end
end
if @ in (2, 3)
begin
	select @ = "1", @ = "1", @b = "1"
	exec  @i, 0, @ output
	select @i = datepart(minute, @)
	exec  @i, 0, @ output
	select @i = datepart(second, @)
	exec  @i, 0, @b output
end
else
begin
	select @ = ltrim(convert(char, @i))
	select @ = ltrim(convert(char, datepart(minute, @)))
	select @b = ltrim(convert(char, datepart(second, @)))
end
select @o = @o + @ + " "
	+ @ + " "
	+ @b + "b"
if @o̓tO = 1
begin
	print @o
end
return (0)
go
grant exec on  to public
grant exec on  to public
grant exec on ^CgR[h to public
grant exec on X to public
grant exec on XR[h to public
grant exec on ̔͏ to public
grant exec on ̔ڍד͏ to public
grant exec on ^Cg to public
grant exec on  to public
grant exec on z\ to public
grant exec on t\ to public
grant exec on \ to public
go
grant create procedure to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	execute sp_adduser guest
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	grant all on oŎ to guest
	grant all on ^Cg to guest
	grant all on  to guest
	grant all on ҃^Cg to guest
	grant all on ̔ to guest
	grant all on ̔ڍ to guest
	grant all on 쌠 to guest
	grant all on X to guest
	grant all on  to guest
	grant all on ҏё to guest
	grant all on ` to guest
	grant exec on ŗ to guest
	grant exec on  to guest
	grant exec on  to guest
	grant exec on ^CgR[h to guest
	grant exec on X to guest
	grant exec on XR[h to guest
	grant exec on ̔͏ to guest
	grant exec on ̔ڍד͏ to guest
	grant exec on ^Cg to guest
	grant exec on  to guest
	grant exec on z\ to guest
	grant exec on t\ to guest
	grant exec on \ to guest
	grant create table to guest
	grant create view to guest
	grant create rule to guest
	grant create default to guest
	grant create procedure to guest
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "sybsystemprocs")
begin
	use sybsystemprocs
end
else
begin
	/*
	** 19528, "The '%1!' database does not exist. Terminating the installation."
	*/
	raiserror 19528, "sybsystemprocs"
	select syb_quit()
end
go
if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go
grant exec on sp_bindefault to guest
grant exec on sp_unbindefault to guest
grant exec on sp_bindrule to guest
grant exec on sp_unbindrule to guest
grant exec on sp_addtype to guest
grant exec on sp_droptype to guest
grant exec on sp_spaceused to guest
grant exec on sp_help to guest
grant exec on sp_helpgroup to guest
grant exec on sp_helpindex to guest
grant exec on sp_helprotect to guest
go
dump transaction jpubs with truncate_only
go
use jpubs
go
if (db_name() != "jpubs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "jpubs"
	select syb_quit()
end
go
exit
