欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle11g: simple sql script examples

程序员文章站 2022-04-28 21:37:46
---https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm drop user geovin; drop user geovindu; create user geovindu identified by... ......

 

---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;