Oralcle PL SQL Parte I

PL/SQL- PL/SQL- FundamentosFundamentos

Instrutor: Márcio Mendonça

1

Oracle Database 11g1

É uma linguagem de programação utilizada para acessar e modificar dados num banco de dados Relacional.

Ela é integrada ao servidor de banco de dados de modo que o seu código possa ser processado de maneira rápida e eficiente.

O que é PL/SQL?O que é PL/SQL?


2

Oracle Database 11g

Um programa pode ser escrito em blocos, com variáveis locais e tratamento de erros;

Performance;Potabilidade - pode ser usado em qualquer plataforma

que acesse um banco de dados Oracle.O fluxo do programa pode ser

determinado através de comandos de desvio.

Benefícios da LinguagemBenefícios da Linguagem


3

Oracle Database 11g

Estrutura do PL/SQLEstrutura do PL/SQL

O PL/SQL é uma linguagem estruturada em blocos lógicos que podem conter um número qualquer de sub-blocos aninhados.

Um bloco PL/SQL pode ser um bloco anônimo ou um sub-programa ou uma trigger.


4

Oracle Database 11g

Blocos EstruturadosBlocos Estruturados

DECLARE(Opcional)Variáveis, cursores, definição de exceções

definidas pelo desenvolvedorBEGIN (Obrigatório)

Declaração SQL Declaração PL SQL EXCEPTION (Opcional)

Ações a serem tomadas caso ocorra algum erroEND; (Obrigatório)


5

Oracle Database 11g

Bloco Anônimo => É um bloco de comandos sem nome. É declarado na aplicação no ponto em que será executado e passado para a execução em tempo de execução.

Bloco AnônimoBloco Anônimo


6

Oracle Database 11g

Consistem em Procedures e Funções.

Podem ser armazenados no banco de dados como objetos independentes, como parte de um pacote ou como métodos de um tipo de objeto.

Sub-ProgramasSub-Programas


7

Oracle Database 11g

Consistem em um bloco PL/SQL que está associado a um evento que ocorre no banco de dados.

TriggersTriggers


8

Oracle Database 11g

Sub-programas definidos dentro de um outro sub-programa ou dentro de um bloco PL/SQL, são sub-programas locais e não podem ser referenciados por outras aplicações.

Linhas de comando podem ser divididas em diversas linhas.

Identificadores devem começar com uma letra e conter, no máximo, 30 caracteres;

Sintaxe Básica do PL/SQLSintaxe Básica do PL/SQL


9

Oracle Database 11g

Campos caracteres e datas devem ser usados entre aspas simples;

Comentários são delimitados por: /* */ e podem conter diversas linhas.

Comentários de apenas uma linha são iniciados por '--' (pode ser em linhas que já tenham comandos).

Palavras reservadas não podem ser usadas;

Sintaxe Básica do PL/SQLSintaxe Básica do PL/SQL


10

Oracle Database 11g

Símbolos Simples do PL/SQLSímbolos Simples do PL/SQL


11

Oracle Database 11g

Símbolos Compostos do PL/SQLSímbolos Compostos do PL/SQL


12

Oracle Database 11g

Regras de Estrutura de BlocosRegras de Estrutura de Blocos

O mínimo requerido em um bloco são os comandos BEGIN e END delimitando as ações que serão executadas.

Se o bloco PL/SQL precisar de definição de variáveis, conterá, também, a seção DECLARE.


13

Oracle Database 11g

Blocos AninhadosBlocos Aninhados

Blocos podem estar aninhados, inclusive na seção de EXCEPTION.


14

Oracle Database 11g

Blocos aninhadosBlocos aninhadosBEGIN Ações do bloco principal

DECLARE Declaração de objetos BEGIN Ações do bloco interno EXCEPTION O que fazer na ocorrência de erros do bloco interno END;

Outras ações do bloco principalEXCEPTION WHEN erro THEN BEGIN Ações do bloco interno do EXCEPTION EXCEPTION Ações a serem tomadas na ocorrência de erros no bloco interno de EXCEPTION. END;END;


