o7planning

Database Programming with MySQL

  1. Starting with MySQL Workbench
  2. Function
  3. Procedure
  4. The basic statements
  5. Exception Handling

1. Starting with MySQL Workbench

In this document, I will guide you programming database with MySQL, use visual tool MySQL Workbench. If you use another visual tool to write code, there will be not much differences.
This is the image of MySQL Workbench you have just opened it. Click on red circled area to select the SCHEMA you work with.
There are some available examples of SCHEMA after you fully install MySQL Community.
Or you can create learningsql SCHEMA. An example of SCHEMA is used in a document instructing MySQL at o7planning.org.
Right-click on a SCHEMA, select "Set as Default Schema" to work with this schema.
Click on the icon of creating a new SQL window in order to write code (1). Click on the circled area (2) in order to hide an unnecessary window.

2. Function

Like procedure, function is a group of PL/SQL statements performing some function. Unlike procedure, functions will return a value right after its call.
Function can also be saved right on database in form of Store procedure.
Syntax create function.
-- function_name:  Name of function
-- argument: 
-- mode:  IN or not to write.
-- datatype:  Data type.

CREATE FUNCTION <function_name>
            (
               [
                [mode1] argument1 datatype1,
                [mode2] argument2  datatype2,
                ...
                ]
             )
            RETURNS  datatype
BEGIN
  -- Declare variables used
  -- Statements
  -- Return value
END;
Example:
-- a Function with parameters
CREATE FUNCTION Sum_Ab(a Integer, b Integer)
RETURNS Integer
Begin
  return a + b;
End;

-- a Function without parameters
CREATE FUNCTION Get_Current_Datetime()
RETURNS Date
Begin
  return current_date();
End;
Drop Function:
-- Drop Function

DROP FUNCTION <function_name>;

-- Example:

DROP FUNCTION My_Function;

-- Example:

DROP FUNCTION IF EXISTS  `My_Function`;
Call function
-- When calling the function, must declare a variable

-- Declare variable c.
Declare c Integer;
....

-- Call function.
Set c = Sum_Ab(10, 100);
Function Example:
This is an example of creating the first function with MySQL.
  1. Create a Function
  2. Compile the function.
  3. Run the function
If you are a beginner, you should watch this section thoroughly. This helps you easily understand the next issues mentioned in this document.
-- Drop function My_Sum if it already exists.
-- (To enable Recreate)
DROP function  if Exists `My_Sum`;

-- When programming the function / procedure you need to use semicolon
-- to separate the different commands.
-- Use DELIMITER $$ to allow use of semicolons.
DELIMITER $$

Create Function My_Sum(P_a Float, p_B Float) returns Float
Begin  

 -- Declare a variable Float
  Declare v_C Float;
 
  -- Assigning value to variable v_C
  Set V_C = p_A + p_B;
 
  -- The return value of the function.
  return v_C;

End;
Click the icon to compile function.
The function can participate in SELECT statements
SELECT
    acc.account_id,
    acc.cust_id,
    acc.avail_balance,
    acc.pending_balance,
    MY_SUM(acc.avail_balance, acc.pending_balance) balance
FROM
    account acc;
Result:

3. Procedure

A group of statements performing some function can be gathered in a procedure for the purpose of increasing the capability of handling, Reuse, security, data safety, and utility in development.

Procedure can be saved on database as a object of database and ready for reuse. Procedure then is called Store procedure. To Store procedure,after Store procedure is saved, it is translated into p-code so its performing capability is raised.
Procedures do not return values directly as a function. However it may have 0 or more output parameters.
The syntax to create a procedure:
-- procedure_name:  Name of Procedure.
-- argument: 
-- mode:  IN or OUT or INOUT, default is IN
-- datatype:  Data type.

CREATE PROCEDURE <procedure_name>
       (
        [
         [mode1]  argument1  datatype1,
         [mode2]  argument2   datatype2,
        ...
         ]
       )
