END

MySQL

Example

CREATE PROCEDURE productpricing()
BEGIN
  DECLARE p_price INT;
  DECLARE p_name VARCHAR(20);

  DECLARE cur1 CURSOR FOR SELECT name, price FROM products;
  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO p_name, p_price;

    IF p_price > 100 THEN
      SELECT p_name, 'High' as price_category;
    ELSE
      SELECT p_name, 'Low' as price_category;
    END IF;

    IF done THEN LEAVE read_loop; END IF;
  END LOOP;

  CLOSE cur1;
END

Output

// executing CALL productpricing();

'Product1', 'High'
'Product2', 'Low'
'Product3', 'High'
'Product4', 'Low'

Explanation

The given SQL code demonstrates a stored procedure in MySQL named productpricing. This procedure uses a cursor to loop through all products in the products table. Using the IF statement, it categorizes each product as ‘High’ if its price is greater than 100, or ‘Low’ otherwise. The END IF marks the end of the IF control structure, whereas END LOOP indicates the end of the LOOP structure. Lastly, END signifies the end of the procedure.

The output displays the names and price categories of each product after calling the productpricing procedure.

SQL Server

Example

DECLARE @PersonAge int;
SET @PersonAge = 18;

SELECT CASE
     WHEN @PersonAge < 13 THEN 'Child'
     WHEN @PersonAge < 20 THEN 'Teenager'
     ELSE 'Adult'
END;

Output

'Teenager'

Explanation

The CASE statement iterates over the conditions until it finds one that is true. Since the @PersonAge in this example has been set to 18, the output is ‘Teenager’.

</TabItem>

<TabItem label="Oracle">
#### Example

```sql
DECLARE
   a number(2);
BEGIN
   a := 10;
   IF a > 20 THEN
       dbms_output.put_line('a is greater than 20');
   ELSE
       dbms_output.put_line('a is not greater than 20');
   END IF;
END;
/

Output

a is not greater than 20

Explanation

The provided PL/SQL block declares a variable a, then checks if a is greater than 20. The IF conditional statement evaluates the condition, and depending on whether the result is true or false, it executes the corresponding branch. Since a is 10 which is not greater than 20, it executes the ELSE block and prints ‘a is not greater than 20’. The END keyword indicates the end of the IF statement and the anonymous block.