15

Oracle Database 11g

Variáveis Variáveis


16

Oracle Database 11g

Uso de VariáveisUso de Variáveis•As variáveis podem ser utilizadas para armazenar temporariamente os dados e manipular valores.

•O nome atribuído a uma variável deve ter no máximo 30 posições. Pode possuir letras, números e caracteres especiais como underscore, ponto simples, dólar.

•Elas devem ser declaradas e inicializadas na seção declarativa. Usada e atribuída novos valores na seção de executáveis. Passada como parâmetros em subprogramas PL/SQL

•Não existe distinção entre letras maiúsculas e minúsculas.


17

Oracle Database 11g

Declaração de VariáveisDeclaração de Variáveis

Nome_da_variável [CONSTANT] datatype [NOT NULL] [{:= | DEFAULT} valor];Onde nome_da_variável é o identificador, datatype é tipo e valor é o conteúdo inicial da variável. EX:DECLARE

v_dataInicial DATE;v_contador BINARY_INTEGER NOT NULL := 0;v_nome VARCHAR2(20);c_PI CONSTANT NUMBER DEFAULT 3.14;


18

Oracle Database 11g

Tipos de DadosTipos de Dados

O Oracle PL/SQL possui diferentes tipos de dados (datatypes) para atender suas necessidades, que são divididos nas seguintes categorias:•CHARACTER, •NUMBER, •DATE, •LOB, •BOOLEANOS, •%TYPE


19

Oracle Database 11g

CharacterCharacter

CHAR(<n>) armazena string de tamanho fixo. Tamanho default 1, máximo 32.767. Subtipo: CHARACTER

VARCHAR2(<n>) armazena string de tamanho variável. É possível armazenar string de até 32.767 bytes. Subtipo: STRING

VARCHAR(<n>) sinônimo para o tipo VARCHAR2.

NCHAR(<n>) e NVARCHAR2(<n>) possuem as mesmas características dos tipos CHAR e VARCHAR2 e são usados para armazenar dados NLS (National Language Support). A arquitetura Oracle NLS permite armazenar, processar e recuperar informações em linguagens nativas.

LONG é um tipo de dados que se tornou “obsoleto” com a chegada dos tipos LOB (Large Object). O tipo LONG armazena strings de tamanho variável de no máximo 32.760 bytes.Instrutor: Márcio Mendonça

20

Oracle Database 11g

NuméricoNuméricoUsado para armazenar dados numéricos com precisão de até 38 digitos.

NUMBER(<x>, <y>) onde <X> corresponde ao número de dígitos e <Y> o número de casas decimais. Valores inseridos em colunas numéricas com número de casas decimais menor que o dado inserido serão arredondados. Subtipos: DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT.

BINARY_INTEGER utilizado para armazenar inteiros com sinal, que variam de –2147483647 a 2147483647. Requerem menos memória que tipos NUMBER. Subtipos: NATURAL (n>=0), NATURALN (n>=0 not null), POSITIVE (n>0), POSITIVEN (n>0 not null), SIGNTYPE (-1, 0, 1).

PLS_INTEGER Possui as mesmas características do tipo BINARY_INTEGER, entretanto possui melhor performance para cálculos.


21

Oracle Database 11g

DataData

O tipo DATE permite valores de data e hora. O formato padrão é definido pelo parâmetro NLS_DATE_FORMAT. O Oracle armazena internamente a data em formato de número juliano com a parte fracionária usada para controlar a hora. Uma data Juliana corresponde ao número de dias desde 1 de Janeiro de 4712 A.C.

Para operações aritméticas com datas no Oracle, basta adicionar ou subtrair números inteiros ou fracionários. Por exemplo, SYSDATE + 1 para somar uma dia, 1/24 para acrescentar uma hora, 1/(24x60) ou 1/1440 para acrescentar 1 minuto e 1/(24x60x60) ou 1/86400 para um segundo.


