PL-SQL Syllabus 

Oracle PL/SQL
Basics of PL/SQL
PL/SQL architecture
PL/SQL and SQL*Plus
PL/SQL Basics
Variables
Constants
Datatypes
Error messages � user_errors and show errors
PL/SQL wrapper utility
PL/SQL structures
Simple blocks
Control structures
PL/SQL records
Recognizing the Basic PL/SQL Block and Its Sections
Describing the Significance of Variables in PL/SQL
Distinguishing Between PL/SQL and Non-PL/SQL Variables
Declaring Variables and Constants
Executing a PL/SQL Block
Error checking � exception handling
Defining exceptions
Using the when others clause
Ensuring complete error checking
Passing error messages to calling routine
Boolean logic in PL/SQL
Identifying the Uses and Types of Control Structures
Constructing an IF Statement
Constructing and Identifying Different Loop Statements
Controlling Block Flow Using Nested Loops and Labels
Using Logic Tables
If-then-else structure
Testing for numbers characters and Booleans
Cursors in PL/SQL
Cursor basics
Using a cursor for a multi-row SQL query
Iteration in PL/SQL
For loop
While loop
PL/SQL tables
Defining PL/SQL tables
Reasons to use PL/SQL tables
Populating a PL/SQL table
Retrieving from a PL/SQL table
Dynamic SQL in PL/SQL
Introduction to the dbms_sql package
Creating a dynamic SQL statement
Nested blocks in PL/SQL
Creating nested blocks
Understanding scope in nested blocks
Triggers in PL/SQL
Triggers and database events
Defining a trigger
Timing a trigger
Enabling and disabling a trigger
Stored procedures, functions and packages
Basics of stored procedures
Basics of functions
Basics of packages
Defining stored procedures & functions
Function and stored procedures prototypes
Passing arguments to functions and stored procedures
Recompiling functions and stored procedures
Package forward declaration
Package dependency
Package overloading
Listing package information
Bulking in PL/SQL
Bulk queries
Bulk DML (for all statement)
Using cursor attributes
Analyzing impact of bilk operations Database with MySQL

                 PL-SQL Interview questions

  1.Define PL/SQL.

  - PL/SQL is a procedural language and is an extension to standard SQL.
  - It enables us to execute procedural logic on the database.
  - We use PL/SQL to perform processing on the server.
  - We can also use PL/SQL to create stored procedure and functions.

  PL/SQL is Procedural Language SQL that is an extension of SQL that results in a more structural      language composed of blocks. It is mainly used in writing applications that needs to be structured    and has error handling.

 2. Main Features of PL/SQL

 1) Offers conditional blocks of code having if else etc
 2) Offers error handling. It can handle exceptions.
 3) The blocks can be nested within each other.
 4) The PL/SQL engine processes the statements in blocks.

  The block typically looks like
  DECLARE
  BEGIN
  EXCEPTION
  END

   3.PL/SQL Architecture

  The architecture consists of PL/SQL block, PL/SQL engine and an oracle server in which the PL/SQL engine is embedded. PL/SQL block of statements are sent to the PL/SQL engine for processing. The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine in the Oracle database.

   Basic Structure of PL/SQL

    A PL/SQL block consists of:-
    DECLARATIVE section- This is where all variables are declared.
    BEGIN section- This section contains the PL/SQL block. The statements of code are written in this block.
    EXCEPTION- Any exceptions that are anticipated are written here.

   Variables and Types

   Just like in any other language, PL/SQL constants and variables need to be declared before using them in your statements. The variables are declared in the BEGIN section. Variables can take data types as CHAR, DATE, or NUMBER.

    Example for declaring variables: 
    DECLARE
    Student_id NUMBER(6);
    student_name VARCHAR2(20);Simple PL/SQL Programs

a)To give 10% hike in current salary from employee table and insert into appraisal table.

DECLARE
    salary NUMBER(8,2);
    emp_id NUMBER(6) := 100;
BEGIN
   SELECT salary * 0.10 INTO appraisal FROM employees
         WHERE employee_id = emp_id;
END;

b)To display employees with id < 100.

BEGIN
FOR someone IN (SELECT * FROM employees WHERE employee_id < 100 )
LOOP
DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name || ', Last name = ' || someone.last_name);
END LOOP;
END;

4.Explain the concept of exception.

An exception occurs when unwanted situation arises. The situation can be exceptional to normal functioning of the program.
It can occur due to system error, user error and application error.In PL/SQL, we can anticipate and trap these errors by means of exception handling code.

Types of Exceptions:

Predefined oracle exceptions
User-defined exceptions
Defined user defined exceptions.

We use user defined exception only when oracle doesn't raise its own exception. In this procedure we raise an exception by using RAISE command.

