20091029 - PL-SQL

Banco de Dados

PL-SQLPL-SQL

Prof. André Yoshimi KusumotoProf. André Yoshimi Kusumoto

andre_unip@kusumoto.com.brandre_unip@kusumoto.com.br

PL-SQLPL-SQL

SQLSQLLinguagem de comando utilizada para comunicação com o BD;Linguagem de comando utilizada para comunicação com o BD;

SQL*PLUSSQL*PLUSFerramenta Oracle que reconhece e executa padrões PL/SQL e Ferramenta Oracle que reconhece e executa padrões PL/SQL e

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Ferramenta Oracle que reconhece e executa padrões PL/SQL e Ferramenta Oracle que reconhece e executa padrões PL/SQL e SQL;SQL;

PL/SQLPL/SQLLinguagem procedural que estende o SQL adicionando Linguagem procedural que estende o SQL adicionando aplicações lógicas.aplicações lógicas.

PL-SQL

Interação SQL e SQL*Plus

PL-SQL

Interação SQL e SQL*Plus

SQL*Plus Server

Oracle

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

PL-SQLPL-SQL

Linguagem de processamento de transações.Linguagem de processamento de transações.Extensão da Structured Query Language (SQL)Extensão da Structured Query Language (SQL)Permite manipular dados e comandos SQL dentro de blocos Permite manipular dados e comandos SQL dentro de blocos estruturados, e unidades de código proceduralestruturados, e unidades de código procedural

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

estruturados, e unidades de código proceduralestruturados, e unidades de código procedural

Alguns Benefícios:Alguns Benefícios:Desenvolvimento modularizadoDesenvolvimento modularizadoUso de blocos aninhadosUso de blocos aninhadosDeclaração de identificadores (variáveis, constantes, Declaração de identificadores (variáveis, constantes, cursores)cursores)Uso de controle de estruturas (condicionais, loops)Uso de controle de estruturas (condicionais, loops)

PL-SQL

Melhorando Performance

PL-SQL

Melhorando Performance

Grupo de comandos SQL dentro de um bloco reduz tráfego Grupo de comandos SQL dentro de um bloco reduz tráfego de redede rede

SQL

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Aplicação

Aplicação

Outros

Oracle

SQL

SQL

SQL

If .... Then

SQL

Else

SQL

End if

PL-SQL

Declarando uma variável

PL-SQL

Declarando uma variávelvar[iable] var[iable] datatypedatatype

DATATYPES PRINCIPAISVARCHAR2(comprimento) - Conjunto de caracteres (string) de tamanho variável. O

comprimento varia entre um máximo de 2000 caracteres e um mínimo de 1.

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

comprimento varia entre um máximo de 2000 caracteres e um mínimo de 1. NUMBER(p, e) - Representa um número com uma precisão de p e uma escala de e. A

precisão p pode variar entre 1 e 38 e a escala e entre –84 e 127. LONG - Conjunto de caracteres de tamanho variável com até 2 gigabytes (231-1 bytes). DATE - Um valor de data entre 1 de Janeiro de 4712 AC e 31 de Dezembro de 4712 DC. CHAR(comprimento) - Conjunto de caracteres de tamanho fixo. O comprimento máximo

é de 255 bytes e o comprimento por omissão é de 1 byte. BLOB - Tipo de dados para conteúdos binários até 4 Gigabytes

PL-SQL

Estrutura do Bloco

PL-SQL

Estrutura do Bloco

DECLARE DECLARE -- OpcionalOpcionalVariáveis, constantes, cursoresVariáveis, constantes, cursores

BEGIN BEGIN -- MandatórioMandatório

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

BEGIN BEGIN -- MandatórioMandatório-- Estrutura SQLEstrutura SQL-- Controle PL/SQLControle PL/SQL

EXCEPTION EXCEPTION -- OpcionalOpcional-- Ação a executar no caso de erroAção a executar no caso de erro

END; END; -- MandatórioMandatório

PL-SQL

Construções

PL-SQL

Construções

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Construção Descrição

Blocos Anônimos Bloco PL/SQL embutido em alguma aplicação

Stored | Application Procedure ou Função

Bloco PL/SQL nomeado que aceita parâmetros e pode receber várias chamadas de execução

Nota:Nota: Uma função é semelhante a uma procedure, exceto que a primeira deve retornar um Uma função é semelhante a uma procedure, exceto que a primeira deve retornar um valor enquanto que procedures não.valor enquanto que procedures não.

Procedure ou Função várias chamadas de execução

Triggers de Banco Bloco PL/SQL associado a tabelas do Banco de Dados e que disparam automaticamente

