Banco de Dados II - FTP da PUC

Banco de Dados II
PL/SQL – Teoria e Exemplos
Curso: Análise de Sistemas
Professora: Sílvia Cristina de Matos Soares
e-mail: [email protected]
PL/SQL
É estruturada em blocos. Cada bloco pode conter outros blocos.
Em cada bloco podemos declarar variáveis que deixam de existir quando o bloco termina.
Estrutura
DECLARE
<declaração de variáveis>
BEGIN
<lógica> (pode conter declaração de outros blocos)
EXCEPTION
<tratamento de erros>
END;
/ ou .
Observações:
1. Declarações entre “ ” fazem distinção entre maiúsculas e minúsculas. Portanto “XX” é
diferente de “xx”.
2. Toda estrutura de PL/SQL deve ser finalizada. A finalização pode ser feita de 2
maneiras:
 / : finaliza e executa o programa
 . : finaliza o programa sem executá-lo
3. O fim de linha deve ser feito com ;
Exemplo:
SQL> DECLARE
VALOR
VARCHAR2(10);
END_CASA VARCHAR2(20);
BEGIN
VALOR := -- exemplo de comentário
‘RUA A’;
END_CASA := /* exemplo 2 de comentário */ ‘RUA B S/N’;
END;
/
Definição de Variável no SQL PLUS
Uma variável definida no SQL PLUS será considerada uma variável externa ao programa
PL/SQL.
Sintaxe:
SQL> VARIABLE nome-da-variável tipo
Exemplo:
SQL> VARIABLE MSG VARCHAR2(100)
Comando PRINT
O comando PRINT serve para exibir o resultado de uma variável externa.
Sintaxe:
SQL> PRINT nome-da-variável
Exemplo1:
SQL> PRINT MSG
Exemplo2:
SQL> VARIABLE P1 VARCHAR2(100)
SQL> DECLARE
WCHAR CHAR(5);
BEGIN
WCHAR := “ABC”;
:P1 := ‘*’|| WCHAR || ‘*’;
END;
/
SQL> PRINT P1
P1
----------------------*ABC *
Exemplo3:
SQL> DECLARE
WVARCHAR2 VARCHAR2(32767);
WVARCHAR VARCHAR2(100);
BEGIN
WVARCHAR2 := “ABC”;
WVARCHAR := WVARCHAR2;
:P1 := ‘*’ || WVARCHAR || ‘*’;
END;
/
SQL> PRINT P1
P1
----------------------*ABC*
Restrição NOT NULL no DECLARE
Sempre que a restrição NOT NULL for utilizada na declaração de uma variável, deverá ser
atribuído um valor inicial à variável.
Exemplo:
SQL> DECLARE
V_DATA DATE NOT NULL := SYSDATE;
.
.
.
Atributo %TYPE
Serve para copiar o tipo de dado de uma variável ou coluna do banco de dados.
Exemplo:
SQL> DECLARE
VCOD
NUMBER(4) := 0;
V_DATA
DATE NOT NULL := SYSDATE;
WCOD
VCOD%TYPE NOT NULL := 1234;
W_DATA V_DATA%TYPE := SYSDATE;
W_DEP
DEPTO.DCOD%TYPE;
BEGIN
WDEP := NULL;
VCOD := 1;
WDATA := NULL;
END;
/
Observações:
1. Para variável baseada em coluna do banco de dados, são copiados somente o tipo e
tamanho. Não são copiadas as restrições.
2. Para variável local, tudo é copiado, inclusive as restrições.
Variáveis: Escopo e Visibilidade
Exemplo:
SQL> VARIABLE MSG VARCHAR2(200)
SQL> DECLARE
WNUM
NUMBER := 12;
WCHAR
VARCHAR2(20) := ‘WCHAR EXTERNA’;
WUNICO
NUMBER := 5;
BEGIN
DECLARE
WNUM
NUMBER := 10;
WCHAR VARCHAR2(20) := ‘WCHAR INTERNA’;
WTOTAL NUMBER;
BEGIN
WTOTAL := WNUM + WUNICO;
:MSG := WCHAR || ‘ – ‘ || WTOTAL;
END;
END;
/
SQL> PRINT MSG
MSG
-----------------WCHAR INTERNA - 15
Qualificação
Exemplo:
SQL> VARIABLE MSG VARCHAR2(200)
SQL> BEGIN
<<EXT>>
DECLARE
WCOD
NUMBER := 1;
BEGIN
DECLARE
WCOD
NUMBER := 5;
BEGIN
IF WCOD <> EXT.WCOD THEN
:MSG := ‘DIFERENTE’;
ELSE
:MSG := ‘IGUAL’;
END IF;
END;
END;
END;
/
SQL> PRINT MSG
MSG
-----------------DIFERENTE
Atribuição ( := )
Exemplo:
SQL> VARIABLE RETORNO NUMBER
SQL> DECLARE
V1 NUMBER := 1;
V2 NUMBER;
BEGIN
V1 := V1 + V2;
:RETORNO := V1 * 2;
END;
/
SQL> PRINT RETORNO
RETORNO
----------------------
OBSERVAÇÃO:
 Sempre que fizermos uma operação incluindo uma variável sem valor, o resultado
