Oracle11g: simple sql script examples_ZenDei技術網路在線

---https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm drop user geovin; drop user geovindu; create user geovindu identified by... ...
---https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm
drop user geovin;

drop user geovindu;

create user geovindu identified by ORCA;

--oracle用戶創建及許可權設置
create user geovin identified by password
default tablespace users 
quota 10m on users 
temporary tablespace temp
password expire; 

--創建用戶才有權限創建表
create user geovindu identified by password
default tablespace users 
quota 10m on users 
temporary tablespace temp
password expire; 

alter user GEOVINDU account lock;

--組用戶許可權
grant create session to GEOVINDU;

grant create session to GEOVIN;


select username,user_id,account_status,default_tablespace,temporary_tablespace from dba_users where default_tablespace='USERS' AND account_status='OPEN';
--查看用戶
select username,user_id,account_status,default_tablespace,temporary_tablespace from dba_users where default_tablespace='USERS';

select username,user_id,account_status,default_tablespace,temporary_tablespace from dba_users where username='GEOVINDU';


select tablespace_name,bytes,max_bytes  from dba_ts_quotas;



--查詢表是否存在
SELECT COUNT(*) FROM User_Tables t  WHERE t.table_name = upper('BookKindList');

drop table TestDu; --刪除表

select * from TestDu;

declare tableCount number;
begin
   select count(1) into tableCount  from user_tables t where t.table_name = upper('TestDu'); --從系統表中查詢當表是否存在
   if tableCount  = 0 then --如果不存在，使用快速執行語句創建新表
      execute immediate
      'create table TestDu --創建測試表
         (
             TestID     number   not null,
             TestName   varchar2(20)  not null
          )';
   end if;
end;

delete from BookKindList; 

drop table BookKindList;

truncate table BookKindList;

--書分類目錄kind 
-- Geovin Du 
create table geovindu.BookKindList
(
	BookKindID INT   PRIMARY KEY,
	BookKindName nvarchar2(500) not null,
	BookKindParent INT  null,
	BookKindCode varchar(100)   ---編號
);
--序列創建
 
drop SEQUENCE BookKindList_SEQ;

CREATE SEQUENCE geovindu.BookKindList_SEQ
INCREMENT BY 1     -- 每次加幾個
START WITH 1     -- 從1開始計數
NOMAXVALUE        -- 不設置最大值
NOCYCLE            -- 一直累加，不迴圈
NOCACHE;           --設置緩存cache個序列，如果系統down掉了或者其它情況將會導致序列不連續，也可以設置為---------NOCACHE

--自增長觸發器  
drop trigger BookKindList_ID_AUTO;


 create or replace trigger geovindu.BookKindList_ID_AUTO
  before insert on geovindu.BookKindList   --BookKindList 是表名
  for each row
declare
  nextid number;
begin
  IF :new.BookKindID IS NULL or :new.BookKindID=0 THEN --BookKindID是列名
    select geovindu.BookKindList_SEQ.Nextval --BookKindList_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.BookKindID:=nextid;
  end if;
end;    

--對錶的說明
comment on table geovindu.BookKindList is '書分類目錄';
--對錶中列的說明
comment on column geovindu.BookKindList.BookKindID is '目錄ID';
comment on column geovindu.BookKindList.BookKindName is '目錄名稱';
comment on column geovindu.BookKindList.BookKindParent is '目錄父ID';
comment on column geovindu.BookKindList.BookKindCode is '目錄code';


declare
gg nvarchar2(500):='geovindu2';
dd nvarchar2(500):='d';
begin
select REPLACE(gg, chr(10), '') into dd from dual;
dbms_output.put_line(dd);
end;



insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('六福書目錄',0,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('自然科學',1,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('社會科學',1,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('文學',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('設計藝術',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('小說',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('詩詞散曲',4,'');

select * from geovindu.BookKindList;

SELECT * FROM geovindu.BookKindList
ORDER BY BookKindID
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY;


SELECT *
FROM geovindu.BookKindList
ORDER BY BookKindID
FETCH FIRST 5 ROWS ONLY;

---
SELECT *
FROM
  (SELECT BookKindID,
  BookKindName,
    BookKindParent,    
    ROW_NUMBER() OVER (ORDER BY BookKindID) R
  FROM geovindu.BookKindList
  WHERE BookKindID >= 1
  )
WHERE R >= 1
AND R   <= 15;


declare 
pageNumber int:=1;
pageSize int:=3;
begin
SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM geovindu.BookKindList WHERE BookKindName LIKE 'A%'
        ORDER BY BookKindID DESC, BookKindName DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)

WHERE r__ >= (((pageNumber-1) * pageSize) + 1)
end;



select * from geovindu.BookKindList where BookKindName='文學';
update geovindu.BookKindList set BookKindName='電腦' where BookKindID=1;
--DISTINCT  not in
declare
temvar nvarchar2(200):='哲學';
namevar int;
begin
select count(*) into namevar from geovindu.BookKindList T1 where exists (select BookKindName  from geovindu.BookKindList T2 where T1.BookKindName = temvar ); --not  exist除它自身之外的個數，exists自身的個數
  dbms_output.put_line('value'||namevar);
 if namevar<=0 then
 begin
    insert into geovindu.BookKindList(BookKindName,BookKindParent) values(temvar,0);
    dbms_output.put_line('insert'||namevar);
 end; 
 else
 begin
      select BookKindID into namevar from geovindu.BookKindList where BookKindName=temvar;
     update geovindu.BookKindList set BookKindName=temvar where BookKindID=namevar;
    dbms_output.put_line('update  '||namevar);
 end;
 end if;
end;

declare
temvar nvarchar2(200):='文學';
namevar int;
begin
if exists  (select BookKindName  from geovindu.BookKindList T2 where T1.BookKindName = temvar ) then  --不可以exists 
    dbms_output.put_line('update'||namevar);
else
   dbms_output.put_line('value'||namevar);
end if;
end;

--書藉位置Place目錄
 drop table geovindu.BookPlaceList;
 

create table geovindu.BookPlaceList
(
	BookPlaceID INT  PRIMARY KEY,  --NUMBER
	BookPlaceName nvarchar2(500) not null,
	BookPlaceCode varchar(100) null,		--位置編碼
	BookPlaceParent INT  null
	--BookPlaceKindId nvarchar(500) null       --放置目錄範圍ID
);

select * from geovindu.BookPlaceList;
----自動增長ID 
--序列創建 
drop SEQUENCE geovindu.BookPlaceList_SEQ;

CREATE SEQUENCE geovindu.BookPlaceList_SEQ
INCREMENT BY 1     -- 每次加幾個
START WITH 1     -- 從1開始計數
NOMAXVALUE        -- 不設置最大值
NOCYCLE            -- 一直累加，不迴圈
NOCACHE;           --設置緩存cache個序列，如果系統down掉了或者其它情況將會導致序列不連續，也可以設置為---------NOCACHE
      
SELECT geovindu.BookPlaceList_SEQ.Currval FROM DUAL;

SELECT geovindu.BookPlaceList_SEQ.Nextval FROM DUAL;
  
--自增長觸發器
drop TRIGGER geovindu.BookPlaceList_ID_AUTO;

CREATE OR REPLACE TRIGGER geovindu.BookPlaceList_ID_AUTO
BEFORE INSERT ON geovindu.BookPlaceList FOR EACH ROW
BEGIN
SELECT geovindu.BookPlaceList_SEQ.NEXTVAL INTO :NEW.BookPlaceID FROM DUAL;
END;
      
--自增長觸發器      
create or replace trigger geovindu.BookPlaceList_ID_AUTO
  before insert on geovindu.BookPlaceList   --BookPlaceList 是表名
  for each row
declare
  nextid number;
begin
  IF :new.BookPlaceID IS NULL or :new.BookPlaceID=0 THEN --BookPlaceID是列名
    select geovindu.BookPlaceList_SEQ.Nextval --BookPlaceList_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.BookPlaceID:=nextid;
  end if;
end;  -- BookPlaceList_ID_AUTO

 --添加
insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('圖書位置目錄','',0);
 
insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第一櫃','',1);
insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第二櫃','',1);

insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第三櫃','',1);

select * from geovindu.BookPlaceList;

--
CREATE TABLE geovindu.YearNames (
  YearNameID INT  PRIMARY KEY, 
  YearName varchar(50) NOT NULL 
);

--書系列Series或套名稱（一本的0.無，有分上下本）
create table geovindu.BookSeriesList
(
	BookSeriesID  INT  PRIMARY KEY,  --INTEGERint
	BookSeriesName nvarchar2(500) not null
);

--序列創建 
CREATE SEQUENCE geovindu.BookSeriesList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器      
create or replace trigger geovindu.BookSeriesList_ID_AUTO
  before insert on geovindu.BookSeriesList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.BookSeriesID IS NULL or :new.BookSeriesID=0 THEN --ID是列名
    select geovindu.BookSeriesList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.BookSeriesID:=nextid;
  end if;
end;     

--職位Position，
create table geovindu.PositionList
(
	PositionID INT  PRIMARY KEY,
	PositionName nvarchar2(500) not null
);

--部門Department 
create table geovindu.DepartmentList
(
	DepartmentID INT  PRIMARY KEY,
	DepartmentName nvarchar2(500) not null
);
--序列創建 
CREATE SEQUENCE geovindu.DepartmentList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器      
create or replace trigger geovindu.DepartmentList_ID_AUTO
  before insert on geovindu.DepartmentList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.DepartmentID IS NULL or :new.DepartmentID=0 THEN --ID是列名
    select geovindu.DepartmentList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.DepartmentID:=nextid;
  end if;
end;

--語種 Language
create table geovindu.LanguageList
(
	LanguageID INT PRIMARY KEY,
	LanguageName nvarchar2(500) not null
);
--序列創建 
CREATE SEQUENCE geovindu.LanguageList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器      
create or replace trigger geovindu.LanguageList_ID_AUTO
  before insert on geovindu.LanguageList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.LanguageID IS NULL or :new.LanguageID=0 THEN --ID是列名
    select geovindu.LanguageList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.LanguageID:=nextid;
  end if;
end;


--出版社Press
create table geovindu.PressList
(
	PressID INT PRIMARY KEY,
	PressName nvarchar2(500) not null
	--拼音索引
);
--序列創建 
CREATE SEQUENCE geovindu.PressList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器      
create or replace trigger geovindu.PressList_ID_AUTO
  before insert on geovindu.PressList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.PressID IS NULL or :new.PressID=0 THEN --ID是列名
    select geovindu.PressList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.PressID:=nextid;
  end if;
end;
--判斷表是否存在
SELECT COUNT(*) FROM geovindu.User_Tables t WHERE t.table_name = upper('AuthorList');

--作家Author
create table geovindu.AuthorList
(
	AuthorID INT PRIMARY KEY,
	AuthorName nvarchar2(500) not null
);
--序列創建 
CREATE SEQUENCE geovindu.AuthorList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器      
create or replace trigger geovindu.AuthorList_ID_AUTO
  before insert on geovindu.AuthorList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.AuthorID IS NULL or :new.AuthorID=0 THEN --ID是列名
    select geovindu.AuthorList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.AuthorID:=nextid;
  end if;
end;
--BookStatus 書藉存在狀態（1，在用，2，報廢，3。轉移）
create table geovindu.BookStatusList
(
	BookStatusID INT PRIMARY KEY,
	BookStatusName nvarchar2(500) not null
);
--序列創建 
CREATE SEQUENCE geovindu.BookStatusList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器      
create or replace trigger geovindu.BookStatusList_ID_AUTO
  before insert on geovindu.BookStatusList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.BookStatusID IS NULL or :new.BookStatusID=0 THEN --ID是列名
    select geovindu.BookStatusList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.BookStatusID:=nextid;
  end if;
end;
--借閱狀態：借出，續借，歸還，預借
create table geovindu.LendStatusList
(
	LendStatusID INT  PRIMARY KEY,
	LendStatusName nvarchar2(500) not null
);
--序列創建 
CREATE SEQUENCE geovindu.LendStatusList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器      
create or replace trigger geovindu.LendStatusList_ID_AUTO
  before insert on geovindu.LendStatusList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.LendStatusID IS NULL or :new.LendStatusID=0 THEN --ID是列名
    select geovindu.LendStatusList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.LendStatusID:=nextid;
  end if;
end;

drop table geovindu.DielectricList;
--圖書介質（紙質，光碟，硬碟，網路）DielectricMaterials
create table geovindu.DielectricList
(
	DielectricID INT  PRIMARY KEY,
	DielectriName nvarchar2(500) not null
);
--序列創建 
CREATE SEQUENCE geovindu.DielectricList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.DielectricList_ID_AUTO
  before insert on geovindu.DielectricList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.DielectricID IS NULL or :new.DielectricID=0 THEN --ID是列名
    select geovindu.DielectricList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.DielectricID:=nextid;
  end if;
end;

--角色或權限類型Permission
create table geovindu.PermissionList
(
	PermissionID INT  PRIMARY KEY,
	PermissionName nvarchar2(500) not null,
	PermissionDesc NCLOB null
);
--序列創建 
CREATE SEQUENCE geovindu.PermissionList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.PermissionList_ID_AUTO
  before insert on geovindu.PermissionList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.PermissionID IS NULL or :new.PermissionID=0 THEN --ID是列名
    select geovindu.PermissionList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.PermissionID:=nextid;
  end if;
end;

---菜單列表，用於控制許可權
create table geovindu.PermissionMenu
(
	PermissionMenuID INT  PRIMARY KEY,  --IDENTITY(1,1)
	PermissionMenuName nvarchar2(500) not null,
	PermissionMenuParent int null
);
--序列創建 
CREATE SEQUENCE geovindu.PermissionMenu_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.PermissionMenu_ID_AUTO
  before insert on geovindu.PermissionMenu   --表名
  for each row
declare
  nextid number;
begin
  IF :new.PermissionMenuID IS NULL or :new.PermissionMenuID=0 THEN --ID是列名
    select geovindu.PermissionMenu_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.PermissionMenuID:=nextid;
  end if;
end;

---找回密碼問類型Question Answer
create table geovindu.QuestionTypeList
(
	QuestionTypeID INT  PRIMARY KEY,
	QuestionTypeName nvarchar2(500) not null
);

--序列創建 
CREATE SEQUENCE geovindu.QuestionTypeList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.QuestionTypeList_ID_AUTO
  before insert on geovindu.QuestionTypeList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.QuestionTypeID IS NULL or :new.QuestionTypeID=0 THEN --ID是列名
    select geovindu.QuestionTypeList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.QuestionTypeID:=nextid;
  end if;
end;


drop table geovindu.StaffReaderList;
--職員信息Reader  staff member IC卡號（卡換了，卡號不一樣），員工號，職位，部門,如果職員換崗或離職了，這個問題如何解決記錄關聯問題
create table geovindu.StaffReaderList
(
	StaffReaderID INT  PRIMARY KEY,
    StaffReaderIC varchar(100) not null,			--員工工牌IC號
    StaffReaderNO varchar(20) not null,				--員工編號
	StaffReaderName nvarchar2(500) not null,			--員工姓名
	StaffReaderImage BFILE null,
	StaffReaderDepartment int,
     CONSTRAINT fky_StaffReaderDepartment
			FOREIGN KEY(StaffReaderDepartment) REFERENCES geovindu.DepartmentList(DepartmentID),--員工所屬部門（外鍵）   ON DELETE SET NULL   ON DELETE CASCADE
	  StaffReaderPosition	int, 
     CONSTRAINT fky_StaffReaderPosition
			FOREIGN KEY(StaffReaderPosition) REFERENCES geovindu.PositionList(PositionID),	--職位Position（外鍵）
	  StaffReaderMobile varchar(50) null,				--手機
    StaffReaderTel varchar(200) null,				--電話，
    StaffReaderSkype varchar(50) null,				---
    StaffReaderQQ varchar(50) null,					--
    StaffReaderEmail varchar(100) null,				--電子郵件
    StaffReaderIsJob char check (StaffReaderIsJob in ('N','Y')),				--是否離職
    StaffReaderOperatorID int,
    CONSTRAINT fky_StaffReaderOperatorID
	         FOREIGN KEY(StaffReaderOperatorID) REFERENCES  geovindu.BookAdministratorList(BookAdminID),--操作人員ID（添加記錄的人員）（外鍵）
    StaffReaderDatetime TIMESTAMP  --				
);

--序列創建 
CREATE SEQUENCE geovindu.StaffReaderList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.StaffReaderList_ID_AUTO
  before insert on geovindu.StaffReaderList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.StaffReaderID IS NULL or :new.StaffReaderID=0 THEN --ID是列名
    select geovindu.StaffReaderList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.StaffReaderID:=nextid;
  end if;
end;


--許可權類型列表，也是系統操作的視窗功能的詳細列表BookAdminPermissTypeList
create table geovindu.BookAdminPermissTypeList
(
	AdminPermissTypeID INT  PRIMARY KEY,
    AdminPermissParent int null,						--父類型
    AdminPermissTypeName nvarchar2(300) not null,	
    AdminPermissTypeDesc NCLOB null,					--許可權描述	
	AdminPermissFormName varchar(100) null				--視窗名稱
);

--序列創建 
CREATE SEQUENCE geovindu.AdminPermissTypeList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.AdminPermissTypeList_ID_AUTO
  before insert on geovindu.BookAdminPermissTypeList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.AdminPermissTypeID IS NULL or :new.AdminPermissTypeID=0 THEN --ID是列名
    select geovindu.AdminPermissTypeList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.AdminPermissTypeID:=nextid;
  end if;
end;


--權限公配錶 listview treeview  check
create table geovindu.BookAdminPermissionrList
(
	BookAdminPermissID INT  PRIMARY KEY,
	BookAdminPermissKey int,
  CONSTRAINT fky_BookAdminPermiss
			FOREIGN KEY(BookAdminPermissKey) REFERENCES geovindu.BookAdministratorList(BookAdminID) ON DELETE CASCADE,				--管理員ID
    BookAdminPermissDesc NCLOB null					--許可權分配ID	
);

CREATE SEQUENCE geovindu.AdminPermissionrList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.AdminPermissionrList_ID_AUTO
  before insert on geovindu.BookAdminPermissionrList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.BookAdminPermissID IS NULL or :new.BookAdminPermissID=0 THEN --ID是列名
    select geovindu.AdminPermissionrList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.BookAdminPermissID:=nextid;
  end if;
end;


--還有一種方式是角色快速分配許可權,先固定角色類型分配權限，再角色設置權限   PermissionList
create table geovindu.PermissionAssignmentList
(
	PermissionAssignmentID INT PRIMARY KEY,
	PermissionAssignmentKey int,
  CONSTRAINT fky_PermissionAssignment
			FOREIGN KEY(PermissionAssignmentKey) REFERENCES geovindu.PermissionList(PermissionID),	--角色ID
    PermissionAssignmentDesc NCLOB null					    --許可權分配ID	
);

CREATE SEQUENCE geovindu.PermissionAssignment_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.PermissionAssignment_ID_AUTO
  before insert on geovindu.PermissionAssignmentList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.PermissionAssignmentID IS NULL or :new.PermissionAssignmentID=0 THEN --ID是列名
    select geovindu.PermissionAssignment_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.PermissionAssignmentID:=nextid;
  end if;
end;

--許可權管理 AuthorizationManagement
create table geovindu.BookAdministratorList
(
   BookAdminID INT  PRIMARY KEY,
   BookAdminIC varchar(100) not null,				--員工工牌IC號（換了卡，號會改變的）
   BookAdminNO varchar(20) not null,				--員工編號
   BookAdminName nvarchar2(500) not null,			--員工姓名
   BookAdminEmail varchar(100) null,				--電子郵件
   BookAdminQQ varchar(50) null,					--
   BookAdminSkype varchar(50) null,					--
   BookAdminPassword nvarchar2(100) not null,		--密碼
   BookAdminQuestion int,  
   CONSTRAINT fky_AdminQuestionID
               FOREIGN KEY(BookAdminQuestion) REFERENCES geovindu.QuestionTypeList(QuestionTypeID),	--找迴密碼類型(外鍵）
   BookAdminAnswer nvarchar2(300) null,				--找迴密碼答題
   BookAdminIs  char check (BookAdminIs in ('N','Y')),				--是否在職
   BookAdminPermission	int,
   CONSTRAINT fky_PermissionID
              FOREIGN KEY (BookAdminPermission) REFERENCES  geovindu.PermissionList(PermissionID),	--許可權範圍(錄入人員，盤點人員，申請書報銷人員，批准人員，審核人員等）(角色或權限類型外鍵）		
   BookAdminDate TIMESTAMP
);

CREATE SEQUENCE geovindu.AdministratorList_SEQ
INCREMENT BY 1     
START WITH 1     
NOMAXVALUE        
NOCYCLE           
NOCACHE; 
      
 --自增長觸發器 (名稱不能超過三十個字元)     
create or replace trigger geovindu.BookAdministratorList_ID_AUTO
  before insert on geovindu.BookAdministratorList   --表名
  for each row
declare
  nextid number;
begin
  IF :new.BookAdminID IS NULL or :new.BookAdminID=0 THEN --ID是列名
    select geovindu.AdministratorList_SEQ.Nextval --_SEQ正是剛纔創建的
    into nextid
    from dual;
    :new.BookAdminID:=nextid;
  end if;
end;


--2、創建主鍵
ALTER TABLE geovindu.BookAdministratorList
  ADD CONSTRAINT pk_BookAdminQuestion 
  PRIMARY KEY (BookAdminQuestion) USING INDEX ;
  
--3、創建Unique約束
ALTER TABLE geovindu.BookAdministratorList
  ADD CONSTRAINT uk_students_license 
  UNIQUE (state, license_no) USING INDEX ;
--4、創建Check約束
ALTER TABLE geovindu.BookAdministratorList ADD CONSTRAINT ck_students_st_lic
   CHECK ((state IS NULL AND license_no IS NULL) OR
          (state IS NOT NULL AND license_no is NOT NULL));
--5、創建外鍵約束
ALTER TABLE geovindu.BookAdministratorList
  ADD CONSTRAINT fk_students_state
  FOREIGN KEY (state) REFERENCES state_lookup (state);

--開啟屏幕輸出顯示
-- SET SERVEROUTPUT ON;
--顯示當前日期與時間
BEGIN
  DBMS_OUTPUT.PUT_LINE('現在的日期時間：');
  --顯示信息不換行
  DBMS_OUTPUT.PUT('今天是：');
  --顯示信息並換行
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'DAY'));
  DBMS_OUTPUT.PUT('現在時間是： ');
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));  
END;