TIL

TIL

  • Docs
  • Contact
  • Feedback
  • Contribute

›Week 11

Getting Started

  • Introduction

Principles of Programming Languages

    Week 3

    • Pattern Matching and Recursive Data Types

    Week 8

    • Monads

Introduction to Databases

    Week 3

    • Cinema Database

    Week 4

    • Pine Valley Furniture
    • Pine Valley Furniture - Solution

    Week 5

    • ERD to Shorthand Conversions
    • ERD to Shorthand Conversions - Solution

    Week 6

    • Normalizations
    • Normalizations - Solutions

    Week 7

    • Other Normal Forms
    • Other Normal Forms - Solutions

    Week 8

    • SQL Introduction

    Week 9

    • More SQL

    Week 10

    • Joins and Subqueries

    Week 11

    • Functions, Procedures, Triggers and Embedded SQL

    Week 12

    • Prepared SQL Statements
Edit

Functions, Procedures, Triggers and Embedded SQL

Functions

  • Perform routine actions in SQL like a normal function
  • Returns one value
  • Can be embedded into normal SQL

Syntax

DELIMITER $$
CREATE FUNCTION count_countries ()
    RETURNS INT DETERMINISTIC
    BEGIN
        DECLARE num INT;
        SET num = (SELECT count(*) FROM world.country);
        RETURN num;
    END$$
DELIMITER ;

-- Then you call them like so
SELECT count_countries();

Procedures

  • Also performs routine actions in SQL
  • Able to return multiple values
  • Cannot be embedded into normal SQL

Syntax

DELIMITER $$
CREATE PROCEDURE count_countries (OUT num_countries INT)
    BEGIN
        SELECT count(*) INTO num_countries FROM world.country;
    END$$
DELIMITER ;

-- You call these like so
CALL count_countries(@result);
SELECT @result;

Triggers

  • Perform miniscule tasks
  • Useful for controlling data on insertion/deletion/updates

Syntax

CREATE TRIGGER set_to_asia BEFORE INSERT ON country
    FOR EACH ROW
        SET new.continent = 'asia'; -- you can set the new row's values

Embedded SQL

Embedded SQL refers to the hard-coded SQL statements that you write in a back-end program (i.e., a Java or Python program) as they are embedded into the code.

Here is an example demonstrating how to connect to a MySQL database using Java:

import java.sql.*;

public class Main {
    public static void main(String[] args) {
        String username = "user";
        String password = "pass";
        try {
            Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/world", username, password);
            Statement stmt = con.createStatement(); // hard-coded sql
            ResultSet rs=stmt.executeQuery("select * from country");
            while(rs.next())
                System.out.println(rs.getString(1) + " " + rs.getString(2));
            con.close();  // always include this afterwards
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

It's important to close your connection after running a SQL command since you'll have lingering connections that will block future processes from running.

Questions

  1. Write a procedure that returns the name of the cities that starts with 'SH' and insert it into a table called special_cities. Call the procedure and see the result.
  2. Copy table city into a table called _city. Then write a trigger to remove a city from special_cities where it is removed from _city.
Last updated on 1/4/2019
← Joins and SubqueriesPrepared SQL Statements →
  • Functions
    • Syntax
  • Procedures
    • Syntax
  • Triggers
    • Syntax
  • Embedded SQL
  • Questions
TIL
Docs
Getting StartedPrinciples of Programming Languages (C24)Introduction to Databases (C43)
Community
PiazzaQuercus
More
GitHubStar
Facebook Open Source
Copyright © 2019 Rakin Uddin