Coding Bootcamp: Stored Procedures, Cursors, Triggers, Indexes

Stored Procedures, Cursors, Triggers, Indexes

Stored Procedures

Stored Procedures

Stored Procedures

Stored Procedures Benefits

Stored Procedures Drawbacks

Stored Procedures

Store Procedures

T-SQL Store Procedures

T-SQL Store Procedures

Creation, calling & deleting a SP

ST Creation

SP – Structure (My SQL)

DELIMITER //  ##declare the character  end the procedure
CREATE PROCEDURE test()   
BEGIN  
    SELECT *  FROM actor;   
END //
CALL test() ;
DROP PROCEDURE test;

SP – Example (My SQL)

DELIMITER $
CREATE PROCEDURE hello_world2()
BEGIN
 SELECT * FROM student ORDER BY am;
 SELECT * FROM course ORDER BY course_id;
 SELECT * FROM registration;
END$

CALL hello_world2();

SP – Example (My SQL)

Value Assignments

Declarations in a SP

Declarations & value assignments in SP


Input & Output of a SP

Input & Output of a SP – Example (1/2)

DELIMITER $
CREATE PROCEDURE afairesi(IN a INT, IN b INT, OUT result INT)
BEGIN
SET result=a-b;
END$
DELIMITER ;

CALL afairesi(5,4,@res);

SELECT @res; (result=1)

Input & Output of a SP – Example (2/2)

DELIMITER $
CREATE PROCEDURE arnitiko(INOUT num INT)
BEGIN
 SET num=-num;
END$
mysql>DELIMITER ;

mysql>SET @y=17;
mysql>CALL arnitiko(@y);
mysql>SELECT @y;

SP – Example, MSSQL

Flow control structures

SP – If, Then, else Example

IF condition
    THEN statement/s
ELSEIF condition
    THEN statement/s
ELSE
DELIMITER $
CREATE PROCEDURE absolute(IN num INT, OUT abs_num INT)
BEGIN
IF(num<0) THEN
    SET abs_num=-num;
ELSE
    SET abs_num=num;
END IF;
END$
mysql>DELIMITER ;

SP – If, Then, else Example

DELIMITER $
CREATE PROCEDURE pointOfTime(IN inputDay DATE)
BEGIN
 DECLARE currentDay DATE;
 SET currentDay=CURDATE();
 IF(inputDay>currentDay) THEN
    SELECT 'Future';
 ELSEIF(inputDay=currentDay) THEN
    SELECT 'Present';
 ELSE
    SELECT 'Past';
 END IF;
END$
DELIMITER ;
CALL pointOfTime('2011-12-31');

What else we could do?

Store Procedure Example - MSSQL

CREATE PROCEDURE SP_ChangeSupplier @FromSID INT,@ToSID INT,  @Result INT OUTPUT AS
DECLARE @Error INT
set @Error = 0
Begin
IF @ToSID>0
Begin
Update Products
Set SupplierID = @ToSID
WHERE SupplierID =@FromSID
End
Else 
    @Error = 1
End
Set @Result =@Error
GO

Calling the SP
EXEC SP_ChangeSupplier (30, 37, @r output)

Dropping the SP
DROP PROCEDURE SP_ChangeSupplier

WHILE

WHILE condition
    DO statement/s
END WHILE;
DELIMITER $
CREATE PROCEDURE simpleFor(IN maxNum INT)
BEGIN
    DECLARE i INT;
    SET i=0;
    WHILE(i<maxNum AND maxNum>=0) DO
        SELECT i;
         SET i=i+1;
    END WHILE;
END$
DELIMITER ;

WHILE

If –then-else & While / MS SQL

REPEAT

REPEAT statement/s
    UNTIL condition
END REPEAT;
DELIMITER $
CREATE PROCEDURE simpleForAlt(IN maxNum INT)
BEGIN
 DECLARE i INT;
 SET i=0;
 REPEAT
 SELECT i;
 SET i=i+1;
 UNTIL(i>=maxNum OR maxNum<0)
 END REPEAT;
END$

CALL simpleForAlt(2);

REPEAT

CASE

CASE
WHEN condition1 THEN statement/s
WHEN condition2 THEN statement/s
...
ELSE statement/s
END CASE;

CASE

SP: in Class Exercises (1/3)

Write a stored procedure that receives as as input the id of an employee and displays his name and his salary. Then call this procedure for the employee id=2
Employee (id, FNAME, LNAME ,SALARY)

CREATE PROCEDURE find_emp_salary  @ide int
AS
BEGIN
    SELECT FNAME
      ,LNAME
      ,SALARY
FROM EMPLOYEE
WHERE ID = @ide
END
CALL find_emp_salary (2);

SP: in Class Exercises (2/3)

Write a stored procedure that receives as input the id of an employee and returns his id and the number of projects he participates in
Employee (id, NAME)
Works_On (idEmpl, project)

CREATE PROCEDURE find_emp_projects @ide int
AS
BEGIN
    SELECT a.Name, COUNT(b.project) AS NUM_OF_PROJECT
    FROM EMPLOYEE a
    INNER JOIN WORKS_ON b
    ON a.id=b. idEmpl
    WHERE a.ID = @ide
    GROUP BY a.Name
