PL/SQL

1. What is PL/SQL? How is it different from SQL?
Answer: PL/SQL (Procedural Language/SQL) is Oracle's procedural extension to SQL. It allows you to write programs with procedural constructs like loops, conditions, and exceptions, which are not possible in SQL. While SQL is used to query and manipulate data, PL/SQL provides more control, flexibility, and the ability to define logic in the database layer.
 
2. What are the different types of PL/SQL blocks?
Answer: PL/SQL blocks can be of three types:
Anonymous Block: A block of code that is executed without being stored in the database.
Named Blocks (Procedures/Functions): Stored in the database with a name.
Packages: A collection of procedures, functions, and variables grouped together.
 
3. What is the purpose of the "DECLARE" section in PL/SQL?
Answer: The DECLARE section is used to declare variables, constants, cursors, and exceptions before the executable part of the PL/SQL block. It is optional, and if not needed, you can skip this section.
 
4. Explain the concept of anonymous blocks in PL/SQL.
Answer: A block of code that is executed without being stored in the database. An anonymous block is a PL/SQL block that doesn't have a name. It is often used for one-time operations or when the logic doesn't need to be reused. It consists of three parts: DECLARE, BEGIN, and END.
 
5. What are cursors in PL/SQL? Differentiate between implicit and explicit cursors.
Answer: Cursors are used to handle the result set of a query in PL/SQL.
Implicit Cursor: Automatically created by Oracle when a SELECT statement is executed in PL/SQL. No need for explicit declaration.
Explicit Cursor: Declared by the programmer to handle multi-row queries. The cursor is explicitly opened, fetched, and closed.
 
6. How can you handle exceptions in PL/SQL?
Answer: Exceptions in PL/SQL can be handled using the EXCEPTION block. When an error occurs, control is transferred to the EXCEPTION section, where you can handle predefined or user-defined exceptions.
For example:
BEGIN
    -- Code that may raise an error
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    -- Handle NO_DATA_FOUND exception
    WHEN OTHERS THEN
     -- Handle other exceptions
END;
 
7. What is the difference between “%ROWTYPE” and “%TYPE” in PL/SQL?
Answer:
%ROWTYPE: It is used to declare a record type that represents a row of a table or a view. It will have fields matching the columns of the table.
%TYPE: It is used to declare a variable that takes the data type of a column or a field of a table.

8. What is a trigger in PL/SQL? How do you create it?
Answer: A trigger is a named PL/SQL block that is automatically executed when a certain event occurs on a table (INSERT, UPDATE, DELETE). You create a trigger using the CREATE TRIGGER statement.
Syntax
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- Trigger code here
END;
 
9. Define Trigger. What are the different types of trigger events in PL/SQL?
Answer:
Trigger event: It indicates when to activate the trigger
Trigger supports 6 basic trigger events
1.Before insert
2.Before update    
3.Before delete          
4.After insert   
5.After update
6.After delete
 
10. What is the use of the "PRAGMA" keyword in PL/SQL?
Answer: PRAGMA is used to provide compiler directives to control the behaviour of PL/SQL code. Common examples are PRAGMA EXCEPTION_INIT (to associate an exception with a specific error code) and PRAGMA AUTONOMOUS_TRANSACTION (to make a procedure independent of the transaction).
 
11. What are the advantages of using PL/SQL over SQL?
Answer: PL/SQL provides the ability to:
1. Write procedural code like loops, conditionals, and error handling.
2.Define reusable procedures, functions, and packages.
3.Perform complex business logic in the database, reducing network traffic and improving performance.
4.Support exception handling for better error management.
 
12. How can you optimize a PL/SQL function?
Answer: Optimization of a PL/SQL function can be done by:
1.Reducing unnecessary SQL queries inside loops.
2.Using bulk operations like BULK COLLECT and FORALL for handling large datasets.
3.Minimizing the number of context switches between PL/SQL and SQL.
4.Using proper indexing on tables to speed up data retrieval.
 
13. What are collections in PL/SQL? Explain the different types.
Answer: Collections are data types that allow you to store multiple values in a single variable. The types of collections are:
1.Associative Arrays (Index-By Tables): Can be indexed by both integer and string values.
2.Nested Tables: Can store an unordered collection of elements and are similar to arrays in other programming languages.
3.Varrays: Fixed-size collections that are stored in the database.
 
14. What is the use of a cursor FOR loop in PL/SQL?
Answer: A cursor FOR loop simplifies the processing of a query result set by automatically handling the opening, fetching, and closing of the cursor.
For example:
declare
cursor my_cur is select * from emp;
begin
for i in my_cur
loop
dbms_output.put_line(i.ename||' '||i.sal||' '||i.empno);
end loop;
end;
 
15. How do you handle bulk data processing in PL/SQL?
Answer: To process bulk data efficiently, PL/SQL provides:
• BULK COLLECT: To fetch multiple rows at once into a collection.
• FORALL: To perform bulk DML operations like INSERT, UPDATE, or DELETE on a collection.
 
