execute immediate [帶繫結變數的目標sql] using [對應繫結變數的具體輸入值];

舉例1：
declare
    vc_name varchar2(10);
begin
    execute immediate 'select ename from emp where empno=:1' into vc_name using 7369;
    dbms_output.put_line(vc_name)；
end;
/

舉例2：
declare
    vc_column varchar2(10);
    vc_sql varchar2(4000);
    n_temp number;
    vc_ename varchar2(10);
begin
    vc_column := 'enpno';
    vc_sql := 'delete from emp where '||vc_column||' = ：1 returning ename into :2';
    execute immedaite vc_sql using 7369 returning into vc_ename;
    dbms_output.put_line(vc_ename);
    commit;
end;
/

舉例3：
declare
    cur_emp sys_refcursor;
    vc_sql varchar2(4000);
    type namelist is table of varchar2(10);
    enames namelist;
    CN_BATCH_SIZE constant pls_integer := 1000;
begin
    vc_sql := 'select ename from emp where empno > :1';
    open cur_emp for vc_sql using 7900;
    loop
        fecth cur_emp bulk connect into enames limit CN_BATCH_SIZE;
        for i in 1..enames.count
        loop
            dbms_output.put_line(enames(i));
        end loop;
        exit where ename.count < CN_BATCH_SIZE；
    end loop;
    close cur_emp;
end;
/

PLSQL中使用繫結變數的語法

相關文章