存储过程的学习以及使用2

在这里插入代码片1.这边所做的存储过程的功能主要是,对一些表或一个表的数据 进行查询 ,之后将所查询的数据赋予给新的表中.
– 创建存储过程

CREATE PROCEDURE my_procdure ()

– 开始存储过程

BEGIN

– 自定义变量1

DECLARE my_id VARCHAR (32);

– 自定义变量2

DECLARE my_name VARCHAR (50);

– 自定义 控制 游标循环变量，默认false

DECLARE done INT DEFAULT FALSE;

– 定义游标并输入结果

DECLARE my_Cursor CURSOR FOR (SELECT id, NAME FROM t_people);

– 绑定控制变量到游标，游标循环

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

– 打开游标

OPEN my_Cursor;

– 开始循环体，myLoop为自定义循环名，结束循环时用到

myLoop :
LOOP
-- 将游标当前读取行的数据顺序富裕自定义变量12
FETCH my_Cursor INTO my_id,
my_name;

– 判断是否继续循环

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;




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 ;