SQL creation script
Published 27 November 2013
To copy the SQL below, click anywhere in the box, then Ctrl+A followed by Ctrl+C.
---- WIDGET DEV ---- DROP USER WidgetDev CASCADE; CREATE USER WidgetDev IDENTIFIED BY password; GRANT UNLIMITED TABLESPACE TO WidgetDev; GRANT CONNECT TO WidgetDev; GRANT CREATE SESSION TO WidgetDev; GRANT ALTER ANY TABLE TO WidgetDev; GRANT CREATE ANY INDEX TO WidgetDev; GRANT CREATE ANY PROCEDURE TO WidgetDev; GRANT CREATE ANY VIEW TO WidgetDev; / CREATE TABLE WidgetDev.WidgetPrices ( RecordID INTEGER NOT NULL , WidgetID INTEGER NULL , Price INTEGER NULL , DateValidFrom DATE NULL , DateValidTo DATE NULL , Active CHAR(1) NULL ); ALTER TABLE WidgetDev.WidgetPrices MODIFY DateValidFrom DATE DEFAULT sysdate ; ALTER TABLE WidgetDev.WidgetPrices MODIFY Active CHAR DEFAULT ('N'); ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT PK_WidgetPrices PRIMARY KEY (RecordID); CREATE INDEX WidgetDev.IX_WidgetPrices_1 ON WidgetDev.WidgetPrices(WidgetID); CREATE INDEX WidgetDev.IX_WidgetPrices_2 ON WidgetDev.WidgetPrices(DateValidFrom); CREATE INDEX WidgetDev.IX_WidgetPrices_3 ON WidgetDev.WidgetPrices(DateValidTo); GRANT SELECT ON WidgetDev.WidgetPrices TO public; CREATE TABLE WidgetDev.Widgets ( RecordID INTEGER NOT NULL , Description VARCHAR2 (50) NULL , SKU VARCHAR2 (20) NULL ); CREATE TABLE WidgetDev.WidgetReferences ( WidgetID INTEGER NOT NULL, Reference VARCHAR2(25) NULL ); ALTER TABLE WidgetDev.WidgetReferences ADD CONSTRAINT PK_WidgetReferences PRIMARY KEY (WidgetID); ALTER TABLE WidgetDev.Widgets ADD CONSTRAINT PK_Widgets PRIMARY KEY (RecordID); GRANT SELECT ON WidgetDev.Widgets TO PUBLIC; ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT FK_WidgetPrices_Widgets FOREIGN KEY (WidgetID) REFERENCES WidgetDev.Widgets(RecordID); CREATE OR REPLACE VIEW WidgetDev.CurrentPrices AS SELECT WidgetID, Price, Description FROM WidgetDev.Widgets INNER JOIN WidgetDev.WidgetPrices ON Widgets.RecordID = WidgetPrices.WidgetID WHERE WidgetPrices.Active = 'Y'; GRANT SELECT ON WidgetDev.CurrentPrices TO PUBLIC; CREATE OR REPLACE PROCEDURE WidgetDev.prcActivatePrices IS BEGIN UPDATE WidgetDev.WidgetPrices SET Active='N' WHERE sysdate<DateValidTo OR sysdate>DateValidFrom; UPDATE WidgetDev.WidgetPrices SET Active='Y' WHERE sysdate>=DateValidFrom OR sysdate<=DateValidFrom; END; / CREATE OR REPLACE PACKAGE WidgetDev.WidgetPackage AS PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2); PROCEDURE remWidget(widgetID INTEGER); END WidgetPackage; / CREATE OR REPLACE PACKAGE BODY WidgetDev.WidgetPackage AS PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2) IS BEGIN INSERT INTO WidgetDev.Widgets(RecordID, Description, SKU) VALUES(widgetID, widgetDesc, widgetSKU); END addWidget; PROCEDURE remWidget(widgetID INTEGER) IS BEGIN DELETE FROM WidgetDev.Widgets WHERE WidgetDev.Widgets.RECORDID = remWidget.widgetID; END remWidget; END WidgetPackage; / ---- WIDGET TEST ---- DROP USER WidgetTest CASCADE; CREATE USER WidgetTest IDENTIFIED BY password; GRANT UNLIMITED TABLESPACE TO WidgetTest; GRANT CONNECT TO WidgetTest; GRANT CREATE SESSION TO WidgetTest; GRANT ALTER ANY TABLE TO WidgetTest; GRANT CREATE ANY INDEX TO WidgetTest; GRANT CREATE ANY PROCEDURE TO WidgetTest; GRANT CREATE ANY VIEW TO WidgetTest; CREATE TABLE WidgetTest.WidgetPrices ( RecordID INTEGER NOT NULL , WidgetID INTEGER NULL , Price INTEGER NULL ); ALTER TABLE WidgetTest.WidgetPrices ADD CONSTRAINT PK_WidgetPrices PRIMARY KEY (RecordID); CREATE TABLE WidgetTest.Widgets ( RecordID INTEGER NOT NULL , Description VARCHAR2(50) NULL ); CREATE TABLE WidgetTest.WidgetReferences ( WidgetID int NOT NULL , Reference VARCHAR2(50) NULL ); ALTER TABLE WidgetTest.WidgetReferences ADD CONSTRAINT PK_WidgetReferences PRIMARY KEY (WidgetID); ALTER TABLE WidgetTest.Widgets ADD CONSTRAINT PK_Widgets PRIMARY KEY (RecordID); CREATE OR REPLACE VIEW WidgetTest.CurrentPrices AS SELECT WidgetID, Price, Description FROM WidgetTest.Widgets INNER JOIN WidgetTest.WidgetPrices ON WidgetTest.Widgets.RecordID = WidgetTest.WidgetPrices.WidgetID ; CREATE OR REPLACE PACKAGE WidgetTest.WidgetPackage AS PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2); PROCEDURE remWidget(widgetID INTEGER); END WidgetPackage; / CREATE OR REPLACE PACKAGE BODY WidgetTest.WidgetPackage AS PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2) IS BEGIN INSERT INTO WidgetTest.Widgets(RecordID, Description) VALUES(widgetID, widgetDesc); END addWidget; PROCEDURE remWidget(widgetID INTEGER) IS BEGIN DELETE FROM WidgetTest.Widgets WHERE WidgetTest.Widgets.RECORDID = remWidget.widgetID; END remWidget; END WidgetPackage; /