Plain Text Version - no formatting
/*
This code was written by Edward Stoever as an example for students of PL/SQL.
The concept is to create a relatively large schema with sample data that 
has meaning. The schema can subsequently be used for all kinds of classroom 
excercises such as backup and recovery, tuning, data mining, data pump, etc.

Note: Running this script as is will add approximately 200M of data to the 
USERS tablespace. Ensure that you have the room on your disk! This should 
not be run on a production database.

If you do not change the values for the number of rows created, this script
will complete in roughly 2.5 hours on a low-end student machine (P3, 512M RAM).

launch this scrip with the following command:
sqlplus /nolog @script_name 

-- 2006 Edward Stoever 

*/
/*

set verify off

accept system_password char prompt "Enter the password for SYSTEM: " hide

accept db_conn char prompt "Enter DB SID: "

accept user_2_create char prompt "Enter new username: "

accept new_user_pw char prompt "Enter new user's password: " hide

connect system/&&system_password@&&db_conn

create user &&user_2_create identified by &&new_user_pw;

grant create session, create table,

create sequence, create procedure,

create view, create trigger to &&user_2_create;

alter user &&user_2_create default tablespace users quota 500M on users;

connect &&user_2_create/&&new_user_pw@&&db_conn;

-- code begin

create or replace package session_vars

is

session_start_time timestamp;

session_part1_end_time timestamp;

session_part2_end_time timestamp;

session_end_time timestamp;

end;

/

begin session_vars.session_start_time := sysdate; end;

/

create or replace procedure disable_foreign_keys (p_table_name varchar2) is

CURSOR my_c

IS

SELECT 'alter table ' || table_name || ' disable constraint '

|| constraint_name

FROM user_constraints

WHERE constraint_type='R' AND status = 'ENABLED'

AND table_name = upper(p_table_name);

my_var varchar2(1000);

BEGIN

OPEN my_c;

LOOP

FETCH my_c INTO my_var;

EXIT WHEN my_c%NOTFOUND;

EXECUTE IMMEDIATE my_var;

END LOOP;

CLOSE my_c;

END;

/

create or replace procedure enable_foreign_keys is

CURSOR my_c

IS

SELECT 'alter table ' || table_name || ' enable constraint '

|| constraint_name

FROM user_constraints

WHERE constraint_type='R' AND status = 'DISABLED';

my_var varchar2(1000);

BEGIN

OPEN my_c;

LOOP

FETCH my_c INTO my_var;

EXIT WHEN my_c%NOTFOUND;

EXECUTE IMMEDIATE my_var;

END LOOP;

CLOSE my_c;

END;

/

create table fname (fname_id number,

fname_name varchar2(40),

fname_gender varchar2(1)) tablespace users;

insert into fname values (1,'Fred','M');

insert into fname values (2,'Mary','F');

insert into fname values (3,'Pat','U');

insert into fname values (4,'Hillary','F');

insert into fname values (5,'Beatriz','F');

insert into fname values (6,'Georgette','F');

insert into fname values (7,'Frank','M');

insert into fname values (8,'Geoffry','M');

insert into fname values (9,'Steven','M');

insert into fname values (10,'Beth','F');

insert into fname values (11,'Charley','M');

insert into fname values (12,'Yaqoush','M');

insert into fname values (13,'Brian','M');

insert into fname values (14,'Bethany','F');

insert into fname values (15,'Sybil','F');

insert into fname values (16,'Scarlett','F');

insert into fname values (17,'Veronica','F');

insert into fname values (18,'Michael','M');

insert into fname values (19,'Brittney','F');

insert into fname values (20,'Cher','F');

insert into fname values (21,'Angela','F');

insert into fname values (22,'Harold','M');

insert into fname values (23,'Brad','M');

insert into fname values (24,'Stuart','M');

insert into fname values (25,'Kathy','F');

insert into fname values (26,'Bill','M');

insert into fname values (27,'Mildred','F');

insert into fname values (28,'Erin','U');

insert into fname values (29,'Colly','F');

insert into fname values (30,'Lolly','F');

insert into fname values (31,'Mary-Kate','F');

insert into fname values (32,'Filmore','M');

insert into fname values (33,'Jeff','M');

insert into fname values (34,'Strove','M');

insert into fname values (35,'Bethanny','F');

insert into fname values (36,'Paula','F');

insert into fname values (37,'Binky','M');

insert into fname values (38,'Nestor','M');

insert into fname values (39,'Pauly','M');

insert into fname values (40,'Becky','F');

insert into fname values (41,'Thomas','M');

insert into fname values (42,'Xavier','M');

insert into fname values (43,'Billy','M');

insert into fname values (44,'Amanda','F');

insert into fname values (45,'Roberta','F');

insert into fname values (46,'Ona','F');

insert into fname values (47,'Gerry','M');

insert into fname values (48,'Beto','M');

insert into fname values (49,'Victor','M');

insert into fname values (50,'Valery','F');

insert into fname values (51,'Timmy','M');

insert into fname values (52,'Bob','M');

insert into fname values (53,'Jack','M');

insert into fname values (54,'Jill','F');

insert into fname values (55,'Stacy','F');

insert into fname values (56,'Norma','F');

insert into fname values (57,'William','M');

insert into fname values (58,'Thelma','F');

insert into fname values (59,'Emma','F');

insert into fname values (60,'Silvia','F');

insert into fname values (61,'Stan','M');

insert into fname values (62,'Lautauro','M');

insert into fname values (63,'Benny','M');

insert into fname values (64,'Kerry','F');

insert into fname values (65,'Jilian','F');

insert into fname values (66,'Umma','F');

insert into fname values (67,'Ken','M');

insert into fname values (68,'Wanda','F');

insert into fname values (69,'Lolly','F');

insert into fname values (70,'Pricilla','F');

insert into fname values (71,'Mark','M');

insert into fname values (72,'Roger','M');

insert into fname values (73,'Morry','M');

insert into fname values (74,'Sue','F');

insert into fname values (75,'Jackie','F');

insert into fname values (76,'Paulina','F');

insert into fname values (77,'Teodoro','M');

insert into fname values (78,'Yi','F');

insert into fname values (79,'Crissy','F');

insert into fname values (80,'Vanessa','F');

insert into fname values (81,'Allen','M');

insert into fname values (82,'Adam','M');

insert into fname values (83,'Greg','M');

insert into fname values (84,'Tippy','F');

insert into fname values (85,'Adrian','U');

insert into fname values (86,'Lucy','F');

insert into fname values (87,'Bert','M');

insert into fname values (88,'Grace','F');

insert into fname values (89,'Peggy','F');

insert into fname values (90,'Sissy','F');

commit;

create table lname (lname_id number, lname_name varchar2(40)) tablespace users;

insert into lname values (1,'Franko');

insert into lname values (2,'Welling');

insert into lname values (3,'Grabto');

insert into lname values (4,'Lawrence');

insert into lname values (5,'Plasto');

insert into lname values (6,'Chrand');

insert into lname values (7,'Tryo');

insert into lname values (8,'Black');

insert into lname values (9,'Matter');

insert into lname values (10,'Yahoo');

insert into lname values (11,'Liken');

insert into lname values (12,'Pott');

insert into lname values (13,'Starbuck');

insert into lname values (14,'Mather');

insert into lname values (15,'Ubb');

insert into lname values (16,'Tyron');

insert into lname values (17,'Into');

insert into lname values (18,'Strac');

insert into lname values (19,'Chilly');

insert into lname values (20,'Yado');

insert into lname values (21,'Lock');

insert into lname values (22,'Strasso');

insert into lname values (23,'Ink');

insert into lname values (24,'Tear');

insert into lname values (25,'Stew');

insert into lname values (26,'Till');

insert into lname values (27,'Opus');

insert into lname values (28,'Rondo');

insert into lname values (29,'Harp');

insert into lname values (30,'Picky');

insert into lname values (31,'Lulu');

insert into lname values (32,'Stamp');

insert into lname values (33,'Choolo');

insert into lname values (34,'Yarton');

insert into lname values (35,'Ulup');

insert into lname values (36,'Lack');

insert into lname values (37,'Placid');

insert into lname values (38,'Yoyo');

insert into lname values (39,'Orum');

insert into lname values (40,'Tyme');

insert into lname values (41,'York');

insert into lname values (42,'Tron');

insert into lname values (43,'Waz');

insert into lname values (44,'Plocko');

insert into lname values (45,'Yorn');

insert into lname values (46,'Pirend');

insert into lname values (47,'Yah');

insert into lname values (48,'Mired');

insert into lname values (49,'Stroble');

insert into lname values (50,'Mi');

insert into lname values (51,'Yeh');

insert into lname values (52,'Mikto');

insert into lname values (53,'Zibb');

insert into lname values (54,'Picker');

insert into lname values (55,'Radno');

insert into lname values (56,'Liu');

insert into lname values (57,'Zur');

insert into lname values (58,'Bock');

insert into lname values (59,'Brut');

insert into lname values (60,'Hrand');

insert into lname values (61,'Batt');

insert into lname values (62,'Loopo');

insert into lname values (63,'Zireo');

insert into lname values (64,'Piti');

insert into lname values (65,'Poin');

insert into lname values (66,'Midi');

insert into lname values (67,'Ole');

insert into lname values (68,'Joren');

insert into lname values (69,'Lire');

insert into lname values (70,'Star');

insert into lname values (71,'Lolo');

insert into lname values (72,'Blardy');

insert into lname values (73,'Trooper');

insert into lname values (74,'Zier');

insert into lname values (75,'Moodu');

insert into lname values (76,'Myno');

insert into lname values (77,'Torry');

insert into lname values (78,'Rondolo');

insert into lname values (79,'Plic');

insert into lname values (80,'Sro');

commit;

create table lname_suffix (lname_suffix_id number,

lname_suffix_suffix varchar2(10));

insert into lname_suffix values (1,'son');

insert into lname_suffix values (2,'ian');

insert into lname_suffix values (3,'io');

insert into lname_suffix values (4,'sto');

insert into lname_suffix values (5,'john');

insert into lname_suffix values (6,'smith');

insert into lname_suffix values (7,'tod');

insert into lname_suffix values (8,'cob');

insert into lname_suffix values (9,'blas');

insert into lname_suffix values (10,'ur');

insert into lname_suffix values (11,'igson');

insert into lname_suffix values (12,'ianmanson');

insert into lname_suffix values (13,'iotopolis');

insert into lname_suffix values (14,'stew');

insert into lname_suffix values (15,'johntrob');

insert into lname_suffix values (16,'smithicky');

insert into lname_suffix values (17,'topper');

insert into lname_suffix values (18,'col');

insert into lname_suffix values (19,'blastin');

insert into lname_suffix values (20,'iron');

insert into lname_suffix values (21,'la');

insert into lname_suffix values (22,'ron');

insert into lname_suffix values (23,'tra');

insert into lname_suffix values (24,'smyth');

insert into lname_suffix values (25,'lo');

insert into lname_suffix values (26,'pol');

insert into lname_suffix values (27,'er');

insert into lname_suffix values (28,'wa');

insert into lname_suffix values (29,'mir');

insert into lname_suffix values (30,'pire');

insert into lname_suffix values (31,'do');

insert into lname_suffix values (32,'gin');

insert into lname_suffix values (33,'ost');

insert into lname_suffix values (34,'ig');

insert into lname_suffix values (35,'lil');

insert into lname_suffix values (36,'uyh');

insert into lname_suffix values (37,'krop');

insert into lname_suffix values (38,'pip');

insert into lname_suffix values (39,'ind');

insert into lname_suffix values (40,'yasz');

insert into lname_suffix values (41,'op');

insert into lname_suffix values (42,'tor');

insert into lname_suffix values (43,'acing');

insert into lname_suffix values (44,'plo');

insert into lname_suffix values (45,'rond');

insert into lname_suffix values (46,'bing');

insert into lname_suffix values (47,'nono');

insert into lname_suffix values (48,'pore');

insert into lname_suffix values (49,'sire');

insert into lname_suffix values (50,'zindy');

commit;

create table street (street_id number, street_name varchar2(100));

insert into street values (1,'Pasadena Way');

insert into street values (2,'Orange Grove Ave.');

insert into street values (3,'Colorado Blvd.');

insert into street values (4,'Windward Way');

insert into street values (5,'La Presta Ct.');

insert into street values (6,'Strasson Lane');

insert into street values (7,'Chandler Blvd.');

insert into street values (8,'Mesa Court Drive');

insert into street values (9,'Standard Way');

insert into street values (10,'First Street');

insert into street values (11,'Noodle Drive');

insert into street values (12,'Stackton Blvd.');

insert into street values (13,'Martin Luther King Drive');

insert into street values (14,'Washington Ave.');

insert into street values (15,'Strasson Parkway');

insert into street values (16,'Highway 57');

insert into street values (17,'Longpark Drive');

insert into street values (18,'Bethington Lane');

insert into street values (19,'Charles McStreson Drive');

insert into street values (20,'Blackmore Lane');

insert into street values (21,'Chunko Way');

insert into street values (22,'Flappington Stadium Drive');

insert into street values (23,'Steriods Blvd.');

insert into street values (24,'Plathmore Lane');

insert into street values (25,'Sitting Bull Way');

insert into street values (26,'Miller Lane');

insert into street values (27,'Santa Clara Blvd.');

insert into street values (28,'Verdugo Drive');

insert into street values (29,'Pilliam Court Drive');

insert into street values (30,'Pracklone Street');

insert into street values (31,'Childer Drive');

insert into street values (32,'Mason Blvd.');

insert into street values (33,'Little Nook Drive');

insert into street values (34,'Stroop Ave.');

insert into street values (35,'Miller Highway');

insert into street values (36,'Yimook Drive');

insert into street values (37,'Ullman Way');

insert into street values (38,'Plackoop Street');

insert into street values (39,'Flapper Drive');

insert into street values (40,'Chilcot Lane');

insert into street values (41,'Santo Ricardo Drive');

insert into street values (42,'O''Rourk Lane');

insert into street values (43,'Jorge Mire Drive');

insert into street values (44,'Zander Way');

insert into street values (45,'Meander Drive');

insert into street values (46,'Urundo Place');

insert into street values (47,'Warzoond Lane');

insert into street values (48,'Doodlemeister Blvd.');

insert into street values (49,'Borondo Place');

insert into street values (50,'Crabtree Way');

commit;

create table zip (zip_id number, zip_code varchar2(9));

insert into zip values (1,'91001');

insert into zip values (2,'91002');

insert into zip values (3,'91003');

insert into zip values (4,'91008');

insert into zip values (5,'91100');

insert into zip values (6,'91101');

insert into zip values (7,'91105');

insert into zip values (8,'91201');

insert into zip values (9,'91206');

insert into zip values (10,'91208');

insert into zip values (11,'91209');

insert into zip values (12,'91211');

insert into zip values (13,'91215');

insert into zip values (14,'91301');

insert into zip values (15,'91311');

insert into zip values (16,'91313');

insert into zip values (17,'91318');

insert into zip values (18,'91400');

insert into zip values (19,'91401');

insert into zip values (20,'92000');

insert into zip values (21,'92001');

insert into zip values (22,'92101');

insert into zip values (23,'92104');

insert into zip values (24,'92111');

insert into zip values (25,'92119');

insert into zip values (26,'93001');

insert into zip values (27,'93002');

commit;

create table cxclie (cxclie_id number,

cxclie_first_name varchar2(40),

cxclie_last_name varchar2(60),

cxclie_gender varchar2(1))

tablespace users;

alter table cxclie add constraint pk_cxclie primary key (cxclie_id);

create unique index cxclie_unique on cxclie( cxclie_first_name,

cxclie_last_name);

create sequence cxclie_pop_number start with 10000;

create table cxaddr (cxaddr_cxclie_id number,

cxaddr_address varchar2(100),

cxaddr_zip_code varchar2(9),

cxaddr_priority number

) tablespace users;

create sequence cxaddr_pop_priority start with 100000;

alter table cxaddr add constraint fk_cxaddr_cxclie_id foreign key

(cxaddr_cxclie_id) references cxclie(cxclie_id);

-- set tables nologging

ALTER TABLE CXCLIE NOLOGGING;

ALTER TABLE CXADDR NOLOGGING;

prompt Populating client and address tables.

prompt Allow several minutes to complete.

------------------- anonymous block begin -----------------------

declare

-- variables used to populate cxclie

rand_fname number;

rand_lname number;

rand_suffix number;

this_fname fname%rowtype;

this_lname lname.lname_name%type;

this_suffix lname_suffix.lname_suffix_suffix%type;

sq number;

this_zip_code zip.zip_code%type;

-- variables used to populate cxaddr

addr_number number;

cxaddr_fk number;

rand_addr number;

rand_street street.street_name%type;

this_addr_priority number;

-- min/max cxclie_id values

min_cxclie_id cxclie.cxclie_id%type;

max_cxclie_id cxclie.cxclie_id%type;

-- min/max zip_id values

min_zip_id zip.zip_id%type;

max_zip_id zip.zip_id%type;

-- sub fuction:

FUNCTION rand_zip_code (min_id NUMBER, max_id NUMBER)

RETURN zip.zip_code%TYPE

IS

rand_zip_code zip.zip_code%TYPE := NULL;

CURSOR my_c

IS

SELECT zip_code

FROM zip

WHERE zip_id = TRUNC (DBMS_RANDOM.VALUE (min_id, max_id));

BEGIN

for x in 1..100 LOOP -- try 100 times max!

OPEN my_c;

FETCH my_c INTO rand_zip_code;

-- DBMS_OUTPUT.put_line (x);

EXIT WHEN my_c%FOUND;

CLOSE my_c;

END LOOP;

RETURN rand_zip_code;

END;

-- sub fuction:

FUNCTION rand_cxclie_id (min_id NUMBER, max_id NUMBER)

RETURN cxclie.cxclie_id%TYPE

IS

rand_id cxclie.cxclie_id%TYPE := NULL;

CURSOR my_c

IS

SELECT cxclie_id

FROM cxclie

WHERE cxclie_id = TRUNC (DBMS_RANDOM.VALUE (min_id, max_id));

BEGIN

for x in 1..100 LOOP -- try 100 times max!

OPEN my_c;

FETCH my_c INTO rand_id;

-- DBMS_OUTPUT.put_line (x);

EXIT WHEN my_c%FOUND;

CLOSE my_c;

END LOOP;

RETURN rand_id;

END;

-- sub procedure:

procedure insert_cxclie ( val1 cxclie.cxclie_id%type,

val2 cxclie.cxclie_first_name%type,

val3 cxclie.cxclie_last_name%type,

val4 cxclie.cxclie_gender%type

)

is

begin

insert into cxclie(cxclie_id,

cxclie_first_name,

cxclie_last_name,

cxclie_gender)

values

(val1,

val2,

val3,

val4);

exception

when others then

null;

--dbms_output.put_line('I got the error!');

end;

begin

disable_foreign_keys('CXADDR');

/****************************************************/

