DB2 - Exemplos simples - PROCEDURE



Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

DB2 - Exemplos simples - PROCEDURE

You can use CASE statements, compound statements, and nested statements within an SQL procedure body.
Você pode usar instruções CASE, instruções compostas e instruções aninhadas dentro de um corpo de procedimento SQL.

Example - CASE statement - Exemplo: declaração CASE:

The following SQL procedure demonstrates how to use a CASE statement.
The procedure receives an employee's ID number and rating as input parameters.
The CASE statement modifies the employee's salary and bonus, using a different UPDATE statement for each of the possible ratings.

O procedimento SQL a seguir demonstra como usar uma instrução CASE.
O procedimento recebe o número de identificação e classificação de um funcionário como parâmetros de entrada.
A instrução CASE modifica o salário e o bônus do funcionário, usando uma instrução UPDATE diferente para cada uma das classificações possíveis.

    CREATE PROCEDURE UPDATESALARY2
       (IN EMPNUMBR CHAR(6),
        IN RATING INT)
        LANGUAGE SQL
        MODIFIES SQL DATA
        CASE RATING
             WHEN 1 THEN
                  UPDATE CORPDATA.EMPLOYEE
                  SET SALARY = SALARY * 1.10, BONUS = 1000
                  WHERE EMPNO = EMPNUMBR;
             WHEN 2 THEN
                  UPDATE CORPDATA.EMPLOYEE
                  SET SALARY = SALARY * 1.05, BONUS = 500
                  WHERE EMPNO = EMPNUMBR;
             ELSE
                  UPDATE CORPDATA.EMPLOYEE
                  SET SALARY = SALARY * 1.03, BONUS = 0
                  WHERE EMPNO = EMPNUMBR;
        END CASE

Example: Compound statement with nested IF and WHILE statements:
Exemplo: Instrução composta com instruções IF e WHILE aninhadas:

The following example shows a compound statement that includes an IF statement, a WHILE statement, and assignment statements.
The example also shows how to declare SQL variables, cursors, and handlers for classes of error codes.

O exemplo a seguir mostra uma instrução composta que inclui uma instrução IF, uma instrução WHILE e instruções de atribuição.
O exemplo também mostra como declarar variáveis ??SQL, cursores e manipuladores para classes de códigos de erro.

The procedure receives a department number as an input parameter.
A WHILE statement in the procedure body fetches the salary and bonus for each employee in the department, and uses an SQL variable to calculate a running total of employee salaries for the department.
An IF statement within the WHILE statement tests for positive bonuses and increments an SQL variable that counts the number of bonuses in the department.
When all employee records in the department have been processed, a NOT FOUND condition occurs.
A NOT FOUND condition handler makes the search condition for the WHILE statement false, so execution of the WHILE statement ends.
Assignment statements then assign the total employee salaries and the number of bonuses for the department to the output parameters for the stored procedure.

O procedimento recebe um número de departamento como parâmetro de entrada.
Uma instrução WHILE no corpo do procedimento obtém o salário e o bônus de cada funcionário do departamento e usa uma variável SQL para calcular um total contínuo dos salários dos funcionários do departamento.
Uma instrução IF dentro da instrução WHILE testa os bônus positivos e incrementa uma variável SQL que conta o número de bônus no departamento.
Quando todos os registros de funcionários no departamento tiverem sido processados, ocorre uma condição NÃO ENCONTRADA.
Um tratador de condição NOT FOUND torna a condição de pesquisa para a instrução WHILE falsa, então a execução da instrução WHILE termina.
As declarações de atribuição atribuem o total de salários dos funcionários e o número de bônus do departamento aos parâmetros de saída do procedimento armazenado.

If any SQL statement in the compound statement P1 receives an error, the SQLEXCEPTION handler receives control.
The handler action sets the output parameter DEPTSALARY to NULL.
After the handler action has completed successfully, the original error condition is resolved (SQLSTATE '00000', SQLCODE 0).
Because this handler is an EXIT handler, execution passes to the end of the compound statement, and the SQL procedure ends.

