Question

(I ve found this which partly answers the question, the declare way would look neater though given that my sql is will be used in oracle and mssql:) Binding variables in dynamic PL/SQL

我有一些动态的 s子,我用像以下的yn子执行:

EXECUTE IMMEDIATE plsql_block USING employeeid, sortname;

and I can then access those variables inside the dynamic sql using :1, :2, etc. Can I use named parameters instead? something like

EXECUTE IMMEDIATE plsql_block USING employeeid => employeeid

然后使用:雇员,而不是依赖职位,在动态车库内进入他们?

如果没有我的想法,就象在 s开始的时候那样做:

declare employeeid varchar(15 := :1;

那么,我可以把我充满活力的 s子改变到我的心脏中,而不担心立场。

是否有更好的办法?

感谢

Answer 1

Your workaround is what I have used in the past.
There s also good old DBMS_Sql, which allows binding variables like you would be able to via OCI. (Which is basically what DBMS_Sql seems to be doing anyways ;-) )

Answer 2

You can use named arguments in DBMS_SQL:

DECLARE
        res INT;
        cr INT;
BEGIN
        cr := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cr, :plsql_block, DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(cr,  :employee , :employee);
        res := DBMS_SQL.EXECUTE(cr);
        DBMS_SQL.CLOSE_CURSOR(cr);
END;

友情链接