• mysql7笔记----存储过程实例


    mysql创建存储过程

    DROP PROCEDURE IF EXISTS getCreateTimes
    /*前面要写DELIMITER $$ 或DELIMITER // */
    DELIMITER $$ 
    CREATE PROCEDURE `getCreateTimes`()
     BEGIN
      SELECT userCreateTime FROM users;
    
    END;

    mysql 存储过程有参数输入拼接

    DROP PROCEDURE IF EXISTS getTest01;
    DELIMITER $$ 
    CREATE PROCEDURE `getTest01`(
      uname VARCHAR(50),
      upass VARCHAR(50)
    )
     BEGIN
     SET @sql= 'SELECT * FROM users where 1=1';
     IF uname IS NOT NULL THEN
          SET @sql=CONCAT(@sql,' AND userName=',"'",uname,"'");
          END IF;
      IF upass IS NOT NULL THEN
          SET @sql=CONCAT(@sql,' AND password=',"'",upass,"'");
          END IF;
      PREPARE stmt FROM @sql;
       EXECUTE stmt;    
    END;
    
    CALL getTest01(NULL,"123");

    mysql存储过程含输入参数的拼接的分页 

     DROP PROCEDURE IF EXISTS getTest01;
    DELIMITER $$ 
    CREATE PROCEDURE `getTest01`(
      startPage INT,/*第startPage页,从0开始算*/
      pageSize INT,/*每页显示的记录数*/
      uname VARCHAR(50),
      upass VARCHAR(50)
    )
     BEGIN
     SET @sql= 'SELECT * FROM users where 1=1';
     IF uname IS NOT NULL THEN
          SET @sql=CONCAT(@sql,' AND userName=',"'",uname,"'");
          END IF;
      IF upass IS NOT NULL THEN
          SET @sql=CONCAT(@sql,' AND password=',"'",upass,"'");
          END IF;
          SET @sql=CONCAT(@sql,' LIMIT ',startPage*pageSize,",",pageSize);
      PREPARE stmt FROM @sql;
       EXECUTE stmt;    
    END;
    
    CALL getTest01(4,2,NULL,NULL);
  • 相关阅读:
    采用重写tostring方法使ComboBox显示对象属性
    JavaScript中正则表达式test()、exec()、match() 方法
    高级软件工程实践总结作业
    用户使用调查报告
    Beta阶段置顶集合
    Beta冲刺阶段总结随笔
    Beta冲刺Day7
    Beta冲刺Day6
    Beta冲刺Day5
    Beta冲刺Day4
  • 原文地址:https://www.cnblogs.com/tk55/p/10652807.html
Copyright © 2020-2023  润新知