BEGIN
-- Declare variables used
-- Statements ..
END;
Example:
-- For example a procedure with no parameters.
CREATE Procedure Do_Something()
Begin
   -- Declare variables here.
  Declare v_a Integer;
  -- Do something here.
  -- ....
End;

-- For example a procedure with parameters.
-- There is an input parameter and an output parameter.

CREATE Procedure Do_Something(p_Param1 Varchar(20),
                                                 OUT v_Param2 Varchar(50) )
Begin
    -- Declare variables here.
   Declare v_a Integer;

   -- Do something here.
   -- ...
End;
Drop procedure:
-- Drop procedure.

DROP PROCEDURE <Procedure_Name>
The steps for a procedure:
Procedure Example:
Here I created a simple procedure, with 1 input parameter p_Emp_ID, and has 3 output parameters, v_First_Name, v_Last_Name, v_Dep_ID.
Get_Employee_Infos
-- Drop procedure Get_Employee_Infos if it already exists.
-- (To enable Recreate)
DROP Procedure if Exists `Get_Employee_Infos`;

-- When programming the function/procedure you need to use a semicolon
-- to separate the different commands.
-- Use DELIMITER $$ to allow use of semicolons.
DELIMITER $$

-- Input parameter p_Emp_Id
-- Output parameters: v_First_Name, v_Last_Name, v_Dept_Id.
Create Procedure Get_Employee_Infos(p_Emp_Id     Integer
                                  ,Out v_First_Name Varchar(50)
                                  ,Out v_Last_Name    Varchar(50)
                                  ,Out v_Dept_Id      Integer)  
Begin
  -- Log - use for programmers
 Select Concat('Parameter p_Emp_Id = ',p_Emp_ID)  as log_info1 ;
 --
 -- Use SELECT .. INTO statement to assign values to the variable from query
 -- This statement may cause errors, if the SELECT clause returns more than one records.
 --
 Select Emp.First_Name
       ,Emp.Last_Name
       ,Emp.Dept_Id
 Into   v_First_Name
       ,v_Last_Name
       ,v_Dept_Id
 From   Employee Emp
 Where  Emp.Emp_Id = p_Emp_Id;
 --
 -- Log for developer
 --
 Select 'Found Record!' as log_info2 ;
 Select concat(' v_First_Name= ', v_First_Name) as log_info3 ;
 Select concat(' v_Last_Name= ', v_Last_Name) as log_info4 ;
 Select concat(' v_Dept_Id= ', v_Dept_Id) as log_info5 ;

End;
Click the icon to compile the procedure.
Test procedure
In programming, testing a procedure and detect error are very important. Open a new window and execute the statement of calling procedure:
Call Get_Employee_Infos(1,@v_First_Name,@v_Last_Name,@v_Dept_Id);
Use @variable_name to store the output parameter.

4. The basic statements

Here I introduce the overview of basic statements of PL/SQL. You will understand it through examples in the next sections.
Control flow statement - If-elsif-else
Syntax:
IF <condition 1> THEN
    Job 1;
[ELSIF <condition 2> THEN
     Job 2;
]
[ELSE
     Job n + 1;
]
END IF;
Example:
If v_Option = 1 Then
 v_Action := 'Run';
Elseif v_Option = 2 Then
 v_Action := 'Backup';
Elseif v_Option = 3 Then
 v_Action := 'Stop';
Else
 v_Action := 'Invalid';
End If;
Else_If_Example
DROP Procedure if Exists `Else_If_Example`;

DELIMITER $$

CREATE PROCEDURE Else_If_Example()
BEGIN

   -- Declare a variable
   Declare v_Option Integer;
   Declare v_Action Varchar(30);
   
   Set v_Option = 2;
   
   
   If v_Option = 1 Then
     Set v_Action  = 'Run';
   Elseif v_Option = 2 Then
     Set v_Action  = 'Backup';
   Elseif v_Option = 3 Then
     Set v_Action  = 'Stop';
   Else
     Set v_Action  = 'Invalid';
   End If;
   
   -- Logging
   SELECT Concat('v_Action= ', v_Action) as Log_Info;
   
