复合类型练习
一、记录类型
1.显示定义
declaretype t_record is record( id test.id%type, mc test.mc%type );var_record t_record;counter number default 0;beginfor row_test in (select id,mc from test) loopcounter :=counter+1;var_record.id := row_test.id;var_record.mc := row_test.mc;dbms_output.put_line('var_record:'|| var_record.id || '----' || var_record.mc);dbms_output.put_line('row_test: '|| row_test.id || '----' ||row_test.mc);dbms_output.put_line('=======loop' || counter ||'times.');end loop;exception when others thendbms_output.put_line(sqlcode ||sqlerrm);end;/?declaret_record1 test%rowtype;cursor cur_test(v_id in varchar2) isselect id,mc from testwhere id <= v_id;t_record2 cur_test%rowtype;beginfor row_test in cur_test('333') loopt_record1.id := row_test.id;t_record1.mc := row_test.id;t_record2.id := row_test.id;t_record2.mc := row_test.id;dbms_output.put_line('row_test:' || row_test.id || '----' || row_test.mc);dbms_output.put_line('t_record1:' || t_record1.id || '----' || t_record1.mc);dbms_output.put_line('t_record2:' || t_record2.id || '----' || t_record2.mc);dbms_output.put_line('=======loop' || cur_test%rowcount || 'times.');end loop;exception when others thendbms_output.put_line(sqlcode || sqlerrm);end;?
declarecursor cur_test is select id,mc from test;type t_test1 is table of varchar2(60) index by binary_integer;type t_test2 is table of test%rowtype index by binary_integer;var_test1 t_test1;var_test2 t_test2;var_new t_test2;beginselect id,mc into var_test2(0) from test where id='111';dbms_output.put_line('var_test2(0):' || var_test2(0).id || '----' || var_test2(0).mc);select id,mc into var_test2(8) from test where id='333';dbms_output.put_line('var_test2(8):' || var_test2(8).id || '----' || var_test2(8).mc);var_new :=var_test2;dbms_output.put_line('===== copy var_test2 to var_new =====');dbms_output.put_line('var_new(0):'||var_new(0).id||'---'||var_new(0).mc);dbms_output.put_line('var_new(8):'||var_new(8).id||'---'||var_new(8).mc);end;?DECLARETYPE t_test1 IS TABLE OF test.id%TYPE;TYPE t_test2 IS VARRAY (10) OF test.id%TYPE;var_test1 t_test1;var_test2 t_test2;beginvar_test1 := t_test1('test1.1','test1.2','test1.3');dbms_output.put_line('var_test1: '||var_test1(1)||','||var_test1(2)||','||var_test1(3));var_test2 := t_test2('test2.1','test2.2','test2.3');dbms_output.put_line('var_test2: '||var_test2(1)||','||var_test2(2)||','||var_test2(3));var_test1(2) := 'test1.2_update';dbms_output.put_line('==== modify var_test1(2) ====');dbms_output.put_line('var_test1: '||var_test1(1)||','||var_test1(2)||','||var_test1(3));dbms_output.put_line(var_test1.next(3));dbms_output.put_line('the length of var_test2''s element: '||var_test2.limit());end;/?set serverout onDECLARETYPE t_record IS RECORD (id number(18,0),mc varchar2(50));var_record t_record;type t_test is table of t_record;var_test t_test := t_test();cursor cur_test is select id,mc from test;beginopen cur_test;fetch cur_test BULK COLLECT INTO var_test;for i in 1..var_test.count() loopdbms_output.put_line(var_test(i).id||'---'||var_test(i).mc);end loop;end;/??
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?