SQL creation script

To copy the SQL below, click anywhere in the box, then Ctrl+A followed by Ctrl+C.

  1. ---- WIDGET DEV ----
  2. DROP USER WidgetDev CASCADE;
  3. CREATE USER WidgetDev IDENTIFIED BY password;
  4. GRANT UNLIMITED TABLESPACE TO WidgetDev;
  5. GRANT CONNECT TO WidgetDev;
  6. GRANT CREATE SESSION TO WidgetDev;
  7. GRANT ALTER ANY TABLE TO WidgetDev;
  8. GRANT CREATE ANY INDEX TO WidgetDev;
  9. GRANT CREATE ANY PROCEDURE TO WidgetDev;
  10. GRANT CREATE ANY VIEW TO WidgetDev;
  11. /
  12. CREATE TABLE WidgetDev.WidgetPrices (
  13. RecordID INTEGER NOT NULL ,
  14. WidgetID INTEGER NULL ,
  15. Price INTEGER NULL ,
  16. DateValidFrom DATE NULL ,
  17. DateValidTo DATE NULL ,
  18. Active CHAR(1) NULL
  19. );
  20. ALTER TABLE WidgetDev.WidgetPrices MODIFY DateValidFrom DATE DEFAULT sysdate ;
  21. ALTER TABLE WidgetDev.WidgetPrices MODIFY Active CHAR DEFAULT ('N');
  22. ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT PK_WidgetPrices PRIMARY KEY (RecordID);
  23. CREATE INDEX WidgetDev.IX_WidgetPrices_1 ON WidgetDev.WidgetPrices(WidgetID);
  24. CREATE INDEX WidgetDev.IX_WidgetPrices_2 ON WidgetDev.WidgetPrices(DateValidFrom);
  25. CREATE INDEX WidgetDev.IX_WidgetPrices_3 ON WidgetDev.WidgetPrices(DateValidTo);
  26. GRANT SELECT ON WidgetDev.WidgetPrices TO public;
  27. CREATE TABLE WidgetDev.Widgets (
  28. RecordID INTEGER NOT NULL ,
  29. Description VARCHAR2 (50) NULL ,
  30. SKU VARCHAR2 (20) NULL
  31. );
  32. CREATE TABLE WidgetDev.WidgetReferences (
  33. WidgetID INTEGER NOT NULL,
  34. Reference VARCHAR2(25) NULL
  35. );
  36. ALTER TABLE WidgetDev.WidgetReferences ADD CONSTRAINT PK_WidgetReferences PRIMARY KEY (WidgetID);
  37. ALTER TABLE WidgetDev.Widgets ADD CONSTRAINT PK_Widgets PRIMARY KEY (RecordID);
  38. GRANT SELECT ON WidgetDev.Widgets TO PUBLIC;
  39. ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT FK_WidgetPrices_Widgets FOREIGN KEY (WidgetID) REFERENCES WidgetDev.Widgets(RecordID);
  40. CREATE OR REPLACE VIEW WidgetDev.CurrentPrices AS
  41. SELECT
  42. WidgetID,
  43. Price,
  44. Description
  45. FROM WidgetDev.Widgets INNER JOIN
  46. WidgetDev.WidgetPrices ON Widgets.RecordID = WidgetPrices.WidgetID
  47. WHERE WidgetPrices.Active = 'Y';
  48. GRANT SELECT ON WidgetDev.CurrentPrices TO PUBLIC;
  49. CREATE OR REPLACE PROCEDURE WidgetDev.prcActivatePrices
  50. IS
  51. BEGIN
  52. UPDATE WidgetDev.WidgetPrices SET Active='N' WHERE sysdate<DateValidTo OR sysdate>DateValidFrom;
  53. UPDATE WidgetDev.WidgetPrices SET Active='Y' WHERE sysdate>=DateValidFrom OR sysdate<=DateValidFrom;
  54. END;
  55. /
  56. CREATE OR REPLACE PACKAGE WidgetDev.WidgetPackage AS
  57. PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2);
  58. PROCEDURE remWidget(widgetID INTEGER);
  59. END WidgetPackage;
  60. /
  61. CREATE OR REPLACE PACKAGE BODY WidgetDev.WidgetPackage AS
  62. PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2) IS
  63. BEGIN
  64. INSERT INTO WidgetDev.Widgets(RecordID, Description, SKU) VALUES(widgetID, widgetDesc, widgetSKU);
  65. END addWidget;
  66. PROCEDURE remWidget(widgetID INTEGER) IS
  67. BEGIN
  68. DELETE FROM WidgetDev.Widgets
  69. WHERE WidgetDev.Widgets.RECORDID = remWidget.widgetID;
  70. END remWidget;
  71. END WidgetPackage;
  72. /
  73. ---- WIDGET TEST ----
  74. DROP USER WidgetTest CASCADE;
  75. CREATE USER WidgetTest IDENTIFIED BY password;
  76. GRANT UNLIMITED TABLESPACE TO WidgetTest;
  77. GRANT CONNECT TO WidgetTest;
  78. GRANT CREATE SESSION TO WidgetTest;
  79. GRANT ALTER ANY TABLE TO WidgetTest;
  80. GRANT CREATE ANY INDEX TO WidgetTest;
  81. GRANT CREATE ANY PROCEDURE TO WidgetTest;
  82. GRANT CREATE ANY VIEW TO WidgetTest;
  83. CREATE TABLE WidgetTest.WidgetPrices (
  84. RecordID INTEGER NOT NULL ,
  85. WidgetID INTEGER NULL ,
  86. Price INTEGER NULL
  87. );
  88. ALTER TABLE WidgetTest.WidgetPrices ADD CONSTRAINT PK_WidgetPrices PRIMARY KEY (RecordID);
  89. CREATE TABLE WidgetTest.Widgets (
  90. RecordID INTEGER NOT NULL ,
  91. Description VARCHAR2(50) NULL
  92. );
  93. CREATE TABLE WidgetTest.WidgetReferences (
  94. WidgetID int NOT NULL ,
  95. Reference VARCHAR2(50) NULL
  96. );
  97. ALTER TABLE WidgetTest.WidgetReferences ADD CONSTRAINT PK_WidgetReferences PRIMARY KEY (WidgetID);
  98. ALTER TABLE WidgetTest.Widgets ADD CONSTRAINT PK_Widgets PRIMARY KEY (RecordID);
  99. CREATE OR REPLACE VIEW WidgetTest.CurrentPrices AS
  100. SELECT
  101. WidgetID,
  102. Price,
  103. Description
  104. FROM WidgetTest.Widgets INNER JOIN
  105. WidgetTest.WidgetPrices ON WidgetTest.Widgets.RecordID = WidgetTest.WidgetPrices.WidgetID
  106. ;
  107. CREATE OR REPLACE PACKAGE WidgetTest.WidgetPackage AS
  108. PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2);
  109. PROCEDURE remWidget(widgetID INTEGER);
  110. END WidgetPackage;
  111. /
  112. CREATE OR REPLACE PACKAGE BODY WidgetTest.WidgetPackage AS
  113. PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2) IS
  114. BEGIN
  115. INSERT INTO WidgetTest.Widgets(RecordID, Description) VALUES(widgetID, widgetDesc);
  116. END addWidget;
  117. PROCEDURE remWidget(widgetID INTEGER) IS
  118. BEGIN
  119. DELETE FROM WidgetTest.Widgets
  120. WHERE WidgetTest.Widgets.RECORDID = remWidget.widgetID;
  121. END remWidget;
  122. END WidgetPackage;
  123. / 

Didn't find what you were looking for?