/****************************************************/

/* EDIT NEXT LINE TO DETERMINE HIGH END OF CLIENTS! */

for x in 1..250000 loop

rand_fname := trunc(dbms_random.value(1, 90.999)); -- number of first_names

rand_lname := trunc(dbms_random.value(1, 80.999)); -- number of last_names

rand_suffix := trunc(dbms_random.value(1, 50.999)); -- number of suffixes

select * into this_fname

from fname

where fname_id = rand_fname;

select lname_name into this_lname

from lname

where lname_id = rand_lname;

select lname_suffix_suffix into this_suffix

from lname_suffix

where lname_suffix_id = rand_suffix;

select cxclie_pop_number.NEXTVAL into sq from dual;

insert_cxclie (sq,

this_fname.fname_name,

this_lname || this_suffix,

this_fname.fname_gender);

end loop;

commit;

-- populate min and max values:

select min(cxclie_id) into min_cxclie_id from cxclie;

select max(cxclie_id) into max_cxclie_id from cxclie;

select min(zip_id)into min_zip_id from zip;

select max(zip_id)into max_zip_id from zip;

/******************************************************/

/******************************************************/

/* EDIT NEXT LINE TO DETERMINE HIGH END OF ADDRESSES! */

for x in 1..220000 loop

rand_addr := trunc(dbms_random.value(1, 50.999)); -- number of streets

