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
已有 0 人发表留言，猛击->>这里<<-参与讨论
ITeye推荐
—软件人才免语言低担保 赴美带薪读研！—