APEX:Project Data示例项目数据库(EBA_PROJECTS)

The following database objects will be installed into the selected schema:
EBA_PROJECT_STATUS (Table)
EBA_PROJECTS (Table)
EBA_PROJECT_MILESTONES (Table)
EBA_PROJECT_TASKS (Table)
EBA_PROJECT_TASK_TODOS (Table)
EBA_PROJECT_TASK_LINKS (Table)
EBA_PROJECT_COMMENTS (Table)
EBA_PROJECTS_V (View)
EBA_PROJECTS_COMPLETED_V (View)
  CREATE TABLE "EBA_MILESTONES" 
   (	"ID" NUMBER, 
	"PROJECT_ID" NUMBER, 
	"NAME" VARCHAR2(250), 
	"DESCRIPTION" VARCHAR2(250), 
	"DUE_DATE" DATE, 
	"IS_COMPLETE_YN" VARCHAR2(10), 
	"CREATED_BY" VARCHAR2(150), 
	"CREATED" DATE, 
	"UPDATED_BY" VARCHAR2(150), 
	"UPDATED" DATE, 
	 PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;
   
  CREATE TABLE "EBA_PROJECTS" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"STATUS_ID" NUMBER, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(4000), 
	"PROJECT_LEAD" VARCHAR2(255), 
	"BUDGET" NUMBER, 
	"COMPLETED_DATE" DATE, 
	"CREATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	"UPDATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "EBA_PROJECTS_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "EBA_PROJECTS_UK" UNIQUE ("NAME")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "EBA_PROJECTS" ADD CONSTRAINT "EBA_PROJECT_STATUS_FK" FOREIGN KEY ("STATUS_ID")
	  REFERENCES "EBA_PROJECT_STATUS" ("ID") ON DELETE SET NULL ENABLE;

  CREATE INDEX "EBA_PROJECTS_STATUS_IDX" ON "EBA_PROJECTS" ("STATUS_ID") 
  ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EBA_PROJECTS_BIU" 
    before insert or update on eba_projects
    for each row
begin
    if inserting then
        :new.created    := current_timestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.updated    := current_timestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;
/
ALTER TRIGGER "EBA_PROJECTS_BIU" ENABLE;

  CREATE TABLE "EBA_PROJECT_COMMENTS" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"PROJECT_ID" NUMBER NOT NULL ENABLE, 
	"COMMENT_TEXT" VARCHAR2(4000) NOT NULL ENABLE, 
	"CREATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	"UPDATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "EBA_PROJECT_COMMENTS_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "EBA_PROJECT_COMMENTS" ADD CONSTRAINT "EBA_PROJECT_COMMENT_PROJ_FK" FOREIGN KEY ("PROJECT_ID")
	  REFERENCES "EBA_PROJECTS" ("ID") ON DELETE CASCADE ENABLE;

  CREATE INDEX "EBA_PROJECT_COMMENT_PRJ_IDX" ON "EBA_PROJECT_COMMENTS" ("PROJECT_ID") 
  ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EBA_PROJECT_COMMENTS_BIU" 
    before insert or update on eba_project_comments
    for each row
begin
    if inserting then
        :new.created    := current_timestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.updated    := current_timestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;
/
ALTER TRIGGER "EBA_PROJECT_COMMENTS_BIU" ENABLE;

  CREATE TABLE "EBA_PROJECT_MILESTONES" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"PROJECT_ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(4000), 
	"DUE_DATE" DATE NOT NULL ENABLE, 
	"CREATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	"UPDATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "EBA_PROJECT_MILESTONES_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "EBA_PROJECT_MILESTONES" ADD CONSTRAINT "EBA_PROJECT_MSTONE_PROJ_FK" FOREIGN KEY ("PROJECT_ID")
	  REFERENCES "EBA_PROJECTS" ("ID") ON DELETE CASCADE ENABLE;

  CREATE INDEX "EBA_PROJECT_MSTONE_PROJ_IDX" ON "EBA_PROJECT_MILESTONES" ("PROJECT_ID") 
  ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EBA_PROJECT_MILESTONES_BIU" 
    before insert or update on eba_project_milestones
    for each row
begin
    if inserting then
        :new.created    := current_timestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.updated    := current_timestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;
/
ALTER TRIGGER "EBA_PROJECT_MILESTONES_BIU" ENABLE;

  CREATE TABLE "EBA_PROJECT_STATUS" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 4 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"CODE" VARCHAR2(15) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(255) NOT NULL ENABLE, 
	"DISPLAY_ORDER" NUMBER NOT NULL ENABLE, 
	"CREATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	"UPDATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "EBA_PROJECT_USERS_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "EBA_PROJECT_STATUS_UK" UNIQUE ("CODE")
  USING INDEX  ENABLE
   ) ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EBA_PROJECT_STATUS_BIU" 
before insert or update on eba_project_status
    for each row
begin
    if inserting then
        :new.created    := current_timestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.code       := upper(:new.code);
    :new.updated    := current_timestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;
