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;
/

ITPub博客

PLSQL中使用绑定变量的语法

最新文章