18.txt

Oracle database 10g基础教程（第二版） 源码

create table proposal(
proposalID number(10),
recipientName varchar2(20),
proposalName varchar2(20),
description varchar2(200),
proposalText clob,
budget blob,
cover bfile,
constraint proposal_pk primary key(proposalID)
);


 

create tablespace proposals
datafile 'D:/proposals.dbf' size 10M autoextend on;
create tablespace proposals_lobs
datafile 'D:/proposals_lob.dbf' size 10M autoextend on;

create table proposal(
proposalID number(10),
recipientName varchar2(20),
proposalName varchar2(20),
description varchar2(200),
proposalText clob,
budget blob,
cover bfile,
constraint proposal_pk primary key(proposalID)
)
tablespace proposals
lob (proposalText, budget) store as
  (tablespace proposals_lobs 
     storage(initial 100k next 100k pctincrease 0)
     chunk 16k pctversion 10 nocache logging);





insert into proposal(
proposalID, recipientName, proposalName, description, 
proposalText, budget, cover)
values(1, '赵总', '关于技术培训的建议', null,
'当前，我们公司正面临着发展的重大机遇，开展数据库技术培
训是推动公司发展的重要措施。', 
empty_blob(), 
null);



create directory proposal_dir 
as
'D:/';

insert into proposal(
proposalID, recipientName, proposalName, description, 
proposalText, budget, cover)
values(2, '钱处长', '关于调整机构建议', null,
empty_clob(), empty_blob(), 
bfilename('proposal_dir', 'org02.doc'));








insert into proposal(
proposalID, recipientName, proposalName, description, 
proposalText, budget, cover)
select 3, '孙部长', '关于房屋装修建议', null,
       proposalText, budget, cover
from proposal
where proposalID = 1;



update proposal
set proposalText = '房屋装修是当前行政管理的主要工作之一。',
    cover = bfilename('proposal_dir', 'office09.doc')
where proposalID = 3;




select proposalText, substr(proposalText, 1, 8)
from proposal
where proposalID = 1;







select proposalText
from proposal
where proposalID = 1;


set serveroutput on
declare 
  locator_var clob;
  amount_var integer;
  offset_var integer;
  output_var varchar2(200);
begin
  amount_var := 10;
  offset_var := 1;
  select proposalText into locator_var
  from proposal
  where proposalID = 1;
  dbms_lob.read(locator_var, amount_var, offset_var, output_var);
  dbms_output.put_line('建议开始的10个字：'||output_var);
end;
/















empty_clob(), empty_blob(), 
bfilename('proposal_dir', 'org02.doc'));





drop table proposal;


create tablespace proposals
datafile 'D:/proposals.dbf' size 20M autoextend on;


create table proposal(
proposalID number(10),
recipientName varchar2(20),
proposalName varchar2(20),
description varchar2(200),
proposalText clob,
budget blob,
cover bfile,
constraint proposal_pk primary key(proposalID)
) tablespace proposals;





drop tablespace proposals;