17.05 - Example: Creating a Procedure with Local Variable, Cursor, and Conditional Handler Declarations - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

The following example shows the CREATE PROCEDURE statement for creating a procedure named sp_sample3 with local variable declarations, cursor declarations, and condition handler declarations:

    CREATE PROCEDURE sp_sample3(OUT p1 CHARACTER(80))
    BEGIN
       DECLARE i INTEGER; 
       DECLARE emp_cursor CURSOR WITHOUT RETURN FOR
          SELECT emp_name, salary FROM emp_details ORDER BY dept_code;
       DECLARE dept_cursor CURSOR WITHOUT RETURN FOR
          SELECT dept_name from department;
          DECLARE EXIT HANDLER
          FOR sqlstate '42000'
          BEGIN
           OPEN emp_cursor;
           SET p1='FAILED TO INSERT ROW';
          END;
       DECLARE CONTINUE HANDLER
          FOR SQLSTATE value '23505'
          BEGIN
           SET p1='FAILED TO INSERT ROW';
          END;
       DECLARE CONTINUE HANDLER
          FOR SQLEXCEPTION 
          BEGIN
            SET p1='FAILED TO INSERT ROW';
          END;
       INSERT INTO table1 VALUES(1000,'aaa');
    END;