Download as:
Rating : ⭐⭐⭐⭐⭐
Price: $10.99
Language:EN
Pages: 17
Words: 2226

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:

  1. 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

  1. Stored procedures provide better security to your data

  2. 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;
  1. Fetching the cursor for retrieving data

  2. 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;
Copyright © 2009-2023 UrgentHomework.com, All right reserved.