使用“like”
CREATE procedure pro_sql_data(in sear_name varchar(2000))
BEGIN
declare vid bigint(20);
if sear_name is not null and sear_name!='' then
select ad_place_id into vid from ad_place where name like concat('%',sear_name,'%');
end if;
...
END$$
使用“=”
CREATE procedure pro_sql_data(in sear_name varchar(2000))
BEGIN
declare vid bigint(20);
if sear_name is not null and sear_name!='' then
select ad_place_id into vid from ad_place where name =sear_name;
end if;
...
END$$
在游标中使用变量
CREATE procedure pro_sql_data(in combineId bigint(20))
BEGIN
declare vid bigint(20);
-- 定义游标
DECLARE rs_cursor CURSOR FOR select type,object_id from combine_code_normal where combine_id=combineId;
...
END$$
在动态sql中使用
CREATE procedure pro_sql_data(in qfs varchar(20),in ids varchar(20))
BEGIN
declare sql1 varchar(2000);
set sql1 = concat('select name into @colsTmp from dd_report_query_fields e where e.status!=-1 and table_id in (',ids,')');
if qfs is not null and qfs !='' THEN
set sql1 = concat(sql1,' and (e.type=2 and e.table_id in (',qfs,')) ');
end if;
set @ms=sql1;
PREPARE s1 from @ms;
EXECUTE s1;
deallocate prepare s1;
set colsStr = @colsTmp;
...
END$$