有mysql下面function中有这么一段:
请问,怎样才能得到,不会要一个一个编辑吧?我在 vs类似中中可以下断点再得到其相关的值。SQLs的sql 语句 ?我用的是toad for mysql谢谢了DROP FUNCTION IF EXISTS generiton.GET_RES_QC_OOC_SQL;
CREATE FUNCTION generiton.`GET_RES_QC_OOC_SQL`(Lot_Id VARCHAR(30) ,ParaName VARCHAR(30)) RETURNS varchar(8096) CHARSET utf8
BEGIN
DECLARE SQLs VARCHAR(8096);
SET SQLs = ' SELECT DISTINCT t2.RUNNO AS RUNNO, 1 AS Layer, t2.workorder, t3.side,';
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.*');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.twafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO = t2.MonitorRunNo)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''6%'')))');
SET SQLs = CONCAT(SQLs, ' UNION');
SET SQLs = CONCAT(SQLs, ' SELECT DISTINCT t2.RUNNO2 AS RUNNO, 2 AS Layer, t2.workorder, t3.side,');
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.* ');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.twafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo2 <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO2 = t2.MonitorRunNo2)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO2 <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.RUNNO2 <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''7%'')))');
SET SQLs = CONCAT(SQLs, ' UNION');
SET SQLs = CONCAT(SQLs, ' SELECT DISTINCT t2.RUNNO AS RUNNO, 2 AS Layer, t2.workorder, t3.side,');
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.* ');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.twafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo2 = '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO = t2.MonitorRunNo)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''7%'')))');
SET SQLs = CONCAT(SQLs, ' UNION');
SET SQLs = CONCAT(SQLs, ' SELECT DISTINCT t2.RUNNO AS RUNNO, 2 AS Layer, t2.workorder, t3.side,');
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.* ');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.tscrapwafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo2 = '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO = t2.MonitorRunNo)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''7%'')))');
RETURN SQLs;
END;
请问,怎样才能得到,不会要一个一个编辑吧?我在 vs类似中中可以下断点再得到其相关的值。SQLs的sql 语句 ?我用的是toad for mysql谢谢了DROP FUNCTION IF EXISTS generiton.GET_RES_QC_OOC_SQL;
CREATE FUNCTION generiton.`GET_RES_QC_OOC_SQL`(Lot_Id VARCHAR(30) ,ParaName VARCHAR(30)) RETURNS varchar(8096) CHARSET utf8
BEGIN
DECLARE SQLs VARCHAR(8096);
SET SQLs = ' SELECT DISTINCT t2.RUNNO AS RUNNO, 1 AS Layer, t2.workorder, t3.side,';
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.*');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.twafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO = t2.MonitorRunNo)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''6%'')))');
SET SQLs = CONCAT(SQLs, ' UNION');
SET SQLs = CONCAT(SQLs, ' SELECT DISTINCT t2.RUNNO2 AS RUNNO, 2 AS Layer, t2.workorder, t3.side,');
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.* ');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.twafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo2 <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO2 = t2.MonitorRunNo2)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO2 <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.RUNNO2 <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''7%'')))');
SET SQLs = CONCAT(SQLs, ' UNION');
SET SQLs = CONCAT(SQLs, ' SELECT DISTINCT t2.RUNNO AS RUNNO, 2 AS Layer, t2.workorder, t3.side,');
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.* ');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.twafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo2 = '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO = t2.MonitorRunNo)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''7%'')))');
SET SQLs = CONCAT(SQLs, ' UNION');
SET SQLs = CONCAT(SQLs, ' SELECT DISTINCT t2.RUNNO AS RUNNO, 2 AS Layer, t2.workorder, t3.side,');
SET SQLs = CONCAT(SQLs, ' CAST(t1.VALUE AS DECIMAL(10, 5)) AS VALUE,');
SET SQLs = CONCAT(SQLs, ' t1.CREATTIME AS COL_TIME');
SET SQLs = CONCAT(SQLs, ' FROM ( ( generiton.tcolplanhistory t1');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' (SELECT w.* ');
SET SQLs = CONCAT(SQLs, ' FROM generiton.twafer w');
SET SQLs = CONCAT(SQLs, ' WHERE w.lotid=''', Lot_Id, '''');
SET SQLs = CONCAT(SQLs, ' ) t2');
SET SQLs = CONCAT(SQLs, ' )');
SET SQLs = CONCAT(SQLs, ' JOIN');
SET SQLs = CONCAT(SQLs, ' generiton.tscrapwafer t3)');
SET SQLs = CONCAT(SQLs, ' WHERE ( (t1.WAFERID = t3.WAFERID)');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo2 = '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.MonitorRunNo <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO = t2.MonitorRunNo)');
SET SQLs = CONCAT(SQLs, ' AND (t3.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (t2.RUNNO <> '''')');
SET SQLs = CONCAT(SQLs, ' AND (UCASE(t1.NAME) = ''', ParaName ,''')');
SET SQLs = CONCAT(SQLs, ' AND SUBSTR(t1.SHOPFLOORNODEIDS,');
SET SQLs = CONCAT(SQLs, ' (LOCATE('','', t1.SHOPFLOORNODEIDS) + 1)) IN');
SET SQLs = CONCAT(SQLs, ' (SELECT tshopfloornode.SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' AS SHOPFLOORNODEID');
SET SQLs = CONCAT(SQLs, ' FROM tshopfloornode');
SET SQLs = CONCAT(SQLs, ' WHERE (tshopfloornode.NAME LIKE ''7%'')))');
RETURN SQLs;
END;
把FUNCTION换成procedure 然后执行存储过程
CREATE DEFINER
PROCEDURE ps_ddgl_sbxx_selectby_sbmc_lxmc(IN ps_sbxxmc VARCHAR(20) CHARACTER SET UTF8, IN ps_sblxid INT(11), IN ps_jgxx VARCHAR(20) CHARACTER SET UTF8,IN ps_start INT(11), IN ps_limit INT(11))
BEGIN
DECLARE ps_sqlwhere VARCHAR(200) DEFAULT ' '; SET ps_sqlwhere = concat(' where 1=1 ');
IF (ps_sbxxmc != 'all' && length(ps_sbxxmc) > 0) THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and sbmc like "%', ps_sbxxmc, '%"');
END IF;
IF (ps_jgxx != 'all' && length(ps_jgxx ) > 0) THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and jgm =', ps_jgxx );
END IF; IF (ps_sblxid != 0) THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and sblxid =', ps_sblxid, '');
END IF;
SET @ps_sqltotal = concat(' SELECT count(*) INTO @total FROM ddgl_sbxx ', ps_sqlwhere);
SET @ps_sqltext = concat(' SELECT sb_id as sbid, sbmc, sbbh, cfwz, jgm, sbzt, sfyxtfgg, sblxid, sblxmc, sbbzxx ,(SELECT sbxx.sbmc FROM ddgl_sbxx sbxx WHERE sbxx.sbbh=ddgl_sbxx.qzsbbh limit 1 )as qzsbbh,@total as total FROM ddgl_sbxx ', ps_sqlwhere, ' order by sb_id ', ' limit ', ps_start, ',', ps_limit);
PREPARE stmt FROM @ps_sqltotal;
EXECUTE stmt;
PREPARE stmt2 FROM @ps_sqltext;
EXECUTE stmt2;
END