END
Results test procedure:
Control flow statement - CASE
Syntax:
CASE case_value
   WHEN when_value THEN statement_list
   [WHEN when_value THEN statement_list] ...
   [ELSE statement_list]
END CASE

Or:


CASE
   WHEN search_condition THEN statement_list
   [WHEN search_condition THEN statement_list] ...
   [ELSE statement_list]
END CASE
Case_When_Example1
DROP Procedure if Exists `Case_When_Example1`;

DELIMITER $$

CREATE PROCEDURE Case_When_Example1()
BEGIN

  -- Declare two variables x and y.
  Declare x integer;
  Set x  = 3 ;
 
  -- Check the value of x
  Case x
   -- When x = 1 then
   When 1 then    
     Select 'one' as log_info1;
   
   -- When x = 2 then
   When 2 then
      Select 'Two' as log_info2;
     
   -- When x = 3 then
   When 3 then
      Select 'Three' as log_info3;
     
   -- Other case
   Else
      Select 'Other' as log_info3;
     
   End case;
 
 
END;
Case_When_Example2
DROP Procedure if Exists `Case_When_Example2`;

DELIMITER $$

CREATE PROCEDURE Case_When_Example2()
BEGIN

  -- Declare two variables x and y.
  Declare x integer;
  Set x  = 3 ;
 
  -- Kiểm tra các trường hợp:
  Case  
   -- When x = 1 then
   When x = 1 then    
     Select 'one' as log_info1;
   
   -- When x = 2 then
   When x = 2 then
      Select 'Two' as log_info2;
     
   -- When x = 3 then
   When x = 3 then
      Select 'Three' as log_info3;
     
   -- Other case
   Else
      Select 'Other' as log_info3;
     
   End case;
 
 
END;
LOOP
Syntax:
LOOP
 -- Do something here
EXIT WHEN <Condition>;
END LOOP;
In LOOP you can use LEAVE to exit the loop. RETURN can also be used, but it is a statement of exiting procedure (or function).
Use ITERATE statement for the purpose of skipping statement lines in LOOP block and below it, in order to continue a new loop.
LEAVE is similar to BREAK while ITERATE is similar to CONTINUE in other programming languages like Java, C/C++, C#.
Loop_Example1
DROP Procedure if Exists `Loop_Example1`;

DELIMITER $$

CREATE PROCEDURE Loop_Example1()
BEGIN

  -- Declare 2 variables x and y.
  Declare x,y integer;
  Set x  = 0 ;
  Set y = 10;
 
  -- Start loop named My_Loop
  My_Loop: Loop
      -- Every time the loop execute, x increase by 1
      Set x = x + 1;
      -- Every time the loop execute, x decrease by 2
      Set y = y - 2;
     
      if x > y then
           -- Exit the loop - My_Loop.
          Leave My_Loop;
      End if;
     
  End Loop My_Loop;
     
  -- Write log
  SELECT Concat('x,y = ', x,', ', y) as Log_Info;
 
END;
Loop_Example2
DROP Procedure if Exists `Loop_Example2`;

DELIMITER $$

CREATE PROCEDURE Loop_Example2()
BEGIN

   -- Declare two variables x and y.
   Declare x,y integer;
   Set x  = 0 ;
   Set y = 10;
   
   -- Start loop named My_Loop
   My_Loop: Loop
       -- Each time the loop run the value of x increases by 1
       Set x = x + 1;
       -- Each time the loop runs, the value of y decreased by 2
       Set y = y - 2;
       
       if x <= y then
            -- Ignoring the commands below (And in Loop block) to continue.
           Iterate My_Loop;
       End if;
       
       -- Exiting the loop.
       Leave My_Loop;
       
   End Loop My_Loop;
       
   -- Ghi ra log
   SELECT Concat('x,y = ', x,', ', y) as Log_Info;
   
END;
REPEAT .. UNTIL
REPEAT .. UNTIL loop means repeating .. until the condition is not true any more. It is similar to do .. while loop in Java, C/C++, C# language.
Syntax:
FOR v_Index IN <Min value> .. <Max value>
LOOP
 -- Do something here
