Oracle11g: simple sql script examples

---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;