|
“The person who
makes a success of living is the one who see his goal steadily
and aims for it unswervingly. That is dedication.” Cecil B.
DeMille (1881 - 1959) |
Read
first then play the video:
SQL019(VIDEO)-How
to use Native Dynamic SQL
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
iself user.
SQL> CONNECT iself/schooling
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> /
|
You see things; and
you say, 'Why?' But I dream things that never were; and I say,
"Why not?" George Bernard Shaw (1856 - 1950),
"Back to Methuselah" (1921), part 1 |
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.
|