END LOOP;
In REPEAT.. UNTIL loop, you also can use LEAVE, and ITERATE statement without changing the meaning (More details in LOOP).
Repeat_Until_Example
DROP Procedure if Exists `Repeat_Until_Example`;

DELIMITER $$

CREATE PROCEDURE Repeat_Until_Example()
BEGIN

  -- Declare 2 variables x and y.
  Declare x integer;
  Set x  = 0 ;
 
  -- Start loop named My_Loop
  My_Loop: Repeat
 
      -- Every time the loop execute, x increase by 1.
      Set x = x + 1;
     
      -- Write log
      SELECT Concat('x = ', x) as Log_Info;
 
  -- Exit the loop when x > 3
  Until x > 3    
  End Repeat My_Loop;      
 
 
END;
WHILE .. DO
Syntax:
WHILE <Condition> LOOP
 -- Do something here
END LOOP;
In WHILE...DO loop, you can also use LEAVE statement and ITERATE statement without changing its meaning (For more details in LOOP).
While_Do_Example
DROP Procedure if Exists `While_Do_Example`;

DELIMITER $$

CREATE PROCEDURE While_Do_Example()
BEGIN

  -- Declare 2 variables x and y.
  Declare x integer;
  Set x  = 0 ;
 
  -- Start loop named My_Loop
  My_Loop: While x < 5 Do
 
      -- Every time the loop execute, x increases by 1.
      Set x = x + 1;
     
      -- Write log
      SELECT Concat('x = ', x) as Log_Info;
     
  End While My_Loop;      
 
 
END;

5. Exception Handling

During the programming of MySQL, there may be some errors. For example, when you insert records with an identical primary key, MySQL will throw out an exception with code corresponding to the cause.

In MySQL in order to process exception cases, you define a variable, and tell MySQL to assign value for this variable when the exception occur. You can check the value of variable in order to handle exception.
For example start with exceptions
Consider a procedure that inserts data into the Department table:
Handling_Exception_Example1
DROP Procedure if Exists `Handling_Exception_Example1`;

DELIMITER $$

CREATE PROCEDURE Handling_Exception_Example1()
BEGIN

-- Logging
Select 'Three';  

-- Insert into Department
-- Dept_Id: value assigned automatically
-- No problems with this statement
Insert into department(Dept_Id,Name)
values (null, 'Dept 1');

-- Write log
Select 'Two';  

-- Insert into Department
-- Exists a record in the DB with Dept_ID = 1
--  (Error here!!)
Insert into department(Dept_Id,Name)
values (1 , 'Dept 2');


-- Write log
Select 'One!! Lets go';

END
After you execute the above procedure:
  1. Statements (1), (2), (3) run without any problem
  2. Statement (4) makes error due to the insertion of a record into Department table which causes an identical key to the available one in DB. It exists the procedure.
  3. Statement (5) is not executed.
We edit the code of that procedure and add sections of code that handle exception.
Handling_Exception_Example1 (2)
DROP Procedure if Exists `Handling_Exception_Example1`;

DELIMITER $$

CREATE PROCEDURE Handling_Exception_Example1()
BEGIN

-- Declare a handler for error 1062, Print out a message.
-- And continue working (CONTINUE).
DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Found Error: Ignore to Continue'  as My_Message;

-- Write log
Select 'Three';  

-- Insert into Department
-- Dept_Id:  value assigned automatically
-- No problems with this statement
Insert into department(Dept_Id,Name)
values (null, 'Dept 1');

-- Write log
Select 'Two';  

-- Insert into Department
-- Exists a record in the DB with Dept_ID = 1
--  (Error here!!)
Insert into department(Dept_Id,Name)
values (1 , 'Dept 2');


-- Write log
Select 'One!! Lets go';

END
Please see the illustration when you call the procedure above:
You have declared CONTINUE handler for error 1062. That means that after error occur (Code 1062), the program continues to run the line of code below.