22

Oracle Database 11g

LobLob

Large Object (LOB) datatypes são usado para armazenar dados não estruturados como imagens, arquivos binários. Os tipos LOBs podem armazenar até 4GB de informação. A manipulação dos tipos LOB é feita através da package DBMS_LOB.

- BLOBBinary Large Object: Armazena até 4GB de dados binários no banco

-CLOBCharacter Large Object: Armazena até 4GB de dados caráter

- BFILEBinary File: Armazena até 4GB de dados em arquivos binários externos. Uma coluna BFILE armazena um ponteiro para o arquivo armazenado no sistema operacional.


23

Oracle Database 11g

BooleanoBooleano

O único tipo de dados na família booleana é o BOOLEAN.

Variáveis booleanas são utilizadas em estruturas de controle da PL/SQL como as instruções IF-THEN-ELSE e de LOOP. Podem conter apenas os valores TRUE, FALSE ou NULL.


24

Oracle Database 11g

Utilizando %TYPEUtilizando %TYPE

Utilizado para declarar uma variável com o mesmo tipo de uma coluna de alguma tabela, ex:

DECLAREv_Nome STUDENTS.FIRST_NAME%TYPE;v_Idade PLS_INTEGER NOT NULL :=0;v_IdadeTemp v_Idade%TYPE; --não herda restrição

nem valor default


25

Oracle Database 11g

Variáveis de HostVariáveis de Host

São variáveis declaradas no ambiente operacional que podem ser referenciadas nos comandos PL/SQL.

Sintaxe: :nome_da_variável := expressão;

Onde expressão pode ser uma variável, um valor, uma expressão usada para chegar a um valor específico.


26

Oracle Database 11g

Conversão entre Tipos de Conversão entre Tipos de DadosDados

Quando possível, a PL/SQL converterá automaticamente (conversão implícita) tipos dentro da mesma família e entre as famílias de tipos de dados:Caracteres e números Caracteres e datas


27

Oracle Database 11g

TO_CHARConverte seu argumento em um tipo VARCHAR2

TO_DATEConverte seu argumento em um tipo DATE

TO_NUMBERConverte seu argumento em um tipo NUMBER

Funções mais utilizadas para Funções mais utilizadas para conversão explícita de dadosconversão explícita de dados


28

Oracle Database 11g

Funções de caracter Funções de caracter que retornam que retornam valores caractervalores caracter


29

Oracle Database 11g

CHR (x[using nchar_cs])Retorna o caractere que tem o valor equivalente ao x no conjunto de caracteres do banco de dados. CHR e ASCII são funções opostas.

CONCAT (string1, string2)Retorna string1 concatenada com string2. Essa função é idêntica ao operador || .

INITCAP (string) Retorna string com o primeiro caractere de cada palavra em letra maiúscula e os caracteres restantes de cada palavra em letras minúsculas.

Funções Pré DefinidasFunções Pré Definidas


30

Oracle Database 11g

LOWER (string) Retorna string com todos os caracteres em letras minúsculas. Quaisquer caracteres que não forem letras permanecem intactos. Se string tiver o tipo de dados CHAR, o resultado também será CHAR. Se string for VARCHAR2, o resultado será VARCHAR2.

LPAD (String1, x[string2])Retorna string1 preenchida à esquerda até o comprimento x com os caracteres em string2.

LTRIM (String1,String2)Retorna string1 com os caracteres mais à esquerda aparecendo em string2 removidos. string2 assume o padrão de um espaço em branco.



31

Oracle Database 11g

REPLACE (string, string_a_pesquisar [string_substituta])Retorna string com cada ocorrência de string_a_pesquisar susbstituída por string_substituta.

RPAD (string1, x, [string2])Retorna string1, com os caracteres mais à direita que aparecem em string2, removidos. A string2 assume o padrão de um espaço em branco.

TRANSLATE (string, str_de, str_para)Retorna string com todas as ocorrências de cada caractere em str_de_substituído pelo caractere correspondente em str_para.