5.What is a cursor? Define explicit and implicit cursor.

The oracle engine opens a work area for each SQL's operations for its internal processing in order to execute SQL statements. This area is private to SQL's operations and is called as a cursor.

Implicit cursor - If the oracle engine has opened a cursor for its internal processing, then it is implicit cursor.

Explicit cursor - It is also known as user defined cursor. When a user opens a cursor for processing data, the cursor is explicit cursor.

5.Explain about the cursor attributes.

Each cursor or cursor variable has four attributes:

%FOUND, %ISOPEN, %NOTFOUND and %ROWCOUNT

When appended to the cursor, these attributes return useful information about the execution of a data manipulation statement.

6.What are the restrictions of using cursor variables?

-PL/SQL tables cannot store cursor variables.
-Remote subprogram cannot return the value of a cursor variable.
What is a trigger in PLSQL?

A trigger is a PLSQL block that is executed whenever an event occurs. It fires implicitly whenever the triggering event happens, a trigger never accepts argument. A trigger cannot be used for a SELECT statement.

7.What are the triggers supported in oracle?

-DML triggers
-Instead of triggers
-DDL triggers
-Database event triggers

DML triggers

It is defined on a table and fires in response to an event like

- When a row is inserted to a table
- When a row is updated
- When a row is deleted

Instead of trigger

This trigger is created on views. You can either use Insert or Update or Delete or all three actions.

8.What are triggering attributes?

Triggering attributes are used to catch event when you want to identify or to perform certain actions.

They are as follows:

Inserting
Updating
Deleting

9.What is the difference between a function and a procedure in oracle?

A function always returns a value back to the calling block.

10.What are packages?

A package is an encapsulated collection of related schema objects. A package is compiled and then stored in the database's data dictionary as a schema objects. These objects can be procedure, functions, variables, constants, cursors and exceptions.

11.What is the difference between Anonymous blocks and sub programs ?

Ans :-

a)  Anonymous blocks are unnamed blocks which are not stored anywhere while sub programs are compiled and stored in database.

b) Anonymous blocks compile at run time.

12.What is the difference between DELETE and TRUNCATE ?

Ans:-

a) DELETE is a DML command and TRUNCATE is a DDL command.

b) TRUNCATE re-set the memory blocks after execution and much faster than DELETE in most of the circumstances.

13. What is Implicit Cursor and Explicit Cursor ?

a) Implicit Cursor is defined and controlled by Oracle Internally.

Example :-

declare
v_ename varchar2(50);
begin
select ename into v_ename from emp where empno = 10;
end;

select query used in above PL/SQL block is an implicit cursor

b) Explicit Cursor is defined and controlled programatically.

Example :-

declare
v_ename varchar2(50);
Cursor Cur_ename is select ename into v_ename from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_ename;
Close Cur_ename;
end;

14.What is the basic structure of PL/SQL?

PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.

PL/SQL block contains 3 sections.

The Declaration Section (optional)
The Execution Section (mandatory)
The Exception handling Section (optional)

15. What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?

Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must
 return a value specified in that type.

Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution
and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.

Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of
functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide
information from unauthorized users.

16.What is the difference between a function and a procedure in oracle?

A function always returns a value back to the calling block.

17.What are packages?

A package is an encapsulated collection of related schema objects. A package is compiled and then stored in the database's data dictionary as a schema objects. These objects can be procedure, functions, variables, constants, cursors and exceptions.
Explain the difference between GRANT and REVOKE command.

GRANT command is used to allow a user to perform certain activities on the database. The REVOKE command disallows the user from performing certain activities.

18.Explain the difference between ROLLBACK and COMMIT commands.

The COMMIT command is used to save the modifications done to the database values by the DML commands.

ROLLBACK command is used to undo the changes made by the DML commands. This ensures the values that existed prior to the changes can be achieved.

19.Define Row level trigger.

Row level trigger is fired each time a row is affected by DML statements like Insert, Update and Delete. When no rows affected, the trigger is not executed at all.
Define Statement level triggers.

It is fired when statement affects rows in a table but the processing required is completely independent of the number of rows affected.

20.Define Joins and its types.

A join is a query that extracts corresponding rows from two or more tables, views or snapshots.

Types:

Equi-joins
Non-equi joins
Self joins
Outer joins

Equi-join - information from two or more tables are retrieved by using equality conditions.

Self joins - Self join is a join that relates to itself.

Outer joins - Outer join fetch the rows from two tables which matches the join condition and the rows which don't match the join condition.
Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'

DBMS_SQL.EXECUTE function is used to execute cursor. It accepts the id of the cursor and returns the number of rows processed.
Syntax:
DBMS_SQL.EXECUTE ( c IN INTEGER) RETURN INTEGER;

Where c is the id of cursor.