Compiled verified and stored the server database
Updating a View
Inserting Rows into a View
Types of Triggers
Creating a database trigger
What is Views?
A view is SQL statement and virtual table. It is stored in the database with a relevant name.
Syntax:
For Example:
View created. |
|---|
Updating a View:
Might not contain multiple tables.
The WHERE clause
SET Salary = 8000 WHERE Teacher_Name='Sini'; |
|---|
Syntax:
Dropping Views:
You can delete a view with the “DROP VIEW” command.
View Delete. |
|---|
What isTrigger?
Triggers might be described about the table or view or schema or database with which the event is associated.
Forms of Triggers
After triggers
Creating a Database Trigger
Whether row-level trigger or not
Condition to filter rows.
[BEFORE|AFTER}]
[DELETE|INSERT|UPDATE{OF columns}]
PL/SQL block
Figure: Execution sequence of database-triggers.
The following trigger is fired only when AMOUNT is more than 1000.
It can be dropped using DROP TRIGGER command .
Syntax:
| drop trigger payments_bu_row; |
|---|
BEFORE INSERT Trigger
Syntax
AFTER INSERT Trigger
Syntax
ON table-name {FOR EACH ROW} EXCEPTION WHEN.. |
|---|
BEFORE UPDATE Trigger
Syntax
AFTER UPDATE Trigger
Syntax
CREATE {OR REPLACE} TRIGGER trigger-name AFTER UPDATE BEGIN // trigger-code |
|---|
BEFORE DELETE Trigger
The syntax to create a BEFORE DELETE Trigger.
Syntax
AFTER DELETE Trigger
The syntax to create an AFTER DELETE Trigger.
Syntax
DECLARE // variable-declarations // exception-handling END; |
|---|
Stored procedures can be used for many different application purposes such as:
Distributing the logic between a client and a server
In SQL we are having different types of stored procedures are there
a) System Stored Procedures
Ex: sp_helptext [StoredProcedure_Name]
User Defined Stored Procedures:
There are two categories into which stored procedures can be divided:
SQL stored procedures
from the iSeries server to other DBMS. Implementation of the SQL stored procedures is
based on procedural SQL standardized in SQL99.
language must be compiled so that a program object is created. Then the CREATE
PROCEDURE statement is used to tell the system where to find the program object that
• Execute multiple DB operation at single call.
• Used to migrate data from one table to another table.
[local declarations]
BEGIN
OR REPLACE is used to create a procedure even a procedure with the same name is already existing.
To create a stored procedure the syntax is fairly simple:
Creating a stored function
A stored function is same as a procedure, except that it returns a value. CREATE FUNCTION command is used to create a stored function.
[local declarations]
BEGIN
END [name];
OR REPLACE is used to create a function even though a function with the same name already exists
Stored procedures provide better security to your data
Easy Maintenance
Oracle uses a work area to execute SQL commands and store processing information. PL/SQL allows you to access this area through a name using a cursor.
Cursors that you use in PL/SQL are of two types:
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.
When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
PL/SQL’s implicit cursor can handle only single-row queries. If you ever need to select more than one row using SELECT in PL/SQL then you have to use explicit cursor.
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, which is called as current row. When you fetch a row the current row position moves to next row.
| CURSOR cursor_name IS select_statement; |
|---|
Fetching the cursor for retrieving data
Closing the cursor to release allocated memory
Declaring the Cursor
Opening the Cursor
| OPEN c_customers; |
|---|
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example we will fetch rows from the above-opened cursor as follows:
Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close above-opened cursor as follows:
| CLOSE c_customers; |
|---|