Provide conditional execution of SQL statements.
IF search-condition THEN statement-list
... [ ELSE IF search-condition THEN statement-list ] ...
... [ ELSE statement-list ]
... END IF
Procedures, triggers, and batches.
None.
None.
The IF statement is a control statement that allows you to conditionally execute the first list of SQL statements whose search-condition evaluates to TRUE. If no search-condition evaluates to TRUE, and an ELSE clause exists, the statement-list in the ELSE clause is executed. If no search-condition evaluates to TRUE, and there is no ELSE clause, the expression returns a NULL value.
Execution resumes at the first statement after the END IF.
CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
DECLARE err_notfound EXCEPTION
FOR SQLSTATE '02000' ;
DECLARE curThisCust CURSOR FOR
SELECT company_name, CAST( sum(sales_order_items.quantity *
product.unit_price) AS INTEGER) VALUE
FROM customer
LEFT OUTER JOIN sales_order
LEFT OUTER JOIN sales_order_items
LEFT OUTER JOIN product
GROUP BY company_name ;
DECLARE ThisValue INT ;
DECLARE ThisCompany CHAR(35) ;
SET TopValue = 0 ;
OPEN curThisCust ;
CustomerLoop:
LOOP
FETCH NEXT curThisCust
INTO ThisCompany, ThisValue ;
IF SQLSTATE = err_notfound THEN
LEAVE CustomerLoop ;
END IF ;
IF ThisValue > TopValue THEN
SET TopValue = ThisValue ;
SET TopCompany = ThisCompany ;
END IF ;
END LOOP CustomerLoop ;
CLOSE curThisCust ;
END