Notice
Recent Posts
Recent Comments
Link
«   2025/04   »
일 월 화 수 목 금 토
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
Tags more
Archives
Today
Total
관리 메뉴

개발의변화

프로시저 반복문 본문

카테고리 없음

프로시저 반복문

refindmySapporo 2023. 6. 13. 10:46
반응형

프로시저 반복 => 

DECLARE i INT DEFAULT 1;

WHILE  i <= 5000  DO

 

 

     SET i = i + 1;

END WHILE;

BEGIN

   DECLARE 
   SET,SELECT

   RETURN

END

APP_INFO

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_APP_INFO`()
BEGIN
   DECLARE i INT DEFAULT 1;
   DECLARE random_custnum VARCHAR(50);
   DECLARE random_bankingpass VARCHAR(30);
   DECLARE random_reclogindate DATETIME;
   
   WHILE i <= 5000 DO
SELECT custnum INTO random_custnum
FROM CUST_INFO
ORDER BY RAND()
LIMIT 1;
    
    SET random_bankingpass = (
SELECT CONCAT(
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
    SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1)
            ) AS random_bankingpass 
    ); 
    
    SET random_reclogindate = (
        SELECT 
    );
END WHILE;
    
   
END

    SET random_string = (
            SELECT CONCAT(
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
                SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1)
            ) AS random_string
        );
               SET random_birth = (
            SELECT DATE_SUB(DATE('2003-06-12'), INTERVAL FLOOR(RAND() * 29200) DAY) AS random_birth
        );
        SET random_category = (
            SELECT CASE FLOOR(RAND() * 2)
                WHEN 0 THEN '개인'
                WHEN 1 THEN '사업자'
            END AS random_category
        );
        


        
        SET random_corporatenum = (
            SELECT CONCAT(
                LPAD(FLOOR(RAND() * 1000), 3, '0'),
                '-',
                LPAD(FLOOR(RAND() * 100), 2, '0'),
                '-',
                LPAD(FLOOR(RAND() * 100000), 5, '0')
            ) AS random_corporatenum
        );
        
        SET random_birth = (
            SELECT DATE_SUB(DATE('2003-06-12'), INTERVAL FLOOR(RAND() * 29200) DAY) AS random_birth
        );
        SET random_gender = (
			SELECT CASE FLOOR(RAND() * 2)
                WHEN 0 THEN 'M'
                WHEN 1 THEN 'F'
            END AS random_gender
        );
	SET random_residentnum = (
		SELECT CONCAT(
			DATE_FORMAT(DATE_SUB(DATE('2003-06-12'), INTERVAL FLOOR(RAND() * 29200) DAY), '%y%m%d'),
			'-',
			CASE random_gender
				WHEN 'M' THEN '1'
				WHEN 'F' THEN '2'
			END,
			LPAD(FLOOR(RAND() * 10000000), 6, '0')
			) AS random_residentnum
		);
		SET random_address = (
         SELECT LPAD(FLOOR(RAND() * 100000), 5, '0') AS random_address  
        );
		SET random_phonenum = (
             SELECT CONCAT(
                '010',
                '-',
                LPAD(FLOOR(RAND() * 10000), 4, '0'),
                '-',
                LPAD(FLOOR(RAND() * 10000), 4, '0')
            ) AS random_phonenum         
        );
		SET random_privacyconsent = (
		  SELECT CASE FLOOR(RAND() * 2)
             WHEN 0 THEN 'Y'
             WHEN 1 THEN 'N'
		  END AS random_privacyconsent
        );
        SET random_job = (
		  SELECT CASE FLOOR(RAND()*6) 
             WHEN 0 THEN '학생'
             WHEN 1 THEN '공무원'
             WHEN 2 THEN '회사원'
             WHEN 3 THEN '주부'
             WHEN 4 THEN '전문직'
             WHEN 5 THEN '무직'
		  END AS random_job
        );
        SET random_creditrating = (
		   SELECT FLOOR(RAND() * 10) + 1 AS random_creditrating
        );
        SET random_custgrade = (
            SELECT FLOOR(RAND() * 5) + 1 AS random_custgrade
        );
        SET random_productstatus = (
          SELECT CASE FLOOR(RAND()*2) 
              WHEN 0 THEN 'Y'
			  WHEN 1 THEN 'N'
		  END AS random_productstatus
        );

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_APP_INFO`()
BEGIN
   DECLARE i INT DEFAULT 1;
   DECLARE random_bankingid VARCHAR(10);
   DECLARE random_custnum VARCHAR(50);
   DECLARE random_bankingpass VARCHAR(30);
   DECLARE random_reclogindate DATETIME;
   
   WHILE i <= 10 DO
       SET random_bankingid = (
           SELECT CONCAT(
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', FLOOR(RAND() * 52) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1)
           ) AS random_bankingid
       );
       
       SELECT custnum INTO random_custnum
       FROM CUST_INFO
       ORDER BY RAND()
       LIMIT 1;
       
       SET random_bankingpass = (
           SELECT CONCAT(
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1),
               SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*()-=_+', FLOOR(RAND() * 74) + 1, 1)
           ) AS random_bankingpass
       );
       
       SET random_reclogindate = (
           SELECT DATE_SUB(DATE('2023-06-13'), INTERVAL FLOOR(RAND() * 500) DAY) AS random_reclogindate
       );
       
       INSERT INTO APP_INFO (bankingid, custnum, bankingpass, reclogindate) VALUES(random_bankingid,random_custnum,random_bankingpass,random_reclogindate);
       SET i = i + 1;
   END WHILE;
   
END

CREATE FUNCTION `generate_random_num8` ()
RETURNS INTEGER

DETERMINISTIC
BEGIN
  DECLARE random_num8 INT ;
  SET random_num8 = (
  SELECT LPAD(FLOOR(RAND() * 100000000),8,'0') AS random_num8
  );
RETURN random_num8;
END

반응형
저작자표시 비영리 변경금지