16. Explain the difference between “ASSIGN” and “SELECT INTO” in PL/SQL.
Answer:
ASSIGN: This is used to assign values to variables. For example:
v_emp_name := 'John';
SELECT INTO: This is used to fetch a single row of data from a table or view and assign it to a variable.
For example:
SELECT employee_name INTO v_emp_name FROM employees WHERE employee_id = 100;
The key difference is that SELECT INTO is used for retrieving data from the database, while ASSIGN is used for assigning values to variables directly.
 
17. What is the difference between a function and a procedure in PL/SQL?
Answer:
Function: A function is a PL/SQL block that returns a single value and can be used in SQL expressions. It must have a RETURN statement.
Procedure: A procedure is a PL/SQL block that does not return a value. It is typically used to perform an action like inserting or updating data.
 
18. How do you pass parameters to a PL/SQL procedure?
Answer: Parameters can be passed to a PL/SQL procedure in three ways:
IN Parameter (default): Passed as input to the procedure.
OUT Parameter: Used to return values from the procedure.
IN OUT Parameter: Used to both pass values to the procedure and return values from the procedure.
Example:
create or replace procedure myproce
(x in number , y in number , z out number)
is
begin
z:=x+y;
end myproce;
Calling Procedure
declare
res number;
begin
myproce(2,4,res);
dbms_output.put_line('The addition is '||res);
end;
Output:
The addition is 6
 
19. What are the different ways of handling errors in PL/SQL?
Answer: Errors in PL/SQL can be handled using:
EXCEPTION block: To handle predefined or user-defined exceptions.
BEGIN
    -- Code
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    -- Handle the error
END;
RAISE_APPLICATION_ERROR: Used to raise a user-defined exception with a custom error code and message.
RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
 
20. What is a package in PL/SQL? How do you create one?
Answer: A package in PL/SQL is a collection of related procedures, functions, variables, and other PL/SQL constructs grouped together for better modularity and code reuse.
Package Specification: Contains the declaration of types, variables, procedures, and functions.
Package Body: Contains the implementation of the procedures and functions.
 
21. What is the difference between a package specification and package body?
Answer:
Package Specification: It is the interface of the package. It declares the types, variables, procedures, and functions that can be accessed by other PL/SQL blocks or applications.
Package Body: It contains the actual implementation of the procedures, functions, and other elements declared in the specification. The body is not accessible outside the package.
 
22. What is the purpose of the “RETURN” statement in PL/SQL functions?
Answer: The RETURN statement is used to return a value from a PL/SQL function to the caller. It must be the last statement executed in the function.
Example:
CREATE FUNCTION calculate_bonus (salary IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURN salary * 0.1;
END;
 
23. What is a cursor operations?
Answer: Cursor Operations:
1.      Declaration of cursors: In declaration part we can declare the cursor
Syntax:
Cursor <cursor_name> is <select statement>;
Example:
Cursor my_cur is select ename from emp;
2.      Open <cursor_name>: It opens the cursor, Memory allocated after opened
Example:
Open my_cur;
3.      Fetch <cursor_name> into <pl/sql variables>
1.      It fetches data from cursor into pl/sql
2.      Fetch statement fetches one record at a time
Example : Fetch my_cur into v_name;

4.    close <cursor_name>

1. it closes the cursor

2. allocated memory will be de-allocated.

Example: Close my_cur;
 
24.What are the cursor attributes?
Answer: Cursor Attributes:
          It returns status of the cursor .
          Syntax:
          <cursor_name>%<attribute>;
1.      %isopen:
It returns true, when cursor opened successfully
2.      %found:
It returns true, when cursor contains data
3.      %notfound:
It returns true, when cursor doesn’t find any data
4.      %rowcount:
It returns number ie.., No of fetch statements are fetched.
 
25. What are stored procedures and how are they different from functions?
Answer:
Stored Procedures: A set of SQL statements that perform a specific task. They do not return values, but they can modify data and perform actions.
Functions: Similar to stored procedures, but they must return a single value.
 
26. How do you debug PL/SQL code?
Answer: Debugging PL/SQL code can be done using:
•DBMS_OUTPUT.PUT_LINE: To print intermediate values.
•PL/SQL Debugger: Provided by Oracle tools like SQL Developer or TOAD, which allow setting breakpoints, stepping through code, and inspecting variables.
 
27. What is the difference between “SAVEPOINT” and “ROLLBACK” in PL/SQL?
Answer:
SAVEPOINT: Marks a point in a transaction to which you can later roll back.
ROLLBACK: Rolls back all changes made after the last savepoint or the beginning of the transaction.
 
28. What is the difference between Assignment and camparision Operators?
Answer:
1.      := (Assignment Operator)
The := operator is used to assign a value to a variable. It is used when you are initializing or changing the value of a variable.
Where to use “:=”  When assigning a value to a variable.
 
2.       = (Comparision Operator)
The = operator is used to compare values. It checks whether two values are equal.
Where to use “=” When comparing two values in conditions (such as in IF statements, WHERE clauses, or loops).
Key Differences:
:= is used for assignment (setting a variable's value).
= is used for comparison (checking if two values are equal).
 
29. What are conditional control structures in PL/SQL?
Answer: Conditional control structures include:
IF statement
IF...ELSE statement
Nested IF
ELSIF ladder
 
30. What are iteration control structures in PL/SQL?
Answer: Iteration control structures execute a block of code repeatedly:
LOOP
WHILE LOOP
FOR LOOP