 |
|
Oracle Tips by Burleson |
Dynamic
SQL in PL/SQL
The programmer actually has two methods for
executing a SQL statement within PL/SQL, the embedded method which we
have been using throughout the book, and implementing dynamic SQL.
There are many times when a SQL statement needs to be built and
executed dynamically. When using SQL*Plus creating and executing
dynamic SQL is a fairly simple task; create a script that generates
the SQL, and then run the script. The script below will create the
commands to truncate all the tables in the PUBS schema.
set pages 0
line 132 feedback off trim on
spool /opt/script/truncate.sql
select
'truncate table '||tablename||';'
from user_tables;
spool off;
set pages 999 feedback on
@/opt/script/truncate.sql
In PL/SQL the SQL statement is created as a
string, which is then executed using the EXECUTE IMMEDIATE clause
.
SQL> declare
2 v_str varchar2(200);
3 begin
4 for r_c1 in (select * from user_tables) loop
5 v_str:= 'delete '||r_c1.table_name;
6 dbms_output.put_line(v_str);
7 execute immediate v_str;
8 end loop;
9 end;
10 /
delete AUTHOR
delete EMP
delete JOB
delete PUBLISHER
delete SALES
delete STORE
delete BOOK_AUTHOR
delete BOOK
SQL> rollback
Notice that you still need to execute a commit to
make the changes permanent. The ROLLBACK statement
in the above example returns the deleted rows to the tables.
The EXECUTE IMMEDIATE clause
allows the application to build different SQL statements based on
user input or application errors. It also allows the developer to
dynamically tune the application. A report may require a set of
indexes to operate efficiently; however, these indexes may harm
database performance if left in the database all the time. Using
dynamic SQL, the application can build the indexes, run the report,
and then delete the indexes upon completion.
SQL> begin
2 execute immediate 'create index ln_idx
3 on author(author_last_name)';
4 execute immediate 'create index fn_idx
5 on author(author_first_name)';
6 -- run_big_report;
7 execute immediate 'drop index ln_idx';
8 execute immediate 'drop index fn_idx';
9 end;
10 /
Dynamic SQL is easy to implement and very
powerful. When creating database objects, such as shown in the
example above, be careful that the cost of creating the object is not
greater than the cost of running the report without the object.
There is one significant issue with using dynamic
SQL. A dynamical SQL statement submitted to the database using
EXECUTE IMMEDIATE does not use bind variables. SQL statements in
PL/SQL blocks automatically use bind variables, but dynamic SQL
cannot. Bind variables allow the database to reuse stored execution
plans. Never use dynamic SQL when a normal SQL statement will work.
If you want the details of how the database used bind variables, look
through any Oracle database tuning book, they will cover it in detail.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
