Unit 4
Q1. List two advantages of PL/SQL..
Ans.
1. Tight Integration with SQL:
• PL/SQL is tightly integrated with SQL, the standard database query language. It allows you to write blocks of code that combine procedural logic (such as loops and conditionals) with SQL statements. This integration makes it easy to manipulate data and perform complex operations directly within the database, reducing the need for multiple trips between the application and the database server, which enhances performance.
2. Support for Procedural Logic:
• PL/SQL extends SQL by adding procedural constructs like variables, loops, conditionals (if-else statements), and exception handling. This enables the creation of powerful, flexible, and reusable code blocks such as functions, procedures, and triggers. This procedural logic allows developers to write more complex and maintainable code that can handle various scenarios within a single PL/SQL program.
Summer 2022
Q2. Explain block structure of PL/SQL..
Ans.
The block structure in PL/SQL (Procedural Language/Structured Query Language) organizes the code into logical sections, making it easy to manage, understand, and debug. A PL/SQL block consists of three main sections: declaration, execution, and exception handling. The execution section is mandatory, while the declaration and exception handling sections are optional.
DECLARE
-- Declarations of variables, constants, and cursors (optional)
BEGIN
-- Executable statements (mandatory)
EXCEPTION
-- Exception handling code (optional)
END;
Q3. Explain conditional control in PL/SQL with example.
Ans.
In PL/SQL, conditional control allows you to execute specific blocks of code based on certain conditions. This is achieved using control structures like IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF, as well as the CASE statement. These control structures let you introduce decision-making in your PL/SQL programs, allowing you to execute different paths of code based on the evaluation of conditions.
Types of Conditional Control in PL/SQL:
1. IF-THEN: Executes a block of code if a specified condition is TRUE.
2. IF-THEN-ELSE: Executes one block of code if the condition is TRUE and another if the condition is FALSE.
3. IF-THEN-ELSIF-ELSE: Allows multiple conditions to be tested sequentially.
4. CASE Statement: Provides an alternative way of evaluating multiple conditions similar to the switch statement in other programming languages.
Q4. Write and explain syntax for creating Trigger.
Ans.
A trigger in PL/SQL is a stored program that automatically executes or fires when a specific event occurs on a table or view. It is mainly used to enforce business rules, validate data, or keep audit logs. Triggers can be fired before or after INSERT, UPDATE, or DELETE operations on a table.
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
DECLARE
-- Declarations (optional)
BEGIN
-- Executable code to run when the trigger fires
EXCEPTION
-- Exception handling code (optional)
END;
Explanation of the Syntax:
1. CREATE [OR REPLACE] TRIGGER trigger_name:
o This creates a new trigger with the specified name. The optional OR REPLACE clause allows you to recreate the trigger if it already exists without first dropping it.
2. {BEFORE | AFTER | INSTEAD OF}:
o Defines when the trigger should be executed relative to the DML operation.
BEFORE: The trigger fires before the operation is performed.
AFTER: The trigger fires after the operation is performed.
INSTEAD OF: Used for views, to define trigger actions that replace the default DML operations on a view.
3. {INSERT | UPDATE | DELETE}:
o Specifies the type of DML operation (or multiple operations) that will trigger the execution. You can combine operations, e.g., INSERT OR UPDATE OR DELETE.
4. ON table_name:
o Specifies the name of the table or view on which the trigger is defined.
5. [FOR EACH ROW]:
o Specifies that the trigger is a row-level trigger and will fire once for each row affected by the DML operation. If omitted, the trigger is statement-level and fires only once for the entire statement.
6. [WHEN (condition)]:
o This is an optional condition that determines whether the trigger should be fired. If the condition evaluates to true, the trigger fires. Otherwise, it does not.
7. DECLARE:
o This section is optional and is used to declare variables, cursors, or constants that are required within the trigger's body.
8. BEGIN:
o Contains the executable PL/SQL code that defines the actions performed by the trigger when it fires.
9. EXCEPTION:
o This optional section is used to handle any exceptions or errors that might occur during the execution of the trigger.
Example
CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT
ON employees
FOR EACH ROW
WHEN (NEW.salary < 5000)
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least 5000');
END;
Q5. Write a PL/SQL program, which accept the number from user. If user enters an odd number then exception invalid number is raised using user defined exception handling.
Ans.
DECLARE
user_number int;
odd_number_ex EXCEPTION;
--Procedure to check if the number is odd
PROCEDURE check_odd_number (num IN int) IS
BEGIN
IF MOD(num, 2) <> 0 THEN
RAISE odd_number_ei. END IF;
END;
BEGIN
--Accepting input from the user
DBMS_OUTPUT.PUT_LINE('Enter a number:');
--Using substitution variable to get the user input
user_number: &user_number;
-- Call the procedure to check if the number is odd
check_odd_number(user_number);
-- If no exception is raised, the number is even
DBMS_OUTPUT.PUT_LINE('The number is even.');
EXCEPTION
WHEN odd number ex THEN
DBMS_OUTPUT.PUT_LINE('Invalid number: The number is odd.');
END;
Winter 2022
Q6. Write a PL/SQL code to print reverse of a number.
Ans.
DBMS_OUTPUT.PUT_LINE('reverse is 'rev);
DECLARE
n number;
i number;
rev number:=0;
r number;
BEGIN
n:=&n;
while n>0
LOOP r:=mod(n,10);
rev:=(rev*10)+r;
n: trunc(n/10);
END LOOP;
END;
/
Q7. Explain the steps of cursor implementation with syntax and example.
Ans. In SQL, a cursor is used to retrieve, manipulate, and traverse rows returned by a query one row at a time, especially useful in procedural database operations. Here’s a step-by-step guide on cursor implementation with syntax and an example.
Steps to Implement a Cursor in SQL
1. Declare the Cursor: Define a cursor with a DECLARE statement, specifying the SQL query to fetch records.
2. Open the Cursor: Use the OPEN statement to execute the SQL query and load the result set into the cursor.
3. Fetch Data from the Cursor: Retrieve each row from the cursor one at a time using FETCH.
4. Process Each Row: Perform the necessary operations on each fetched row.
5. Close the Cursor: When done, use the CLOSE statement to release the cursor.
6. Deallocate the Cursor: Use DEALLOCATE to free the memory associated with the cursor.
SQL Syntax
DECLARE cursor_name CURSOR FOR
SELECT column1, column2, ...
FROM table_name
WHERE condition;
OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO variable1, variable2, ...;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Perform operations on fetched data
FETCH NEXT FROM cursor_name INTO variable1, variable2, ...;
END;
CLOSE cursor_name;
DEALLOCATE cursor_name;
Example of a Cursor in SQL
Let's say we have a table Employees with columns EmployeeID, EmployeeName, and Salary. We want to increase the salary of each employee by 10%.
-- Step 1: Declare variables to store cursor data
DECLARE @EmployeeID INT, @EmployeeName NVARCHAR(50), @Salary DECIMAL(10,2);
-- Step 2: Declare the cursor
DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, EmployeeName, Salary FROM Employees;
-- Step 3: Open the cursor
OPEN employee_cursor;
-- Step 4: Fetch the first row from the cursor
FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName, @Salary;
-- Step 5: Loop through the rows until there are no more rows
WHILE @@FETCH_STATUS = 0
BEGIN
-- Increase salary by 10%
UPDATE Employees
SET Salary = @Salary * 1.10
WHERE EmployeeID = @EmployeeID;
-- Fetch the next row
FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName, @Salary;
END;
-- Step 6: Close and deallocate the cursor
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
Q8. Write a trigger which invokes on deletion of record on emp table.
Ans.
CREATE OR REPLACE trigger trg1 BEFORE delete ON emp_details
DECLARE
raise_application_error(-20000, 'cannot delete the record');
BEGIN
END;
/
----------------------------------------------Second Program---------------------------------------------
CREATE OR REPLACE TRIGGER log_emp_deletions
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
INSERT INTO emp_deletions (empno, ename, job, deleted_date)
VALUES (:empno, :ename, : job, SYSDATE);
END;
/
Q9. Write a PL/SQL code to check whether specified employee is present in Emp table or not. Accept empno from user. If employee does not exist.
Ans.
DECLARE
no emp.empno%type;
BEGIN
no:=&no;
select empno into no from emp where empno-no;
DBMS_OUTPUT.PUT_LINE('Empno is present: '||no);
EXCEPTION when no_data_found then
DBMS_OUTPUT.PUT_LINE('Empno not present');
end;
/
-------------------------------------------------Second Program------------------------------------------
DECLARE
v_empno NUMBER; -- Variable to hold employee number
v_count NUMBER := 0; -- Variable to store count of rows
BEGIN
-- Accept empno from user
v_empno := &empno;
-- Check if employee exists in the Emp table
SELECT COUNT(*) INTO v_count FROM Emp
WHERE empno = v_empno;
-- Conditional check
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('Employee with empno ' || v_empno || ' is present in the Emp table.');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee with empno ' || v_empno || ' does not exist in the Emp table.');
END IF;
END;
/
Summer 2023
Q10. State syntax of while loop command.
Ans. In SQL, the WHILE loop is primarily used in procedure.
Syntax of WHILE Loop
WHILE condition
BEGIN
-- Statements to execute
END;
Explanation of Syntax
• WHILE condition: The loop executes as long as condition is true. This condition is typically a Boolean expression.
• BEGIN...END: The block of SQL statements that will execute in each iteration of the loop. If the loop condition is still true after running the statements, it repeats; if false, the loop exits.
Q11. Write a PL-SQL program to print odd numbers from 1 to 10.
Ans.
BEGIN
DBMS_OUTPUT.PUT_LINE (Oddnos from 1 to 10 are:");
FOR num in 1..10 LOOP
IF(MOD(num, 2)!=0) THEN
DBMS_OUTPUT.PUT_LINE(num);
END IF;
DBMS OUTPUT.PUT_LINE(num);
END IF;
END LOOP;
END;
LOOP
Q12. Explain for loop syntax in PL-SQL with example of printing 10 to 1 reverse number
Ans. In PL/SQL, the FOR loop is used to iterate through a range of values or execute a block of code a specified number of times.
ntax of the FOR Loop in PL/SQL
FOR loop_variable IN [REVERSE] lower_bound .. upper_bound
LOOP
-- Statements to execute in each iteration
END LOOP;
Example
BEGIN
FOR i IN REVERSE 1 .. 10
LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/
Explanation of the Example
1. FOR i IN REVERSE 1 .. 10: The FOR loop iterates over the range from 10 down to 1, using the REVERSE keyword.
2. DBMS_OUTPUT.PUT_LINE(i);: Prints the current value of i in each iteration.
Q13. Explain function in PL-SQL with suitable example.
Ans. In PL/SQL, a function is a named block of code that performs a specific task and returns a single value. Functions in PL/SQL are often used to perform calculations, manipulate data, or return values based on specific logic. Unlike procedures, functions are designed to return a value, which can be used directly in SQL statements.
Syntax for Creating a Function in PL/SQL
CREATE OR REPLACE FUNCTION function_name (parameter1 datatype, parameter2 datatype, ...)
RETURN return_datatype
IS
-- Declaration section (variables, constants, etc.)
BEGIN
-- Executable statements
-- Logic to compute or perform an action
RETURN value; -- Return a single value
END function_name;
/
Example: Creating a Function to Calculate Employee Bonus
Suppose we have an Employees table with columns EmployeeID, EmployeeName, and Salary. We want to create a function that calculates a bonus based on an employee’s salary, using a percentage provided as a parameter.
CREATE OR REPLACE FUNCTION calculate_bonus (
p_salary NUMBER,
p_bonus_percentage NUMBER
) RETURN NUMBER
IS
v_bonus NUMBER; -- Local variable to store the calculated bonus
BEGIN
-- Calculate the bonus as a percentage of the salary
v_bonus := p_salary * (p_bonus_percentage / 100);
-- Return the calculated bonus
RETURN v_bonus;
END calculate_bonus;
/
To use the function, you can call it from within a PL/SQL block, or in a SQL query:
DECLARE
v_salary NUMBER := 5000;
v_bonus_percentage NUMBER := 10;
v_bonus NUMBER;
BEGIN
-- Call the function and store the result in v_bonus
v_bonus := calculate_bonus(v_salary, v_bonus_percentage);
DBMS_OUTPUT.PUT_LINE('The bonus is: ' || v_bonus);
END;
/
Winter 2023
Q14. Define cursor. Enlist the types of cursors.
Ans. A cursor in PL/SQL is a pointer to the context area in memory that holds the result set of a query. It allows the PL/SQL program to fetch and manipulate rows returned by the SQL statement one at a time, especially when dealing with multi-row queries.
In PL/SQL, cursors are used to retrieve rows from a result set. Cursors allow row-by-row processing of the query result, providing a way to handle multiple rows in PL/SQL code.
Types of Cursors:
PL/SQL supports two types of cursors:
1. Implicit Cursor
2. Explicit Cursor
1. Implicit Cursor:
1. Automatically created by Oracle for single-row queries or DML statements.
2. No need for explicit handling (opening, fetching, closing).
3. Provides minimal control.
2. Explicit Cursor:
1. Defined by the user for multi-row queries.
2. Requires explicit declaration, opening, fetching, and closing.
3. Provides full control over the query execution and row-by-row processing.
Cursors are a powerful tool in PL/SQL for handling and processing query results, especially when multiple rows need to be processed in a sequential manner.
Q15. Describe exception handling in PL/SQL.
Ans. Exception handling in PL/SQL refers to the process of managing and responding to errors or unexpected situations that may arise during the execution of a PL/SQL program. PL/SQL provides a robust exception-handling mechanism that allows you to detect, handle, and recover from errors in a controlled manner, ensuring the stability and integrity of the application.
An exception is a runtime error or warning condition. In PL/SQL, when an error occurs, normal execution halts, and the control is transferred to the exception-handling section of the code, where the error can be dealt with appropriately.
Types of Exceptions in PL/SQL
PL/SQL exceptions can be classified into two main categories:
1. Predefined Exceptions: These are exceptions that are automatically raised by Oracle for common errors (e.g., NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE). They are implicitly defined by PL/SQL and have corresponding names.
2. User-Defined Exceptions: These are custom exceptions that are explicitly declared by the developer. They allow you to define and raise exceptions specific to the application or business logic.
Q16. Explain Procedure in PL/SQL with example.
Ans. In PL/SQL, a procedure is a named block of code that performs a specific task but does not necessarily return a value (unlike a function, which must return a single value). Procedures can accept parameters and can modify data, perform actions, or execute multiple SQL statements within a single call. They are commonly used to encapsulate logic, enforce business rules, or perform repetitive tasks.
Syntax for Creating a Procedure in PL/SQL
CREATE OR REPLACE PROCEDURE procedure_name (
parameter1 [IN | OUT | IN OUT] datatype,
parameter2 [IN | OUT | IN OUT] datatype, ...
)
IS
-- Declaration section (variables, constants, etc.)
BEGIN
-- Executable statements
END procedure_name;
/
Example: Creating a Procedure to Update Employee Salary
Suppose we have an Employees table with columns EmployeeID, EmployeeName, and Salary. We’ll create a procedure that takes an employee ID and a percentage increase, and then updates the employee’s salary based on this percentage.
CREATE OR REPLACE PROCEDURE update_salary (
p_emp_id IN NUMBER,
p_increase_percentage IN NUMBER,
p_new_salary OUT NUMBER
)
IS
BEGIN
-- Update the employee's salary with the specified percentage increase
UPDATE Employees
SET Salary = Salary + (Salary * (p_increase_percentage / 100))
WHERE EmployeeID = p_emp_id;
-- Retrieve the new salary after the update
SELECT Salary INTO p_new_salary FROM Employees
WHERE EmployeeID = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Salary updated successfully.');
END update_salary;
/
Q17. Compare database triggers and procedures
Ans.
While triggers and procedures both play important roles in managing database logic and operations, they differ significantly in terms of functionality, usage, and control. Below is a detailed comparison between database triggers and stored procedures.
1. Definition:
• Trigger:
o A trigger is a special kind of stored program that automatically executes (or "fires") in response to specific events such as INSERT, UPDATE, or DELETE operations on a table or view. Triggers are typically used to enforce data integrity, audit changes, or automate business logic.
• Procedure:
o A procedure is a named PL/SQL block that performs one or more tasks. Procedures must be explicitly invoked by a user or application to execute. They can take parameters and return values (via OUT parameters) to the caller.
________________________________________
2. Invocation:
• Trigger:
o Triggers are implicitly invoked or fired automatically by Oracle when a specific event occurs, such as a DML (INSERT, UPDATE, DELETE) operation on a table or DDL operation.
o They cannot be called directly by users or applications.
• Procedure:
o Procedures are explicitly invoked by the user or an application. They can be called from PL/SQL blocks, other procedures, or even triggers.
________________________________________
3. Purpose:
• Trigger:
o Triggers are used to automate actions based on specific database events, such as:
Enforcing complex business rules.
Auditing changes to sensitive data.
Automatically logging changes to a table.
Enforcing referential integrity.
• Procedure:
o Procedures are generally used to perform specific tasks or operations in the database, such as:
Complex data manipulation.
Business logic implementation.
Reusable actions (e.g., updating a set of rows).
Batch processing of data.
________________________________________
4. Execution Control:
• Trigger:
o Execution is automatic: A trigger is fired automatically whenever the specified event occurs on the associated table or view.
o The user has no direct control over when a trigger executes.
• Procedure:
o Execution is manual: A procedure must be explicitly invoked using a CALL or an anonymous block, either by a user, application, or another PL/SQL block.
________________________________________
5. Parameters:
• Trigger:
o Triggers do not accept parameters. The context in which they run (i.e., the table or operation that caused the trigger to fire) is fixed and implicit.
o They work within the context of the affected row(s) using special variables like :OLD and :NEW.
• Procedure:
o Procedures can accept parameters in the form of IN, OUT, and IN OUT parameters. This allows the caller to pass arguments into the procedure and receive output back from the procedure.
________________________________________
6. Timing of Execution:
• Trigger:
o Triggers can be defined to fire BEFORE or AFTER a specific DML operation (e.g., before an INSERT, or after a DELETE).
o They are tightly bound to the timing of the database event.
• Procedure:
o Procedures are not bound by any automatic timing mechanism. They execute only when they are called explicitly.
o Their execution is controlled by the user or program invoking them.
________________________________________
7. Association with Database Objects:
• Trigger:
o Triggers are tied to specific database objects (like tables or views). They fire when an event occurs on the associated object.
o For example, a trigger may be defined to fire on an UPDATE operation on the employees table.
• Procedure:
o Procedures are standalone or stored within packages and are not tied to specific database objects.
o They can be invoked from anywhere in the PL/SQL environment.
8. Execution Context (Row-Level or Statement-Level):
• Trigger:
o Triggers can be row-level or statement-level:
Row-Level Triggers: Execute once for each row affected by the DML operation.
Statement-Level Triggers: Execute once for the entire DML operation, regardless of how many rows are affected.
• Procedure:
o Procedures do not have row-level or statement-level execution distinctions. They execute as a whole, based on the logic inside the procedure.
________________________________________
9. Rollback and Commit:
• Trigger:
o Triggers cannot perform transaction control (i.e., you cannot explicitly issue COMMIT or ROLLBACK inside a trigger).
o They are part of the transaction that caused them to fire and must wait until the end of the transaction for the commit or rollback to occur.
• Procedure:
o Procedures can perform transaction control and can explicitly issue COMMIT, ROLLBACK, or SAVEPOINT commands, thus controlling the transaction.
________________________________________
10. Complexity and Maintenance:
• Trigger:
o Triggers can add complexity to the system because they are fired automatically and can affect performance and data integrity if not used carefully.
o Debugging and understanding the cause of errors can be more challenging, especially if multiple triggers exist on the same table.
• Procedure:
o Procedures are easier to debug and maintain because their execution is explicit and controlled.
o They are modular, reusable blocks of code, which makes them easier to test and modify.
------------------------------------------------------------------------------------------------------------
|
Feature |
Trigger |
Procedure |
|
Invocation |
Automatically, based on events (e.g.,
INSERT) |
Explicitly, called by a user or application |
|
Purpose |
Enforce business rules, audit, automate
actions |
Perform tasks, implement business logic |
|
Parameters |
Cannot accept parameters |
Can accept IN, OUT, and IN OUT parameters |
|
Execution Control |
Fires automatically, no direct control |
Executed manually |
|
Association |
Bound to database objects (tables, views) |
Independent, not tied to specific database
objects |
|
Timing |
Defined by DML timing (BEFORE, AFTER) |
Not tied to DML timing |
|
Row/Statement Level |
Can be row-level or statement-level |
Executes as a whole block |
|
Transaction Control |
Cannot issue COMMIT or ROLLBACK |
Can issue COMMIT, ROLLBACK, SAVEPOINT |
|
Use Cases |
Enforcing constraints, auditing, logging
changes |
Reusable logic, batch processing, complex
tasks |
|
Complexity |
Can be complex, harder to debug |
Easier to debug and maintain |
Q18. Write a PL/SQL program to print sum of n odd numbers using for loop.
Ans.
DECLARE
I number-1, n number, sum number:0;
BEGIN
n := &n;
FOR 1 in 1..n LOOP
IF MOD(1,2) 1 THEN sum sum+ 1;
END IF;
END LOOP,
DBMS_OUTPUT.PUT_LINE('sum sum);
END;
Summer 2024
Q19. Write PL/SQL code to print largest number from three numbers, (accept three numbers from user).
Ans.
DECLARE
a number (2), b number(2);
c number(2);
BEGIN
a:-&a; b:-&b;
c:-&c;
IF a>b and axc THEN
DBMS_OUTPUT.PUT_LINE('Largest No '||a);
ELSIF b>c then
DBMS_OUTPUT.PUT_LINE('Largest No '||b);
ELSE DBMS_OUTPUT.PUT_LINE('Largest No '||c);
END IF;
END;
Q20. Write PL/SQL program to print number of employee working in specified department consider emp table and accept dept-no, from user.
Ans.
SET SERVEROUTPUT ON;
v_emp count NUMBER; BEGIN
DECLARE
v_dept noemp.dept_noXTYPE;
--Accept department number from the user
v_dept_no: &dept_no;
--Retrieve count of employees in the specified department
SELECT COUNT(*) INTO v_emp_count FROM emp
WHERE dept nov_dept_no;
--Print the count of employees DBMS_OUTPUT.PUT_LINE('Number of employees in department || v_dept_no ||
EXCEPTION
WHEN NO DATA FOUND THEN
|| v_emp_count);
DBMS_OUTPUT.PUT_LINE('No employees found in department' || v_dept_no);
Q21. Differentiate between PL/SQL procedure and function with syntax and example.
Ans. In PL/SQL, both procedures and functions are named blocks that encapsulate SQL and PL/SQL code, but they have different purposes and characteristics.
|
Feature |
Procedure |
Function |
|
Purpose |
Performs a specific action or task |
Performs a calculation and returns a value |
|
Return Type |
Does not return a value directly |
Must return a single value |
|
Return Statement |
Optional, used for control flow |
Mandatory to return a specific value |
|
Usage in SQL |
Cannot be called directly in SQL statements |
Can be called directly in SQL statements (e.g., SELECT, WHERE) |
|
Parameter Modes |
Supports IN, OUT, and IN OUT |
Only IN
parameter mode is allowed |
|
Calling Syntax |
CALL or EXEC command in PL/SQL |
Can be called within expressions or SQL queries |
Syntax Comparison
Procedure Syntax
CREATE OR REPLACE PROCEDURE procedure_name ( parameter1 [IN | OUT | IN OUT] datatype, parameter2 [IN | OUT | IN OUT] datatype, ... )
IS BEGIN -- Statements to execute
END procedure_name; /
Function Syntax
CREATE OR REPLACE FUNCTION function_name ( parameter1 IN datatype, parameter2 IN datatype, ... ) RETURN return_datatype IS
BEGIN -- Statements to execute
RETURN value;
END function_name; /
Q22. Explain use of database trigger. List types of trigger. Write command to create and delete trigger.
Ans.
A database trigger is a stored procedure that is automatically executed when specific events occur in the database, such as INSERT, UPDATE, or DELETE operations on a table or view. Triggers are used to automate tasks, enforce complex business rules, ensure data integrity, and maintain audit logs within the database.
Uses of Database Triggers
1. Enforcing Business Rules:
o Triggers can enforce specific rules that go beyond standard constraints (like CHECK and UNIQUE). For example, preventing changes to salary information if it exceeds a certain limit.
2. Data Integrity and Consistency:
o Triggers ensure data consistency across related tables. For instance, they can update related data in other tables to reflect changes, such as updating a total sales amount in a summary table whenever a new sale is recorded.
3. Audit Logging:
o Triggers can track changes made to critical tables by logging each INSERT, UPDATE, or DELETE operation, creating an audit trail of who made changes and when.
4. Automatic Calculations and Actions:
o Triggers can automate calculations and processes, such as calculating discounts, updating inventory levels, or sending notifications after specific database events.
Types of Triggers
1. DML Triggers (Data Manipulation Language):
o These are activated by INSERT, UPDATE, or DELETE operations on a table or view.
o Types of DML triggers:
BEFORE Trigger: Executes before the DML operation (like BEFORE INSERT or BEFORE UPDATE). Useful for validation before the data is committed.
AFTER Trigger: Executes after the DML operation. Useful for actions that need to occur only if the DML statement completes successfully.
INSTEAD OF Trigger: Typically used on views, it replaces the DML operation with the trigger’s code.
2. DDL Triggers (Data Definition Language):
o Fired by DDL statements like CREATE, ALTER, and DROP. Useful for auditing schema changes.
3. Database Triggers:
o Fired by system events (e.g., database startup or shutdown, user logon or logoff). Useful for maintaining application-wide logic or logging system-level events.
Command to Create a Trigger
Here’s an example of creating an AFTER INSERT trigger to log insert actions on an Employees table into an Employee_Log table.
CREATE OR REPLACE TRIGGER log_employee_inserts
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Employee_Log (EmployeeID, Action, ActionDate)
VALUES (:NEW.EmployeeID, 'INSERT', SYSDATE);
END;
/
Example: Creating a Trigger
CREATE OR REPLACE TRIGGER log_employee_inserts
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Employee_Log (EmployeeID, Action, ActionDate)
VALUES (:NEW.EmployeeID, 'INSERT', SYSDATE);
END;
/
No comments:
Post a Comment