Avoidance of network traffic : In client server architecture, applications access the remote database over the network. This results in poor performance because it returns a lot of unnecessary data to the client. But when using stored procedure, as stored procedure runs on the server, only the results the client application needs are returned. This reduces the network traffic, also Stored Procedures groups SQL statements together which can also save on network traffic. A typical application requires two trips across the network for each SQL statement. Grouping of SQL statements reduces the trips across the network which results in better performance for applications.
Access to features that exist only on the server : Stored procedures can have access to commands that run only on the server, such as LIST DB DIRECTORY and LIST TABLES. It can take the advantages of increased memory and disk space on server machines and they can access any additional software installed on the server.
Define Business rules common to several application : You can use stored procedures to define business rules that are common to several applications similar to triggers and constraints. When an application calls the stored procedure, it will process data based on the rules defined in the stored procedure. If you need to change the rules, you only need to make the change once in the stored procedure, not in every application that calls the stored procedure.
Protection from SQL injection attacks: Stored procedures reduces the risk of sql injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands
Helps for workload distribution: It splits the application logic and encourages an even distribution of the computational workload.Stored procedures can be developed using DB2 development center (in db2 8.2) , a GUI based or you can create using CLP . Now let us see how to create and run a Stored Procedures using Command Line Processor (CLP ) .
Example 1 : Stored Procedure to filter job seekers applications based on the conditions like Date of Birth of the candidate should be between the given two input dates and qualification should be equal to the given input string. The Stored Procedure will accept three input parameters MinDate , MaxDate and Qualification. Output will be the result sets.
CREATE PROCEDURE Shortlist (mindate date , maxdate date, qual varchar(20)) SPECIFIC sp5 DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR select * from applications where dob>=mindate and dob<=maxdate and qualification=qual;
OPEN cursor1;
END P1
@
Store the above lines in a file named
sp1.sql. If you run the above script file using the command
db2 -tvf sp1.sql,
you may get the error like DB21028E The cursor "CURSOR1" has not been declared. DB21007E End of file reached while reading the command.
To solve the above error, select an alternate terminating character for the Command Line Processor (DB2 CLP), other than the default terminating character which is semicolon (;) , to use in the script. For example, the above create procedure statement, @ is used as a terminating character. Now run the DB2 CLP script containing the CREATE PROCEDURE statement from the command line, using the following CLP command:
db2 -td <terminating-character> -vf <CLP-script-name>
to run the above Stored Procedure, issue the command
db2 -td@ -vf sp1.sql
where @ is a terminating character for the procedure. Now the Stored Procedure (sp1.sql) is created. To run the above stored procedure , issue the command
db2 call Shortlist('1970-01-01', '1980-01-01', 'BE') ,
running the command returns all the records whose dob<='1970-01-01' and dob<='1980-01-01' and qualification='BE'
Example 2 : To update employee's salary based on the grade. Accepts input employee code and grade .
CREATE PROCEDURE SALARY_UPDATE (IN emp_code varchar(10), IN grade SMALLINT) LANGUAGE SQL
BEGIN
DECLARE error CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR error SIGNAL SQLSTATE '20001' SET MESSAGE_TEXT = 'Employee code does not exist';
IF (grade = 1) THEN UPDATE emp3 SET salary = salary + (salary*15/100) WHERE empcode= emp_code;
ELSEIF (grade = 2) THEN UPDATE emp3 SET salary = salary + (salary*10/100) WHERE empcode= emp_code;
ELSEIF (grade=3) THEN UPDATE emp3 SET salary = salary + (salary*5/100) WHERE empcode= emp_code;
END IF;
END
@
Store the above script in a file named
sp2.sql.
Run the above script by the command
db2 -td@ -vf sp2.sql. and run the procedure by
db2 call SALARY_UPDATE('1132', 1)
-where empcode is 1132 and grade is 1. So the salary will be updated by 15% increment.
Suppose the employee code does not exist in the table , then the default error
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 will be thrown.
SQLSTATE is 02000 .
Now we can change the default error message with own error message like Employee code does not exist . . For the the following lines are used. error variable is declared, if the SQLSTATE=02000. For that error, we are setting a new message as "Employee code doe not exist" and SQLSTATE as 20001.
DECLARE error CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR error SIGNAL SQLSTATE '20001' SET MESSAGE_TEXT = 'Employee code doe not exist'; . So the following error will be thrown, if no records found.
SQL0438N Application raised error with diagnostic text: "Employee code does not exist". SQLSTATE=20001
Example 3 : The above two stored procedures return the result sets as output. Now let us see the a different example , to calculate sum of salaries of a particular employee's whose designation matches the given input string . (for eg. UDC , LDC, ...)
CREATE PROCEDURE totalsalary(IN Desig char(20) , OUT totalsalary INTEGER) LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE tot_sal INTEGER;
DECLARE empsalary INTEGER;
DECLARE cursor1 CURSOR FOR SELECT SALARY FROM EMP3 where Designation=Desig;
SET tot_sal= 0;
OPEN cursor1;
FETCH FROM cursor1 INTO empsalary;
WHILE(SQLSTATE = '00000') DO
SET tot_sal = tot_sal+ empsalary;
FETCH FROM cursor1 INTO empsalary;
END WHILE;
CLOSE cursor1;
SET totalsalary=tot_sal;
END%
In the above script, termination charater is %, so run the script with command
db2 -td% -vf sp3.sql.
It accepts two parameters. One is for input , another is for output. We have set SQLSTATE to '00000'. So the while loop continues until the SQLSTATE changes. The SQLSTATE changes when the cursor has no further rows. when there is no rows , it throws exception with SQLSTAT 02000. Now the while loop terminates.
