Explain how procedures and functions are called in a PL/SQL
block ?
Answers were Sorted based on User's Feedback
Answer / tulsi
PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / p.rajasekar
CREATE OR REPLACE PACKAGE PersistPkg AS
–– Type which holds an array of book ISBN's
TYPE t_BookTable IS TABLE OF books.isbn%TYPE
INDEX BY BINARY_INTEGER;
–– Maximum number of rows to return each time.
v_MaxRows NUMBER := 4;
–– Returns up to v_MaxRows ISBN's
PROCEDURE ReadBooks(p_BookTable OUT t_BookTable,
p_NumRows OUT NUMBER);
END PersistPkg;
/
CREATE OR REPLACE PACKAGE BODY PersistPkg AS
–– Query against books. Since this is global to the package
–– body, it will remain past a database call.
CURSOR c_BasicBooks IS
SELECT isbn
FROM BOOKS
WHERE category = 'Oracle Basics'
ORDER BY title;
PROCEDURE ReadBooks(p_BookTable OUT t_BookTable,
p_NumRows OUT NUMBER) IS
v_Done BOOLEAN := FALSE;
v_NumRows NUMBER := 1;
BEGIN
IF NOT c_BasicBooks%ISOPEN THEN
–– First open the cursor
OPEN c_BasicBooks;
END IF;
–– Cursor is open, so we can fetch up to v_MaxRows
WHILE NOT v_Done LOOP
FETCH c_BasicBooks INTO p_BookTable(v_NumRows);
IF c_BasicBooks%NOTFOUND THEN
Chapter 9: Using Procedures, Functions, and Packages 407
–– No more data, so we're finished.
CLOSE c_BasicBooks;
v_Done := TRUE;
ELSE
v_NumRows := v_NumRows + 1;
IF v_NumRows > v_MaxRows THEN
v_Done := TRUE;
END IF;
END IF;
END LOOP;
–– Return the actual number of rows fetched.
p_NumRows := v_NumRows - 1;
END ReadBooks;
END PersistPkg;
/
PersistPkg.ReadBooks will select from the c_BasicBooks
cursor.
Since this cursor is declared at the package level (not
inside ReadBooks), it will
remain past a call to ReadBooks. We can call
PersistPkg.ReadBooks with
the following block:
DECLARE
v_BookTable PersistPkg.t_BookTable;
v_NumRows NUMBER := PersistPkg.v_MaxRows;
v_Title books.title%TYPE;
BEGIN
PersistPkg.ReadBooks(v_BookTable, v_NumRows);
DBMS_OUTPUT.PUT_LINE(' Fetched ' || v_NumRows || ' rows:');
FOR v_Count IN 1..v_NumRows LOOP
SELECT title
INTO v_Title
FROM books
WHERE isbn = v_BookTable(v_Count);
DBMS_OUTPUT.PUT_LINE(v_Title);
END LOOP;
END;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / nishi
PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
Is This Answer Correct ? | 0 Yes | 0 No |
what are rollup and cube in t-sql? : Transact sql
What is indexing in sql and its types?
How do you assign Construct the where clause without concatenating Field,value pairs?
What are all the ddl commands?
What is sql injection vulnerability?
What is the difference between DELETE and TRUNCATE?
15 Answers Johns Hopkins University, Tech Mahindra,
how can i read write files from pl/sql
What is consistency?
What is oracle sql developer?
how mysql optimizes distinct? : Sql dba
What are types of joins?
Explain the uses of control file.