Oracle11g: simple sql script examples

 

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