Oracle PL/SQL Tutorial/Large Objects/CLOB — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:08, 26 мая 2010
Содержание
- 1 Adding Content to a CLOB
- 2 close Clob data pointer
- 3 Compare date value after to_char() and trim()
- 4 Convert string to clob
- 5 Copy clob data
- 6 Creating Tables Containing CLOB Objects
- 7 Initialize CLOB column
- 8 Insert into clob column
- 9 Loading data to the CLOB by using BFILE
- 10 Obtain Clob data pointer
- 11 Open the CLOB
- 12 Performing basic string operations on CLOBs
- 13 Read clob data to varchar2 type variable
- 14 Read clob type data, DBMS_LOB.READ
- 15 Reading and Writing to a CLOB
- 16 Update clob data
Adding Content to a CLOB
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 id INTEGER PRIMARY KEY, 3 clob_column CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> INSERT INTO myTable(id, clob_column) VALUES (1, EMPTY_CLOB()); 1 row created. SQL> SQL> SQL> UPDATE myTable
2 SET clob_column = "AAAAA" 3 WHERE id = 1;
1 row updated. SQL> SQL> select * from myTable;
ID CLOB_COLUMN
--------------------------------------------------------------------------------
1 AAAAA
SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
close Clob data pointer
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY, 3 clob_data CLOB);
Table created. SQL> SQL> INSERT INTO myClob VALUES (101,null); 1 row created. SQL> SQL> declare
2 3 clob_pointer CLOB; 4 5 v_buf VARCHAR2(1000); 6 7 Amount BINARY_INTEGER :=1000; 8 9 Position INTEGER :=1; 10 BEGIN 11 v_buf :=rpad("A",1000,"A"); 12 13 insert into myClob values (1,EMPTY_CLOB()); 14 15 SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1 FOR UPDATE; 16 17 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE); 18 19 FOR i IN 1..500 LOOP 20 21 DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf); 22 23 Position :=Position +Amount; 24 25 END LOOP; 26 27 DBMS_LOB.CLOSE (clob_pointer); 28 END; 29 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myClob; Table dropped.</source>
Compare date value after to_char() and trim()
<source lang="sql">
SQL> create table gift(
2 gift_id integer primary key 3 ,emp_id integer 4 ,register_date date not null 5 ,total_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,message varchar2(100) 12 );
Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created. SQL> SQL> SQL> SQL> select gift_id, register_date
2 from gift 3 where trim(to_char(register_date, "Month")) = "February"; GIFT_ID REGISTER_
---------
1 14-FEB-99 2 14-FEB-99 3 14-FEB-99 4 14-FEB-99
4 rows selected. SQL> SQL> SQL> drop table gift; Table dropped.</source>
Convert string to clob
<source lang="sql">
SQL> SQL> declare
2 3 c1 clob := to_clob("abc"); 4 c2 clob; 5 6 begin 7 case c1 8 when to_clob("abc") then dbms_output.put_line("abc"); 9 when to_clob("def") then dbms_output.put_line("def"); 10 end case; 11 12 c2 := case c1 13 when to_clob("abc") then "abc" 14 when to_clob("def") then "def" 15 end; 16 17 dbms_output.put_line(c2); 18 19 end; 20 /
abc abc PL/SQL procedure successfully completed. SQL></source>
Copy clob data
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN 3 SELECT clobData INTO clob_par FROM myTable WHERE id = id_par; 4 END initClob; 5 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
2 clobVariable CLOB; 3 charVariable VARCHAR2(50); 4 offsetPos INTEGER := 1; 5 amount_var INTEGER := 50; 6 BEGIN 7 initClob(clobVariable, id_par); 8 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 9 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var); 11 END readClob; 12 /
Procedure created. SQL> SQL> CREATE OR REPLACE PROCEDURE copy_example IS
2 clobSrc CLOB; 3 clobDest CLOB; 4 src_offsetPos INTEGER := 1; 5 dest_offsetPos INTEGER := 7; 6 amount_var INTEGER := 5; 7 BEGIN 8 SELECT clobData INTO clobSrc FROM myTable WHERE id = 2; 9 SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE; 10 11 readClob(1); 12 DBMS_LOB.COPY(clobDest, clobSrc, amount_var,dest_offsetPos, src_offsetPos); 13 readClob(1); 14 15 ROLLBACK; 16 END copy_example; 17 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.</source>
Creating Tables Containing CLOB Objects
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 id INTEGER PRIMARY KEY, 3 clob_column CLOB NOT NULL 4 );
Table created. SQL> SQL> desc myTable;
Name Null? Type --------------- ID NOT NULL NUMBER(38) CLOB_COLUMN NOT NULL CLOB
SQL> SQL> drop table myTable; Table dropped. SQL></source>
Initialize CLOB column
<source lang="sql">
SQL> CREATE TABLE MyTable (
2 id INTEGER PRIMARY KEY, 3 clob_column CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> INSERT INTO myTable(id, clob_column) VALUES (1, EMPTY_CLOB()); 1 row created. SQL> SQL> select * from mytable;
ID CLOB_COLUMN
-----------
1
SQL> SQL> drop table myTable; Table dropped. SQL></source>
Insert into clob column
<source lang="sql">
SQL> SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY, 3 clob_data CLOB);
Table created. SQL> SQL> SQL> INSERT INTO myClob VALUES (102,RPAD("AAAAAAAAAAAAAAAAAA",40000,"A")); 1 row created. SQL> SQL> SQL> SQL> drop table myClob; Table dropped. SQL></source>
Loading data to the CLOB by using BFILE
CLOBs are very useful structures. You can store lots of text information in a CLOB.
<source lang="sql">
SQL> SQL> create table catalog
2 (id number, 3 name VARCHAR2(2000), 4 manual_cl CLOB, 5 firstpage_bl BLOB, 6 mastertxt_bf BFILE 7 );
Table created. SQL> SQL> SQL> create directory IO as "C:\IO";
SQL> --grant read, write on directory IO to public; SQL> SQL> insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", BFILENAME ("IO", "text.htm")); 1 row created. SQL> SQL> SQL> declare
2 v_file_bf BFILE; 3 v_manual_cl CLOB; 4 lang_ctx NUMBER := DBMS_LOB.default_lang_ctx; 5 charset_id NUMBER := 0; 6 src_offset NUMBER := 1; 7 dst_offset NUMBER := 1; 8 warning NUMBER; 9 begin 10 update catalog set manual_cl = EMPTY_CLOB() where id = 1; 11 12 select mastertxt_bf, manual_cl into v_file_bf, v_manual_cl from catalog where id = 1; 13 14 DBMS_LOB.fileopen(v_file_bf, DBMS_LOB.file_readonly); 15 DBMS_LOB.loadclobfromfile (v_manual_cl, v_file_bf,DBMS_LOB.getlength (v_file_bf), 16 src_offset, dst_offset,charset_id, lang_ctx,warning); 17 DBMS_LOB.fileclose (v_file_bf); 18 end; 19 /
declare
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 14
SQL> SQL> drop directory IO; Directory dropped. SQL> SQL> drop table catalog; Table dropped.</source>
Obtain Clob data pointer
<source lang="sql">
SQL> SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY, 3 clob_data CLOB);
Table created. SQL> SQL> INSERT INTO myClob VALUES (101,null); 1 row created. SQL> SQL> declare
2 clob_pointer CLOB; 3 v_buf VARCHAR2(1000); 4 Amount BINARY_INTEGER :=1000; 5 Position INTEGER :=1; 6 BEGIN 7 v_buf :=rpad("A",1000,"A"); 8 9 insert into myClob values (5,EMPTY_CLOB()); 10 11 SELECT clob_data INTO clob_pointer FROM myClob WHERE id =5 FOR UPDATE; 12 13 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE); 14 15 FOR i IN 1..500 LOOP 16 17 DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf); 18 19 Position :=Position +Amount; 20 21 END LOOP; 22 23 DBMS_LOB.CLOSE (clob_pointer); 24 END; 25 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myClob; Table dropped.</source>
Open the CLOB
<source lang="sql">
SQL> SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY, 3 clob_data CLOB);
Table created. SQL> SQL> DECLARE
2 clob_pointer CLOB; 3 v_Buf VARCHAR2(1000); 4 Amount BINARY_INTEGER :=1000; 5 Position INTEGER :=1; 6 fp UTL_FILE.FILE_TYPE; 7 BEGIN 8 9 insert into myClob values (1,EMPTY_CLOB()); 10 11 SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1; 12 13 if (DBMS_LOB.ISOPEN(clob_pointer)!=1) then 14 15 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READONLY); 16 17 end if; 18 19 fp :=UTL_FILE.FOPEN("BFILE_DIR","output.dat","w"); 20 21 LOOP 22 BEGIN 23 24 DBMS_LOB.READ (clob_pointer, Amount, Position, v_Buf); 25 26 UTL_FILE.PUT_LINE(fp, v_Buf, TRUE); 27 28 Position :=Position + Amount; 29 EXCEPTION 30 WHEN NO_DATA_FOUND THEN 31 EXIT; 32 END; 33 END LOOP; 34 35 UTL_FILE.FCLOSE(fp); 36 37 DBMS_LOB.CLOSE (clob_pointer); 38 END; 39 /
SQL> SQL> drop table myClob; Table dropped.</source>
Performing basic string operations on CLOBs
<source lang="sql">
SQL> SQL> create table catalog
2 ( 3 id number, 4 name VARCHAR2(2000), 5 manual_cl CLOB, 6 firstpage_bl BLOB, 7 mastertxt_bf BFILE 8 );
Table created. SQL> SQL> SQL> create directory IO as "C:\IO"; Directory created. SQL> --grant read, write on directory IO to public; SQL> SQL> insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", BFILENAME ("IO", "text.htm")); 1 row created. SQL> SQL> SQL> declare
2 v_manual_cl CLOB; 3 v_nr NUMBER; 4 v_tx VARCHAR2 (2000); 5 v_add_tx VARCHAR2 (2000):="Loaded: "||TO_CHAR(SYSDATE,"mm/dd/yyyy hh24:mi"); 6 begin 7 select manual_cl into v_manual_cl from catalog where id = 1 8 for update; 9 10 DBMS_LOB.writeappend (v_manual_cl,LENGTH (v_add_tx), v_add_tx); 11 12 v_nr := INSTR (v_manual_cl, "Loaded:", -1); 13 v_tx := SUBSTR (v_manual_cl, v_nr); 14 DBMS_OUTPUT.put_line (v_tx); 15 end; 16 17 drop table catalog; 18 19 drop directory IO; 20 21 --Keep in mind that LOB pointers are transaction dependent. This means that if you have a COMMIT command in your code, the LOB pointer could become invalid (not pointing to anything) and you may not be able to perform some operations by using that locator. 22</source>
Read clob data to varchar2 type variable
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN 3 SELECT clobData INTO clob_par FROM myTable WHERE id = id_par; 4 END initClob; 5 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
2 clobVariable CLOB; 3 charVariable VARCHAR2(50); 4 offsetPos INTEGER := 1; 5 amount_var INTEGER := 50; 6 BEGIN 7 initClob(clobVariable, id_par); 8 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable); 9 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable); 10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var); 11 END readClob; 12 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE write_example(id_par IN INTEGER) IS
2 clobVariable CLOB; 3 charVariable VARCHAR2(10) := "pretty"; 4 offsetPos INTEGER := 7; 5 amount_var INTEGER := 6; 6 BEGIN 7 SELECT clobData INTO clobVariable FROM myTable WHERE id = id_par FOR UPDATE; 8 9 readClob(1); 10 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable); 11 readClob(1); 12 13 END write_example; 14 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.</source>
Read clob type data, DBMS_LOB.READ
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clob_column CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN 3 SELECT clob_column INTO clob_par FROM myTable WHERE id = id_par; 4 END initClob; 5 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
2 clob_var CLOB; 3 char_buffer_var VARCHAR2(50); 4 offset INTEGER := 1; 5 amount_var INTEGER := 50; 6 BEGIN 7 initClob(clob_var, id_par); 8 DBMS_LOB.READ(clob_var, amount_var, offset, char_buffer_var); 9 DBMS_OUTPUT.PUT_LINE("char_buffer_var = " || char_buffer_var); 10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var); 11 END readClob; 12 /
Procedure created. SQL> SQL> drop table myTable; Table dropped.</source>
Reading and Writing to a CLOB
<source lang="sql">
SQL> SQL> CREATE TABLE myClob
2 (id NUMBER PRIMARY KEY, 3 clob_data CLOB);
Table created. SQL> SQL> INSERT INTO myClob VALUES (101,null); 1 row created. SQL> SQL> declare
2 clob_pointer CLOB; 3 v_buf VARCHAR2(1000); 4 Amount BINARY_INTEGER :=1000; 5 Position INTEGER :=1; 6 BEGIN 7 v_buf :=rpad("A",1000,"A"); 8 9 insert into myClob values (1 ,EMPTY_CLOB()); 10 11 commit; 12 13 SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1 FOR UPDATE; 14 DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READWRITE); 15 16 FOR i IN 1..500 LOOP 17 18 DBMS_LOB.WRITE (clob_pointer,Amount,Position,v_buf); 19 20 Position :=Position +Amount; 21 22 END LOOP; 23 24 DBMS_LOB.CLOSE (clob_pointer); 25 26 END; 27 /
PL/SQL procedure successfully completed. SQL> SQL> drop table myClob; Table dropped. SQL> SQL></source>
Update clob data
<source lang="sql">
SQL> SQL> CREATE TABLE myClobTable (id NUMBER,clob_data CLOB); Table created. SQL> SQL> CREATE TABLE myLongTable (id NUMBER,long_data LONG); Table created. SQL> SQL> INSERT INTO myLongTable VALUES (100,RPAD("A",200000,"A")); 1 row created. SQL> SQL> SQL> update myClobTable set clob_data =(select to_lob(long_data)from
2 myLongTable where id =100) 3
SQL> drop table myLongTable; Table dropped. SQL> SQL> SQL> drop table myClobTable; Table dropped.</source>