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

--书藉位置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_SEQINCREMENT 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_AUTOBEFORE INSERT ON BookPlaceList FOR EACH ROWBEGINSELECT BookPlaceList_SEQ.NEXTVAL INTO :NEW.BookPlaceID FROM DUAL;END;   --自增长触发器   create or replace trigger BookPlaceList_ID_AUTO before insert on BookPlaceList  --BookPlaceList 是表名 for each rowdeclare 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 IStmpVar 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;--测试 涂聚文 20150522select f_BookPlacename(1) FROM dual;

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：sql: Oracle 11g create table, function,trigger, sequence

关键词：sql