Lesson 4 : Function

A function in SQL looks very similar to procedure, only unlike procedure it needs to return a value as output. A function could be a sub-routine in a query or a procedure for computations and other task that needs to be automated inside the database management system.

With the table above we could generate a pay slip for each Employee with net amount 59,550.00 and 39,600.00 respectively as both employees has deductions for SSS and GSIS.

By means of creating a pay slip, we could do that by simply creating a sub-query like this:

SELECT e.*, t1.netamount FROM Employee AS e INNER JOIN (SELECT Amount-(SELECT SUM(Amount) FROM Deductions WHERE EmployeeID='16-M89031')AS netamount,EmployeeID FROM salary WHERE EmployeeID='16-M89031') AS t1 ON t1.EmployeeID = e.EmployeeID

And we can even integrate it into creating a payroll for the whole company but what if there is a requirement on your program which will only generate the NetAmount for a specific employee, it will only return the value 59,550.00 or 39,600.00

Then maybe you need to prepare a single function for that option.

Syntax

DELIMITER $$
CREATE FUNCTION NAME() RETURNS DOUBLE
BEGIN
END$$
DELIMITER;

Example

DELIMITER $$
CREATE FUNCTION getNetSalary() RETURNS DOUBLE
BEGIN
RETURN (SELECT Amount-(SELECT SUM(Amount) FROM Deductions WHERE EmployeeID='16-M89031')AS netamount FROM salary WHERE EmployeeID='16-M89031');
END$$
DELIMITER;

The example above could be called on your program using this command

Select getNetSalary();

But it is a custom query that will only return the net salary of employee 16-M89031. To make if more useful, reusable for other employee we will add a parameter on that function to make it dynamic, see example below:

Example

DELIMITER $$
CREATE FUNCTION getNetSalary(EID VARCHAR(20)) RETURNS DOUBLE
BEGIN
RETURN (SELECT Amount-(SELECT SUM(Amount) FROM Deductions WHERE
EmployeeID=EID)AS netamount FROM salary WHERE EmployeeID=EID);
END$$
DELIMITER;

Activity

With the example table above create a simple function which will return the total NETINCOME of each employee for the whole year.

Download PDF File

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

Lesson 2 : Trigger

A trigger is a feature in most databases which serves as a background function that will execute on table’s changes. This execution will create changes to other table like update, delete or add a new row of information. Instead of having to navigate to other table to manually change its information or having to fire another set of query after the first one, trigger will automatically do the task for the database administrator or the programmer in the background without having to fire another query from the client program or manually navigate into the other table to do changes. For example when I add a new employee in EMPLOYEE table it will add a new count on POPULATION table according to gender.

Syntax 

DELIMITER $$ 

CREATE 

TRIGGER UpdatePopulation 

BEFORE/AFTER INSERT/UPDATE/DELETE ON `scheduling`.`<Table Name>` 

FOR EACH ROW 

BEGIN 

“DO SOMETHING HERE” 

END$$ 

DELIMITER ; 

Example 

DELIMITER $$ 

CREATE 

TRIGGER UpdatePopulation AFTER INSERT ON Employee FOR EACH ROW 

BEGIN 

Update Population set HeadCount = HeadCount+1 Where Gender = Gender.NEW; 

END$$ 

DELIMITER ; 

Note: BEFORE/AFTER INSERT/UPDATE/DELETE ON `scheduling`.`<Table Name>` 

This line indicates when the trigger will be executed, will it be BEFORE some changes are done to the first table or AFTER a change is done to the first table and will it be before an insert, update or delete happen on the first table or after an insert, update or delete happen on the first table. 

6 Combinations: 

BEFORE INSERT                        AFTER INSERT 

BEFORE UPDATE                      AFTER UPDATE 

BEFORE DELETE                       AFTER DELETE 

Activity: 

On your Elementary Grading System create a table Population and implement 3 triggers on it for AFTER INSERT, UPDATE and DELETE on the STUDENTS table. These triggers will update the total population of each classroom according to the students’ gender. 

Download PDF File

Lesson 1 : Views

Just like a normal entity a view has its own attributes. It will look like a normal table but without a primary key itself and all data or information are borrowed from one or more table from the same database. For example personal information from the EMPLOYEE table is combined with the information in SALARY table to create a new set of information ready for retrieval. The good side of having views in your database is that you can distribute an information to any other system or people without having given them the actual editable table, meaning, whatever the changes on the view will not affect the original table.

Syntax

CREATE VIEW MonthlySalary AS (SELECT * FROM ...);

Sample

Create view MonthlySalary as Select e.Fullname as Employee, e.Position as Position, s.Amount * 20 as Salary from Employee as e inner join salary as s on e.SalaryGrade = s.SalaryGrade

Activity

Create a database for elementary grading system which will house views that gives this information:

1. List of students per subject with their average grades arrange in a descending order by average.

2. List of students per classroom with the average grade for all subjects {English, Math, Filipino, Science, PE, MAPEH, Araling Panlipunan and GMRC } arrange in a descending order by average.

Download PDF File