CREATE OR REPLACE TYPE T_TEST_Attr AS OBJECT( ID INTEGER, RQ DATE, MC VARCHAR2(60) ); CREATE OR REPLACE TYPE T_TEST AS OBJECT( ID INTEGER, RQ DATE, MC VARCHAR2(60), V_TEST_Attr T_TEST_Attr ); CREATE OR REPLACE TYPE T_TEST_TABLE AS TABLE OF T_TEST;create or replace function f_test_array(n in number default null) return t_test_table as v_test t_test_table := t_test_table(); begin for i in 1 .. nvl(n,100) loop v_test.extend(); v_test(v_test.count) := t_test(i,sysdate,'mc'||i,T_TEST_Attr(1,sysdate,'attr')); end loop; return v_test; end f_test_array; / select * from table(f_test_array(10)); -----------------select * from the(select f_test_array(10) from dual);---像这样只不过想把 T_TEST_Attr 这个自定义对象类型变成对应的表,这样就不用表改了类型也要改一下。
用blob或者是clob设置为collob 然后select colA||colB||colC into collob from table
可以通过嵌套表实现 CREATE OR REPLACE TYPE emp_type AS OBJECT( name VARCHAR2(10),salary NUMBER(6,2), hiredate DATE); / CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type; / 使用嵌套表 CREATE TABLE department( deptno NUMBER(2),dname VARCHAR2(10), employee emp_array )NESTED TABLE employee STORE AS employee
DECLARE
v_RoomRecord rooms%ROWTYPE;
将定义一个记录,该记录中的字段将与rooms表中的列相对应。把定义语句放在DECLARE下。v_RoomRecord是记录。
http://blog.csdn.net/jwbecalm/archive/2005/07/09/418403.aspx
你这里的CUST 想放什么东西啊?
和表行相同类型的记录?
ID INTEGER,
RQ DATE,
MC VARCHAR2(60)
);
CREATE OR REPLACE TYPE T_TEST AS OBJECT(
ID INTEGER,
RQ DATE,
MC VARCHAR2(60),
V_TEST_Attr T_TEST_Attr
);
CREATE OR REPLACE TYPE T_TEST_TABLE AS TABLE OF T_TEST;create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
v_test.extend();
v_test(v_test.count) := t_test(i,sysdate,'mc'||i,T_TEST_Attr(1,sysdate,'attr'));
end loop;
return v_test;
end f_test_array;
/
select * from table(f_test_array(10));
-----------------select * from the(select f_test_array(10) from dual);---像这样只不过想把 T_TEST_Attr 这个自定义对象类型变成对应的表,这样就不用表改了类型也要改一下。
然后select colA||colB||colC into collob from table
CREATE OR REPLACE TYPE emp_type AS OBJECT(
name VARCHAR2(10),salary NUMBER(6,2),
hiredate DATE);
/
CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;
/
使用嵌套表
CREATE TABLE department(
deptno NUMBER(2),dname VARCHAR2(10),
employee emp_array
)NESTED TABLE employee STORE AS employee