当前位置：首页 > news >正文

一个简单的存储过程的学习以及使用

news 来源：原创 2024/5/6 9:14:46

我这边所做的存储过程的功能主要是,对一些表或一个表的数据进行查询 ,之后将所查询的数据赋予给新的表中.

操作如下:

1.创建表

2.创建存存储过程

3.执行存储过程

4.比对表之间的数据

下面的是参考别人的一个存储过程:

create procedure my_procedure() -- 创建存储过程
begin -- 开始存储过程
declare my_id varchar(32); -- 自定义变量1
declare my_name varchar(50); -- 自定义变量2
DECLARE done INT DEFAULT FALSE; -- 自定义控制游标循环变量,默认false
 
DECLARE My_Cursor CURSOR FOR ( SELECT id, name FROM t_people ); -- 定义游标并输入结果集
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 绑定控制变量到游标,游标循环结束自动转true
 
OPEN My_Cursor; -- 打开游标
  myLoop: LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到
    FETCH My_Cursor into my_id, my_name; -- 将游标当前读取行的数据顺序赋予自定义变量12
    IF done THEN -- 判断是否继续循环
      LEAVE myLoop; -- 结束循环
    END IF;
    -- 自己要做的事情,在 sql 中直接使用自定义变量即可
    UPDATE t_user SET c_name = my_name WHERE id = my_id and rtrim(ltrim(c_name)) = ''; -- 左右去空格
 
    COMMIT; -- 提交事务
  END LOOP myLoop; -- 结束自定义循环体
  CLOSE My_Cursor; -- 关闭游标
END; -- 结束存储过程

下面是之前需要写的一个SQl 包含建表的sql

DROP TABLE
IF
EXISTS `report_seller_settle`;
CREATE TABLE
IF
NOT EXISTS `report_seller_settle` (
  `id` BIGINT ( 20 ) NOT NULL,
  `seller_id` BIGINT ( 20 ) DEFAULT '0' COMMENT '商家ID',
  `seller_name` VARCHAR ( 200 ) DEFAULT '' COMMENT '商家名称',
  `money_amount` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '结算现金总额',
  `efenbao_integral_amount` INT ( 11 ) DEFAULT '0' COMMENT 'e分宝积分总额',
  `icon_integral_amount` INT ( 11 ) DEFAULT '0' COMMENT '惠金币总额',
  `voucher_amount` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '代金券总额',
  `gift_amount` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '礼品卡总额',
  `comm_amount` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '佣金总额',
  `promotion_paid_amount` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '活动优惠金额',
  `settle_amount` DECIMAL ( 10, 2 ) DEFAULT '0.00' COMMENT '结算金额',
  `year` INT ( 11 ) DEFAULT '0' COMMENT '结算日期-年',
  `month` INT ( 11 ) DEFAULT '0' COMMENT '结算日期-月',
  `day` INT ( 11 ) DEFAULT '0' COMMENT '结算日期-日',
  `report_time` date DEFAULT '3000-12-31' COMMENT '结算日期',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ( `id` ) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 3333 DEFAULT CHARSET = utf8mb4 COMMENT = '商家结算报表';


DROP PROCEDURE
IF
EXISTS `SELECT_SELLER_SETTLE`;
DELIMITER //
CREATE PROCEDURE `SELECT_SELLER_SETTLE` ( ) BEGIN
  -- 声明变量
  DECLARE
  NO_MORE_RECORD INT DEFAULT 0;
  DECLARE
  `r_id` BIGINT ( 20 );
  DECLARE
  `r_sellerId` BIGINT ( 20 );
  DECLARE
  `r_sellerName` VARCHAR ( 200 );
  DECLARE
  `r_moneyAmount` DECIMAL ( 10, 2 );
  DECLARE
  `r_efenbaoIntegralAmount` INT ( 11 );
  DECLARE
  `r_iconIntegralAmount` INT ( 11 );
  DECLARE
  `r_voucherAmount` DECIMAL ( 10, 2 );
  DECLARE
  `r_giftAmount` DECIMAL ( 10, 2 );
  DECLARE
  `r_commAmount` DECIMAL ( 10, 2 );
  DECLARE
  `r_promotionPaidAmount` DECIMAL ( 10, 2 );
  DECLARE
  `r_sellerAmount` DECIMAL ( 10, 2 );
  DECLARE
  `r_year` INT ( 11 );
  DECLARE
  `r_month` INT ( 11 );
  DECLARE
  `r_day` INT ( 11 );
  DECLARE
  `r_reportTime` date;
  DECLARE
  `r_createTime` date;
  -- 声明游标
  DECLARE
    r_cursor CURSOR FOR SELECT
                               id,
                               seller_id,
                               seller_name,
                               money_amount,
                               efenbao_integral_amount,
                               icon_integral_amount,
                               voucher_amount,
                               gift_amount,
                               comm_amount,
                               promotion_paid_amount,
                               settle_amount,
                               `year`,
                               `month`,
                               `day`,
                               report_time,
                               create_time
                        FROM
                             emateshop.seller_settle_report
                        ORDER BY
                                 report_time DESC,
                                 `id` ASC;
  DECLARE
  CONTINUE HANDLER FOR NOT FOUND
  SET NO_MORE_RECORD = 1;
  TRUNCATE  table report_seller_settle;
  OPEN r_cursor;
  REPEAT
    FETCH r_cursor INTO r_id,
      r_sellerId,
      r_sellerName,
      r_moneyAmount,
      r_efenbaoIntegralAmount,
      r_iconIntegralAmount,
      r_voucherAmount,
      r_giftAmount,
      r_commAmount,
      r_promotionPaidAmount,
      r_sellerAmount,
      r_year,
      r_month,
      r_day,
      r_reportTime,
      r_createTime;
    IF NOT NO_MORE_RECORD THEN
    REPLACE INTO report_seller_settle (
        id,
        seller_id,
        seller_name,
        money_amount,
        efenbao_integral_amount,
        icon_integral_amount,
        voucher_amount,
        gift_amount,
        comm_amount,
        promotion_paid_amount,
        settle_amount,
        `year`,
        `month`,
        `day`,
        report_time,
        create_time
        )
    VALUES
           (
               r_id,
               r_sellerId,
               r_sellerName,
               r_moneyAmount,
               r_efenbaoIntegralAmount,
               r_iconIntegralAmount,
               r_voucherAmount,
               r_giftAmount,
               r_commAmount,
               r_promotionPaidAmount,
               r_sellerAmount,
               r_year,
               r_month,
               r_day,
               r_reportTime,
               r_createTime
               );
  END IF;
  UNTIL NO_MORE_RECORD
  END REPEAT;
  CLOSE r_cursor;

END//
DELIMITER ;

另外调用存储过程