O curso de base de dados ORACLE terá os seguintes módulos:  Introdução a bases de dados (20h)

1

DISCIPLINA:

BASE DE DADOS 1

O docente:

Hélder MC Muianga

 helder.muianga@uem.mz  (+258) 82-4727160

UEM

DMI

3

Sumário do Curso

O curso de base de dados ORACLE terá os

seguintes módulos:



Introdução a bases de dados (20h)

Linguagem SQL (Básico) (20h)



Linguagem SQL (Avançado) (20h)



Programação em PL/SQL (30h)



Administração de base de dados Oracle (40h)

(Básico)



Administração de base de dados Oracle(40h)

UEM

DMI

Objectivos do curso

Ao fim deste módulo o participante deverá estar habilatado em:

 Programar em PL/SQL usando:  Estrutura de PL/SQL

 Variáveis em PL/SQL

 Estruturas de controle (IF…THEN… ELSE, FOR…LOOP)  Cursor

 Processo de excepção  Procedimento e Função

UEM

DMI

5

Conteúdo

 Introdução ao PL/SQL

 Sumário de PL/SQL  Vantagem de PL/SQL

 Declaração de variáveis de PL/SQL

 Norma nomeada

 Declaração de tipo escalar  Declaração de tipo %TYPE

 Estrutura de Blocos PL/SQL

 DECLARE, BEGIN, EXCEPTION e END

 Interacção com servidor de Oracle

 Uso SQL no ambiente de PL/SQL

UEM

DMI

Conteúdo “cont.”

 Estruturas de controlo

 IF, CASE, LOOP, WHILE e FOR

 Tipos de dados

 %ROWTYPE

 INDEX BY

 Uso de cursor explícito

 Declaração de CURSOR

 Uso de CURSOR explícito

 Uso de excepção

UEM

DMI

7

Objectivos

Depois de terminar esta lição, deverá estar capacitado para:

 Explicar a necessidade do PL/SQL

 Explicar os benefícios que advém do uso do

PL/SQL

 Identificar os diferentes tipos de blocos do PL/SQL

 Usar o iSQL*Plus como um ambiente de

desenvolvimento do PL/SQL

UEM

DMI

Questão 1

 Uma empresa do ramo industrial, tem feito

periodicamente um balanço dos seus rendimentos.

 Ao fazer cálculo de bónus de empregados, deve

seguir os seguintes critérios:

 Se esse empregado for do departamento de Vendas,

recebe 2000 e os restantes 1000.

 Caso esse empregado seja Gestor de Vendas recebe mais

1000.

 A questão é: Como resolver este problema

UEM

DMI

9

O que é PL/SQL



PL/SQL

 Procedural Language extension to SQL

 Extensão de Linguagem Procedural para SQL

 É linguagem padrão da Oracle para acesso a

dados numa BD relacional.

 No seu processamento integra código SQL

IF… THEN … ELSE … END IF; LOOP … END LOOP; SQL SELECT … FROM …; UPDATE …; PL/SQL

IF … THEN

UPDATE …;

ELSE …

INSERT …;

END IF;

UEM

DMI

O que é PL/SQL (Cont.)

PL/SQL:

 Define uma estrutura em bloco para execução de

códigos SQL. A manutenção do código é simples e depende da correcta definição da estrutura

 Define procedural constructs como:  Variáveis, constantes e tipos

 Estruturas de controle tais como: declarações condicionais

e ciclos

UEM

DMI

11

Porquê PL/SQL?

Existem alguns comandos básicos em SQL que podemos usar para manipular dados:

Exemplo:

Select empno, ename, deptno, sal from emp;

A declaração SQL acima, é bastante simples e directa. Contudo, enfrentaremos alguns problemas caso queiramos alterar os dados por si devolvidos segundo uma certa condição. Deparamo-nos com algumas limitações do SQL.

Considere o seguinte problema, já modificado:

Para todo o empregado devolvido, verifique o seu deptno e o sal. Dependendo

do desempenho do departamento e do salário do empregado poderá querer atribuir bónus diferentes a cada um dos empregados.

UEM

DMI

Porquê PL/SQL (Cont.)

Olhando para o problema anterior, constatamos que precisamos executar uma declaração SQL e ainda aplicar uma certa lógica aos dados por si devolvidos. Usando SQL para resolver este problema, precisaríamos de uma declaração SQL para cada

deptno e efectuar a referida bonificação a cada empregado sem

se esquecer que será preciso verificar o salário antes de atribuir o bónus.

Desta forma o problema torna-se complicado. Sentimos que tudo

seria muito mais simples se tivéssemos declarações

UEM

DMI

13

Ambiente de PL/SQL

Execução de Servidor de Oracle Execução de Ferramentas

* Ferramentas : Oracle Forms, Oracle Report

SQL *Plus _ORACLE

Oracle Forms Bloco de PL/SQL Mota de PL/SQL Bloco PL/SQL

Executor de SQL Mota de PL/SQL

Procedural SQL

Executor de Declaração Procedural

Mota de PL/SQL Procedural

SQL

Executor de Declaração

Procedural

Executor de SQL

ORACLE

UEM

DMI

Vantagens do PL/SQL

1. Integração de construção procedural com SQL

• Quando executamos um comando SQL, este comunica

ao Servidor de BD o que deve ser feito e não como deve ser feito. PL/SQL incorpora estas declarações de controle e declarações condicionais junto com o SQL. Dá-nos um melhor controle sobre as nossas declarações SQL e sua execução. O problema visto antes, mostra a necessidade de tal integração.

2. Melhoria no desempenho

• Sem o PL/SQL não seria possível combinar logicamente

UEM

DMI

15

Vantagens do PL/SQL

(Cont.)

1. Envio de declarações SQL uma a uma à BD (inúmeras solicitações da BD)

ORACLE ORACLE SQL 1 SQL 2 SQL 3 SQL 4 Resultado SQL1 SQL2

IF … THEN

SQL 3 SQL4 ELSE SQL5 END IF; Uma vez Várias vezes

UEM

DMI

Vantagens do PL/SQL

(Cont.)

3. Desenvolvimento de programas em módulos

 A unidade básica dum programa PL/SQL é um bloco. Estes blocos podem ser sequenciais ou podem ser aninhados

 Manutenção e Depuração (melhora de legibilidade)

4. Integração com ferramentas da Oracle

 Apoia todos os tipos de dados e mais

 Formulários Oracle, Relatórios Oracle

5. Portabilidade

 Programa do PL/SQL pode ser executado em qualquer ambiente onde se possa executar o Servidor Oracle independentemente da plataforma do SO.

