sample programes for stored procedures

Hello World Examle in Procedure Programs

Procedure Programs.

Hello World Example .

Create Procedure :

Delimiter $$

drop procedure if exists helloWorld$$

create procedure HelloWorld()

begin

Select 'Hello World';

End $$

Call Created Procedure:

call helloWOrld();

OUtPut :Hello World

ss

Line Explanation

1    Issue the DELIMITER command to set '$$' as the end of a statement. Normally, MySQL regards ";" as the end of

a statement, but since stored procedures contain semicolons in the procedure body, we need to use a different

delimiter.

3   Issue a DROP PROCEDURE IF EXISTS statement to remove the stored procedure if it already exists. If we don’t do

this, we will get an error if we then try to re-execute this file with modifications and the stored procedure exists.

4   The CREATE PROCEDURE statement indicates the start of a stored procedure definition. Note that the stored procedure

name "HelloWorld" is followed by an empty set of parentheses "( )". If our stored procedure had any

parameters, they would be defined within these parentheses. This stored procedure has no parameters, but we need

to include the parentheses anyway, or we will get a syntax error.

5   The BEGIN statement indicates the start of the stored procedure program. All stored programs with more than a

single statement must have at least one BEGIN and END block that defines the start and end of the stored program.

6   This is the single executable statement in the procedure: a SELECT statement that returns "Hello World" to

the calling program. As you will see later, SELECT statements in stored programs can return data to the console or

calling program just like SELECT statements entered at the MySQL command line.

7   The END statement terminates the stored procedure definition. Note that we ended the stored procedure definition

with $$ so that MySQL knows that we have completed the CREATE PROCEDURE statement.

Example 2:

Multiple variable take and initialized the values dynamically 

Delimiter $$

drop PROCEDURE if EXISTS variableDemo$$

create procedure variableDemo()

begin

declare myInteger INT;

declare myCurrency NUMERIC(10,2);

declare myVarChar VARCHAR(50) default 'Sairam is a Java Developer';

set myInteger=20;

set myCurrency=10.2;

select myInteger myNo,myVarChar aboutMe,myCurrency mySalary;

end $$

Output:

Example 3: Take input and increment that value and increment by 2;

Parameter Modes

Parameters in MySQL can be defined as IN, OUT, or INOUT:

IN

This mode is the default. It indicates that the parameter can be passed into the

stored program but that any modifications are not returned to the calling

program.

OUT

This mode means that the stored program can assign a value to the parameter,

and that value will be passed back to the calling program.

INOUT

This mode means that the stored program can read the parameter and that the

calling program can see any modifications that the stored program may make to

that parameter.

Example 4: using parameter out

Example 5: Base on If , else if and else conditions :

Example 6:

A LOOP statement can be labeled.

end_label cannot be given unless begin_label also is present.

If both are present, they must be the same.

Example 7: Read Data base data:

SELECTing INTO Local Variables

Use the SELECT INTO syntax when you are querying information from a single row of

data (whether retrieved from a single row, an aggregate of many rows, or a join of

multiple tables). In this case, you include an INTO clause “inside” the SELECT statement

that tells MySQL where to put the data retrieved by the query.

Example 8: Read 2 more database data using procedure 

DELIMITER $$

DROP PROCEDURE IF EXISTS `employee`.`readDatabase` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `readDatabase`()

    READS SQL DATA

begin

declare totalPayments double(10,3);

declare totalSal double(10,2);

select sum(pay.amount) into totalPayments from payments pay ;

select sum(em.Sal) into totalSal From employee em;

select totalPayments TotalPayments,totalSal 2ndTableTotal ;

end $$

DELIMITER ;

Example 9 :

Creating and Using Cursors

To handle a SELECT statement that returns more than one row, we must create and

then manipulate a cursor. A cursor is an object that provides programmatic access to

the result set returned by your SELECT statement. Use a cursor to iterate through the

rows in the result set and take action for each row individually.

Defining a Cursor

Define a cursor with the DECLARE statement, which has the following syntax:

DECLARE cursor_name CURSOR FOR SELECT_statement;

Cursor Statements

The MySQL stored program language supports three statements for performing

operations on cursors:

OPEN

Initializes the result set for the cursor. We must open a cursor before fetching

any rows from that cursor. The syntax for the OPEN statement is very simple:

OPEN cursor_name;

FETCH

Retrieves the next row from the cursor and moves the cursor “pointer” to the

following row in the result set. It has the following syntax:

FETCH cursor_name INTO variable list;

CLOSE

Deactivates the cursor and releases the memory associated with that cursor. The

syntax for this statement is:

CLOSE cursor_name;

delimiter //

