例: 簡単なCASE - Advanced SQL Engine - Teradata Database

以下のストアド プロシージャには単純なCASE文が組み込まれています。

CREATE PROCEDURE spSample(IN  pANo    INTEGER,
                          IN  pName   CHARACTER(30),
                          OUT pStatus CHARACTER(50))
BEGIN
  DECLARE vNoOfAccts INTEGER DEFAULT 0;
   SELECT COUNT(*) INTO vNoOfAccts FROM Accounts;
     CASE vNoOfAccts
        WHEN 0 THEN
            INSERT INTO Accounts (pANo, pName);
        WHEN 1 THEN
            UPDATE Accounts
            SET aName = pName WHERE aNo = pANo;
        ELSE
            SET pStatus = 'Total ' || vNoOfAccts || 'customer                                            accounts';
    END CASE;
END;

上記の例では、ローカル変数vNoAcctsの値に応じて、WHEN句の該当するSET文が実行されます。

INSERT INTO Accounts (pANo,               pName);

UPDATE Accounts 
    SET aName = pName 
   WHERE aNo = pANo;

SET pStatus = 'Total ' || vNoAccts    || ' customer accounts';