/
ALTER TRIGGER "EBA_PROJECT_STATUS_BIU" ENABLE;

  CREATE TABLE "EBA_PROJECT_TASKS" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"PROJECT_ID" NUMBER NOT NULL ENABLE, 
	"MILESTONE_ID" NUMBER, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(4000), 
	"ASSIGNEE" VARCHAR2(255), 
	"START_DATE" DATE NOT NULL ENABLE, 
	"END_DATE" DATE NOT NULL ENABLE, 
	"COST" NUMBER, 
	"IS_COMPLETE_YN" VARCHAR2(1), 
	"CREATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	"UPDATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "EBA_PROJECT_TASKS_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "EBA_PROJECT_TASKS_UK" UNIQUE ("PROJECT_ID", "NAME")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "EBA_PROJECT_TASKS" ADD CONSTRAINT "EBA_PROJECT_TASK_MSTONE_FK" FOREIGN KEY ("MILESTONE_ID")
	  REFERENCES "EBA_PROJECT_MILESTONES" ("ID") ON DELETE SET NULL ENABLE;
  ALTER TABLE "EBA_PROJECT_TASKS" ADD CONSTRAINT "EBA_PROJECT_TASK_PROJ_FK" FOREIGN KEY ("PROJECT_ID")
	  REFERENCES "EBA_PROJECTS" ("ID") ON DELETE CASCADE ENABLE;

  CREATE INDEX "EBA_PROJECT_TASK_PROJ_IDX" ON "EBA_PROJECT_TASKS" ("PROJECT_ID") 
  ;

  CREATE INDEX "EBA_PROJECT_TASK_MSTONE_IDX" ON "EBA_PROJECT_TASKS" ("MILESTONE_ID") 
  ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EBA_PROJECT_TASKS_BIU" 
    before insert or update on eba_project_tasks
    for each row
begin
    if inserting then
        :new.created    := current_timestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.updated    := current_timestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;
/
ALTER TRIGGER "EBA_PROJECT_TASKS_BIU" ENABLE;


  CREATE TABLE "EBA_PROJECT_TASK_LINKS" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"PROJECT_ID" NUMBER NOT NULL ENABLE, 
	"TASK_ID" NUMBER NOT NULL ENABLE, 
	"LINK_TYPE" VARCHAR2(20) NOT NULL ENABLE, 
	"URL" VARCHAR2(255), 
	"APPLICATION_ID" NUMBER, 
	"APPLICATION_PAGE" NUMBER, 
	"DESCRIPTION" VARCHAR2(4000), 
	"CREATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	"UPDATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "EBA_PROJECT_TASK_LINKS_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "EBA_PROJECT_TSK_LINK_LTY_CH" CHECK ( link_type in ('URL','Application')) ENABLE
   ) ;

  ALTER TABLE "EBA_PROJECT_TASK_LINKS" ADD CONSTRAINT "EBA_PROJECT_TSK_LINK_PRJ_FK" FOREIGN KEY ("PROJECT_ID")
	  REFERENCES "EBA_PROJECTS" ("ID") ON DELETE CASCADE ENABLE;
  ALTER TABLE "EBA_PROJECT_TASK_LINKS" ADD CONSTRAINT "EBA_PROJECT_TSK_LINK_TSK_FK" FOREIGN KEY ("TASK_ID")
	  REFERENCES "EBA_PROJECT_TASKS" ("ID") ON DELETE CASCADE ENABLE;

  CREATE INDEX "EBA_PROJECT_TSK_LINK_TK_IDX" ON "EBA_PROJECT_TASK_LINKS" ("TASK_ID") 
  ;

  CREATE INDEX "EBA_PROJECT_TSK_LINK_PR_IDX" ON "EBA_PROJECT_TASK_LINKS" ("PROJECT_ID") 
  ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EBA_PROJECT_TASK_LINKS_BIU" 
    before insert or update on eba_project_task_links
    for each row
begin
    if inserting then
        :new.created    := current_timestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.updated    := current_timestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;
