/*
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 SECOND(9);
begin
elapsed_time :=
session_vars.session_end_time - session_vars.session_part2_end_time;
dbms_output.put('Elapsed time from beginning of step 3 to end of step 3: ');
dbms_output.put(substr(to_char(elapsed_time), 5,8));
dbms_output.new_line;
elapsed_time :=
session_vars.session_end_time - session_vars.session_start_time;
dbms_output.put('Total Elapsed time: ');
dbms_output.put(substr(to_char(elapsed_time), 5,8));
dbms_output.new_line;
end;
/
drop package session_vars;
exit