Q 1) What is PL/SQL and What are its advantages and Disadvantages of PL/SQL?
PL/SQL (Procedural Language/Structured Query Language) was developed by Oracle Corporation in the 1990s as an extension of SQL (Structured Query Language) used in Oracle databases. It combines SQL’s data manipulation capabilities with procedural features like loops, conditions, and error handling.
Advantages of PL/SQL:
- High-Level Execution: Unlike SQL, where each statement executes individually, PL/SQL processes entire blocks of code containing multiple statements. This improves execution efficiency.
- Increased Performance: By executing entire blocks at once, PL/SQL reduces traffic between the database and applications, making it beneficial for handling large data volumes.
- Portability: PL/SQL code can be run on any Oracle database, ensuring that applications are portable across different environments.
- Error Handling: PL/SQL provides robust error-handling capabilities, allowing developers to catch and manage exceptions effectively.
- Procedural Capabilities: PL/SQL supports procedural features like loops, conditions, and variable declarations, enabling the creation of complex business logic and data manipulation.
Disadvantages of PL/SQL:
- Oracle-Specific: PL/SQL is specific to Oracle databases, limiting its portability to other database systems.
- Learning Curve: Developers may find PL/SQL challenging to learn, especially if they are not familiar with procedural programming or Oracle-specific features.
- Complexity: For simple tasks, the added complexity of PL/SQL might be unnecessary, and using standard SQL or other scripting languages could be more efficient.
Q 2) What is difference between the SQL and PL/SQL?
difference between the SQL and PL/SQL?
| SQL | PL/SQL |
|---|---|
| SQL (Structured Query Language) is a standard database language used for creating, maintaining, and retrieving relational databases. | PL/SQL (Procedural Language extensions to SQL) is a block-structured language that combines the power of SQL with procedural statements. It extends SQL by adding conditional constructs (like if...else) and loops (such as for and while loops). |
| In SQL, variables are not available. | PL/SQL supports variables, constraints, and data types. |
| SQL lacks control structures like for loops or if statements. | PL/SQL provides control structures such as for loops, while loops, and if conditions. |
| SQL is a data-oriented language. | PL/SQL is an application-oriented language. |
| SQL performs single operations (e.g., querying) one at a time. | PL/SQL processes groups of operations as a single block, reducing network traffic. |
| SQL is declarative, specifying what is needed without detailing how it should be done. | PL/SQL is procedural, allowing developers to write code with conditional logic and loops. |
| SQL is a standard language used across various relational database systems, though specific dialects and features may vary. | PL/SQL is specific to Oracle databases, meaning it is not portable across different database systems. |
Q 3) Explain the PL/SQL blockstructure?
In PL/SQL, a block is the basic unit of a program. The block structure allows developers to organize code in a logical and manageable way. Each PL/SQL block consists of three main sections:
- Declaration Section (Optional):
- This section begins with the DECLARE keyword.
- It is used to declare variables, constants, cursors, and exceptions.
- Executable Section (Mandatory):
- This section starts with the BEGIN keyword.
- It contains the actual PL/SQL code, including SQL statements, procedural constructs (such as loops and conditionals), and calls to other blocks or procedures.
- This is the only mandatory section of a PL/SQL block and must contain at least one executable statement.
Exception Handling Section (Optional):
- This section begins with the EXCEPTION keyword.
- It is used to handle runtime errors or exceptions that occur during the execution of the block.
Example of a PL/SQL Block
DECLARE
msg VARCHAR2(100);
BEGIN
msg := ‘Hello, PL/SQL!’;
DBMS_OUTPUT.PUT_LINE(msg);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An error occurred.’);
END;
In this example:
- The Declaration Section declares a variable ‘msg’.
- The Executable Section assigns a value to ‘msg’and prints it.
- The Exception Handling Section handles any unexpected errors.
Each PL/SQL block is enclosed by a BEGIN and END statement, defining the block’s boundaries.
Q 4) What is variable ? Explain it.
In PL/SQL, a variable is a named storage location that holds a value of a specific data type. These variables allow us to store temporary values within your program. Here’s how they work:
Declaration:
Before using a variable, we declare it in the declaration section of a PL/SQL block.
The syntax for declaring a variable is as follows:
variable_name datatype [ NOT NULL] [:= initial_value];
For example, we might declare three variables:
DECLARE
roll_no NUMBER(15, 2);
student_name VARCHAR2(255);
BEGIN
— Our code here
END;
Q 5) What do you mean by control structure? Explain it.
In PL/SQL, control structures are essential for managing the flow of execution within a program. These structures allow developers to dictate the order in which statements are executed based on specific conditions or loops.
There are three main types of control structures in PL/SQL:
- Conditional Control (IF Statements)
- Iterative Control (Loops)
- Sequential Control (GOTO Statements)
- Conditional Control (IF Statements)
Conditional control structures allow the execution of different sections of code based on certain conditions. The primary types of conditional control in PL/SQL are:
- IF-THEN: Executes a sequence of statements if the specified condition is true.
- IF-THEN-ELSE: Executes one sequence of statements if the condition is true and another sequence if it is false.
- IF-THEN-ELSIF-ELSE: Allows for multiple conditions, executing different sequences of statements based on which condition is true.
- Iterative Control (Loops) : Iterative control structures allow the execution of a block of statements repeatedly. PL/SQL supports several types of loops:
- BASIC LOOP: Repeats the enclosed statements indefinitely unless explicitly exited using an EXIT statement.
- WHILE LOOP: Repeats the enclosed statements as long as a specified condition is true.
- FOR LOOP: Repeats the enclosed statements a specific number of times, iterating over a range of values.
- Sequential Control (GOTO Statements)
The GOTO statement transfers control unconditionally to another part of the PL/SQL block. It is generally avoided in structured programming due to the potential for creating complex and hard-to-maintain code flows.
Q 6) What do you mean by IF Statements? Explain its types with example.
In PL/SQL, an IF statement is a conditional control structure that allows a program to execute different blocks of code based on certain conditions. It helps in decision-making by evaluating a boolean expression and directing the flow of execution accordingly.
Types of IF Statements in PL/SQL
- IF-THEN Statement
- IF-THEN-ELSE Statement
- IF-THEN-ELSIF-ELSE Statement
- IF-THEN Statement
The IF-THEN statement executes a sequence of statements if the condition evaluates to TRUE. If the condition is FALSE or NULL, the sequence of statements is skipped.
Example:
DECLARE
age NUMBER := 19;
BEGIN
IF age >= 18 THEN
DBMS_OUTPUT.PUT_LINE(‘Person is eligible to vote’);
END IF;
END;
In this example, the message will be printed because the condition age >= 18 is TRUE.
- IF-THEN-ELSE Statement
The IF-THEN-ELSE statement allows for an alternative sequence of statements to be executed if the condition evaluates to FALSE or NULL.
Example:
DECLARE
age NUMBER := 20;
BEGIN
IF age >= 18 THEN
DBMS_OUTPUT.PUT_LINE(‘Eligible to vote.’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Not eligible to vote.’);
END IF;
END;
In this example, the message “Eligible to vote.” will be printed because age >= 18 is TRUE.
- IF-THEN-ELSIF-ELSE Statement
The IF-THEN-ELSIF-ELSE statement allows for multiple conditions to be evaluated sequentially. If the first condition is FALSE or NULL, the next condition is checked, and so on. If none of the conditions are TRUE, the ELSE part (if provided) is executed.
DECLARE
grade CHAR(1) := ‘B’;
BEGIN
IF grade = ‘A’ THEN
DBMS_OUTPUT.PUT_LINE(‘Excellent’);
ELSIF grade = ‘B’ THEN
DBMS_OUTPUT.PUT_LINE(‘Good’);
ELSIF grade = ‘C’ THEN
DBMS_OUTPUT.PUT_LINE(‘Fair’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Needs Improvement’);
END IF;
END;
In this example, the message “Good” will be printed because grade = ‘B’ is TRUE. If grade were not ‘A’, ‘B’, or ‘C’, the ELSE part would execute.
Q 7) What do you mean by loop statements/ Iterative control? Explain its types with example.
In PL/SQL, loop statements or iterative control structures are used to repeat a set of statements multiple times. These structures allow developers to execute a block of code repeatedly, either a fixed number of times or until a certain condition is met.
Types of Loop Statements in PL/SQL
- BASIC LOOP
- WHILE LOOP
- FOR LOOP
- BASIC LOOP : A BASIC LOOP repeatedly executes a block of code indefinitely until an EXIT statement is explicitly encountered, which typically includes a condition to terminate the loop.
Example:
DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(‘Iteration: ‘ || counter);
counter := counter + 1;
EXIT WHEN counter > 5;
END LOOP;
END;
Output:
Iteration: 1, 2, 3, 4, 5
- WHILE LOOP
A WHILE LOOP repeats a block of code as long as a specified condition evaluates to TRUE. The condition is evaluated before the loop body is executed, meaning the loop might not execute at all if the condition is initially FALSE.
Example:
DECLARE
x NUMBER := 1;
BEGIN
WHILE x <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(‘Value of x: ‘ || x);
x := x + 1;
END LOOP;
END;
Output:
Value of x: 1, 2, 3, 4, 5
- FOR LOOP
A FOR LOOP repeats a block of code a specified number of times, iterating over a range of values. The loop variable is implicitly declared and cannot be altered within the loop.
Example:
DECLARE
total NUMBER := 0;
BEGIN
FOR i IN 1..5 LOOP
total := total + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total: ‘ || total);
END;
Output:
Total: 15
Q 8) What do you mean by Trigger ? Write its advantages?
In PL/SQL, a trigger is a stored procedure that is automatically executed, or “triggered,” by the database in response to specific events on a particular table or view. Triggers can be defined to execute before or after data modifications, such as INSERT, UPDATE, or DELETE operations, as well as other database events like LOGON, LOGOFF, or DDL (Data Definition Language) changes.
Advantages of Triggers:
- Data Validation: Triggers help validate data before it’s inserted or updated. For example, you can prevent invalid data from being added to a table.
- Business Logic Enforcement: Triggers enforce business rules consistently across the database. For instance, ensuring that an order cannot be deleted if it has associated payments.
- Audit Trail: Triggers can log changes (e.g., who modified a record and when) for auditing purposes.
- Complex Calculations: Use triggers to compute derived values or perform complex calculations during data modification.
- Scheduling Tasks: Triggers can schedule tasks (e.g., sending notifications) based on specific events.
- Reduced Client-Side Code: By handling logic within triggers, you reduce the need for application-level code.
Q 9) Explain the types of Trigger?
PL/SQL has four types of trigger which are given below-:
- Row-Level Triggers :- A row trigger is fired each time a row in the table is affected by triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If the triggering statement affects no rows, the trigger is not executed at all. Row triggers should be used when some processing is required whenever a triggering statement affects a single row in a table. Row triggers are created using each row clause in the CREATE TRIGGER command.
- Statement Level Triggers: A statement triggers is fired once on behalf of the triggering statement, independent of the number of rows the triggering statement affects (even if no rows are affected). Statement triggers should be used when a triggering statement affects rows in a table but the processing required is completely independent of the number of rows affected. Statement level trigger are the default type of triggers created via the CREATE TRIGGER commands.
- Before and After triggers: Because triggers are executed by events, they may be set to occur immediately before or after those events, since the events that execute trigger include database transactions, trigger can be executed immediately before and after inserts, updates and deletes.
- INSTEAD OF Triggers : INSTEAD OF triggers are used on views to handle DML operations that cannot be directly performed on the view itself. These triggers allow developers to define custom insert, update, or delete operations, thus providing a way to manage changes to complex views that involve multiple underlying tables.
Q 10) What do you mean by cursor? What are the different types of cursor?
An PL/SQL, a cursor is a database object that allows you to retrieve and manipulate rows from a query result set. Cursors provide a way to handle multiple rows returned by a query, enabling you to fetch, process, and manage data one row at a time.
Types of Cursors
- Implicit Cursors
- Explicit Cursors
- Implicit Cursors
Implicit Cursors are automatically created by Oracle when a SELECT INTO statement is executed, or when a DML operation (INSERT, UPDATE, DELETE) is performed. They handle the execution and management of the SQL statement without requiring explicit declaration or control from the developer. It is used for single-row queries. It provides information such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.
- Explicit Cursors: Explicit Cursors are defined and controlled by the developer. They are used for queries that return multiple rows and provide more control over fetching, processing, and handling of the result set.
Steps to Use an Explicit Cursor:
- Declare the Cursor: Define the cursor with a query.
- Open the Cursor: Execute the query and populate the result set.
- Fetch from the Cursor: Retrieve rows from the result set one at a time.
- Close the Cursor: Release the resources associated with the cursor.
Q 11) Explain the following attributes: i) %FOUND ii) %NOTFOUND iii) %ISOPEN iv)%ROWCOUNT
- %FOUND : The %FOUND attribute returns TRUE if the most recent SQL statement (either SELECT INTO or DML statement) affected one or more rows. It returns FALSE if no rows were affected or if an exception occurred.
- %NOTFOUND : The %NOTFOUND attribute returns the opposite of %FOUND. It returns TRUE if the most recent SQL statement did not affect any rows and FALSE otherwise. This attribute is often used to check if a SELECT INTO statement returned no rows or if a DML statement affected no rows.
- %ISOPEN :The %ISOPEN attribute indicates whether the implicit cursor is currently open. For implicit cursors, this attribute always returns FALSE because implicit cursors are managed automatically by Oracle and do not remain open for user management.
- %ROWCOUNT :The %ROWCOUNT attribute returns the number of rows affected by the most recent SQL statement. For a SELECT INTO statement, it indicates the number of rows fetched (typically 0 or 1). For DML statements, it shows the number of rows inserted, updated, or deleted.
