artigo sql magazine 51- Construindo Aplicações no Oracle

Construindo Aplicações no Oracle

Stored Procedures - Procedures, Functions e Packages – Parte I

O banco de dados Oracle nos oferece uma poderosa ferramenta para construção de aplicações diretamente no banco de dados.

Esta ferramenta é chamada de stored procedure ou, em português, procedimentos armazenados. Principalmente pelo fato de termos o código do procedimento armazenado diretamente no banco de dados, a execução e resposta passa a ser bem rápida, oferecendo uma melhor performance à aplicação final.

Stored Procedures nada mais são que códigos desenvolvidos em linguagem PL/SQL e são objetos no banco de dados. Sua implementação se dá através de três tipos de objetos que são as procedures (procedimentos), functions (funções) e packages (pacotes).

Está série de artigos abordará exatamente estes três objetos do banco de dados, fechando assim todo o assunto sobre stored procedures.

O ambiente

Neste artigo, utilizei um Oracle Database 10g Enterprise Editon Release 10.2.0.1.0 em um banco de dados criado através do utilitário DBCA (Database Configuration Assistant), onde selecionei a opção de instalação dos esquemas de demonstração. Utilizaremos o velho e bom esquema de demonstração SCOTT cuja senha inicial continua sendo TIGER. O Oracle 10g, por padrão, mantém a conta travada (locked), não se esqueça de conectar-se como SYS e destravá-la. Na primeira conexão feita com o usuário SCOTT, será solicitada a alteração da senha.

Caso já possua um banco de dados criado e o esquema SCOTT não tenha sido instalado, você poderá fazê-lo manualmente executando o script utlsampl.sql, que pode ser encontrado no diretório ORACLE_HOME/rdbms/admin/ (barra invertida - \ - no Windows).

A Listagem 1 apresenta os passos necessários para a criação do ambiente.

Listagem 1. Criando o esquema SCOTT ou destravando-o, caso já esteja criado.

SYS@RRDB> -- Criando o esquema SCOTT manualmente.

SYS@RRDB> @?\rdbms\admin\utlsampl

SYS@RRDB> -- Destravando o esquema SCOTT, caso o mesmo tenha sido criado juntamente com o BD.

SYS@RRDB> ALTER USER SCOTT ACCOUNT UNLOCK;

User altered.

SYS@RRDB> -- Conectando com o usuário SCOTT.

SYS@RRDB> conn scott@rrdb

Enter password: *****

Connected.

Apenas um parêntesis, RRDB é o nome da instância que estou utilizando.

Procedures

Uma procedure em PL/SQL é um programa autônomo compilado em um esquema de banco de dados Oracle. Em outras palavras, é um objeto de banco de dados que possui um “dono” (OWNER) e um código compilado (PL/SQL).

Uma procedure pode executar várias ações ou somente uma, que pode ser nada mais nada menos que uma simples chamada de outra procedure.

Quando a procedure é criada, o comando CREATE PROCEDURE irá inserir o nome deste novo objeto no dicionário de dados do Oracle. A Figura 1 mostra a estrutura básica de uma procedure, que é a mesma tanto para simples procedures autônomas quanto para procedures do corpo de um pacote (package), que veremos mais adiante.

Figura 1. Estrutura básica de uma procedure (ou corpo de pacote).

Os componentes chave da procedure, onde efetivamente escrevemos o código são:

·         declaração: é o local onde declaramos as variáveis que serão utilizadas na procedure. Pode-se ter também a definição de tipos (types) e pode-se ainda declarar exceções, que deverão ser manipuladas localmente e nenhum programa que “chamou” a procedure terá acesso a esta exceção;

·         corpo da procedure: aqui vai toda a codificação em PL/SQL. Qualquer estrutura PL/SQL pode ser usada como loops, desvios condicionais (if-then-else), etc.;

·         manipulação de exceções: esta seção da procedure é opcional. É aí que faremos o tratamento de erro, ou seja, como a procedure se comportará caso determinado erro ocorra. É importante ter em mente que, uma vez tratado, o erro “deixa de existir”, evitando que a procedure seja “abortada”.

Vejamos agora a sintaxe de criação de uma procedure (Listagem 2).

Listagem 2. Sintaxe de criação de uma procedure.

CREATE [OR REPLACE] PROCEDURE [schema.]nome [(parâmetro_1 [,parâmetro_2 ...])]

   [AUTHID DEFINER | CURRENT_USER]

IS

   [declaração]

BEGIN

   corpo da procedure

[EXCEPTION]

   manipulação de excessões

END [nome];

Para esta sintaxe, os componentes são:

·         CREATE [OR REPLACE]: palavra chave para criar o objeto (no caso, a procedure). A chave REPLACE é opcional, contudo, se omitida e já existir uma procedure com o mesmo nome, a criação não será feita, retornando um erro. A chave REPLACE irá simplesmente sobrescrever o código da procedure com o mesmo nome, permitindo a criação da procedure;

·         schema: define o nome do “dono” (owner) da procedure e é opcional. Caso seja omitido, o “dono” da procedure será o usuário que a está criando e caso seja definido como “dono” um usuário diferente do corrente, este deverá ter privilégio de criação de procedure em outro esquema (privilégio CREATE ANY PROCEDURE);

·         nome: define o nome da procedure e deverá sempre estar logo após a chave PROCEDURE;

·         parâmetros: uma lista de parâmetros que define a passagem de informação, que pode ser uma informação vinda do “chamador” da procedure (parâmetro de entrada) ou uma informação que será enviada ao “chamador” da procedure (parâmetro de saída);

·         AUTHID: cláusula opcional. Define se a procedure será executada com os privilégios do “dono” da procedure (DEFINER) ou com os privilégios do usuário que a “chamou” (CURRENT_USER). Caso a cláusula seja omitida, o padrão será utilizado, que é a execução com os privilégios do “dono”;

·         declaração: o mesmo que aparece na Figura 1, ou seja, local onde declaramos as variáveis e/ou definição dos tipos;

·         corpo da procedure: também o mesmo que aparece na Figura 1, ou seja, local onde a codificação da lógica da procedure estará;

·         manipulação de excessões: vemos também na Figura 1, e apenas relembrando, é opcional e é onde tratamos os eventuais errors que possam ocorrer durante a execução da procedure.

Após uma breve introdução aos conceitos de procedures, veremos de maneira prática como trabalhar com eles em nosso banco de testes no esquema SCOTT.

Hands On

Hora de colocar a “mão na massa” e criar algumas procedures que podem, inclusive, ser utilizadas em seu dia-a-dia.

Veremos como chamar e executar uma procedure, porém, antes de mais nada, vamos criar nossa primeira procedure. Vejamos o código na Listagem 3.

Listagem 3. Código de criação da procedure ALTERA_SALARIO.

SCOTT@RRDB> CREATE OR REPLACE PROCEDURE ALTERA_SALARIO

  2    (EMP_ID IN EMP.EMPNO%TYPE, PCT_SAL IN NUMBER)

  3  IS

  4   V_ENAME      EMP.ENAME%TYPE;

  5   V_JOB        EMP.JOB%TYPE;

  6   V_SAL        EMP.SAL%TYPE;

  7   PCT_INVALIDA EXCEPTION;

  8  BEGIN

  9    IF (PCT_SAL > 0) AND (PCT_SAL <= 15) THEN

 10     SELECT ENAME, JOB

 11      INTO V_ENAME, V_JOB

 12     FROM EMP

...