addr_number:= trunc(dbms_random.value(1000, 3999));

cxaddr_fk := rand_cxclie_id(min_cxclie_id, max_cxclie_id);

this_zip_code := rand_zip_code(min_zip_id,max_zip_id);

select cxaddr_pop_priority.nextval into this_addr_priority from dual;

select street_name into rand_street from street where street_id=rand_addr;

insert into cxaddr (cxaddr_cxclie_id, cxaddr_address,

cxaddr_zip_code, cxaddr_priority)

VALUES

(cxaddr_fk, addr_number||' '||rand_street,

this_zip_code, this_addr_priority);

end loop;

commit;

enable_foreign_keys;

end;

/

-- anonymous block end

-- enable foreign keys

alter table cxaddr enable constraint fk_cxaddr_cxclie_id;

----- Create View Example:

create or replace view cvclie

(CVCLIE_ID,

CVCLIE_FIRST_NAME,

CVCLIE_LAST_NAME,

CVCLIE_GENDER,

CVCLIE_ADDRESS,

CVLIE_ZIP_CODE)

as

select

CXCLIE_ID,

CXCLIE_FIRST_NAME,

CXCLIE_LAST_NAME,

CXCLIE_GENDER,

CXADDR_ADDRESS,

CXADDR_ZIP_CODE

