DBMS/MySQL

[MYSQL] πŸ“š Select into 문법 정리

인파_ 2022. 6. 18. 06:40

Select into

Select into

MySQLμ—μ„œ select into 문법은 μ‚¬μš©μž μ •μ˜ λ³€μˆ˜μ— select ν•œ ν•„λ“œ 값을 μ§‘μ–΄λ„£κΈ° μœ„ν•΄μ„œ μ‚¬μš©λœλ‹€.

보톡 μŠ€ν† μ–΄λ“œ ν”„λ‘œμ‹œμ € λ‚΄μ—μ„œ 즐겨 쓰인닀.

BEGIN
    DECLARE a int;
    DECLARE b int;
    
    select user_name, user_id into a, b from user_table
    -- user_tableμ—μ„œ μ‘°νšŒν•œ user_nameκ³Ό user_id ν•„λ“œκ°’μ„ μœ„μ—μ„œ μ„ μ–Έν•œ μ§€μ—­λ³€μˆ˜ a, b에 각각 λ„£λŠ”λ‹€.

END$$
DELIMITER $$
 
CREATE PROCEDURE GetCustomerLevel(
    IN  p_customerNumber int(11), 
    OUT p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
 
    SELECT creditlimit INTO creditlim -- selectν•œ κ²°κ³Ό creditlimit ν•„λ“œ 값을 μœ„μ—μ„œ μ„ μ–Έν•œ μ§€μ—­λ³€μˆ˜ creditlim에 λ„£λŠ”λ‹€ 
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
 	-- κ·Έλ ‡κ²Œ selectν•œ 값을 λŒ€μž…ν•œ λ³€μˆ˜λ₯Ό μ΄μš©ν•˜μ—¬ μ½”λ”©ν•œλ‹€.
    IF creditlim > 50000 THEN
        SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
 
END$$
 
DELIMITER ;