MySQL存储过程例子

DELIMITER $$

DROP PROCEDURE IF EXISTS `fxmonkey`.`SPSearchSystemPerformance` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SPSearchSystemPerformance`(
   _systemName VARCHAR(30),
   _pair  VARCHAR(20),
   _grossPlMax   DOUBLE ,
 _grossPlMin   DOUBLE ,
 ...
   _calcDate DATE
)
BEGIN

DECLARE _sql VARCHAR(2000);

SET @sql = '
SELECT systemID, systemName,pair, grossPL, pl,
       numTrades, maxDD, profitFactor, pipsTrade, creationDate,
       avgTradeTime, maxPos, riskAdjust, winPercent, po,
       otherAPT, otherALT, otherLWT, otherLLT, otherDIS,
       calcStartDate, calcEndDate
FROM TblSystemPerformance
WHERE 1 = 1
';

SET @sql = CONCAT(@sql, ' AND grossPL >= ' , _grossPlMin ,
                        ' AND grossPL <= ' , _grossPlMax , ' ');
SET @sql = CONCAT(@sql, ' AND pl >= ' , _plMin ,
                        ' AND pl <= ' , _plMax , ' ');
...
IF (_calcDate IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND YEAR(calcStartDate) = ', YEAR(_calcDate) ,
                        ' AND MONTH(calcStartDate) = ', MONTH(_calcDate), ' ');
END IF;
IF (_creationDate IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND creationDate >= ', _creationDate ,' ');
END IF;
IF (_systemName IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND systemName = ''', _systemName , ''' ');
END IF;
IF (_pair IS NOT NULL) THEN
SET @sql = CONCAT(@sql, ' AND pair LIKE ''%', _pair , '%'' ');
END IF;

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

 

END $$

DELIMITER ;

 

 =======================================

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `fxmonkey`.`SPIntelligentMiningDavid` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SPIntelligentMiningDavid`()
BEGIN

Create TEMPORARY TABLE _TblSystemSeeds (
  systemName varchar(30),
  pair varchar(20)
);

 

INSERT _TblSystemSeeds
SELECT systemName, pair FROM (
SELECT systemName, pair, COUNT(systemID) 'ccc' FROM TblSystemPerformance

WHERE
(
   (  YEAR(calcStartDate) = 2009 AND MONTH(calcStartDate) in (1)  )
   OR
   (  YEAR(calcStartDate) = 2008 AND MONTH(calcStartDate) in (11,12)  )
)
 AND grossPl > 0

 AND numTrades > 10 AND numTrades < 100
 AND maxDd > -1000
 AND maxPos < 5

GROUP BY systemName, pair

) consecutive
WHERE ccc > 1
;

 

SELECT COUNT(*) FROM _TblSystemSeeds;

 

SELECT COUNT(*) FROM _TblSystemSeeds seeds, TblSystemPerformance pers
WHERE seeds.systemName = pers.systemName AND seeds.pair = pers.pair

AND grossPL > 0
AND YEAR(pers.calcStartDate) = 2009 AND MONTH(pers.calcStartDate) in (2)
;

TRUNCATE TABLE  _TblSystemSeeds;
DROP TABLE _TblSystemSeeds;

END $$

DELIMITER ;

 

===============================================

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `DBName`.`StoredProcsName` $$
CREATE DEFINER=`root`@`169.181.251.0/255.255.255.0` PROCEDURE `StoredProcsName`(paraName Type)
BEGIN
  Create TEMPORARY TABLE _rtn (
    cid int null,
    groupId varchar(15) null,
    reportTypes varchar(200) null,
    ReportingInd varchar(20) null,
    MgnRiskInd varchar(20) null
    said int null,
    sa varchar(8) null);

  insert _rtn
  SELECT distinct _ClientId as ClientId,
    SB.groupId,
    '' as reportTypes,
    case when (GRP.eqReportingInd ='Y')
    then 'Reporting,'
    else '' end as eqReportingInd,
    case when (GRP.eqMarginInd='Y')
    then 'Margin,'
    case when (GRP.eqMgnRiskVirtualInd='Y')
    then 'Margin & Risk (Virtual),' end as eqMgnRiskVirtualInd,
    AH.SubAcctId AS SubAcct,

    case when PFS.subAcct is null then '' else PFS.subAcct end as subAcct
  FROM GroupSubAcct SB  LEFT outer JOIN Group GRP ON (SB.groupId = GRP.groupId)
  LEFT OUTER JOIN AcctHierarchy AH ON (SB.Id=AH.Id AND AH.accountType='SUB-ACCT')
  LEFT OUTER JOIN SubAcct PFS ON (AH.SubAcctId=PFS.Id)
  WHERE AH.ClientId=_ClientId GROUP BY AH.SubAcctId ORDER BY SB.groupId,AH.SubAcctId;
  /* update temp table and concat all the strings*/
  update _rtn
     set reportTypes=
      concat(eqReportingInd,eqMarginInd,eqAFCMEInd,eqRiskInd,eqAdminInd,eqInterestInd,
      eqMgnRiskInd,eqMgnRiskVirtualInd);
  /* remove the last ','*/
  update _rtn
  set reportTypes = Left(reportTypes,length(reportTypes)-1) where length(reportTypes)>0;
/* return */
  select  ClientId,
          groupId,
          case when reportTypes is null then '' else reportTypes end as reportTypes,
          SubAcct,
          subAcct
  from _rtn order by groupId, SubAcct;
  /*delte temp data*/
  drop table  _rtn;
END $$

DELIMITER ;