6. Controle de excepções

UEM

DMI

17

Estrutura do Bloco PL/SQL

[DECLARE]

Variáveis, cursores, excepções definidas pelo usuário

BEGIN

Declarações SQL, Declarações PL/SQL

[EXCEPTION]

Acções a levar a cabo caso ocorra um erro

END;

Opcional

Opcional Obrigatório

Obrigatório  Um bloco PL/SQL é composto pelas secções de:

1. Declaração (DECLARE)

2. Execução (BEGIN … END) 3. Excepção (EXCEPTION)

UEM

DMI

Secções dum Bloco PL/SQL

 Declaração

 Começa com a palavra DECLARE e termina quando a secção de execução

começa

 É opcional

 Contém declaração de variáveis, constantes, cursor e excepção definidas

pelas usuário

 Execução

 Começa com a palavra BEGIN e termina com a palavra END seguido de

ponto-e-vírgula (;)

 Secção obrigatória

 Contém SQL, estruturas de controle e secção de excepção e pode ainda

conterb muitos outros blocos PL/SQL

 Excepção

UEM

DMI

19

Tipo de Blocos

 Um programa PL/SQL inclui um ou mais blocos que

podem estar separados ou aninhados. Existem três tipos de blocos que compõem um programa PL/SQL:

PROCEDURE nome

IS

BEGIN

--Declarações

[EXCEPTION]

END; FUNCTION nome RETURN tipo_de_dado IS BEGIN --Declarações

[EXCEPTION]

END;

[DECLARE]

BEGIN

--Declarações

[EXCEPTION]

END;

Anónimo Procedimento Função

UEM

DMI

Tipo de Blocos (cont.)

 Anónimo

 É um bloco sem nome

 Declarado num ponto da aplicação onde vai ser executado e é

compilado sempre que a aplicação for executada. Estes blocos não são armazenados na BD. São enviados ao executor de declarações PL/SQL no momento da execução.

 Subprogramas

 Procedimento

 Tem nome

 Pode-se indicar valor retornado, este valor é mudado no

UEM

DMI

21

Tipo de Programas

Ferramentas Constructs Servidor Oracle Construct

Blocos Anónimos Blocos Anónimos

Procedimentos ou Funções Stored Proceduresou Stored Functions

Aplicações de Pack ages Stored Pack ages

Aplicações de Triggers Triggers da BD

UEM

DMI

Ambientes de Programação

PL/SQL

 iSQL *Plus

 É a aplicação que funciona em browser (ex. Internet Explorer, Firefox, Opera e

mais)

 Conectar base de dados local e remota

 É possível executar todas as operações de programação

 Oracle JDeveloper 10g (grátis)

 É um software para desenvolver páginas web com GUI

 Estabelece conexão à BD com ajuda dum user-friendly wizard

 Pesquisa dados da BD à qual se está conectado  Cria objectos e usuários de BD

 Cria, executa e depura programas de PL\SQL como procedimentos, funções e

packages

 PL/SQL Developer (versão trial)

 Uma aplicação para desenvolver programas PL\SQL

UEM

DMI

23

Uso de

i

SQL*Plus



Inicializar o navegador (ex. IE, Firefox…)



Escrever o seguinte URL:

 http://[endereço de servidor]:[Portos]/isqlplus

 Username : oracleXX

UEM

DMI

Criação Dum Bloco Anónimo



Digite o bloco anónimo no

Workspace

do

UEM

DMI

25

Execução Dum Bloco

Anónimo

UEM

DMI

Saída Dum Bloco PL/SQL



Configure a visualização de saída no

i

SQL*Plus usando o comando:



Use o pacote pré-definido da Oracle e seus

procedimentos:

UEM

DMI

27

Saída Dum Bloco

PL/SQL (cont.)

UEM

DMI

UEM

DMI

29

Comando SET

 Torna a saída legível no SQL*Plus

 É dispensável o uso deste comando quando se usa

iSQL*Plus

Variável Valor Descrição

HEADING ON ou OFF Exibe títulos de coluna

LINESIZE Número Indica comprimento de uma linha NULL Caracter Substitui valor de Nulo por outro PAGESIZE Número Número de linhas por página

TIMING ON ou OFF Exibe a duração duma execução

UEM

UEM

DMI

31

Exemplos

1. Qual dos seguintes blocos PL/SQL será executado com sucesso?

a) BEGIN

END;

b) DECLARE

quantidade INTEGER (10);

c) DECLARE

BEGIN END;

d) DECLARE

quantidade INTEGER (10); BEGIN

DBMS_OUTPUT.PUT_LINE(quantidade); END;

2. Crie e execute um bloco anónimo que imprime na tela a seguinte

mensagem: “Meu primeiro bloco em PL/SQL”. Grave com o nome

UEM

DMI

Objectivos

Depois de terminar esta lição, deverá estar capacitado para:

 Listar os identificadores válidos e inválidos

 Listar os usos das variáveis

 Declarar e inicializar variáveis

 Listar e descrever os diferentes tipos de dados

 Identificar e listar os benefícios do uso do

UEM

DMI

33

Sumário



Explicar a necessidade do PL/SQL



Explicar os benefícios que advém do uso do

PL/SQL



Identificar os diferentes tipos de blocos do

PL/SQL



Usar o

i

SQL*Plus como um ambiente de

desenvolvimento do PL/SQL

UEM

DMI

Questão 2

 Na qualidade de operador da BD, solicita-se uma

listagem dos empregados dos departamentos 10 e 20.

 Adicionalmente questiona-se, se PL/SQL também

tem variáveis, uma vez tratar-se duma linguagem procedural?

UEM

DMI

35

Uso de Variáveis

 Variáveis são usadas para:

 Armazenamento temporário de dados

 SELECT coluna [, coluna …] INTO var1 [, var2 …] FROM …

 Manipulação de valores armazenados

 Podemos usar o valor armazenado para manipular/processar dados

 Reusabilidade

 Depois de declaradas, as variáveis podem ser usadas repetidamente

por várias aplicações bastando para tal fazer referência às mesmas.

UEM

DMI

Identificadores

Os identificadores são usados para:

• Nomear uma variável

• Providenciar a convenção para os nomes das variáveis como sendo:

• Deve começar sempre com uma letra

• Pode incluir letras e números

UEM

DMI

37

Identificadores VS Variáveis

Qual é a diferença entre Identificadores e variáveis?

• Identificadores são nomes das variáveis.

• Variáveis são locais de armazenamento temporário de dados. O armazenamento permanente dos

dados é feito na memória. Portanto, as variáveis apontam para este ponto da memória a partir do qual os dados podem ser lidos e modificados.

