|
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 |
