Example: Creating a Procedure with Local Variable, Cursor, and Conditional Handler Declarations - Teradata Vantage - Analytics Database

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;