我現(xiàn)在想通過(guò)mysql的存儲(chǔ)過(guò)程執(zhí)行動(dòng)態(tài)SQL，傳遞一個(gè)表名查詢(xún)，根據(jù)表名查詢(xún)表中的第一個(gè)列。
根據(jù)第一列獲取其列中的最大值，下面是我寫(xiě)的存儲(chǔ)過(guò)程。但始終獲取不到最大值的結(jié)果，請(qǐng)幫忙看一下 是什么原因?
DELIMITER $$
CREATE PROCEDURE genBillsNoProc(IN tableName VARCHAR(30),OUT data_t VARCHAR(100))
BEGIN
DECLARE sql_1 VARCHAR(1000);
DECLARE sql_2 VARCHAR(1000);
SET sql_1 = CONCAT("SELECT COLUMN_NAME into @colName FROM information_schema.COLUMNS WHERE table_name='",tableName,"' limit 0,1");
SET sql_2 = CONCAT("SELECT MAX(?) FROM ",tableName);
SET @frist_sql=sql_1;
PREPARE stmt FROM @frist_sql;
EXECUTE stmt;
SET data_t := @colName;
SET @second_sql=sql_2;
PREPARE stmt1 FROM @second_sql;
EXECUTE stmt1 USING @colName;
SELECT sql_2 ;
END$$
##執(zhí)行存儲(chǔ)過(guò)程
CALL genBillsNoProc('t_common_system',@data_t);
運(yùn)行存儲(chǔ)過(guò)程返回結(jié)果就是一個(gè)列名:
col_xxx
問(wèn)題補(bǔ)充：呵呵，問(wèn)題解決了
正確的SQL：
DELIMITER $$
CREATE PROCEDURE genBillsNoProc(IN tableName VARCHAR(30),OUT data_t VARCHAR(100))
BEGIN
DECLARE sql_1 VARCHAR(1000);
DECLARE sql_2 VARCHAR(1000);
SET sql_1 = CONCAT("SELECT COLUMN_NAME into @colName FROM information_schema.COLUMNS WHERE table_name='",tableName,"' limit 0,1");
#執(zhí)行sql_1SQL語(yǔ)句;
SET @frist_sql=sql_1;
PREPARE stmt FROM @frist_sql;
EXECUTE stmt;
SET sql_2 = CONCAT("select max(",@colName,") into @sql_result from ",tableName);
#執(zhí)行sql_2SQL語(yǔ)句;
SET @second_sql=sql_2;
PREPARE stmt1 FROM @second_sql;
EXECUTE stmt1;
#設(shè)置返回結(jié)果;
SET data_t := @sql_result;
END$$
##執(zhí)行存儲(chǔ)過(guò)程
CALL genBillsNoProc('t_common_system',@data_t);
SELECT @data_t;
