oracle create table sequence,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_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

StaffReaderPositionint,

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;

原文：http://www.cnblogs.com/geovindu/p/4521499.html