Orcale详细教程 2

5.

--创建一个目录
create or replace directory booktext as 'd:\aa'
--请在指定目录中写一文件a.txt
--使用bfile
create table mybook
(bname varchar2(40),
bookfile bfile);
delete mybook

--对bfile类型插入数据
insert into mybook values(
'第一章',bfilename('BOOKTEXT','a.txt'));
select * from mybook;
commit;

--从bfile类型中拿到数据
declare
bf bfile;
text raw(100);--定义缓存
len binary_integer :=100;--定义缓存长度
begin
  select bookfile into bf from mybook;
  dbms_lob.open(bf);--打开文件
  dbms_lob.read(bf,len,1,text);--读取文件到缓存中
  dbms_lob.close(bf);--关闭
  dbms_output.put_line(utl_raw.cast_to_varchar2(text));--将缓存中数据显示
  dbms_output.put_line('len='||to_char(len));
end;

--通过循环显示内容
declare
bf bfile;
text raw(10);
len integer :=10;
flen int;
star int;
begin
  select bookfile into bf from mybook;
  dbms_lob.open(bf);
  star:=1; 
  flen:=dbms_lob.getlength(bf);
  while star<flen
  loop
      len:=10;
      dbms_lob.read(bf,len,star,text);
      dbms_output.put_line(utl_raw.cast_to_varchar2(text));
      star:=star+len;
  end loop;
  dbms_lob.close(bf);
end;

--使用blob类型
create table myfile(
fdesc varchar2(40),
fno int,
f blob);

--插入数据
declare
   bf bfile;
   bl blob;
begin
insert into myfile  values ('haha my test',1,empty_blob())
return f into bl;
bf:=bfilename('BOOKTEXT','a.txt');
dbms_lob.open(bf,dbms_lob.file_readonly);
dbms_lob.loadfromfile(bl,bf,dbms_lob.getlength(bf));
dbms_lob.close(bf);
commit;
end;

select * from myfile;

--从blob类型中读取数据
declare
bl blob;
begin
select f into bl from myfile;
dbms_output.put_line(utl_raw.cast_to_varchar2(bl));
end;

--使用clob类型
create table mytext(
title varchar2(20),
des clob);

