Mysql Database Event and Procedure(2)Store Procedure

Mysql Database Event and Procedure(2)Store Procedure

F.Y.I mysqldump command
>mysqldump -h localhost -u root -P 7778 -ppassword databasename table name --where "click_time >= '2016-12-01'" > affiliate_clicks_2016_12_01.sql

Import Command
mysql -h localhost -u username -p'password' databasename < clicks2_2016_12.sql

MySQL supports event scheduler after 5.1, supports stored procedure after 5.0.

First Example
DELIMITER //
CREATE PROCEDURE proc1(OUT s int)
BEGIN
     SELECT COUNT(1) INTO s FROM user;
END
//
DELIMITER ;

IN -  input parameter, procedure can not return and change that parameter
OUT - procedure can change that variable and return
INOUT -

IN Example
DELIMITER //
CREATE PROCEDURE demo_in_parameter (IN p_in int)
BEGIN
     SELECT p_in;
     SET p_in = 2;
     SELECT p_in;
END;
//
DELIMITER ;

Call the procedure
>SET @p_in = 1;
>CALL demo_in_parameter(@p_in);
p_in will be 2 in the procedure, but it is still 1 outside.

OUT Example
DELIMITER //
CREATE PROCEDURE demo_out_parameter(OUT p_out int)
BEGIN
     SELECT p_out;
     SET p_out=2;
     SELECT p_out;
END;
//
DELIMITER ;

INOUT Example
DELIMITER //
CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
BEGIN
     SELECT p_inout;
     SET p_inout=2;
     SELECT p_inout;
END;
//
DELIMITER ;

Call the procedure
SET @p_inout = 1;
CALL demo_inout_parameter(@p_inout);

Variable
DECLARE variable_name datatype [DEFAULT value];
DECLARE l_varchar archer(255) DEFAULT ’this is a sample default value’;

SET
SET variable_name = expression

USER Variable
SELECT ‘Hello World’ into @x;
SELECT @x;

SET @y=‘Goodbye Cruel World’;
SELECT @y;

Query All Tables
>show tables;

Query All Procedure
>show procedure status where db = 'jobs';

Query detail of Procedure, databaseName.procedureName
>show create procedure jobs.get_lineitem;

Condition Statement
DELIMITER //
CREATE PROCEDURE proc2(IN parameter int)
begin
     declare var int;
     set var = parameter+1;
     if var = 0 then
          insert into t values(17);
     end if;
     if parameter = 0 then
          update t set s1=s1+1;
     else
          update t set s1=s1+2;
     end if;
end;
//
DELIMITER ;

case Statement
DELIMITER //
CREATE PROCEDURE proc3 (in parameter int)
begin
     declare var int;
     set var=parameter+1;
     case var
          when 0 then
               insert into t values(17);
          when 1 then
               insert into t values(18);
          else
               insert into t values(19);
     end case;
end;
//
DELIMITER ;

Loop Statement - while … end while
DELIMITER //
CREATE PROCEDURE proc4()
begin
     declare var int;
     set var = 0;
     while var < 6 do
          insert into t values(var);
          set var=var+1;
     end while;
end;
//
DELIMITER ;

Loop Statement - repeat … end repeat
DELIMITER //
CREATE PROCEDURE proc5 ()
begin
     declare v int;
     set v=0;
     repeat
          insert into t values(v);
          set v=v+1;
          until v >=5
     end repeat;
end;
//
DELIMITER ;

Loop Statement - loop … end loop
DELIMITER //
CREATE PROCEDURE proc6()
begin
     declare v int;
     set v=0;
     LOOP_LABLE:loop
          insert into t values(v);
          set v=v+1;
          if v >=5 then
               leave LOOP_LABLE;
          end if:
     end loop:
end

Functions in Stored Procedure
ONCAT, LCASE, LEFT, LENGTH
ABS(number), round(number)
NOW(), DATE(datetime)

References:
http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

http://www.jianshu.com/p/1cb06d5eda09
http://www.blogjava.net/nonels/archive/2008/10/09/233324.html
https://yq.aliyun.com/articles/20804