BD2 - Linguagem procedural

Banco de Dados II
Prof: Márcio Soussa
Curso de Sistemas de Informação
Faculdades Jorge Amado
Referências Bibliográficas
Sistemas de Banco de Dados
Elmasri, Navathe
Introdução a Sistemas de Bancos de
Dados, C.J. Date, Ed. Campus.
Sistema de Banco de Dados, A
Silberschatz, H.F. Korth, Ed. Makron Books
Linguagem Procedural
Introdução
Objetivo
Reforça a arquitetura Cliente-Servidor
Características
Mescla os seguintes comandos:
Comandos de seleção (IF)
Comandos de iteração (While, Loop, For)
Comandos de BD (Select, Insert, Delete, Update)
Padrão inexistente
PL/SQL
Transact-SQL
Introdução ao
PL/SQL
Linguagem PL/SQL
Características:
PL/SQL é uma extensão da linguagem SQL.
Linguagem procedural de 4a. geração.
Combinação de manipulação e processamento de dados no
banco de dados Oracle.
Vantagens:
Alta performance de procedimentos armazenados no banco.
Integração com o dicionário de dados do Oracle.
Provê escalabilidade para as aplicações.
Portável para qualquer ambiente Oracle.
Suporte a programação orientada a Objetos.
Linguagem PL/SQL
Exemplo de um Bloco PL/SQL:
DECLARE
Emp_number INTEGER := 9999;
Emp_name VARCHAR2(10);
BEGIN
SELECT Ename INTO Emp_name FROM Emp_tab
WHERE Empno = Emp_number;
DBMS_OUTPUT.PUT_LINE('O Nome é ' ||
Emp_name);
END;
Tipos de Dados do PL/SQL
Tipos Escalares:
 Number
Binary_integer: -2147483647 a 2147483647
Number (precision,scale) - 38 precisão máxima
Decimal
Float
Integer

Character
Char(n) - 1 até 32767 bytes
Varchar2(n) - 1 até 32767 bytes