• Identificadores são usados para nomear qualquer objecto PL/SQL como: variáveis, tipos, cursores e subprogramas.

UEM

DMI

Controle de Variáveis em

PL/SQL



Variáveis são:

 Declaradas e inicializadas na secção de

declaração

 Usadas e atribuídas novos valores na secção de

execução

 Passadas como parâmentros aos subprogramas

PL/SQL

 Usadas para armazenar o resultado da saída

UEM

DMI

39

Declaração e Inicialização de

Variáveis PL/SQL



Sintaxe

Identificador [CONSTANT] tipo_de_dado [NOT NULL]

[:= | DEFAULT expr];



Exemplos

DECLARE

emp_data DATE;

emp_dno NUMBER(2) NOT NULL := 10; location VARCHAR(13) := ‘Atlanta’;

UEM

DMI

Declaração e Inicialização

de Variáveis PL/SQL (cont.)

SET SERVEROUTPUT ON DECLARE

Nome VARCHAR2(20); BEGIN

DBMS_OUTPUT.PUT_LINE('O Meu Nome é ' || Nome);

Nome := ‘ALBERTO’;

UEM

DMI

41

Declaração e Inicialização

de Variáveis PL/SQL (cont.)

SET SERVEROUTPUT ON

DECLARE

Nome VARCHAR2(20)

:= ‘ALBERTO’

;

BEGIN

Nome

:= ‘JOAO’

;

