---https://docs.oracle.com/cd/b19306_01/server.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;
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;
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_auto
before insert on geovindu.bookkindlist --bookkindlist 是表名
for each row
declare
nextid number;
begin
if :new.bookkindid is null or :new.bookkindid=0 then --bookkindid是列名
select geovindu.bookkindlist_seq.nextval --bookkindlist_seq正是刚才创建的
into nextid
from 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) r
from geovindu.bookkindlist
where 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
) a
where 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 then
begin
insert into geovindu.bookkindlist(bookkindname,bookkindparent) values(temvar,0);
dbms_output.put_line('insert'||namevar);
end;
else
begin
select 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);
else
dbms_output.put_line('value'||namevar);
end if;
end;
--书藉位置place目录
drop table geovindu.bookplacelist;
create table geovindu.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 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掉了或者其它情况将会导致序列不连续，也可以设置为---------nocache
select 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_auto
before insert on geovindu.bookplacelist --bookplacelist 是表名
for each row
declare
nextid number;
begin
if :new.bookplaceid is null or :new.bookplaceid=0 then --bookplaceid是列名
select geovindu.bookplacelist_seq.nextval --bookplacelist_seq正是刚才创建的
into nextid
from 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, --integerint
bookseriesname 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_auto
before insert on geovindu.bookserieslist --表名
for each row
declare
nextid number;
begin
if :new.bookseriesid is null or :new.bookseriesid=0 then --id是列名
select geovindu.bookserieslist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.departmentlist --表名
for each row
declare
nextid number;
begin
if :new.departmentid is null or :new.departmentid=0 then --id是列名
select geovindu.departmentlist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.languagelist --表名
for each row
declare
nextid number;
begin
if :new.languageid is null or :new.languageid=0 then --id是列名
select geovindu.languagelist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.presslist --表名
for each row
declare
nextid number;
begin
if :new.pressid is null or :new.pressid=0 then --id是列名
select geovindu.presslist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.authorlist --表名
for each row
declare
nextid number;
begin
if :new.authorid is null or :new.authorid=0 then --id是列名
select geovindu.authorlist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.bookstatuslist --表名
for each row
declare
nextid number;
begin
if :new.bookstatusid is null or :new.bookstatusid=0 then --id是列名
select geovindu.bookstatuslist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.lendstatuslist --表名
for each row
declare
nextid number;
begin
if :new.lendstatusid is null or :new.lendstatusid=0 then --id是列名
select geovindu.lendstatuslist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.dielectriclist --表名
for each row
declare
nextid number;
begin
if :new.dielectricid is null or :new.dielectricid=0 then --id是列名
select geovindu.dielectriclist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.permissionlist --表名
for each row
declare
nextid number;
begin
if :new.permissionid is null or :new.permissionid=0 then --id是列名
select geovindu.permissionlist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.permissionmenu --表名
for each row
declare
nextid number;
begin
if :new.permissionmenuid is null or :new.permissionmenuid=0 then --id是列名
select geovindu.permissionmenu_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.questiontypelist --表名
for each row
declare
nextid number;
begin
if :new.questiontypeid is null or :new.questiontypeid=0 then --id是列名
select geovindu.questiontypelist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_staffreaderdepartment
foreign key(staffreaderdepartment) references geovindu.departmentlist(departmentid),--员工所属部门（外键） on delete set null on delete cascade
staffreaderposition int,
constraint fky_staffreaderposition
foreign 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_staffreaderoperatorid
foreign 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_auto
before insert on geovindu.staffreaderlist --表名
for each row
declare
nextid number;
begin
if :new.staffreaderid is null or :new.staffreaderid=0 then --id是列名
select geovindu.staffreaderlist_seq.nextval --_seq正是刚才创建的
into nextid
from 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_auto
before insert on geovindu.bookadminpermisstypelist --表名
for each row
declare
nextid number;
begin
if :new.adminpermisstypeid is null or :new.adminpermisstypeid=0 then --id是列名
select geovindu.adminpermisstypelist_seq.nextval --_seq正是刚才创建的
into nextid
from dual;
:new.adminpermisstypeid:=nextid;
end if;
end;
--權限公配錶 listview treeview check
create table geovindu.bookadminpermissionrlist
(
bookadminpermissid int primary key,
bookadminpermisskey int,
constraint fky_bookadminpermiss
foreign key(bookadminpermisskey) references geovindu.bookadministratorlist(bookadminid) on delete cascade, --管理员id
bookadminpermissdesc nclob null --权限分配id
);
create sequence geovindu.adminpermissionrlist_seq
increment by 1
start with 1
nomaxvalue
nocycle
nocache;
--自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.adminpermissionrlist_id_auto
before insert on geovindu.bookadminpermissionrlist --表名
for each row
declare
nextid number;
begin
if :new.bookadminpermissid is null or :new.bookadminpermissid=0 then --id是列名
select geovindu.adminpermissionrlist_seq.nextval --_seq正是刚才创建的
into nextid
from dual;
:new.bookadminpermissid:=nextid;
end if;
end;
--还有一种方式是角色快速分配权限,先固定角色類型分配權限，再角色設置權限 permissionlist
create table geovindu.permissionassignmentlist
(
permissionassignmentid int primary key,
permissionassignmentkey int,
constraint fky_permissionassignment
foreign key(permissionassignmentkey) references geovindu.permissionlist(permissionid), --角色id
permissionassignmentdesc nclob null --权限分配id
);
create sequence geovindu.permissionassignment_seq
increment by 1
start with 1
nomaxvalue
nocycle
nocache;
--自增长触发器 (名称不能超过三十个字符)
create or replace trigger geovindu.permissionassignment_id_auto
before insert on geovindu.permissionassignmentlist --表名
for each row
declare
nextid number;
begin
if :new.permissionassignmentid is null or :new.permissionassignmentid=0 then --id是列名
select geovindu.permissionassignment_seq.nextval --_seq正是刚才创建的
into nextid
from 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_adminquestionid
foreign key(bookadminquestion) references geovindu.questiontypelist(questiontypeid), --找迴密碼類型(外鍵）
bookadminanswer nvarchar2(300) null, --找迴密碼答題
bookadminis char check (bookadminis in ('n','y')), --是否在职
bookadminpermission int,
constraint fky_permissionid
foreign 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_auto
before insert on geovindu.bookadministratorlist --表名
for each row
declare
nextid number;
begin
if :new.bookadminid is null or :new.bookadminid=0 then --id是列名
select geovindu.administratorlist_seq.nextval --_seq正是刚才创建的
into nextid
from dual;
:new.bookadminid:=nextid;
end if;
end;
--2、创建主键
alter table geovindu.bookadministratorlist
add constraint pk_bookadminquestion
primary key (bookadminquestion) using index ;
--3、创建unique约束
alter table geovindu.bookadministratorlist
add constraint uk_students_license
unique (state, license_no) using index ;
--4、创建check约束
alter table geovindu.bookadministratorlist add constraint ck_students_st_lic
check ((state is null and license_no is null) or
(state is not null and license_no is not null));
--5、创建外键约束
alter table geovindu.bookadministratorlist
add constraint fk_students_state
foreign key (state) references state_lookup (state);
--开启屏幕输出显示
-- set serveroutput on;
--显示当前日期与时间
begin
dbms_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;