Boolean - true, false e null
Date - data (default: dia 01 do mês corrente) e hora (default:
12:00 a.m.)
Rowid - endereço binário de linha.
Declaração de
Variáveis e Constantes
DECLARE
var1
var2
var3
var4
var5
var6
DATE;
SMALLINT:=0;
REAL:=5;
NUMBER(2):=12;
DATE:=null;
CHAR DEFAULT '0';
var7 var2%TYPE;
var8 tabela_do_banco%ROWTYPE;
Declaração de
Variáveis e Constantes
Atributos:
%TYPE - atribui o tipo de uma variável ou coluna
do banco de dados.
l_dname scott.dept.dname%TYPE;
var7 var2%TYPE;
%ROWTYPE - atribui o tipo de um registro (linha)
de uma tabela do banco de dados.
emp_rec emp%ROWTYPE;
Comandos
SQL
BEGIN
SELECT mun.cdmunicipio, uf.sgestado,uf.dsestado
INTO municipio,uf,estado
FROM Municipio mun, Estado uf
WHERE mun.sgestado = uf.sgestado
ORDER BY uf.sgestado;
...
SELECT dsNivelAtiv INTO nivel FROM AtividadeNivel
ORDER BY cdNivelAtiv;
...
END;
Estruturas do
PL/SQL
Funções do PL/SQL
Categorias de Funções do PL/SQL:
Tipo Number
Tipo Char
Tipo Date
Conversão de Tipos
Controle de Erros
Tipo
Number
Função
Definição
MOD(m,n)
Retorna o resto da divisão de m por n.
LTRIM(a,b)
Remove a cadeia de caracteres que aparece à esquerda
Character
SUBSTR(c,m,n) Retorna um pedaço da cadeia de caracteres
TO_CHAR(d,f) Converte a data para o formato especificado
Conversion
TO_DATE(c,f) Converte o caracter para uma data no formato especificado
Date
ROUND(d,f)
Arredonda a data para o formato especificado
Funções do PL/SQL
Number
ABS
ACOS
ASIN
ATAN
ATAN2
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
POWER
ROUND
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC
Character
ASCII
CHR
CONCAT
INITCAP
INSTR
INSTRB
LENGTH
LENGTHB
LOWER
LPAD
LTRIM
NLS_INITCA
NLS_LOWE
NLSSORT
NLS_UPPER
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
SUBSTRB
TRANSLATE
TRIM
UPPER
Conversion
Date
CHARTOROWI ADD_MONTH
CONVERT
LAST_DAY
HEXTORAW
MONTHS_BET
RAWTOHEX
NEW_TIME
ROWIDTOCHA NEXT_DAY
TO_CHAR
ROUND
TO_DATE
SYSDATE
TO_MULTI_BY TRUNC
TO_NUMBER
TO_SINGLE_B
Estruturas de
Controle de Fluxo
Estruturas de Controle de Fluxo:
•
Condicional
IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
•
Iterativo
LOOP
WHILE-LOOP
FOR-LOOP
•
Sequencial
GOTO sentença
NULL
Procedures,
Funções e
Triggers em
PL/SQL
Tipos de Programas
PL/SQL
Blocos Anônimos - direto no prompt do SQLPlus.
DECLARE
Emp_number INTEGER := 9999;
Emp_name VARCHAR2(10);
BEGIN
SELECT Ename INTO Emp_name FROM Emp_tab
WHERE Empno = Emp_number;
DBMS_OUTPUT.PUT_LINE('O Nome é ' || Emp_name);
END;
Tipos de Programas
PL/SQL
Procedures:
CREATE OR REPLACE PROCEDURE Get_emp_rec
(Emp_number IN Emp_tab.Empno%TYPE,
Emp_ret OUT Emp_tab%ROWTYPE) IS
BEGIN
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
INTO Emp_ret
FROM Emp_tab WHERE Empno = Emp_number;
END;
Tipos de Programas
PL/SQL
Funções:
CREATE OR REPLACE FUNCTION upErro (pnuErro
CHAR)
RETURN VARCHAR2 AS
msg VARCHAR2(150);
BEGIN
SELECT nvl(dsmensagem,'Erro não cadastrado')
INTO msg FROM tratamentoerro WHERE nuerro=
pnuerro;
return msg;
END;
Tipos de Programas
PL/SQL
Cursor
É um dos recursos mais poderosos da linguagem procedural
Está vinculado a uma consulta de banco de dados
Permite a navegação entre os registros resultantes da consulta
no banco de dados
Corresponde a um ponteiro que aponta para um determinado
registro em um determinado momento
Comandos
DECLARE CURSOR: Vincula um cursor a uma consulta
OPEN: Abre o cursor
FETCH: Lê um registro do cursor e movimenta o ponteiro para o
próximo registro
CLOSE: Fecha o cursor
Tipos de Programas
PL/SQL
CURSOR c_empregado IS
Select nome, cpf funcionario where datademissao is not null;
stNome varchar2(30);
stCPF varchar2(11);
BEGIN
OPEN c_empregado;
LOOP
FETCH c_empregado INTO stNome, stCPF;
EXIT WHEN c_empregado%NOTFOUND;
INSERT INTO Arquivo_morto (arqu_nome, arqu_CPF) VALUES
(stnome, stcpf);
END LOOP;
CLOSE c_empregado;
END
Tipos de Chamdas de um Programa
PL/SQL
Chamada de Procedures:
- Dentro do código de outra procedure ou de
um trigger:
...
Calculo_IR(cd_emp,15);
...
- Dentro do ambiente SQLPlus e SQLTools:
...
EXECUTE Calculo_IR(cd_emp,15);
...
Tipos de Chamadas de um Programa
PL/SQL
Chamada de Funções:
- Dentro do código de outra procedure o função ou de um trigger.
- Dentro de um Comando SQL nas cláusulas:
SELECT
WHERE e HAVING
ORDER BY e GROUP BY
VALUES (do comando Insert)
SET
(do comando Update)
EXEMPLOS:
SELECT Calculo_IR(cd_emp,15)
INTO vl_calculo
FROM Emp_tab;
WHERE Calculo_IR(cd_emp,15) > 100;
Tipos de Programas
PL/SQL
É acionado automaticamente pelo SGBD (Ativa)
Relacionado a comandos de Update, Insert e Delete
Pode ser acionado antes ou depois do comando
Pode ser: Row-level ou Statement-level
Pode acionar um procedimento armazenado
Cuidado com loop de gatilhos
Geralmente utilizado para realizar alguma operação de
modificação no banco de dados ou garantir a integridade
do mesmo
Linguagem Procedural
Gatilho (trigger)
CREATE TRIGGER TR_Fornecedor_Estado BEFORE INSERT OR UPDATE
ON FORNECEDOR FOR EACH ROW
DECLARE Estado CHAR(02);
BEGIN
SELECT
est_cha_sigla INTO Estado
FROM cidade
JOIN
estado ON (cid_int_estado = est_int_ID)
WHERE Cid_int_ID = :New.For_Int_Cidade;
IF (Estado <> 'BA' AND Estado <> 'SE') THEN
RAISE_APPLICATION_ERROR(-20300, 'Não é permitido fornecedor
fora do eixo Bahia-Sergipe');
END IF;
END;
Tipos de Programas
PL/SQL
Triggers:
CREATE OR REPLACE TRIGGER tr_bir_CEP
BEFORE insert ON CEP
FOR EACH ROW
DECLARE
chValida EXCEPTION;
BEGIN
if (:NEW.stTipoCEP = 1 or :NEW.stTipoCEP = 3) then
RAISE chValida;
end if;
EXCEPTION
WHEN chValida THEN
RAISE_APPLICATION_ERROR(-20011,'Erro',False);