drop procedure if exists coursourExample//

create procedure coursourExample()

begin

declare row int default 1;

declare variable1 int;

declare variable2 varchar(20);

declare variable3 int;

declare variable4 varchar(20);

declare done int default false;

declare csr1 CURSOR for select cst.city, sum(cst.creditLimit) from customers cst

group by cst.city;

declare CONTINUE HANDLER for not found set done=TRUE;

open csr1;

coursorLoop:LOOP

if done then

leave coursorLoop;

end if;

if row =1 then

fetch csr1 into variable2,variable1;

else

fetch csr1 into variable4,variable3;

end if;

set row=row+1;

end LOOP coursorLoop;

close csr1;

select variable2,variable1 customersCreditLimit,variable3,variable4 customersCreditLimit,row ;

end;

//

Example 10:

Case Example :

delimiter //

drop procedure if exists caseExample//

create procedure caseExample(startingRang tinyint)

begin

case when  startingRang >10 then

select ' ok its condtion satisfied' message;

end case;

end;

//

Repeate Example :

delimiter //

drop procedure if exists repeatExample//

create procedure repeatExample(amount tinyInt)

begin

declare locValue int default 0;

repeat

set locValue=locValue+1;

until locValue>50 end repeat;

select locValue;

end;

//

Output :

While Example :

delimiter //

drop procedure if EXISTS whileExample//

create procedure whileExample(input int)

main:begin

declare output int default 0;

if input >100 then

leave main;

end if;

while output<=input do

set output=output+1;

end while;

select output;

end;

//

Functions Examples:

Sample function Examples.

Example1 :

delimiter //

drop function if exists function1//

create function function1() returns int

return 1;

end;

//

Output :

Example : Example read database data using functions .

delimiter //

drop function if exists employeeFunctions//

create function employeeFunctions(coustomerId int) returns double

reads sql data

begin

declare totalPayment double default 0.0;

select sum(pmts.amount) into totalPayment from payments pmts where pmts.customerNumber =coustomerId;

return totalPayment;

end;

//

Output:

Triggers :

Database triggers are stored programs that are executed in response to some kind of

event that occurs within the database. triggers fire in response to a DML statement (INSERT, UPDATE, DELETE) on a specified

table.

syntax:

CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name

{BEFORE|AFTER}

{UPDATE|INSERT|DELETE}

ON table_name

FOR EACH ROW

trigger_statements.

Referring to Column Values Within the Trigger

Trigger statements can include references to the values of the columns being affected

by the trigger. You can access and sometimes modify the values of these columns.

To distinguish between the values of the columns “before” and “after” the relevant

DML has fired, you use the NEW and OLD modifiers.

 For instance, in a BEFORE UPDATE

trigger, the value of the column mycolumn before the update is applied is OLD.

mycolumn, and the value after modification is NEW.mycolumn.

If the trigger is an INSERT trigger, only the NEW value is available (there is no OLD

value). Within a DELETE trigger, only the OLD value is available (there is no NEW value).

Within BEFORE triggers you can modify a NEW value with a SET statement—thus changing

the effect of the DML.

Implementing Logging

The ability to identify the source and nature of updates to application data is increasingly

critical in our security-conscious societies.

Suppose that we are building a financial application, for which we must track all

modifications to a user’s account balance.

 Using triggers to implement audit logging

CREATE TRIGGER account_balance_au

AFTER UPDATE ON account_balance FOR EACH ROW

BEGIN

INSERT into transaction_log

(user_id, description)

VALUES (user( ),

CONCAT('Adjusted account ',

NEW.account_id,' from ',OLD.balance,

' to ', NEW.balance));

END;

Validating Data with Triggers;

CREATE TRIGGER account_balance_bu

BEFORE UPDATE

ON account_balance

FOR EACH ROW

BEGIN

-- The account balance cannot be set to a negative value.

IF (NEW.balance < 0) THEN

-- Warning! Not implemented in MySQL 5.0...

SIGNAL SQLSTATE '80000'

SET MESSAGE_TEXT='Account balance cannot be less than 0';

END IF;

END;

Unfortunately, MySQL 5.0 and 5.1 do not support the SIGNAL statement; we expect it

to appear in version 5.2. Consequently,

Trigger Example:

Example 1:

drop trigger if exists student_delete;

delimiter //

create trigger student_delete after DELETE on student

for each row

begin

insert into deletedstudents VALUES(old.studentId,old.studentName);

end;

//

               

drop trigger if exists student_insertion;

delimiter //

create trigger student_insertion  before insert on student

for EACH row

begin

set new.birthMonth=month(new.studentDateOfBirth);

end;

//