simple sql script examples"/>
Oracle11g: simple sql script examples
---.102/b14200/statements_8003.htm
drop user geovin;drop user geovindu;create user geovindu identified by ORCA;--oracle用户创建及权限设置
create user geovin identified by password
default tablespace users
quota 10m on users
temporary tablespace temp
password expire; --創建用戶才有權限創建表
create user geovindu identified by password
default tablespace users
quota 10m on users
temporary tablespace temp
password expire; alter user GEOVINDU account lock;--组用户权限
grant create session to GEOVINDU;grant create session to GEOVIN;select username,user_id,account_status,default_tablespace,temporary_tablespace from dba_users where default_tablespace='USERS' AND account_status='OPEN';
--查看用戶
select username,user_id,account_status,default_tablespace,temporary_tablespace from dba_users where default_tablespace='USERS';select username,user_id,account_status,default_tablespace,temporary_tablespace from dba_users where username='GEOVINDU';select tablespace_name,bytes,max_bytes from dba_ts_quotas;--查询表是否存在
SELECT COUNT(*) FROM User_Tables t WHERE t.table_name = upper('BookKindList');drop table TestDu; --删除表select * from TestDu;declare tableCount number;
beginselect 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;delete from BookKindList; drop table BookKindList;truncate table BookKindList;--书分类目录kind
-- Geovin Du
create table geovindu.BookKindList
(BookKindID INT PRIMARY KEY,BookKindName nvarchar2(500) not null,BookKindParent INT null,BookKindCode varchar(100) ---編號
);
--序列创建drop SEQUENCE BookKindList_SEQ;CREATE SEQUENCE geovindu.BookKindList_SEQ
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE--自增长触发器
drop trigger BookKindList_ID_AUTO;create or replace trigger geovindu.BookKindList_ID_AUTObefore insert on geovindu.BookKindList --BookKindList 是表名for each row
declarenextid number;
beginIF :new.BookKindID IS NULL or :new.BookKindID=0 THEN --BookKindID是列名select geovindu.BookKindList_SEQ.Nextval --BookKindList_SEQ正是刚才创建的into nextidfrom dual;:new.BookKindID:=nextid;end if;
end; --对表的说明
comment on table geovindu.BookKindList is '书分类目录';
--对表中列的说明
comment on column geovindu.BookKindList.BookKindID is '目录ID';
comment on column geovindu.BookKindList.BookKindName is '目录名称';
comment on column geovindu.BookKindList.BookKindParent is '目录父ID';
comment on column geovindu.BookKindList.BookKindCode is '目录code';declare
gg nvarchar2(500):='geovindu2';
dd nvarchar2(500):='d';
begin
select REPLACE(gg, chr(10), '') into dd from dual;
dbms_output.put_line(dd);
end;insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('六福书目录',0,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('自然科学',1,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('社会科学',1,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('文学',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('设计艺术',3,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('小说',4,'');
insert into geovindu.BookKindList(BookKindName,BookKindParent,BookKindCode) values('诗词散曲',4,'');select * from geovindu.BookKindList;SELECT * FROM geovindu.BookKindList
ORDER BY BookKindID
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY;SELECT *
FROM geovindu.BookKindList
ORDER BY BookKindID
FETCH FIRST 5 ROWS ONLY;---
SELECT *
FROM(SELECT BookKindID,BookKindName,BookKindParent, ROW_NUMBER() OVER (ORDER BY BookKindID) RFROM geovindu.BookKindListWHERE BookKindID >= 1)
WHERE R >= 1
AND R <= 15;declare
pageNumber int:=1;
pageSize int:=3;
begin
SELECT * FROM
(SELECT a.*, rownum r__FROM(SELECT * FROM geovindu.BookKindList WHERE BookKindName LIKE 'A%'ORDER BY BookKindID DESC, BookKindName DESC) aWHERE rownum < ((pageNumber * pageSize) + 1 )
)WHERE r__ >= (((pageNumber-1) * pageSize) + 1)
end;select * from geovindu.BookKindList where BookKindName='文学';
update geovindu.BookKindList set BookKindName='计算机' where BookKindID=1;
--DISTINCT not in
declare
temvar nvarchar2(200):='哲学';
namevar int;
begin
select count(*) into namevar from geovindu.BookKindList T1 where exists (select BookKindName from geovindu.BookKindList T2 where T1.BookKindName = temvar ); --not exist除它自身之外的个数，exists自身的个数dbms_output.put_line('value'||namevar);if namevar<=0 thenbegininsert into geovindu.BookKindList(BookKindName,BookKindParent) values(temvar,0);dbms_output.put_line('insert'||namevar);end; elsebeginselect BookKindID into namevar from geovindu.BookKindList where BookKindName=temvar;update geovindu.BookKindList set BookKindName=temvar where BookKindID=namevar;dbms_output.put_line('update '||namevar);end;end if;
end;declare
temvar nvarchar2(200):='文学';
namevar int;
begin
if exists (select BookKindName from geovindu.BookKindList T2 where T1.BookKindName = temvar ) then --不可以exists dbms_output.put_line('update'||namevar);
elsedbms_output.put_line('value'||namevar);
end if;
end;--书藉位置Place目录drop table geovindu.BookPlaceList;create table geovindu.BookPlaceList
(BookPlaceID INT PRIMARY KEY, --NUMBERBookPlaceName nvarchar2(500) not null,BookPlaceCode varchar(100) null, --位置編碼BookPlaceParent INT null--BookPlaceKindId nvarchar(500) null --放置目录範圍ID
);select * from geovindu.BookPlaceList;
----自动增长ID
--序列创建
drop SEQUENCE geovindu.BookPlaceList_SEQ;CREATE SEQUENCE geovindu.BookPlaceList_SEQ
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHESELECT geovindu.BookPlaceList_SEQ.Currval FROM DUAL;SELECT geovindu.BookPlaceList_SEQ.Nextval FROM DUAL;--自增长触发器
drop TRIGGER geovindu.BookPlaceList_ID_AUTO;CREATE OR REPLACE TRIGGER geovindu.BookPlaceList_ID_AUTO
BEFORE INSERT ON geovindu.BookPlaceList FOR EACH ROW
BEGIN
SELECT geovindu.BookPlaceList_SEQ.NEXTVAL INTO :NEW.BookPlaceID FROM DUAL;
END;--自增长触发器
create or replace trigger geovindu.BookPlaceList_ID_AUTObefore insert on geovindu.BookPlaceList --BookPlaceList 是表名for each row
declarenextid number;
beginIF :new.BookPlaceID IS NULL or :new.BookPlaceID=0 THEN --BookPlaceID是列名select geovindu.BookPlaceList_SEQ.Nextval --BookPlaceList_SEQ正是刚才创建的into nextidfrom dual;:new.BookPlaceID:=nextid;end if;
end; -- BookPlaceList_ID_AUTO--添加
insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('图书位置目录','',0);insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第一柜','',1);
insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第二柜','',1);insert into geovindu.BookPlaceList(BookPlaceName,BookPlaceCode,BookPlaceParent) values('第三柜','',1);select * from geovindu.BookPlaceList;--
CREATE TABLE geovindu.YearNames (YearNameID INT PRIMARY KEY, YearName varchar(50) NOT NULL
);--书系列Series或套名称（一本的0.无，有分上下本）
create table geovindu.BookSeriesList
(BookSeriesID INT PRIMARY KEY, --INTEGERintBookSeriesName nvarchar2(500) not null
);--序列创建
CREATE SEQUENCE geovindu.BookSeriesList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器
create or replace trigger geovindu.BookSeriesList_ID_AUTObefore insert on geovindu.BookSeriesList --表名for each row
declarenextid number;
beginIF :new.BookSeriesID IS NULL or :new.BookSeriesID=0 THEN --ID是列名select geovindu.BookSeriesList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.BookSeriesID:=nextid;end if;
end; --職位Position，
create table geovindu.PositionList
(PositionID INT PRIMARY KEY,PositionName nvarchar2(500) not null
);--部門Department
create table geovindu.DepartmentList
(DepartmentID INT PRIMARY KEY,DepartmentName nvarchar2(500) not null
);
--序列创建
CREATE SEQUENCE geovindu.DepartmentList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器
create or replace trigger geovindu.DepartmentList_ID_AUTObefore insert on geovindu.DepartmentList --表名for each row
declarenextid number;
beginIF :new.DepartmentID IS NULL or :new.DepartmentID=0 THEN --ID是列名select geovindu.DepartmentList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.DepartmentID:=nextid;end if;
end;--語种 Language
create table geovindu.LanguageList
(LanguageID INT PRIMARY KEY,LanguageName nvarchar2(500) not null
);
--序列创建
CREATE SEQUENCE geovindu.LanguageList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器
create or replace trigger geovindu.LanguageList_ID_AUTObefore insert on geovindu.LanguageList --表名for each row
declarenextid number;
beginIF :new.LanguageID IS NULL or :new.LanguageID=0 THEN --ID是列名select geovindu.LanguageList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.LanguageID:=nextid;end if;
end;--出版社Press
create table geovindu.PressList
(PressID INT PRIMARY KEY,PressName nvarchar2(500) not null--拼音索引
);
--序列创建
CREATE SEQUENCE geovindu.PressList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器
create or replace trigger geovindu.PressList_ID_AUTObefore insert on geovindu.PressList --表名for each row
declarenextid number;
beginIF :new.PressID IS NULL or :new.PressID=0 THEN --ID是列名select geovindu.PressList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.PressID:=nextid;end if;
end;
--判断表是否存在
SELECT COUNT(*) FROM geovindu.User_Tables t WHERE t.table_name = upper('AuthorList');--作家Author
create table geovindu.AuthorList
(AuthorID INT PRIMARY KEY,AuthorName nvarchar2(500) not null
);
--序列创建
CREATE SEQUENCE geovindu.AuthorList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器
create or replace trigger geovindu.AuthorList_ID_AUTObefore insert on geovindu.AuthorList --表名for each row
declarenextid number;
beginIF :new.AuthorID IS NULL or :new.AuthorID=0 THEN --ID是列名select geovindu.AuthorList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.AuthorID:=nextid;end if;
end;
--BookStatus 书藉存在状态（1，在用，2，报废，3。转移）
create table geovindu.BookStatusList
(BookStatusID INT PRIMARY KEY,BookStatusName nvarchar2(500) not null
);
--序列创建
CREATE SEQUENCE geovindu.BookStatusList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器
create or replace trigger geovindu.BookStatusList_ID_AUTObefore insert on geovindu.BookStatusList --表名for each row
declarenextid number;
beginIF :new.BookStatusID IS NULL or :new.BookStatusID=0 THEN --ID是列名select geovindu.BookStatusList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.BookStatusID:=nextid;end if;
end;
--借阅状态：借出，续借，归还，预借
create table geovindu.LendStatusList
(LendStatusID INT PRIMARY KEY,LendStatusName nvarchar2(500) not null
);
--序列创建
CREATE SEQUENCE geovindu.LendStatusList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器
create or replace trigger geovindu.LendStatusList_ID_AUTObefore insert on geovindu.LendStatusList --表名for each row
declarenextid number;
beginIF :new.LendStatusID IS NULL or :new.LendStatusID=0 THEN --ID是列名select geovindu.LendStatusList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.LendStatusID:=nextid;end if;
end;drop table geovindu.DielectricList;
--图书介质（纸质，光盘，硬盘，网络）DielectricMaterials
create table geovindu.DielectricList
(DielectricID INT PRIMARY KEY,DielectriName nvarchar2(500) not null
);
--序列创建
CREATE SEQUENCE geovindu.DielectricList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.DielectricList_ID_AUTObefore insert on geovindu.DielectricList --表名for each row
declarenextid number;
beginIF :new.DielectricID IS NULL or :new.DielectricID=0 THEN --ID是列名select geovindu.DielectricList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.DielectricID:=nextid;end if;
end;--角色或權限類型Permission
create table geovindu.PermissionList
(PermissionID INT PRIMARY KEY,PermissionName nvarchar2(500) not null,PermissionDesc NCLOB null
);
--序列创建
CREATE SEQUENCE geovindu.PermissionList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.PermissionList_ID_AUTObefore insert on geovindu.PermissionList --表名for each row
declarenextid number;
beginIF :new.PermissionID IS NULL or :new.PermissionID=0 THEN --ID是列名select geovindu.PermissionList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.PermissionID:=nextid;end if;
end;---菜单列表，用于控制权限
create table geovindu.PermissionMenu
(PermissionMenuID INT PRIMARY KEY, --IDENTITY(1,1)PermissionMenuName nvarchar2(500) not null,PermissionMenuParent int null
);
--序列创建
CREATE SEQUENCE geovindu.PermissionMenu_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.PermissionMenu_ID_AUTObefore insert on geovindu.PermissionMenu --表名for each row
declarenextid number;
beginIF :new.PermissionMenuID IS NULL or :new.PermissionMenuID=0 THEN --ID是列名select geovindu.PermissionMenu_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.PermissionMenuID:=nextid;end if;
end;---找回密码问类型Question Answer
create table geovindu.QuestionTypeList
(QuestionTypeID INT PRIMARY KEY,QuestionTypeName nvarchar2(500) not null
);--序列创建
CREATE SEQUENCE geovindu.QuestionTypeList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.QuestionTypeList_ID_AUTObefore insert on geovindu.QuestionTypeList --表名for each row
declarenextid number;
beginIF :new.QuestionTypeID IS NULL or :new.QuestionTypeID=0 THEN --ID是列名select geovindu.QuestionTypeList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.QuestionTypeID:=nextid;end if;
end;drop table geovindu.StaffReaderList;
--职员信息Reader staff member IC卡号（卡换了，卡号不一样），员工号，职位，部门,如果职员换岗或离职了，这个问题如何解决记录关联问题
create table geovindu.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_StaffReaderDepartmentFOREIGN KEY(StaffReaderDepartment) REFERENCES geovindu.DepartmentList(DepartmentID),--员工所属部门（外键） ON DELETE SET NULL ON DELETE CASCADEStaffReaderPosition int, CONSTRAINT fky_StaffReaderPositionFOREIGN KEY(StaffReaderPosition) REFERENCES geovindu.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_StaffReaderOperatorIDFOREIGN KEY(StaffReaderOperatorID) REFERENCES geovindu.BookAdministratorList(BookAdminID),--操作人员ID（添加记录的人员）（外键）StaffReaderDatetime TIMESTAMP --
);--序列创建
CREATE SEQUENCE geovindu.StaffReaderList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.StaffReaderList_ID_AUTObefore insert on geovindu.StaffReaderList --表名for each row
declarenextid number;
beginIF :new.StaffReaderID IS NULL or :new.StaffReaderID=0 THEN --ID是列名select geovindu.StaffReaderList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.StaffReaderID:=nextid;end if;
end;--权限类型列表，也是系统操作的窗口功能的详细列表BookAdminPermissTypeList
create table geovindu.BookAdminPermissTypeList
(AdminPermissTypeID INT PRIMARY KEY,AdminPermissParent int null, --父类型AdminPermissTypeName nvarchar2(300) not null, AdminPermissTypeDesc NCLOB null, --权限描述 AdminPermissFormName varchar(100) null --窗口名稱
);--序列创建
CREATE SEQUENCE geovindu.AdminPermissTypeList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.AdminPermissTypeList_ID_AUTObefore insert on geovindu.BookAdminPermissTypeList --表名for each row
declarenextid number;
beginIF :new.AdminPermissTypeID IS NULL or :new.AdminPermissTypeID=0 THEN --ID是列名select geovindu.AdminPermissTypeList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.AdminPermissTypeID:=nextid;end if;
end;--權限公配錶 listview treeview check
create table geovindu.BookAdminPermissionrList
(BookAdminPermissID INT PRIMARY KEY,BookAdminPermissKey int,CONSTRAINT fky_BookAdminPermissFOREIGN KEY(BookAdminPermissKey) REFERENCES geovindu.BookAdministratorList(BookAdminID) ON DELETE CASCADE, --管理员IDBookAdminPermissDesc NCLOB null --权限分配ID
);CREATE SEQUENCE geovindu.AdminPermissionrList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.AdminPermissionrList_ID_AUTObefore insert on geovindu.BookAdminPermissionrList --表名for each row
declarenextid number;
beginIF :new.BookAdminPermissID IS NULL or :new.BookAdminPermissID=0 THEN --ID是列名select geovindu.AdminPermissionrList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.BookAdminPermissID:=nextid;end if;
end;--还有一种方式是角色快速分配权限,先固定角色類型分配權限，再角色設置權限 PermissionList
create table geovindu.PermissionAssignmentList
(PermissionAssignmentID INT PRIMARY KEY,PermissionAssignmentKey int,CONSTRAINT fky_PermissionAssignmentFOREIGN KEY(PermissionAssignmentKey) REFERENCES geovindu.PermissionList(PermissionID), --角色IDPermissionAssignmentDesc NCLOB null --权限分配ID
);CREATE SEQUENCE geovindu.PermissionAssignment_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.PermissionAssignment_ID_AUTObefore insert on geovindu.PermissionAssignmentList --表名for each row
declarenextid number;
beginIF :new.PermissionAssignmentID IS NULL or :new.PermissionAssignmentID=0 THEN --ID是列名select geovindu.PermissionAssignment_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.PermissionAssignmentID:=nextid;end if;
end;--权限管理 AuthorizationManagement
create table geovindu.BookAdministratorList
(BookAdminID INT PRIMARY KEY,BookAdminIC varchar(100) not null, --员工工牌IC号（换了卡，号会改变的）BookAdminNO varchar(20) not null, --员工编号BookAdminName nvarchar2(500) not null, --员工姓名BookAdminEmail varchar(100) null, --电子邮件BookAdminQQ varchar(50) null, --BookAdminSkype varchar(50) null, --BookAdminPassword nvarchar2(100) not null, --密码BookAdminQuestion int, CONSTRAINT fky_AdminQuestionIDFOREIGN KEY(BookAdminQuestion) REFERENCES geovindu.QuestionTypeList(QuestionTypeID), --找迴密碼類型(外鍵）BookAdminAnswer nvarchar2(300) null, --找迴密碼答題BookAdminIs char check (BookAdminIs in ('N','Y')), --是否在职BookAdminPermission int,CONSTRAINT fky_PermissionIDFOREIGN KEY (BookAdminPermission) REFERENCES geovindu.PermissionList(PermissionID), --权限范围(录入人员，盘点人员，申请书报销人员，批准人员，审核人员等）(角色或權限類型外鍵） BookAdminDate TIMESTAMP
);CREATE SEQUENCE geovindu.AdministratorList_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE; --自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.BookAdministratorList_ID_AUTObefore insert on geovindu.BookAdministratorList --表名for each row
declarenextid number;
beginIF :new.BookAdminID IS NULL or :new.BookAdminID=0 THEN --ID是列名select geovindu.AdministratorList_SEQ.Nextval --_SEQ正是刚才创建的into nextidfrom dual;:new.BookAdminID:=nextid;end if;
end;--2、创建主键
ALTER TABLE geovindu.BookAdministratorListADD CONSTRAINT pk_BookAdminQuestion PRIMARY KEY (BookAdminQuestion) USING INDEX ;--3、创建Unique约束
ALTER TABLE geovindu.BookAdministratorListADD CONSTRAINT uk_students_license UNIQUE (state, license_no) USING INDEX ;
--4、创建Check约束
ALTER TABLE geovindu.BookAdministratorList ADD CONSTRAINT ck_students_st_licCHECK ((state IS NULL AND license_no IS NULL) OR(state IS NOT NULL AND license_no is NOT NULL));
--5、创建外键约束
ALTER TABLE geovindu.BookAdministratorListADD CONSTRAINT fk_students_stateFOREIGN KEY (state) REFERENCES state_lookup (state);--开启屏幕输出显示
-- SET SERVEROUTPUT ON;
--显示当前日期与时间
BEGINDBMS_OUTPUT.PUT_LINE('现在的日期时间：');--显示信息不换行DBMS_OUTPUT.PUT('今天是：');--显示信息并换行DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'DAY'));DBMS_OUTPUT.PUT('现在时间是： ');DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
END;
转载于:.html
更多推荐
Oracle11g: simple sql script examples
发布评论