MySQL存储过程的创建和调用

--  创建

DELIMITER //
create procedure authenti(in p1 varchar(32), in p2 varchar(32), in p3 varchar(32), in p4 varchar(45), out p5 int)
begin
-- 权限检查
-- p1:项目ename; p2:接口ename; p3:appkeyindex; p4:appkey
-- by wuzude 2018-01-22 ver 1.0
    declare rawsum int(1) default 0;   -- 行计数器
    declare projid int(4) default 0;   -- 项目ID
    declare intfcid int(4) default 0;  -- 接口ID
declare appkeyid int(11) default 0;  -- appkey序号
    set p5 = 0;
    -- 取得有效的项目ID和接口ID，对行计数
    select count(pi_id), proj_id, intfc_id into rawsum, projid, intfcid
    from proj_intfc
    where proj_id = (select proj_id from proj where ename = p1 and valid = 1)
    and intfc_id = (select intfc_id from intfc where ename = p2 and valid = 1)
    and valid = 1;
-- select rawsum,projid,intfcid;
if rawsum = 1 then
set rawsum = 0;
-- 取得有效的appkey_id(顺序号)，对行计数
select count(appkey_id), appkey_id into rawsum, appkeyid
from appkeys
where appkeyindex = p3
and appkey = p4
and valid = 1
and ((unix_timestamp(now()) * 1000) < expiretime or remain_num > 0);

if rawsum = 1 then
set rawsum = 0;
-- 查APPKEY的项目权限
select count(power_id) into rawsum
from  power_proj
where appkey_id = appkeyid
and   proj_id = projid
and   valid = 1;

if rawsum = 1 then
set rawsum = 0;
-- 查APPKEY的接口权限
select count(power_id) into rawsum
from power_intfc
where appkey_id = appkeyid
and intfc_id = intfcid
and valid = 1;

if rawsum = 1 then
set p5 = 1;
end if;

end if;

end if;

end if;
select p5;
end //
DELIMITER ;


-- 在phpmyadmin中调用
-- 出参必须使用一个变量接住
call authappkey('ccav', 'avcc', '1fb997cc', '6191555132980973', @out) 

// 在PHP中调用
$sql = "call authenti($proj, $intfc, $appkeyindex, $appkey, @out)";
if($T->authenti($sql, "p5"))
{
    echo "验证成功";
}

public function authenti($sql, $tag)
{
    $CN = $this->cn();
    $R = $this->r();
    $M = $this->m();
    if($CN->connect_error)
    {
        $R->send(1120);
        $M->error($this->ms() . "," . __FILE__ . ",Line:" . __LINE__ . ",authenti():" . $CN->connect_error);
    }
    $resource = $CN->query($sql);
    if($resource === false)
    {
        $R->send(1121);
        $M->error($this->ms() . "," . __FILE__ . ",Line:" . __LINE__ . ",authenti():cn->query($sql)=false");
    }
    $result = $this->obj2arr(mysqli_fetch_array($resource, MYSQLI_BOTH));  //转数组
    mysqli_free_result($resource);
    $CN->close();
    //return $result;
    if(!isset($result[$tag]))
    {
        $R->send(1122);
        $M->error($this->ms() . "," . __FILE__ . ",Line:" . __LINE__ . ",authenti():未找到名为" . "【" . $tag. "】" . "的key");
    }
    if($result[$tag] != 1)
    {
        $R->send(1123);
        $M->error($this->ms() . "," . __FILE__ . ",Line:" . __LINE__ . ",authenti():proj|intfc|appkeyindex|appkey错误");
    }
    return true;
}