What’s a PL/SQL table? Its purpose and Advantages?
A PL/SQL table is one dimensional, indexed, unbounded sparsed collection of homogeneous
PLSQL tables are used to move data into and out of the database and between client side applications and stored sub-programs. They have attributes such as exits, prior, first, last, delete ,next . These attributes make PLSQL tables easier to use and applications easier to maintain.
PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PL\SQL table in memory.
Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO.
They also offer the ease-of-use of standard tables, since standard SQL can be used with them; no special array-processing syntax is required.
2. What is a Cursor? How many types of Cursor are there?
A) Cursor is an identifier/name to a work area that we can interact with to access its information. A cursor points to the current row in the result set fetched. There are three types of cursors. They are
Implicit cursors – created automatically by PL/SQL for all SQL-DML statements such as
Insert Update, delete and Select
Explicit cursors – Created explicitly. They create a storage area where the set of rows
Returned by a query are placed.
Dynamic Cursors – Ref Cursors( used for the runtime modification of the select querry).
Declaring the cursor, Opening the cursor, Fetching data , Closing the cursor(Releasing the work area) are the steps involved when using explicit cursors.
3. What is the difference between Function and Procedure?
Procedure is a sub program written to perform a set of actions and returns multiple values
Using out parameters or return no value at all.