--书藉位置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;
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;
declare --在PL/SQL 匿名块中定义变量
vName nvarchar2(200):='齐白石水彩画系列';
vEname nvarchar2(200):='丰子恺油画系列';
vId number:=2;
--set serveroutput on size 5000;
begin
update BookSeriesList set BookSeriesName=vName where BookSeriesID=vId;
DBMS_OUTPUT.PUT_LINE('书系列更新成功！');
IF SQL%NOTFOUND THEN --判断，如果未更新数据，则向表中插入记录
insert into BookSeriesList(BookSeriesName) values(vName);
DBMS_OUTPUT.PUT_LINE('书系列插入成功！');
END IF;
---异常处理
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('插入书系列记录错误！');
end;
Toad for Oracle 启动DBMS输出. Oracle SQL developer 和 SQL Plus 用Script启动:set serveroutput on size 5000;
见图:
