本文共 4189 字，大约阅读时间需要 13 分钟。
--书藉位置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; 转载地址：http://kdvxl.baihongyu.com/