from cxclie, cxaddr

where cxclie_id=cxaddr_cxclie_id (+);

-- INSTEAD OF TRIGGER EXAMPLE:

create or replace trigger IUD_CVCLIE

INSTEAD OF insert or update or delete on cvclie

for each row

declare

begin

if inserting then

insert into cxclie (CXCLIE_ID,

CXCLIE_FIRST_NAME,

CXCLIE_LAST_NAME,

CXCLIE_GENDER)

VALUES

(:NEW.CVCLIE_ID,

:NEW.CVCLIE_FIRST_NAME,

:NEW.CVCLIE_LAST_NAME,

:NEW.CVCLIE_GENDER);

insert into cxaddr (CXADDR_CXCLIE_ID,

CXADDR_ADDRESS)

values

(:NEW.CVCLIE_ID,

:NEW.CVCLIE_ADDRESS);

elsif updating ('CVCLIE_FIRST_NAME') then

update cxclie set cxclie_first_name = :NEW.cvclie_first_name

where cxclie_id = :NEW.cvclie_id;

elsif updating ('CVCLIE_LAST_NAME') then

update cxclie set cxclie_last_name = :NEW.cvclie_last_name

where cxclie_id = :NEW.cvclie_id;

elsif updating ('CVCLIE_GENDER') then

