|
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
a dynamically, since you don’t know what table you
are getting statistics from. You should write your
function so that your client can display all of the
tables’ name, plus the number of records contained
each table.
-- Hands-On 07 (Using
Native Dynamic SQL)
SET ECHO ON
CLEAR SCR
-- Connect to sqlplus as the oracle user.
--
pause
CONNECT oracle/learning
pause
CLEAR SCR
-- Set the pagesize to 55 and the linesize to 100.
--
pause
SET PAGESIZE 55 LINESIZE 100
pause
CLEAR SCR
-- Write a stored procedure, to pass the table name
-- as a parameter, and get back the number of
records
-- that table contains.
--
pause
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;
/
-- Notice that the native dynamic SQL was used
-- in the stored procedure.
pause
CLEAR SCR
-- Test your function with a single table.
--
pause
SELECT get_total_recs('emp') FROM dual
/
pause
CLEAR SCR
-- Test your function with multiple tables.
--
pause
SELECT table_name as "Table Name",
get_total_recs(table_name) as "Number of Records"
FROM user_tables
/
pause
CLEAR SCR
-- Drop the get_total_recs function.
--
pause
DROP FUNCTION get_total_recs
/
pause
CLEAR SCR
-- Now, practice this Hands-On over and over
-- until you become a master at it.
-- For more information about the subject, you are
encouraged
-- to read from a wide selection of available books.
-- Good luck!
pause |
