Stored Procedure

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

Stored Procedure : 

Stored Procedure

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

authorStream Live Help