同表插入测试数据

最新推荐文章于 2023-10-11 18:36:49 发布

jis117

最新推荐文章于 2023-10-11 18:36:49 发布

阅读量152

点赞数

分类专栏： mysql 文章标签：数据库 php

本文链接：https://blog.csdn.net/jis117/article/details/84619615

版权

mysql 专栏收录该内容

51 篇文章 0 订阅

订阅专栏


-- phpMyAdmin SQL Dump
-- version 4.0.4.2
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2014 年 07 月 28 日 07:34
-- 服务器版本: 5.6.13
-- PHP 版本: 5.4.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- 数据库: `demo`
--

-- --------------------------------------------------------

--
-- 表的结构 `t_address`
--

CREATE TABLE IF NOT EXISTS `t_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(50) NOT NULL,
  `province` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `postCode` varchar(50) NOT NULL,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;

--
-- 转存表中的数据 `t_address`
--

INSERT INTO `t_address` (`id`, `country`, `province`, `city`, `postCode`, `createTime`) VALUES
(1, '中国', '辽宁', '沈阳', '110000', '2014-07-28 06:09:58'),
(2, '中国', '广东', '深圳', '510001', '2014-07-28 06:10:29'),
(3, '中国', '北京', '北京', '100000', '2014-07-28 06:10:57'),
(4, '美国', '纽约州', '纽约', '12201', '2014-07-28 06:23:39');


-- -------------------------------------------
-- 同表插入测试数据
-- -------------------------------------------
DROP PROCEDURE IF EXISTS proc_test;
DELIMITER $
CREATE PROCEDURE proc_test(IN loop_times INT)
BEGIN
	DECLARE id INT;
	DECLARE country VARCHAR(255);
	DECLARE province VARCHAR(255);
	DECLARE city VARCHAR(255);
	DECLARE postCode INT DEFAULT 0;
	DECLARE createTime TIMESTAMP;

	DECLARE var INT DEFAULT 0;
	DECLARE	num INT DEFAULT 0;
	DECLARE done BOOLEAN DEFAULT FALSE;
	DECLARE cur CURSOR FOR SELECT t.id, t.country, t.province, t.city, t.postCode, t.createTime FROM t_address t;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	-- declare continue handler FOR SQLSTATE '02000' SET done = 1;  

	OPEN cur;
	FETCH NEXT FROM cur INTO id, country, province, city, postCode, createTime;
	flag: WHILE var < loop_times DO
		IF done THEN
			LEAVE flag;
		END IF;

		SET var = var + 1;
		INSERT INTO t_address VALUES(NULL, country, province, city, postCode, NULL);
		FETCH NEXT FROM cur INTO id, country, province, city, postCode, createTime;
	END WHILE;
	CLOSE cur;
END
$
DELIMITER ;
-- -------------------------------------------
CALL proc_test(6);
-- -------------------------------------------
SELECT t.* FROM t_address t;
-- -------------------------------------------