DBMS_OUTPUT.PUT_LINE('O Meu Nome

é ' || Nome);

UEM

DMI

Delimitadores Com

Apóstrofes

Muitas palavras Inglesas levam consigo apóstrofes e por vezes pode haver necessidade de gerar saídas para esse tipo de palavras:

SET SERVEROUTPUT ON DECLARE

event1 VARCHAR2(15); event2 VARCHAR2(15); BEGIN

UEM

DMI

43

Tipos de Variáveis

• Variáveis PL/SQL:

• Escalares

• Armazenam valores singulares

• Compostas

• Um registo e uma tabela são exemplos de variáveis compostas

• Referência

• Armazenam ponteiros, que apontam para o local de armazenamento de dados

• Large Objects (LOB)

• Armazenam valores denominados alocadores, que especificam a localização de objectos de grande porte (como imagens gráficas).

• Variáveis Não-PL/SQL:

UEM

DMI

Questão 3

Agora já sabemos:

 Que PL/SQL usa variáveis



Pretendemos declarar variáveis como:

1. 123_sal

2. sal_123

3. select#SMITH

UEM

DMI

45

Directrizes Para Declaração e

Inicialização de Variáveis PL/SQL

 Sigue a convenção para atribuição de nomes  Dê nomes significativos

 Inicialize sempre as variáveis designadas como sendo NOT NULL e

CONSTANT

 Evite atribuir nome duma coluna a um identificador

DECLARE

enome VARCHAR2(20); BEGIN

SELECT enome INTO enome FROM emp

WHERE empno=50; END;

UEM

DMI

Directrizes Para Declaração e

Inicialização de Variáveis

PL/SQL(Cont.)

• Declare um identificador por linha para legibilidade e fácil manutenção de código

DECLARE

nome VARCHAR2(20);

num_dept NUMBER(2);

salario NUMBER(6);

BEGIN....

UEM

DMI

47

Questão 4

UEM

DMI

Tipos de Variáveis

 Escalar

 NUMBER

 VARCHAR2

 DATE

 BOOLEAN

 Composto

 %ROWTYPE

 INDEX BY

 VARRAY

 Referência

UEM

DMI

49

Tipo de Dados Escalar

 CHAR [(comprimento_máximo)]

 VARCHAR2 (comprimento_máximo)

 LONG

 LONG RAW

 NUMBER [(precisão, escala)]  BINARY_INTEGER

 PLS_INTEGER  BOOLEAN

 BINARY_FLOAT  BINARY_DOUBLE

 DATE

 TIMESTAMP[(precisão)]

 TIMESTAMP WITH TIME ZONE

 TIMESTAMP WITH LOCAL TIME ZONE  INTERVAL YEAR TO MONTH

UEM

DMI

Declaração de Variáveis

Escalares

Exemplo:

DECLARE

emp_trabalho VARCHAR2(9);

contador BINARY_NUMBER :=0

dept_total_sal NUMBER(9,2) :=0;

data_compra DATE := SYSDATE +7;

c_taxa CONSTANT NUMBER(3,2) :=8.25;

UEM

DMI

51

Questão 5

 Ao declarar uma certa variável, será preciso

pesquisar o tipo de dado da coluna afectada?

 Por exemplo, declaramos a variável emp_num,

UEM

DMI

Atributo %TYPE

Atributo %TYPE



É usado para declarar um variável de acordo:

 Com a definição duma certa coluna na BD

 Com a declaração de uma outra variável



É prefixado com:

 A tabela e coluna da BD

UEM

DMI

53

Declaração de Variáveis

Usando Atributo %TYPE



Sintaxe



Exemplos

Identificador tabela

.

nome_de_coluna

%TYPE;

nome empregado.enome%TYPE;

idade

empregado.idade%TYPE;

UEM

DMI

Declaração de Variáveis

BOOLEAN



Só podem receber valores TRUE, FALSE e

NULL



Expressões condicionais usam operadores

lógicos AND, OR e operador unário NOT

para verificar os valores da variável



Sempre cedem TRUE, FALSE ou NULL



Operações aritméticas, caracteres e

UEM

DMI

55

Variáveis de Ambiente

Variáveis de ambiente são:

 Criadas no ambiente de desenvolvimento

 Também designadas de variáveis anfitriãs

 Criadas usando a palavra reservada VARIABLE

 Usadas em declarações SQL e nos blocos PL/SQL

 Acedidas mesmo depois de se ter executado o

bloco PL/SQL

 Referenciadas com uma precedência de dois

UEM

DMI

Variáveis de Ambiente

(Cont.)

Exemplo:

VARIABLE resultado NUMBER BEGIN

SELECT (sal*12) + NVL(comm,0) INTO :resultado FROM emp

WHERE empno=7788; END;

UEM

DMI

57

Imprimindo Variáveis de

Ambiente

Exemplo:

VARIABLE emp_salario NUMBER BEGIN

SELECT sal INTO :emp_salario FROM emp WHERE empno=7788; END;

/

PRINT emp_salario

UEM

DMI

Imprimindo Variáveis de

Ambiente(Cont.)

Exemplo:

VARIABLE emp_salario NUMBER SET AUTOPRINT ON

BEGIN

SELECT sal INTO :emp_salario FROM emp

UEM

DMI

59

Variáveis de Substituição

 São usadas para receber os dados de entrada do usuário no momento

de execução

 São referenciadas num bloco PL/SQL precedido do símbolo &  São usadas para evitar uma codificação explícita de valores que

podem ser obtidos no momento da execução VARIABLE emp_salario NUMBER

SET AUTOPRINT ON DECLARE

emp_numero NUMBER(6) := &emp_numero ; BEGIN

SELECT sal INTO :emp_salario FROM emp

WHERE empno=emp_numero; END;

UEM

DMI

Variáveis de Substituição

UEM

DMI

61

Solicitando Variáveis de

Substituição

SET VERIFY OFF

VARIABLE emp_salario NUMBER

ACCEPT emp_numero PROMPT ‘Introduza um valor valido para o número do empregado: ’

SET AUTOPRINT ON DECLARE

emp_no NUMBER(6):= &emp_numero; BEGIN

SELECT sal INTO :emp_salario FROM emp

WHERE empno=emp_no; END;

UEM

DMI

Uso do DEFINE Para

Variáveis do Usuário

Exemplo:

SET VERIFY OFF

SET SERVEROUTPUT ON

DEFINE emp_apelido = 'SMITH' DECLARE

salsal NUMBER; BEGIN

SELECT sal INTO salsal FROM emp

UEM

DMI

63

Questão 6

 Analisemos a seguinte questão:

 Ao referenciar dados da tabela, temos que declarar

variáveis para cada coluna? (É muito trabalhoso…)

 Ao existir muitas variáveis a legibilidade é inexistente

 Não há maneira de declarar várias variáveis duma só vez?

SELECT empno, ename, sal FROM emp

WHERE deptno=d_no;

empno

enome

SELECT * FROM emp

WHERE deptno=d_no;

d_no

sal

UEM

DMI

Tipo de Dados Compostos

Campo1 (DATA) Campo2 (VARCHAR2)Campo3(NUMBER) Campo4(BLOB)

UEM

DMI

65

Questão 7



Existem alguns comandos escritos com letra

maiúscula e outras minúscula. Existe alguma

diferença? Ou existe alguma norma?

 Em declaração SQL tem muitos símbolos. Por

UEM

DMI

Letra Maiúscula e Minúscula



Não tem diferença entre letra maiúscula e

minúscula, excepto para dados



Exemplos

 DECLARE e declare

 SET SERVER ON e Set Server On

 ‘JOHN’ e ‘John’ e ‘john’

Não há diferença

UEM

DMI

67

Objectivos

Depois de terminar esta lição, deverá estar capacitado para:

 Identificar unidades léxicas num bloco PL/SQL  Usar funções SQL pré-definidas no PL/SQL

 Descrever quando é que ocorre um conversão

explícita e implícita e quando devem ser assim tratadas

 Construir blocos aninhados e qualificar os nomes

das variáveis

 Escrever códigos legíveis com uma indexação

UEM

DMI

Unidades léxicas em um

Bloco de PL / SQL

Unidades léxicas:

 São uma sequência de caracteres incluindo letras,

dígitos, espaços, símbolos

 Podem ser classificados como sendo:

 Identificadores

 Delimitadores

 São símbolos que têm um significado especial:

UEM

DMI

69

Unidades léxicas em um

Bloco de PL / SQL (Cont.)

 Literais

 São alguns valores que são atribuídos a variáveis

 Caracter literal : ‘John’, ‘123’, ’12-JAN-1999’

 Numérico literal : 438, 12544.77

 Boolean literal : TRUE, FALSE e NULL

 Comentários

 -- Para comentar numa linha

UEM

DMI

Identificadores

 São nomes usados para referenciar objectos em

PL/SQL

 Existem restricções

 Palavras reservadas não podem ser usadas como

identificadores

 Declare, begin, end, exception e mais…

 Palavra com espaço é inválida como Identificador  Se quisermos usar palavras reservadas ou

palavras com espaço, devemos colacar ente “ ”:

UEM

DMI

71

Delimitadores

 São símbolos que têm um significado especial

símbolo significado + Operador de adição

- Operador de subtração

* Operador de multiplicação

/ Operador de divisão

= Operador de igualdade

@ indicador de acesso remoto

; delimitador de declaração

símbolo significado

<> Operador de desigualdade

!= Operador de desigualdade

|| Operador de concatenação

-- Indicador de comentário de linha única

/* Delimitador de início de comentário

*/ Delimitador de fim de comentário

:= Operador de tarefa

UEM

DMI

Literais



Caracteres e data devem estar entre ‘ ‘:

 Nome_de_empregado := ‘Henderson’;



Números podem ser valores simples ou sob

notação científica

 2E5, significa 2*105 = 200000



Declarações podem ser feitas em várias

UEM

DMI

73

Comentários Dentro do

Código

 Os comentários não são executados pelo Servidor Oracle  Há dois tipod de comentários

 Para uma linha

 -- uma linha

 Para várias linhas

 /* início

... fim */  Exemplo DECLARE … sal NUMBER(9,2);

BEGIN -- secção que começa à execução /* Armazena o salário anual baseado

no valor mensal auferido por cada empregado*/ sal := sal_mes * 12;

UEM

DMI

Questão 8



Com os conhecimentos que se tem sobre

UEM

DMI

75

Exercícios

1. Liste os identificadores válidos e não válidos entre os seguintes:

a) hoje

b) p_nome

c) today´s day

d) #numero

e) NUMBER#

f) numero5

g) Numero_total_de_dias_de_duracao_do_curso_de_bases_de_dados_Oracle_10g

2. Qual é a vantagem do uso do %TYPE

3. Examine os seguintes blocos. Corrija-o caso seja necessário para garantir que corra sem problemas:

VARIABLE emp_salario NUMBER SET AUTOPRINT ON

DECLARE

p_nome BEGIN

SELECT sal INTO :emp_salario FROM emp WHERE empno=emp_numero; END; / PRINT emp_salario DECLARE

valor_inicial CONSTANT NUMBER(2); BEGIN

SELECT empno, deptno FROM emp

UEM

DMI

Exercícios

DECLARE

empregado_id NUMBER(2); salario NUMBER(6,2);

BEGIN

SELECT sal int salario FROM emp

WHERE empno=empregado_id; END;

/

4. Crie um bloco anónimo em que:

a) Imprima a data de hoje e a data de amanha, acompanhadas das seguintes mensagens: Hoje é dia: <dia> e amanha será dia: <dia>

UEM

DMI

77