update cxclie set cxclie_gender = :NEW.cvclie_gender

where cxclie_id = :NEW.cvclie_id;

elsif updating ('CVCLIE_ADDRESS') then

RAISE_APPLICATION_ERROR(-20009,'You cannot update an address through CVCLIE.'

||chr(10)

|| 'TRY UPDATING CXADDR using '

|| :NEW.cvclie_id

|| ' for the CXADDR_CXCLIE_ID');

null;

elsif deleting then

delete from cxaddr where cxaddr_cxclie_id = :OLD.cvclie_id;

delete from cxclie where cxclie_id = :OLD.cvclie_id;

end if;

end;

/

CREATE SEQUENCE cxprod_pop_product_id START WITH 1000;

-- cxprod PRODUCTS THE COMPANY SELLS

CREATE TABLE cxprod (cxprod_product_id NUMBER,

cxprod_prod_desc VARCHAR2(100),

cxprod_base_price NUMBER(10,2));

ALTER TABLE cxprod

ADD CONSTRAINT pk_cxprod

PRIMARY KEY (cxprod_product_id);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Widget with Red Flares', 1011.50);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Widget Base', 12.10);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Widget Stand', 14.15);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Blank Mondo Block', 8.03);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Electric Meat Processor', 99.80);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Colorless Widget', 199.00);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Mid-range Plank', 77.65);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Black and Red Wizdo', 2001.00);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Silver Shoehorn', 70.15);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'White Blackmoore', 100.10);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Sirten Birten', 521.50);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Wide-range Plank', 606.65);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'German-Indian Carp', 200.10);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Torpintune', 52.21);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'Blue Canary Pild', 110.65);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'JipJapJop Rope Widget', 871.99);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'HamHom Windget', 10.55);

INSERT INTO cxprod

VALUES (cxprod_pop_product_id.NEXTVAL, 'GreatWall Val', 5.90);

COMMIT ;

-- cxsales INDIVIDUAL SALES (INVOICES)

CREATE TABLE cxsals (cxsals_cxclie_id NUMBER,

cxsals_invoice_id NUMBER,

cxsals_date DATE);

ALTER TABLE cxsals

ADD CONSTRAINT pk_cxsals

PRIMARY KEY (cxsals_invoice_id);

ALTER TABLE cxsals

ADD CONSTRAINT fk_cxsals_cxclie_id

