注:name是存储过程的名字
//TODO:存储过程
//传参
// $aid1 = ‘report_aid’;
//
m
a
i
n
2
=
"
c
a
l
l
s
a
m
p
l
e
n
u
m
(
′
"
.
main2 = "call sample_num('".
main2="callsamplenum(′".aid1."’)";
//
r
e
s
u
l
t
2
=
D
b
:
:
q
u
e
r
y
(
result2 = Db::query(
result2=Db::query(main2);
// $res = Db::query(‘call name(:in_param1,in_param2,)’,
// [
// ‘in_param1’ => $in_param1,
// ‘in_param2’ => $in_param2,
// ]);
$res = Db::query(‘call name’);
//如果存储过程不需要返回数据
Db::execute(‘call name’);
数据库-存储过程代码
传入的参数声明
注:数据库函数过程(传入的是不同字段时)
BEGIN
#Routine body goes here...
DECLARE curr_time date DEFAULT NOW();
DECLARE datas TINYTEXT DEFAULT NULL;
DECLARE opt VARCHAR(255) DEFAULT '';
DECLARE min_time VARCHAR(200) DEFAULT '';
DECLARE start_hour VARCHAR(200) DEFAULT '';
DECLARE end_hour VARCHAR(200) DEFAULT '';
DECLARE last_res VARCHAR(500) DEFAULT '';
DECLARE hour_time date DEFAULT NOW();
SET min_time = DATE_ADD(curr_time, INTERVAL -7 DAY);
DROP TEMPORARY TABLE
IF EXISTS main;
CREATE TEMPORARY TABLE main (
`date` VARCHAR(100) DEFAULT NULL,
`count` varchar(50) DEFAULT NULL
);
SET datas = '';
SET opt = CONCAT(" AND ",aid," <> -1");
WHILE min_time < curr_time
DO
SET start_hour = min_time;
SET end_hour = DATE_ADD(start_hour, INTERVAL +2 DAY);
set @sel_sql = CONCAT("SELECT COUNT(id) AS count FROM sample
WHERE is_del = 1",opt," AND last_commit_time >'", start_hour ,"' AND last_commit_time < '", end_hour ,"' ORDER BY last_commit_time into @datas");
-- SELECT COUNT(id) AS count FROM sample
-- WHERE is_del = 1 AND opt <> -1 AND last_commit_time > start_hour AND last_commit_time < end_hour ORDER BY last_commit_time INTO datas;
set @excutesql = @sel_sql;
PREPARE stmt from @excutesql;
execute stmt;
DEALLOCATE PREPARE stmt;
SELECT @datas INTO datas;
INSERT INTO main(`date`,`count`) VALUES(curr_time,IFNULL(datas,"''"));
SET min_time = DATE_ADD(min_time, INTERVAL +1 DAY);
END WHILE;
SELECT GROUP_CONCAT(count) from main into last_res;
SELECT last_res;
END