Funções SQL no PL/SQL

 Disponíveis nas declarações procedural

1. Linha que devolve um número

2. Linha que devolve um caracter

3. Conversão de tipo de Dados

4. Data

5. Timestamp

6. GREATEST e LEAST

7. Funções Misturadas

 Não disponível nas declarações procedural

1. DECODE

UEM

DMI

Funções SQL no PL/SQL

Exemplo



Obtém o comprimento de caracteres

Converte o nome do empregado para letra

minúscula

tamanho INTEGER(5);

descricao VARCHAR2(70) := ‘Você pode usar este produto com o seu rádio.’;

UEM

DMI

79

Conversão de Tipo de

Dados

 Converte dados a tipos de dados comparáveis

 São de dois tipos:

1. Conversões implícitas

2. Conversões explícitas

 Algumas funções de conversão  TO_CHAR

 TO_DATE

 TO_NUMBER

UEM

DMI

Conversão de Tipo de

Dados (cont.)



Conversão implícita



Erro de conversão implícita



Conversão explícita

data_de_trabalho DATE := ’01-MAY-2000’ ;

data_de_trabalho DATE := ’February 02, 2000’ ;

data_de_trabalho DATE :=

UEM

DMI

81

Blocos Aninhados

 Blocos PL/SQL podem

ser aninhados

 A secção executável

(BEGIN … END;) pode

conter os Blocos Aninhados

 A secção de excepção

UEM

DMI

Alcance e Visibilidade da

Variável Num Bloco

SET SERVEROUTPUT ON; DECLARE

nome_de_pai VARCHAR(20) := 'Patrick'; data_de_ani DATE := '20-Apr-1972';

BEGIN

DECLARE

nome_de_filho VARCHAR(20) := 'Mike'; data_de_ani DATE := '11-DEC-2003'; BEGIN

DBMS_OUTPUT.PUT_LINE('Nome do Pai é ' || nome_de_pai); DBMS_OUTPUT.PUT_LINE('Nome do filho é ' || nome_de_filho);

UEM

DMI

83

Alcance e Visibilidade da

Variável Num Bloco(cont.)

 O resultado

 Alcance é o bloco do programa onde a variável é declarada e acessível.

 A visibilidade é o bloco do programa onde a variável pode ser acedida sem precisar de usar um qualificador

 As variáveis afectam as variáveis que são dentro dos seus blocos

 Se se tiver mesmo nome para duas variável distintas em blocos aninhados (exterior e interior), a variável interior tem prioridade.

Nome do Pai é Patrick Nome do filho é Mike

A data do aniversário é 11-DEC-03 A data do aniversário é 20-APR-72

UEM

DMI

Qualificar Um Identificador

 Esta qualificação é feita quando se pretende usar a variável exterior no bloco interior mesmo que este último tenha também declarado uma variável com o mesmo nome. Neste caso

podemos nomear bloco como sendo <<nome de bloco>>

SET SERVEROUTPUT ON; <<exterior>>

DECLARE

data_de_ani DATE := '20-Apr-1972'; BEGIN

DECLARE

UEM

DMI

85

Operadores no PL/SQL

 Lógico  Aritmética  Concatenação

 Parênteses para controlar ordem de operações

 Operador Exponencial (**)

Similar ao SQL

Operador Operação

** Exponencial

+, - Identidade, negação *, / Multiplicação, divisão

+, -, || Adição, Subtracção, Concatenação =, <, >, <=, >=, <>, !=, ~=, ^=,

IS NOT NULL, LIKE, BETWEEN, IN

Comparação

UEM

DMI

Operadores no PL/SQL

(cont.)



Exemplos

 Incrementa o contador de loop

 Atribui um valor Booleano contador := contador + 1;

UEM

DMI

87

Notificação de Operador no

PL/SQL

 Quando se trabalha com valores nulos, podem-se

evitar alguns erros comuns, recordando as seguintes regras:

 Comparações envolvendo nulos sempre retornam

NULL.

 Aplicando o operador lógico NOT ao nulo retorna

NULL.

 Em declarações de controle condicional, se a

UEM

DMI

Formas de Programação

 Crie código que facilita a manutenção :  Documentando o código com comentários

 Desenvolvendo convenções para o código

 Desenvolvendo convenções para a nomenclatura de

identificadores e outros objectos

 Criando alinhamento de código

Categoria Caso convenção Exemplos

SQL maiúscula SELECT, INSERT

UEM

DMI

89

Alinhamento (

Indentation

)

de Código



É melhor fazer alinhamento dó código para a

sua própria legibilidade



Exemplo

BEGIN

IF x=0 THEN y=1;

END IF; END;

UEM

DMI

Sumário



SQL funções em PL/SQL



Blocos Aninhados



Conversões explícitas

UEM

DMI

91

Declarações de SQL no

PL/SQL



Retornar linhas da base de dados pelo uso

do comando SELECT



Alterar o conteúdo das linhas da base de

dados, usando comandos DML



Controlar a transacção com os comandos

UEM

DMI

Declarações de SELECT no

PL/SQL



Cláusula INTO é consultado??????



Consultas devem retornar somente uma linha



Exemplo

SET SERVEROUTPUT ON DECLARE

nome_de_emp VARCHAR2(25);

BEGIN

SELECT ename INTO nome_de_emp

UEM

DMI

93

Retorno de dados no

PL/SQL



Retorne a

hiredate

e a

sal

do empregado

especificado



Consultas devem retornar somente uma linha



Exemplo

DECLARE

emp_hiredate emp.hiredate%TYPE;

emp_sal emp.sal%TYPE;

BEGIN

SELECT hiredate, sal

INTO emp_hiredate, emp_sal FROM emp

WHERE empno=7369; END;

UEM

DMI

Retorno de dados no

PL/SQL (cont.)



Retorne o total dos salários de todos os

empregados do departamento especificado



Exemplo

SET SERVEROUTPUT ON DECLARE

sum_sal NUMBER(10, 2);

no_dept NUMBER NOT NULL := 10;

BEGIN

SELECT SUM(sal) -- função agregada

UEM

DMI

95

Manipulação de Dados

Usando PL/SQL

 Altere o conteúdo das tabelas da base de dados

usando comandos DML:

 INSERT …para inserir

 UPDATE…para actualizar  DELETE …para remover

UEM

DMI

Inserção de Dados



Adicionar uma nova instância de empregado

na tabela emp



Exemplo

BEGIN

INSERT INTO emp ( empno, ename, job , hiredate, sal, comm , deptno

) VALUES

UEM

DMI

97

Actualização de Dados



Aumentar o salário de todos os empregados

