-- 26. may 2011. I instaled 28. mart 2020.
-- 1. conn hr/hr@ora7
-- 2. start J:\xampp\htdocs\z_wishlist\app\database\oracle\oracle_script.sql
prompt 1. HTMLDB_ PLAN_ TABLE
CREATE TABLE "HTMLDB_PLAN_TABLE" (
"STATEMENT_ID" VARCHAR2(30),
"PLAN_ID" NUMBER,
"TIMESTAMP" DATE,
"REMARKS" VARCHAR2(4000),
"OPERATION" VARCHAR2(30),
"OPTIONS" VARCHAR2(255),
"OBJECT_NODE" VARCHAR2(128),
"OBJECT_OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"OBJECT_ALIAS" VARCHAR2(65),
"OBJECT_INSTANCE" NUMBER(*,0),
"OBJECT_TYPE" VARCHAR2(30),
"OPTIMIZER" VARCHAR2(255),
"SEARCH_COLUMNS" NUMBER,
"ID" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
"DEPTH" NUMBER(*,0),
"POSITION" NUMBER(*,0),
"COST" NUMBER(*,0),
"CARDINALITY" NUMBER(*,0),
"BYTES" NUMBER(*,0),
"OTHER_TAG" VARCHAR2(255),
"PARTITION_START" VARCHAR2(255),
"PARTITION_STOP" VARCHAR2(255),
"PARTITION_ID" NUMBER(*,0),
"OTHER" LONG,
"DISTRIBUTION" VARCHAR2(30),
"CPU_COST" NUMBER(*,0),
"IO_COST" NUMBER(*,0),
"TEMP_SPACE" NUMBER(*,0),
"ACCESS_PREDICATES" VARCHAR2(4000),
"FILTER_PREDICATES" VARCHAR2(4000),
"PROJECTION" VARCHAR2(4000),
"TIME" NUMBER(*,0),
"QBLOCK_NAME" VARCHAR2(30) )
/
prompt 2. W I S H E R S
CREATE TABLE "WISHERS" (
"ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(50) NOT NULL ENABLE,
"PASSWORD" VARCHAR2(50) NOT NULL ENABLE,
CONSTRAINT "WISHERS_PK" PRIMARY KEY ("ID") ENABLE,
UNIQUE ("NAME") ENABLE
)
/
prompt 3. W I S H E S
CREATE TABLE "WISHES" (
"ID" NUMBER NOT NULL ENABLE,
"WISHER_ID" NUMBER NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(255) NOT NULL ENABLE,
"DUE_DATE" TIMESTAMP (6) WITH TIME ZONE,
CONSTRAINT "WISHES_PK" PRIMARY KEY ("ID") ENABLE,
CONSTRAINT "WISHES_FK1" FOREIGN KEY ("WISHER_ID")
REFERENCES "WISHERS" ("ID") ENABLE
)
/
prompt 4.1 F N SET_ DUE_ DATE
CREATE OR REPLACE
FUNCTION "SET_DUE_DATE" (in_date in VARCHAR2) return VARCHAR2 is
begin return TO_TIMESTAMP_TZ(in_date, 'YYYY-MM-DD HH24:MI:SS TZR');
end;
/
prompt 4.2 F N FORMAT_ DUE_ DATE
CREATE OR REPLACE
FUNCTION "FORMAT_DUE_DATE" (in_date in TIMESTAMP) return VARCHAR2 is
begin
return TO_CHAR(IN_DATE, 'YYYY-MM-DD HH24:MI:SS TZR');
end;
/
prompt 5. INDEXes
--CREATE UNIQUE INDEX "WISHES_PK" ON "WISHES" ("ID")
-- ORA-00955: name is already used by an existing object
--CREATE UNIQUE INDEX "WISHERS_PK" ON "WISHERS" ("ID")
-- ORA-00955: name is already used by an existing object
--CREATE UNIQUE INDEX "SYS_C004149" ON "WISHERS" ("NAME")
-- ORA-01408: such column list already indexed
prompt 6. SEQUENCEs
CREATE SEQUENCE "WISHES_ID_SEQ"
MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1
START WITH 30 CACHE 20 NOORDER NOCYCLE
/
CREATE SEQUENCE "WISHERS_ID_SEQ"
MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1
START WITH 23 CACHE 20 NOORDER NOCYCLE
/
prompt 7. TRIGGERs
CREATE OR REPLACE
TRIGGER "WISHES_INSERT" BEFORE insert on "WISHES" for each row
begin
select WISHES_ID_SEQ.NEXTVAL into :NEW.ID from dual;
end;
/
ALTER TRIGGER "WISHES_INSERT" ENABLE
/
CREATE OR REPLACE TRIGGER "WISHERS_INSERT" BEFORE insert on "WISHERS" for each row
begin
select WISHERS_ID_SEQ.NEXTVAL into :NEW.ID from dual;
end;
/
ALTER TRIGGER "WISHERS_INSERT" ENABLE
/
|
Download
