分类目录归档：Oracle 开发

--create directory 
create directory ULTLOBDIR as 'd:'

--create table
create table blobtest(col1 BLOB);
create table clobtest(col1 cLOB);

--insert BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');
begin
insert into blobtest values (empty_blob())
returning col1 into a_blob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;

--update BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');
begin
update blobtest set col1=empty_blob() where rownum=1
returning col1 into a_blob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;

--insert CLOB
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc');
begin
insert into clobtest values (empty_clob())
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;

--update CLOB
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt');
begin
update clobtest set col1=empty_clob() where rownum=1
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;

--查询是否成功
select dbms_lob.getlength(col1) from blobtest;
select dbms_lob.getlength(col1) from clobtest;

1、procedure

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name
[parameter_lister]
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
--举例
create or replace
procedure dos_fx
is
cursor c1 is 
select get from dos_gj;              
begin
 for c2 in c1 loop
 insert into dos_gj_1(ip,gettime,get)values(REGEXP_SUBSTR( c2.get, '(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])(\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])){3} '),
 REGEXP_SUBSTR( c2.get, '^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})'),c2.get);
 end loop;
 commit;
end;

2、function

CREATE [ OR REPLACE] FINCTION [schema.]function_name
[parameter_list]
RETURN returning_datatype
{AS|IS}
declaration_section
BEGIN
executable_section
[EXCEPTION]
exception_section
END [procedure_name]
--举例
CREATE OR REPLACE function fn_md5(input_string VARCHAR2) return varchar2
IS
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
decrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
sys.dbms_obfuscation_toolkit.MD5(input => raw_input,checksum => decrypted_raw);
return rawtohex(decrypted_raw);
END;

3、package

--header
CREATE [OR REPLACE] PACKAGE package_name
{AS|IS}
public_variable_declarations |
public_type_declarations |
public_exception_declarations |
public_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--body
CREATE [OR REPLACE] PACKAGE BODY package_name
{AS|IS}
private_variable_declarations |
private_type_declarations |
private_exception_declarations |
private_cursor_declarations |
function_declarations |
procedure_specifications
END [package_name]
--举例
--header
create or replace
package pk_t1
as
procedure get_num(getnum in number,aname varchar2);
end pk_t1;
--body
create or replace
package body pk_t1
as
procedure get_num(getnum in number,aname varchar2)
is 
begin
 insert into shell_1 values(getnum,aname);
end;
end pk_t1;

4、trigger

CREATE [OR REPLACE] TRIGGER trigger_name
{before|after|instead of} event
ON {table_or_view_name|DATABASE}
[FOR EACH ROW[WHEN condition]]
trigger_body
--举例
create or replace trigger add_shell
before update 
on shell_1 for each row
declare
begin
 if :OLD.name!=:new.name then
  :new.name:='ggggg';
 end if;
end;

一、简单创建外部表
1、简单模拟外部文件
10,20,30
40,50,60
70,80,90
2、创建外部表用户名和授权

create user test identified by “123” default tablespace test quota unlimited on test;
grant RESOURCE,create session,create any directory to test;

3、创建目录

conn test/"123"
create directory ext as '/sdb/orcl/file';

4、创建外部表

 create table exttable(
　　id number,
     name varchar2(10),
     i number
)
organization external
　(
     type oracle_loader
　　default directory ext
　　access parameters
　　  (
         records delimited by newline
　　    fields terminated by ','
　　  )
     location('ext.dat')
　);

二、使用外部表管理alert文件

create directory alert as 'create directory bdump as '/oracle/u01/app/oracle/admin/db2/bdump';
create table alert_log
(
　text varchar2(400)
)
organization external
　(
     type oracle_loader
　　default directory alert
　　access parameters
　　(
        records delimited by newline
　　)
     location('alert_orcl.log')
　);
--查找到ora错误记录
select * from alert_log where TEXT like 'ORA-%';