que tenham a categoria CLERK



Exemplo

DECLARE

sal_aumento emp.sal%TYPE := 800; BEGIN

UPDATE emp

SET sal = sal + sal_aumento

WHERE job = ‘CLERK’;

UEM

DMI

Eliminação de Dados



Eliminar as linhas que pertencem ao

departamento 10 da tabela emp.



Exemplo

DECLARE

d_no emp.deptno := 10;

BEGIN

UEM

DMI

99

Fusão de Linhas

 Inserir ou actualizar linhas na tabela emp_copiada  Ao inserir uma nova linha, se já existir uma linha que tem

mesmo valor de PK, actualize-a em vez de inserí-la.

DECLARE

e_no emp.empno := 7999; BEGIN

MERGE INTO emp_copiada c USING emp e ON (e.empno=e_no)

WHEN MATCHED THEN

UPDATE SET c.ename = e,ename, c.sal=e.sal WHEN NOT MATCHED THEN

INSERT VALUES(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno);

UEM

DMI

Questão



O seu chefe disse que

 O seu programa retorna apenas uma linha, não

é?

 Não se pode efectuar uma consulta que retorna

UEM

DMI

101

Como é que se retornam várias

linhas no Bloco do PL/SQL



Já foi aprendido como fazer uma consulta de

SQL que retorna uma única linha no Bloco do

PL/SQL



Deve-

se usar SELECT … INTO … FROM …

UEM

DMI

Cursor de SQL

 CURSOR é uma referência à área de memória

privada alocada pelo Servidor Oracle

SELECT (seleccionar)

UEM

DMI

103

Cursor de SQL



CURSOR é

indicador

do conjunto

executado das linhas retornadas



Estas linhas são executadas

respectivamente pelo Servidor de Oracle



Há dois tipos de cursores

 Cursores implícitos

 São criados e manejados interiormente pelo servidor de Oracle para processar a declaração de SQL

 Cursores explícitos

UEM

DMI

Questão



O Edson perguntou:

 Eu quero saber qual foi o número de linha

executado (seleccionado, inserido, actualizado e removido) pela declaração de SQL

UEM

DMI

105

Atributos de Cursor SQL

para cursores implícitos

 Use os atributos do Cursor SQL, para poder

testar o resultado da sua declaração de SQL

SQL% FOUND Atributo Booleano que retorna VE RDADE IRO se a m ais recente

declaração de SQL devolver pelo m enos

um a linha.

SQL% NOTFOUND Atributo Booleano que retorna VE RDADE IRO se a m ais recente

declaração de SQL não devolver

nenhum a linha.

UEM

DMI

Atributos de Cursor SQL para

cursores implícitos (cont. 1)

 Remover as linhas que contém o número de

empregado especificado, da tabela emp. Exibir a seguir o número de linhas removidas

 Exemplo

VARIABLE linhas_removidas VARCHAR2(30) DECLARE

e_no emp.empno%TYPE := 7635; BEGIN

UEM

DMI

107

Atributos de Cursor SQL para

cursores implícitos (cont. 2)



Exemplo

SET SERVEROUTPUT ON DECLARE

nome emp.ename%TYPE; BEGIN

SELECT ename INTO nome FROM EMP WHERE empno=7788; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

UEM

DMI

Sumário



Emissão de Declarações DML, declarações

de controle de transacção e declarações de

DDL em PL/SQL



Clausula de INTO que é obrigatório para

SELECT em PL/SQL



CURSOR implícito e explícito



Atributos de CURSOR implícito

UEM

DMI

109

Questão



O seu colega, o Edson disse:

 Eu ouvi dizer que PL/SQL tem estruturas de

controle. É verdade?

 Agora, eu tenho de usar a ordem de divergência

no PL/SQL, como também tenho de repetir processos.

UEM

DMI

Controle de Fluxo de

Execução



Pode-se mudar o fluxo lógico das

declarações dentro do bloco do PL/SQL,

usando estruturas de controle

 Declaração IF

 Expressão CASE

UEM

DMI

111

Diagrama de IF, CASE e

LOOP

 IF deve diferenciar o processo mediante uma condição

 CASE separa os casos mediante os valores da condição

 LOOP repete processo enquanto a condição for verdadeira

Condição Falso Verdadeiro IF Condição CASE

Processo 1 Processo 3

Processo 2 Processo 1

Condição

Falso

Verdadeiro

LOOP

Valor 1 Valor 2 Valor 3

UEM

DMI

Declaração IF

 IF é semelhante à estrutura de IF de qualquer

linguagem procedural

 Sintaxe

IF condição THEN

Declarações;

[ ELSIF condição THEN

Declarações;]

[ ELSE

Declarações;]

UEM

DMI

113

Declarações simples de IF

a) Minha_idade é 25

b) Condição sempre deve retornar Verdadeiro ou Falso ou Nulo

c) Nesta condição minha_idade é menor do que 11. O resultado da

proposição é falso

d) Neste caso, as declarações após o THEN não são executadas

 O que deve ser alterado, para que as declarações sejam executadas?

DECLARE

minha_idade NUMBER := 25; BEGIN

IF minha_idade < 11 THEN

DBMS_OUTPUT.PUT_LINE(‘Eu sou criança.’); -- Não é executado

END IF;

UEM

DMI

Declarações de IF THEN

ELSE

SET SERVEROUTPUT ON DECLARE

minha_idade NUMBER := 31; BEGIN

IF minha_idade < 11 THEN

DBMS_OUTPUT.PUT_LINE(‘Eu sou criança.’); -- Não é executado

ELSE

DBMS_OUTPUT.PUT_LINE(‘Eu não sou criança!!!’);

END IF; END;

UEM

DMI

115

Cláusula de IF ELSIF ELSE

 IF ELSIF ELSE é executado por ordem.

DECLARE

minha_idade NUMBER := 31; BEGIN

IF minha_idade < 11 THEN

DBMS_OUTPUT.PUT_LINE(‘Eu sou criança.’);

ELSIF minha_idade < 25 THEN

DBMS_OUTPUT.PUT_LINE(‘Eu sou rapaz.’);

ELSIF minha_idade < 40 THEN

DBMS_OUTPUT.PUT_LINE(‘Eu sou meia-idade.’); ELSE

DBMS_OUTPUT.PUT_LINE(‘Eu já tou a ficar velhinho’);

END IF; END;

UEM

DMI

Valores de NULL na

Declarações IF

DECLARE

minha_idade NUMBER; BEGIN

IF minha_idade < 11 THEN

DBMS_OUTPUT.PUT_LINE(‘Eu sou criança.’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘Eu não sou criança!!!’);

