Programming in almost language

This is the site where you may share your knowledge and experience to eachother..

  • Categories

  • LinkedIn

  • Tweet Me

  • My footsteps

Archive for the ‘Database tutorial’ Category

Cursor

Posted by Praveen Kumar on April 30, 2008

Definition: Cursors are database objects used to traverse the results of an SQL query. They point to a certain location within a recordset and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time. Cursors are often criticized for their high overhead.

In a simple way

A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement.

A record pointer in a database. When a database file is selected and the cursor is opened, the cursor points to the first record in the file. Using various commands, the cursor can be moved forward, backward, to top of file, bottom of file and so forth.

I have categorized the cursor into the following topics:

Declare a Cursor
OPEN Statement
FETCH Statement
CLOSE Statement
Cursor Attributes (%FOUND, %NOTFOUND, etc)
SELECT FOR UPDATE Statement
WHERE CURRENT OF Statement

Declare a Cursor

The DECLARE CURSOR statement names a cursor and specifies a select-statement. The select-statement defines a set of rows that, conceptually, make up the result table. For a serial cursor, the statement looks like this (the FOR UPDATE OF clause is optional):

DECLARE cursor-name CURSOR FOR

SELECT column-1, column-2 ,…

FROM table-name , …

FOR UPDATE OF column-2 ,…

For a scrollable cursor, the statement looks like this (the WHERE clause is optional):

DECLARE cursor-name DYNAMIC SCROLL CURSOR FOR

SELECT column-1, column-2 ,…

FROM table-name ,…

WHERE column-1 = expression …

Cursor without parameters (simplest)

The basic syntax for a cursor without parameters is:

CURSOR cursor_name
IS
SELECT_statement;

For example, you could define a cursor called c1 as below.

CURSOR c1
IS
SELECT course_number from courses_tbl where course_name = name_in;

The result set of this cursor is all course_numbers whose course_name matches the variable called name_in.

Below is a function that uses this cursor.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;
close c1;

RETURN cnumber;

END;

Cursor with parameters

The basic syntax for a cursor with parameters is:

CURSOR cursor_name (parameter_list)
IS
SELECT_statement;

For example, you could define a cursor called c2 as below.

CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number  from courses_tbl where subject_id=subject_id_in;

The result set of this cursor is all course_numbers whose subject_id matches the subject_id passed to the cursor via the parameter.

Cursor with return clause

The basic syntax for a cursor with a return clause is:

CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_statement;

For example, you could define a cursor called c3 as below.

CURSOR c3
RETURN courses_tbl%ROWTYPE
IS
SELECT * from courses_tbl   where subject = ‘Mathematics’;

The result set of this cursor is all columns from the course_tbl where the subject is Mathematics.

Step2 : OPEN Statement

Once you’ve declared your cursor, the next step is to open the cursor.

The basic syntax to OPEN the cursor is:

OPEN cursor_name;

For example, you could open a cursor called c1 with the following command:

OPEN c1;

Below is a function that demonstrates how to use the OPEN statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;

close c1;RETURN cnumber;

END;

Step3 : FETCH Statement

The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.

The basic syntax for a FETCH statement is:

FETCH cursor_name INTO <list of variables>;

For example, you could have a cursor defined as:

CURSOR c1
IS
SELECT course_number from courses_tbl  where course_name = name_in;

The command that would be used to fetch the data from this cursor is:

FETCH c1 into cnumber;

This would fetch the first course_number into the variable called cnumber;

Below is a function that demonstrates how to use the FETCH statement.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;

RETURN cnumber;

END;

Step4 : CLOSE Statement

The final step of working with cursors is to close the cursor once you have finished using it.

The basic syntax to CLOSE the cursor is:

CLOSE cursor_name;

For example, you could close a cursor called c1 with the following command:

CLOSE c1;

Below is a function that demonstrates how to use the CLOSE statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;

RETURN cnumber;

END;

Cursor Attributes (%FOUND, %NOTFOUND, etc)

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Attribute Explanation
%ISOPEN – Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND – Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. – Returns NULL if cursor is open, but fetch has not been executed.

– Returns TRUE if a successful fetch has been executed.

– Returns FALSE if no row was returned.

%NOTFOUND – Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. – Return NULL if cursor is open, but fetch has not been executed.

– Returns FALSE if a successful fetch has been executed.

– Returns TRUE if no row was returned.

%ROWCOUNT – Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. – Returns the number of rows fetched.

