mysql存储过程的学习

时间：2022-01-17 11:04:07

平时在工作中写过很多存储过程，但有时候对某些存储过程还是有些困惑的，所以发一篇文章记录下。

标准存储过程写法

create procedure`myQueryTask`(
IN Task_No VARCHAR(100),
IN aLevel TINYINT,
IN aTask_Plan TINYINT,
IN aSTATUS TINYINT,
IN aTask_Type TINYINT,
IN Start_Time VARCHAR(20),
IN End_Time VARCHAR(20),
IN Tel VARCHAR(100),
IN Package_Name VARCHAR(255),
IN Operator_Account VARCHAR(50),
IN aStart INT,
IN size INT,
IN flag INT)
BEGIN
SET @aTask_No=IF(LENGTH(Task_No)=0,NULL,Task_No);

SET @aLevel = alevel;
SET @aTask_Plan = aTask_Plan;
SET @aStatus = aStatus;
SET @aTask_Type = aTask_Type;
SET @aStart_Time=IF(LENGTH(Start_Time)=0,NULL,Start_Time);
SET @aEnd_Time=IF(LENGTH(End_Time)=0,NULL,End_Time);
SET @aTel=IF(LENGTH(Tel)=0,NULL,Tel);
SET @aPackage_Name=IF(LENGTH(Package_Name)=0,NULL,Package_Name);
SET @aOperator_Account=IF(LENGTH(Operator_Account)=0,NULL,Operator_Account);
SET @var_start = aStart ;
SET @var_limit = size;
SET @aflag = flag ;

SET @aSql='SELECT t.Id,t.Task_No,Task_Type,Task_Plan,t.LEVEL,t.STATUS,t.Retry_No,t.Result_Code,t.Create_Time,t.Start_time,t.End_time,t.Update_Time,t.Operator_Name,t.Operator_Account,TA,TSA,TEA,t.Memo,Busi_Type,Tel,t.Result_Memo,Package_Name,tl.name FROM T_Task t,T_Task_List tl WHERE t.Task_No=tl.Task_No';
SET @conSql='SELECT count(*) FROM T_Task t,T_Task_List tl where t.Task_No=tl.Task_No ';
SET @whereSql='';
SET @excSql='';
IF(@aTask_No IS NOT NULL) THEN
    SET @whereSql=CONCAT(' and t.task_no="',@aTask_No,'"');
END IF;
IF(@aLevel > -1) THEN
    SET @whereSql=CONCAT(@whereSql,' and t.Level=',@aLevel);
END IF;
IF(@aTask_Plan > -1) THEN
    SET @whereSql=CONCAT(@whereSql,' and t.Task_Plan=',@aTask_Plan);
END IF;
IF(@aStatus > -1) THEN
    SET @whereSql=CONCAT(@whereSql,' and t.Status=',@aStatus);
END IF;
IF(@aTask_Type > -1) THEN
    SET @whereSql=CONCAT(@whereSql,' and t.Task_Type=',@aTask_Type);
END IF;
IF(@aStart_Time IS NOT NULL) THEN
     SET @whereSql=CONCAT(@whereSql,' and t.Start_Time>"',@aStart_Time,' 23:59:59"');
END IF;
IF(@aEnd_Time IS NOT NULL) THEN
     SET @whereSql=CONCAT(@whereSql,' and t.End_Time<"',@aEnd_Time,' 00:00:00"');
END IF;
IF(@aTel IS NOT NULL) THEN
    SET @whereSql=CONCAT(@whereSql,' and tl.Tel="',@aTel,'"');
END IF;
IF(@aPackage_Name IS NOT NULL) THEN
    SET @whereSql=CONCAT(@whereSql,' and tl.Package_Name="',@aPackage_Name,'"');
END IF;
IF(@aOperator_Account IS NOT NULL) THEN
    SET @whereSql=CONCAT(@whereSql,' and t.Operator_Account="',@aOperator_Account,'"');
END IF;

IF(flag=0) THEN
          SET @excSql=CONCAT(@conSql,@whereSql);
    ELSE
         SET @excSql=CONCAT(@aSql,@whereSql,' ORDER BY create_time DESC ',' limit ',aStart,',',size);
END IF;
SET @excSql=REPLACE(@excSql,'\'\'','\'');
  SET @excSql=REPLACE(@excSql,'\\','');
   insert into T_Temp(value) values(@excSql);
  PREPARE stmt FROM @excSql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

　

如果想在sql语句里面实现多条件查询，则用这种方式

SELECT tel,name AS user_name,business_code,product_name,IFNULL(current_credit,0) AS current_credit,IFNULL(used_credit,0) AS used_credit,update_time FROM T_User_Credits WHERE tel LIKE CONCAT(?,'%') AND IF(?='', 1=1, IFNULL(name,'') LIKE CONCAT(?,'%')) AND status=1 ORDER BY update_time DESC LIMIT ?,?

　　

　

相关文章