mysql預存程序例子

標籤：arch   out   varchar   定義變數   limit   inner   mysql預存程序   procedure   建立   

/*
定義變數
方式1：set @變數名=值;
方式2：select 值 into @變數名;
方式3：declare 變數名 類型(字串類型加範圍) default 值;

in參數 入參的值會僅在預存程序中起作用
out參數 入參的值會被置為空白，儲存中計算的值會影響外面引用該變數的值
inout參數 入參的值不會被置為空白，儲存中計算的值會影響外面引用該變數的值
*/
use mysql;
/*建立1個預存程序*/
delimiter $$
DROP PROCEDURE IF EXISTS porc_person_02;
CREATE PROCEDURE porc_person_02(IN p1 INT, OUT p2 INT, INOUT p3 VARCHAR(20))
BEGIN
DECLARE innerp1 VARCHAR(10) DEFAULT ‘this is innerp1‘;
DECLARE innerp2 VARCHAR(10) DEFAULT ‘this is innerp2‘;
SET p1=10;
SET p2=20;
SET p3=‘this is 字串‘;

if p1=10 then
select ‘p1 is 10‘;
end if;

if p1=p2 then
select ‘p1=p2‘;
else
select p1,p2,p3;
end if;

case p3
when ‘a‘ then
select ‘p3 is a‘;
when ‘b‘ then
select ‘p3 is b‘;
when ‘c‘ then
select ‘p3 is c‘;
else
select p3;
end case;

/*條件不滿足會被終止*/
while p1>4
do
set p1=p1-1;
end while;
select p1;

checka:loop
set p1=p1+1;
if p1=14 then
leave checka;
end if;
end loop;
select p1;

/*條件滿足會被終止*/
repeat
set p1=p1-1;
until p1=6
end repeat;
select p1;

END;
$$

set @p_in=3;
set @p_out=2;
set @p_inout=‘b‘;
select ‘check procedure‘ into @p4;

call porc_person_02(@p_in,@p_out,@p_inout);
select @p_in,@p_out,@p_inout,@p4;

mysql預存程序例子