sql: Oracle 11g create table, function,trigger, sequence，11gtrigger

sql: Oracle 11g create table, function,trigger, sequence，11gtrigger

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

create table 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 BookPlaceList;

---自動增長ID 
--序列創建 
drop SEQUENCE BookPlaceList_SEQ;

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

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

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

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

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

select * from BookPlaceList;

drop table StaffReaderList;
--職員信息Reader  staff member IC卡號（卡換了，卡號不一樣），員工號，職位，部門,如果職員換崗或離職了，這個問題如何解決記錄關聯問題
create table 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 DepartmentList(DepartmentID),--員工所屬部門（外鍵）   ON DELETE SET NULL   ON DELETE CASCADE
	  StaffReaderPosition	int, 
     CONSTRAINT fky_StaffReaderPosition
			FOREIGN KEY(StaffReaderPosition) REFERENCES 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  BookAdministratorList(BookAdminID),--操作人員ID（添加記錄的人員）（外鍵）
    StaffReaderDatetime TIMESTAMP  --				
);

--判斷表是否存在
SELECT COUNT(*) FROM User_Tables t WHERE t.table_name = upper('AuthorList');
create or replace FUNCTION f_BookPlacename(kid in number) RETURN nvarchar2 IS
tmpVar nvarchar2(100);
/******************************************************************************
   NAME:       f_BookPlacename
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2015/5/21   geovindu       1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     f_BookPlacename
      Sysdate:         2015/5/21
      Date and Time:   2015/5/21, 12:02:38, and 2015/5/21 12:02:38
      Username:        geovindu (set in TOAD Options, Procedure Editor)
      Table Name:      BookPlaceList (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   --tmpVar := "";
   select BookPlaceName into tmpVar from BookPlaceList where BookPlaceID=kid;
   RETURN tmpVar;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
        --tmpVar := "";
       -- Consider logging the error and then re-raise
       RAISE;
END f_BookPlacename;
--測試 塗聚文 20150522
select f_BookPlacename(1)  FROM dual;