FOREIGN KEY (cxsals_cxclie_id)

REFERENCES cxclie (cxclie_id);

CREATE SEQUENCE cxsals_pop_invoice_id START WITH 1000;

-- cxsldt SALES DETAILS

CREATE TABLE cxsldt (cxsldt_cxsals_invoice_id NUMBER,

cxsldt_units NUMBER,

cxsldt_cxprod_product_id NUMBER,

cxsldt_amount_per_unit NUMBER(16,2));

ALTER TABLE cxsldt

ADD CONSTRAINT fk_cxsldt_cxsals_invoice_id

FOREIGN KEY (cxsldt_cxsals_invoice_id)

REFERENCES cxsals (cxsals_invoice_id);

ALTER TABLE CXSLDT

ADD CONSTRAINT FK_CXSLDT_CXPROD_PRODUCT_ID

FOREIGN KEY (CXSLDT_CXPROD_PRODUCT_ID)

REFERENCES CXPROD (CXPROD_PRODUCT_ID);

CREATE INDEX IND_CXSLDT_CXSALS_INVOICE_ID ON CXSLDT

(CXSLDT_CXSALS_INVOICE_ID);

-- alter tables for nologging

ALTER TABLE CXSALS NOLOGGING;

ALTER TABLE CXSLDT NOLOGGING;

begin session_vars.session_part1_end_time := sysdate; end;

/

set serveroutput on

declare

elapsed_time INTERVAL DAY TO SECOND(9);

begin

elapsed_time :=

session_vars.session_part1_end_time - session_vars.session_start_time;

dbms_output.put('Elapsed time from beginning to end of step 1: ');

dbms_output.put(substr(to_char(elapsed_time), 5,8));

dbms_output.new_line;

end;

/

prompt Populating sales and details tables.

prompt Allow several minutes to complete.

-- populate sales tables

------------------ anonymous block begin ---------------------

DECLARE

-- min/max cxclie_id values

min_cxclie_id cxclie.cxclie_id%TYPE;

max_cxclie_id cxclie.cxclie_id%TYPE;

this_cxclie_id cxclie.cxclie_id%TYPE;

this_cxsals_invoice_id cxsals.cxsals_invoice_id%TYPE;

this_start_date DATE := SYSDATE - 6000;

this_items_on_invoice NUMBER;

this_units NUMBER;

this_base_year NUMBER

:= TO_NUMBER (TO_CHAR (this_start_date, 'YYYY'));

TYPE sales_detail_type IS RECORD (

product_id cxprod.cxprod_product_id%TYPE,

amount cxprod.cxprod_base_price%TYPE

);

this_sale_detail sales_detail_type;

min_cxprod_product_id cxprod.cxprod_product_id%TYPE;

max_cxprod_product_id cxprod.cxprod_product_id%TYPE;

-- sub function:

FUNCTION rand_cxprod_product (min_id NUMBER, max_id NUMBER)

RETURN sales_detail_type

IS

rand_product sales_detail_type;

CURSOR my_c

IS

SELECT cxprod_product_id, cxprod_base_price

FROM cxprod

WHERE cxprod_product_id =

TRUNC (DBMS_RANDOM.VALUE (min_id, max_id + .999))

AND cxprod_product_id NOT IN (

SELECT cxsldt_cxprod_product_id

FROM cxsldt

WHERE cxsldt_cxsals_invoice_id =

this_cxsals_invoice_id);

BEGIN

FOR x IN 1 .. 100

LOOP

OPEN my_c;

FETCH my_c INTO rand_product;

EXIT WHEN my_c%FOUND;

CLOSE my_c;

END LOOP;

RETURN rand_product;

END;

-- sub fuction:

FUNCTION rand_cxclie_id (min_id NUMBER, max_id NUMBER)

RETURN cxclie.cxclie_id%TYPE

IS

rand_id cxclie.cxclie_id%TYPE := NULL;

CURSOR my_c

IS

SELECT cxclie_id

FROM cxclie

WHERE cxclie_id = TRUNC (DBMS_RANDOM.VALUE (min_id, max_id));

BEGIN

FOR x IN 1 .. 100

LOOP -- try 100 times max!

OPEN my_c;

FETCH my_c INTO rand_id;

-- DBMS_OUTPUT.put_line (x);

EXIT WHEN my_c%FOUND;

CLOSE my_c;

END LOOP;

RETURN rand_id;

END;

BEGIN

disable_foreign_keys('CXSALS');

/* DO NOT DISABLE THE CONSTRAINTS ON CXSLDT. DOING SO

SLOWS THE PROCESS DOWN AS THE CXSALS TABLE GROWS */

-- disable_foreign_keys('CXSLDT');

-- populate min and max values:

SELECT MIN (cxclie_id)

INTO min_cxclie_id

FROM cxclie;

SELECT MAX (cxclie_id)

INTO max_cxclie_id

FROM cxclie;

SELECT MIN (cxprod_product_id)

INTO min_cxprod_product_id

FROM cxprod;

SELECT MAX (cxprod_product_id)

INTO max_cxprod_product_id

FROM cxprod;

/*****************************************************/

/*****************************************************/

