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 is the difference between the repeatable read and serializable isolation levels? : Transact sql
how to escape special characters in sql statements? : Sql dba
Can you join views in sql?
How many unique keys can a table have?
how to get a list of indexes of an existing table? : Sql dba
What is coalesce sql?
What is sqlca in db2?
What does the sign mean in sql?
can we write stored function in out parameters? how to call through select statement? how to written more than one value plz give the exmple?
What does “select count(1) from tab” result?
How does postgresql compare to mysql?
How do I remove sql developer from windows 10?