150.Oracle数据库SQL开发之 大对象——10g对大对象的增强
欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/50185853
10g对大对象的下列增强:
n CLOB和NCLOB对象之间的隐式转换
n 在触发器中使用LOB时, :new属性的用法
10g以下的数据库版本中,使用TO_CLOB和TO_NCLOB函数在UNICODE文本和国家语言字符集文本之间进行显示转换。
CREATETABLE nclob_content (
id INTEGERPRIMARY KEY,
nclob_columnNCLOB
);
CREATEPROCEDURE nclob_example
AS
v_clob CLOB:= 'It is the east and Juliet is the sun';
v_nclobNCLOB;
BEGIN
-- insertv_clob into nclob_column; this implicitly
-- convertsthe CLOB v_clob to an NCLOB, storing
-- thecontents of v_clob in the nclob_content table
INSERT INTO nclob_content (
id,nclob_column
) VALUES (
1, v_clob
);
-- selectnclob_column into v_clob; this implicitly
-- convertsthe NCLOB stored in nclob_column to a
-- CLOB,retrieving the contents of nclob_column
-- into v_clob
SELECTnclob_column
INTO v_clob
FROMnclob_content
WHERE id = 1;
-- displaythe contents of v_clob
DBMS_OUTPUT.PUT_LINE('v_clob = ' || v_clob);
END nclob_example;
/
执行如下:
collection_user@PDB1> set serveroutput on
collection_user@PDB1> call nclob_example();
v_clob = It is the east and Juliet is thesun
Call completed.
在触发器中使用LOB时 :new属性的用法。
在10g或更高版本中,挡在BEFORE UPDATE或BEFORE INSERT行级触发器中使用LOB时,可以使用:NEW属性。
CREATE TRIGGER before_clob_content_update
BEFORE UPDATE
ON clob_content
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('clob_content changed');
DBMS_OUTPUT.PUT_LINE(
'Length = '|| DBMS_LOB.GETLENGTH(:new.clob_column)
);
END before_clob_content_update;
/
INSERTINTO clob_content (
id,clob_column
) VALUES(
1,TO_CLOB('Creeps in this petty pace')
);
INSERTINTO clob_content (
id,clob_column
) VALUES(
2, TO_CLOB('from day to day')
);
执行如下:
collection_user@PDB1> update clob_content setclob_column='Creeep in this petty pace' where id=1;
clob_content changed
Length = 25
1 row updated.