/* EDIT NEXT LINE TO DETERMINE HIGH END OF INVOICES! */

FOR x IN 1 .. 500000

LOOP

-- create a sale (invoice)

this_cxclie_id := rand_cxclie_id (min_cxclie_id, max_cxclie_id);

SELECT cxsals_pop_invoice_id.NEXTVAL

INTO this_cxsals_invoice_id

FROM DUAL;

this_start_date := this_start_date + DBMS_RANDOM.VALUE (0, .01);

if this_start_date > sysdate then exit; end if;

INSERT INTO cxsals

(cxsals_cxclie_id, cxsals_invoice_id, cxsals_date

)

VALUES (this_cxclie_id, this_cxsals_invoice_id, this_start_date

);

FOR x IN 1 .. TRUNC (DBMS_RANDOM.VALUE (1, 7.999))

LOOP

-- create the sale details:

this_units := TRUNC (DBMS_RANDOM.VALUE (1, 25));

-- weight it for more natural looking invoices:

IF this_units > 15

THEN

this_units := TRUNC (DBMS_RANDOM.VALUE (1, 3));

ELSIF this_units BETWEEN 13 AND 15

THEN

this_units := 1;

END IF;

this_sale_detail :=

rand_cxprod_product (min_cxprod_product_id, max_cxprod_product_id);

--sales price is adjusted upward each year

INSERT INTO cxsldt

(cxsldt_cxsals_invoice_id, cxsldt_units,

cxsldt_cxprod_product_id,

cxsldt_amount_per_unit

)

VALUES (this_cxsals_invoice_id, this_units,

this_sale_detail.product_id,

TRUNC ( this_sale_detail.amount

* ( 1

+ ( .1

* TO_NUMBER (TO_CHAR (this_start_date,

'YYYY'

)-this_base_year

)

)

),

2

)

);

END LOOP;

COMMIT;

END LOOP;

enable_foreign_keys; -- enable foreign keys!

END;

/

CREATE TABLE cxpays (cxpays_cxclie_id NUMBER,

cxpays_date DATE,

cxpays_amount NUMBER(20,2),

cxpays_pay_method VARCHAR2(10),

cxpays_cxsals_invoice_id NUMBER

);

ALTER TABLE cxpays

ADD CONSTRAINT fk_cxpays_cxlie_id

FOREIGN KEY (cxpays_cxclie_id)

REFERENCES cxclie (cxclie_id);

ALTER TABLE cxpays

ADD CONSTRAINT fk_cxpays_cxsals_invoice_id

FOREIGN KEY (cxpays_cxsals_invoice_id)

REFERENCES cxsals (cxsals_invoice_id);

begin session_vars.session_part2_end_time := sysdate; end;

/

set serveroutput on

declare

elapsed_time INTERVAL DAY TO SECOND(9);

begin

elapsed_time :=

session_vars.session_part2_end_time - session_vars.session_part1_end_time;

dbms_output.put('Elapsed time from beginning of step 2 to end of step 2: ');

dbms_output.put(substr(to_char(elapsed_time), 5,8));

dbms_output.new_line;

end;

/

prompt Populating payment record table.

prompt Allow several minutes to complete.

----------------- anonymous block begin -----------------------

DECLARE

this_result VARCHAR2 (12);

CURSOR this_c

IS

SELECT cxsals_cxclie_id, cxsals_invoice_id, cxsals_date,

SUM (cxsldt_amount_per_unit * cxsldt_units

) AS cxsals_invoice_total

FROM cxsals, cxsldt

WHERE cxsals_invoice_id = cxsldt_cxsals_invoice_id

-- include next line for testing

-- AND cxsals_invoice_id BETWEEN (500500) AND (500999)

GROUP BY cxsals_cxclie_id, cxsals_invoice_id, cxsals_date;

this_var this_c%ROWTYPE;

this_pay_method cxpays.cxpays_pay_method%TYPE;

this_high_val NUMBER;

this_partial_payment NUMBER;

this_total_paid NUMBER;

BEGIN

disable_foreign_keys('CXSLDT');

OPEN this_c;

LOOP

FETCH this_c INTO this_var;

EXIT WHEN this_c%NOTFOUND;

-- assign a result to determine if the client paid all

-- 80% pay all, 19% pay part, 1% problem

SELECT DECODE (SIGN (TRUNC (DBMS_RANDOM.VALUE (1, 100.99)) - 80),

1, 'pay_part',

0, 'problem',

'pay_all'

)

INTO this_result

FROM DUAL;

-- assing a method of payment

SELECT DECODE (TRUNC (DBMS_RANDOM.VALUE (1, 7.99)),

1, 'CHECK',

2, 'CHECK',

3, 'CHECK',

4, 'CHECK',

5, 'CC',

6, 'CC',

'CASH'

)

INTO this_pay_method

FROM DUAL;

this_total_paid := 0; -- reset the value

IF this_result = 'pay_all'

THEN

NULL;

INSERT INTO cxpays

(cxpays_cxclie_id,

cxpays_date,

cxpays_amount, cxpays_pay_method,

cxpays_cxsals_invoice_id

)

