[SQL][MySQL] 복합문 ( Compound Statement Syntax ) - 흐름 제어문 ( CASE, IF, LOOP 등 ), 선언문 ( DECLARE ), BEGIN ... END

 

□  복합문

• BEGIN ... END
• DECLARE
  ( variable, condition, cursor, ... )
• FLow Control
  • CASE
  • IF
  • LOOP
  • WHILE
  • RETURN
  • REPEAT
  • ITERATE
  • LEAVE

 

  ◾  BEGIN ... END

[begin_label:] BEGIN
    [statement_list]
END [end_label]

  ◾  DECLARE

-- Local Variable DECLARE Statement
DECLARE var_name [, var_name] ... type [DEFAULT value]

-- Local Variable Scope and Resolution
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
  DECLARE xname VARCHAR(5) DEFAULT 'bob';
  DECLARE newname VARCHAR(5);
  DECLARE xid INT;
  SELECT xname, id INTO newname, xid
    FROM table1 WHERE xname = xname;
  SELECT newname;
END;

DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}

  ◾  CASE

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

  ◾  IF

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

  ◾  LOOP

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

  ◾  WHILE

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

  ◾  RETURN

RETURN expr

  ◾  REPEAT

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

  ◾  ITERATE

ITERATE label

  ◾  LEAVE

LEAVE label

 

https://dev.mysql.com/doc/refman/8.0/en/flow-control-statements.html