SQL creation script
Published 24 February 2022
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;
- /