VALUES (this_var.cxsals_cxclie_id,

this_var.cxsals_date + DBMS_RANDOM.VALUE (0, 45),

this_var.cxsals_invoice_total, this_pay_method,

this_var.cxsals_invoice_id

);

ELSIF this_result = 'pay_part'

THEN

IF TRUNC (DBMS_RANDOM.VALUE (1, 3.999)) = 1

THEN

-- break invoice into equal multiple payments

this_high_val := TRUNC (DBMS_RANDOM.VALUE (2, 4));

this_partial_payment :=

ROUND (this_var.cxsals_invoice_total / this_high_val, 2);

FOR x IN 1 .. this_high_val

LOOP

IF x < this_high_val

THEN -- pay one-third, one-quarter, etc.

INSERT INTO cxpays

(cxpays_cxclie_id,

cxpays_date,

cxpays_amount, cxpays_pay_method,

cxpays_cxsals_invoice_id

)

VALUES (this_var.cxsals_cxclie_id,

this_var.cxsals_date

+ DBMS_RANDOM.VALUE (0, 90),

this_partial_payment, this_pay_method,

this_var.cxsals_invoice_id

);

ELSE -- pay remainder

INSERT INTO cxpays

(cxpays_cxclie_id,

cxpays_date,

cxpays_amount,

cxpays_pay_method, cxpays_cxsals_invoice_id

)

VALUES (this_var.cxsals_cxclie_id,

this_var.cxsals_date

+ DBMS_RANDOM.VALUE (0, 90),

this_var.cxsals_invoice_total

- this_total_paid,

this_pay_method, this_var.cxsals_invoice_id

);

END IF;

this_total_paid := this_total_paid + this_partial_payment;

END LOOP;

ELSE

-- break payment up into three unequal amounts

FOR x IN 1 .. 3

LOOP

IF x = 1

THEN

this_partial_payment :=

TRUNC (DBMS_RANDOM.VALUE (1,

( this_var.cxsals_invoice_total

* .6

)

),

2

);

ELSIF x = 2

THEN

this_partial_payment :=

TRUNC (DBMS_RANDOM.VALUE (1,

( this_var.cxsals_invoice_total

- this_total_paid

)

* .8

),

2

);

ELSIF x = 3

THEN

this_partial_payment :=

(this_var.cxsals_invoice_total - this_total_paid

);

END IF;

this_total_paid := this_total_paid + this_partial_payment;

INSERT INTO cxpays

(cxpays_cxclie_id,

cxpays_date,

cxpays_amount, cxpays_pay_method,

cxpays_cxsals_invoice_id

)

VALUES (this_var.cxsals_cxclie_id,

this_var.cxsals_date + DBMS_RANDOM.VALUE (0, 90),

this_partial_payment, this_pay_method,

this_var.cxsals_invoice_id

);

END LOOP;

END IF;

ELSIF this_result = 'problem'

THEN

IF TRUNC (DBMS_RANDOM.VALUE (1, 3.999)) = 1

THEN

NULL; -- do nothing, this invoice gets no payment, customer is a deadbeat!

ELSE

-- make fixed $100 payments up to last payment

LOOP

IF this_var.cxsals_invoice_total - this_total_paid > 99.99

THEN

this_partial_payment := 100;

ELSE

this_partial_payment :=

this_var.cxsals_invoice_total

- this_total_paid;

END IF;

this_total_paid := this_total_paid + this_partial_payment;

INSERT INTO cxpays

(cxpays_cxclie_id,

cxpays_date,

cxpays_amount, cxpays_pay_method,

cxpays_cxsals_invoice_id

)

VALUES (this_var.cxsals_cxclie_id,

this_var.cxsals_date + DBMS_RANDOM.VALUE (0, 750),

this_partial_payment, this_pay_method,

this_var.cxsals_invoice_id

);

IF this_total_paid >= this_var.cxsals_invoice_total

THEN

EXIT;

END IF;

-- deadbeat factor.... once in a while, exit so

-- that the whole amount is not paid!

IF TRUNC (DBMS_RANDOM.VALUE (1, 25.999)) = 7

THEN

EXIT;

END IF;

END LOOP;

END IF;

END IF;

END LOOP;

CLOSE this_c;

COMMIT;

enable_foreign_keys;

END;

/

create index IND_CXPAYS_CXSALS_INVOICE_ID on CXPAYS(CXPAYS_CXSALS_INVOICE_ID);

create index IND_CXPAYS_CXCLIE_ID on CXPAYS(CXPAYS_CXCLIE_ID);

-- alter tables for logging

ALTER TABLE CXCLIE LOGGING;

ALTER TABLE CXADDR LOGGING;

ALTER TABLE CXSALS LOGGING;

ALTER TABLE CXSLDT LOGGING;

ALTER TABLE CXPAYS LOGGING;

-- drop tables used to create the schema

drop table FNAME cascade constraints;

drop table LNAME cascade constraints;

drop table LNAME_SUFFIX cascade constraints;

drop table STREET cascade constraints;

drop table ZIP cascade constraints;

begin session_vars.session_end_time := sysdate; end;

/

set serveroutput on

declare

elapsed_time INTERVAL DAY TO