Triggers de Aplicação Bloco PL/SQL associado a algum evento da aplicação

PL-SQL

Criando uma Procedure

PL-SQL

Criando uma Procedure

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Sintaxe:Sintaxe:

PROCEDURE nomePROCEDURE nome

[(parâmetros,...)] [(parâmetros,...)] ------ parâmetros formaisparâmetros formais

ISISISIS

<bloco pl/sql><bloco pl/sql>

END nome;END nome;

Sintaxe de parâmetrosSintaxe de parâmetrosparâmetros [IN | OUT | IN OUT] datatypeparâmetros [IN | OUT | IN OUT] datatype

PL-SQL

Criando uma Procedure

PL-SQL

Criando uma Procedure

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Parâmetro Descrição

IN Argumento padrãoPassa um valor de ambiente de chamada para dentro da Procedure

OUT Deve ser especificadoOUT Deve ser especificadoRetorna um valor de dentro da procedure para o ambiente de chamada

IN OUT Deve ser especificadoPassa um valor de ambiente de chamada para dentro da Procedure e retorna um valor de dentro da procedure para o ambiente de chamada

PL-SQL

Criando uma Procedure

PL-SQL

Criando uma Procedure

Crie uma procedure para atualizar o salário de um Crie uma procedure para atualizar o salário de um empregado a ser especificado pelo usuário:empregado a ser especificado pelo usuário:

CREATE OR REPLACE PROCEDURE muda_salario

(v_cpf in number, v_novo_sal in number)

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

(v_cpf in number, v_novo_sal in number)

is

begin

update empregados

set salario= v_novo_sal

where cpf = v_cpf;

commit;

end;

/

PL-SQL

Criando uma Função

PL-SQL

Criando uma Função

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Sintaxe:Sintaxe:

FUNCTION nomeFUNCTION nome

[(parâmetro,...)][(parâmetro,...)]

RETURN datatypeRETURN datatypeRETURN datatypeRETURN datatype

ISIS

<bloco pl/sql>;<bloco pl/sql>;

END nome;END nome;

PL-SQL

Criando uma Função

PL-SQL

Criando uma Função

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

ExemploExemplo

function taxafunction taxa

(v_valor in number)(v_valor in number)

return numberreturn numberreturn numberreturn number

isis

beginbegin

return (v_valor * .7);return (v_valor * .7);

End;End;

PL-SQL

Prática

PL-SQL

Prática

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Crie a seguinte tabela Funcionario:Crie a seguinte tabela Funcionario:

CodFuncionario Long

Nome Varchar2(80)

Salario Number(10,2)

Desenvolva uma procedure que aumente os salários de todos os Desenvolva uma procedure que aumente os salários de todos os funcionários em 10%.funcionários em 10%.Criar uma função para calcular a área de uma circunferência Criar uma função para calcular a área de uma circunferência ((ππ.r²)..r²).

Salario Number(10,2)

Operador de ConcatenaçãoOperador de Concatenação

Representado por duas barras verticais( || )Representado por duas barras verticais( || )Junta colunas ou strings com outras colunas.Junta colunas ou strings com outras colunas.

Exemplo:Exemplo:

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Exemplo:Exemplo:> Select prenome || sobrenome as “Empregados”

from empregados;

•• Machado, Felipe Nery Rodrigues. Banco de Dados Machado, Felipe Nery Rodrigues. Banco de Dados -- Projeto e Projeto e Implementação, Érica, 2004, 1ª Edição.Implementação, Érica, 2004, 1ª Edição.•• SILBERSCHATZ, Abraham; KORTH, Henry e SUDARSHAN, S. SILBERSCHATZ, Abraham; KORTH, Henry e SUDARSHAN, S. Sistema de Banco de Dados Sistema de Banco de Dados –– São Paulo: MAKRON Books, 1999.São Paulo: MAKRON Books, 1999.

BibliografiaBibliografia

Prof. André Y. Kusumoto - andre_unip@kusumoto.com.br

Sistema de Banco de Dados Sistema de Banco de Dados –– São Paulo: MAKRON Books, 1999.São Paulo: MAKRON Books, 1999.•• HEUSER, Carlos Alberto. Projeto de Banco de Dados HEUSER, Carlos Alberto. Projeto de Banco de Dados –– Editora Editora Sagra Luzzatto, 2001, 4ª Edição.Sagra Luzzatto, 2001, 4ª Edição.

•• Notas de aula do Prof. Gustavo Henrique dos Santos Notas de aula do Prof. Gustavo Henrique dos Santos •• Silveira, José Silveira, José MarcosMarcos Barbosa da. Universidade Paulista, 2008.Barbosa da. Universidade Paulista, 2008.