/**//*** DDL ***/
begin EXECUTE IMMEDIATE 'drop table temp_1'; EXECUTE IMMEDIATE 'create table temp_1(name varchar2(8))'; end; /**//*** DML ***/declare v_1 varchar2(8); v_2 varchar2(10); str varchar2(50); begin v_1:='測試人員'; v_2:='北京'; str := 'INSERT INTO test (name ,address) VALUES (:1, :2)'; EXECUTE IMMEDIATE str USING v_1, v_2; commit; end; declare str varchar2(500); c_1 varchar2(10); r_1 test%rowtype; begin c_1:='測試人員'; str:='select * from test where name=:c WHERE ROWNUM=1'; execute immediate str into r_1 using c_1; DBMS_OUTPUT.PUT_LINE(R_1.NAMER_1.ADDRESS); end ; CREATE OR REPLACE package pkg_test as /**//* 定義ref cursor類型 
不加return類型，為弱類型，答應動態sql查詢， 否則為強類型，無法使用動態sql查詢; 
*/ type myrctype is ref cursor; --函數申明 function get(intID number) return myrctype; end pkg_test; / CREATE OR REPLACE package body pkg_test as --函數體 function get(intID number) return myrctype is rc myrctype; --定義ref cursor變量 sqlstr varchar2(500); begin if intID=0 then --靜態測試，直接用select語句直接返回結果 open rc for select id,name,sex,address,postcode,birthday from student; else --動態sql賦值，用:w_id來申明該變量從外部獲得 sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id'; --動態測試，用sqlstr字符串返回結果，用using要害詞傳遞參數 open rc for sqlstr using intid; end if; return rc; end get; end pkg_test; / 新聞熱點
疑難解答