também retornará sem valor.
IF e Passagem de Parâmetro
Exemplo:
SQL> VARIABLE MSG VARCHAR2(100)
SQL> DECLARE
VALOR NUMBER := &VAL;
BEGIN
IF VALOR > 0 THEN
:MSG := ‘VALOR MAIOR QUE ZERO’;
ELSIF VALOR = 0 THEN
:MSG := ‘VALOR IGUAL A ZERO’;
ELSE
:MSG := ‘VALOR MENOR QUE ZERO’;
END IF;
END;
/
SQL> PRINT MSG
MSG
---------------------VALOR MENOR QUE ZERO
ROWID e SELECT INTO
O tipo de dado ROWID é utilizado para acesso rápido à tabela.
Cada tabela do banco de dados possui uma pseudo coluna ROWID que armazena valores
hexadecimais que correspondem ao endereço de cada linha (row).
Exemplo:
SQL> SELECT DCOD, ROWID
FROM DEPTO
WHERE ROWNUM < 5;
O comando SELECT INTO é utilizado para seleção de uma única linha de uma tabela.
Exemplo:
SQL> VARIABLE MSG VARCHAR2(100)
SQL> DECLARE
VSALARIO NUMBER;
ENDROW ROWID;
BEGIN
SELECT SALARIO, ROWID INTO VSALARIO, ENDROW
FROM FUNCIONARIO
WHERE COD = ‘000130’;
IF VSALARIO > 500 THEN
VSALARIO := VSALARIO * 1.3;
ELSIF VSALARIO IS NULL THEN
VSALARIO := 1500;
ELSE
VSALARIO := VSALARIO * 1.15;
END IF;
UPDATE FUNCIONARIO
SET SALARIO = VSALARIO
WHERE ROWID = ENDROW;
:MSG := ‘SALARIO = ‘|| VSALARIO;
COMMIT;
END;
/
SQL> PRINT MSG
MSG
----------------------------SALARIO = 3197,92
GOTO
Exemplo:
SQL> DECLARE
CONTA NUMBER := 0;
BEGIN
<<INICIO>>
CONTA := CONTA + 1;
IF CONTA < 5 THEN
GOTO INICIO;
END IF;
END;
/
CURSOR
É utilizado para acesso a uma base de dados. Pode retornar uma ou mais linhas.
Exemplo:
SQL> DECLARE
CURSOR C1 IS SELECT DCOD FROM DEPTO;
CURSOR C2 (PDEPTO IN VARCHAR2 := ‘A00’) IS
SELECT COUNT(*), MAX(SALARIO)
FROM FUNCIONARIO
WHERE DEPTO = PDEPTO;
VDCOD
DEPTO.DCOD%TYPE;
VQTD
NUMBER;
VMAX
NUMBER;
BEGIN
OPEN C1;
<<INICIO>>
FETCH C1 INTO VDCOD;
OPEN C2(VDCOD);
FETCH C2 INTO VQTD, VMAX;
<<TESTE>>
NULL;
END;
/
LOOP
Exemplo:
SQL> VARIABLE MSGL VARCHAR2(200)
SQL> DECLARE
CURSOR C1 IS SELECT SALARIO, COD FROM FUNCIONARIO;
RC1
C1%ROWTYPE;
BEGIN
OPEN C1;
:MSGL := ‘MATRÍCULAS = ‘;
LOOP
FETCH C1 INTO RC1;
IF C1%FOUND THEN
:MSGL := :MSGL || RC1.COD || ‘—‘;
ELSE
EXIT;
END IF;
END LOOP;
END;
/
SQL> PRINT MSGL
MSGL
-----------------------MATRÍCULAS = 000120 -- 000130 ---