/* @(#) generic/localization/scripts/deckanji/installjpubs	195.1	9/6/95 */
/* Has primary and foreign keys, plus text and image tables*/
/* primary  foreign , text  image ơ֥ɲ */

/*
** 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 'ǡ١ "jpubs" Ƥޤ'
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 
	(ԥ id not null,
	̾ varchar(40) not null,
	ᤤ varchar(60) not null,
	̾ varchar(20) not null,
	ʤޤ varchar(30) not null,
	 char(17) not null,
	 varchar(40) null,
	夦 varchar(60) null,
	ԶĮ¼ varchar(20) null,
	礦 varchar(30) null,
	ƻܸ varchar(20) null,
	Ȥɤդ varchar(30) null,
	country varchar(12) null,
	 varchar(12) null,
	͹ֹ char(10) null)
end
go
grant select on  to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table Ǽ
	(ǥ char(4) not null,
	Ǹ varchar(40) null,
	äѤ varchar(60) null,
	ԶĮ¼ varchar(20) null,
	礦 varchar(30) null,
	ƻܸ varchar(20) null,
	Ȥɤդ varchar(30) null)
end
go
grant select on Ǽ to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table 
	(ȥ륳 tid not null,
	 int null,
	 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 
	(Ź char(4) not null,
	ʸֹ varchar(20) not null,
	 datetime not null)
end
go
grant select on  to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table ܺ
	(Ź char(4) not null,
	ʸֹ varchar(20) not null,
	ȥ륳 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 ԥȥ
	(ԥ id not null,
	ȥ륳 tid not null,
	 tinyint null,
	Ψ int null)
end
go
grant select on ԥȥ to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table ȥ
	(ȥ륳 tid not null,
	̾ varchar(80) not null,
	 char(12) not null,
	ǥ char(4) null,
	 money null,
	ʧ money null,
	 int null,
	 varchar(200) null,
	 datetime not null,
	 bit not null )
end
go
grant select on ȥ to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table Ź
	(Ź char(4) not null,
	Ź̾ varchar(40) null,
	Ƥᤤ varchar(60) null,
	Ź varchar(40) null,
	ߤ夦 varchar(60) null,
	ԶĮ¼ varchar(20) null,
	礦 varchar(30) null,
	ƻܸ varchar(20) null,
	Ȥɤդ varchar(30) null,
	 varchar(12) null,
	country varchar(12) null,
	͹ֹ char(10) null,
	ʧ varchar(12) null)
end
go
grant select on Ź to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "jpubs")
begin
	create table 
	(   varchar(40) not null,
	Ź         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 Ծ
	(ԥ	char(11) not null,
	̿		image null,
	ǡ	char(11) null,
	Хȥ	int null,
	Ĳǿ	char(14) null,
	ǿ	char(14) null)
end
go
grant select on Ծ to public
go
if exists (select * from master.dbo.sysdatabases 
                where name = "jpubs") 
begin 
	create table 
	(ԥ	id not null,
	 ԡ	text null)
end
go 
grant select on  to public 
go 
execute sp_primarykey ȥ, ȥ륳
execute sp_primarykey ԥȥ, ԥ, ȥ륳
execute sp_primarykey , ԥ
execute sp_primarykey Ǽ, ǥ
execute sp_primarykey , ȥ륳
execute sp_primarykey , Ź, ʸֹ
execute sp_primarykey ܺ, Ź, ʸֹ
execute sp_primarykey Ź, Ź
execute sp_primarykey , , Ź	 /* problem? ꡩ */
execute sp_primarykey Ծ, ԥ
execute sp_primarykey , ԥ
go
execute sp_foreignkey ԥȥ, ȥ, ȥ륳
execute sp_foreignkey ԥȥ, , ԥ
execute sp_foreignkey , ȥ, ȥ륳
execute sp_foreignkey , Ź, Ź
execute sp_foreignkey ܺ, ȥ, ȥ륳
execute sp_foreignkey ܺ, , Ź, ʸֹ
execute sp_foreignkey ȥ, Ǽ, ǥ
execute sp_foreignkey , Ź, Ź
execute sp_foreignkey Ծ, , ԥ
execute sp_foreignkey , , ԥ
go

