Oracle PL/SQL Tutorial/PL SQL Data Types/BLOB
Содержание
Blob locator
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> DECLARE
2 blobValue BLOB; 3 BEGIN 4 INSERT INTO myBlob VALUES (3,HEXTORAW("AAAAAAAAAAAAAAAAAAAA")); 5 SELECT blob_data 6 INTO blobValue 7 FROM myBlob 8 WHERE id =3; 9 10 END; 11 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myBlob; Table dropped. SQL> SQL> SQL></source>
Deal with blob data
<source lang="sql">
SQL> SQL> create table demo
2 ( id int primary key, 3 theBlob blob 4 ) 5 /
Table created. SQL> SQL> create or replace directory my_files as "c:\temp\"; Directory created. SQL> SQL> create sequence blob_seq; Sequence created. SQL> SQL> create or replace function clean( p_raw in blob,
2 p_from_byte in number default 1, 3 p_for_bytes in number default 4000 ) 4 return varchar2 as 5 l_tmp varchar2(8192) default utl_raw.cast_to_varchar2( dbms_lob.substr(p_raw,p_for_bytes,p_from_byte)); 6 l_char char(1); 7 l_return varchar2(16384); 8 l_whitespace varchar2(25) default chr(13) || " " || chr(9); 9 l_ws_char varchar2(50) default "rnt"; 10 11 begin 12 for i in 1 .. length(l_tmp) 13 loop 14 l_char := substr( l_tmp, i, 1 ); 15 if ( ascii(l_char) between 32 and 127 ) 16 then 17 l_return := l_return || l_char; 18 if ( l_char = "\" ) then l_return := l_return || "\"; 19 end if; 20 elsif ( instr( l_whitespace, l_char ) > 0 ) 21 then 22 l_return := l_return || "\" || substr( l_ws_char, instr(l_whitespace,l_char), 1 ); 23 else 24 l_return := l_return || "."; 25 end if; 26 end loop; 27 return substr(l_return,1,4000); 28 end; 29 /
Function created. SQL> SQL> select id,dbms_lob.getlength(theBlob) len,clean(theBlob,30,40) piece,
2 dbms_lob.substr(theBlob,40,30) raw_data 3 from demo 4 /
no rows selected SQL> drop table demo; Table dropped. SQL> drop sequence blob_seq; Sequence dropped.</source>
Insert into returning to blob type variable
<source lang="sql">
SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> SQL> SQL> DECLARE
2 blobValue BLOB; 3 BEGIN 4 INSERT INTO myBlob VALUES (4,HEXTORAW("AAAAAAAAAAAAAAAAAAAA")) 5 RETURNING blob_data INTO blobValue; 6 7 END; 8 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myBlob; Table dropped. SQL></source>
Stored procedure to display the contents of the col_blob and col_clob columns
<source lang="sql">
SQL> SQL> create table myTable
2 (key NUMBER PRIMARY KEY 3 ,col_blob BLOB 4 ,col_clob CLOB);
Table created. SQL> SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(1, HEXTORAW("101F"), "ZYXW"); 1 row created. SQL> SQL> INSERT INTO myTable(key, col_blob, col_clob) VALUES(2, HEXTORAW("111101F"), "ABCD"); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE sp_inmyTable
2 IS 3 v_key1 myTable.key%TYPE; 4 blobValue1 myTable.col_blob%TYPE; 5 clobValue1 myTable.col_clob%TYPE; 6 v_key2 myTable.key%TYPE; 7 blobValue2 myTable.col_blob%TYPE; 8 clobValue2 myTable.col_clob%TYPE; 9 v_buffer VARCHAR2(1000); 10 v_offset NUMBER; 11 v_amount NUMBER; 12 BEGIN 13 SELECT key, col_blob, col_clob 14 INTO v_key1, blobValue1, clobValue1 15 FROM myTable 16 WHERE key = 1; 17 18 v_amount := 80; 19 v_offset := 1; 20 DBMS_LOB.READ (clobValue1, v_amount, v_offset, v_buffer); 21 DBMS_OUTPUT.PUT_LINE ("Clob Contents => " || v_buffer); 22 23 v_amount := 80; 24 v_offset := 1; 25 DBMS_LOB.READ (blobValue1, v_amount, v_offset, v_buffer); 26 DBMS_OUTPUT.PUT_LINE ("Blob Contents => " || v_buffer); 27 END; 28 /
Procedure created. SQL> SQL> exec sp_inmyTable Clob Contents => ZYXW Blob Contents => 101F PL/SQL procedure successfully completed. SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>