首先,感觉大对象这章的内容真是有点难,但又感觉很实用,值得多花点时间研究一番。
CLOB, NCLOB及BLOB的数据状态:
隐含LOB定位器函数架构:
CLOB, NCLOB的Insert语句原型:
CLOB, NCLOB的Update语句原型:
示例:
-- Assign dynamic string to statement.
stmt := 'UPDATE '||table_name||' '
|| 'SET '||column_name||' = empty_clob() '
|| 'WHERE '||primary_key_name||' = '||''''||primary_key_value||''' '
|| 'RETURNING '||column_name||' INTO :locator';
1. 小型LOB赋值
CLOB或NCLOB PL/SQL赋值:
/* Formatted on 2018/12/10 0:25:53 (QP5 v5.256.13226.35538) */
DECLARE
var1 CLOB; -- Declare a null reference to a CLOB.
var2 CLOB := EMPTY_CLOB (); -- Declare an empty CLOB.
var3 CLOB := 'some_string'; -- Declare a CLOB with a string literal.
BEGIN
NULL;
END;
/
2. BLOB PL/SQL赋值
/* Formatted on 2018/12/10 0:27:20 (QP5 v5.256.13226.35538) */
DECLARE
var1 BLOB; -- Declare a null reference to a BLOB.
var2 BLOB := EMPTY_BLOB (); -- Declare an empty BLOB.
var3 BLOB := '43' || '41' || '52'; -- Declare with hexadecimal values.
BEGIN
NULL;
END;
3. 使用PL/SQL函数给CLOB赋值
SQL> /* Formatted on 2018/12/10 0:31:23 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE FUNCTION create_clob (pv_input_string VARCHAR2)
2 RETURN CLOB
3 AS
4 /* Declare a local CLOB variable. */
5 lv_return CLOB;
6 BEGIN
7 /* Create a temporary CLOB in memory for the scope of the call. */
8 DBMS_LOB.createtemporary (lv_return, FALSE, DBMS_LOB.CALL);
9
10 /* Write the string to the empty temporary CLOB. */
11 DBMS_LOB.WRITE (lv_return,
12 LENGTH (pv_input_string),
13 1,
14 pv_input_string);
15
16 /* Return a CLOB value. */
17 RETURN lv_return;
18 END create_clob;
19 /
函数已创建。