Oracle PL/SQL Tutorial/PL SQL Data Types/BLOB

Материал из SQL эксперт
Перейти к: навигация, поиск

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>