The topics in this section contain many examples of DBMS_LOB usage. We use for our examples tables called my_book_files and my_book_text which contain (or point to) large volumes of text for a book. The structures of these tables are:CREATE TABLE my_book_files (
file_descr VARCHAR2(100),
book_file BFILE);CREATE TABLE my_book_text (
chapter_descr VARCHAR2(100),
chapter_text CLOB);We will often query one of the fields from the table for a given chapter (chapter_desc) value. To avoid repetition of code, here are the implementations of functions which will be used throughout the examples:CREATE OR REPLACE FUNCTION book_file (chapter_in IN VARCHAR2)
RETURN CLOB
IS
CURSOR book_cur
IS
SELECT chapter_file
FROM my_book_files
WHERE file_descr = chapter_in;
book_rec book_cur%ROWTYPE;
BEGIN
OPEN book_cur;
FETCH book_cur INTO book_rec;
CLOSE book_cur;
RETURN book_rec.chapter_file;
END;
/
CREATE OR REPLACE FUNCTION book_text (chapter_in IN VARCHAR2)
RETURN CLOB
IS
CURSOR book_cur
IS
SELECT chapter_text
FROM my_book_text
WHERE chapter_descr = chapter_in;
book_rec book_cur%ROWTYPE;
BEGIN
OPEN book_cur;
FETCH book_cur INTO book_rec;
CLOSE book_cur;
RETURN book_rec.chapter_text;
END;
/
CREATE OR REPLACE FUNCTION book_text_forupdate (chapter_in IN VARCHAR2)
RETURN CLOB
IS
CURSOR book_cur
IS
SELECT chapter_text
FROM my_book_text
WHERE chapter_descr = chapter_in
FOR UPDATE;
book_rec book_cur%ROWTYPE;
BEGIN
OPEN book_cur;
FETCH book_cur INTO book_rec;
CLOSE book_cur;
RETURN book_rec.chapter_text;
END;
/In several of the examples, we will compare before and after “images” of LOB content using the following procedure:CREATE OR REPLACE PROCEDURE compare_text (descr IN VARCHAR2)
IS
BEGIN
SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = descr;
ROLLBACK; DBMS_OUTPUT.PUT_LINE ('Rollback completed'); SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = descr;
END;
/
file_descr VARCHAR2(100),
book_file BFILE);CREATE TABLE my_book_text (
chapter_descr VARCHAR2(100),
chapter_text CLOB);We will often query one of the fields from the table for a given chapter (chapter_desc) value. To avoid repetition of code, here are the implementations of functions which will be used throughout the examples:CREATE OR REPLACE FUNCTION book_file (chapter_in IN VARCHAR2)
RETURN CLOB
IS
CURSOR book_cur
IS
SELECT chapter_file
FROM my_book_files
WHERE file_descr = chapter_in;
book_rec book_cur%ROWTYPE;
BEGIN
OPEN book_cur;
FETCH book_cur INTO book_rec;
CLOSE book_cur;
RETURN book_rec.chapter_file;
END;
/
CREATE OR REPLACE FUNCTION book_text (chapter_in IN VARCHAR2)
RETURN CLOB
IS
CURSOR book_cur
IS
SELECT chapter_text
FROM my_book_text
WHERE chapter_descr = chapter_in;
book_rec book_cur%ROWTYPE;
BEGIN
OPEN book_cur;
FETCH book_cur INTO book_rec;
CLOSE book_cur;
RETURN book_rec.chapter_text;
END;
/
CREATE OR REPLACE FUNCTION book_text_forupdate (chapter_in IN VARCHAR2)
RETURN CLOB
IS
CURSOR book_cur
IS
SELECT chapter_text
FROM my_book_text
WHERE chapter_descr = chapter_in
FOR UPDATE;
book_rec book_cur%ROWTYPE;
BEGIN
OPEN book_cur;
FETCH book_cur INTO book_rec;
CLOSE book_cur;
RETURN book_rec.chapter_text;
END;
/In several of the examples, we will compare before and after “images” of LOB content using the following procedure:CREATE OR REPLACE PROCEDURE compare_text (descr IN VARCHAR2)
IS
BEGIN
SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = descr;
ROLLBACK; DBMS_OUTPUT.PUT_LINE ('Rollback completed'); SELECT chapter_descr, chapter_text
FROM my_book_text
WHERE chapter_descr = descr;
END;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货