32

Oracle Database 11g

TRIM([{{LEADING|TRAILING|BOTH}[aparar_char])|aparar_char}FROM]string)Retorna string com as ocorrências inicial, final ou ambas de aparar_char removidas.

UPPER (string) Retorna string com todas as letras em maiúsculas.

SUBSTRSintaxe: SUBSTR (string, a[,b])Retorna uma parte da string que inicia no caractere a, com o comprimento dos caracteres b. Se a for 0, é tratado como 1 (o início da string). Se a for positivo, os caracteres que retornam são contados da esquerda. Se a for negativo, os caracteres retornam iniciando do final da string e são contados da direita. Se b não estiver presente, a string inteira é assumida como padrão. Se b for menor que 1, NULL é retornado. Se um valor de ponto flutuante for passado para a e b, o valor primeiro é truncado para um inteiro. O tipo de retorno é sempre do mesmo tipo da string.



33

Oracle Database 11g

Funções de caracter Funções de caracter que retornam valores que retornam valores numéricosnuméricos


34

Oracle Database 11g

ASCII (string)Retorna a representação decimal do primeiro byte de string no conjunto de caracteres do banco de dados.

INSTR (string1,string2 [,a] [,b])Retorna a posição dentro de string 1 onde string2 está contida, com a e b medidos em caracteres.

LENGTH (string)Retorna o comprimento de string medido em caracteres.

INSTR Sintaxe: INSTR (string1, string2 [,a] [,b])Retorna a posição dentro de string1 em que string2 está contida.



35

Oracle Database 11g

Funções numéricasFunções numéricas


36

Oracle Database 11g

MOD(x,y)Retorna o resto de x dividido por y. Se y for 0, x é retomado.

ROUND(x[,y])Retorna x arredondado para y casas à direita do ponto decimal. Y assume o padrão de 0, o qual arredonda x para o inteiro mais próximo. Se y for negativo, os dígitos à esquerda do ponto decimal são arredondados. Y deve ser um inteiro.

TRUNC (x,[,y])Retorna x truncado (em oposição a arredondado ) para y casas decimais. Y assume o padrão de 0, o que trunca x para um valor de inteiro. Se y for negativo, os dígitos à esquerda do ponto decimal são truncados.



37

Oracle Database 11g

Funções de DatasFunções de Datas


38

Oracle Database 11g

ADD_MONTHS(d,x)Retorna a data d mais x meses. X pode ser qualquer inteiro. Se o mês resultante tiver menos dias que o mês d, o último dia do mês resultante é retornado. Se não, o resultado tem o mesmo componente de dia que d. O componente de hora de d e o resultado são os mesmos.

LAST_DAYRetorna a data do último dia do mês que contém d.Essa função pode ser utilizada para determinar quantos dias restam no mês atual.

MONTHS_BETWEENRetorna o número de meses entre a data1 ea data2.

SYSDATERetorna a data e a hora atual no tipo DATE.

TRUNC (d,[,formato])Retorna a data d truncado para a unidade especificada por formato.



39

Oracle Database 11g

O escopo de uma variável é a parte do programa onde a variável pode ser acessada antes de ser liberada da memória.

A visibilidade de uma variável é a parte do programa onde a variável pode ser acessada sem ter de qualificar a referência.

DECLARE x number;

BEGIN........DECLARE

y number;BEGIN

........

........END;..........

END;

Escopo de VariáveisEscopo de Variáveis


40

Oracle Database 11g

OperadoresOperadores

O operador mais básico na PL/SQL é o de atribuição. A atribuição de valores permite mudar o valor de uma variável já definida em uma seção DECLARE.

Sintaxe: nome_da_variável := expressão;

Onde expressão pode ser um outro identificador, um valor, uma expressão usada para chegar a um valor específico, uma linha ou coluna de um outro identificador ou de uma tabela ou cursor, uma variável de host dependendo de como o nome_da_variável foi declarado.