Se qualquer instrução SQL na instrução composta P1 receber um erro, o manipulador SQLEXCEPTION receberá o controle.
A ação do manipulador define o parâmetro de saída DEPTSALARY como NULL.
Depois que a ação do manipulador for concluída com êxito, a condição de erro original será resolvida (SQLSTATE '00000', SQLCODE 0).
Como esse manipulador é um manipulador EXIT, a execução passa para o final da instrução composta e o procedimento SQL termina.

    CREATE PROCEDURE RETURNDEPTSALARY
     (IN DEPTNUMBER CHAR(3),
      OUT DEPTSALARY DECIMAL(15,2),
      OUT DEPTBONUSCNT INT)
     LANGUAGE SQL
     READS SQL DATA
     P1: BEGIN
         DECLARE EMPLOYEE_SALARY DECIMAL(9,2);
         DECLARE EMPLOYEE_BONUS DECIMAL(9,2);
         DECLARE TOTAL_SALARY DECIMAL(15,2) DEFAULT 0;
         DECLARE BONUS_CNT INT DEFAULT 0;
         DECLARE END_TABLE INT DEFAULT 0;
         DECLARE C1 CURSOR FOR
                 SELECT SALARY, BONUS FROM CORPDATA.EMPLOYEE
                  WHERE WORKDEPT = DEPTNUMBER;
         DECLARE CONTINUE HANDLER FOR NOT FOUND
                 SET END_TABLE = 1;
         DECLARE EXIT HANDLER FOR SQLEXCEPTION
                 SET DEPTSALARY = NULL;
         OPEN C1;
         FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
         WHILE END_TABLE = 0 DO
          SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_SALARY + EMPLOYEE_BONUS;
          IF EMPLOYEE_BONUS > 0 THEN
           SET BONUS_CNT = BONUS_CNT + 1;
          END IF;
          FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
         END WHILE;
         CLOSE C1;
         SET DEPTSALARY = TOTAL_SALARY;
         SET DEPTBONUSCNT = BONUS_CNT;
     END P1

Example: Compound statement with dynamic SQL statements:
Exemplo: Instrução composta com instruções SQL dinâmicas:

The following example shows a compound statement that includes dynamic SQL statements.
O exemplo a seguir mostra uma instrução composta que inclui instruções SQL dinâmicas.

The procedure receives a department number (P_DEPT) as an input parameter.
In the compound statement, three statement strings are built, prepared, and executed:

O procedimento recebe um número de departamento (P_DEPT) como parâmetro de entrada.
Na instrução composta, três strings de instrução são criadas, preparadas e executadas:

  • The first statement string executes a DROP statement to ensure that the table to be created does not already exist.
    This table is named DEPT_deptno_T, where deptno is the value of input parameter P_DEPT.
    A primeira string de instrução executa uma instrução DROP para garantir que a tabela a ser criada ainda não exista.
    Esta tabela é denominada DEPT_deptno_T, em que deptno é o valor do parâmetro de entrada P_DEPT.

  • The next statement string executes a CREATE statement to create DEPT_deptno_T.
    A próxima string de instrução executa uma instrução CREATE para criar DEPT_deptno_T.

  • The third statement string inserts rows for employees in department deptno into DEPT_deptno_T.
    A terceira string de instrução insere linhas para funcionários no departamento deptno em DEPT_deptno_T.

Just as statement strings that are prepared in host language programs cannot contain host variables, statement strings in SQL procedures cannot contain SQL variables or stored procedure parameters.
Therefore, the third statement string contains a parameter marker that represents P_DEPT.
When the prepared statement is executed, parameter P_DEPT is substituted for the parameter marker.

Assim como as cadeias de instruções preparadas em programas de linguagem host não podem conter variáveis de host, as cadeias de instruções em procedimentos SQL não podem conter variáveis ??SQL ou parâmetros de procedimento armazenado.
Portanto, a terceira string de instrução contém um marcador de parâmetro que representa P_DEPT.
Quando a instrução preparada é executada, o parâmetro P_DEPT é substituído pelo marcador de parâmetro.

    CREATE PROCEDURE CREATEDEPTTABLE (IN P_DEPT CHAR(3))
     LANGUAGE SQL
     BEGIN
      DECLARE STMT CHAR(1000);
      DECLARE MESSAGE CHAR(20);
      DECLARE TABLE_NAME CHAR(30);
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
       SET MESSAGE = 'ok';
      SET TABLE_NAME = 'DEPT_'||P_DEPT||'_T';
      SET STMT = 'DROP TABLE '||TABLE_NAME;
      PREPARE S1 FROM STMT;
      EXECUTE S1;
      SET STMT = 'CREATE TABLE '||TABLE_NAME||
       '( EMPNO CHAR(6) NOT NULL, '||
       'FIRSTNME VARCHAR(6) NOT NULL, '||
       'MIDINIT CHAR(1) NOT NULL, '||
       'LASTNAME CHAR(15) NOT NULL, '||
       'SALARY DECIMAL(9,2))';
      PREPARE S2 FROM STMT;
      EXECUTE S2;
      SET STMT = 'INSERT INTO '||TABLE_NAME ||
       'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY '||
       'FROM EMPLOYEE '||
       'WHERE WORKDEPT = ?';
      PREPARE S3 FROM STMT;
      EXECUTE S3 USING P_DEPT;
    END