有人能告诉我我错过了什么吗?我已经花了4个小时,看了很多文章,不明白为什么显示返回空
mysql-mysql程序如下。
create procedure proc_studymaterial_listing(
get_exam_id int(11)
)
begin
set @query1 = concat( "select c.content_type,
c.title,
c.status,
c.created_on,
c.pdf_is_downloadable,
a.content_id,
a.expiry_date,
exam_Name,
fun_subject_name(a.subject_id) as subject_name,
fun_subject_name(a.chapter_id) as chapter_name
from tbl_studymaterial_exam_chapter_map as a
join tbl_exam_master as b on a.exam_id= b.exam_ID
join tbl_studymaterial as c on c.id= a.content_id
where c.status=1 " );
if( get_exam_id = 0 )
then
set @exam_id = " and 1=1 ";
else
set @exam_id = concat( ' and b.exam_ID = " ', @get_exam_id , '"' );
end if;
SET @final_query = CONCAT ( @query1,@exam_id );
PREPARE stmt FROM @final_query ;
# select stmt;
# EXECUTE stmt;
# DEALLOCATE PREPARE stmt;
end;
用户定义的变量@get_exam_id
似乎不会在任何地方赋值。如果没有指定非NULL值,则评估此行的结果:
set @exam_id = concat( ' and b.exam_ID = " ', @get_exam_id , '"' );
则CCD_ 2将为NULL。这将级联到下一个CONCAT中,它也将计算为NULL。
用户定义的变量以符号字符处的@
开头。
它们不同于过程参数和变量,它们不以符号字符@
开头。
看起来可能是为了引用局部变量get_exam_id
,而不是用户定义的变量。
跟进
就我个人而言,如果我必须写程序的主体,我会这样做:
DELIMITER $$
CREATE PROCEDURE proc_studymaterial_listing( get_exam_id INT(11))
BEGIN
SET @sql = CONCAT( 'SELECT c.content_type'
,'n' ,' , c.title'
,'n' ,' , c.status'
,'n' ,' , c.created_on'
,'n' ,' , c.pdf_is_downloadable'
,'n' ,' , a.content_id'
,'n' ,' , a.expiry_date'
,'n' ,' , b.exam_name'
,'n' ,' , fun_subject_name(a.subject_id) AS subject_name'
,'n' ,' , fun_subject_name(a.chapter_id) AS chapter_name'
,'n' ,' FROM tbl_studymaterial_exam_chapter_map a'
,'n' ,' JOIN tbl_exam_master b'
,'n' ,' ON b.exam_id = a.exam_id'
,'n' ,' JOIN tbl_studymaterial c'
,'n' ,' ON c.id = a.content_id'
,'n' ,' WHERE c.status = 1'
,'n'
,CASE
WHEN get_exam_id <> 0
THEN ' AND b.exam_id = ? '
ELSE ''
END
,'n' ,' ORDER BY subject_name, chapter_name, exam_name'
);
PREPARE stmt FROM @sql;
IF (get_exam_id <> 0) THEN
SET @id = get_exam_id;
EXECUTE stmt USING @id;
SET @id = '';
ELSE
EXECUTE stmt;
END IF;
DEALLOCATE PREPARE stmt;
SET @sql = '';
END;
$$
DELIMITER ;