– The ROWCOUNT attribute doesn’t give the real row count until you have iterated through the entire cursor. In other words, you shouldn’t rely on this attribute to tell you how many rows are in a cursor after it is opened.

SELECT FOR UPDATE Statement

The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

CURSOR cursor_name
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];

For example, you could use the Select For Update statement as follows:

CURSOR c1
IS
SELECT course_number, instructor  from courses_tbl  FOR UPDATE of instructor;

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

WHERE CURRENT OF Statement

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

The syntax for the Where Current Of statement is either:

UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;

ORWHERE CURRENT OF cursor_name; DELETE FROM table_name

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN

open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;

else
UPDATE courses_tbl
SET instructor = ‘SMITH’
WHERE CURRENT OF c1;

COMMIT;end if;

close c1;

RETURN cnumber;

END;

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in
FOR UPDATE of instructor;
BEGIN

open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;

else
DELETE FROM courses_tbl
WHERE CURRENT OF c1;

COMMIT;

end if;

close c1;

RETURN cnumber;

END;

Posted in Database tutorial | Leave a Comment »

Triggers Introduction

Posted by Praveen Kumar on April 5, 2008

Introduction

A trigger is a database object that is bound to a table. In many aspects, it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a “special kind of stored procedure”.

An Example

First Create table

create table companies(companycode varchar(10) NULL,compname varchar(10))

Insert one record

insert companies values(‘A1′,’Mahiti’)

Create Trigger

create TRIGGER trg_Companies_i_companycode_specialUNQ
on companies for insert,update
as
if exists(select I.companycode from inserted as I JOIN Companies as C
on I.companycode=C.companycode
where I.companycode<>”
group by I.companycode
having count(*)>1)
begin
raiserror(‘Duplicate Found,Transaction rollback,’,10,1)
rollback tran
end

Issues

If you will again insert the same record it will give you message

‘Duplicate Found,Transaction rollback,’

When to Use Triggers

There are many reasons to use triggers. If you have a table which keeps a log of messages, you may want to have a copy of them mailed to you if they are urgent. If there were no triggers, you would have some solutions, though they are not as elegant. You could modify the application(s) logging the messages. This means that you might be redundantly coding the same thing in every application that logs messages.

Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.

SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the “Instead Of” trigger.

SQL Server 2000 has many types of triggers:

  1. After Trigger

  2. Multiple After Triggers

  3. Instead Of Triggers

  4. Mixing Triggers Type

After Triggers

Triggers that run after an update, insert, or delete can be used in several ways:

  • Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
  • Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
  • Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.

Note: An AFTER trigger can be created only on tables, not on views.

How to Create After Triggers

  1. Working with INSERT Triggers

    INSERT INTO Customers
    VALUES (‘Mayank’,’Gupta’,’Hauz Khas’,’Delhi’,
    ’Delhi’,’110016’,’01126853138’)
    INSERT INTO Customers
    VALUES(‘Himanshu’,’Khatri’,’ShahjahanMahal ’,
    ’Jaipur’,’Rajesthan’,’326541’,’9412658745’)
    INSERT INTO Customers
    VALUES (‘Sarfaraz’,’Khan’,’Green Market’,
    ’Hydrabad’,’AP’,’698542’,’9865478521’)

    INSERT INTO Products
    VALUES (‘ASP.Net Microsoft Press’,550)
    INSERT INTO Products
    VALUES (‘ASP.Net Wrox Publication’,435)
    INSERT INTO Products
    VALUES (‘ASP.Net Unleased’,320)
    INSERT INTO Products
    VALUES (‘ASP.Net aPress’,450)

    CREATE TRIGGER invUpdate ON [Orders]
    FOR INSERT
    AS
    UPDATE p SET p.instock=[p.instock – i.qty]
    FROM products p JOIN inserted I ON p.prodid = i.prodid

    You created INSERT trigger that referenced the logical inserted table. Whenever you insert a new record in the Orders table now, the corresponding record in the Products table will be updated to subtract the quantity of the order from the quantity on hand in the instack column of the Products table.

  2. Working with DELETE Triggers

    DELETE triggers are used for restricting the data that your users can remove from a database. For example:

    CREATE TRIGGER DelhiDel ON [Customers]
    FOR DELETE
    AS
    IF (SELECT state FROM deleted) = ‘Delhi’
    BEGIN
    PRINT ‘Can not remove customers from Delhi’
    PRINT ‘Transaction has been canceled’
    ROOLBACK
    END

    DELETE trigger uses the logical deleted table to make certain that you were not trying to delete a customer from the great state “Delhi” – if you did try to delete such a customer, you would be met with Mayank in the from of an error message (which was generated by the PRINT statement that you entered in the trigger code).

  3. Working with UPDATE Triggers

    UPDATE triggers are used to restrict UPDATE statements issued by your users, or back your previous data.

    CREATE TRIGGER CheckStock ON [Products]
    FOR UPDATE
    AS
    IF (SELECT InStock FROM inserted) < 0
    BEGIN
    PRINT ‘Cannot oversell Products’
    PRINT ‘Transaction has been cancelled’
    ROLLBACK
    END

    You created an UPDATE trigger that references the inserted table to verify that you are not trying to insert a value that is less than zero. You need to check only the inserted table because SQL Server performs any necessary mathematical functions before inserting your data.