create unique clustered index Ǹ
on Ǽ (ǥ)
go
create unique clustered index ԥɸ
on  (ԥ)
go
create nonclustered index ̾
on  (̾, ̾)
go
create unique clustered index ȥ륳ɸ
on ȥ (ȥ륳)
go
create nonclustered index ȥ븫
on ȥ (̾)
go
create unique clustered index ԥȥ븫
on ԥȥ (ԥ, ȥ륳)
go
create nonclustered index ԥɸ
on ԥȥ (ԥ)
go
create nonclustered index ȥ륳ɸ
on ԥȥ (ȥ륳)
go
create unique clustered index 丫
on  (Ź, ʸֹ)
go
create nonclustered index ȥ륳ɸ
on ܺ (ȥ륳)
go
create nonclustered index ܺٸ
on ܺ (Ź, ʸֹ)
go
create nonclustered index ȥ륳ɸ
on  (ȥ륳)
go
create default ǥեȼ
as "̤"
go
sp_bindefault ǥեȼ, "ȥ."
go
create default ǥե
as getdate()
go
sp_bindefault ǥե, "ȥ."
go
create default ǥե
as ""
go
sp_bindefault ǥե, "."
go
insert 
values("409-56-7008", "ʢ", "Ϥ", "Ϻ",
"󤿤", "03-3508-0727", "ëۥ֣ݣݣ",
"Ȥ礦Ȥ䤯褦",
"ë", "䤯", "", "Ȥ礦", "Japan",
"", "104")
go
insert  values("409-56-9004", "󥸥˥",
"󤼤󤨤󤸤ˤ", "", "֤",
"044-193-3323", "ԹĮݣ",
"ˤޤ襤꤫ۤ礦", "", "ˤޤ",
"", "ʤ櫓", "Japan", "", "210")
go
insert 
values("213-46-8915", "ȹ", "褷Ҥ", "",
"褳", "0424-36-2882", "ջĮݣݣ",
"ʤ櫓Ϥޤ礦", "ջĮ", "Ϥޤ礦",
"", "ʤ櫓", "Japan", "", "241")
go
insert 
values("238-95-7766", "", "ޤ", "", "Ҥ",
"03-3773-2366", "Ķ軳Įݣ",
"Ȥ礦ȤΤ礦", "Ķ", "",
"", "Ȥ礦", "Japan", "", "102")
go
insert 
values("998-72-3567", "ë", "", "", "",
"045-473-1976", "ͻԹ̶迷ͣݣݣ",
"ʤ櫓褳Ϥޤۤ褳Ϥ",
"ͻ", "褳Ϥޤ", "", "ʤ櫓", "Japan",
"", "222")
go
insert 
values("899-46-2035", "", "ۤ", "", "󤤤",
"0473-23-1234", "̸»Ƿ⣱ݣݣ",
"ޤ󤦤路ۤΤ", "»", "路",
"̸", "ޤ", "Japan", "", "264")
go
insert 
values("722-51-5454", "ե֥", "դ֤",
"ʡݣ", "ä", "̤", "", NULL, NULL, NULL, NULL,
NULL, "France", "ե", NULL)
go
insert 
values("807-91-6654", "", "", "绰Ϻ",
"֤", "03-3868-4799",
"ë豨ݣݣ",
"Ȥ礦Ȥ䤯餹",
"ë", "䤯", "", "Ȥ礦", "Japan",
"", "104")
go
insert 
values("893-72-1158", "", "ꤿ", "",
"", "0433-26-4354", "һͳ12-6",
"ʤ櫓󤫤ޤ餷椤Ϥ", "һ", "ޤ餷",
"", "ʤ櫓", "Japan", "", "236")
go
insert 
values("724-08-9931", "ܺ", "ߤ䤶", "",
"ޤҤ", "0423-37-2332", "߻Ա¼ݣݣ",
"Ȥ礦ȤᤷϤ", "߻", "ᤷ", "",
"Ȥ礦", "Japan", "", "256")
go
insert 
values("274-80-9391", "¼", "", "",
"ޤ", "0988-23-1198", "츩ƻƣݣ",
"ʤ櫓ʤϤ", "ƻ", "ʤϤ", "츩",
"ʤ櫓", "Japan", "", "996")
go
insert 
values("756-30-7391", "å", "ä",
"⡼ꥹ", "⡼ꤹ", "415-658-9932",
"6223 Bateman St. Berkeley CA", "դˤ夦С줤",
"Berkeley", "С줤", "CA", "դˤ",
"USA", "ꥫ", "94705")
go
insert 
values("724-80-9391", "ƣ", "Ȥ", "",
"ޤҤ", "044-544-2331", "ԹŶţݣ",
"ʤ櫓󤫤蘆Ĥ", "", "蘆",
"", "ʤ櫓", "Japan", "", "211")
go
insert 
values("427-17-2319", "", "", "ǯ", "䤹Ȥ",
"0862-12-1234", "Ծ뻳ݣ",
"ޤ󤪤ޤ", "", "ޤ", "",
"ޤ", "Japan", "", "629")
go
insert 
values("672-71-3249", "", "Ĥ", "ɧ", "褷Ҥ",
"0428-21-3457", "ԶϾ죲",
"Ȥ礦Ȥդ夦Ф礦", "", "դ夦", "",
"Ȥ礦", "Japan", "", "183")
go
insert 
values("267-41-2394", "ȥե顼", "Ȥդ顼", "ӥ", "Ӥ",
"212-355-9856", "350 Park Avenue New York, NY",
"ˤ塼衼夦ˤ塼衼", "New York", "ˤ塼衼", "NY",
"ˤ塼衼", "USA", "ꥫ", "10022")
go
insert 
values("472-27-2349", "", "Ȥ", "Ϻ", "",
"03-3503-4753", "ݣݣ",
"Ȥ礦ȤȤҤ", "", "Ȥ",
"", "Ȥ礦", "Japan", "", "110")
go
insert 
values("527-72-3246", "", "Ƥ餪", "СХ",
"СФ", "03-3241-1049", "ܶݣݣ",
"Ȥ礦Ȥ夦ˤۤФ", "", "夦",
"", "Ȥ礦", "Japan", "", "103")
go
insert 
values("172-32-1176", "¿", "", "", "ߤ",
"022-398-0412", "ܾ븩ջݣݣ",
"ߤ䤮󤻤Ф", "", "",
"ܾ븩", "ߤ䤮", "Japan", "", "981")
go
insert 
values("712-45-1867", "꡼", "꡼", "",
"", "415-938-6445", "5420 College Av. Oakland CA",
"դˤ夦", "Oakland", "",
"CA", "դˤ", "USA", "ꥫ", "95428")
go
insert 
values("846-92-7186", "", "Ҥ", "ɸ", "",
"0426-22-1346", "ȬһԻĮݣ",
"Ȥ礦ȤϤƤޤ", "Ȭһ", "Ϥ",
"", "Ȥ礦", "Japan", "", "135")
go
insert 
values("486-29-1786", "ꥹƥ", "ꤹƤ",
"", "", "̤", "", NULL, NULL, NULL, NULL, NULL,
"England", "ꥹ", NULL)
go
insert 
values("648-92-1872", "¼", "ޤ", "", "",
"0196-25-9845", "긩ݣݣ",
"Ƥꤪ夦", "", "ꤪ", "긩",
"Ƥ", "Japan", "", "020")
go
insert Ǽ
values("1389", "ʸҽǶ", "֤󤷤㤷äѤ󤭤礯",
"ʸ", "֤󤭤礦", "", "Ȥ礦")
go
insert Ǽ
values("0736", "ʸ", "֤󤫤", "ɶ",
"󤸤夯", "", "Ȥ礦")
go
insert Ǽ
values("0877", "Ź", "ȤޤƤ", "", "ߤʤȤ",
"", "Ȥ礦")
go
insert Ǽ
values("1622", "޽", "ޤäѤ", "", "ҤΤ",
"", "Ȥ礦")
go
insert Ǽ
values("1756", "ĽսǼ", "󤷤äѤ󤷤",
"Ķ", "", "", "Ȥ礦")
go
insert Ǽ
values("9987", "ե饸", "դ餸äѤ",
"", "ߤʤȤ", "", "Ȥ礦")
go
insert Ǽ
values("9911", "", "礦Ǥ󤷤",
"", "蘆", "", "ʤ櫓")
go
insert Ǽ
values("9934", "®˼", "Ϥ䤫路ܤ",
"ë", "֤䤯", "", "Ȥ礦")
go
insert Ǽ
values("9952", "ĽŹ", "Ƥ",
"Ļ", "", "ĸ", "")
go
insert Ǽ
values("9922", "ʿ߼", "ؤܤ󤷤",
"Ķ", "", "", "Ȥ礦")
go
insert Ǽ
values("9964", "̼", "󤷤",
"ë", "֤䤯", "", "Ȥ礦")
go
insert Ǽ
values("9977", "˼", "ޤܤ",
"ʸ", "֤󤭤礦", "", "Ȥ礦")
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 ԥȥ
values("409-56-7008", "BU1349", 1, 50)
go
insert ԥȥ
values("893-72-1158", "BU1349", 2, 50)
go
insert ԥȥ
values("409-56-9004", "BU9196", 1, 100)
go
insert ԥȥ
values("238-95-7766", "DC1437", 1, 65)
go
insert ԥȥ
values("807-91-6654", "DC1437", 2, 35)
go
insert ԥȥ
values("998-72-3567", "PC3856", 1, 100)
go
insert ԥȥ
values("899-46-2035", "MP9231", 1, 100)
go
insert ԥȥ
values("724-08-9931", "PC1363", 1, 100)
go
insert ԥȥ
values("722-51-5454", "PC3100", 1, 30)
go
insert ԥȥ
values("807-91-6654", "PC3100", 2, 70)
go
insert ԥȥ
values("724-08-9931", "BU1360", 1, 100)
go
insert ԥȥ
values("274-80-9391", "PC3250", 1, 100)
go
insert ԥȥ
values("756-30-7391", "PC3256", 1, 70)
go
insert ԥȥ
values("724-80-9391", "PC3256", 2, 30)
go
insert ԥȥ
values("427-17-2319", "PC3389", 1, 100)
go
insert ԥȥ
values("672-71-3249", "PC3679", 1, 100)
go
insert ԥȥ
values("267-41-2394", "BU9185", 1, 70)
go
insert ԥȥ
values("472-27-2349", "BU9185", 2, 30)
go
insert ԥȥ
values("527-72-3246", "MC2836", 1, 100)
go
insert ԥȥ
values("172-32-1176", "GL1331", 1, 100)
go
insert ԥȥ
values("213-46-8915", "MC0738", 1, 100)
go
insert ԥȥ
values("712-45-1867", "PS2089", 1, 70)
go
insert ԥȥ
values("846-92-7186", "PS2089", 2, 30)
go
insert ԥȥ
values("486-29-1786", "MS4021", 1, 20)
go
insert ԥȥ
values("648-92-1872", "MS4021", 2, 80)
go
insert ȥ
values("BU1349", "Ǥ֣Σϡפȸ", "ӥͥ", "1839",
\850, \500000, 4095,
"ܤΰĤ  ˡĲĹʢϺ᤬ƹȿ򴬤֣֡Σϡפȸ פɮǡ",
"1990/05/30", 1)
go
insert ȥ
values("BU1360", "줫кϿ", "ӥͥ", "0736",
\1360, \400000, 8070,
"Х֥кѤ⡢ΤȤޤ褦Ǥ롣ܤˤ餺ꥫǤʵȤˬƤ롣кϿޤϡɤɤ꤫ƤΤݡݡХդμӤܤȥɥĤγ˽򾷤",
"1991/04/18", 1)
go
insert ȥ
values("PC3250", 'ࡼΦΰˤä  ĴǤ狼äȵסɤο',
"ʸ", "0877", \1300, \360000, 312,
"ࡼΦϡǤϥ礭ʥ西äȤƤ롣κȤʤäࡼΦϼºߤϸߤβդˤäˤ˾¿ɼԤΥޥ򤫤ƤƤ롣",
"1991/06/30", 1)
go
insert ȥ
values("PC3256", "ŷʸؤȣգƣ", "ʳ", "1622", \1600, \120000, 702,
"ܽϥץŷʸؼԤˤգƣϸȤܤ٤Ǥ롣Ǵ¬줿ԲĲʸݤ˴ؿ餬գƣϤξȹͤϵ峰Ū̿λŶȤǤϤʤȹͻƤ롣",
"1991/03/10", 1)
go
insert ȥ
values("PC3389", "ϤʤҥȤˤʤʤΤ  ʪʲͤʤ",
"ʳ", "1839", \770, \80000, 286,
"ʪοʲϡΤǡʹ֤िˤȤʤΤܻؤƤΤΤǤϤʤʹ֡ʲʪȤʹ濴ˡΤ餺Τ餺Ƥ桹ˤȤäơԤιͤϿǤ롣",
"1991/01/30", 1)
go
insert ȥ
values("PC3679", "ĶʳؤѤʹ֤Σǯ壱ǯ  äƤϤʤϸ¤",
"ʳ", "1756", \750, \60000, 833,
"ˤƿबǤѤ߽ФҲͽ¬롣",
"1991/04/01", 1)
go
insert ȥ
values("PC1363", "女ԥ塼ܤĩ", "ӥͥ", "9987",
\1600, \450000, 1342,
	"ʹ֤ʸƤ줫ʸʹ֤ϤϤΤܤײϡƹˤȤäƼư֤ʤɤȤ٤Τˤʤʤ餤ȤʤäƤ롣",
"1983/08/01",
1)
go
insert ȥ
values("MC0738", "ƥ붵", "", "9922", \580, \30000, 328,
"ƥʤǤϳڤѡƥαФϤǤʤ塪ץˤ⥢ޥ奢ˤƥνޥʡ夷",
"1979/06/20", 1)
go
insert ȥ
values("BU9196", "ѲĤ񤤤Ūפι",
"ӥͥ", "9911", \1200, \20000, 7362,
"ֿͤǽϥåפȿڳפȤ礭˼Ȥ󥸥˥ΡεϿǤ롣餬ʤפξĳưȤϤɤΤ褦ʤΤäΤ̤ϡ",
"1987/04/24", 1)
go
insert ȥ
values("BU9185", "ѥե  塦",
"ӥͥ", "9987", \1600, \120000, 4023,
"ޤǤ٤ƤϹ¤Τ٤ƤĤĤִ֤ˤ޲桹Ƥ롣ߤϸϹ¤ѲʥեȡˤĤĤѥեȤλʤΤǤ롣",
"1990/11/20", 1)
go
insert ȥ
values("MC2836", "֣м̿פ᤭Ф  λ",
"ʸ", "0877", \760, \60000, 653,
"ǶΡ֣м̿סݿ̺ˤϡܿͤĶǥˤ褦ʤΡפȷٹ𤷤Ƥ롣ܽ˾ŪȤ⤤ǡη򹯤뤫ơޤȤʤäƤ롣",
"1990/10/31", 1)
go
insert ȥ
values("GL1331", "̾ϥꥫ׿͡41ΰ˾",
"", "0877", \1100, \40000, 859,
"ǯƹ餷ԤϡϤƹͤ׿ͤȯư줳­ˤޤˤ뤳Ȥ˶ä׿ͤĴϤƸȡˤ϶̣οԤʤɥޤä",
"1990/01/30", 1)
go
insert ȥ
values("PS2089", "ԡݥݡѥ󡦥ɥݥࡡʤͤˤʤʤΤ",
"", "9911", \1600, \30000, 898,
"ԡݥݡѥϡʱξǯǤ롣ͼҲǤꡢ̴ι֤ʤʤפ餹ǯϤȤ뤬ԡݥݡѥϡͤˤʤꤿʤοξɸԡѥ󡦥ɥǤ롣",
"1984/05/05", 1)
go
insert ȥ
values("MS4021", "ꥹƥ£ǯǰ֥å",
"ߥƥ", "9934", \1500, \20000, 412,
"Υߥƥνϥꥹƥ£ǯˤ롣͡Ϻ΢ʤδꥹȱǲ衦രꥹȤʤɡΤˤϳʹΰȤ롣",
"1990/11/30", 1)
go
insert ȥ
values("TD2365", "⣱",
"", "9952", \1700, \15000, 226,
'ܳϤ˻ĤäƤϡŤ˭ʼ餷ȼʸ򻺤߰Ǥ͡ΡηäȼɤΤǤ롣桹ˡȡȸŤɤܡɤؤζӤƤ롣',
"1990/10/25", 1)
go
insert ȥ
values("DC1437", "ưʪ޴ա",
"޴", "9922", \20000, \30000, 412,
"̤ΥʬˡȤϤäơʹ֤ȥåפˤȤ¤ο޴դȤϤǤ롣Ǥ϶ưʪؤӽͶưʪȤŷˤĤƤܺ٤ʵҤʤƤ롣",
"1988/05/27", 1)
go
insert ȥ
values("PC3856", "Ϥ٤뤷",
"ʳ", "9964", \640, \5000, 612,
"Τι¤ðϡ֤ǤưʪˤߤʤϡˤΤϤʤǤ롣",
"1989/10/27", 1)
go
insert ȥ
values("MP9231", "ϿޤΥΥ", "Ͽ", "9977",
\3300, \20000, 327,
"衼åѤθϿޤᤰ뤳ܤϡ˭ɺ碌ƻϤƤ롣줷ʤ褦ϿޤܤƤǤ롣",
"1989/11/24", 1)
go
insert ȥ
values("PC3100", "ե֥뺫", "", "0736",
\1300, \20000, 532,
"Ȥե֥뺫ǡǯäեʸؼԤǤϤ狼ʤäʬ򤪤ʤǯδưˤäŤ褦ȤϫͤɤǤʤ⤷ʤ",
"1991/06/21", 0)
go
dump transaction jpubs with truncate_only
go
insert Ź
values("7066", "ͧ˲ƲŹ", "椦ۤɤƤ", "Կɶݣݣݣ", "Ȥ礦Ȥ󤸤夯", "ɶ", "󤸤夯", "", "Ȥ礦", "", "Japan", "106", "")
go
insert Ź
values("7072", "", "礻󤰤", "ĶĮݣݣ", "Ȥ礦Ȥܤ礦", "Ķ", "", "", "Ȥ礦", "", "Japan", "104", "")
go
insert Ź
values("7078", "ͭƲ", "椦ɤ", "ͻĮݣݣ", "褳Ϥޤʤ礦", "ͻ", "褳Ϥޤ", "", "ʤ櫓", "", "Japan", "223", "")
go
insert Ź
values("6032", ";ƲŹ", "ɤƤ", "ĶĮݣݣ", "Ȥ礦Ȥܤ礦", "Ķ", "", "", "Ȥ礦", "", "Japan", "104", "")
go
insert Ź
values("8029", "Фι񲰽Ź", "ҤΤˤ䤷Ƥ", "Կɶ迷ɣݣݣ", "Ȥ礦Ȥ󤸤夯󤸤夯", "ɶ", "󤸤夯", "", "Ȥ礦", "", "Japan", "106", "")
go
insert Ź
values("7896", "Ȭ֥åŹ", "䤨֤äƤ", "ȬŽݣݣ", "Ȥ礦Ȥ夦䤨", "", "夦", "", "Ȥ礦", "", "Japan", "104", "")
go
insert Ź
values("7298", "Ź", "󤷤Ƥ", "Թݣݣ", "Ȥ礦ȤߤʤȤˤФ", "", "ߤʤȤ", "", "Ȥ礦", "", "Japan", "105", "")
go
insert 
values("̳", NULL, NULL, NULL, 10.5)
go
insert 
values("̳", "7072", 100, 1000, 6.7)
go
insert 
values("̳", "7078", NULL, NULL, 5)
go
insert 
values("եڡ̳", "7298", NULL, NULL, 3)
go
insert  values("409-56-7008",
"£ǯޤ졣ء⹻´Ȥ춶ء
£ǯ裲ۤεפȯɽ⣳ǯ裳ޤޡ
Ʊǯ´ȤȤȤ˺롣£ǯ˻ıΩ䡢
ѶŪưΤ롣ͥʢͼϺμǤ롣")
insert  values("274-80-9391",
"ΰؽ칶ϳϼء仰ȯ˱٤
ϴήФͽܤ򽸤᤿κȤʤäࡼΦϼºߤ
ϸߤβդˤäȼĥؼԤǤ⤢롣")
insert  values("998-72-3567",
"ΤȸƤФ建ؤζǤ롣֤ɤʪǤ̯ˤǤ
ƤȤФޤǤǤ̵ۤưʪưʪȤϤ¿
ǥ䵡ǽĤ⤢ޤפȥΤϸΤˡΤϡ
¤Ǥ⡢ʤŪʿͤμ߷פ줿ΤǤϤʤդȽ
ʪ˽񤤤Ƥ롣")
insert  values("722-51-5454",
"Υե󥹤κؼԡȤե֥뺫Ԥϡ
ӤΥǶ̤ĤꡢߤĤ륹١ʵ
ˤդơԻ׵ĤˤޤȤͤϡ򤭤ä˺
ǯˤʤä⤷ʤޤ˻Ҥɤ̴ͿƤƤ롣")
insert  values("267-41-2394",
'ǯΡ̤ξ׷١ǯΡ軰ȡ٤³ǯ֤
إѥեȡ٤ȯɽ줿ΣϤΩʤȤƤɤ
ΤǤ뤬ޤȤȡŪ˰ӤɤʪȤʤ褦Ǥ롣
Ǥ뤬礭ưĤŤξɤ߼뤿ˤϣ
̤ɤ߿ʤΤ٥Ȥ')
go

create rule ǥɵ§
as @ǥ in ("1389", "0736", "0877", "1622", "1756")
or @ǥ like "99[0-9][0-9]"
go
sp_bindrule ǥɵ§, "Ǽ.ǥ"
go
create rule ȥ륳ɵ§
as
@ȥ륳 like "BU[0-9][0-9][0-9][0-9]" or	/* ӥͥ */
@ȥ륳 like "M[CPS][0-9][0-9][0-9][0-9]" or
				/* ʸϿޡߥƥ꡼ */
@ȥ륳 like "P[SC][0-9][0-9][0-9][0-9]" or
				/* ءʸʳء */
@ȥ륳 like "TD[0-9][0-9][0-9][0-9]" or	/*  */
@ȥ륳 like "GL[0-9][0-9][0-9][0-9]" or	/*  */
@ȥ륳 like "DC[0-9][0-9][0-9][0-9]" or	/* 񡦿޴ */
@ȥ륳 like "[A-Z][A-Z]xxxx" or
@ȥ륳 like "[A-Z][A-Z]yyyy"

/*ͭ:  BU, MC, MP, MS, PS, PC TD, GL, DC + 4ο
**뤤
**ʸȾʸΥե٥åȤ³ xxxx  yyyy ǹ
*/
go
sp_bindrule ȥ륳ɵ§, "ȥ.ȥ륳"
go
sp_bindrule ȥ륳ɵ§, "ܺ.ȥ륳"
go
create trigger ȥ
on ȥ
for delete
as
if (select count(*) from deleted, ܺ
where ܺ.ȥ륳 = deleted.ȥ륳) >0
begin
	rollback transaction
 	print "'ܺ'˳'ȥ륳'뤿ᡢȥϤǤޤ"
end
go
create trigger ȥꥬ
on ܺ
for insert, update, delete
as
/* ¸:  ƶԤʤ */
if @@rowcount = 0
	begin
		return
	end

/* ٤Ƥοͤɲ */
/* isnull : ȥơ֥ null ͤΰ̣
**		"̤"Ǥꡢ""ǤϤʤ
*/
update ȥ
        set  = isnull(, 0) + (select sum()
		from inserted
		where ȥ.ȥ륳 = inserted.ȥ륳)

/* deleted ޤ updated Ƥͤκ */
update ȥ
        set  = isnull(, 0) - (select sum()
		from deleted
		where ȥ.ȥ륳 = deleted.ȥ륳)
go
create view ȥӥ塼
as
select ̾, , ̾,
, , ǥ
from , ȥ, ԥȥ
where .ԥ = ԥȥ.ԥ
and ȥ.ȥ륳 = ԥȥ.ȥ륳
go
create procedure Ψ @ѡ int
as
select ԥ from ԥȥ
where ԥȥ.Ψ = @ѡ
go
grant execute on Ψ to public
go
/* APT  Sales ѤΥץ */
create proc  @Ź char(4)
as
select ,
       .ʸֹ,
       ,
       ܺ.ȥ륳,
       Ψ,
       ,
        =  *  * (1 - Ψ/100)
from , ܺ, ȥ
where .Ź = @Ź
and .ʸֹ = ܺ.ʸֹ
and ȥ.ȥ륳 = ܺ.ȥ륳
order by  desc, .ʸֹ
go
create proc  as
select , Ź, , , Ψ
from 
go
create proc Ź̾
@Ź̾      varchar(40)
as
declare @ʸ̾ varchar(40)
select  @ʸ̾ = lower(@Ź̾) + "%"
select  Ź̾,
        Ź,
        Ź,
        ԶĮ¼,
        ƻܸ,
        ͹ֹ,
        country
from    Ź
where   lower(Ź̾) like @ʸ̾
return  @@rowcount
go
create proc Źɽ
@Ź	char(4)
as
select Ź̾,
        Ź,
        Ź,
        ԶĮ¼,
        ƻܸ,
        ͹ֹ,
        country
from Ź
where Ź = @Ź
return @@rowcount
go
create proc Ͻ
	@Ź	char(4),
	@ʸֹ	varchar(20),
	@ʸ	varchar(40)
as
insert 
values(@Ź, @ʸֹ, @ʸ)
go
create proc ܺϽ
	@Ź        char(4),
	@ʸֹ        varchar(20),
	@ȥ륳       tid,
	@            smallint,
	@Ψ       float
as
insert ܺ
values(@Ź, @ʸֹ, @ȥ륳, @, @Ψ)
go
create proc ȥ ( @̾ varchar(40))
as
select @̾ = lower( @̾ ) + "%"
select  ̾, ȥ륳, 
from    ȥ
where   lower(̾) like @̾
return @@rowcount
go
create proc ȥ륳ɽ ( @ȥ륳 varchar(80))
as
select @ȥ륳 = lower( @ȥ륳 ) + "%"
select  ̾, ȥ륳, 
from    ȥ
where   lower(ȥ륳) like @ȥ륳
return @@rowcount
go
/*
**	ؤѴѥץ
**	exec  @ money, @ tinyint[, @ varchar(60)]
**	
**	@:
**	0 = 黰ɴͽ (default)
**	1 = 黲ɴͽ
**	2 = ɴ
**	3 = ɴ
*/
create procedure 
	@ money,
	@ tinyint = null,
	@	varchar(60) = null OUTPUT
as
declare
	@	varchar(30),		/*  */
	@̼ char(12),		/* ̼Ѥδ */
	@	char(18),		/* Υץ */
	@1	char(2),		/* ߡ˲Ƚ */
	@奫	tinyint,	/* ٤ƤηΥ */
	@1	tinyint,	/* ޤǤη奫 */
	@2	tinyint,	/* η奫 */
	@	tinyint,		/* ơηˤ */
	@ϥե饰	bit

set nocount on
if @ = null
begin
	select @ϥե饰 = 1
end
if @ = 0
begin
	select @ = ""
	if @ϥե饰 = 1
	begin
		print @
	end
	return (0)
end
/* ϥ顼 */
if (@ < 0 and @ > 3) or @ is null
begin
	select @ = 0
end
select @ = ltrim(convert(char, @))
select @奫 = charindex(".", @) - 1
if @ = 0 or @ = 2
begin	/* @ = 0: 黰ɴͽ */
	select @̼ = "ɴ", @ = "󻰻͸ϻȬ"
end
else
begin	/* @ = 1: 黲ɴͽ */
	select @̼ = "ɴ", @ = "͸ϻȬ"
end
select 	@1 = 0, @2 = 3, @ = null

while(@奫 > 0)
begin
	select @ = convert(tinyint, substring(@, @奫, 1))
	if @1 = 0
	begin	/* ΰ */
		if @2 > 3
		begin	/*  ν */
			select @1 = substring(@, 1, 1)
			select @1 = @1 where
				@1 =
				substring(@̼, @2 - 1, 1)
			if @@rowcount = 0 or @2 = 4
			begin
				select @ =
				substring(@̼, @2, 1)
				+ @
			end
			else
			begin
				select @ =
				stuff(@, 1, 1,
				substring(@̼, @2, 1))
			end
		end
		if @ != 0
		begin	/* ν */
			select @ = substring(@, @, 1)
				+ @
		end
	end
	else
	begin	/* ɴΰ */
		if @ != 0
		begin
			if @ != 1
				or (@ in (2, 3) and @1 = 3)
			begin
				select @ =
					substring(@, @, 1)
				+ substring(@̼, @1, 1)
				+ @
			end
			else
			begin
				select @ =
				substring(@̼, @1, 1)
				+ @
			end
		end
	end
	select @1 = @1 + 1,
		@奫 = @奫 - 1
	if @1 > 3
	begin
		select @1 = 0, @2 = @2 + 1
	end
end	/* while ν */
if @ < 0
begin
	select @ = "" + @
end
if @ϥե饰 = 1
begin
	print @
end
return (0)
go
/*
**	ɽ
**	exec ɽ @, @[, @ = varchar(60)]
**	:
**	0 = \12345 (default)		3 = 黲ɴͽޱ
**	1 = 12345			4 = 1234567
**	2 = 黰ɴͽޱ 
*/
create procedure ɽ
	@ money,
	@ tinyint = null,
	@	varchar(60) = null OUTPUT
as
declare
	@work	varchar(30),
	@i1	tinyint,
	@ϥե饰 bit

set nocount on
if @ = null
begin
	select @ϥե饰 = 1
end
/* ϥ顼 */
if (@ < 0 and @ > 4) or @ is null
begin
	select @ = 0
end
select @work = ltrim(convert(char, @))
select @i1 = charindex(".", @work), @ = ""
/* \12345 */
if @ = 0
begin
	select @ = "\" + substring(@work, 1, @i1 -1)
end
else
begin
	/* 12345 */
	if @ = 1
	begin
		select @ = substring(@work, 1, @i1 -1) + ""
	end
	else
	begin
		if @ = 2
		begin
			exec  @, 0, @ output
			select @ = @ + ""
		end
		else
		begin
			if @ = 3
			begin
				exec  @, 1, @ output
				select @ = "" + @ + ""
			end
			else
			begin
				/* 1234567 */
				if @ = 4
				begin
					select @ = stuff(@work,
						@i1, 1, "")
						+ ""
				end
			end
		end
	end
end
if @ϥե饰 = 1
begin
	print @
end
return (0)
go
/*
**	ɽ
**	exec ɽ @ datetime, @ tinyint
**		[, @ varchari(36)]
**	:
**	= 0 1991ǯ 8 30 
**	= 1 ʿǯ Ȭ  
*/
create procedure ɽ
	@	datetime = null,
	@	tinyint = null,
	@	varchar(36) = null
as
declare	@ǯi	smallint,
	@ǯc	varchar(8),
	@	varchar(4),
	@	varchar(6),
	@i	int,
	@ϥե饰	bit
set nocount on
if @ is null
begin
	select @ = getdate()
end
if @ is null
begin
	select @ = 0
end
if @ is null
begin
	select @ϥե饰 = 1
end
select @ǯi = datepart(year, @)
if @ = 1
begin	/* Ѵ */
	if @ǯi <= 1911
	begin
		select @ǯi = @ǯi - 1867
		select @ = ""
	end
	else
	begin
		if @ǯi <= 1925
		begin
			select @ǯi = @ǯi - 1911
			select @ = ""
		end
		else
		begin
			if @ǯi <= 1988
			begin
				select @ǯi = @ǯi - 1925
				select @ = ""
			end
			else
			begin
				select @ǯi = @ǯi - 1988
				select @ = "ʿ"
			end
		end
	end
	select @ǯc = "1", @ = "1"
	exec  @ǯi, 0, @ǯc output
	select @i = datepart(day, @)
	exec  @i, 0, @ output
	select @ = @ + @ǯc + "ǯ "
		+ datename(month, @) + " "
		+ @ + " "
		+ datename(weekday, @) + ""
end
else
begin
	select @ = ""
		+ ltrim(convert(char, datepart(year, @))) + "ǯ "
		+ ltrim(convert(char, datepart(month, @))) + " "
		+ ltrim(convert(char, datepart(day, @))) + " "
		+ datename(weekday, @) + ""
end
if @ϥե饰 = 1
begin
	print @
end
return (0)
go
/*
**	ɽ
**	exec ɽ [@ datetime[, @ tinyint
**		[, @ output]]]
**	:
**	= 0 17 13ʬ 39
**	= 1 5 13ʬ 39
**	= 2  ʬ 
**	= 3 ޻ ʬ 
*/
create procedure ɽ
	@	datetime = null,
	@	tinyint = null,
	@	varchar(32) = null
as
declare	@	varchar(6),
	@ʬ	varchar(6),
	@	varchar(6),
	@i	tinyint,
	@ϥե饰	bit
set nocount on
if @ is null
begin
	select @ = getdate()
end
if (@ < 0 or @ > 3) or @ is null
begin
	select @ = 0
end
if @ is null
begin
	select @ϥե饰 = 1
end
/*  */
select @i = datepart(hour, @), @ = null
if @ in (1, 3)
begin
	if @i > 11
	begin
		select @ = " ", @i = @i - 12
	end
	else
	begin
		select @ = " "
	end
end
if @ in (2, 3)
begin
	select @ = "1", @ʬ = "1", @ = "1"
	exec  @i, 0, @ output
	select @i = datepart(minute, @)
	exec  @i, 0, @ʬ output
	select @i = datepart(second, @)
	exec  @i, 0, @ output
end
else
begin
	select @ = ltrim(convert(char, @i))
	select @ʬ = ltrim(convert(char, datepart(minute, @)))
	select @ = ltrim(convert(char, datepart(second, @)))
end
select @ = @ + @ + " "
	+ @ʬ + "ʬ "
	+ @ + ""
if @ϥե饰 = 1
begin
	print @
end
return (0)
go
grant exec on  to public
grant exec on  to public
grant exec on ȥ륳ɽ to public
grant exec on Ź̾ to public
grant exec on Źɽ to public
grant exec on Ͻ to public
grant exec on ܺϽ to public
grant exec on ȥ to public
grant exec on  to public
grant exec on ɽ to public
grant exec on ɽ 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 Ǽ to guest
	grant all on ȥ to guest
	grant all on  to guest
	grant all on ԥȥ to guest
	grant all on  to guest
	grant all on ܺ to guest
	grant all on  to guest
	grant all on Ź 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 ȥ륳ɽ to guest
	grant exec on Ź̾ to guest
	grant exec on Źɽ to guest
	grant exec on Ͻ to guest
	grant exec on ܺϽ to guest
	grant exec on ȥ to guest
	grant exec on  to guest
	grant exec on ɽ to guest
	grant exec on ɽ 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