41

Oracle Database 11g

Outros OperadoresOutros Operadores


42

Oracle Database 11g

Expressões BooleanasExpressões Booleanas

Todas as estruturas de controle PL/SQL envolvem expressões booleanas, também conhecidas como condições.

Uma expressão booleana é qualquer expressão que é avaliada como um valor booleano (TRUE, FALSE ou NULL).


43

Oracle Database 11g

Usando PL/SQL com PL/SQL Usando PL/SQL com PL/SQL DeveloperDeveloper


44

Oracle Database 11g

Create table alunos as(codigo number(3), nome varchar2(50), endereco varchar2(150), codigo_profissao varchar2(50));

Create table profissoes as(codigo number(2), descricao varchar2(50));

Criação de TabelasCriação de Tabelas


45

Oracle Database 11g

Insert into profissoes(codigo, descricao) values (1,’Analista de Sistemas’);

Insert into alunos(codigo, nome, endereco, codigo_profissao) values(1,’Maria’,’Av Afonso Pena, 600’,1);

Inserindo RegistrosInserindo Registros


46

Oracle Database 11g

Exemplo de Declaração Exemplo de Declaração

DECLARE v_desc varchar2(50);BEGIN SELECT descricao INTO v_desc FROM profissoes WHERE codigo = 1; END;


47

Oracle Database 11g

Cada comando SQL deve terminar com um ponto e vírgula(;)

Um bloco PL/SQL não é uma unidade de transação. Os comandos COMMIT e ROLLBACK devem ser usados dependendo da necessidade da aplicação.

Comandos DDL não são permitidos.

Comandos SQL em Blocos PL/SQLComandos SQL em Blocos PL/SQL


48

Oracle Database 11g

Comandos SELECT que não retornam apenas uma linha causam uma exeception a ser tratada pelo usuário;

Comandos DML podem processar diversas linhas ao mesmo tempo.

Comandos SQL em Blocos PL/SQLComandos SQL em Blocos PL/SQL


49

Oracle Database 11g

SQL apresenta uma série de comandos que permitem a definição dos dados, chamada de DDL ( Data Definition Language) , composta entre outros pelos comandos Create, que é destinado a criação do Banco de Dados, das tabelas que o compõe, além das relações existentes entre as tabelas. Como exemplo de comandos da classe DDL temos os comandos Create, Alter e Drop.

Os comandos da série DML ( Data Manipulation Language), destinados a consultas, inserções, exclusões e alterações em um ou mais registros de uma ou mais tabelas de maneira simultânea. Com exemplo de comandos da classe DML temos os comandos Select, Insert, Update e Delete.

Um subclasse de comandos DML, a DCL ( Data Control Language), dispõe de comandos de controle como Grant e Revoke.

DDL X DMLDDL X DML


50

Oracle Database 11g

Comandos SQLComandos SQL


51

Oracle Database 11g

Usando Variáveis PL/SQL em SQLUsando Variáveis PL/SQL em SQL

Valores de variáveis e de constantes PL/SQL podem ser passados e usados diretamente por comandos SQL.

Exemplo:

DECLARE Cod number(6) := 3;

BEGINUPDATE clientes SET conceito = 4WHERE codigo = cod;

END;


52

Oracle Database 11g

Controle de TransaçõesControle de Transações

Para o ORACLE uma transação DML vai começar no primeiro comando seguinte a um COMMIT ou ROLLBACK, e vai terminar no próximo COMMIT ou ROLLBACK.

Estas ações podem ocorrer dentro de um bloco PL/SQL, serem controladas manualmente(através da ferramenta utilizada) ou através de um evento do sistema.

Os comandos de controle de transações são pemitidos em um bloco PL/SQL .


53

Oracle Database 11g

Controle de TransaçõesControle de Transações

Exemplo:

BEGINUPDATE Detalhes_do_Pedido SET Desconto = 10 WHERE Quantidade > 1000;IF SQL%ROWCOUNT > 20

