What is trigger ? Explain with an example
A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.
The Syntax for creating a trigger is:
CREATE [OR REPLACE ] TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF ]
[INSERT [OR] | UPDATE [OR] | DELETE}]
ON table_name
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- Sql statements
END;
For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.
We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.
1) Create the 'product' table and 'product_price_history' table
CREATE TABLE product_price_history
(
product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2)
);
CREATE TABLE product
(
product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2)
);
2) Create the price_history_trigger and execute it.
CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE OF unit_price
ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
VALUES
(:old.product_id,
:old.product_name,
:old.supplier_name,
:old.unit_price);
END;
/
3) Lets update the price of a product.
UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100
Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.
4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.
What is cursor ? explain with an example
[1] A cursor is a temporary work area created in the system memory when a SQL statement is executed.
[2] This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time.
There are two types of cursors in PL/SQL:
[1]Implicit cursors:
[2]Explicit cursors:
[1]Implicit cursors:These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
[2]Explicit cursors:They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time,
For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.
These are placeholders that store the values that can change through the PL/SQL Block.
The General Syntax to declare a variable is:
Variable_name datatype [NOT NULL: = value];
• variable_name is the name of the variable.
• datatype is a valid PL/SQL datatype.
• NOT NULL is an optional specification on the variable.
• value or DEFAULT valueis also an optional specification, where you can initialize a variable.
• Each variable declaration is a separate statement and must be terminated by a semicolon.
For example, if you want to store the current salary of an employee, you can use a variable.
DECLARE
salary number (6);
* “salary” is a variable of datatype number and of length 6.
When a variable is specified as NOT NULL, you must initialize the variable when it is declared.
For example: The below example declares two variables, one of which is a not null.
DECLARE
salary number(4);
dept varchar2(10) NOT NULL := “HR Dept”;
The value of a variable can change in the execution or exception section of the PL/SQL Block. We can assign values to variables in the two ways given below.
1) We can directly assign values to variables.
The General Syntax is:
variable_name:= value;
2) We can assign values to variables directly from the database columns by using a SELECT... INTO statement. The General Syntax is:
SELECT column_name
INTO variable_name
FROM table_name
[WHERE condition];
Example: The below program will get the salary of an employee with id '1116' and display it on the screen.
DECLARE
var_salary number(6);
var_emp_id number(6) = 1116;
BEGIN
SELECT salary
INTO var_salary
FROM employee
WHERE emp_id = var_emp_id;
dbms_output.put_line(var_salary);
dbms_output.put_line('The employee '
|| var_emp_id || ' has salary ' || var_salary);
END;
/
What is PL/SQL?
PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
A Simple PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.
A PL/SQL Block consists of three sections:
• The Declaration section (optional).
• The Execution section (mandatory).
• The Exception (or Error) Handling section (optional).
Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.
This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section.
Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.
Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END.
This is a mandatory section and is the section where the program logic is written to perform any task.
The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.
Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION.
This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully.
If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.
This is how a sample PL/SQL Block looks.