END IF; END;

UEM

DMI

117

Expressões de CASE

 Expressão de CASE selecciona o resultado duma

expressão satisfeita

 Para seleccionar o resultado, a declaração CASE

usa expressões. O valor retornado por estas

expressões é usado para seleccionar uma de várias alternativas

 Sintaxe

CASE [selector]

WHEN expressão1 THEN resultado1

WHEN expressão2 THEN resultado2 …

WHEN expressãoN THEN resultadoN

[ELSE resultadoN+1]

END;

UEM

DMI

Exemplo : CASE

SET SERVEROUTPUT ON SET VERIFY OFF

DECLARE

nivel CHAR(1) := UPPER('&nivel'); avaliacao VARCHAR2(20);

BEGIN

avaliacao :=

CASE nivel

WHEN 'A' THEN 'Excelente' WHEN 'B' THEN 'Muito Bom' WHEN 'C' THEN 'Bom'

UEM

DMI

119

Exemplo : CASE Pesquisada

SET SERVEROUTPUT ON SET VERIFY OFF

DECLARE

nivel CHAR(1) := UPPER('&nivel'); avaliacao VARCHAR2(20);

BEGIN

avaliacao :=

CASE -- Não há variável de selecção (selector) WHEN nivel= 'A' THEN 'Excelente'

WHEN nivel IN ('B', 'C') THEN 'Bom' ELSE 'Não há esse nível ' || nivel END;

DBMS_OUTPUT.PUT_LINE('Nível é ' || nivel || '. ' || avaliacao || '.'); END;

UEM

DMI

Controlando Nulos

 Ao trabalhar com valores nulos, evite alguns erros

comuns, usando as seguintes normas:

 Comparações simples envolvendo nulos sempre retornam

NULL

 A aplicação do operador lógico de NOT à um nulo, retorna

NULL

 Em declarações de estruturas controle, se a condição

retornar NULL, as declarações associadas à condição não são executadas

X := 5;

Y := NULL;

X := NULL; Y := NULL;

UEM

DMI

121

Tabelas de lógica

Resultado

TRUE TRUE TRUE

TRUE FALSE FALSE

FALSE TRUE FALSE

FALSE FALSE FALSE

TRUE NULL NULL

NULL TRUE NULL

FALSE NULL FALSE

NULL FALSE FALSE

NULL NULL NULL

AND Resultado

TRUE TRUE TRUE

TRUE FALSE TRUE

FALSE TRUE TRUE

FALSE FALSE FALSE

TRUE NULL TRUE

NULL TRUE TRUE

FALSE NULL NULL

NULL FALSE NULL

NULL NULL NULL

OR

NOT Resultado

TRUE FALSE

FALSE TRUE

UEM

DMI

Condições Booleanas

 Qual é o valor de bandeira em cada caso?

dinheiro_bandeira banco_bandeira bandeira

TRUE TRUE

TRUE FALSE

NULL TRUE

NULL FALSE

UEM

DMI

123

Controle de Repetição:

Declarações de LOOP

 PL/SQL disponibiliza várias versões de estruturas

LOOP, para repetir declaração ou sequência de declarações

 LOOP básico

 Executa acções repetitivas sem condições globais

 WHILE LOOP

 Executa as acções repetitivas baseado numa condição

 FOR LOOP

UEM

DMI

LOOP Básico



Sintaxe



Exemplo

LOOP

Declaração1;

…

EXIT [WHEN condição];

END LOOP;

UEM

DMI

125

WHILE LOOP



Use WHILE LOOP para repetir declarações

enquanto a condição inicial for

verdadeira



Sintaxe



Exemplo

WHILE condição LOOP

Declaração1;

…

END LOOP;

WHILE idade < 10 LOOP idade := idade + 1;

UEM

DMI

FOR LOOP

 Use FOR LOOP para repetir declarações que tem

contador definido num intervalo

 Sintaxe

FOR contador IN [REVERSE] limite inferior .. limite superior LOOP

Declaração1; Declaração2;

…

UEM

DMI

127

Alinhando Repetições



Exemplo

SET SERVEROUTPUT ON DECLARE

BEGIN

FOR i IN 1..3 LOOP

DBMS_OUTPUT.PUT_LINE(i || ' '); FOR j IN 1..5 LOOP

DBMS_OUTPUT.PUT_LINE(j); END LOOP;

END LOOP; END;

UEM

DMI

Sumário



Condicional (declaração de IF)



Expressões de CASE e declarações de

CASE



LOOP

UEM

DMI

129

Questão



O Edson veio perguntar

 Eu quero usar variáveis que armazenam vários

tipos de dados.

UEM

DMI

Tipos de dados compostos



Registros de PL/SQL



Colecções de PL/SQL

 Tabelas de INDEX BY ou ordens associativas

 Tabela agregada

UEM

DMI

131

Registros do PL/SQL

 É uma estrutura de unidade de dados

 Deve conter pelo menos um componente de alguma

escalar chamado campo

 Sintaxe

TYPE nome_de_tipo IS RECORD

(declaração_de_campo [, declaração_de_campo] …);

Identificador nome_de_tipo;

Nome_de_campo { tipo_de_campo ou variável%TYPE

ou tabela.coluna%TYPE ou tabela%ROWTYPE}

[[NOT NULL] {:= ou DEFAULT} expr]

UEM

DMI

Criando Registros do

PL/SQL



Declara variáveis para armazenar ename, job

e sal da tabela emp



Exemplo

…

TYPE tipo_registro_emp IS RECORD -- Novo tipo de dados

(ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2));

UEM

DMI

133

Estrutura de Registros de

PL/SQL

Campo1(tipo) Campo2(tipo) Campo3(tipo)

 Exemplo empno

NUMBER(4)

ename

VARCHAR2(10) job

VARCHAR2(9)



Campos no registro são acessados com o

nome do registro. Para referir ou inicializar

cada campo, use o ponto

UEM

DMI

Questão



Você pensou assim:

 Não há maneira de as variáveis compostas

UEM

DMI

135

Atributo %ROWTYPE

 Sintaxe

 Vantagens de uso de %ROWTYPE

 Usando registro, o número de colunas e seu tipo de dados

na base de dados, não precisa ser sabido

 O número de colunas e o seu tipo de dados na base de

dados usando registro, podem alterar sem afectar o seu uso

 O atributo é muito útil quando a declaração SELECT

retorna uma linha inteira duma tabela

DECLARE

UEM

DMI

Exemplo:

Atributo %ROWTYPE

DEFINE emp_id = 7999; DECLARE

reg_emp emp%ROWTYPE; BEGIN

