Stored Procedure Overview :
Stored Procedure Overview Stored Procedure is a function in a shared library accessible to the database server
can also write stored procedures using languages such as C or vb
Advantages of stored procedure : Reduced network traffic
The more SQL statements that are grouped together for execution, the larger the savings in network traffic
Slide 3:
Applications using stored procedures
Writing Stored Procedures :
Writing Stored Procedures CREATE OR REPLACE PROCEDURE <name> (<arglist>) AS <declarations>
BEGIN
<procedure statements>
END
Tasks performed by the client application
Tasks performed by the stored procedure, when invoked
The CALL statement
Explicit parameter to be defined :
IN: Passes a value to the stored procedure from the client application
OUT: Stores a value that is passed to the client application when the stored procedure terminates.
INOUT : Passes a value to the stored procedure from the client application, and returns a value to the Client application when the stored procedure terminates
Example for Stored Procedure :
Example for Stored Procedure Example 1
BEGIN
INSERT INTO EMPLOYEE VALUES (‘Sharad’, 123, 234);
DELETE FROM EMPLOYEE WHERE ssn = 234;
END
Example 2
CREATE PROCEDURE UPDATE_SALARY_1
(IN EMPLOYEE_NUMBER CHAR(6),
IN RATE INTEGER)
LANGUAGE SQL
BEGIN
PDATE EMPLOYEE
SET SALARY = SALARY * (1.0 * RATE / 100.0 )
WHERE SSN = EMPLOYEE_NUMBER;
END
Slide 6:
Some Valid SQL Procedure Body Statements
CASE statement
FOR statement
GOTO statement
IF statement
ITERATE statement
RETURN statement
WHILE statement
Example for Valid Stored Procedure :
Example for Valid Stored Procedure EXAMPLE :
CREATE PROCEDURE UPDATE_SALARY_IF
(IN employee_number CHAR(6), IN rating SMALLINT)
LANGUAGE SQL
BEGIN
SET counter = 10;
WHILE (counter > 0) DO
IF (rating = 1)
THEN UPDATE employee
SET salary = salary * 1.10, bonus = 1000
WHERE empno = employee_number;
ELSEIF (rating = 2)
THEN UPDATE employee
SET salary = salary * 1.05, bonus = 500
WHERE empno = employee_number;
ELSE UPDATE employee
SET salary = salary * 1.03, bonus = 0
WHERE empno = employee_number;
END IF;
SET counter = counter – 1;
END WHILE;
END
summary :
summary Stored Procedure Overview
Writing Stored Procedures
Some Valid SQL Procedure Body Statements
Example for Valid Stored Procedure