Multiple After Triggers

More than one trigger can now be defined on a table for each Insert/Update/Delete. Although in general, you might not want to do this (it’s easy to get confused if you over-use triggers), there are situations where this is ideal. One example that springs to mind is that you can split your triggers up into two categories:
  • Application based triggers (cascading deletes or validation, for example).
  • Auditing triggers (for recording details of changes to critical data).
This would allow you to alter triggers of one type without fear of accidentally breaking the other.
If you are using multiple triggers, it is of course essential to know which order they fire in. A new stored procedure called sp_settriggerorder allows you to set a trigger to be either the “first” or “last” to fire.
If you want more than two triggers to fire in a specific order, there is no way to specifically define this. A deeply unscientific test I did indicated that multiple triggers for the same table and operation will run in the order they were created unless you specifically tell them otherwise. I would not recommend relying on this though.

Instead Of Triggers

Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in this simple example:

CREATE TABLE Mayank (Name varchar(32))
GO
CREATE TRIGGER tr_mayank ON Mayank
INSTEAD OF DELETE
AS
PRINT Sorry – you cannot delete this data’
GO
INSERT Mayank
SELECT Cannot’ union
SELECT Delete’ union
SELECT Me’
GO
DELETE Mayank
GO
SELECT * FROM Mayank
GO
DROP TABLE Mayank

If you were to print out the contents of the inserted and deleted tables from inside an Instead Of trigger, you would see they behave in exactly the same way as normal. In this case, the deleted table holds the rows you were trying to delete, even though they will not get deleted.

Instead of Triggers can be used in some very powerful ways!

  • You can define an Instead Of trigger on a view (something that will not work with After triggers) and this is the basis of the Distributed Partitioned Views that are used so split data across a cluster of SQL Servers.

  • You can use Instead Of triggers to simplify the process of updating multiple tables for application developers.

  • Mixing Trigger Types.

If you were to define an Instead Of trigger and an After trigger on the same table for the same operation, what would happen?

Because an After trigger fires after an operation completes, and an ‘instead of’ trigger prevents the operation from taking place, the After trigger would never fire in this situation.

However, if an Instead Of trigger on a (say) delete operation contains a subsequent delete on the same table, then any After trigger defined for the delete operation on that table will fire on the basis of the delete statement issued from the Instead Of trigger. The original delete statement is not executed, only the Delete in the Instead Of trigger runs.

This code sample creates a trigger of each type, and changed the nature of the delete statement issued so that only comics that have a value of 0 in the Preserve column can be deleted.

CREATE TABLE Gupta (Comic VARCHAR (32), Preserve INT)
GO
INSERT Gupta
SELECT groucho’, 1 UNION
SELECT chico’, 1 UNION
SELECT harpo’, 0 UNION
SELECT zeppo’, 0
GO
CREATE TRIGGER trGuptaDelete ON Gupta
FOR DELETE
AS
SELECT Comic AS deleting_these_names_only”
FROM deleted
GO
CREATE TRIGGER tr_Gupta_InsteadOf ON Gupta
INSTEAD OF DELETE
AS
DELETE Gupta
FROM Gupta
INNER JOIN Deleted
ON Gupta.Comic = Deleted.Comic
WHERE Gupta.Preserve= 0
GO
DELETE Gupta WHERE Comic IN (GROUCHO’, HARPO’)
GO
SELECT * FROM Gupta
DROP TABLE Gupta

Posted in Database tutorial | 2 Comments »