Oracle ¶¯Ì¬SQL·µ»Øµ¥Ìõ½á¹ûºÍ½á¹û¼¯

Oracle ¶¯Ì¬SQL·µ»Øµ¥Ìõ½á¹ûºÍ½á¹û¼¯
1. DDL ºÍ DML
/**//*** 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;
2. ·µ»Øµ¥Ìõ½á¹û
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.NAME||R_1.ADDRESS);
end ;
3. ·µ»Ø½á¹û¼¯
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