SELECT * INTO reg_emp FROM emp WHERE empno=&emp_id;

/* Insere a nova linha para tabela de emp_aposentado */ INSERT INTO emp_aposentado(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno)

UEM

DMI

137

Inserção de Registros que

Usam %ROWTYPE

DEFINE emp_id = 7999; DECLARE

reg_emp emp_aposentado%ROWTYPE; BEGIN

SELECT * INTO reg_emp FROM emp; WHERE empno=&emp_id;

/* Insere a nova linha para tabela de emp_aposentado */ INSERT INTO emp_aposentado(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno)

VALUES(reg_emp.empno, reg_emp.ename, reg_emp.job, reg_emp.mgr, reg_emp.hiredate, SYSDATE, reg_emp.sal,

reg_emp.comm, reg_emp.deptno);

UEM

DMI

Actualização duma Linha na

Tabela Usando Registro

SET SERVEROUTPUT ON SET VERIFY OFF

DEFINE empno=7369 DECLARE

reg_emp emp_aposentado%ROWTYPE;

BEGIN

SELECT * INTO reg_emp FROM emp_aposentado;

reg_emp. Leavedate := SYSDATE;

UPDATE emp_aposentado SET ROW = reg_emp WHERE empno=&empno;

END;

UEM

DMI

139

Questão



O seu colega veio perguntar:

 No caso de registos, nós podemos retornar os

todos os dados de uma linha inteira

 Agora, é possível retornar todos os dados duma

coluna?

coluna1 coluna2 coluna3

TOM 200 MAPUTO

SMITH 300 MATOLA

JOHN 250 ZIMPETO

TOM 200 MAPUTO

Registo

UEM

DMI

Tabelas de INDEX BY ou

Ordens associativas

 Ordens associativas são estruturas do PL/SQL com

duas colunas:

 Inteiro ou caracter de tipo de chave primária

 Coluna do tipo escalar ou do tipo de dados de registro

 Não são compelido no tamanho. Porém o tamanho

depende de valores da chave de tipo de dados

 Se coluna for do tipo escalar pode possuir somente um

UEM

DMI

141

Criando tabela de INDEX BY

Sintaxe



Exemplo : Declarar o INDEX BY tabela para

armazenar o nome de empregado

TYPE nome_de_tipo IS TABLE OF

{tipo_de_coluna ou variável%TYPE ou tabela.coluna%TYPE} [NOT NULL] ou tabela%ROWTYPE

[INDEX BY PLS_INTEGER ou BINARY_INTEGER ou VARCHAR2(tamanho)] identificador tipo_de_nome;

…

TYPE tipo_tabela_ename IS TABLE OF

emp.ename%TYPE

INDEX BY PLS_INTEGER;

…

UEM

DMI

Estrutura de Tabela de

INDEX BY

PLS_INTEGER Unique Key

1 5 3

…

…

Escalar Valor

Jones Smith Maduro

…

UEM

DMI

143

Criação de tabela de INDEX

BY

DECLARE

TYPE tipo_tabela_ename IS TABLE OF emp.ename%TYPE

INDEX BY PLS_INTEGER; -- Declaração do tipo de variável TYPE tipo_tabela_hiredate IS TABLE OF DATE

INDEX BY PLS_INTEGER; -- Declaração de tipo de variável

tabela_ename tipo_tabela_ename; -- Declaração de tabela ename

tabela_hiredate tipo_tabela_hiredate; -- Declaração de tabela hiradate

BEGIN

tabela_ename(1) := ‘CAMERON’; tabela_hiredate(8) := SYSDATE + 7;

IF tabela_ename.EXISTS(1) THEN

INSERT INTO emp(empno, ename) VALUES(7999, tabela_ename(1));

END IF;

END;

UEM

DMI

Uso de Métodos de Tabela

de INDEX BY

M étodo Descrição

E XISTS(n) Retorna Verdadeiro se existe o n-elem ento na tabela de PL/SQL

COUNT Retorna o núm ero de elem entos contidos actualm ente na tabela de PL/SQL FIRST

LAST

Retorna prim eiro ou últim o núm ero de index na tabela de PL/SQL.

Retorna NULL se a tabela de PL/SQL estiver vazia.

PRIOR(n) Retorna o núm ero de index que precede o index n na tabela de PL/SQL

NE XT(n) Retorna o núm ero de index que sucede ao index n na tabela de PL/SQL

DE LE TE DE LE TE rem ove todos os elem entos de tabela de PL/SQL DE LE TE (n) rem ove elem ento n de tabela de PL/SQL

DE LE TE (m , n) rem ove todos os elem entos no intervalo m …n de tabela de

UEM

DMI

145

Registros de Tabela de

INDEX BY



Define um variável de tabela de INDEX BY

para guardar uma linha inteira de tabela

DECLARE

TYPE tipo_tabela_dept IS TABLE OF dept%ROWTYPE INDEX BY PLS_INTEGER;

UEM

DMI

Registros de Tabela de

INDEX BY (cont.)

empno ename job mgr sal deptno

Tabela emp

TYPE tipo_tabela_emp IS TABLE OF emp%ROWTYPE

UEM

DMI

147

Exemplo de Tabela de

INDEX BY de Registros

SET SERVEROUTPUT ON DECLARE

TYPE tipo_tabela_emp IS TABLE OF emp%ROWTYPE

INDEX BY PLS_INTEGER; -- Declaração do tipo de ROWTYPE

a_minha_tabela_emp tipo_tabela_emp; min_count := 7996;

max_count := 7999; BEGIN

FOR i IN min_count..max_count LOOP

SELECT * INTO a_minha_tabela_emp(i) FROM emp WHERE empno=i; END LOOP;

FOR i IN a_minha_tabela_emp.FIRST.. a_minha_tabela_emp.LAST LOOP DBMS_OUTPUT.PUT_LINE(a_minha_tabela_emp(i).ename);

END LOOP; END;

UEM

DMI

Tabela Composta

(Aninhada)



Tabela composta tem semelhante de INDEX

BY Funcionalmente

 Chave é a partir de 1, chave não é possível valor

UEM

DMI

149

VARRAY:

Variable

-

size

arrays

 VARRAY é semelhante a tabelas de PL/SQL, sendo

que o VARRAY é constrangido pelo tamanho.

 VARRAY é válido ao nível do schema

 Exemplo:

TYPE tipo_varray_loc IS VARRAY(3) OF dept.loc%TYPE;

escritorios tipo_varray_loc;

O tamanho deste VARRAY é restrito a 3

Se tentar inicializar o VARRAY com mais do que três elementos,