FAQ's in SQL/PLSQL-1

What is PL/SQL?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

What are the components of a PL/SQL Block?

Declarative part

Executable part

Exception part

What are the datatypes a available in PL/SQL?

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?

% TYPE provides the data type of a variable or a database column to that variable.

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are:

I. Need not know about variable’s data type

ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

What is difference between % ROWTYPE and TYPE RECORD?

% ROWTYPE is to be used whenever query returns an entire row of a table or view. TYPE RECORD is to be used whenever query returns columns of different table or views and variables.

Explain the two types of Cursors?

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries.

User defined cursors are called Explicit Cursors. They can be declared and used.

What are the cursor attributes used in PL/SQL?

% ISOPEN – Used to check whether a cursor is open or not.

% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.

% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.

What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

What is the difference between implicit and explicit cursors?

An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.

What are the different types of joins available in Oracle?

Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.

Self Join: If comparison comes in a single table

Cartesian Join: When tables are joined without giving any join condition.

Inner Join: The resultant set includes all the rows that satisfy the join condition.

Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition.  The outer join operator Plus sign (+) will be included in the join condition.

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What is an autonomous transaction?

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.

What is the difference between View and Materialized view?

Materialized view will not be refreshed every time you query the view. So to have good performance when data is not changed so rapidly, we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.

What is dynamic SQL?

Dynamic SQL allows you to construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string and then execute it at runtime.

Can you use COMMIT in a trigger?

Yes but by defining an autonomous transaction.