Programaçã para banco de dados

PROGRAMAÇÃO

PARA

BANCO DE DADOS

PL/SQL

Prof. Marcos Alexandruk

SUMÁRIO

1. Conceitos de programação para banco de dados: Introdução ao PL/SQL

2. Declarações

3. Tipos de dados

4. Constantes e variáveis

5. Comandos SQL dentro de um bloco PL/SQL

6. Instruções IF-THEN-ELSE e CASE

7. Instruções LOOP, FOR e WHILE

8. Tratamento de exceções

9. Cursores explícitos e implícitos

10. Procedures

11. Functions

12. Triggers

13. Packages

PROGRAMAÇÃO PARA BANCO DE DADOS 3

1. CONCEITOS DE PROGRAMAÇÃO PARA BANCO DE DADOS: INTRODUÇÃO AO PL/SQL

PL/SQL: • Linguagem de programação: Procedural Language/Stuctured Query Language

Principais recursos oferecidos pela linguagem: • Executar comandos SQL para manipular dados nas tabelas • Criar constantes e variáveis • Criar cursores para tratar o resultado de uma consulta • Criar registros para guarda o resultado de um cursor ou campo de tabela • Tratar erros • Utilizar comandos de controle (if, if-then-else, case) e repetição (loop, for, while)

Vantagens: • Versatilidade • Portabilidade • Integração com o SGBD (Sistema Gerenciador de Banco de Dados) • Capacidade procedural (comandos de controle e repetição) • Redução de tráfego de rede

Rotinas PL/SQL podem ser desenvolvidas com diversas ferramentas: • SQL*Plus • SQL*Plus Worksheet • Oracle Forms • etc.

Estrutura de um bloco PL/SQL:

DECLARE

Inicializações, declaração de constantes, variáveis e cursores

BEGIN

Comandos SQL, estruturas de programação e outros blocos PL/SQL

BEGIN

...

END;

EXCEPTION (opcional)

Tratamento de exceções, emissão de mensagens

END;

IMPORTANTE: Para visualizar a saída no SQL*Plus faça a seguinte alteração:

SET SERVEROUTPUT ON

PROGRAMAÇÃO PARA BANCO DE DADOS 4

2. DECLARAÇÕES

Na área DECLARE podemos declarar: • constantes • variáveis • cursores • estruturas • tabelas

3. TIPOS DE DADOS

3.1. Os tipos de dados simples que podem ser utilizados em declarações PL/SQL são:

TIPOS DESCRIÇÃO CHAR Alfanumérico, tamanho fixo, limite: 2000 caracteres CHARACTER Idêntico ao CHAR, mantém compatibilidade com outras versões SQL VARCHAR2 Alfanumérico, tamanho variável, limite: 4000 caracteres VARCHAR E STRING Idêntico ao VARCHAR2, mantém compatibilidade com outras versões SQL CLOB (Character Long Object) Alfanumérico, tamanho variável, limite 4 Gb LONG Alfanumérico, limites: 2 GB, apenas um por tabela ROWID Armazena os valores dos ROWIDs das linhas das tabelas BLOB (Binary Long Object) Binário, tamanho variável, limite: 4 Gb BFILE (Binary File) Armazena uma referência a um arquivo externo (que deverá localizar-se na mesma máquina do banco de dados, não permite referência remota) RAW Hexadecimais, tamanho variável, limite: 2 Kb LONG ROW Hexadecimais, tamanho variável, limite: 2 Gb

NUMBER

Numérico, limite: 38 dígitos Exemplo: NUMBER (10,2) armazena 10 números (8 inteiros e 2 decimais) SUBTIPOS: • DECIMAL • DEC • DOUBLEPRECISION • INTEGER • INT • NUMERIC • REAL • SMALLINT • FLOAT • PLS_INTEGER BINARY_INTEGER Numérico, positivos e negativos, limites: -2147483647 e 2147483647 SUBTIPOS: • NATURAL (limites: 0 e 2147483647) • NATURALN (limites: 0 e 2147483647, não aceita valores nulos) • POSITIVE (limites: 1 e 2147483647) • POSITIVEN (limites: 0 e 2147483647, não aceita valores nulos) DATE Data e hora (formato padrão: DD-MMM-YY TIMESTAMP Data e hora (com milésimos de segundo) BOOLEAN Armazena os valores: TRUE, FALSE ou NULL

3.2. Tipos compostos: • RECORD • TABLE

3.3. Tipos referenciados: • REF CURSOR

PROGRAMAÇÃO PARA BANCO DE DADOS 5

4. CONSTANTES E VARIÁVEIS

4.1. CONSTANTES Para declarações de constantes, a palavra CONSTANT deve aparecer antes do tipo de dado e a seguir utiliza-se o operador := para atribuir-lhe um valor.

Exemplo:

DECLARE pi CONSTANT NUMBER(9,7) := 3.1415927; ... BEGIN ... END; /

4.2. VARIÁVEIS As variáveis são inicializadas de maneira similar às constantes: declarando-se o tipo e atribuindo-lhe um valor. Variáveis não inicializadas explicitamente recebem o valor NULL. Não é necessário inicializar uma variável com valor NULL. Pode-se aplicar a restrição NOT NULL a uma variável. Neste caso ela deverá ser inicializada.

Exemplos:

v1 NUMBER(4):= 1; v2 NUMBER(4):= DEFAULT 1; v3 NUMBER(4) NOT NULL:= 1;

Podemos atribuir valores às variáveis de duas maneiras: • Utilizando o operador de atribuição: total := quant * valor;

• Utilizando um comando SELECT com a cláusula INTO: SELECT ra_aluno, nome_aluno INTO ra, aluno FROM aluno;

4.2.1. HERANÇA DE TIPO E TAMANHO As constantes e variáveis podem herdar o tipo de outras variáveis, de colunas ou até da linha inteira de uma tabela. Desta forma, diminuem-se as manutenções oriundas nas alterações realizadas nas colunas