When you call the procedure:
  1. The command line (1), (2), (3) runs without any problems.
  2. Statement (4) throws an error in 1062, duplicate in primary key (Column DEPT_ID). The program print out a message.
  3. Line code (5) continue to be run.
In case you want the program to exit the block of statements when error arises, you can declare the error handler to be EXIT.
-- -- Declare EXIT handler
-- When errors occur 1062, it write a message, and exit block
-- (The block containing the error statement).
DECLARE EXIT HANDLER FOR 1062 SELECT 'Found Error: Exit now!!' as My_Message;
Example 2:
Handling_Exception_Example2
DROP Procedure if Exists `Handling_Exception_Example2`;

DELIMITER $$

CREATE PROCEDURE Handling_Exception_Example2()
BEGIN

-- Declare a variable
Declare error1602 Varchar(1) default 'N';

-- Declare CONTINUE handler for error 1062.
-- If error occur, assign error1602 = 'Y'.
-- And continue.
DECLARE CONTINUE HANDLER FOR 1062 Set error1602 = 'Y';

-- Write log
Select 'Three';  

-- Insert into Department
-- DEPT_ID column value are assigned automatically
-- No problem with this statement.
Insert into department(Dept_Id,Name)
values (null, 'Dept 1');

-- Write log
Select 'Two';  

-- Insert into Department
-- (Error here! Dupplicate primary key Dept_ID = 1)
--  
 Insert into department(Dept_Id,Name)
 values (1 , 'Dept 2');

 if error1602 = 'Y' then
    Select 'Found error Duplicate Entry (1062)' as My_Message;  
 End if;

  -- Write log
 Select 'One!! Lets go';
 
END
Declaring exception handler
The above examples illustrate how to handle exception in MySQL. And now you can see the general rule.
Syntax:
DECLARE <action> HANDLER FOR <condition_value> <statement>;
<action>
Can get two values CONTINUE or EXIT.
  • CONTINUE: Tell the program that the error occurred execute <statement> and continue.
  • EXIT: Tell the program that the error occurred execute <statement> and exit the parent block.
<condition_value>
Describe the characteristics of that error when errors arise, this handler will be active. It may be:
  • A specific error code (a number), for example:
    • 1062 was error when inserting a record that its ID already exists.
  • A string with 5 characters (standard SQLSTATE value), for example:
    • HY000 is the error code saying the drive is full.
    • HY001 is due to a buffer overflow error.
  • A common error that is named by MySQL or user, for example:
    1. SQLWARNING: is the standard warning that the code started by '01'.
    2. NOTFOUND: is the class of the standard error code (SQLSTATE) started by '02'. often involves to treatment of Cursors.
    3. SQLEXCEPTION: A class of errors that standard code had not started by '00', '01', '02'. Note that the code started by '00' is the success message.
Note: If a condition arises, you do not declare a handler, the program will be handled by default, depending on conditions class :
  1. For SQLEXCEPTION conditions, the program will stop the procedure at error location.
  2. For SQLWARNING or NOTFOUND conditions, the program continues to execute, just like with the CONTINUE handler.
Specifically:
  1. A MySQL error code (a number) or an SQLSTATE value (a 5-character string literal). You should not use MySQL error code 0 or SQLSTATE values that begin with '00', because those indicate success rather than an error condition.
  2. SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.
  3. NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). For an example, see “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.
  4. SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.

If a condition occurs for which no handler has been declared, the action taken depends on the condition class:
  1. For SQLEXCEPTION conditions, the stored program terminates at the statement that raised the condition, as if there were an EXIT handler. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers.
  2. For SQLWARNING or NOT FOUND conditions, the program continues executing, as if there were a CONTINUE handler.

You can look up the error code and error messages at:

Error code example:
-- Duplicate primary key errors. (When you insert)
DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';
SQLException example:
-- The SQLSTATE not started by '00', '01', '02':
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
   ROLLBACK;
   SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;
Get DIAGNOSTICS
Once exception happened. You want to get information about the error, such as error code, SQLSTATE, and error message, and other informations ...
  • TODO

Newest Articles

Show More