Stored Procedures

• My sorce for this is "MySQL StoredProcedures" by Peter Gulutzan.
• Procedures take parameters and return nothing (except through the parameter list)
• Functions return a value.
• User variables
  • We will need to have variables at the top level variables to test parameters
  • So we will start there
  • A reference
  • variables consist
    • alphanumerics
    • .
    • _
    • $
  • They are session specific
  • set @id=value
    • If you'd like to type another character you can use :=
    • If you want you can do several at a time
    • set @foo="hello", @world=7;
    • int, decimal, float, string, NULL
    • Or another variable
    • Or the result of a function (cast, concat, ...)
  • They are NULL by default.
  • They may be displayed with select
  • They can be used most places

    mysql> set @foo="Camping";
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from SKU_DATA where Department=@foo;
    
    	

  • They caution that order of evaluation might not be as expected.
  • set @a = @a +1; is ok.
  • So don't assign and use in the same statement.
  • Should not be used in HAVING, GROUP BY or ORDER BY clauses of a SELECT
  • They are somewhat limited in use, but they will do fine for us.
  • These are global by the way, we will see this later.
• We are a number of arguments for using stored procedures
  • Reduction of network traffic might mean speed improviements.
  • The are part of the Databse, all apps have access to them.
  • They are part of SQL so they (should)
    • Migrate between DBMS
    • Migrate between OS
  • They are stored as part of the DB, so you have code IN THE DB
• Creating Procedures/Functions
  • CREATE PROCEDURE
  • DROP PROCEDURE
  • A list of them with SELECT name, type FROM mysql.proc;
  • As well as SHOW PROCEDURE STATUS;
  • SHOW CREATE PROCEDURE procname
  • There is an alter command for functions
    • but it can not be used to alter the body.
    • so it is probably not as useful as you would like.
    • Create them with scripts.
  • CALL to call.
• A simple example

  • CREATE PROCEDURE simple() SELECT * from SKU_DATA;
    	

• Procedure identifiers
  • Not case sensetive (P1 = p1)
  • No overloading.
  • Max length 64 characters
  • Can include spaces, but why?
  • You can overload existing mysql identifiers.

    SELECT pi();
    
    CREATE PROCEDURE pi() SELECT 3.14159;
    
    CALL pi();
    
    delimiter #
    CREATE FUNCTION pi() RETURNS INTEGER BEGIN RETURN 3; END; #
    delimiter ;
    	

• Parameters
  • IN, OUT, INOUT
  • In is optional, it is the default is nothing is passed..
  • id
  • type

     CREATE PROCEDURE params(IN a INTEGER, OUT b INTEGER, INOUT c INTEGER)
        BEGIN
            SELECT a, b,c;
            SET a = 1;
            SET b = 2;
            SET c = 3;
            SELECT a,b,c;
        END;  
        #
    
    	

• Characteristics
  • COMMENT 'string'
  • LANGUAGE SQL
  • [NOT] DETERMINISTIC
  • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  • all are optional
  • comment is a MySQL extension
  • Mysql ignores LANGUAGE for now, but perhaps in the future ....
  • LANGUAGE is required for other DBMS however.
  • DETERMINISTIC
    • Default NOT DETERMINISTIC
    • If you declare it to be DETERMINISTIC it MUST BE DETERMINISTIC
    • Same Input -> Same Ouput
    • Not depending on values stored in tables for example.
    • Apparently TIME and RAND are ok however?
  • SQL SECURITY {DEFINER | INVOKER}
    • Who the function will run as.
    • uses DEFINER field
• Code blocks
  • BEGIN statements END;
  • Empty is legal
  • Can be nested
• Prohibited code in body
  • CREATE/ALTER/DROP PROCEDURE/FUNCTION
  • USE database;
  • LOAD DATA IFILE
  • LOCK TABLES
• Local Variables
  • DECLARE id[, id ...] type [DEFAULT value];
  • NULL is the default if default is missing.
  • valid identifiers
  • SCOPE : within the nested BEGIN END blocks
  • Name hiding as in C/C++
  • Not in scope for PREPAREd statements
  • Should be different from table column names.
    • MySQL will not access the column in such cases, it will prefer the variable.
• We saw IF-THEN-ELSEIF-ELSE last time.
• case
• While
• REPEAT
• LOOP