END
--------
exec find_emp_projects (@ide)

SP: in Class Exercises (2/3)

-OR-

CREATE PROCEDURE find_emp_projects @ide int, @numb int out
AS 
BEGIN
     SET @numb =(
    SELECT COUNT(b.project)
    FROM EMPLOYEE a
    INNER JOIN WORKS_ON b
    ON a.id=b. idEmpl
    WHERE a.ID = @ide
    GROUP BY a.Name)
    END
------
exec find_emp_projects (@ide, @numb)
Select @ide, @numb

SP: in Class Exercises (3/3)- Solution MS SQL

Write a stored procedure that receives as input the sex and the department and displays the average salary of the employees
Employees (id, name, salary, sex, Did)
Department(Did, Dname)

CREATE PROCEDURE find_avg_salaries @sex nvarchar(20), @department nvarchar(50)
AS
BEGIN
SELECT  d. DNAME,
        e.SEX,
        avg(e.SALARY) AS AVG_SALARY
    FROM EMPLOYEE e
    INNER JOIN DEPARTMENT d
    ON e.DID = d.DID
    WHERE d.DNAME = @department AND e.SEX = @sex
    GROUP BY d. DNAME
END

Cursors

Cursors in SQL

Syntax for Cursors

Controlling Cursor…

Controlling Cursor

Cursors example – MS SQL

Advantages & Disadvantages

Cursor & Procedure example - MySQL

DELIMITER $
DROP PROCEDURE IF EXISTS showCourseLectures$
CREATE PROCEDURE showCourseLectures(IN courseId INT)
BEGIN
 DECLARE lectSubject VARCHAR(128);
 DECLARE lectNum INT(2);
 DECLARE finishedFlag INT;
 DECLARE lectCursor CURSOR FOR
 SELECT subject, num_lecture FROM lecture WHERE course_lecture=courseId;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finishedFlag=1;
 OPEN lectCursor;
    SET finishedFlag=0;
    FETCH lectCursor INTO lectSubject, lectNum;
        WHILE(finishedFlag=0) DO
            SELECT lectNum AS 'Αριθμός Διάλεξης', lectSubject AS 'Θέμα';
            FETCH lectCursor INTO lectSubject, lectNum;
        END WHILE;
 CLOSE lectCursor;
END$
DELIMITER ;

Cursors example - MySQL

DELIMITER $
DROP PROCEDURE IF EXISTS showCourseLecturesAlt$
CREATE PROCEDURE showCourseLecturesAlt(IN courseId INT)
BEGIN
 DECLARE lectSubject VARCHAR(128);
 DECLARE lectNum INT(2);
 DECLARE finishedFlag INT;
 DECLARE lectCursor CURSOR FOR
 SELECT subject,num_lecture FROM lecture WHERE course_lecture=courseId;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finishedFlag=1;
 OPEN lectCursor;
 SET finishedFlag=0;
 REPEAT
 FETCH lectCursor INTO lectSubject, lectNum;
 IF(finishedFlag=0) THEN
     SELECT lectNum AS 'Αριθμός Διάλεξης', lectSubject AS 'Θέμα';
 END IF;
 UNTIL(finishedFlag=1)
 END REPEAT;
 CLOSE lectCursor;
END$
DELIMITER ;

Triggers

Triggers

Basic Commands

Designing a Trigger

Trigger Creation

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW trigger_body

Trigger Events

Trigger Example (1/2)

SET @courseCount=
    (SELECT COUNT(*) FROM course);
SELECT @courseCount;  
(assume the table was empty at the beginning->0)

CREATE TRIGGER keep_count
AFTER INSERT ON course
FOR EACH ROW
SET @courseCount=@courseCount+1;
INSERT INTO course(title,course_id,supervisor)
VALUES
('t1',NULL,'anastasia@aueb.gr'),
('t2',NULL, 'vasilis@aueb.gr');

SELECT @courseCount;
Result->2

Trigger Example (2/2)

DELIMITER $
CREATE TRIGGER checkRegDate
BEFORE INSERT ON registration
FOR EACH ROW
BEGIN
 DECLARE currDate DATE
 SET currDate=CURDATE();
 IF NEW.reg_date>currDate THEN
    SET NEW.reg_date=currDate;
 END IF;
END$
DELIMITER ;
INSERT INTO registration(reg_date,reg_student,reg_course)
VALUES('2030-04-17',2193,2);
SELECT * FROM registration
WHERE reg_course=2 AND reg_student=2193;
-> What is the day of the value of the reg_date?

Indexes

Indexes (1/3)

Indexes (2/3)

Indexes (3/3)

Exercises

Exercise 1

Create and execute a procedure that displays the first and the last name from table actors.

Exercise 2

Create a procedure to display everything from table actor with id=58

Exercise 3

Create a procedure that receives as input a payment amount and a date and returns those payments (table=payment) that exceed this amount for the days after the given day.
Then execute the procedure for amount=1, and date = '2004-05-25 11:30:37‘.

Exercise 4

In the previous procedure also count the number of distinct dates that satisfy the following the restriction (amount=1, and date = '2004-05-25 11:30:37‘.)

Creative Commons Licence
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.