|
Using Native Dynamic SQL
Hands-On
Introduction
Your
organization wants you to write a stored
procedure to pass the table name and get
back the number of records that table
contains. The SELECT statement must be
created dynamically, since you don�t know
what table you are getting statistics from.
You should write your function so that your
client can display the tables� name, plus
the number of records contained each table.
Your
assignments are:
1- To create a
function called �get_total_recs,�
2- To pass a
table name as a parameter,
3- To test and
query a list of all user tables with their
number of rows in the table.
Write a PL/SQL
procedure using Native Dynamic SQL
Native Dynamic
SQL allows an application to run SQL
statements whose contents are not known
until runtime. The statement is built up as
a string by the application and is then
passed to the server. Generally dynamic SQL
is slower than static SQL so it should not
be used unless absolutely necessary. Make
sure to check the syntax, since syntax
checking and object validation cannot be
done until runtime. The only advantage of
dynamic SQL is that it allows you to perform
DDL commands and also allows you to access
objects that will not exist until runtime.
Connect to
sqlplus as the oracle user.
SQL> CONNECT
oracle/learning
Set the
pagesize to 55 and the linesize to 100.
SQL> SET
PAGESIZE 55 LINESIZE 100
Pass a table
name as parameter
Write a stored
procedure, to pass the table name as a
parameter, and get back the number of
records that table contains.
SQL> CREATE OR
REPLACE FUNCTION get_total_recs (loc
VARCHAR2)
RETURN NUMBER IS
Query_str VARCHAR2(1000);
Num_of_recs NUMBER;
BEGIN
Query_str := 'SELECT COUNT(*) FROM ' || loc;
EXECUTE IMMEDIATE query_str INTO num_of_recs;
RETURN num_of_recs;
END;
SQL> /
Notice that the native dynamic
SQL was used in the stored procedure.
Test the
function
Test your
function with a single table.
SQL> SELECT
get_total_recs('emp') FROM dual
SQL> /
Test your
function with multiple tables.
SQL> SELECT
table_name as "Table Name",
get_total_recs(table_name) as "Number of
Records"
FROM user_tables
SQL> /
Drop the
get_total_recs function.
SQL> DROP
FUNCTION get_total_recs
SQL> /
Questions:
Q: What is a
Native Dynamic SQL statement?
Q: Write a
stored procedure to pass the table name and
get back the number of records that table
contains. The SELECT statement must be
created dynamically, since you don�t know
what table you are getting statistics from.
You should write your function so that your
client can display the tables� name, plus
the number of records contained each table. |
