1.產生一個表
begin
execute immediate
'create table yourtable(
yourrow number,
yourdesc varchar2(20)) tablespace tabs';
end;
/
2、insert 資料
declare
v_yournum number;
v_yourdesc varchar2(50);
v_insert_stmt varchar2(100);
begin
v_insert_stmt := 'insert into yourtable values(:1,:2)';
v_yournum := 1;
v_yourdesc := 'one';
execute immediate v_insert_stmt using v_yournum,v_yourdesc;
v_yournum := 2;
v_yourdesc := 'two';
execute immediate v_insert_stmt using v_yournum,v_yourdesc;
end;
/
3、查詢
set serveroutput on;
declare
type your_cursor_type is ref cursor;
your_cursor your_cursor_type;
type dyn_record is record(
yourrow yourtable.yourrow%type,
yourdesc yourtable.yourdesc%type);
dyn_rec dyn_record;
dynamic_select varchar2(500);
begin
dynamic_select := 'select * from yourtable order by yourrow';
open your_cursor for dynamic_select;
loop
fetch your_cursor into dyn_rec;
exit when your_cursor%notfound;
dbms_output.put_line(dyn_rec.yourrow||' '||dyn_rec.yourdesc);
end loop;
close your_cursor;
end;
/
4、pl/sql 塊
--pl/sql block
declare
block_to_exec varchar2(500) := '
begin
select * into :1,:2 from yourtable where yourrow=2;
end;';
yourrow number;
yourdesc yourtable.yourdesc%type;
begin
execute immediate block_to_exec using out yourrow, out yourdesc;--預設為in
dbms_output.put_line(yourrow||' '||yourdesc);
end; /