THEN ROLLBACK;ELSE COMMIT;

END IF;END;


54

Oracle Database 11g

Usos do Comando SELECT em PL/SQL Usos do Comando SELECT em PL/SQL

A) Atribuir valor a uma variável: Neste caso, deve retornar sempre apenas uma linha de resposta.

Sintaxe:

SELECT coluna_1,..., coluna_n INTO variável_1, ..., variável_nFROM tabela_1,..., tabela_n WHERE condição.

Onde coluna_i é colocada na variável_i. E cada variável já foi declarada.


55

Oracle Database 11g

B) Como subquerie de um outro comando, neste caso sem a cláusula INTO.

Exemplo:

INSERT INTO funcionarios SELECT max(cod_funcionario) + 1, 'fulano', sysdate,777,1000,2000 FROM funcionarios;

Usos do Comando SELECT em PL/SQL Usos do Comando SELECT em PL/SQL


56

Oracle Database 11g

Tratamento de Tratamento de ExcessõesExcessões


57

Oracle Database 11g

Tratamento de ExceçõesTratamento de Exceções

Em PL/SQL, um aviso ou condição de erro é denominado uma exceção.

Exceções podem ser definidas internamente ou pelo usuário.

Uma exceção pré-definida é disparada implicita e automaticamente pelo sistema. Exceções definidas pelo usuário devem ser escritas em rotinas separadas e disparadas explicitamente


58

Oracle Database 11g

Tratamento de ExceçõesTratamento de Exceções

Exemplos de Exceções Pré-definidas:


59

Oracle Database 11g

Tratamento de ErrosTratamento de Erros

Quando um erro ocorre, uma exceção é disparada, a execução normal é interrompida e o controle é passado para a parte de tratamento de exceções do bloco.

Se uma exceção encontrada não tem tratamento então o bloco PL/SQL é terminado com a mensagem: UNHANDLED EXCEPTION.


60

Oracle Database 11g

Tratamento de ErrosTratamento de Erros

Exemplo de declaração de uma Exception:

DECLARE nome EXCEPTION; ...........BEGIN ...........EXCEPTION WHEN no_data_found THEN

........... WHEN two_many_rows THEN

...........END;


61

Oracle Database 11g

Capturando ExceçõesCapturando Exceções

Na seção EXCEPTION pode ser definida uma exceção que capture uma outra exceção que não foi definida, quando esta ocorrer.

Exemplo:DECLARE preco NUMBER(5,2);BEGIN SELECT Preco_Unitario INTO preco FROM Produtos WHERE Cod_Produto = 1; IF preco > 120 THEN UPDATE Produtos

SET Preco_Unitario = 90WHERE Cod_Produto = 1

END IF;END;


62

Oracle Database 11g

Capturando ExceçõesCapturando ExceçõesDECLARE preco NUMBER(5,2);

BEGIN SELECT Preco_Unitario INTO preco FROM Produtos WHERE Cod_Produto = 3; IF preco > 120 THEN UPDATE Produtos SET Preco_Unitario = 90 WHERE Cod_Produto = 3; END IF;EXCEPTION WHEN no_data_found THEN INSERT INTO error_tab VALUES ('Código não cadastrado'); WHEN too_many_rows THEN INSERT INTO error_tab VALUES ('Mais de um produto com código 1'); WHEN others THEN INSERT INTO error_tab VALUES ('Erro ocorrido no bloco');END;


63

Oracle Database 11g

Funções Úteis na Captura de ErrosFunções Úteis na Captura de Erros

Para tratarmos uma exceção, podemos utilizar as funções SQLCODE e SQLERRM, para descobrirmos qual erro ocorreu e conseguir a mensagem associada ao erro.

SQLCODE: Retorna o número da mensagem associada ao erro ocorrido, para exceções internas.

SQLERRM: Retorna a mensagem associada ao erro ocorrido.


64

Oracle Database 11g

Documents

Oralcle PL SQL Parte I