--插入数据
insert into mytext values('我的测试','-----------------------------------------
全文的维普有效帐号密码一个，绝对管用（无威望限制），刚测试过
入口：http://dx1.cqvip.com/index.asp
帐号/密码：aydxtsg/aydxtsg
-------------------------------');
commit;
select * from mytext;

--读取clob类型

declare
cl clob;
begin
select des into cl from mytext;
dbms_output.put_line(cl);
end;

--if 语句
declare
n int;
begin
select count(*) into n from  st;
if n>0 then
   delete st;
end if;
end;

select * from st;
rollback;
select * from st;

--case 语句1
declare
sn varchar2(10);
begin
select stname into sn from st where stid=1;
case sn
     when '张三' then  dbms_output.put_line('zhangshan');
     when '李四' then  dbms_output.put_line('lishi');
     when '王五' then  dbms_output.put_line('wangwu');
     else  dbms_output.put_line(sn);
end case;
end;

--case 语句2
declare
n int;
begin
select math+china into n from st where stid=1;
dbms_output.put_line('成绩='||to_char(n));
case
     when n<120 then dbms_output.put_line('不及格');
     when n<150 then dbms_output.put_line('中');
     when n<180 then dbms_output.put_line('良');
     else dbms_output.put_line('优');
end case;
end;

--Loop 语句
declare
n int:=1;
s int:=0;
begin
loop
  s:=s+n;
  if s>100 then
      dbms_output.put_line('n='||to_char(n));
      dbms_output.put_line('s='||to_char(s));
      exit;
  end if;
  n:=n+1;
end loop;
end;

--for 语句
declare
i int;
s int :=0;
begin
for i in 1..100
loop
   s:=s+i;
end loop;
dbms_output.put_line('1+2+..+99+100='||to_char(s));
end;

--goto 语句
declare
i int :=1;
s int:=0;
begin
<<lbl1>>
s:=s+i;
i:=i+2;
if i<100 then
   goto lbl1;
end if;
dbms_output.put_line('1+2+..+99+100='||to_char(s));
end;

--使用execute immediate 调用动态SQL,并可以传参数
declare
n int;
jobid int :=1;
job jobs%rowtype;
sel varchar2(100);
begin
select count(*) into n from user_all_tables where table_name='HAO';
if n>0 then
  execute immediate 'DROP TABLE HAO';
end if;
execute immediate 'CREATE TABLE HAO AS SELECT * FROM JOBS';
sel :='select * from hao where job_id = :id';
execute immediate sel into job using jobid;
dbms_output.put_line(to_char(job.job_id)||'   '||job.job_desc);
commit;
end;

select * from hao;

--exception 异常处理
declare
n int:=0;
begin
n:=n/n;
exception
when zero_divide then
     dbms_output.put_line('除数为0了');
end;

--自定义错误
declare
myerr exception;
n int:=10;
begin
if n<20 then
    raise myerr;
else
    dbms_output.put_line('n<=20');
end if;
exception
   when myerr then
        dbms_output.put_line('数据大于20了');
end;

--raise_application_error 引发应用程序错误
declare
begin
raise_application_error(-20003,'我出错了');
end;

 

--timestamp with time zone数据类型
declare
test_tz timestamp with time zone;
begin
test_tz:=to_timestamp_tz('03-09-23 09:02:01','yyyy-mm-dd hh:mi:ss');
dbms_output.put_line(test_tz);
end;

--clob使用
 create table vendor_master (vencode varchar2(5),
 msg clob)

insert into vendor_master values('accp','bei da jade bird computer educaton  professional');
/

---又一次使用

create table lobs (name varchar2(15),addr varchar2(15),phone number,pers_det clob,photo blob,album bfile)

insert into lobs values('sachin','5 veena gardens',3533859,'famous cricketer. has ok',null,null)

declare
clob_var clob;
start_ind integer;
end_ind integer;
output_var varchar2(110);
begin
end_ind:=110;
start_ind:=1;
select pers_det into clob_var from lobs
where phone=3533859;
dbms_lob.read(clob_var,end_ind,start_ind,output_var);
dbms_output.put_line(output_var);
end;

--oopType
create or replace type ADDRESS_TY as object(
                                          Street_no number(10),
                                          Street_name varchar2(20),
                                          City varchar2(20),
                                          State varchar2(20))
                                         
declare
a address_ty:=address_ty(10,'first st','mds','tn');
begin
dbms_output.put_line('Street No:' || a.street_no);
dbms_output.put_line('Street Name' || a.street_name);
dbms_output.put_line('City is:' || a.city);
dbms_output.put_line('Provencie is:' || a.state);
end;

----if

 declare
 newSal emp.sal % type;
 begin
 select sal into newSal from emp
 where ename='JAMES';
 if newSal>1000 then
 update emp
 set comm=8000
 where ename='JAMES';
 else
 update emp
 set comm=4000
 where ename='JAMES';
 end if;
 end;
 
 -----case end
 
declare
sumSal int:=100;
begin
case sumSal
when 100 then dbms_output.put_line('100');
when 200 then dbms_output.put_line('200');
when 300 then dbms_output.put_line('300');
else dbms_output.put_line('unkown');
end case;
end;

--case return
SELECT AVG(CASE
  WHEN e.sal > 2000 THEN e.sal
  when e.sal>1000 then 1000
  when e.sal>500 then 500   
     ELSE 0
    END) "Average Salary"
from emp e

---for
declare
i int:=1;
begin
for i in reverse  1..10
loop
dbms_output.put_line(to_char(i));
end loop;
end;

---loop-endloop

declare
a number:=100;
begin
loop
a:=a+25;
exit when a=250;
end loop;
dbms_output.put_line(to_char(a));
end;

----while
declare
a number:=100;
begin
while a<250
loop
a:=a+25;
end loop;
dbms_output.put_line(to_char(a));
end;

---%type

declare
en emp.ename%type;
begin
select ename into en from emp where ename='FORD';
dbms_output.put_line(en);
end;

--%rowtype
declare
cursor my_cur is select * from emp;
myrs emp%rowtype;
begin
open my_cur;
loop fetch my_cur into myrs;
dbms_output.put_line(myrs.ename);
exit when my_cur%notfound;
end loop;
dbms_output.put_line('The count of rs are:' || my_cur%rowcount);
close my_cur;
end;
---exception

declare
sal emp.sal%type;
begin
select sal into sal from emp where ename='zyl';
exception
when no_data_found then
dbms_output.put_line('No RecordSet find!');
end;

------
declare
sal emp.sal%type;
myexp exception;
begin
select sal into sal from emp where ename='JAMES';
if sal<5000 then
 raise myexp;
end if;
exception
when no_data_found then
dbms_output.put_line('No RecordSet find!');
when myexp then
dbms_output.put_line('Sal is to less!');
end;

------raise exception

DECLARE
 null_salary EXCEPTION;
 PRAGMA EXCEPTION_INIT(null_salary, -20101);
BEGIN

 DECLARE
  curr_comm NUMBER;
 BEGIN
  SELECT comm INTO curr_comm FROM emp WHERE empno = &empno;
  
  IF curr_comm IS NULL THEN
  
   raise_application_error(-20101, 'Salary is missing');
  ELSE
   DBMS_OUTPUT.PUT_LINE('有津贴');
  END IF;
 END;
 
EXCEPTION
 WHEN null_salary THEN
  DBMS_OUTPUT.PUT_LINE('津贴未知');
END;

-------激发系统异常
create table student
(
stuid number(4),
stuname varchar2(20)
)
/
alter table student
add constraint pk_stuid
primary key (stuid)

/
insert into student values (1,'stephen')
/
insert into student values (2,'tellixu')
/
declare
dup_primary_key exception;
pragma exception_init(dup_primary_key,-1);
begin
 insert into student values (1,'stephen');
exception
 when dup_primary_key then
    dbms_output.put_line('主键重复');
end;
/

----------------------------------------------------------------------------------------------------------------------------------------------------------------

6.

--隐式游标的使用
--%NotFound %RowCount的使用
begin
update jobs set job_desc=job_desc where 1=2;
   if sql%notfound then
      dbms_output.put_line('Not Found');
   else
      dbms_output.put_line('Found '||
      to_char(sql%rowcount)||' rows!!');
   end if;
end;

--隐式游标的使用
--%Found  %RowCount的使用
begin
update jobs set job_desc=job_desc;
   if sql%found then
      dbms_output.put_line('Found '||
      to_char(sql%rowcount)||' rows!!');
   else
      dbms_output.put_line('Not Found');
   end if;
end;

--显示游标的使用
declare
job jobs%rowtype;--定义数据类型
cursor cur is
select * from jobs;--定义游标
begin
open cur;--打开游标
loop
   fetch cur into job;--提取数据
   exit when cur%notfound;
   dbms_output.put_line(to_char(job.job_id)||'   '||
   job.job_desc);
end loop;
dbms_output.put_line('Total '||
   to_char(cur%rowcount)||' Rows!!!');
close cur;--关闭游标
end;

--使用显式游标进行更新
declare
job jobs%rowtype;
cursor cur is
select * from jobs where job_id<4 for update;
begin
open cur;
loop
    fetch cur into job;
    exit when cur%notfound;
    if job.min_lvl<100 then
        update jobs set min_lvl=job.min_lvl+1 where current of cur;       
    end if;       
end loop;
close cur;
commit;
end;

select * from jobs;

--显式游标带参数
declare
job jobs%rowtype;
cursor cur(minid int,maxid int ) is
select * from jobs  where job_id>=minid and job_id<=maxid;
begin
open cur(5,10);
loop
   fetch cur into job;
   exit when cur%notfound;
   dbms_output.put_line(to_char(job.job_id)||
   '   '||job.job_desc);
end loop;
dbms_output.put_line('Total '||
to_char(cur%rowcount)||' Rows');
close cur;
end;

--循环游标
declare
cursor curjobs is
select * from jobs;
begin
for cur in curjobs
loop
   dbms_output.put_line(to_char(cur.job_id)||
   '   '||cur.job_desc);
end loop;
end;

--循环游标带参数
declare
cursor curjobs(minid int,maxid int) is
select * from jobs where job_id>=minid and job_id<=maxid;
begin
for cur in curjobs(5,10)
loop
   dbms_output.put_line(to_char(cur.job_id)||
   '   '||cur.job_desc);
end loop;
end;

select * from titles where rownum<5
--Ref 游标

declare
TYPE myrefcur is ref cursor;
cur myrefcur;
job jobs%rowtype;
title titles%rowtype;
strsql varchar2(50);
begin
strsql:='select * from jobs where rownum<5';
dbms_output.put_line('=====Jobs=====');
open cur for strsql;
loop
   fetch cur into job;
   exit when cur%notfound;
   dbms_output.put_line(to_char(job.job_id)||
   '   '||job.job_desc);
end loop;
close cur;
strsql:='select * from titles where rownum<5';
dbms_output.put_line('=====Titles=====');
open cur for strsql;
loop
    fetch cur into title;
    exit when cur%notfound;
    dbms_output.put_line(title.title_id||'  '
    ||title.title);
end loop;
close cur;
end;

 

Declare
  I Integer;
Begin
  I := 4**2; -- 指数运算
  dbms_output.put_line(I);
End;
------隐式游标
begin
delete from emp where ename='JAMES';
if sql%notfound then
dbms_output.put_line('未打到值');
else
dbms_output.put_line('找到并删除！');
end if;
end;
---------
declare
    mysal emp.sal%type;
 begin
    select sal into mysal from emp where ename='JAMES';
    if sql%rowcount=1 then
            dbms_output.put_line('工资是：' || mysal);
    else
            dbms_output.put_line('表中有多条符合条件记录！');
    end if;
 exception
    when no_data_found then
            dbms_output.put_line('表中没有该条记录！');
 end;
 
 --------显示游标
 --递增更新员工工资
declare
addSal int:=100;
cursor myemp is select ename from emp;
myname emp.ename%type;
begin
open myemp;
loop
fetch myemp into myname;
update emp set sal=sal+addSal where ename=myname;
addSal:=addSal+100;
exit when myemp%notfound;
end loop;
dbms_output.put_line('工资已经递增更新！');
close myemp;
end;
------------------
declare
cursor test is select * from emp where ename='zyl';
myrs emp%rowtype;
begin
 open test;
 loop
  fetch test into myrs;
  exit when test%notfound;
 end loop;
 dbms_output.put_line('表中共有：'||test%rowcount);
 close test;
end;
------for循环游标
declare  
   cursor mycur is select deptno from emp;      
   ind number:=0;
begin
   for rsc in mycur
   loop    
     ind:=ind+1;
   end loop;
   dbms_output.put_line('总共有:'||ind);
end;
------
DECLARE
 CURSOR emp_cur IS
  SELECT empno, ename, job, sal FROM emp;
BEGIN
 FOR mrec IN emp_cur LOOP

  INSERT INTO temp
   VALUES( mrec.empno,mrec.ename,
    mrec.job, mrec.sal);
 END LOOP;
END;
----------Ref游标

---无返回
declare
 type r1_cur is ref cursor;
 var1 r1_cur;
 ono varchar2(5);
 no number;
 qord number;
begin
  no:='&no';
  if no=1 then
 
   open var1 for select deptno from emp where ename='zyl';  
  fetch var1 into ono;
  dbms_output.put_line('部门是'||ono);
  close var1;
 else  
  open var1 for select sal from emp where ename='zyl';
  
  loop
   fetch var1 into qord;
   exit when var1%notfound;
   dbms_output.put_line('工资是：'||qord);
  end loop;   
  close var1;
 end if;
end;

--------有返回
declare
type ordertype is record(
deptno number(2),
dname varchar2(14),
loc varchar2(13));
type ordercur is ref cursor return dept%rowtype;
order_cv ordercur;
order_rec ordertype;
begin
open order_cv for
select deptno,dname,loc
from dept
where deptno=30;
loop
fetch order_cv into order_rec;
exit when order_cv%notfound;
dbms_output.put_line('这些值是' || order_rec.deptno ||' '||
order_rec.dname||' '||
order_rec.loc);
end loop;
close order_cv;
end;

 

-----------------------------------------------------------------------------------------------------------------------------------------------

7.

 --过程
 create or replace procedure UpdateSal(per number,var_empno number)
 is
    en emp.ename%type;
    sa emp.sal%type;
    old emp.sal%type;
 begin
    --保存原来的工资
    select sal into old
    from emp
    where empno=var_empno;
   
    --根据参数更新工资
    update emp
    set sal=sal+sal*per
    where empno=var_empno;
   
    --取得更新后的工资
    select ename,sal into en,sa
    from emp
    where empno=var_empno;
   
    --输出工资
    dbms_output.put_line(en||'原来的工资是：'||old||',更新后的工资是: '||sa);
 exception
    when no_data_found then
            dbms_output.put_line('查无此人！');
 end;
 
 --执行
 execute updatesal(0.3,7369);
 
 --函数
 create or replace function f1(a out int,b int) return int
as
begin
a:=a+1;
return b+1;
end f1;
---执行
 declare
 a int:=10;
 c int;
 begin
 c:=f1(a,10);
 dbms_output.put_line(c);
 dbms_output.put_line('a>>'||a);
 end ;
 
 ------------程序包信息
 select object_name,object_type
from user_objects
where object_type in('PROCEDURE','FUNCTION','PACKAGE')

 

--创建程序包说明
 create or replace package testpak
 is
 n int:=10;
 procedure mysub(n int);
 function myfunc(n int) return varchar2;
 end testpak;

--创建程序包主体
 
 create or replace package body testpak
 as
 procedure mysub(n int)
 is
 begin
 dbms_output.put_line(n*n);
 end mysub;

 function myfunc(n int) return varchar2
 is
 begin
 return n+1;
 end myfunc;
 end testpak;

--调用包中的过程和方法
declare
begin
testpak.mysub(10);
end;

--调用函数
 declare
 c varchar2(20);
 begin
 c:=testpak.myfunc(2);
 dbms_output.put_line(c);
 end;

----基于游标

create or replace package cur_pack is
cursor dept_cur return dept%rowtype;
procedure showNo(var_deptno int);
end cur_pack;
/

create or replace package body cur_pack
is
cursor dept_cur return dept%rowtype is select * from dept;
procedure showNo(var_deptno int) is
de_rec dept%rowtype;
begin
open dept_cur;
loop
fetch dept_cur into de_rec;
exit when dept_cur%notfound;
dbms_output.put_line(de_rec.dname);
end loop;
end showNo;
end cur_pack;
/

exec cur_pack.showNo(123);
/

---------重载
create or replace package mypack
 as
 procedure show(n int);
 procedure show(n varchar2);
 end mypack;

create or replace package body mypack
as
procedure show(n int) is
begin
dbms_output.put_line('INt>>'||n);
end show;
procedure show(n varchar2)is
begin
dbms_output.put_line('Float>>'||n);
end show;
end mypack;

 execute mypack.show(10);
execute mypack.show('abc');

 

--使用动态语句创建用户并且分配权限
CREATE OR REPLACE PROCEDURE createUser(className VARCHAR2,personNum NUMBER)
IS
 sqlStatement VARCHAR2(500);
 userName VARCHAR2(20); 
BEGIN
 FOR i in 1..personNum LOOP
  --创建用户
  IF i<10 THEN
   userName:=className || '0'||i;
  ELSE
   userName:=className || i;
  END IF;
  
  sqlStatement:='CREATE USER ' || userName || ' IDENTIFIED BY ' || userName;
  EXECUTE IMMEDIATE sqlStatement;    
  --分配权限
  sqlStatement:='GRANT CONNECT TO ' || userName;
  EXECUTE IMMEDIATE sqlStatement; 
  sqlStatement:='GRANT RESOURCE TO ' || userName;
  EXECUTE IMMEDIATE sqlStatement; 
 END LOOP;
END;
/
--使用上述存储过程
BEGIN
 createUser('&className',&personNum);
END;

---------------------------------------------------------------------------------------------------------------

8.

--创建表
create table myemp
(id int,age int);

--基于SCOTT示例模式的EMPLOYEES表建立的触发器
create or replace trigger myTri1
before insert or update of age
on myemp
referencing old as old_value
            new as new_value
for each row
when (new_value.id>10)
begin
dbms_output.put_line('fdfdfdfd');
end;

--测试触发器(更新操作)
update myemp
set age=34
where id=100;

update myemp
set id=200

--查看更新后的列
select * from myemp where id=100;

--测试触发器（插入操作）
insert into myemp values(100,30);
select * from myemp where id=100;

 

-----触发器
create table foo(n int);
 
 create or replace trigger ntr
 before insert or update or delete
 on foo
 referencing new as new_value
  for each row
 begin
   dbms_output.put_line(:new_value.n);
   --dbms_output.put_line(:old.n);
   null;
 end;

insert into scott.foo values(15);

update scott.foo
set n=55;

select * from foo

-------
drop trigger biud_foo;

create table foo(n number);

create or replace trigger biud_foo
before insert or update or delete
on foo
begin
   if user not in('SCOTT') then
      raise_application_error(-20008,'您无权修改该表记录!');
   end if;
end;

grant all on foo to tellixu

insert into scott.foo values(10);
insert into foo values(21);

select * from foo;

----------------------
-- 数据库级触发器
CREATE TABLE system.session_info (
  username   VARCHAR2(30),
  logontime  DATE,
  session_id VARCHAR2(30),
  ip_addr    VARCHAR2(30),
  hostname   VARCHAR2(30),
  auth_type  VARCHAR2(30)
);

CREATE OR REPLACE TRIGGER trg_session_info
BEFORE LOGOFF
ON DATABASE
DECLARE
  session_id VARCHAR2(30);
  ip_addr    VARCHAR2(30);
  hostname   VARCHAR2(30);
  auth_type  VARCHAR2(30);
  logontime  date;
BEGIN
  SELECT sys_context ('USERENV', 'SESSIONID')  -- 会话编号
  INTO session_id FROM dual;
  --  用户登录的客户端IP地址  
  SELECT sys_context ('USERENV', 'IP_ADDRESS')
  INTO ip_addr  FROM dual;
  --  用户登录的客户端主机名   
  SELECT sys_context ('USERENV', 'HOST')
  INTO hostname FROM dual;
  --  登录认证方式，数据库认证或外部认证
  SELECT sys_context ('USERENV', 'AUTHENTICATION_TYPE')
  INTO auth_type FROM dual;
  INSERT INTO system.session_info
  VALUES (user, sysdate, session_id,
          ip_addr, hostname,auth_type);
END;
/

SELECT * FROM system.session_info;

SET SERVEROUTPUT ON
CREATE OR REPLACE trigger trgUpdtEmployee
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF(:new.salary > 40000) THEN
    RAISE_APPLICATION_ERROR(-20001,'职员工资不能超过 40000');
  END IF;
END;
/

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE demo (salary IN NUMBER) AS
   cursor_name    INTEGER;
   rows_processed INTEGER;
BEGIN
  cursor_name := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE (cursor_name, 'DELETE FROM  salary_records
  WHERE empsal > :temp_sal', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (cursor_name, ':temp_sal', salary);
  rows_processed := DBMS_SQL.EXECUTE(cursor_name);
  DBMS_SQL.CLOSE_CURSOR (cursor_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR (cursor_name);
END;
/

----------------

--管理触发器
--禁用个别
alter trigger order_tri disable;

--启用
alter trigger order_tri enable;

--禁用一张表上的所有触发器
alter table emp disable all triggers;

--启用所有的
alter table emp enable all triggers;

--查看所有信息
select * from user_triggers;

---dbms_output包
declare
  buf varchar2(255);
  st integer;
begin
     --dbms_output.enable(100000);--缓冲区大小
     --dbms_output.put_line('welcome');
     dbms_output.put('to');
     dbms_output.put('xf');
     --dbms_output.new_line;
     --dbms_output.put_line('center');
     --dbms_output.get_line(buf,st);
     --dbms_output.put_line(buf);
     --dbms_output.put_line(st);
end;

-------log test
---system用户
--建立文
create or replace directory
wrox_dir as 'c:\temp';

--授权
grant read,write on directory wrox_dir to public;

------bfile

--建立文
create or replace directory
wrox_dir as 'c:\temp';

--授权
grant read,write on directory wrox_dir to public;

--建表
create table bfile_table(
 name varchar2(255),
 the_file bfile);

--插入文件
insert into bfile_table values('testdoc',bfilename('WROX_DIR','sss.BMP'));
commit;
--确认文件是否保存成功
declare
 l_bfile bfile;
 l_dir_alias varchar2(2000);
 l_filename varchar2(2000);
begin
 select the_file into l_bfile from bfile_table where name='testdoc';
 dbms_lob.fileopen(l_bfile,dbms_lob.file_readonly);
 
 if dbms_lob.fileexists(l_bfile)=1 then
  dbms_output.put_line('保存正确!');
 else
  dbms_output.put_line('保存失败!');
 end if;

 if dbms_lob.fileisopen(l_bfile)=1 then
  dbms_lob.fileclose(l_bfile);
 end if;
end;

 

------------
create table lobs (name varchar2(15),addr varchar2(15),phone number,pers_det clob,photo blob,album bfile)

insert into lobs values('sachin','5 veena gardens',3533859,'famous cricketer. has ok',null,null)

declare
clob_var clob;
start_ind integer;
end_ind integer;
output_var varchar2(110);
begin
end_ind:=110;
start_ind:=1 ;
select pers_det into clob_var from lobs
where phone=3533859;
dbms_lob.read(clob_var,end_ind,start_ind,output_var);
dbms_output.put_line(output_var);
end;

 

=-------------------------------------------------------------------------------------------------------------------------------------------------

9.

参考./chm_help/《EXP&IMP.doc》

修改数据库为归档或不归档命令

1>以sysdba身份登录
2>关闭服务  shutdown immediate
3>开启服务到mount状态
  start mount
4>修改数据库为归档或不归档
  alter database archivelog
  alter database noarchivelog
  可以用archive log list来查看
5>修改数据库为开启状态
  alter database open

--------------------------------------------------------------------------------------------------------
 

select * from user_types;

create table address of addr(contry primary key);

select * from address;

insert into address values (addr('aa','vb','233'));

drop table address

create type addrs as array(5) of addr;

create table addrss (ads addrs);

insert into addrss values (addrs(
addr('aa1','aa','aa'),
addr('aa2','aa','aa'),
addr('aa3','aa','aa'),
addr('aa4','aa','aa'),
addr('aa5','aa','aa')
)
)

select * from addrss;

declare
ad addrs;
begin
select ads into ad from addrss;
dbms_output.put_line('count='||to_char(ad.count()));

end;

drop table addrss;
drop type addrs;