/
ALTER TRIGGER "EBA_PROJECT_TASK_LINKS_BIU" ENABLE;


  CREATE TABLE "EBA_PROJECT_TASK_TODOS" 
   (	"ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"PROJECT_ID" NUMBER NOT NULL ENABLE, 
	"TASK_ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(255) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(4000), 
	"ASSIGNEE" VARCHAR2(255), 
	"IS_COMPLETE_YN" VARCHAR2(1), 
	"CREATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"CREATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	"UPDATED" TIMESTAMP (6) WITH TIME ZONE NOT NULL ENABLE, 
	"UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE, 
	 CONSTRAINT "EBA_PROJECT_TASK_TODOS_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "EBA_PROJECT_TASK_TODOS" ADD CONSTRAINT "EBA_PROJECT_TSK_TODO_TSK_FK" FOREIGN KEY ("TASK_ID")
	  REFERENCES "EBA_PROJECT_TASKS" ("ID") ON DELETE CASCADE ENABLE;
  ALTER TABLE "EBA_PROJECT_TASK_TODOS" ADD CONSTRAINT "EBA_PROJ_TASK_TODO_PROJ_FK" FOREIGN KEY ("PROJECT_ID")
	  REFERENCES "EBA_PROJECTS" ("ID") ON DELETE CASCADE ENABLE;

  CREATE INDEX "EBA_PROJECT_TSK_TODO_PR_IDX" ON "EBA_PROJECT_TASK_TODOS" ("PROJECT_ID") 
  ;

  CREATE INDEX "EBA_PROJECT_TSK_TODO_TK_IDX" ON "EBA_PROJECT_TASK_TODOS" ("TASK_ID") 
  ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EBA_PROJECT_TASK_TODOS_BIU" 
    before insert or update on eba_project_task_todos
    for each row
begin
    if inserting then
        :new.created    := current_timestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.updated    := current_timestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;
/
ALTER TRIGGER "EBA_PROJECT_TASK_TODOS_BIU" ENABLE;


  CREATE OR REPLACE FORCE EDITIONABLE VIEW "EBA_PROJECTS_V" ("ID", "NAME", "STATUS", "PROJECT_LEAD", "COMPLETED_DATE", "BUDGET", "COST", "BUDGET_V_COST", "MILESTONES", "TASKS") AS 
  select p.id
,      p.name
,      s.description status
,      p.project_lead
,      p.completed_date
,      p.budget
,      (select sum(t.cost)
        from eba_project_tasks t
        where t.project_id = p.id
       ) cost
,       p.budget - (select sum(t.cost)
                   from eba_project_tasks t
                   where t.project_id = p.id
       ) budget_v_cost
,      (select count(*)
        from eba_project_milestones m
        where m.project_id = p.id
       ) milestones 
,      (select count(*)
        from eba_project_tasks t
        where t.project_id = p.id
       ) tasks
from eba_projects p
,    eba_project_status s
where s.id = p.status_id;


  CREATE OR REPLACE FORCE EDITIONABLE VIEW "EBA_PROJECTS_COMPLETED_V" ("ID", "NAME", "PROJECT_LEAD", "COMPLETED_DATE", "BUDGET", "COST", "TASKS_ON_TIME", "TASKS_LATE", "TASKS_INCOMPLETE", "MILESTONES_ON_TIME", "MILESTONES_LATE", "MILESTONES_INCOMPLETE") AS 
  select p.id
,      p.name
,      p.project_lead
,      p.completed_date
,      p.budget
,      (select sum(t.cost)
        from eba_project_tasks t
        where t.project_id = p.id
       ) cost
,      (  (select count(*)
           from eba_project_milestones m
           ,    eba_project_tasks t
           where t.project_id = p.id
           and   t.milestone_id = m.id
           and   nvl(t.is_complete_yn,'N') = 'Y'
           and   t.end_date <= m.due_date
          )
        + (select count(*)
           from eba_project_tasks t
           where t.project_id = p.id
           and   t.milestone_id is null
           and   nvl(t.is_complete_yn,'N') = 'Y'
          )
       ) tasks_on_time 
,      (select count(*)
        from eba_project_milestones m
        ,    eba_project_tasks t
        where t.project_id = p.id
        and   t.milestone_id = m.id
        and   nvl(t.is_complete_yn,'N') = 'Y'
        and   t.end_date > m.due_date
       ) tasks_late
,      (select count(*)
        from eba_project_tasks t
        where t.project_id = p.id
        and   nvl(t.is_complete_yn,'N') = 'N'
       ) tasks_incomplete
,      (select count(*)
        from eba_project_milestones m
        where m.project_id = p.id
        and   not exists (select t.id
                          from eba_project_tasks t
                          where t.milestone_id = m.id
                          and   nvl(t.is_complete_yn,'N') = 'Y'
                          and   t.end_date > m.due_date
                         )
       ) milestones_on_time
,      (select count(*)
        from eba_project_milestones m
        where m.project_id = p.id
        and   exists (select t.id
                      from eba_project_tasks t
                      where t.milestone_id = m.id
                      and   nvl(t.is_complete_yn,'N') = 'Y'
                      and   t.end_date > m.due_date
                     )
       ) milestones_late
,      (select count(*)
        from eba_project_milestones m
        where m.project_id = p.id
        and   exists (select t.id
                      from eba_project_tasks t
                      where t.milestone_id = m.id
                      and   nvl(t.is_complete_yn,'N') = 'N'
                     )
       ) milestones_incomplete
from eba_projects p
where p.status_id = 3;
　　以上演示数据库的创建数据库对象脚本。
环境：ORACLE 23AI Free + apex24.2+ Oracle REST Data Services 25.3 + Oracle SQLcl 25.3
posted @ 2026-04-18 07:01 samrv 阅读(6) 评论(0) 收藏举报
刷新页面返回顶部
APEX:Project Data示例项目数据库(EBA_PROJECTS)

公告
