Information générale
Titre:
NATIVE DYNAMIC SQL on Oracle 8i...
I just learned about the EXECUTE IMMEDIATE command.
It's very interesting so I thought I'd share the info...
-It's used in 8i as a replacement to the DBMS_SQL package...
-Uses something called "NATIVE DYNAMIC SQL"
-It helps compact code and make it easier to read and maintain.
-Executes faster. (code is in the PL\SQL engine) where as calls to DBMS_SQL must
"context switch between SQL and PL\SQL engines..."
-According to Oracle executes 30-60% faster...
Here is an example...
l_sql:='insert into mytable values(:col1,:col2)';
--Step 1 open cursor
csr_handle:=DBMS_SQL.OPEN_CURSOR();
--Step 2 Parse!
DBMS_SQL.PARSE(csr_handle,l_sql,DBMS_SQL.NATIVE);
--Step 3
-Step 3: Bind variables
DBMS_SQL.BIND_VARIABLE(csr_handle,':col1',col1value);
DBMS_SQL.BIND_VARIABLE(csr_handle,':col2',col2value);
-Step 4: Execute
DBMS_SQL.EXECUTE(csr_handle);
-Step 5: Close
DBMS_SQL.CLOSE_CURSOR(csr_handle);
This can be rewritten as...
l_sql:='insert into mytable values(:col1,:col2)';
EXECUTE IMMEDIATE l_sql USING col1value,col2value;
Cool huh!?
I plan to rewite all my stored procedures to use it...
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement