Top

A Linux User Reference

Search tips
  • search ignores words that are less than 4 characters in length
  • searches are case insensitve
  • if a search does not return anything try it in Boolean mode then Query expansion mode by checking the appropriate radio button e.g. searching for 'cron' in just the Administration category returns nothing - presumably because the 50% threshold is reached. Boolean mode ignores this threshold so a search for 'cron' returns several hits
  • in Boolean mode preceding a word with a '+' means the result must include that word, a '-' means it must not
  • in Boolean mode '+crontab -anacron' means match articles about crontab that DO NOT mention anacron
  • to match a phrase e.g. 'manage system' check the Boolean mode radio button and enclose the phrase in quotes "some phrase ..."
  • in Query expansion mode the search context is expanded beyond the keywords you entered - relevancy of hits may well be degraded

DATABASE

MySql stored procedures

  • Stored procedure, routine
    • A stored routine (procedures and functions) are a set of sql statements stored on the server.
    • A client calls the procedure or function thus negating the need to enter all the sql statements that are contained within the procedure or function.
    • They are often used when clients are written in different languages or run on different platforms as a stored routine provides a common interface to the DB.
    • They provide an extra layer of security through provision of a consistent and secure environment. Routines can be logged, clients do not have or need access to tables just the routine(s).
    Performance pros

    the main performance benefit is they use less network bandwidth, less data is sent to the server over the network.

    Performance cons

    the downside to this is that most of the processing is carried out on the server. Too many routines called frequently may degrade overall server performance.

    Stored procedure grants

    For users other than the DB administrator (granted by default) certain priveleges need to be granted in order to create, modify or run stored procedures.

    Privilege Actions
    CREATE ROUTINE needed to create stored routines.
    ALTER ROUTINE needed to alter or drop stored routines. Granted automatically to the routine creator, dropped when the routine creator drops the routine.
    EXECUTE required to execute stored routines. Granted and dropped automatically as for alter routine. Default SQL SECURITY characteristic for a routine is DEFINER - users with access to the DB with which the routine is associated can execute.

    If the 'automatic_sp_privileges' system variable is 0, the EXECUTE and ALTER ROUTINE privileges are not automatically granted and dropped.

    Give user permissions to execute a routine

    mysql> show procedure status;
    +-----------+----------+-----------+----------------+----- / --+---------+--+
    | Shop_demo | perf_ind | PROCEDURE | root@localhost | 2010 / 3 | DEFINER |  |
    | Shop_demo |     test | PROCEDURE | root@localhost | 2010 / 4 | DEFINER |  |
    +-----------+----------+-----------+----------------+----- / --+---------+--+
    2 rows in set (0.00 sec)
    
    mysql> grant execute on procedure perf_ind to anonymous;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for anonymous;
    +----------------------------------------------------------------------+
    |                                GRANT USAGE ON *.* TO 'anonymous'@'%' |
    | GRANT EXECUTE ON PROCEDURE `Shop_demo`.`perf_ind` TO 'anonymous'@'%' |
    +----------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
  • Create procedure

    Command usage

    create procedure <proc_name>(
                     [param type]
                     [param name]
                     [param data type])
    
    [param type] Description
    IN Default. Passes a value into a procedure. The procedure may modify the value but the modification is not visible to the caller when the procedure returns.
    OUT Passes a value from the procedure back to the caller. Its initial value is NULL within the procedure and its value is visible to the caller when the procedure returns.
    INOUT Initialised by the caller, can be modified by the procedure and any change made by the procedure is visible to the caller when the procedure returns.
  • Get details on and delete a stored routine

    View procedure details

    mysql> show procedure status;
    +-----------+----------+-----------+----------------+----- / --+---------+--+
    | Shop_demo | perf_ind | PROCEDURE | root@localhost | 2010 / 3 | DEFINER |  |
    | Shop_demo |     test | PROCEDURE | root@localhost | 2010 / 4 | DEFINER |  |
    +-----------+----------+-----------+----------------+----- / --+---------+--+
    2 rows in set (0.00 sec)
    
    mysql> show create procedure test;
    +-----------+----------+------------------------------------ / -------+
    | Procedure | sql_mode | Create Procedure                    /        |
    +-----------+----------+------------------------------------ / -------+
    | test      |          | CREATE DEFINER=`root`@`localhost` P / s; end |
    +-----------+----------+------------------------------------ / -------+
    1 row in set (0.00 sec)
    

    Although not obvious from this example, the complete code is returned in the 'Create Procedure' column.

    Delete a procedure

    mysql> drop procedure test;
    Query OK, 0 rows affected (0.00 sec)
    
  • Using an 'out' type parameter

    Pass a value back to the caller

    mysql> delimiter |
    mysql> create procedure test(out res char(12))
        -> begin
        ->    select 'hello there' into res;
        -> end;
        -> |
    Query OK, 0 rows affected (0.01 sec)
    mysql> delimiter ;
    mysql>
    mysql> call test(@result);
    Query OK, 0 rows affected (0.00 sec)
    mysql>
    mysql> select @result;
    +-------------+
    | @result     |
    +-------------+
    | hello there |
    +-------------+
    1 row in set (0.00 sec)
    
  • Using cursors
    • A cursor is a temporary work area created in the system memory when a SQL statement is executed.
    • A cursor contains information on a select statement and the rows of data accessed by it.
    • Cursors allow for the row by row processing of a result set.
    • Cursor declarations must appear before handler declarations and after variable and condition declarations.

    Cursors example

    mysql> delimiter //
    mysql> -- A stored procedure to calculate three performance indicators
    mysql> -- Requires access to - 'Product_history'
    mysql> -- Mark
    mysql> create procedure perf_ind()
        -> begin
        ->   /*Declarations:
        ->   Variables */
        ->   declare margin, turnover, ratio varchar(28);     # String vars labelling each \
                                                                performance indicator
        ->   declare unit1 char(6) default " times";          # Turnover Ratio unit
        ->   declare unit2 char(5) default " days";           # Turnover Ration unit
        ->   declare equ char(3) default "=";
        ->   declare mres,tres,rres decimal(8,2);
        ->
        ->   /* Cursors */
        ->   declare marg cursor for select (sum(Sales*Unit_sale)-sum(
        ->     (Opening_stock+Purchases-Closing_stock)*Unit_cost))/(sum(Sales*Unit_sale))
        ->     from Product_history;
        ->   declare turn cursor for select (sum((Opening_stock+Closing_stock)*Unit_cost)/2)/
        ->     (sum((Opening_stock+Purchases-Closing_stock)*Unit_cost)/120)
        ->     from Product_history;
        ->   declare rat cursor for select (sum((Opening_stock+Purchases-Closing_stock)*Unit_cost))/
        ->     (sum((Opening_stock+Closing_stock)*Unit_cost)/2)
        ->     from Product_history;
        ->
        ->   /* Populate variables */
        ->   set margin="Gross Profit Margin";
        ->   set turnover="Stock Turnover (4 months)";
        ->   set ratio="Stock Turnover Ratio";
        ->
        ->   /* Open cursors */
        ->   open marg;
        ->   open turn;
        ->   open rat;
        ->
        ->   /* Fetch next row - only a single row returned by cursor */
        ->   fetch marg into mres;
        ->   fetch turn into tres;
        ->   fetch rat into rres;
        ->
        ->   /* Smarten up the  output a bit */
        ->   select rpad(margin,28, ' ') as "Indicator", equ as " ", mres as " ";
        ->   select rpad(turnover,28,' ') as "Indicator",equ as " ", tres as " ", unit2 as " ";
        ->   select rpad(ratio,28,' ') as "Indicator", equ as " ", rres as " ", unit1 as " ";
        ->
        ->   /* Close cursors */
        ->   close marg;
        ->   close turn;
        ->   close rat;
        -> end;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    

    Call, run the stored routine

    mysql> call perf_ind();
    +------------------------------+------+------+
    | Indicator                    |      |      |
    +------------------------------+------+------+
    | Gross Profit Margin          | =    | 0.61 |
    +------------------------------+------+------+
    1 row in set (0.00 sec)
    
    +------------------------------+------+-------+-------+
    | Indicator                    |      |       |       |
    +------------------------------+------+-------+-------+
    | Stock Turnover (4 months)    | =    | 20.95 |  days |
    +------------------------------+------+-------+-------+
    1 row in set (0.00 sec)
    
    +------------------------------+------+------+--------+
    | Indicator                    |      |      |        |
    +------------------------------+------+------+--------+
    | Stock Turnover Ratio         | =    | 5.73 |  times |
    +------------------------------+------+------+--------+
    1 row in set (0.00 sec)
    

    Warnings occur due to truncation of decimal values.

  • Using a loop

    Looping example

    mysql> delimiter |
    mysql> -- Procedure to calculate the Gross Profit per month and to date
    mysql> -- Creates an 'interim' table to store and then returns the table as the result \
              before dropping it.
    mysql> -- Mark
    mysql> create procedure pandl()
        -> begin
        ->   declare done int default 0;
        ->   declare a varchar(15);
        ->   declare b,c,d decimal(8,2);
        ->   declare e varchar(8);
        ->   declare cur1 cursor for select Monthname(date),sum(Sales*Unit_sale),
        ->      sum(Purchases*Unit_cost),sum((Sales*Unit_sale)-(Purchases*Unit_cost))
        ->      from Product_history group by date;
        ->   declare cur2 cursor for select sum(Sales*Unit_sale),
        ->      sum(Purchases*Unit_cost), sum((Sales*Unit_sale)-(Purchases*Unit_cost))
        ->      from Product_history;
        ->
        ->   /* Declare a loop handler to control number of iterations.  Variable 'a' = 0 on 
        ->      entering the loop, the handler sets 'a' = 1 when there are no more rows to 
        ->      retrieve from the cursor. */
        ->   declare continue handler for not found set done = 1;
        ->
        ->   drop table if exists profitloss;
        ->   create table profitloss (Month varchar(15), Income decimal(8,2),
        ->       Purchases decimal(8,2), GrossProfit decimal(8,2));
        ->
        ->   open cur1;
        ->   open cur2;
        ->
        ->   /* Loop through the rows returned by 'cur1' and insert the Monthly results into
        ->      'interim' table 'profitloss'. */
        ->   repeat
        ->     fetch cur1 into a,b,c,d;
        ->     if not done then
        ->         insert into profitloss values (a,b,c,d);
        ->     end if;
        ->   until done end repeat;
        ->
        ->   /* Calculate column totals for to date results and insert into 'profitloss'.
        ->      Only a single row.  Could just have easily summed the columns in 'profitloss'. */
        ->   fetch cur2 into b,c,d;
        ->   set e="TOTALS:";
        ->   insert into profitloss values (e,b,c,d);
        ->
        ->   close cur1;
        ->   close cur2;
        ->
        ->   /* Display the resulting table then drop it. */
        ->   select * from profitloss;
        ->   drop table profitloss;
        -> end;
        -> |
    Query OK, 0 rows affected (0.00 sec)
    

    Call the procedure

    mysql> call pandl();
    +---------+----------+-----------+-------------+
    | Month   | Income   | Purchases | GrossProfit |
    +---------+----------+-----------+-------------+
    | April   |  1619.35 |    689.68 |      929.67 |
    | May     |  2488.93 |   1262.64 |     1226.29 |
    | June    |  3179.38 |   1130.68 |     2048.70 |
    | July    |  3120.92 |   1481.64 |     1639.28 |
    | TOTALS: | 10408.58 |   4564.64 |     5843.94 |
    +---------+----------+-----------+-------------+
    5 rows in set (0.00 sec)