Lesson 3 : Procedure

A procedure often called a stored procedure is a sub-routine like a sub-program in a regular programming language or computing language but is stored in the database. A procedure requires a name and two types of parameter, the IN and OUT parameter or at least one of the parameter. All most relational database supports stored procedure.

With the sample table above, it would be logical to fire a select query on table Employee first to get EmployeeID then fire another query back to the database inserting new information to Deductions table. 

Example in VB.net 

OpenRecord(“Select EmployeeID from Employee”) 

     with rs 

          while not .eof 

              OpenRecord1 (“Insert into Deductions values(default,’GSIS’, 200,’” & 

              .fields(0).value & ”’)”) 

        .movenext 

    end with

In the above sample code the program has used the port 3306 for 4 times just for two employees, now imagine if you have 1000 employees there would be 1002 queries to be fire,two for select and 1000 for insert. In this case it might cause a disconnection to another query. With stored procedure, we will only fire one query into the database calling the stored procedure with the required parameter and let the procedure execute the command within the database itself. 

Syntax 

DELIMITER $$ 

CREATE PROCEDURE newDeductions(IN tt text, IN aa double, IN ) 

BEGIN 

Do something here… 

END$$ 

DELIMITER ; 

Example 

DELIMITER $$ 

CREATE PROCEDURE newDeductions(IN tt text, IN aa double) 

BEGIN 

DECLARE FINISHED INTEGER DEFAULT 0; 

DECLARE EID VARCHAR(100) DEFAULT ””; 

DECLARE EID_CURSOR CURSOR FOR SELECT EmployeeID FROM Employee; 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1; 

OPEN EID_CURSOR; 

getIDandSAVEDeductions: LOOP 

FETCH EID_CURSOR INTO EID; 

IF FINISHED = 1 THEN 

LEAVE getIDandSAVEDeductions; 

END IF 

INSERT INTO Deductions VALUES(DEFAULT, tt, aa, EID); 

END LOOP getIDandSAVEDeductions; 

CLOSED EID_CURSOR; 

END$$ 

DELIMITER ;

Then call your stored procedure in your program like this 

OpenRecord(“CALL newDeductions(‘GSIS’,200)”); 

The call statement will initiate the Store Procedure to execute the sub-routine commands or instructions with only one fired query into the database saving the traffic of your port 3306.

Activity 

In your mysql database, create a table for top candidates of a pageant. This will contain the following fields {CandidateNo, CandidateName, CandidatePosition, CandidateScore}. Candidates’ Position will be numbered as 1,2,3,4,5 and so on. Whenever a database user deletes candidates from the table especially in the middle position like 2 or 3 the lower position will be change to a higher position. See Example…

Download PDF File