Apostila Introdução ao Oracle Procedural Option para amigos

�� Rua Ponta Grossa, 313. LONDRINA - PR. �� (043) 3327.2727 [email protected] PÁG. 1

INTRODUÇÃO ao ORACLE


I.1 - OBJETIVOS DO CURSO

� Neste curso você aprenderá a usar o SQL*Plus para: • Definir e alterar a estrutura de um banco de dados;

• Consultar, inserir, alterar ou remover informações de um banco de

dados;

• Gerar relatórios a partir de consultas;

• Gerenciar a segurança e o desempenho de um banco de dados. • Conhecer os princípios e terminologias da tecnologia Relacional. • Utilizar blocos de linguagem PL/SQL. • Realizar transações com PL/SQL.


I.2 - O QUE É BANCO DE DADOS?

� É um conjunto de informações armazenadas de forma organizada. No

tradicional sistema manual, as informações geralmente são armazenadas em arquivos de papéis. Para recuperar as informações armazenadas, algum tipo de procura manual é necessária. Já nos sistemas de armazenamento digitalizados, os dados são armazenados em fitas magnéticas ou discos rígidos e o acesso aos dados é feito através de softwares de computador.

� Por quê utilizar sistemas de computadores para armazenar informações

em Banco de Dados? � Muitas vantagens surgem com a utilização de sistemas de informações

baseados em computadores como:

• Recuperação e atualização das informações; • Armazenamento das informações em menor espaço do que no sistema

manual; • Vários usuários podem compartilhar o mesmo dado e utilizá-lo para

diferentes tarefas; • Controle de redundância das informações; • Incompatibilidade de dados podem ser previstos; • Forçar utilização de padronizações; • Controle de acesso e integridade (segurança).


I.3 - RDBMS (RELATIONAL DATABASE MANAGEMENT SYSTEM)

� Para controlar o acesso e armazenamento de informações é necessário a

utilização de um Sistema de Gerenciamento de Banco de Dados (DBMS). Um DBMS é um software que gerencia os pedidos dos usuários para acesso às informações, um DBMS também controla o armazenamento, a recuperação e a modificação dos dados de interesse dos usuários.

� O DBMS atua como “interface” entre o armazenamento físico dos dados e

os usuários. Quando um usuário efetua um pedido de acesso, o DBMS intercepta este pedido e executa as operações necessárias no Banco de Dados. Portanto, o DBMS “protege” os usuários do Banco de Dados dos detalhes técnicos dos equipamentos, da estrutura de armazenamento e da estratégia de acesso.

� Quando um usuário envia uma solicitação ao DBMS, este intercepta a solicitação, interpreta e realiza as operações necessárias no banco de dados.

� Várias alternativas existem para implementar um DBMS. Os tipos mais utilizados são:

• Hierárquico • Lista invertida • Rede • Relacional

� Os vários Banco de Dados que têm sido desenvolvidos recentemente são Relacionais. O Sistema de Gerenciamento de Banco de Dados da ORACLE é relacional. Por essa razão, nós estaremos nos concentrando unicamente no acesso relacional ao Gerenciador de banco de dados.


I.4 - MODELO RELACIONAL

� O princípio do modelo relacional surgiu em junho de 1970 num artigo

publicado que se chamava “A Relational Model of Data for Large Shared Data Banks”. Este artigo foi escrito por Dr. E.F. Codd.

� Para os usuários, o Banco de Dados Relacional é uma coleção de tabelas

bi-dimensionais as quais são de fácil compreensão. Existe quatro conceitos que precisamos entender:

• Tabelas • Colunas • Linhas • Campos

TABELA

Código Nome Endereço 1 Maria Rua 1 2 Pedro Rua 30

Campo

Coluna � O modelo relacional imita uma parte da matemática conhecida como

Álgebra Relacional. Esse processo envolve:

• Uma coleção de objetos conhecidos como Relacionamentos; • Um conjunto de ações que agem no relacionamento produzindo novos

relacionamentos.

� Uma relação pode ser vista como uma tabela. A recuperação de dados é feita através de operações relacionais sobre estas tabelas.

Linha


I.5 - OPERADORES RELACIONAIS

RELAÇÃO DESCRIÇÃO Restrição

É uma operação que retira e mostra dados de uma relação. É possível mostrar todas as linhas, ou apenas aquelas que respeitem uma determinada condição. Conhecida como subconjunto horizontal.

Projeção

É a operação que exibe certas colunas de uma relação e é chamada de subconjunto vertical.

Produto

É o resultado de quando linhas de dois conjuntos de dados são concatenados. Todas as linhas do primeiro conjunto são concatenados com as do segundo. Produtos cartesianos produzem muitas linhas.

Junção (join)

É o resultado obtido quando dois conjuntos de dados são concatenados de acordo com uma determinada condição.

União

Exibe todas as linhas que aparecem em ambas as relações.

Intersecção

Exibe apenas aquelas que existem em ambos os conjuntos.

Diferença

Mostra as linhas que aparecem em apenas uma das relações.


UNION

INTERSECTION

DIFFERENCE

PRODUTO Pedro P Vendedor Pedro Vendedor

Joao R Gerente = Pedro Gerente Ricardo O Joao Vendedor D Joao Gerente Ricardo Vendedor Ricardo Gerente

JOIN

Ricardo 10 J 10 Financeiro Ricardo 10 10 Financeiro Joao 10 O 20 RH Joao 10 10 Financeiro Pedro 20 I 30 Vendas = Pedro 20 20 RH Jose 30 N 40 CPD Jose 30 30 Vendas


I.6 - PROPRIEDADES DE UM BANCO DE DADOS RELACIONAL

� Um banco de dados relacional apresenta-se como coleções de relações

(tabelas) ao usuário. � O formato linha/coluna de uma tabela é a maneira usual de visualizar os

dados. � Existe um conjunto de operadores para particionar e combinar coleções de

dados (seleção, projeção, produto, join, união, interseção, diferença). � Não pode haver ponteiros explícitos. Conexões são realizadas baseadas

puramente em dados. � A linguagem utilizada para pesquisa à base de dados é não-procedural e

parecida com o inglês. � O usuário não precisa informar a rota de acesso ao dado e também não

precisa saber como os dados estão armazenados fisicamente. � Todos comandos para manuseio dos dados são contemplados pela

linguagem SQL. � Há uma total independência dos dados.


I.7 - PROPRIEDADE DE UMA COLEÇÃO DE DADOS TABULAR

� Uma única tabela possui as seguintes propriedades:

• Não pode haver linhas duplicadas; • Não há colunas com nomes duplicados; • A ordem de uma linha é insignificante;

• Valores são atômicos, não se decompõem.

SQL*Plus

Forms Reports

Case Pro*Oracle SQL ORACLE

Card KERNEL Graphics

SQL*Net SQL*Loader


I.8 - ARQUITETURA DE PRODUTOS ORACLE

� O RDBMS ORACLE é o produto principal da Oracle Corporation. Ele inclui um gerenciador de banco de dados e diversas ferramentas para auxiliar os usuários e o administrador do banco de dados - gerentes de sistema - a manter, monitorar e utilizar os dados.

� O núcleo do RDBMS é o “kernel” o qual efetua as seguintes tarefas:

• Gerenciar a armazenagem e definição dos dados; • Controlar e restringir o acesso e concorrência aos dados; • Permitir “back-up” e recuperação de dados; • Interpretar SQL.

� Os comandos SQL são utilizados pelos programadores e usuários para acessar os dados num Banco de Dados ORACLE. Os programas aplicativos e as ferramentas ORACLE freqüentemente permite aos usuários acessarem dados no banco de dados sem diretamente utilizar SQL, mas essas aplicações devem também utilizar SQL quando executar seus pedidos de acesso. Portanto o banco de dados pode ser acessado unicamente utilizando SQL.

� Uma parte do Kernel é o otimizador. O otimizador examina rotas de acesso para resolver e otimizar solicitações de um comando SQL.

� A ORACLE oferece uma variedade adicional de ferramentas para acessar

os dados no Banco de Dados.


I.9 - SQL

� Um sistema gerenciador de banco de dados necessita de uma linguagem

de consulta para permitir que o usuário acesse os dados. Structured Query Language (SQL - pronuncia-se “sequel”) é a linguagem adotada pela maioria dos bancos relacionais.

� O SQL foi desenvolvido em um protótipo de sistema de banco de dados

relacionais - sistema R - da IBM no meio dos anos 70. Em 1979, a Oracle lançou o primeiro banco de dados relacional comercial que se utilizava de SQL.

I.10 - CARACTERÍSTICAS DO SQL

� SQL é uma linguagem “English-like”. Ela utiliza palavras como select, insert, delete como parte de seu conjunto de comandos.

� SQL não é uma linguagem procedural. Você identifica quais informações

você precisa e não como buscá-las. Em outras palavras, a linguagem SQL não requer que você especifique o método de acesso aos dados. Todos os comandos SQL utilizam o otimizador - parte do “kernel” do RDBMS - para determinar a maneira mais rápida de recuperar os dados.

� SQL processa conjuntos de registros ao invés de um único registro por

vez. A mais comum forma de conjunto é uma tabela.

� SQL pode ser usado por um conjunto de usuários incluindo DBA´s, programadores, gerentes de pessoal e muitos outros grupos de usuários.

� SQL contém comandos que efetuam uma variedade de tarefas como:

• Consulta aos dados. • Inserir, atualizar e remover linhas de uma tabela. • Criar, modificar e remover objetos do Banco de Dados. • Controlar o acesso ao Banco de Dados e seus objetos. • Garantir a consistência do Banco de Dados.


I.11 - O QUE É SQL*Plus

� SQL*PLUS é uma interface pela qual pode-se entrar e executar comandos SQL. Existe vários comandos de SQL*PLUS os quais podem facilitar processos e formatar resultados de comandos de SQL, podendo editá-los e até gravá-los.

� É a interface básica para manipulações genéricas de um banco de dados ORACLE.

SQL*PLUS

Parâmetros de controle de

Formatação

SQL +comandosde controle

DBORACLE

� Características principais:

• Interpretador de comandos SQL; • Possui extensões para formatação de relatórios.


I.12 - PL/SQL

� Desde a versão 6 do ORACLE, foi introduzido uma opção no RDBMS chamada PL/SQL (Procedural Language for SQL). PL/SQL aumenta a capacidade do SQL oferecendo blocos de construtores procedimentais combinados com a capacidade de manipulação de dados de forma não procedimental do SQL. As principais caracterísiticas do PL/SQL são:

• Declaração de variáveis. • Atribuições ( X := Y + Z). • Controles condicionais ( IF, THEN, ELSE, ELSIF, GO TO). • Repetições de comandos - looping ( FOR, WHILE, EXIT, WHEN). • Gerenciamento de exceções. • Aumenta a performance (operações processadas junto aos dados). • Aumenta produtividade.

� O PL/SQL oferece:

• Aumento de Performance

� Quando você utiliza SQL, seus comandos são enviados e

processados pelo RDBMS um por vez, enquanto um bloco de comandos PL/SQL que contém um número de comandos SQL combinados com construtores PL/SQL são enviados e processados pelo RDBMS uma única vez. Esta característica aumenta a performance especialmente em sistemas cliente/servidor.

� Comandos PL/SQL são tratados pela “engine” PL/SQL que faz

parte do “Kernel”, ou parte das ferramentas ORACLE. Comandos SQL são executados pelo executor de SQL do “Kernel”.

• Aumento de Produtividade

� PL/SQL adiciona o poder do processamento procedural no

desenvolvimento de aplicativos. Adicionalmente aplicativos escritos em PL/SQL são portáveis para qualquer computador ou sistema operacional que execute o ORACLE RDBMS.

� O PL/SQL deve ser visto como uma tecnologia e não como um

produto externo ao RDBMS.


I.13 - PL/SQL NO ORACLE RDBMS ORACLE RDBMS Bloco PL/SQL Mecanismo DECLARE Bloco PL/SQL

PROCEDURAL PROCEDURAL DECLARE

BEGIN PROCEDURAL PROCEDURAL PROCEDURAL Procedural

SQL BEGIN Statement PROCEDURAL PROCEDURAL Executor SQL SQL END PROCEDURAL SQL END SQL Statement Executor


I.14 - CONJUNTO DE COMANDOS SQL

SELECT

Este é o comando mais utilizado, ele é utilizado para recuperar dados do banco de dados.

INSERT / UPDATE / DELETE

Estes comandos são utilizados para entrar com novas linhas, alterar as linhas já existentes e remove-lás das tabelas em um específico Banco de Dados. São também conhecidos como comandos DML (Data Manipulation Language).

CREATE/ALTER/DROP

Estes comandos são utilizados dinamicamente para configurar (criar, alterar e remover) estrutura de dados. São também conhecidos como comandos DDL (Data Definition Language).

GRANT/REVOKE

Estes comandos são utilizados para controlar as permissões de acesso ao Banco de Dados e seus objetos.

Nota : Existem mais comandos SQL os quais estão referenciados no

SQL*PLUS, Reference Guide.


II - COMANDOS SQL e SQL*Plus


II.1 - CONTEÚDOS

1. Ativação do SQL*Plus

2. Criação de tabelas

3. Manipulação de tabelas

4. Formatação de relatórios

5. Edição de comandos

6. Outros comandos


II.2 - ATIVAÇÃO DO SQL*Plus

� Para entrar no SQL*Plus, digite: $ sqlplus <ENTER> SQL*Plus: Release 3.1.1.9.1 - Production on Wed Apr 3 08:56 … Copyright (c) ORACLE Corporation 1979, 1992, All … Enter user-name: oral1 Enter password: _________ a senha não é exibida Connected to: ORACLE7 Server Release 7.0.12.1.0 - Production With the procedural option PL/SQL Release 2.0.14.0.1 - Production SQL>

ou a senha é exibida

$ sqlplus ora1/senha1 SQL*Plus: Release 3.1.1.9.1 - Production on Wed Apr 3 08:56 … Copyright (c) ORACLE Corporation 1979,1992. All … Connected to: ORACLE7 Server Release 7.0.12.1.0 - Production With the procedural option PL/SQL Release 2.0.14.0.1 - Production SQL>

� Para sair do sql*plus, digite: SQL> exit


II.3 - ESCREVENDO COMANDOS SQL

� Quando escrevemos comandos SQL, é importante relembrar algumas

pequenas regras e diretrizes para construir comandos válidos como:

• Comandos SQL podem estar em uma ou mais linhas. • Usualmente, as cláusulas são escritas em linhas separadas. • Tabulação pode ser utilizada. • Palavras reservadas SQL não podem ser quebradas entre linhas. • Os comandos SQL podem ser escritos em maiúsculo ou minúsculo. • Quando um comando SQL é entrado, as subsequentes linhas são

numeradas. Este é o buffer do SQL. • Somente um comando pode estar contido no SQL buffer. Este

comando pode ser executado das seguintes formas:

♦ Colocando “;” no final. ♦ Colocando “;” ou “/” na última linha. ♦ Colocando “/” no prompt do SQL. ♦ Executando o comando RUN no prompt do SQL.

� Qualquer um dessas sentenças são válidas:

SQL> SELECT * FROM emp;

SQL> SELECT 2 * 3 FROM 4 emp 5 ;

SQL> SELECT * 2 FROM emp;


II.4 - CRIAÇÃO DE TABELAS

� O que é uma tabela? Nome da coluna Coluna Linha ou (registro)

EMP ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Campos

� Para criar a tabela de empregados: SQL> CREATE TABLE emp ( 2 empno number(4) not null, “,” separa as definições 3 ename char(10), das colunas 4 job char(10), 5 mgr number(4), 6 hiredate date, 7 sal number(10,2), “;” executa o comando 8 comm number(10,2), 9 deptno number(2)); Indica a continuação do comando somente o cabeçalho

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO


II.5 - INCLUINDO NOVAS LINHAS EM UMA TABELA � O comando INSERT é usado para inserir linhas em uma tabela. � Sintaxe:

INSERT INTO table schema. View @dblink VALUES ( expr ) , ( column ) subquery_2

Onde: Schema é o schema contendo a tabela ou visão. Se você

omitir o eschema, o Oracle assume a tabela ou visão como seu próprio dono.

table, view é o nome da tabela no qual as linhas serão inseridas. Se você especificar uma visão, o Oralce insere linhas na tabela que a visão baseia-se.

Dblink é o nome completo ou parcial de um link de banco de dados para um banco de dados remoto onde a tabela ou visão está localizada. Se você omitir dblink, o Oracle assume que a tabela ou visão está em um banco de dados local.

Column é uma coluna da tabela ou visão. Na linha inserida, cada coluna nesta lista está atribuída a um valor na cláusula VALUES ou a subconsulta.

VALUES Especifica uma linha de valores a serem inseridos na tabela ou visão.

subsquery_2 é uma sub-consulta que retorna linhas que são inseridas na tabela. A lista selecionada desta consulta tem que ter o mesmo número de colunas da lista do comando INSERT.

� Caracteres e datas devem sempre estar entre aspas simples.

� Para você inserir linhas em uma tabela, a tabela tem que ser sua ou você

tem que ter privilégios sobre ela. � O privilégio de sistema INSERT ANY TABLE também permite a você

inserir linhas em qualquer tabela de qualquer usuário.


� Para inserir informações na tabela EMP. SQL> INSERT INTO emp VALUES 2 (7369, ’SMITH’, ’CLERCK’, 7902, 3 ‘17-DEC-80’, 800, NULL, 20); SQL> INSERT INTO emp VALUES 2 (7566, ‘JONES’, ‘MANAGER’, 7839, ‘02-APR-81’, 3 2975, NULL, 20); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTN

O 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20

� Duas formas para inserir informações na tabela EMP.

SQL> INSERT INTO emp 2 (ename, empno, job, mgr, hiredate, sal, deptno) 3 VALUES (‘SAMANTA’, 103, ‘ANALISTA’, 110, sysdate-30, 4 1500, 20); ou SQL> INSERT INTO emp 2 VALUES (103, ‘SANTANA’, ‘ANALISTA’, 110, sysdate-30, 3 15000, NULL, 20);

� É possível incluir uma nova linha com valores atribuídos para cada coluna,

neste caso os nomes das colunas podem ser omitidos. Recomenda-se que a lista de colunas seja sempre especificada.

� O comando abaixo insere um registro na tabela dept. INSERT INTO dept (deptno, dname, loc) VALUES (99,’Financeiro’, ‘Curitiba’);

� O exemplo abaixo insere um linha na tabela DEPT.

INSERT INTO dept VALUES (50, ’PRODUCAO’, ‘MARINGA’)

� O exemplo a seguir insere uma linha com seis colunas na tabela EMP. Uma dessas colunas está atribuída com valor nulo e outra está atribuída com notação científica. INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (7890, ‘JINKS’, ‘CLERK’, 1.2E3, NULL, 40)


� O comando abaixo inclue uma coluna de cada vez. Utilizando variáveis para substituição, é possível aumentar a velocidade de entrada de informações. INSERT INTO dept (deptno, dname, loc) VALUES (&dept_num, ‘&dept_nome’, ‘&localizacao’);

� Quando este comando for executado, os valores para as variáveis serão pedidos.

� Exemplo: SQL> INSERT INTO salgrade (losal, hisal, grade) 2 SELECT min(sal), max(sal), substr(sal, 1,1) 3 FROM emp 4 GROUP BY substr(sal, 1,1); INSERT INTO func_dept_10 (empno, ename, sal, job, hiredate) SELECT empno, ename, sal, job, hiredate FROM emp WHERE deptno = 10;


II.6 - ALTERANDO LINHAS DE UMA TABELA

� O comando UPDATE é usado para alterar valores já armazenados em tabelas.

� Para você atualizar linhas em uma tabela, a tabela tem que ser sua ou você tem que ter privilégios sobre ela.

� O privilégio de sistema UPDATE ANY TABLE também permite a você

alterar linhas em qualquer tabela de qualquer usuário.

� Sintaxe: UPDATE , table schema. View @dblink t_alias snapshot ( subquery_1 ) , , SET ( column ) = ( subquery_2 ) column = expr ( subquery_3 ) WHERE condição


Onde: schema é o schema contendo a tabela ou visão. Se você omitir o

schema, o Oracle assume a tabela ou visão como seu próprio dono.

table, view é o nome da tabela no qual está sendo atualizada. Se você especificar uma visão, o Oralce atualiza na tabela que a visão baseia-se.

dblink é o nome completo ou parcial de um link de banco de dados para um banco de dados remoto onde a tabela ou visão está localizada. Se você omitir dblink, o Oracle assume que a tabela ou visão está em um banco de dados local.

alias é um nome diferente para a tabela, visão ou subconsulta para ser referenciada no comando.

subquery_1 é uma subconsulta que o oracle trata da mesma maneira que uma visão.

column é o nome de uma coluna para a tabela ou visão que está sendo atualizada. Se você omitir a coluna da tabela na cláusula SET, o valor da coluna permanece inalterado.

expr é o novo valor atribuído a coluna correspondente. subquery_2 é uma subconsulta que retorna novos valores que são

atribuídos para colunas correspondentes. subquery_3 é uma subconsulta que retorna um novo valor que está

atribuído a coluna correspondente. where restrição para atualizar as linhas. Se essa cláusula for

omitida, o Oracle atualiza todas as linhas na tabela ou visão.

� O comando abaixo fornece ao SCOTT a profissão de VENDEDOR e um

aumento de salário de 10%. UPDATE emp SET job = ‘VENDEDOR’, sal = sal * 1.1 WHERE ename = ‘SCOTT’;

� O exemplo abaixo transforma para nulo as comissões de todos os empregados que tenham como cargo SALESMAN.


UPDATE emp SET comm = NULL

WHERE job = ‘SALESMAN’;

� Para colocar R$1000,00 de comissão para o funcionário de número 7499. SQL> UPDATE emp 2 SET comm = 1000 3 WHERE empno = 7499;

� Para dobrar o salário de todos empregados do departamento 20 e transferí-los para o departamento 30. SQL> UPDATE emp 2 SET sal = 2 * sal, deptno = 30 3 WHERE deptno = 20;

� Para atualizar o salário do funcionário 7902; EMPNO ENAME … SAL … 7902 FORD 3000 SQL> UPDATE emp 2 SET sal = 3500 3 WHERE empno = 7902; EMPNO ENAME … SAL … 7902 FORD 3500

� Para dar 10% de aumento para os funcionários que trabalham no departamento 10. SQL> UPDATE emp 2 SET sal = sal*1.10 3 WHERE deptno = 10;

� O comando abaixo promove JONES a gerente do departamento 20 com um salário de R$ 1000,00. (Assumindo que tenha somente um Jones). SQL> UPDATE emp 2 SET job = ‘MANAGER’, sal = sal + 1000, deptno = 20 3 WHERE ename = ‘JONES’;

II.7 - REMOVENDO LINHAS DE UMA TABELA

� O comando DELETE é usado para remover linhas de uma tabela:


� Sintaxe:

DELETE table FROM schema. View @dblink ( subquery ) WHERE condição

Onde: schema é o schema contendo a tabela ou visão. Se você omitir o

schema, o Oracle assume a tabela ou visão como seu próprio dono.

table, view é o nome da tabela no qual está sendo apagado as linhas. Se você especificar uma visão, o Oralce apaga linhas da tabela que a visão baseia-se.

dblink é o nome completo ou parcial de um link de banco de dados com um banco de dados remoto onde a tabela ou visão está localizada. Se você omitir o dblink o Oracle7 assume que a tabela ou visão está localizada em um banco de dados local.

subquery é uma sub-consulta no qual os dados são selecionados para serem apagados. O Oracle executa uma subconsulta e usa o resultado das linhas como uma tabela na cláusula FROM.

WHERE apaga somente linhas que satisfaçam a condição. A condição pode referenciar a tabela e pode conter uma sub-consulta.

� Para você apagar linhas em uma tabela, a tabela tem que ser sua ou você

tem que ter privilégios sobre ela.

� O privilégio de sistema DELETE ANY TABLE também permite a você apagar linhas em qualquer tabela de qualquer usuário.

� Para remover o empregado de número 7369. SQL> DELETE FROM emp WHERE empno = 7369;

� Para remover todos os empregados do departamento 20. SQL> DELETE FROM emp WHERE deptno = 20;

� Para remover todos os empregados. SQL> DELETE from emp;


� Para remover o empregado de número 7934:

SQL> DELETE FROM emp 2 WHERE empno = 7934;

� Para remover todos os empregados admitidos a partir de 1989: SQL> DELETE FROM emp 2 WHERE hiredate >= ‘01-JAN-89’;

� O comando abaixo apaga todos empregados da tabela EMP que tenham profissão SALESMAN e comissão menor do que R$100,00. SQL> DELETE FROM emp 2 WHERE job = ‘SALESMAN’ 3 AND comm < 100;

II.8 - O COMANDO TRUNCATE

� O comando TRUNCATE é usado para remover todas as linhas de uma tabela: TRUCANTE {TABLE | CLUSTER} tabela [ {DROP | REUSE} STORAGE] >> tabela

Nome da tabela de onde serão removidas as linhas

� Observações:

• A opção DROP STORAGE libera o espaço físico alocado à tabela para

o banco de dados. • O comando TRUNCATE não dispara os gatilhos de deleção. • Não é possível executar o comando TRUNCATE sobre uma tabela

referenciada por outra qualquer.

� Exemplos: �

• Para remover as linhas da tabela EMP, liberando seu espaço físico: SQL> TRUNCATE TABLE emp DROP STORAGE;

I.9 - EDIÇÃO DE COMANDOS


� O último comando SQL (não SQL*Plus) fica armazenado no buffer de edição do SQL*Plus.

� Para listar o conteúdo do buffer:

SQL> list qualquer um desses comandos ou pode ser utilizado SQL> l 1 SELECT 2 empno, ename 3 FROM emp 4* WHERE empno = 7902 “*” indica a linha corrente

� Para apagar a linha corrente SQL> del SQL> l 1 SELECT 2 empno, ename 3* FROM emp

� Para inserir linhas após a linha corrente: SQL> list 2 2* empno, ename SQL> imput ou SQL> list deixe um linha em branco 3 job, para terminar a inserção 4 sal 5 SQL> list 1 SELECT 2 empno, ename 3 job, 4 sal 5* FROM emp;

� Para acrescentar caracteres no fim da linha corrente: SQL> list 2


2* empno, ename SQL> append, mgr, ou SQL> a, mgr, SQL> list 1 SELELCT 2 empno, ename, mgr, 3 job, 4 sal 5* FROM emp;

� Para substituir caracteres da linha corrente: SQL> list 2 2* empno, ename, mgr, SQL> change/mgr/sal/ ou SQL> c/mgr/sal/ SQL> list 1 SELECT 2 empno, ename, sal, 3 job, 4 sal 5* FROM emp

� Para executar o comando existente no buffer: SQL> run ou SQL> r ou SQL> /

� O SQL*Plus permite a utilização de outros buffers além do buffer de comandos SQL.

� Para ativar um buffer chamado “TESTE”, por exemplo: SQL> set buffer teste

� Para saber qual é o buffer corrente: SQL> show buffer


buffer TESTE

� Para modificar o conteúdo do buffer corrente com um editor externo: SQL> edit

� O editor externo pode ser especificado pelo usuário da seguinte forma: SQL> define _editor = vi

� Para salvar o buffer corrente num arquivo do Sistema Operacional: SQL> save arquivo nome do arquivo; a extensão .sql é acrescentada automaticamente

� Para recuperar um comando SQL armazenado num arquivo: SQL> get arquivo

� Para remover o conteúdo do buffer corrente: SQL> clear buffer

� Para executar uma seqüência de comandos (SQL ou SQL*Plus) armazenada num arquivo: SQL> start arquivo ou SQL> @arquivo

� Para editar um arquivo qualquer do sistema operacional: SQL> edit ex21 é assumida a extensão “.sql”


II.10 - OUTROS COMANDOS

� Para executar um comando qualquer do sistema operacional: SQL> host is - al ou SQL> ! Is - al total 20 drwxr-xr-x 2 curso oracle 128 Jan 3 18:02 . drwxr-xr-x 3 curso oracle 1024 Jan 3 17:59 .. -rw-r—r—1 curso oracle 800 Jan 3 17:59 ex21.sql -rw-r—r—1 curso oracle 838 Jan 3 17:59 ex22.sql -rw-r—r—1 curso oracle 1152 Jan 3 18:01 ex23.sql -rw-r—r—1 curso oracle 1616 Jan 3 18:01 ex24.sql -rw-r—r—1 curso oracle 1796 Jan 3 18:01 ex25.sql

� Para obter ajuda sobre um comando SQL ou SQL*Plus:

SQL> help select nome do comando

� Para saber todos os comandos disponíveis: SQL> help commands


II.11 - EXERCÍCIOS

1. Crie uma tabela chamado “ALUNO” com as seguintes colunas: NOME CHAR(30) NOT NULL ENDER CHAR(50) CIDADE CHAR(25) ESTADO CHAR(2) CEP NUMBER(5) ANIVERSÁRIO DATE

2. Insira dados sobre você e algum membro da sua família na tabela ALUNO. 3. Consulte o nome e a data de aniversário das pessoas cadastradas. 4. Consulte o seu (e somente o seu) endereço completo. 5. Salve o comando anterior no arquivo “ex24.sql”, saia do SQL*Plus, entre

novamente e execute o comando contido no arquivo gerado. 6. Dobrar o salário do funcionário de número 7566. 7. Remover os empregados que possuem comissão maior que o próprio

salário.


III - CONSULTAS BÁSICAS


III.1 - CONTEÚDO

1. Seleção de colunas 2. Uso de expressões 3. Ordenação de resultados 4. Agrupamento 5. Operações de conjunto 6. Junção de tabelas


III.2 - SELEÇÃO DE COLUNAS

� A sentença do comando SELECT recupera as informações do Banco de Dados implementando todos os operadores da Álgebra Relacional.

� Uma pesquisa simples deve conter:

• A cláusula SELECT que selecionará as colunas que serão exibidas. Os

nomes das colunas devem estar separados por vírgula. • A cláusula FROM que especifica as tabelas envolvidas.

� Para você selecionar dados de uma tabela ou snapshot, a tabela ou o snapshot tem que ser sua ou você tem que ter privilégios sobre ela.

� O privilégio de sistema SELECT ANY TABLE também permite a você

selecionar linhas em qualquer tabela de qualquer usuário.


� Sintaxe:

SELECT * , DISTINCT ALL table . * schema. View snapshot expr

c_alias AS , FROM table schema. View @dblink t_alias WHERE condição snapshot CONNECT BY condição UNION comando SELECT START WITH condição UNION ALL

, INTERSECT GROUP BY expr MINUS HAVING condição , ORDER BY expr position ASC c_alias DESC


Onde: DISTINCT retorna somente uma cópia de cada linha

selecionada que for duplicada. ALL retorna todas as linhas selecionadas,

incluindo as duplicadas. O default é ALL. * select todas as colunas de todas as tabelas. table.*, view.*, snapshot.* seleciona todas as colunas da tabela, visão

ou snapshot. Você pode usar o schema para selecionar de uma tabela, visão ou snapshot em um outro schema.

expr seleciona uma expressão. c_alias provê um nome diferente para a coluna. schema é o schema contendo a tabela, visão ou

snapshot. Se você omitir o schema, o Oracle assume a tabela, visão ou snapshot como seu próprio dono.

table, view, snapshot é o nome da tabela, visão ou snapshot no qual está sendo selecionado.

dblink é o nome completo ou parcial para um link de banco de dados com um banco de dados remoto onde a tabela, visão ou snapshot está localizada.

t_alias provê um nome diferente para a tabela, visão ou snapshot ou subconsulta.

WHERE restringe as linhas selecionadas. Se você omitir esta cláusula, o Oracle retorna todas as linhas da tabela, visão ou snapshot.

START WITH, CONNECT BY

retorna linhas em uma ordem hierárquica.

GROUP BY função de grupo HAVING restringe os grupos de cada linha retornada

no GROUP BY. UNION, UNION ALL, INTERSECT, MINUS

combina as linhas retornadas por dois comandos SELECT usando uma operação de função.


ORDER BY ordena as linhas retornadas por um comando. Expr �� ordena linhas baseadas no valor de expr. A expressão é baseada em colunas na lista selecionada ou colunas em tabelas, visões ou snapshots na cláusula FROM. Position �� ordena linhas baseado em seus valores para a expressão na posição da lista selecionada. ASC, DESC �� especifica a ordenação ascendente ou descendente. O opção ASC é default.

� O comando abaixo mostra o número do departamento, nome e número do

patrão da tabela EMP.

SELECT deptno, ename, mgr FROM emp;

DEPTNO ENAME MGR ----------- ------------------------- ------------ 10 SMITH 7902 30 ALLEN 7698 30 WARD 7698 20 JONES 7839 30 BLAKE 7839 10 CLARK 7839 20 SCOTT 7566 10 KING 30 TURNER 7698 20 ADAMS 7788 30 JAMES 7698 20 FORD 7566 10 MILLER 7782


� Para recuperar as informações da tabela EMP:

SQL> SELECT * FROM emp;


� O comando abaixo seleciona linhas dos empregados que trabalham no

departamento 30. SQL> SELECT * 2 FROM emp 3 WHERE deptno = 30;

� A ordem em que as colunas são especificadas muda a apresentação do resultado: SQL> SELECT ename, empno 2 FROM emp 3 WHERE empno < 7500;

ENAME EMPNO SMITH 7369 ALLEN 7499

� Para indicar as colunas a serem recuperadas de uma tabela:

SQL> SELECT empno, ename 2 FROM emp 3 WHERE empno < 7500;

EMPNO ENAME 7369 SMITH 7499 ALLEN


III.3 - OPERADORES ARITMÉTICOS Os operadores aritméticos são utilizados para somar, subtrair, multiplicar e

dividir valores numéricos. Os operadores de maior precedência, os parênteses, conforme pode ser observado na tabela seguinte, permite alterar a ordem de avaliação dos operadores das expressões. Esta tabela lista os operadores aritméticos na ordem decrescente de precedência:

( )

Alteram as regras de precedência dos operadores. Os operadores entre os parênteses são avaliados em primeiro lugar. SELECT (2+2) / (2-1) from dual;

+ -

Assinalam uma expressão como positiva ou negativa. SELECT * FROM EMP WHERE COMM = -10 SELECT * FROM EMP WHERE SAL > 1000

* /

Multiplicam e dividem valores numéricos.

SELECT 2*X+10 ... WHERE X > Y / 2

+ -

Somam e subtraem valores numéricos. SELECT 2 * X + 1 ... WHERE X > Y - Z


III.4 - USO DE EXPRESSÕES

� Para saber o salário anual dos empregados

ENAME JOB SAL SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300

SQL> SELECT ename, 12 * sal FROM emp;

ENAME 12*SAL SMITH 9600 ALLEN 19200 WARD 15000 JONES 35700 MARTIN 15000 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JANES 11400 FORD 36000 MILLER 15600


� Para saber o número total de caracteres nas colunas ENAME e JOB:

ENAME JOB SAL SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300

sinônimo para a coluna SQL> SELECT 2 length (ename) + 3 length(job) “Comprimento Total” 4 FROM emp;

Comprimento Total 10 13 12 12 14 12 12 12 13 14 10 10 11 11


III.5 - OPERADORES DE CARACTERES

� Os operadores de caracteres são usados em expressões para manipular

strings de caracter. A seguir o único tipo de operador de caracter:

| |

Faz a concatenação de strings de caracteres, ou seja, encadeiam ou ligam duas strings.

SELECT Ó Nome é´ || ename from emp

� O resultado da concatenação é um valor caracter. As strings de caracteres

dos tipos de dados CHAR podem ter o máximo de 255 caracteres. Esta restrição aplica-se também às strings de caracteres formadas pela concatenação de duas outras strings.

� Para saber os nomes dos empregados que trabalham no departamento 20.

EMP ENAME JOB ... DEPTNO 7369 SMITH CLERK ... 20 7499 ALLEN SALESMAN ... 30 7521 WARD SALESMAN ... 30 7566 JONES MANAGER ... 20 7654 MARTIN SALESMAN ... 30 7698 BLAKE MANAGER ... 30 7782 CLARK MANAGER ... 10 7788 SCOTT ANALYST ... 20 7839 KING PRESIDENT ... 10 7844 TURNER SALESMAN ... 30 7876 ADAMS CLERK ... 20 7900 JAMES CLERK ... 30 7902 FORD ANALYST ... 20 7934 MILLER CLERK ... 10

SQL> SELECT ename 2 FROM emp 3 WHERE deptno = 20;

ENAME SMITH JONES SCOTT ADAMS FORD


� Para saber o tempo de serviço dos funcionários cuja comissão é maior que 50% do salário: SQL> SELECT ename, 2 months_between(sysdate, hiredate) MESES 3 FROM emp 4 WHERE comm > sal * 0.50;

ENAME MESES MARTIN 187.5371


III.6 - OPERADORES DE COMPARAÇÃO

� Os operadores de comparação são usados em condições que comparam

duas expressões. Assim como as condições, os resultados das comparações entre expressões podem ser verdadeiro ou falso. A tabela a seguir lista os operadores de comparação:

( )

Alteram as regras de validação das precedências dos operadores. SELECT (SAL + COMM)/2 from emp;

=

Testa a igualdade entre expressões. SELECT * FROM EMP WHERE SAL = 1000;

!= ^= <>

Testam a diferença entre expressões. SELECT * FROM EMP WHERE SAL <> 1000;

>

Testa se uma expressão é maior que outra. SELECT * FROM EMP WHERE SAL > 1000

<

Testa se uma expressão é menor que outra. SELECT * FROM EMP WHERE SAL < 1000

>=

Testa se uma expressão é maior ou igual a outra. SELECT * FROM EMP WHERE SAL >= 1000

<=

Testa se uma expressão é menor ou igual a outra. SELECT * FROM EMP WHERE SAL <= 1000

IN Verifica se um expressão existe em um dos membros


especificados entre parênteses logo após o operador. Esses membros são strings de caracteres que devem aparecer entre aspas simples e separados por vírgula. O operador é equivalente ao operador “=ANY”. SELECT * FROM EMP WHERE JOB IN (´SALESMAN´,ĆLERK´)

NOT IN

Valida a expressão como falsa se algum membro que segue o operador é nulo. Esses membros são os valores retornados de uma pesquisa efetuada com SELECT, por exemplo. É equivalente a “!= ALL”. Todas as linhas serão avaliadas como falso se qualquer membro da lista de valores referenciada pelo operador NOT IN for nulo.

SELECT * FROM EMP WHERE JOB NOT IN (´SALESMAN´,ĆLERK´)

NOT BETWEEN - AND

Validam uma expressão como sendo maior ou igual ao valor que segue BETWEEN e menor ou igual ao valor que segue AND.

NOT LIKE

Este operador é usado para comparar duas strings de caracteres e verificar se elas são parecidas. • Podem ser utilizados caracteres % e _ . O caracter percentual

(%) é utilizado para comparar um ou mesmo nenhum caracter, entretanto não pode comparar um valor nulo. O caracter sublinhado (_) é utilizado para comparar exatamente um caracter.

• Função UPPER: utilizada para fazer com que as expressões sejam avaliadas corretamente comparando letras minúsculas e maiúsculas.

... SELECT SAL FROM EMP

IS NOT NULL

Este operador deve ser usado quando for necessário testar uma condição para verificar se ela é nula ou não. ... WHERE X IS NOT NULL

III.7 - OPERADORES LÓGICOS


� Os operadores lógicos são usados para afetar o resultado de múltiplas

condições. O operador AND requer que a linha satisfaça todas as duas condições comparadas. Pôr sua vez, o operador OR requer que a linha satisfaça pelo menos uma das duas condições comparadas. SELECT * FROM emp WHERE (hiredate < ´01-JAN-93´ ) AND (sal < 1000)

� O comando select abaixo mostra o nome, cargo, salário e departamento de todos os empregados, exceto os que possuem cargo SALESMAN e trabalham no departamento 10. SELECT ename, job, sal, deptno FROM EMP WHERE NOT(job = ‘SALESMAN’ AND deptno = 30)


III.8 - OUTROS OPERADORES

(+)

Indica que a coluna precedida pelo sinal (+) está relacionada à coluna anterior da declaração e faz parte de outra tabela. Este operador é utilizado para especificar os relacionamentos entre as colunas. ...WHERE DEPT.DEPTNO = EMP.DEPTNO (+)

*

Seleciona todas as colunas das tabelas em uma pesquisa.

SELECT * FROM EMP SELECT EMP.* FROM EMP

COUNT(X)

Retorna o número de linhas onde a expressão X não é nula. SELECT COUNT(DEPTNO) FROM EMP

ALL

Retém os valores duplicados em pesquisas.

SELECT ALL * ... COUNT( ALL DEPTNO)

DISTINCT

Elimina as linha duplicadas do resultado de uma pesquisa ou elimina os valores em duplicidade de uma expressão.

SELECT DISTINCT * ...

COUNT(DISTINCT DEPTNO)

PRIOR

Define a relação entre as colunas das tabelas em uma pesquisa. A expressão que segue o operador PRIOR representa a linha da tabela principal. A outra expressão que se segue à anterior representa a linha com a qual se efetua o relacionamento.

CONNECT BY PRIOR EMPNO = LEVEL_EBN


III.9 - SUBSTITUIÇÃO DE VARIÁVEIS

� Você pode utilizar variáveis nos arquivos de comandos ou nos comandos

SQL para representar valores que serão entrados em tempo de execução. � Uma variável começa sempre com o caracter & e é sempre temporária.

Para definir uma variável permanente deve-se utilizar &&. �� Pegar o número do departamento em tempo de execução. SELECT empno, ename, sal FROM emp WHERE deptno = &numero_departamento;

Enter value from numero_departamento: � Se a variável for do tipo caracter, deve-se entrar com a informação entre

aspas simples. �� Exemplo:

SELECT ename, sal FROM emp WHERE ename = ‘&nome_do_funcionario’;


III.10 - ORDENAÇÃO DE RESULTADOS

� Normalmente a ordem das colunas resultantes de uma pesquisa é

indefinida. A cláusula ORDER BY pode ser utilizada para organizar as linhas. Se usada, ela sempre deve ser a última cláusula da sentença do SELECT. ASC

Parâmetro default, ordena o resultado da pesquisa pelas colunas especificada na ordem ascendente.

DESC

Ordena o resultado da pesquisa pelas colunas especificada na ordem decrescente.

� O comando abaixo retorna o nome, salário multiplicado por 12 e número do departamento em ordem alfabética. SELECT ename, job, sal * 12, deptno FROM emp ORDER BY ename;

� Para listar os funcionários em ordem alfabética: SQL> SELECT ename, job, sal 2 FROM emp 3 ORDER BY ename;

ENAME JOB SAL ADAMS CLERK 1100 ALLEN SALESMAN 1600 BLAKE MANAGER 2850 CLARK MANAGER 2450 FORD ANALYST 3000 JAMES CLERK 950 JONES MANAGER 2975 KING PRESIDENT 5000 MARTIN SALESMAN 1250 MILLER CLERK 1300 SCOTT ANALYST 3000 SMITH CLERK 800 TURNER SALESMAN 1500 WARD SALESMAN 1250

� Para listar os empregados em ordem descrescente de salário, por cargo.


SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE job in (‘ANALYST’, ‘SALESMAN’, 4 ‘MANAGER’) 5 ORDER BY job, sal desc;

ENAME JOB SAL SCOTT ANALYST 3000 FORD ANALYST 3000 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250 MARTIN SALESMAN 1250

� Pode-se usar expressões ou referências à posição de expressões no SELECT como argumentos do ORDER BY: SQL> SELECT ename, job, length(job), sal 2 FROM emp 3 WHERE job in (‘ANALYST’, ‘SALESMAN’, 4 ‘MANAGER’) 5 ORDER BY 3, sal + nvl(comm,0) desc;


III.11 - A CLÁUSULA WHERE

� A cláusula WHERE corresponde ao operador Restriction da Álgebra

Relacional. Ela contém a condição às quais efetuarão comparações nas linhas para serem exibidas.

� A cláusula WHERE quando utilizada, deve estar logo após a cláusula

FROM.

� A cláusula WHERE pode comparar valores às colunas, valores literais, expressões aritméticas ou funções utilizando os operadores mencionados.

� O comando abaixo mostra todos os empregados que trabalha no departamento 30.

SELECT * FROM emp WHERE deptno = 30;

� O comando abaixo seleciona o nome, cargo, salário e número do departamento dos empregados, exceto os vendedores que trabalham no departamento 30. SELECT ename, job, sal, deptno FROM emp WHERE not (job = ‘SALESMAN’ and deptno = 30);


III.12 - CLÁUSULA GROUP BY � A cláusula GROUP BY pode ser usada para dividir as linhas de uma tabela

em pequenos grupos. Funções de grupos podem ser utilizadas para retornar uma informação sumarizada de cada grupo.

SELECT job, avg(sal) FROM emp GROUP BY job;

� Com essa cláusula pode-se também agrupar linhas dentro de grupos. SELECT job, deptno, avg(sal) FROM emp GROUP BY deptno, job;

� O comando abaixo retorna o menor e maior salário de cada departamento.

SELECT deptno, min(sal), max(sal) FROM emp GROUP BY deptno;

� O comando abaixo retorna o menor e maior salário para os funcionários que tem como profissão CLERK, por departamento. SELECT deptno, min(sal), max(sal) FROM emp WHERE job = ‘CLERK’

GROUP BY deptno;

� Para saber quantos funcionários trabalham em cada departamento.



coluna do agrupamento todas as linhas SQL> SELECT deptno, count(*) 2 FROM emp função de grupo 3 GROUP BY deptno;

DEPTNO COUNT(*) 10 3 20 5 30 6

�� Nota: Omitir a cláusula “GROUP BY” significa agrupar toda a tabela.


III.13 - CLÁUSULA HAVING � Se quiser especificar quais grupos serão exibidos, utilize a cláusula

HAVING. SELECT deptno, avg(sal) FROM emp GROUP BY deptno HAVING count (*) > 3;

� É recomendado que a cláusula GROUP BY venha antes da cláusula HAVING.

� O comando abaixo retorna o menor e maior salário para os funcionários que tem como profissão CLERK por departamento, tendo o menor salário menor do que 1000. SELECT deptno, min(sal), max(sal) FROM emp WHERE job = ‘CLERK’

GROUP BY deptno HAVING min(sal) < 1000;


� Para saber a soma dos salários para os cargos onde a média de salários é maior que 1500: EMPNO ENAME JOB ... SAL ... 7788 SCOTT ANALYST ... 3000 ... AVG = 3000 7902 FORD ANALYST ... 3000 ... 7369 SMITH CLERK ... 800 ... AVG = 1037.5 7876 ADAMS CLERK ... 1100 ... 7934 MILLER CLERK ... 1300 ... 7900 JAMES CLERK ... 950 ... 7566 JONES MANAGER ... 2975 ... AVG = 2758.3

333 7782 CLARK MANAGER ... 2450 ... 7698 BLAKE MANAGER ... 2850 ... 7839 KING PRESIDENT ... 5000 ... AVG = 5000 7499 ALLEN SALESMAN ... 1600 ... AVG = 1400 7654 MARTIN SALESMAN ... 1250 ... 7844 TURNER SALESMAN ... 1500 ... 7521 WARD SALESMAN ... 1250 ... SQL> SELECT job, sum(sal), max(sal) 2 FROM emp 3 GROUP BY job 4 HAVING avg(sal) > 1500; selecione os grupos (“where” de grupo) função de grupo

JOB SUM(SAL) MAX(SAL) ANALYST 6000 3000 MANAGER 8725 2975 PRESIDENT 5000 5000

III.14 - OPERAÇÕES DE CONJUNTO


� Uma tabela pode ser encarada como um conjunto � Os elementos do conjunto são as linhas da tabela:

Tabela

Col. 1 Col. 2 A 1 B 1 D 2

(A,1) elementos (B,1) do conjunto (D,2)

Conjunto

� Existem três operações básicas de conjunto em SQL:

• União (UNION)

• Intersecção (INTERSECT)

• Diferença (MINUS)

� União TABELAS Col1 Col2 Col1 Col2


A 1 A 2 B 1 B 1 D 2 E 2 D 2 colunas com tipo igual select col1, col2 from tab1 union select col1, col2 from tab2

Col1 Col2 A 1 A 2 B 1 D 2 E 2


� Intersecção

TABELAS Col1 Col2 Col1 Col2 A 1 A 2 B 1 B 1 D 2 E 2 D 2 colunas com tipo igual select col1, col2 from tab1 intersect select col1, col2 from tab2

Col1 Col2 B 1 D 2


� Diferença

Col1 Col2 Col1 Col2 A 1 A 2 B 1 B 1 D 2 E 2 D 2 colunas com tipo igual select col1, col2 from tab1 minus select col1, col2 from tab2

Col1 Col2 A 1


III.15 - JUNÇÃO DE TABELAS

� Um Join é uma consulta que combina linhas de duas ou mais tabelas, visões ou snapshots. O Oracle7 executa um join quando múltiplas tabelas aparecem na cláusula FROM.

� A lista selecionada da coluna pode conter qualquer coluna de qualquer tabela. Caso haja colunas com o mesmo nome, você tem que especificar de qual tabela é a determinada coluna.

� A junção de tabelas corresponde à operação de produto cartesiano entre conjuntos: A,

B, C X 1,2 (A,1), (A,2),

(B,1), (B,2),

(C,1), (C,2) tab1 tab2

col1 col2 col3 col4 col5 A 3 B 2 E

select col1, col2, col3, col4, col5 from tab1, tab2;

col1 col2 col3 col4 col5 A 3 B 2 E

� Para saber o nome do departamento onde cada empregado trabalha:


produto cartesiano SQL> SELECT ename, dname 2 FROM emp, dept 3 WHRE emp.deptno = dept.deptno; critério de seleção da junção para diferenciar colunas de mesmo nome usa-se o nome da tabela como prefixo

ENAME DNAME SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING

ENAME DEPTNO EMP.DEPTNO DEPT.DEPTNO SMITH ACCOUNTING 20 10 ALLEN ACCOUNTING 30 10


WARD ACCOUNTING 30 10 JONES ACCOUNTING 20 10 MARTIN ACCOUNTING 30 10 BLAKE ACCOUNTING 30 10 CLARK ACCOUNTING 10 10 SCOTT ACCOUNTING 20 10 KING ACCOUNTING 10 10 TURNER ACCOUNTING 30 10 ADAMS ACCOUNTING 20 10 JAMES ACCOUNTING 30 10 FORD ACCOUNTING 20 10 MILLER ACCOUNTING 10 10 SMITH RESEARCH 20 20 ALLEN RESEARCH 30 20 WARD RESEARCH 30 20 JONES RESEARCH 20 20 MARTIN RESEARCH 30 20 BLAKE RESEARCH 30 20 CLARK RESEARCH 10 20 SCOTT RESEARCH 20 20 KING RESEARCH 10 20 TURNER RESEARCH 30 20 ADAMS RESEARCH 20 20 JAMES RESEARCH 30 20 FORD RESEARCH 20 20 MILLER RESEARCH 10 20 SMITH SALES 20 30 ALLEN SALES 30 30 WARD SALES 30 30 JONES SALES 20 30 MARTIN SALES 30 30 BLAKE SALES 30 30 CLARK SALES 10 30 SCOTT SALES 20 30 KING SALES 10 30 TURNER SALES 30 30 ADAMS SALES 20 30 JAMES SALES 30 30 FORD SALES 20 30 MILLER SALES 10 30 SMITH OPERATIONS 20 40 ALLEN OPERATIONS 30 40 WARD OPERATIONS 30 40 JONES OPERATIONS 20 40 MARTIN OPERATIONS 30 40 BLAKE OPERATIONS 30 40 CLARK OPERATIONS 10 40 SCOTT OPERATIONS 20 40 KING OPERATIONS 10 40 TURNER OPERATIONS 30 40 ADAMS OPERATIONS 20 40 JAMES OPERATIONS 30 40 FORD OPERATIONS 20 40 MILLER OPERATIONS 10 40

� Dada a tabela salgrade, que classifica faixas de salários:

GRADE LOSAL HISAL


1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

� Para saber a faixa salarial de todos os empregados: SQL> SELECT ename, sal, grade 2 FROM emp, salgrade 3 WHERE sal between losal and hisal; especifica um tipo de junção

ENAME SAL GRADE SMITH 800 1 ADAMS 1100 1 JAMES 950 1 WARD 1250 2 MARTIN 1250 2 MILLER 1300 2 ALLEN 1600 3 TURNER 1500 3 JONES 2975 4 BLAKE 2850 4 CLARK 2450 4 SCOTT 3000 4 FORD 3000 4 KING 5000 5

� Pode-se combinar diversas opções no mesmo comando SQL: SQL> SELECT dname, grade, count(*) 2 FROM emp, dept, salgrade


3 WHERE emp.deptno = dept.deptno and 4 sal between losal and hisal 5 GROUP BY dname, grade 6 HAVING sum(sal) > 1200 7 ORDER BY dname, count(*) DESC;

DNAME GRADE COUNT(*) ACCOUNTING 2 1 ACCOUNTING 4 1 ACCOUNTING 5 1 RESEARCH 4 3 RESEARCH 1 2 SALES 2 2 SALES 3 2 SALES 4 1

� O comando abaixo retorna o nome e cargo do empregado e o número do e nome do departamento SQL> SELECT ename, dept.deptno, dname 2 FROM emp, dept 3 WHERE emp.deptno = dept.deptno;

� O comando abaixo retorna o nome, cargo e número do departamento dos empregados e nome do departamento de todos os funcionários CLERKS. SQL> SELECT ename, job, dept.deptno, dname 2 FROM emp, dept 3 WHERE emp.deptno = dept.deptno 4 AND job = ‘CLERK’;

III.16 - EXERCÍCIOS


1. Consulte o nome, o local do departamento, o salário por hora (assumindo 160 horas/mês) e o total mensal (salário + comissão) dos vendedores da tabela EMP. Salve seu exercício no arquivo “ex31.sql”.

2. Carregue o conteúdo do arquivo “ex31.sql” no buffer corrente e modifique-o

de forma a ordenar o resultado em ordem descrescente do salário por hora. Salve seu exercício novamente, no mesmo arquivo.

3. Descubra o menor salário de todos os empregados. 4. Obtenha a soma dos salários dos empregados que não trabalham nos

departamentos 10 ou 30. 5. Obtenha o nome e a média salarial dos departamentos (exceto o 10), cujo

maior salário seja inferior a 4000. 6. Obtenha a média de salário para cada faixa salarial.


IV - CONSULTAS AVANÇADAS

IV.1 - CONTEÚDO


1. Pseudo - colunas

2. Junção externa

3. Auto-Junção

4. Consultas encaixadas

5. Consultas hierárquicas

IV.2 - PSEUDO-COLUNAS

� Pseudo-colunas são colunas que podem ser consultadas em qualquer tabela, como uma coluna normal, mas não podem ser alteradas:


T Nome Descrição N nome_seq.CURRVAL Valor corrente da sequência nome_seq N nome_seq.NEXTVAL Próximo valor da sequência nome_seq N LEVEL Usada no comando SELCT …

CONNECT BY; é igual a 1 para o nó raíz de uma árvore, 2 para seus filhos e assim por diante

- NULL Valor nulo R ROWID Identificação interna de uma linha N ROWNUM Número que indica a ordem de

seqüência da linha selecionada em uma consulta

SQL> SELECT rownum, rowid 2 FROM dual; tabela com uma única linha e uma única coluna

ROWNUM ROWID 1 00000984.0001.0001

IV.3 - JUNÇÃO EXTERNA

� Para listar o nome de todos departamentos e seus respectivos empregados:


SQL> SELECT dept.deptno, dname, empno, ename 2 FROM dept, emp 3 WHERE dept.deptno = emp.deptno order by dept.deptno;

DEPTNO DNAME EMPNO ENAME 10 ACCOUNTING 7782 CLARK 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7934 MILLER 20 RESEARCH 7369 SMITH 20 RESEARCH 7876 ADAMS 20 RESEARCH 7902 FORD 20 RESEARCH 7788 SCOTT 20 RESEARCH 7566 JONES 30 SALES 7499 ALLEN 30 SALES 7698 BLAKE 30 SALES 7654 MARTIN 30 SALES 7900 JAMES 30 SALES 7844 TURNER 30 SALES 7521 WARD

PROBLEMA: Departamento 40 nao aparece,

pois nao tem empregados

ENAME DEPTNO EMP.DEPTNO DEPT.DEPTNO SMITH ACCOUNTING 20 10 ALLEN ACCOUNTING 30 10 WARD ACCOUNTING 30 10 JONES ACCOUNTING 20 10 MARTIN ACCOUNTING 30 10 BLAKE ACCOUNTING 30 10 CLARK ACCOUNTING 10 10 SCOTT ACCOUNTING 20 10


KING ACCOUNTING 10 10 TURNER ACCOUNTING 30 10 ADAMS ACCOUNTING 20 10 JAMES ACCOUNTING 30 10 FORD ACCOUNTING 20 10 MILLER ACCOUNTING 10 10 SMITH RESEARCH 20 20 ALLEN RESEARCH 30 20 WARD RESEARCH 30 20 JONES RESEARCH 20 20 MARTIN RESEARCH 30 20 BLAKE RESEARCH 30 20 CLARK RESEARCH 10 20 SCOTT RESEARCH 20 20 KING RESEARCH 10 20 TURNER RESEARCH 30 20 ADAMS RESEARCH 20 20 JAMES RESEARCH 30 20 FORD RESEARCH 20 20 MILLER RESEARCH 10 20 SMITH SALES 20 30 ALLEN SALES 30 30 WARD SALES 30 30 JONES SALES 20 30 MARTIN SALES 30 30 BLAKE SALES 30 30 CLARK SALES 10 30 SCOTT SALES 20 30 KING SALES 10 30 TURNER SALES 30 30 ADAMS SALES 20 30 JAMES SALES 30 30 FORD SALES 20 30 MILLER SALES 10 30 SMITH OPERATIONS 20 40 ALLEN OPERATIONS 30 40 WARD OPERATIONS 30 40 JONES OPERATIONS 20 40 MARTIN OPERATIONS 30 40 BLAKE OPERATIONS 30 40 CLARK OPERATIONS 10 40 SCOTT OPERATIONS 20 40 KING OPERATIONS 10 40 TURNER OPERATIONS 30 40 ADAMS OPERATIONS 20 40 JAMES OPERATIONS 30 40 FORD OPERATIONS 20 40 MILLER OPERATIONS 10 40 Não existem empregados no departamento 40

�� Solução: fazer de conta que existe uma linha fictícia, totalmente nula, na tabela de empregados, para que ela seja associada à linha do departamento 40 (junção externa):

SQL> SELECT dept.deptno, dname, empno, ename 2 FROM dept, emp 3 WHERE dept.deptno = emp.deptno(+);


operador opcional para junção externa

DEPTNO DNAME EMPNO ENAME 10 ACCOUNTING 7782 CLARK 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7934 MILLER 20 RESEARCH 7369 SMITH 20 RESEARCH 7876 ADAMS 20 RESEARCH 7902 FORD 20 RESEARCH 7788 SCOTT 20 RESEARCH 7566 JONES 30 SALES 7499 ALLEN 30 SALES 7698 BLAKE 30 SALES 7654 MARTIN 30 SALES 7900 JAMES 30 SALES 7844 TURNER 30 SALES 7521 WARD 40 OPERATIONS

� Para saber os departamentos sem empregados: SQL> SELECT dept.deptno, dname, loc 2 FROM dept, emp 3 WHERE dept.deptno = emp.deptno(+) and empno is null;

DEPTNO DNAME LOC 40 OPERATIONS BOSTON

IV.4 - AUTO - JUNÇÃO

� As tabelas envolvidas numa junção não precisam ser distintas. � Para descobrir quais gerentes estão ganhando menos que o dobro do

salário de algum de seus subordinados:


pseudônimos para tabelas SQL> SELECT ger.ename gerente, ger.sal, max(2*trab.sal) 2 FROM emp trab, emp ger 3 WHERE trab.mgr = ger.empno 4 GROUP BY ger.ename, ger.sal 5 HAVING max(2*trab.sal) > ger.sal;

GERENTE SAL MAX(2*TRAB.SAL) BLAKE 2850 3200 CLARK 2450 2600 JONES 2975 6000 KING 5000 5950

. SQL> SELECT e1.ename||’TRABALHA PARA’|| e2.ename “EMPREGADOS” 2 FROM emp e1, emp e2 where 3 e1.mgr = e2.empno;

IV.5 - CONSULTAS ENCAIXADAS

� Uma subquery é um comando SELECT que está contido em um outro comando SELECT.

� O comando que contém a subconsulta é chamado de comando pai. As linhas retornadas pela subconsulta são usadas pelo comando pai.


� Subqueries podem:

• Retornar uma ou mais linhas. • Retornar uma ou mais colunas. • Utilizar funções de grupos ou GROUP BY. • Agrupar tabelas.

� Uma sub-consulta pode fazer referências a colunas do comando no qual ela está encaixada. SELECT coluna1, coluna2,...

FROM tabela WHERE coluna = ( SELECT coluna FROM tabela WHERE condicao )

� Para saber quais empregados ganham mais que a média dos salários: SQL> SELECT empno, ename, sal parenteses delimitam a 2 FROM emp sub-consulta 3 WHERE sal > (select avg(sal) from emp);

resultado desta sub-consulta é um único valor

� Para saber quais funcionários trabalham com o SCOTT. SQL> SELECT ename, deptno 2 FROM emp 3 WHERE deptno = (SELECT deptno FROM emp where ename = ‘SCOTT’);

� Para saber os departamentos que não tem empregados:


operador de exclusão de conjunto SQL> SELECT deptno, dname elimina os valores duplicados 2 FROM dept 3 WHERE deptno not in (SELECT distinct deptno FROM emp); esta sub-consulta retorna um conjunto de valores

DEPTNO DNAME 40 OPERATONS

�� Encontre o funcionário com maior salário. SELECT ename, job FROM EMP WHERE sal = ( SELECT max(sal) FROM emp);

�� Encontre os funcionários com maior salário em cada departamento. SELECT ename, job, deptno FROM emp WHERE sal IN (SELECT max(sal) FROM emp GROUP BY deptno);

� Para recuperar os empregados que ganham mais que a média de salário

do seu departamento: SQL> SELECT deptno,ename, sal nesse caso é necessário o 2 FROM emp x pseudônimo para a tabela 3 WHERE sal > (SELECT avg(sal) FROM emp y 4 where x.deptno = y.deptno ) 5 ORDER BY deptno;


DEPTNO ENAME SAL 10 KING 5000 20 JONES 2975 20 SCOTT 3000 20 FORD 3000 30 ALLEN 1600 30 BLAKE 2850

� Para saber se o resultado de uma sub-consulta é vazio ou não equivalente ao IS NOT NULL para colunas: SQL > SELECT ename, job, deptno só importa se uma linha 2 FROM emp x existe ou não 3 WHERE exists (SELECT ‘x’ from emp y 4 WHERE x.empno = y.mgr)

� Sub-consultas também podem ser usadas nos comandos de manipulação de dados (INSERT, UPDATE, DELETE).

� Para eliminar os empregados mais novos de cada departamento:

SQL> DELETE FROM emp e 2 WHERE hiredate = (SELECT max(hiredate) FROM emp 3 WHERE deptno = e.deptno);

� Para que todos os empregados mais novos de cada cargo ganhem o melhor salário e a média das comissões de seu cargo: SQL> UPDATE emp e 2 SET (sal,comm) = (SELECT max(sal), avg(comm) FROM emp 3 WHERE job = e.job) 4 WHERE hiredate = (SELECT max(hiredate) FROM emp 5 WHERE job = e.job);

IV.6 - CONSULTAS HIERÁRQUICAS

� Oracle permite recuperar informações estruturadas de forma hierárquica (“em árvore”):

� Você pode selecionar linhas em uma ordem hierárquica usando as seguintes cláusulas

START WITH você pode especificar linha(s) pai de uma hierarquia. CONNECT BY você pode especificar o relacionamento entre linhas pais e


linhas filhas de uma hierarquia. WHERE você pode restringir as linhas retornadas pela consulta sem

afetar outras linhas da hiearquia.

� A cláusula START WITH identifica a(s) linha(s) a ser(em) usada(s) como raiz(es) de uma consulta hierárquica. Esta cláusula especifica uma condição que a raiz tem que satisfazer. Se você omitir essa cláusula, o Oracle7 usa todas as linhas na tabela como linhas raiz.

� A cláusula CONNECT BY especifica o relacionamento entre linhas pais e

filhas na consulta hierárquica. Esta cláusula contém uma condição que define este relacionamento. Alguma parte da condição tem que usar o operador PRIOR para se referir a linha pai.

� Para encontrar o filho de uma linha pai, o Oracle7 executa a expressão

PRIOR para a linha pai e a outra expressão para cada linha na tabela. Linhas para o qual a condição é verdadeira são filhas.

� Se a cláusula CONNECT BY resultar em um loop na hierarquia, o Oracle7

retorna um erro. Um loop ocorre se uma linha é pai e filha de uma outra linha

� A seguinte cláusula CONNECT BY define um relacionamento hierárquico no qual o valor EMPNO da linha pai seja igual ao valor do MGR da linha filha.

CONNECT BY PRIOR empno = mgr

75 / PRESIDENTE 110 / DIRETOR 230 / SECRETÁRIA 189 / DIRETOR


103 / ANALISTA 276 / ANALISTA 201 / VENDEDOR 208/VENDEDOR


� Para saber todos os superiores do SCOTT:

SQL> SELECT empno, ename, job 2 FROM emp indica o sentido da consulta: 3 WHERE ename <> ‘SCOTT’ do empregado para o chefe 4 CONNECT BY empno = PRIOR mgr 5 START WITH ename = ‘SCOTT’; especifica a conexão da indica o começo da coluna consulta

MPNO ENAME JOB 7566 JONES MANAGER 7839 KING PRESIDENT

� Para saber o nome do Presidente e de seus subordinados, exceto aqueles que são também subordinados de JONES: SQL> SELECT empno, ename, job 2 FROM emp 3 CONNECT BY PRIOR empno = mgr AND mgr <> 7566 4 START WITH job = ‘PRESIDENT’; Elimina toda uma sub-árvore


EMPNO ENAME JOB 7839 KING PRESIDENT 7566 JONES MANAGER 7698 BLAKE MANAGER 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7654 MARTIN SALESMAN 7844 TURNER SALESMAN 7900 JAMES CLERK 7782 CLARK MANAGER 7934 MILLER CLERK

� Podemos usar uma pseudo-coluna LEVEL para saber o nível de cada registro na hierarquia: SQL> SELECT level, lpad(‘ ’,2*level ) | | ename nome 2 FROM emp 3 CONNECT BY PRIOR empno = mgr 4 START WITH job = ‘PRESIDENT’;

LEVEL NOME 1 KING


2 JONES 3 SCOTT 4 ADAMS 3 FORD 4 SMITH 2 BLAKE 3 ALLEN 3 WARD 3 MARTIN 3 TURNER 3 JAMES 2 CLARK 3 MILLER

IV.7 - EXERCÍCIOS

1. Consulte os nomes dos funcionários que são chefes de alguém. 2. Consulte o nome dos chefes (MOME_CHEFE) dos funcionários (ENAME)

da tabela EMP, ordenados por NOME_CHEF. Caso algum funcionário não possua chefe, mostre a cadeia de caracteres ‘___________’ em NOME_CHEFE.

3. Consulte os nomes e salários dos funcionários que estão na mesma faixa

salarial que seus chefes.


4. Aumente o salário em 20% para os empregados que sejam os mais

antigos de seus departamentos. 5. Promova a diretor os empregados que recebem mais que a média dos

salários de seus cargos. 6. Desfaça as duas operações anteriores. 7. Faça com que os empregados passem a ganhar o maior salário dentre os

companheiros da mesma faixa. 8. Desfaça a operação anterior.


V - SQL*PLUS

V.1 - SQL*PLUS

� O SQL*PLUS é um programa escrito pela ORACLE Corporation o qual

define um ambiente para que comandos SQL possam ser entrados e executados. Além disso, o SQL*PLUS pode:

• Formatar resultados. • Configurar o ambiente. • Editar e armazenar sentenças SQL.

� Uma vez conectado ao sistema operacional, existem três maneiras para acessar o SQL*PLUS:


1. SQLPLUS A seguinte mensagem aparecerá: SQL*PLUS: Version 3.0.9 - Thu Jun 18 12:14:00 1992 Copyright © 1991, Oracle Corporation, California, USA. All rights reserved. Enter Username : Enter Password : Entrer com o seu usuário e senha e tecle ENTER. Se a operação foi efetuada com sucesso aparecerá o prompt: SQL> 2. SQLPLUS nome_do_usuário 3. SQLPLUS nome_do_usuário/senha


V.2 - EDIÇÃO DE COMANDOS UTILIZANDO SQL*PLUS � Quando um comando SQL é executado, ele é armazenado numa parte da

memória chamada SQL buffer, e permanece por lá até que um novo comando seja executado.

ponto-e-vírgula ( ; ) Indica que a senteça do comando SQL terminou. � Enquanto o comando SQL residir no buffer, exitem alguns comandos para

manipulção deste no buffer como:

APPEND texto (A) Adiciona um texto ao final da linha corrente. CHANGE/velho/novo (C) Altera um texto na linha corrente para um texto

novo. CLEAR BUFFER (CL) Remove todas as linhas do SQL buffer. DEL Remove a linha corrente. INPUT (I) Insere linhas após a linha corrente. LIST (L) Exibe todas as linhas contida no SQL buffer. LIST n Exibe a linha n do SQL buffer. LIST m n Exibe o intervalo de linhas entre m e n. RUN ( R ) Exibe e executa o comando que está no SQL

buffer. / Executa o comando que está no SQL buffer. SAVE nome_do_arquivo Armazena o comando que está no SQL buffer. GET nome_do_arquivo Recupera para o SQL buffer o comando

previamente armazenado em um arquivo. START nome_do_arquivo

Executa um comando armazenado em um arquivo.

ED nome_do_arquivo Utiliza o editor definido como default para editar um arquivo.

SPOOL nome_do_arquivo

Armazena todos os próximos comandos ou Resultados de comandos no arquivo definido.

SPOOL OFF | OUT - OFF Fecha os arquivos que estão sendo gerados pelo SPOOL e OUT imprime o arquivo gerado pelo SPOOL.

DESCRIBE tabela (DESC) Exibe a estrutura da tabela especificada. HELP Chama o programa de ajuda do Oracle. $O/S comando | HOST

Executa um comando do sistema operacional.

CONNECT ( CONN ) nome_do_usuário/senha

Executa novamente o processo de entrada no SQL*PLUS com o usuário especificado.

EXIT Sai do SQL*PLUS.

V.3 - O COMANDO COLUMN


� O comando COLUMN estabelece as opções de exibição de cada coluna. COLUMN nome_da_coluna / alias OPCAO � Quando este comando é executado, ele fica válido enquanto a sessão

SQL*Plus estiver ativa. � Para saber qual é a opção corrente para uma determinada coluna: COL nome_da_coluna / alias


�� Opções:

WRAP/TRUNC Permite que possa definir as ações a serem tomadas se um campo exceder o tamanho especificado. Ele pode continuar na outra linha(WRAP) ou exibir somente os caracteres possíveis (TRUNC).

WORD_WRAPPED Se a palavra não couber no espaço especificado, movimente-a inteiramente para outra linha.

CLEAR Remove os formatos da coluna já definidos. HEADING Especifica o cabeçalho para uma coluna. JUSTIFY (LEFT CENTER HIGHT)

Permite especificar a justificação de uma coluna. Por default, colunas do tipo caracter e data são justificadas à esquerda, e do tipo número são justificadas à direita.

LIKE nome_da_coluna Copia as especificações de uma coluna para uma outra coluna.

NEWLINE Começa uma nova linha antes da coluna ser exibida.

NULL string Troca todos os nulos pela string especificada. PRINT/NOPRINT Define se a coluna será exibida ou não.

COLUMN deptno FORMAT 099 HEADING ‘Dept.’ COLUMN job FORMAT A9 HEADING ‘Cargo’ JUSTIFY RIGHT COLUMN empno FORMAT 9999 HEADING ‘Codigo’ COLUMN sal FORMAT 99,990.99 HEADING ‘Salario’ COLUMN comm FORMAT 99,990.99 HEADING ‘Comissao’ NULL ‘Sem Comissao’


V.4 - OS COMANDOS TTITLE E BTITLE �� Os Comandos TTITLE e BTITLE são usados para produzir os títulos das

páginas.

TTITLE ‘texto’

Exibe no início da página do lado esquerdo a data, o número da página e à esquerda o título especificado no texto.

BTITLE ‘texto’

texto’ Exibe o texto definido centralizado no rodapé da página.

� Para visualizar os títulos corrente basta entrar com o comando TTITTLE ou

BTITLE. � Para cancelar a exibição dos títulos basta entrar com TTITLE OFF ou

BTITLE OFF. TTITLE ‘Relatorio de Salarios e Comissoes’ BTITLE ‘Salarios e Beneficios’


V.5 - SQL*PLUS REPORT � É utilizado para formatar saída de comandos do SQL*Plus. � Comandos SQL X Comandos SQL*Plus

• SQL - acesso ao banco de dados; • SQL*Plus - formato de apresentação dos resultados.

� Para produzir um relatório com o número, nome e salário de todos os

funcionários: SQL> column empno heading NUMERO comandos SQL*Plus SQL> column ename heading NOME não precisam de “;” SQL> column sal format 999999.99 - � heading SALARIO para continuar um SQL> ttitle center ‘Empregados da BAC’ comando use o SQL> select empno, ename, sal from emp; caractere “-“

Empregados da B A C NUMERO NOME SALARIO 7369 SMITH 800.00 7499 ALLEN 1600.00 7521 WARD 1250.00 7566 JONES 2975.00 7654 MARTIN 1250.00 7698 BLAKE 2850.00 7782 CLARK 2450.00 7788 SCOTT 3000.00 7839 KING 5000.00 7844 TURNER 1500.00 7876 ADAMS 1100.00 7900 JAMES 950.00 7902 FORD 3500.00 7934 MILLER 1300.00


V.6 - COMANDOS SET

� Os comandos SET são utilizados para controlar o ambiente corrente que o SQL*Plus está sendo operado. Os comandos geralmente atribuem valores a variáveis de sistema habilitando ou sensibilitando as mesmas.

ECHO(OFF ON) ECHO ON exibe os comandos na tela enquanto são

executados. FEEDBACK(6 n OFF ON) Quando informado o número n, o SQL*Plus

exibirá o número n de linhas. ON ou OFF faz com que exiba ou não o resultado.

HEADING (OFF ON) ON faz com que exiba os nomes das colunas. LINESIZE (80 n) Especifica o número de caracteres que serão

exibidos em cada linha exibida. O valor máximo de cada linha é 500.

NEWPAGE(1 n) Especifica o número de linhas brancas que serão exibidas entre o rodapé de cada página e o cabeçalho a página seguinte.

NUMFORMAT texto Define o formato default para exibir números. O texto deve ser um formato de número.

NUMWIDTH(10 n) Especifica o tamanho padrão para exibir números.

PAGESIZE(24 n) Define o número de linhas por página. PAUSE(OFF ON texto) ON faz com que o SQL*Plus espere antes de

exibir a próxima página. VERIFY(OFF ON) ON faz com que o SQL*Plus exiba o texto da

linha de comando depois que substituiu uma variável pelo valor especificado.

TIMING(OFF ON) ON faz com que o SQL*Plus exiba estatísticas de cada comando SQL.

SPACE(1 n) Especifica o número de espaços exibidos entre colunas.

TERMOUT(OFF ON) OFF não exibe os resultados gerados por arquivos de comandos. Estes resultados podem ser armazenado em um arquivo.

SQLCASE(MIXED LOWER/UPPER)

Convertem os caracteres antes da execução para minúsculos ou maiúsculas. MIXED faz com que não se altere.

� Para saber a situação de cada variável, deve-se utilizar o comando SHOW. �� Exemplo: SHOW PAGESIZE


V.7- CLÁUSULAS DE NUMERAÇÃO DE PÁGINA E OUTRAS VARIÁVEIS DO SISTEMA

SQL.PNO

Variável do sistema que contém o número da página corrente do relatório.

SQL.LNO

Variável do sistema que contém o número da linha corrente da página.

SQL.USER

Variável do sistema que contém o nome do usuário.

SQL.SQLCODE

Variável do sistema que contém o mais recente código de erro SQL.

TTITLE LEFT FORMAT 0999 ‘Pagina : ‘ SQL.PNO - RIGHT ‘Relatório Financeiro’ SKIP 2 - CENTER ‘Vendas e Comissoes’ SKIP - CENTER ‘------------------------------’ SKIP2


V.8 - O COMANDO DEFINE � Um valor pode ser atribuído para uma variável utilizando o comando do

SQL*Plus DEFINE. O valor definido pode ser utilizado num comando SELECT ou num arquivo de comandos. Para esvaziar uma variável deve-se utilizar o comando UNDEFINE.

�� Exemplo: Atribuir a uma variável uma expressão aritmética.

SQL> DEFINE remuneracao = ‘sal*12+NVL(comm,0)’ SQL> SELECT ename, job, &remuneracao 2 FROM emp 3 ORDER BY &remuneracao SQL> UNDEFINE remuneracao

� Utilizando variáveis em arquivos de comandos

SELECT ename,job, sal FROM emp

WHERE job = ‘&1’ SQL> SAVE ARQ1 SQL> START ARQ1 VENDEDOR

� As variáveis são definidas com o & e um numeral(1-9) que indica a posição do parâmetro no comando.


V.9 - O COMANDO ACCEPT

� O comando ACCEPT permite que uma variável possa ser criada e um

valor, entrando em tempo de execução, para que possa ser armazenado. Esta variável pode assim ser utilizada por um comando SQL. Existem alguns benefícios para definir variáveis utilizando o comando ACCEPT:

• O tipo do dados pode ser verificado. • Pode-se colocar uma mensagem explicativa no prompt. • Os valores de respostas podem não ser exibidos.

ACCEPT VARIAVEL NUMBER/VARCHAR2 PROMPT/NOPROMPT ‘TEXTO’ HIDE

Texto

Exibe o texto em tempo de execução quando especificado.

HIDE

Não exibe os caracteres digitados como resposta.

SQL> ACCEPT salario NUMBER PROMPT ‘Salario Referencia :’ Salario Referencia : 1500

SQL>ACCEPT senha VARCHAR2 PROMPT ‘Senha :’ HIDE Senha :


VI - ESTRUTURA DE DADOS


VI.1 - ESTRUTURA DE DADOS ORACLE

� Tabelas podem ser criadas a qualquer hora, mesmo que o Banco de

Dados esteja sendo utilizado. � O tamanho dos dados são variáveis, somente são armazenados os

caracteres e números. Os espaços em branco não são armazenados. � Não há necessidade de especificar o tamanho de alguma tabela. Isto é

definido pelo espaço determinado para o Banco de Dados. Mas é importante sempre verificar o tamanho das tabelas.

� As estruturas das tabelas podem ser modificas quando estão em uso.

VI.2 - CRIANDO UMA TABELA

� O nome escolhido para a tabela deve sempre seguir as regras básicas de

nomenclatura dos objetos do Banco de Dados ORACLE.

1. O nome sempre deve começar com letra (A - Z) 2. Pode-se utilizar letras, números e caracteres especiais 3. O nome da tabela não é sensível a letras maiúsculas e minúsculas 4. Deve ter no máximo 30 caracteres 5. Não pode existir duas tabelas, visões ou sinônimos para um mesmo

usuário com o mesmo nome 6. O nome da tabela não pode ser uma palavra reservada do SQL


VI.3 - DIRETRIZES PARA CRIAÇÃO DE NOMES DE TABELAS

� Utilize nomes auto-descritivos para tabelas, colunas, índices ou outros objetos.

� Cuidado com as abreviações e o uso de singular e plural nos nomes das

tabelas. � Crie padrões para nomear os objetos. Ex.: tabela de notas fiscais -

nota_fiscal. � Utilize os mesmos nomes definidos para as entidade-atributos para

descreverem a tabela-coluna relacionada.


VI.4 - TIPOS DE COLUNAS

� Quando criar uma tabela, você deve especificar os tipos das colunas. Os

mais utilizados são:

CHAR (n)

Valores tipo CHAR consistem em palavras compostas por letras minúsculas e maiúsculas, números e caracteres especiais. O número de caracteres é especificado por n e não pode ser maior que 255. Não usa armazenamento dinâmico.

VARCHAR2(n)

São valores tipo CHAR com no máximo 2000 posições. Usa armazenamento dinâmico.

NUMBER (n,m)

São valores numéricos (0 - 9) e caracteres de sinal (+ -) e ponto decimal. n são números inteiros e m são decimais. Usa armazenamento dinâmico.

DATE

São valores tipo data. Ex.: December 31, 4712 BC. Usa 7 bytes.

LONG

Similar ao tipo CHAR, mas aceita a quantidade de caracteres acima de 65.535. Só pode ser definida uma coluna LONG por tabela.


VI.5 - A OPÇÃO NULL E NOT NULL

NULL

Esta opção define que a coluna pode possuir valores nulos. Esta opção é default e pode ser omitida.

NOT NULL

Esta opção assegura que a coluna sempre tenha valores diferentes de nulo. Se tentar incluir uma linha com a coluna que tem essa opção sem valor, o ORACLE enviará uma mensagem de erro.


VI.6 - CLÁUSULA CONSTRAINT � O Banco de Dados Oracle suporta documentação para integridade

armazenando informações sobre verificação de integridade no dicionário de dados. Uma Constraint de integridade é uma regra que define um relacionamento entre tabelas de um banco de dados. Por exemplo, uma constraint de integridade pode definir que um funcionário não esteja contido em dois ou mais departamentos.

� Uma constraint pode ser definida para tabelas e colunas e são definidas no

comando CREATE ou ALTER TABLE. � O objetivo de uma constraint é definir um intervalo de valores válidos. Para

que os comandos INSERT, UPDATE e DELETE sejam executados com sucesso, devem obedecer as regras de constraint estabelecidas.

� A utilização de constraint possibilita definir as seguintes regras para uma

ou mais colunas:

• Restringir uma coluna ou mais a valores diferentes de nulo (NOT NULL).

• Definir que o valor da coluna seja único na tabela (UNIQUE) • Identificar a coluna como coluna chave da tabela (PRIMARY KEY) • Estabelecer restrições a chaves estrangeiras (FOREIGN KEY) • Verificar se o valor de uma ou mais colunas estão de acordo com uma

expressão (CHECK).

� Existem dois tipos de constraints: • Constraints de tabela • Constraints de coluna

� Estes tipos são idênticos, a não ser que as constraint de coluna refereciem

a uma coluna enquanto a de tabela refereciem a uma ou mais colunas da tabela. � Definição da constraint da tabela:

CREATE TABLE assignment ( projeto number (4) , funcionario number(4), PRYMARY KEY ( projeto, funcionario);


� Definição da constraint da coluna: CREATE TABLE emp ( EMPNO NUMBER (4) NOT NULL CONSTRAINT EMPNO

PRIMARY KEY, ENAME VARCHAR(10) CHECK (ENAME = UPPER (ENAME) ) , JOB VARCHAR(10) , MGR NUMBER (4) CONSTRAINT EMP_MGR REFERENCES EMP (EMPNO), HIREDATE DATE CHECK (HIREDATE <= SYSDATE), SEG_NUM VARCHAR(12) UNIQUE CONTRAINT EMP_SEG , SAL NUMBER (7,2) , COMM NUMBER (7,2) , DEPTNO NUMBER (2) NOT NULL CONSTRAINT EMP_DEPT REFERENCES DEPT(DEPTNO) );


VI.7 - PARÂMETROS DA CONSTRAINT

CONSTRAINT nome_da_constraint

Define o nome da constraint. Este parâmetro é opcional. Se não definir o nome da constraint, ela receberá um nome default no formato SYS_Cn, onde n é um inteiro e único identificador da constraint.

NULL/NOT NULL

Define se a coluna pode ou não conter valores nulos. O default é que pode conter ( NULL).

UNIQUE

Certifica que cada linha da tabela terá um valor diferente para a coluna. Esta coluna tem que ser definida como NOT NULL e não pode ser a chave primária.

PRIMARY KEY

Define que a coluna é a única identificação de cada linha. Esta coluna tem que ser NOT NULL e não pode ter o UNIQUE constraint.

FOREIGN KEY ( coluna ...)/ REFERENCES usuário.tabela(colunas)

Identifica que essa é uma chave estrangeira da tabela do usuário definida. Deve sempre estar referenciada a uma tabela e não a uma visão.

CHECK

Define a condição que deve ser satisfeita para que a coluna possa ser incluída ou alterada numa linha da tabela.


VI.8 - CREATE TABLE

� O comando CREATE TABLE é usado para criar novas tabelas no banco de dados.

� Sintaxe: CREATE TABLE table schema. , ( coluna tipo ) DEFAULT expr column_constraint table_constraint

AS subquery

Obs: Sintaxe abreviada. Onde: Schema É o schema contendo a tabela. Se você

omitir o schema, o Oracle cria a tabela como seu próprio dono.

Table É o nome da tabela a ser criada. Coluna Especifica o nome de uma coluna da tabela.

Uma tabela não pode ter mais do que 254 colunas.

Tipo É o tipo da coluna. Default Especifica um valor a ser atribuído para a

coluna se um comando INSERT subsequente for omitido para o valor da coluna. O tipo da expressão tem que ser o mesmo tipo da coluna.

Column_constraint Define uma integridade de constraint como parte da definição da coluna.

Table_constraint Define uma integridade de constraint como parte da definição da tabela.

As subquery Insere as linhas retornadas por uma subconsulta na tabela que será criada.

�� Criando a tabela DEPT.


CREATE TABLE dept (deptno number (2) NOT NULL, dname char (12), loc varchar2 (12)) ;

�� Criando a tabela EMP.

SQL> CREATE TABLE emp 2 (empno number(4) PRIMARY KEY, 3 ename char(10) NOT NULL, 4 job char(10), 5 mgr number(4) REFERENCES emp(empno), 6 hiredate data DEFAULT sysdate 7 CHECK (hiredate <= sysdate), 8 sal number(10,2), 9 comm number(10,2) CHECK(comm > 0), 10 deptno number(2) REFERENCES

dept(deptno));

� Verificando a estrutura criada na tabela DESCRIBE EMP;

Name Null Type --------------------------------------------- --------------- ------ EMPNO NOT NULL NUMBER (4) ENAME CHAR (10) JOB CHAR (9) MGR NUMBER (4) HIREDATE DATE SAL NUMBER (7,2) COMM NUMBER (7,2) DEPTNO NOT NULL NUMBER (2)


VI.9 - CRIAR UMA TABELA COM BASE EM UMA JÁ EXISTENTE

� A tabela será criada com as colunas especificadas e as linhas resultantes do comando SELECT serão inseridas na nova tabela.

� As especificações das colunas podem ser omitidas se no comando

SELECT foram especificadas. � O número de colunas no comando SELECT deve ser igual ao do comando

CREATE TABLE. CREATE TABLE dept10 AS SELECT empno, ename, job, sal FROM emp WHERE deptno = 10; Table created.

• Exemplo: SQL> CREATE TABLE sal_medio AS 2 SELECT dname, avg(sal) “Media_Salarial” 3 FROM dept, emp 4 WHERE emp.deptno = dept.deptno 5 GROUP BY dname; SQL> SELECT * FROM sal_medio;

DNAME Media_Salario ACCOUNTING 2916.6667 RESEARCH 2175 SALES 1566.6667


VI.10 - ALTERAÇÃO DE UMA TABELA

� Restrições para a alteração da estrutura de uma tabela:

• Só é possível diminuir o tamanho de uma coluna, ou mudar seu tipo, se todos os valores desta coluna forem nulos;

• Só é possível alterar o tipo de uma coluna para “NOT NULL” se

nenhum dos seus valores for nulo; • Não é possível remover uma coluna de uma tabela com o comando

ALTER TABLE. Para conseguir este efeito deve-se criar outra tabela sem a coluna a ser removida. Por exemplo, para apagar a coluna DIRETOR:

SQL> CREATE TABLE dep_temp AS 2 SELECT deptno, dname, loc 3 FROM dept; SQL> DROP TABLE dept; SQL> RENAME dep_temp TO dept;


VI.11 - ALTERANDO UMA TABELA

� O comando ALTER TABLE serve para alterar a estrutura de uma tabela.

� O parâmetro ADD deve ser utilizado para adicionar uma coluna e/ou constraint para uma tabela existente. ALTER TABLE table schema. , ADD ( column_datatype )

DEFAULT exp column_constraint table_constraint , MODIFY ( column ) datatype DEFAULT expr column_contraint Obs: Sintaxe abreviada.

� Onde:

Table é o nome da tabela a ser alterada. ADD adiciona uma coluna ou integridade de

constraint. MODIFY modifica a definição de uma coluna existente. Column é o nome da coluna a ser alterada ou

modificada. Datatype especifica um tipo para a coluna ou um novo

tipo para uma coluna existente. DEFAULT especifica um valor default para uma nova

coluna ou um novo valor default para uma coluna existente. O Oracle assume esse valor para a coluna se um comando INSERT subsequence omitir um valor para a coluna. O tipo de um valor default tem que ser igual ao tipo especificado para a coluna. A expressão DEFAULT não pode conter referências a outras colunas.

Column_constraint adiciona ou remove uma constraint NOT NULL para ou de uma coluna existente.

Table_constraint adiciona uma integridade de constraint para a tabela.

� O comando abaixo adiciona a coluna NOME_DA_ESPOSA na tabela EMP.


ALTER TABLE EMP ADD( nome_da_esposa CHAR (10)); � O comando abaixo adiciona a constraint CHECK na coluna SAL da tabela EMP. ALTER TABLE EMP ADD(CHECK(sal <= 5000)); � O comando abaixo modifica a coluna ENAME da tabela EMP para o tamanho de

25 posições. ALTER TABLE EMP MODIFY (ename char(25));


VI.12 - O COMANDO RENAME � O comando RENAME server para alterar o nome dos objetos de uma

banco de dados.

RENAME OLD TO NEW ;

� Todos os objetos de banco de dados relacionados terão que ser alterados. � Exemplo: Aplicações, programas, relatórios. � Para mudar o nome de uma tabela:

� O exemplo abaixo troca o nome da tabela EMP para FUNCIONARIO. RENAME EMP TO FUNCIONÁRIO;

� O exemplo abaixo troca o nome da tabela NOME_FUN para NOME_FUNCIONARIO. RENAME NOME_FUN TO NOME_FUNCIONARIO;

� O exemplo abaixo troca o nome da tabela DEPT para DEPARTAMENTO. SQL> rename dept to departamento;


VI.13 - DROP TABLE

� Sintaxe: DROP TABLE table schema. CASCADE CONSTRAINTS � Onde:

schema é o esquem em que a tabela está contida. Se você omitir o schema, o Oracle Server assume que o dono da tabela é o schema corrente.

table é o nome da tabela a ser apagada. CASCADE CONSTRAINTS

apaga todas as constraints de integridade referencial que se refere à chave primária e/ou única na tabela a ser apagada. Se você omitir esta opção, e existir constraint de integridade referencial, o Oracle Server retorna uma mensagem de erro e não apaga a tabela.

� O comando DROP TABLE serve para remover a definição de uma tabela.

DROP TABLE emp;

� Quando uma tabela é eliminada, todos os dados e os índices associados também serão removidos.

• Todos os dados serão removidos • Todas as visões (VIEWS) e sinônimos (SYNONYMS) permanecerão,

mas ficarão inválidas. • Todas as transações pendentes serão gravadas. • Somente o dono da tabela ou o DBA pode removê-la.


VI.14 - ELIMINANDO CONSTRAINT DA TABELA � Para eliminar constraint de uma tabela, deve-se utilizar o parâmetro DROP.

ALTER TABLE emp DROP CONSTRAINT emp_seg;

VI.15 - DOCUMENTANDO UMA TABELA

� O Comando COMMENT inclui um comentário de no máximo 255

caracteres sobre a tabela ou coluna no dicionário de dados. � Adicionando um comentário em uma tabela:

COMMENT ON TABLE emp IS ‘Informações sobre Funcioários’;

� Adicionando um comentário em uma coluna:

COMMENT ON COLUMN emp.empno IS ‘Identificador do Funcionário’;

� Eliminando um comentário:

COMMENT ON COLUMN emp.empno;

� Para visualizar os comentários, eles estão armazenados nas seguintes

visões (VIEWS) do dicionário de dados: ALL_COL_COMMENTS ou USER_COL_COMMENTS.


VI.16 - MANIPULAÇÃO DE VISÕES

� Uma visão é como uma janela que permite visualisar ou modificar

seletivamente informações armazenadas em tabelas. � Visões são utilizadas por:

• Segurança: Pode-se restringir o acesso a informações contidas em

tabelas.

Tabela Emp EMPNO ENAME …DEPTNO 7369 SMITH …20 7499 ALLEN …30 7521 WARD …30 7566 JONES …20 7654 MARTIN …30 7698 BLAKE …30 7782 CLARK …10 7788 SCOTT …20 7839 KING …10 7844 TURNER …30 7876 ADAMS …20 7900 JAMES …30 7902 FORD …20 7934 MILLER …10 Visão Emp_10 EMPNO ENAME …DEPTNO 7521 WARD …30 7782 CLARK …10 as colunas (nome e definição são herdadas da tabela origem) SQL> CREATE VIEW emp_10 as 2 SELECT * FROM emp 3 WHERE deptno = 10;


• Conveniência: Consultas complexas podem ser simplificadas com a criação de visões

Emp EMPNO ENAME …DEPTNO Dept 7369 SMITH …20 DEPTNO DNAME 7499 ALLEN …30 10 ACCOUNTING 7521 WARD …30 20 RESEARCH 7566 JONES …20 30 SALES 7654 MARTIN …30 40 OPERATIONS 7698 BLAKE …30 7782 CLARK …10 7788 SCOTT …20 7839 KING …10 7844 TURNER …30 7876 ADAMS …20 7900 JAMES …30 7902 FORD …20 7934 MILLER …10 indicações para dep. 20 apenas, por motivo de clareza Emp_Dept NOME NUM NUM_DEPTO NOME_DEPTO SMITH 7369 20 RESEARCH ALLEN 7499 30 SALES WARD 7521 30 SALES JONES 7566 20 RESEARCH MARTIN 7654 30 SALES BLAKE 7698 30 SALES CLARK 7782 10 ACCOUNTING SCOTT 7788 20 RESEARCH KING 7839 10 ACCOUNTING TURNER 7844 30 SALES ADAMS 7876 20 RESEARCH JAMES 7900 30 SALES FORD 7902 20 RESEARCH MILLER 7934 10 ACCOUNTING o nome das colunas pode ser modificado SQL>CREATE VIEW emp_dept ( nome, num, num_depto, nome_depto) 2 AS SELECT ename, empno, dept.deptno, dname 3 FROM emp, dept 4 WHERE emp.deptno = dept.deptno;


� Uma visão é considerada uma tabela virtual, isto é, funciona como uma tabela mas não contém dados. Os dados de uma visão são calculados a partir de outras tabelas (ou visões)

� Para descrever a estrutura de uma visão:

SQL> desc emp_dept Name Null ? Type NOME CHAR(10) NUM NOT NULL NUMBER(4) NUM_DEPTO NOT NULL NUMBER(2) NOME_DEPTO CHAR(14)

� Para consultar uma visão: SQL> SELECT * FROM emp_dept;

NOME NUM NUM_DEPTO NOME_DEPTO SMITH 7369 20 RESEARCH ALLEN 7499 30 SALES WARD 7521 30 SALES JONES 7566 20 RESEARCH MARTIN 7654 30 SALES BLAKE 7698 30 SALES CLARK 7782 10 ACCOUNTING SCOTT 7788 20 RESEARCH KING 7839 10 ACCOUNTING TURNER 7844 30 SALES ADAMS 7876 20 RESEARCH JAMES 7900 30 SALES FORD 7902 20 RESEARCH MILLER 7934 10 ACCOUNTING


� Somente é possível atualizar (INSERT, DELETE, UPDATE) informações em visões que:

• Estão baseadas em uma única tabela; • Fazem referência a todas as colunas não nulas (NOT NULL); • Recuperam todas as colunas diretamente, sem nenhuma expressão.

� A visão EMP_10 pode ser atualizada, ao contrário da visão EMP_DEPT, que é baseada em duas tabelas.

� Para visualizar a consulta que define uma visão:

faz o SQL*Plus mostrar até 500 caracteres de colunas do tipo LONG SQL> set long 500 SQL> SELECT text FROM user_views WHERE view_name = ‘EMP_EMP’; TEXT SELECT ename, empno, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno

� Para remover uma visão: SQL> DROP VIEW emp_dept;

� Uma VIEW é como uma janela, através da qual os dados nas tabelas

podem ser consultados e alterados. � Uma VIEW é derivada de uma tabela ou outra VIEW a qual é referenciada

com sendo a tabela base desta VIEW. � Não existem dados armazenados em uma VIEW. A VIEW manipula os

dados na tabela de referência. � Uma VIEW é armazenada como uma sentença SELECT. � VIEW são úteis para as seguintes funções:


• Restringir o acesso ao banco de dados • Permite que os usuários efetuam consultas simples sobre

consultas complicadas. • Consultar dados de diversas tabelas.

VI.17 - PARA CRIAR UMA VIEW CREATE VIEW `view OR REPLACE FORCE schema. NO FORCE AS subquery ( alias ) WITH READ ONLY CHECK OPTION CONSTRAINT constraint

� Onde: OR REPLACE cria novamente uma visão. Você pode usar

esta opção para mudar a definição de uma


visão existente sem apagar a visão. FORCE cria a visão sem considerar que a tabela que a

visão se baseia exista. NOFORCE cria a visão somente se a tabela que a visão

se baseia exista. O default é NOFORCE. Schema é o schema que contém a visão. Se você

omitir o schema, o Oracle cria a visão em seu próprio schema.

View é o nome da visão. Alias especifica nomes para as expressões

selecionadas pela consulta. O número de apelidos tem que ser igual ao número de expressões selecionadas pela visão.

AS subquery identifica colunas e linhas da(s) tabela(s) que a visão está baseada. A consulta da visão pode ser qualquer comando SELECT sem a cláusula ORDER BY ou FOR UPDATE. A lista selecionada pode ter no máximo 254 expressões.

WITH READ ONLY especifica que apagar, inserir ou atualizar não pode ser executado pela visão

WITH CHECK OPTION especifica que não será permitido a execução de um INSERT ou UPDATE através da VIEW a não ser que uma específica constraint seja definida.

CONSTRAINT é o nome atribuído para a constraint CHECK OPTION. Se você omitir este identificador, o Oracle automaticamente atribui a contraint um nome desta forma: SYS_Cn. Onde n é um número inteiro.

� O comando abaixo cria uma visão da tabela EMP chamada DEPT20. A visão mostra os empregados do departamento 20 e seu salário anual.

CREATE VIEW dept20 AS SELECT ename, sal*12 salario_anual


FROM emp WHERE deptno = 20;

� O comando abaixo cria uma visão chamada CLERKS de todos clerks na tabela EMP. A visão terá os campos: ID_NUMBER, NAME, DEPTNO, JOB.

CREATE VIEW clerk(id_number, person, department, position) AS SELECT empno, ename, deptno, job FROM emp WHERE job = ‘CLERK’ WITH READ ONLY;

CREATE VIEW d10emp AS SELECT empno, ename, sal FROM emp WHERE deptno = 10;

VI.18 - ALTERANDO DADOS ATRAVÉS DE UMA VIEW

� O comando DELETE é proibido se existir:

• Condição de JOIN


• Funções de grupo • A cláusula GROUP BY • O comando DISTINCT • O parâmetro ROWNUM

� O comando UPDATE é proibido se existir:

• Qualquer uma das condições acima • Colunas definidas por expressões (Ex.: SALARIO * 13)

� O Comando INSERT é proibido se existir:

• Qualquer uma das condições acima • Se uma coluna NOT NULL não for selecionada pela VIEW

� Deve-se utilizar o comando DROP VIEW para remover uma VIEW. DROP VIEW nome_da_view.

VI.19 - DROP TABLE

DROP VIEW view


� Onde: VIEW nome da visão


VI.20 - CRIAÇÃO DE SEQUÊNCIAS

� Você pode usar números sequenciais para gerar automaticamente chaves únicas e primárias.

� É possível definir seqüências numéricas a serem geradas automaticamente pelo ORACLE: CREATE SEQUENCE sequence schema. INCREMENT BY integer START WITH integer MAXVALUE integer NOMAXVALUE MINVALUE integer NOMINVALUE CYCLE NOCYCLE CACHE integer NOCACHE ORDER NOORDER

� Onde:


schema é o schema que contém a sequência. Se você omitir o

schema, o Oracle cria a sequência em seu próprio schema.

INCREMENT BY Determina o intervalo entre os números da seqüência. Se o valor do incremento for positivo, a seqüência será ascendente, caso contrário será decrescente. Não pode utilizar o 0 (zero) como incremento. O valor default é 1.

MINVALUE especifica o menor valor para a sequência. Esse valor inteiro pode ter 28 dígitos ou menos. O MINVALUE tem que ser menor ou igual ao START WITH e tem que ser menor do que o MAXVALUE.

NOMINVALUE especifica o menor valor de 1 para uma sequência ascendente ou (-1026) para uma sequência descendente. O default é NOMINVALUE.

MAXVALUE especifica o valor máximo para poder gerar a sequência. Este valor inteiro pode ter 28 dígitos ou menos. MAXVALUE tem que ser igual ou menor do que o START WITH e tem que ser maior do que o MINVALUE.

NOMAXVALUE O default é NOMAXVALUE. START WITH Define o primeiro número da seqüência. O default é 1

para a seqüência ascendente e MAXVALUE para a seqüência descendente.

CYCLE especifica que a sequência continua a gerar valores depois de alcançar seu valor máximo ou mínimo.

NOCYCLE especifica que a sequência não pode gerar mais valores depois de alcançar seu valor máximo ou mínimo.

CACHE especifica como muitos valores pré-alocados da sequência e continua na memória para acesso mais rápido. Este valor inteiro pode ter 28 dígitos ou menos. O valor mínimo para este parâmetro é 2.

NOCACHE especifica que o valor da sequência não é pré-alocado. Se você omitir os parâmetros CACHE e NOCACHE, o Oracle aloca na sequência o valor default do CACHE como sendo 20.

ORDER garante que o número da sequências são geradas por ordem de pedido.

NOORDER não garante que o o número da sequência seja gerada por ordem de pedido. Se você omitir a opção ORDER e NOORDER o Oracle escolherá NOORDER por default. Note que a opção ORDER é somente necessária para garantir a geração ordenada se você estiver usando Oracle7 com a aquitetura Parallel Server. Se você está usando em modo exclusivo, os números da sequência são gerados em ordem.

� Para criar uma seqüência chamada “seq_emp”:


SQL> CREATE SEQUENCE seq_emp 2 START WITH 1 NOMAXVALUE ORDER;

� Para usá-la: SQL> INSERT INTO cliente (ename, empno, …) 2 VALUES (‘PROXIMO CLIENTE’, seq_emp.NEXTVAL, …);

� Para criar uma seqüência, você deve ter privilégio de recurso (RESOURCE).

CREATE SEQUENCE dept_seq INCREMENT BY 10 START WITH 10 MAXVALUE 10000;

� Após criar uma seqüência, esta pode ser utilizada para gerar um número sequencial único.


VI.21 - GERANDO NÚMEROS DA SEQÜÊNCIA UTILIZANDO NEXTVAL � A pseudo-coluna NEXTVAL é utilizada para extrair números da seqüência.

Quando NEXTVAL é referenciado, um novo número é gerado pela seqüência. SELECT DEPT_SEQ.NEXTVAL FROM SYS.DUAL;

� Quando este comando for executado novamente, ele gerará o próximo número da seqüência.

�� NEXTVAL é muito utilizado pelo comando DML para popular colunas

chaves de uma tabela. INSERT INTO dept VALUES (DEPT_SEQ.NEXTVAL, ‘CONTABILIDADE’,’BRASILIA’);

� Para se referenciar a um número de seqüência que acabou de ser gerado,

você deve utilizar a pseudo_coluna CURRVAL. CURRVAL mostra o número corrente da seqüência. INSERT INTO dept_historico VALUES ( DEPT_SEQ.CURRVAL,’VENDAS’,’BRASILIA’);

� Regras de utilização do NEXTVAL e CURRVAL

• Podem ser utilizados na sentença do comando SELECT. • Podem ser utilizados na cláusula VALUES do comando INSERT. • Podem ser utilizados na cláusula SET do comando UPDATE. • Não podem ser utilizados dentro de um comando SELECT que define

uma VIEW.

• Não podem ser utilizados com a cláusula DISTINCT. • Não podem ser utilizados com as cláusulas ORDER BY, GROUP BY,

CONNECT BY ou HAVING do comando SELECT. • Não podem ser utilizados com os operadores - UNION, INTERSECT,

MINUS.


� Para alterar uma seqüência deve-se utilizar o comando ALTER SEQUENCE. ALTER SEQUENCE sequence schema. INCREMENT BY integer MAXVALUE integer NOMAXVALUE MINVALUE integer NOMINVALUE CYCLE NOCYCLE CACHE integer NOCACHE ORDER NOORDER

� Somente os próximos números criados pela seqüência serão afetados pela alteração.


VI.22 - ÍNDICES

� Os índices tem dois propósitos básicos:

• Acelerar a recuperação de linhas utilizando uma chave específica. • Para certificar que o valor de uma coluna será único na tabela.

� Os índices usam a estrutura B-TREE (Árvore balanceada). Este é um eficiente método que certifica que um acesso a qualquer valor especificado levará aproximadamente o mesmo tempo se a linha estiver no começo, meio ou fim da tabela. O velocidade de acesso não depende do volume do índice de dados.

� Tipos de Índices:

UNIQUE Certifica que os valores numa coluna serão únicos. NON UNIQUE Certifica das possibilidades de obter um rápido

resultado à uma consulta. SINGLE COLUMN Somente uma coluna existe no índice. CONCATENATED Acima de 16 colunas especificadas no índice, por

performance ou checagem. �� Sintaxe:

CREATE INDEX index UNIQUE schema.

, ON table ( column )

schema. ASC

DESC Obs: Sintaxe abreviada.

� Onde:


UNIQUE especifica que o valor da coluna (ou combinação de

colunas) na tabela a ser indexada tem que ser único. Schema é o schema que contém o índice. Se você omitir o

schema, o Oracle cria o índice em seu próprio schema. Index é o nome do índice a ser criado. Table é o nome da tabela no qual o índice será criado. Se você

não especificar qual o schema da tabela, o Oracle assume que a tabela está contida em seu próprio schema.

Column é o nome da coluna na tabela. Um índice não pode ter mais de 16 colunas.

ASC/DESC são permitidos para compatibilidade de sintaxe DB2, embora índices são sempre criados em ordem ascendente.

� Quando um Índice é utilizado?

• Quando uma coluna é referenciada numa cláusula WHERE.

SELECT * FROM emp WHERE ename = ‘SMITH’;

� Quando uma coluna é referenciada na cláusula WHERE como parte de uma expressão ou função, o índice não é utilizado.

� Sugestões para utilização de índices.

• Em uma tabela com mais de 200 linhas, o uso do índice pode melhorar

a performance. • As colunas que contém valores únicos na tabela, devem ser

indexadas. • As colunas que são freqüentemente utilizadas em cláusulas WHERE

ou JOIN devem ser indexadas. • Criar um índice concatenado (CONCATENATED) para duas ou mais

colunas que são utilizadas freqüentemente em conjunto em cláusulas WHERE ou JOIN.

• Não faça mais que três índices por tabela pois poderá acarretar uma

utilização maior de recursos para cada execução de um comando DML.


VI.23 - EXERCÍCIOS 1. Crie uma tabela EMPREG que contém os dados das colunas EMPNO,

ENAME e SAL da tabela EMP. 2. Mude o nome da tabela EMPREG para TEMP e depois a remova. 3. Altere a estrutura da tabela ALUNO criada no exercício 1 da parte II, de

forma a:

a) aumentar o tamanho da coluna CIDADE para 35 caracteres; b) acrescentar o campo SAL_DESEJADO, com tipo numérico de 9 dígitos

na parte inteira e 2 casas decimais. Dica: Lembre-se que o tamanho de um número é dado pela soma dos dígitos na parte inteira com os dígitos na parte decimal do número;

4. Insira os seguintes dados de um(a) amigo(a) seu(a) na tabela ALUNO: NOME, ANIVERSÁRIO e SAL_DESEJADO. 5. Atualize o seu salário desejado e o endereço completo da pessoa

cadastrada no exercício anterior. 6. Remova o atributo SAL_DESEJADO da tabela ALUNO. 7. Remova os dados da pessoa cadastrada no exercício 4 e efetive sua

transação. 8. Crie uma visão chamada DEP_SAL baseada nas tabelas EMP e DEPT,

contendo as seguintes informações:

• Nome do departamento (NOME_DEP) • Numero de funcionários (NUM_FUNC) • Salário mínimo (SAL_MIN) • Salário médio (SAL_MED) • Salário máximo (SAL_MAX)

DICA: Primeiro faça uma consulta que reforma os dados acima, para depois criar uma visão baseada nessa consulta.

9. Verifique a estrutura da visão DEP_SAL com o comando DESCRIBE e depois consulte todos os seus dados.

10. Atualize o salário de SMITH para 3000 e consulte a visão DEP_SAL

novamente. 11. Desfaça a última atualização realizada.


VII - FUNÇÕES


VII.1 - FUNÇÕES NUMÉRICAS

� As funções numéricas recebem um número como parâmetro e retornam

outro valor numérico. • Função ABS Retorna o valor absoluto do número. ABS (numero)

SELECT ABS(-10) “ABS” FROM DUAL;

• Função FLOOR

Retorna o menor inteiro maior ou igual à expressão.

FLOOR(expr)

SELECT CEIL(10.7) “CEIL” FROM DUAL;

• Função CEIL

Retorna o maior inteiro menor ou igual à expressão.

CEIL(expr)

SELECT FLOOR(10.7) “FLOOR” FROM DUAL;

• Função MOD Retorna o resto da divisão do primeiro pelo segundo número.

MOD(numero1, numero2)

SELECT MOD(7,5) “MOD” FROM DUAL;


• Função POWER Retorna o valor do primeiro número elevado ao segundo número (potência).

Se o segundo número não for um inteiro, um erro é retornado. POWER(numero1,numero2)

SELECT POWER(3,2) “POWER” FROM DUAL;

• Função ROUND Retorna o valor do primeiro número arredondado para o segundo número de

casas a direita do ponto decimal. Se o segundo número for omitido, arredonda o primeiro número sem casas decimais. O segundo número pode assumir valores negativos, sendo que nesse caso o primeiro número será arredondado à esquerda do ponto decimal. O segundo número deve ser um inteiro.

ROUND(numero1,numero2)

SELECT ROUND(15.193,1) “ROUND 1”FROM DUAL

• Função SIGN Retorna o sinal do número. Caso o número seja negativo, retornará -1. Se o

número for 0 retornará 0 e se for maior que 0, a função retorna 1.

SIGN(numero)

SELECT SIGN(-10) “SIGN” FROM DUAL;

• Função SQRT Retorna a raiz quadrada do número. No caso do número ser negativo, a

função SQRT retornará um valor nulo. Nas outras vezes, retornará sempre um resultado real.

SQRT(numero) SELECT SQRT(36) “SQRT” FROM DUAL;


• Função TRUNC Retorna o primeiro número truncado de tantas casas decimais quando forem

especificadas pelo segundo número. Se o segundo número for omitido, trunca o primeiro número no ponto decimal. Se o segundo número for negativo, trunca o primeiro número à esquerda do ponto decimal. O segundo número precisa ser inteiro. TRUNC(numero1,numero2)

SELECT TRUNC(15.79,1) “TRUNC 1” FROM DUAL;


VII.2 - FUNÇÕES CARACTER

� As funções caracter recebem um parâmetro caracter e retornam um valor

caracter ou um valor numérico. • Função ASCII Retorna o valor ASCII do primeiro caracter da string char. Não existe uma

função correspondente EBCDIC. Assim, nesses sistemas, esta função retorna o valor EBCDIC do primeiro caracter da string char.

ASCII(CHAR)

S ELECT ASCII(‘Q’) FROM DUAL;

• Função CHR Retorna o caracter que corresponde ao valor ASCII ou EBCDIC especificado

pelo número, dependendo do tipo de caracteres com os quais o computador trabalha.

CHR(numero)

SELECT CHR(75) “CHR” FROM DUAL; • Função INITICAP Retorna a string de caracteres char com as primeiras letras de cada uma das

palavras que forma em maiúscula e todas as outras em minúsculas. As palavras são delimitadas por espaços.

INITICAP(CHAR)

SELECT INITCAP (‘PAULO HENRIQUE CONTE’) “INITCAP” FROM DUAL;


• Função INSTR Retorna a posição da ocorrência de char2 na string de caracteres char1. A

pesquisa inicia-se na posição num1 e, caso num1 seja omitido, é assumido o início da string. O número num2 especifica qual a ocorrência que deverá ser pesquisada. Por exemplo, se num2 for 1 será pesquisada a primeira ocorrência; se num2 for 2, a segunda; se num2 for 3, a terceira ocorrência será pesquisada e daí por diante. No caso de num2 ser omitido, é assumida a primeira ocorrência de char2. INSTR(CHAR1,CHAR2,NUM1,NUM2)

SELECT INSTR(‘LEVEL & EBN’,’E’,3,2) “INSTR” FROM DUAL;

• Função LENGTH Retorna o tamanho da string.

LENGTH(CHAR)

SELECT LENGTH(‘REPUBLICA’) “LENGHT” FROM DUAL;

• Função LOWER Transforma todos os caracteres da string char em letras minúsculas.

LOWER(CHAR) SELECT LOWER(‘LETRAS MINÚSCULAS’) “LOWER” FROM DUAL;

• Função LPAD Insere os caracteres de char2 no início da string char1 e os repete até que a

string char1 fique com um tamanho em caracteres especificado por num. LPAD(CHAR1,NUM,CHAR2) SELECT LPAD(‘Banco de Dados’,20, ‘+8’) “LPAD” FROM DUAL;


• Função LTRIM Remove todos os caracteres especificados em char2 que constarem à

esquerda da string de caracteres char1, até que um dos caracteres sejam diferentes dos especificados. Esta função é semelhante à função RTRIM. LTRIM(CHAR1,CHAR2) SELECT LTRIM(‘xxxXxxxLTRIM’,’x’) “LTRIM” FROM DUAL;

•Função RTRIM Remove todos os caracteres à direita de char1 que sejam iguais aos

caracteres especificados em char2, até que um dos caracteres de char1 seja diferente. Esta função é semelhante à função LTRIM. RTRIM(CHAR1,CHAR2)

SELECT RTRIM(‘LEVELxxXxxxx’,’x’) “RTRIM” FROM DUAL;

• Função RPAD Inclui char2 no final da string de caracteres char1 até que char1 fique com o

tamanho em caracteres especificado por num. RPAD(CHAR1,NUM,CHAR2) SELECT RPAD(‘RAZAO’,10,’x’) “RPAD” FROM DUAL;


• Função SUBSTR Retorna uma parte da string de caracteres char que se inicia na posição

num1 com num2 posições. SUBSTR(CHAR,NUM1,NUM2) SELECT SUBSTR(‘ABCDEFG’,2,3) “SUBSTR” FROM DUAL;

• Função UPPER Transforma a string de caracteres char em letras maiúsculas.

UPPER(CHAR) SELECT UPPER(‘Paulo Henrique Conte’) “UPPER” FROM DUAL;

• Função SOUNDEX Retorna um conjunto de caracteres que representa o som da palavra. Esta

função retorna a representação fonética para cada palavra permitindo assim comparações entre palavras de escrita diferentes mas com o mesmo som. SELECT ename, SOUNDEX(ename) FROM EMP WHERE SOUNDEX(ename) = SOUNDEX(‘MILLER’); Nome SOUNDEX(nome) MILLER M460


• Função TRANSLATE TRANSLATE (coluna/valor ,from,to)

Esta função é utilizada para substituir caracteres.

A função TRANSLATE pode ser usada para pesquisas com a utilização dos

caracteres _ e % da maneira exemplificada abaixo. Neste exemplo, a função TRANSLATE converte o caracter _ para o caracter # com o propósito de avaliar uma pesquisa e a declaração retorna todas as linhas que possuírem o caracter # na coluna ENAME da tabela EMP.

SELECT ename FROM emp WHERE TRANSLATE(ENAME,’_’,’#’) LIKE ‘%#%’ SELECT ename, TRANSLATE (ename,’P’, ‘C’), job,

TRANSLATE(job,’E’,’I’) FROM emp WHERE deptno = 10;

nome TRANSLATE (nome,’P’,’C’) Cargo TRANSLATE(cargo,’E’,’I’) Pedro Cedro Vendedor Vindidor Carlos Carlos Gerente Girinti

• Função REPLACE Como a função TRANSLATE, esta função é utilizada para substituir

caracteres.

REPLACE (coluna/valor,string,string_substituto)

SELECT ename, REPLACE (ename,’C’, ‘P’), job, REPLACE(job,’SALESMAN’,’AGENTE_DE_VENDAS’)

FROM emp WHERE deptno = 10;

nome REPLACE(ename,’P’,’C’) Cargo REPLACE(job,’Salesman’,’Agente’) Pedro Cedro Salesman Agente Carlos Carlos Gerente Gerente


II.3 - FUNÇÕES DE AGRUPAMENTO (GRUPO)

� As funções de agrupamento retornam um valor baseado em um grupo de

linhas. A cláusula GROUP BY da declaração SELECT é usada para dividir o resultado em grupos menores. A cláusula DISTINCT faz com que as funções de agrupamento considerem apenas valores distintos da expressão. A cláusula ALL faz com que as funções considerem todos os valores, inclusive os duplicados. Quando nenhuma cláusula é especificada, a normalmente utilizada é ALL. Os tipos de dados como argumento podem ser CHAR, NUMBER ou DATE. A seguir apresentamos todas as funções de agrupamento. De todas elas, apenas a função COUNT(*) não deixa de levar em consideração os valores nulos. Todas as outras desconsideram esses valores.

• Função AVG Retorna a média de número, ignorando os valores nulos.

AVG(DISTINCT | ALL NUM) SELECT AVG(SAL) “AVG” FROM emp;

• Função COUNT Retorna o número de linhas especificadas para todos os valores da

expressão expr que não sejam nulos. COUNT(DISTINCT | ALL EXPR) SELECT COUNT(JOB) “COUNT(EXPR)2” FROM emp;

• Função COUNT(*) Retorna o número de linhas de uma tabela, incluindo as linhas duplicadas ou

que contenham valores nulos. COUNT(*) SELECT COUNT(*) “COUNT(*)” FROM emp;

• Função MAX


Retorna o valor máximo da expressão expr.

MAX(DISTINCT | ALL EXPR) SELECT MAX(SAL) “MAX” FROM DUAL;

• Função MIN Retorna o valor mínimo da expressão expr.

MIN(DISTINCT | ALL EXPR) SELECT min(hiredate) FROM emp;

• Função SUM Retorna a soma dos valores de num.

SUM (DISTINCT | ALL NUM) SELECT sum(sal) “SUM” FROM emp;


VII.4 - FUNÇÕES DE CONVERSÃO

� As funções de conversão são usadas para converter valores de tipos de dados diferente.

• Função TO_CHAR Converte um valor do tipo NUMBER ou DATE para uma string de caracteres

formatada segundo a caracterização especificada em formato, opcionalmente. Os formatos são discutidos mais tarde neste capítulo. Se o formato for omitido, num é convertido para uma string de caracteres do mesmo tamanho dos números significativos de num, se for número. TO_CHAR(NUM | DATE, FORMATO) SELECT TO_CHAR(18904, ‘$099,999’) “TO_CHAR NUMBER” FROM DUAL;

• Função TO_DATE Converte uma string de caracteres especificando uma data em um valor do

tipo DATE. A data do dia é usada para providenciar informações adicionais na conversão de caracteres para data. TO_DATE(CHAR, FORMATO) SELECT TO_DATE(‘December 11, 1969’, ‘Month DD,YYYY’) “TO_DATE” FROM DUAL;

• Função TO_NUMBER Converte a string de caracteres char, contendo um número, para o tipo de

dados NUMBER. TO_NUMBER(CHAR) UPDATE emp SET SAL = SAL + TO_NUMBER(‘250’);

FORMATOS:


SCC ou CC Século, o prefixo S substitui data AC por ‘-’. YYYY ou SYYYY Ano, o prefixo S substitui data AC por ‘-’. YYY ou YY ou Y Últimos 3,2 ou 1 dígitos do ano. Y,YYY Ano com uma vírgula nesta posição. SYEAR ou YEAR Ano por extenso, o prefixo S substitui data AC por ‘-’. BC ou AD Indicadores AC(BC) e DC(AD). B.C. ou A.D. Indicadores AC(BC) e DC(AD) com períodos. Q Trimestre. MM Mês. MONTH Nome do mês ( 9 caracteres). MON Nome do mês abreviado nas três primeiras letras. WW ou W Semana do ano ou mês. DDD ou DD ou D Dia do ano, mês ou semana. DAY Nome do dia ( 9 caracteres ). DY Nome do dia abreviado nas três primeiras letras. J Data Juliana. AM ou PM Indicador Meridiano. A.M. ou P.M. Indicador Meridiano com períodos. HH ou HH12 Horas do dia ( 1-12 ). HH24 Horas do dia ( 0-23 ). MI Minutos. SS Segundos. SSSSS Segundos passados da meia-noite.


VII.5 - FUNÇÕES QUE ACEITAM QUALQUER TIPO DE DADO COMO ENTRADA

• Função DECODE DECODE é uma das mais poderosas funções do SQL. Esta função facilita

requisições condicionais fazem o papel de um comando CASE ou IF-THEN ELSE. DECODE (coluna/expressão, escolha1, resultado1, escolha2, resultado2, ..., default )

A coluna/expressão entrada é comparada com cada escolha e retorna o resultado correspondente. Se não for encontrado nenhuma escolha igual coluna/expressão, a função DECODE retornará o valor default. SELECT job, sal, DECODE ( job, ‘SALESMAN’, sal*1.1, ‘MANAGER’,sal*0.95,sal) decoded_sal FROM emp;

• Função GREATEST Retorna a maior lista dos valores entrados.

GREATEST (coluna/valor1,coluna/valor2,...) SELECT GREATEST (1000,2000), GREATEST (sal, comm) FROM emp;

• Função LEAST Retorna o menor valor da lista de valores.

LEAST (coluna/valor1,coluna/valor2,...) SELECT LEAST (1000,2000), LEAST (sal, comm) FROM emp;

• Função VSIZE Retorna o número de bytes na representação interna do Oracle.

VSIZE (coluna/valor) SELECT deptno, VSIZE(deptno), ename, VSIZE(ename) FROM emp;


VII.6 - FUNÇÕES DE DATAS

� As funções data são utilizadas para operar com os tipos de dados DATE

do ORACLE. Sempre retornam um valor do tipo DATE, exceto a função MONTHS_BETWEEN que retorna um valor numérico.

• Função ADD_MONTHS Retorna a data dat adicionada de num meses. Se o número num for

negativo, os meses serão subtraídos. O número num sempre deve ser um inteiro. ADD_MONTHS(DAT,NUM) SELECT ADD_MONTHS(HIREDATE, 12) "ADD_MONTHS” FROM emp;

• Função NEXT_DAY Retonar a data no próximo dia da semana especificado no char1 depois do

dia dat1. NEXT_DAY(dat1,char1) SELECT hiredate, NEXT_DAY(hiredate,’FRIDAY’), NEXT_DAY(hiredate,6) FROM emp WHERE deptno = 10;

• Função LAST_DAY Retorna a data do último dia do mês da data dat1.

LAST_DAY(dat1) SELECT sysdate, last_day(sysdate), LAST_DAY(‘25-FEB-84’) FROM SYS.DUAL;

• Função ROUND


Quando utilizado com o parâmetro MONTH retorna o primeiro dia do mês da

quinzena da data referente, se esta estiver na primeira quinzena, caso contrário, retornará o primeiro dia do próximo mês. Quando utilizado com o parâmetro YEAR retorna o primeiro dia do ano da data referente se esta estiver no primeiro semestre, caso contrário retornará o primeiro dia do próximo ano. Quando utilizado sem parâmetro, retorna a data com a hora igual a 12:00AM. ROUND (dat1,’MONTH’/’YEAR’) SELECT sysdate, ROUND(sysdate,’MONTH’), ROUND(sysdate,’YEAR’) FROM SYS.DUAL;

• Função TRUNC Retorna o primeiro dia do mês da data referente se utilizado com o parâmetro

MONTH. Se utilizado com o parâmetro YEAR, retornará o primeiro dia do ano da data referente. TRUNC(dat1,’MONTH’/’YEAR’) SELECT sysdate, TRUNC(sysdate,’MONTH’), TRUNC(sysdate,’YEAR’) FROM SYS.DUAL;

• Função MONTHS_BETWEEN Retorna o número de meses entre as datas dat1 e dat2. Se dat1 for posterior

a dat2, o valor retornado é positivo. Se dat2 for maior que dat1, então o valor retornado é negativo. MONTHS_BETWEEN (DAT1,DAT2) SELECT MONTHS_BETWEEN(‘02-FEB-86’,’01-JAN-86’) “MONTHS_BETWEEN” FROM DUAL;


• Função SYSDATE Retorna a data e hora atual do sistema. Não requer argumentos.

SYSDATE SELECT SYSDATE FROM DUAL;


VIII - DICIONÁRIO DE DADOS


VIII.1 - O DICIONÁRIO DE DADOS ORACLE

� O Dicionário de Dados Oracle contém as definições dos objetos dos bancos de dados. O acesso a esse dicionário é possível devido a existência de visões, semelhantes a tabelas mas não guardam dados, isto é, são apenas definições de comandos SELECT para pesquisa de dados. O Dicionário de Dados contém:

• O identificador dos usuários Oracle • As regras e privilégios atribuída aos usuários • Nomes dos objetos do banco de dados (tabelas, visões, índices,

sinônimos, seqüências,...) • As constraints aplicadas nas tabelas • Informações de auditoria, tais como quem tem acessado ou atualizado

um banco de dados específico.

� O Dicionário de Dados é criado quando um banco de dados é criado. A atualização do dicionário de dados é feita pelo ORACLE RDBMS.


III.2 - CONTROLE DE TRANSAÇÕES

� Uma transação é uma seqüência “indivisível” de comandos SQL que

modifica o conteúdo do banco de dados. � Por exemplo, para transferir uma certa quantia em dinheiro de uma conta A

para uma conta B, devemos:

• Retirar o dinheiro da conta A • Depositar o dinheiro na conta B

� Para que a operação acima seja segura, ou os dois passos são

executados ou nenhum deles pode ocorrer.

� O início de uma transação é determinado pelo primeiro comando de manipulação de dados (INSERT, UPDATE, DELETE) desde o fim da última transação ou início da seção corrente.

� O fim de uma transação é determinado de uma das seguintes formas:

• Execução do comando COMMIT. Todas as modificações são

efetivadas no banco de dados; • Execução do comando ROLLBACK. Nenhuma modificação é

efetivada; • Execução de comandos de definição de dados (CREATE, ALTER, …);

equivale ao comando COMMIT; • Término da sessão corrente: equivale ao comando COMMIT;

� Quando uma transação é interrompida por fatores externos (queda do sistema) ou internos (“deadlock”). Equivale ao comando ROLLBACK.

� Exemplo:


$ sqlplus ora1/senha1 SQL*Plus: Version 3.1.1.9.1 - Production on Wed Apr 3 08:56 … Copyright (c) ORACLE Corporation 1979, 1992. All … Connected to: ORACLE7 Server Release 7.0.12.1.0 - Production With the procedural option PL/SQL Release 2.0.14.0.1 - Production Inibe algumas mensagens de aviso SQL> set feedback off SQL> SELECT deptno, dname FROM dept; DEPTNO DNAME 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS início de uma transação SQL> INSERT INTO dept values (50, ‘MARKETING’, ‘SAO PAULO’); SQL> INSERT INTO dept values (60, ‘INFORMATICA’, ‘CAMPINAS’); SQL> SELECT deptno, dname FROM dept; DEPTNO DNAME 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS o próprio usuário vê os dados, mas 50 MARKETING eles ainda não foram efetivados 60 INFORMÁTICA fim da transação, com efetivação dos dados SQL> commit; Commit compete.


SQL> UPDATE dept SET dname = ‘COMERCIAL’ 2 WHERE deptno = 30; início de outra SQL> SELECT dname FROM dept where deptno = 30; transação DEPTNO COMERCIAL fim da transação; as atualizações foram descartadas SQL> rollback; Rollback complete. SQL> SELECT deptno FROM dept WHERE deptno = 30; DNAME produzem o mesmo SALES efeito SQL> DELETE FROM emp WHERE empno = 7369; SQL> INSERT INTO emp values 2 (103, ‘SAMANTA’, ‘DIRETOR’, 175, ‘17-MAR-85’, 3 2000, NULL, 20); SQL> commit; Commit complete. SQL> update emp set job = ‘DIRETOR’, chefe = 175, sal = 2000 2 where empno = 7369; SQL> commit; Commit complete. Início da última SQL> DELETE FROM emp transação 2 WHERE deptno = 10; SQL> exit fim da última transação, com COMMIT implícito

� Observação:

• Uma transação ou é executado totalmente ou não é executada. Não existe transação parcialmente executada, por definição.

• As modificações efetuadas no decorrer de uma transação por um

usuário só são visíveis a outros usuários após a efetivação da transação (COMMIT).


USUÁRIO 1 USUÁRIO 2

SQL> SELECT sal FROM emp SQL> SELECT sal from emp 2 WHERE empno = 7369; 2 WHERE empno = 7369; SAL SAL 800 800 SQL> UPDATE emp SQL> 2 SET sal = 4000 3 WHERE empno = 7369; SQL> SELECT sal from emp SQL> SELECT sal FROM emp 2 WHERE empno = 7369; 2 WHERE empno = 7369; SAL SAL 4000 800 SQL> commit; SQL> Commit complete. SQL> SELECT sal FROM emp SQL> SELECT sal FROM emp 2 WHERE empno = 7369; 2 WHERE empno = 7369; SAL SAL 4000 4000

� O comando SET AUTOCOMMIT ON do SQL*Plus faz com que o commit seja executado automaticamente a cada comando de manipulação de dados: SQL>set feedback on SQL> set autocommit on SQL> delete from dept = 10; Commit deleted. 1 record deleted. SQL> INSERT INTO emp VALUES (103, ‘SAMANTA’, ‘ANALISTA’, 2 110, ‘17-MAR-85’, 1500, NULL, 20); Commit complete. 1 record created. SQL> set autocommit off SQL> DELETE FROM emp 2 WHERE empno = 189; 1 record deleted. SQL> rollback; Rollback complete.


� É possível determinar pontos de salvamento intermediários em transações: SQL> UPDATE conta 2 SET saldo = saldo - 10 determinar um ponto de 3 WHERE codigo = 1; salvamento SQL> savepoint ja_tirou; SQL> UPDATE conta 2 SET saldo = saldo + 10 desfaz as modificações feitas após 3 WHERE codigo = 2; o ponto de salvamento “já_tirou” SQL> rollback to ja_tirou;

� Para garantir que vários comandos SELECT recuperem sempre os mesmos dados, independentemente de transações concorrentes: SQL> set transaction read only; SQL> SELECT sal 2 FROM emp 3 WHERE empno = 7902; SAL 3000 SQL> os resultados serão SQL> SELECT sal sempre iguais 2 FROM emp 3 WHERE empno = 7902; SAL 3000

� Transações são as operações que ocorrem em uma ou mais tabelas. São iniciadas com o primeiro comando DML ou após o início de uma sessão. As transações terminam após um comando DDL, após um COMMIT ou ROLLBACK, após o encerramento da sessão com o servidor ou após uma falha de hardware (deadlocks).

� O Oracle certifica-se da consistência dos dados através de transações.

INSERT UPDATE DELETE COMMIT ou ROLLBACK Início SAVE POINT COMMIT Término

� Até que a transação seja efetivada:


• Você pode verificar os dados em uma consulta sem as mudanças • Outros usuários também podem pesquisar os mesmos dados • A transação pode ser descartada (ROLLBACK) • Você pode utilizar SAVEPOINTS para marcar pontos da transação


VIII.3 - CONTROLANDO TRANSAÇÕES COM COMANDOS SQL � O comando COMMIT COMMIT WORK;

• Efetua as alterações da transação corrente de forma permanente. • Elimina todos os SAVEPOINTS da transação. • Termina a transação. • Libera os Locks causados pela transação. • A palavra WORK é opcional. • Se uma transação termina de forma não normal, as atualizações da

transação corrente não serão atualizadas. • COMMITs automáticos ocorrem:

♦ Depois de um comando DDL ♦ Ao final normal de uma sessão ao Banco de Dados


VIII.4 - O COMANDO SAVEPOINT

SAVEPOINT nome;

� Pode ser utilizado para dividir uma transação.

�� SAVEPOINT possibilita o retorno a um ponto, desprezando as alterações ocorridas após aquele ponto.

� Se criar um SAVEPOINT com o mesmo nome de uma anterior, o anterior

será eliminado.

VIII.5 - O COMANDO ROLLBACK

ROLLBACK WORK to SAVEPOINT nome;

� O comando ROLLBACK é utilizado para desfazer o trabalho. � A palavra WORK é opcional. � Utilizando o comando ROLLBACK sem a cláusula SAVEPOINT irá:

• Finalizar a transação • Não efetivará as alterações da transação • Eliminar todos os SAVEPOINTS da transação • Eliminar os Locks causados pela transação.

� Utilizando o comando ROLLBACK com a cláusula SAVEPOINT irá:

• Desprezar parte das alterações da transação

� Eliminará todos os SAVEPOINTs entre o momento do comando até o SAVEPOINT destino

• Eliminará os Locks de tabelas e linhas.


VIII.6 - O PARÂMETRO AUTOCOMMIT

AUTOCOMMIT

Controla quando as alterações da transação serão efetivadas no banco de dados.

SET AUTOCOMMIT ON

O comando COMMIT será executado automaticamente após cada comando DML (INSERT, UPDATE OU DELETE).

SET AUTOCOMMIT OFF

O comando COMMIT tem que ser executado pelo usuário ou será executado após os comandos DDL.


VIII.7 - CONCORRÊNCIA

� Uma das maiores tarefas de um RDBMS é controlar a concorrência de

acesso a um mesmo dado por usuários diferentes. � A concorrência dos dados é controlada através dos chamados LOCKs, que

são usados para proteger os dados e controlar os usuários. � O Lock é um mecanismo que é usado para controlar a concorrência no

acesso aos dados em um sistema multi-usuário. O Lock prevê o acesso simultâneo aos mesmos dados por diferentes usuários, isto é, o mesmo conjunto de dados não pode ser alterado por dois usuários ao mesmo tempo ou uma tabela não pode ser removida quando os dados estiverem sendo alterados.

� Os Locks são utilizados sempre que uma alteração ocorra no banco de

dados e são liberadas quando ocorrem um ROLLBACK, COMMIT ou um DEADLOCK, em outras palavras, quando a transação for encerrada.

� Existem dois tipo de locks:

•••• DDL - Data Dictionary Lock : Controla o acesso às definições dos objetos de banco de dados.

•••• DML - Data Manipulation Lock : Controla o acesso aos dados das

tabelas. O Oracle automaticamente usa o recurso do lock quando os dados estão sendo alterados. Entretanto o usuário pode acionar o lock em uma ou mais tabelas utillizando os locks explícitos, através de comandos SQL.

� Os Locks podem ocorrer a nível de tabelas ou a nível de linhas.

Comandos SQL Linha Tabela SELECT - - INSERT X RX UPDATE X RX DELETE X RX DDL - X SELECT FOR UPDATE X RS LOCK TABLE IN EXCLUSIVE - X SHARE UPDATE - RS ROW SHARE - RS SHARE ROW EXCLUSIVE - SRX ROW EXCLUSIVE - RX SHARE - S


Simplificando, os locks tem como finalidade proteger os dados e

controlar o acesso aos dados pelos usuários. � Alguns Locks são mais restritivos que outros. A maioria dos Locks está

entre os Locks de Compartilhamento (Share Locks) e os Locks de exclusividade (Exclusive Lock).

�� Share Lock - Pode ser executado por mais de um usuário na mesma

tabela ao mesmo tempo. �� Exclusive Lock - Pode ser executado por um único usuário de cada vez.

O primeiro usuário a executar o Lock ganha o direito exclusivo de atualizar a tabela, até que o Exclusive Lock for desabilitado.


VIII.8 - TIPOS DE LOCKS

�� Exclusive (X) - Permite consultas no recurso (linha ou tabela) mas proíbe

qualquer outra atividade no recurso. �� Row Exclusive (RX) - Permite concorrência de acesso a tabela, não

permite que outros usuários executem um lock na tabela inteira, vários usuários podem executar o lock em linhas distintas e efetivarem suas alterações, a linha que está com lock pode ser consultada.

�� Share Row Exclusive (SRX) - Permite que usuários consultem dados da

tabela, não permite que a tabela esteja com Exclusive Lock. Pode ocorrer Lock de linha.

�� DEADLOCKS

• Deadlock pode ocorrer quando dois ou mais usuários tentam acessar o mesmo objeto do banco de dados. Tipicamente, cada usuário ficará esperando o recurso que está sendo utilizado por outro usuário.


VIII.9 - ROWID

� O processo de COMMIT pode ser agilizado utilizando o ROWID para

localizar as linhas dentro das tabelas. O ROWID é uma pseudo-coluna a qual armazena para todas as linhas um valor na tabela. ROWID especifica o endereço da linha e o caminho tornando mais rápido o acesso à linha.

� O ROWID é composto por três partes:

00004C90.0001.0001

• A primeira parte identifica o bloco dentro do banco de dados (00004C90)

• A segunda parte identifica linha no bloco (0001) • A terceira parte identifica o arquivo do banco de dados (0001)

�� Obs:

•••• O ROWID é único para cada linha.


VIII.10 - USUÁRIOS E SEGURANÇA

� Criando um usuário ORACLE. � O comando GRANT é utilizado pelo Administrador do Banco de Dados

para criar novos usuários e atualizar a senha dos já existentes. GRANT opção TO usuário IDENTIFIED BY senha; �� Opções:

CONNECT Habilidade de acesso ao ORACLE e acessar qualquer objeto que tenha privilégio para tal.

RESOURCE Habilidade para criar tabelas, seqüências e índices.

DBA Habilidade para criar outros usuários e desprezar as proteções existentes. Este privilégio deve ser restrito.

GRANT CONNECT, RESOURCE TO JOAO IDENTIFIED BY SENHAJOAO;


VIII.11 - PRIVILÉGIOS DE OBJETOS DO BANCO DE DADOS

� Quem criar uma tabela, visão, seqüência e sinônimo será o dono da

tabela. Se não disponibilizar essa tabela para outro usuário Oracle, somente o dono e o DBA podem acessá-la.

� Para permitir que outros usuários acessem o seu objeto de banco de

dados, você deve utilizar o comando GRANT:

GRANT privilégios ON objeto

TO usuário;

� Lista de Privilégios:

SELECT Dados na tabela ou visão. INSERT Linhas na tabela ou visão. UPDATE Linhas ou específicas colunas em uma tabela ou visão. DELETE Linhas de uma tabela ou visão. ALTER Definições de colunas em uma tabela. INDEX índices de uma tabela. REFERENCES Possibilita criar uma chave estrangeira para uma tabela

(constraint). �� Observações:

• Os privilégios ALTER, INDEX e REFERENCES não estão disponíveis

para visões. • Somente os privilégios ALTER e SELECT são aplicados para

seqüências.


VIII.12 - O COMANDO REVOKE

� O comando REVOKE deve ser utilizado para remover privilégios de ojetos

de banco de dados.

REVOKE privilégios ON tabela ou visões FROM usuários;

� Quando o comando REVOKE é executado, o privilégio é removido do usuário referido e de todos os outros para o qual ele tenha dado privilégios. REVOKE ALL ON dept FROM joao;

� Para saber quais usuários têm privilégios em seu objeto de banco de dados, você pode consultar as visões do dicionário de dados USER-TAB_GRANT ou USER_COL_GRANTS.


VIII.13 - CRIANDO UM SINÔNIMO PARA UMA TABELA, VISÃO OU SEQÜÊNCIA � Quando se referenciar a uma tabela que pertence a outros usuários, você

deve utilizar como prefixo da tabela o nome do usuário dono da tabela. SELECT * FROM scott.emp;

� Como alternativa, pode-se criar sinônimos para a tabela ou visão. CREATE SYNONYM EMP FOR scott.emp;

� Somente o DBA pode criar um sinônimo público para que todos os usuários possam acessá-lo. CREATE PUBLIC SYNONYM nome_do_sinonimo FOR dono.nome_do_objeto;

� Para remover um sinônimo basta utilizar o comando DROP. DROP PUBLIC SYNONYM nome_do_sinônimo;

� Os sinônimos são utilizados para:

• Para referenciar a um objeto de banco de dados sem ter que especificar o nome do dono da tabela.

• Para disponibilizar um outro nome da tabela (apelido).

� Por razão de performance, não é aconselhável a utilização de sinônimos

referenciando tabelas nas aplicações.


IX - PL/SQL


IX.1 - O QUE É PL/SQL?

� Uma Linguagem Procedural. � É um veículo para o processamento de transações. � Uma tecnologia disponível na maioria das ferramentas ORACLE. � Uma extensão do SQL. � PL/SQL (Procedural Language/SQL) é uma extensão para o SQL,

incorporando várias facilidades das linguagens de programação existentes. O PL/SQL permite utilizar comandos para manipulação de dados e consultas em blocos de programação estruturados, fazendo do PL/SQL uma poderosa linguagem de processamento de transações.


IX.2 - AS VANTAGENS DO PL/SQL

Estrutura de Bloco PL/SQL é uma linguagem estruturada em blocos, onde cada bloco lógico contém recursos de linguagem requeridos pelo bloco. As variáveis podem ser definidas localmente nos blocos onde serão utilizadas e uma condição de erro pode ser tratada dentro deste mesmo bloco.

Controle de Fluxo Parâmetros condicionais (IF-THEN-ELSE-ELSIF), repetições de grupo de comandos e desvios podem ser empregados para controlar o fluxo de um programa.

Portabilidade O PL/SQL é nativo do ORACLE, portanto os programas PL/SQL podem ser transportados para qualquer ambiente que suportem o ORACLE.

Integração Pode-se utlitizar blocos PL/SQL desenvolvidos para uma ferramenta ORACLE e também para o ORACLE RDBMS.

Performance O uso do PL/SQL pode ajudar a melhorar a performance de uma aplicação. Os benefícios diferem dependendo do ambiente utilizado.


IX.3 - ESTRUTURA DO PL/SQL

� Toda unidade do PL/SQL é compreendida em um ou mais blocos. Estes

blocos podem estar completamente separados ou próximos. No entanto, um bloco pode representar uma pequena parte de um outro bloco.

DECLARE .... declaracoes BEGIN .... sentenças EXCEPTION .... manusear excessoes END;

� No geral, um bloco pode ser anônimo ou um sub-programa.

•••• Blocos anônimos - Basicamente, são blocos sem nome. Estes blocos são declarados em um ponto numa aplicação onde deverão ser executados e passados para o PL/SQL Engine na hora de sua execução. Blocos anônimos podem ser manuseados dentro de um programa pré-compilado, dentro do SQL*PLUS ou SQL*DBA. Trigggers no SQL*Forms consistem em blocos também.

•••• Sub-Programas - São blocos nomeados. Esles podem ser declarados

como uma Procedure ou Função. Em alguns casos, pode retornar valores quando executados.


IX.4 - DEFININDO UM BLOCO ANÔNIMO

DECLARE ... definição dos objetos PL/SQL que serão utilizados neste bloco BEGIN .... ações executáveis EXCEPTION .... o que fazer se um ação executada causar um erro END; � Os comandos BEGIN e END são obrigatórios, e entre eles ficará a

estrutura de comandos da aplicação para serem processados. � A sessão DECLARE é opcional e utilizada para definir os objetos PL/SQL,

como as variáveis, que serão referenciados no bloco ou nos blocos contidos nele. � A sessão EXCEPTION é utilizada para manusear condições de erro pré-

definidas e as ações a serem tomadas na sua ocorrência, esta sessão deve ser a última no bloco antes do END.


DECLARE quantidade_inv NUMBER(5); BEGIN SELECT quantidade INTO quantidade_inv FROM inventario WHERE produto = ‘BAMBA CABECAO’;

IF quantidade_inv > 0 THEN UPDATE inventário SET quantidade = quantidade -1 WHERE produto = ‘BAMBA CABECAO’;

INSERT INTO registro_de_compra VALUES ( ‘Tenis Bamba Cabecao comprado’, SYSDATE );

ELSE

INSERT INTO registro_de_compra

VALUES ( ‘Não tem tenis Bamba Cabecao’, SYSDATE );

END IF;

COMMIT;

EXCEPTION

WHEN no_data_found THEN INSERT INTO tabela_de_erros VALUES (‘Emitir Pedido de Compras para tenis Bamba Cabecao’);

END;

� Observe que as palavras DECLARE, BEGIN e EXCEPTION não são

precedida de ponto-e-vírgula. mas o END e todos outros comandos Pl/SQL requerem ponto-e-vírgula(;).


IX.5 - O AMBIENTE PL/SQL

� PL/SQL não é um produto Oracle inteiramente. Ele é uma tecnologia

empregada pelo RDBMS e para certas ferramentas Oracle. Blocos do PL/SQL são passados e processados pelo PL/SQL Engine, o qual pode residir em uma ferramenta ou dentro do RDBMS mesmo. Onde o PL/SQL Engine será utilizado, dependerá da ferramenta que pediu sua execução.


IX.6 - O PL/SQL ENGINE � O PL/SQL Engine executa comandos procedurais, mas envia os comandos

SQL para o SQL Executor dentro do Oracle RDBMS.


IX.7 - CARACTERÍSTICAS DO PL/SQL

Variáveis e Constantes Para armazenar e manipular valores. Os tipos incluem NUMBER, VARCHAR e DATE.

Suporte ao SQL SELECT, INSERT, UPDATE, DELETE, COMMIT, SAVEPOINT e ROLLBACK são comandos que são suportados pelo PL/SQL.

Dados Compostos É permitido a manipulação de grupos de campos para serem definidos e manipulados pelo bloco PL/SQL.

Controle de Fluxo Sentenças IF, repetições, desvios e nomes de parágrafos prevêem ações de condição, testes complexos, desvios e controles no fluxo dos programas.

Funções A maioria das funções de manipulação de dados do SQL estão disponíveis para manipular variáveis e valores.

Gerenciamento de Cursores Definição de cursores explícitos na memória permitindo o processamento de dados resultantes de consultas a várias linhas. Um grupo de atributos PL/SQL permitem que se teste o estado dos cursores implícitos e explícitos.

Gerenciando Exceções Condições de erros podem ser tratadas em cada bloco.


IX.8 - SINTAXE BÁSICA DO PL/SQL � Sabendo que o PL/SQL é uma extensão do SQL, geralmente as regras de

sintaxe que são aplicadas no SQL são aplicadas no PL/SQL. Aqui estão algumas regras:

• As sentenças de comandos podem ser divididas entre linhas, mas palavras-chave não podem ser divididas

• Palavras reservadas não devem ser utilizadas como identificadores, ao

menos que estejam entre aspas duplas (“UPDATE”) • Os identificadores devem começar com caracteres alfabéticos, e

podem contar até trinta caracteres • Literais do tipo data ou caracter devem estar dentro de aspas simples

(‘) • Literais numéricas podem ser representadas por valores (25,4) ou

notação científica (2e5 = 2 X 10 elevado a 5 = 200000) • Comentários podem estar entre /* e */ que pode se estender por muitas

linhas ou utilizar ‘-’ para comentar uma linha.


IX.9 - DECLARANDO VARIÁVEIS E CONSTANTES

� PL/SQL suporta uma variedade de tipos de dados, os quais podem ser

utilizados para definir Variáveis e Constantes. É opcional atribuir valores para variáveis, e esses valores podem ser modificados dentro de um bloco PL/SQL. Constantes são identificadores que contém um valor fixo e deve ser atribuído quando a constante for definida.

� O PL/SQL suporta os tipos de valores correspondentes aos tipos das

colunas em uma tabela Oracle como NUMBER, DATE, CHAR e BOOLEAN. � Atribuição de Valores � O PL/SQL permite que se atribua ou redefina valores a variáveis numa

seção DECLARATION de um bloco. A variável que receberá o novo valor deve sempre estar à esquerda do operador.

identificador := expressão;

contador := contador + 1; salario_anual := salario * 13 + NVL(comissao,0); nivel := 6; cargo := ‘JOGADOR’; data_de_hoje := SYSDATE; � Controle do Fluxo de Execução de um bloco PL/SQL


� PL/SQL tem facilidades para controlar o fluxo de comandos como:

• Sentenças IF

♦ Efetua um seletivo controle de ações baseado em condições

• Sentenças GO TO

♦ Efetua um desvio incondicional para um ponto determinado no

programa

• Repetições (LOOPs)

♦ Para repetições de ações sem uma condição imposta

• Sentenças FOR (LOOPs)

♦ Controla as repetições de ações utilizando um contador

• Sentenças WHILE (LOOPs)

♦ Controla as repetições de ações baseado em condições

• EXIT

♦ Termina uma repetição


IX.10 - SENTENÇA IF

� O comando IF permite que se execute ações de forma seletiva, baseado

em condições.

IF condicao THEN acoes

ELSIF condicao THEN acao

ELSE acao

END IF;

� Exemplo: declare v_sal number(10,2); v_ename varchar2(20); begin select ename,sal into v_ename,v_sal from emp where empno = 7369; if v_sal < 5000 then dbms_output.put_line('O '||v_ename||' recebe um salário razoável !!!'); elsif v_sal = 5000 then dbms_output.put_line('O '||v_ename||' recebe um bom salário!!!'); else dbms_output.put_line('O '||v_ename||' recebe um salário ótimo !!!'); end if; end;

IX.11 - SENTENÇA FOR


� O comando FOR permite que controle o número de vezes que as açoes

foram repetidas e define quando termina essa repetição. FOR variavel_de_controle IN REVERSE ( menor_valor .. maior_valor );

declare x number; begin for x in 1..10 loop dbms_output.put_line(x); end loop; end;


IX.12 - SENTENÇA WHILE

� O comando WHILE permite que se verifica uma condição a cada início de

execução de um grupo de ações.

WHILE condicao

declare x number := 0; begin while x < 10 loop dbms_output.put_line(x); x := x + 1; end loop; end;


IX.13 - O ATRIBUTO %TYPE

� O atributo %TYPE é utilizado para declarar um registro baseado numa

coluna de uma tabela ou visão. � Os registros são definidos na sessão DECLARE.

identificador tabela_de_referencia.campo%TYPE;

DECLARE v_ename emp.ename%TYPE;

BEGIN SELECT ename INTO v_ename FROM emp WHERE empno = 1234; ... END;


IX.14 - O ATRIBUTO %ROWTYPE � O atributo %ROWTYPE é utilizado para declarar um registro baseado

numa coleção de colunas de uma tabela ou visão. Os campos dentro dos registros receberão seus tipos de dados das colunas referenciadas.

� Os registros são definidos na sessão DECLARE.

identificador tabela_de_referencia%ROWTYPE;

DECLARE reg_fun emp%ROWTYPE;

BEGIN SELECT * INTO reg_fun FROM emp WHERE num_func = 1234; ... END;


IX.15 - O QUE É UM CURSOR? � O Oracle utiliza uma área de trabalho chamada ‘Private SQL Area’ (Área

Privativa do SQL) para executar comandos SQL e armazenar informações de processamento. Um cursor é uma construção PL/SQL que permite dar nomes a essas áreas de trabalho e acessar as informações armazenadas nela.

� Existem dois tipos de cursores:

•••• Cursores Implícitos - Declarados pelo PL/SQL implicitamente para todos os comandos DMLs e para consultas que retornarão apenas uma linha.

•••• Cursores explícitos - Declarados explicitamente, juntamente com

outros identificadores utilizados em um bloco, e manipulado através de comandos específicos dentro de um bloco. Esse tipo de cursor permite que se manipule várias linhas resultantes de uma consulta.

� Como temos vistos, os comandos SELECT que ocorrem como uma

cláusula separada dentro de um PL/SQL podem retornar apenas uma linha. Isto significa que PL/SQL executou duas procuras na tabela utilizando um cursor implícito: um satisfez a consulta e o outro não retornou nenhuma linha.

� Cursores explícitos permitem a execução desta segunda pesquisa

melhorando a eficiência. Esses cursores podem ser utilizados para executar várias consultas simples ou reexecutá-las.


IX.16 - CONTROLE SOBRE CURSORES EXPLÍCITOS

DECLARE Define nomes de cursores e definições de estrutura de pesquisa a serem executadas. Neste momento a consulta é definida mas não é executada.

OPEN Executa a pesquisa, populando as variáveis referenciadas. Linhas retornadas pela pesquisa, chamadas de “active set”, estão disponíveis para serem pesquisadas.

FETCH Faz a leitura dos valores da linha corrente colocando o resultado dentro das variáveis. A linha corrente é a linha que o cursor está apontando. Cada FETCH causa a movimentação do cursor para a próxima linha no “active set” .

CLOSE Libera a área de trabalho que as linhas produziram pela última execução do comando OPEN.


IX.17 - O COMANDO CURSOR

� O comando CURSOR é utilizado para definir um cursor explícito.

Parâmetros podem ser definidos para permitir a substituição de valores dentro de uma pesquisa quando o cursor é inicializado (OPEN).

CURSOR identificador (detalhes sobre parametros) IS sentença_de_consulta;

DECLARE CURSOR C1 IS SELECT ename, sal, hiredate FROM emp WHERE deptno = 20 AND job = ‘ANALYST’;

� O comando OPEN OPEN identificador_do_cursor ( lista de argumentos );

OPEN C1;

� O comando FETCH

FETCH identificador_do_cursor INTO variavel1, variavel2,... ; FETCH C1 INTO nome_fun_var, salario_var, data_adm_var;

� O comando CLOSE


CLOSE identificador_do_cursor ;

CLOSE C1;


IX.18 - CURSORES E REGISTROS

� Já vimos que podemos definir registros com a mesma estrutura de colunas

de uma tabela. É também possível definir um registro baseado numa lista selecionada de colunas num cursor explícito. Isto é conveniente para processar as linhas no “active set”, podendo assim simplificar os FETCH para os registros, e os valores das linhas são armazenados diretamente dentro dos correspondentes campos nos registros. DECLARE CURSOR C1 IS SELECT empno, sal, hiredate, rowid FROM emp WHERE deptno = 20 AND job = ‘ANALYST’ FOR UPDATE OF sal; registro_emp c1%ROWTYPE; BEGIN OPEN C1; .... FETCH C1 INTO registro_emp; ... IF registro_emp.sal < 2000 THEN ...

Cláusula WHERE CURRENT OF � Quando se está referenciando a linha corrente de um cursor explícito, os

comandos SQL podem utilizar a cláusula WHERE CURRENT OF, dando o nome do cursor. Isto permite que atualizações ou remoções possam ser aplicadas na linha corrente, sem a necessidade de especificar a coluna ROWID. Para tanto, deve-se incluir a cláusula FOR UPDATE na sentença de consulta do cursor e as linhas selecionadas estarão “locked”.


IX.19 - CURSOR FOR LOOPS

� PL/SQL contém um tipo especial do comando FOR para repetir o processo

de retornar consulta utilizando um cursor explícito. Num Cursor FOR Loop, o cursor é declarado, aberto (OPEN) e fechado (CLOSE) automaticamente. Cada interação da repetição faz com que o comando FETCH seja executado automaticamente. DECLARE CURSOR nome_do_cursor ( parametros ) IS sentença_de_pesquisa; BEGIN FOR nome_do_registro IN nome_do_Cursor ( parametros ) LOOP ... END LOOP; DECLARE CURSOR C1 IS SELECT n1, n2, n3 FROM tabela; resultado NUMBER; BEGIN FOR rec IN C1 LOOP resultado := rec.n2 / ( rec.n1 + rec.n3); INSERT INTO tabela_tmp VALUES ( resultado ); END LOOP; COMMIT; END;


PROCEDURAL OPTION


OBJETIVOS: Ao final do curso os treinados estarão aptos para:

� Desenvolver aplicações modularizadas utilizando procedures e funções

armazenadas. � Gerenciar a construção de programas documentando, analisando

problemas em tempo de execução, desenvolver scripts no SQL*Plus e controlar a segurança.

� Gerenciar as dependências entre objetos de banco de dados e

construção de programas. � Agrupar procedures e funções, compartilhando identificadores entre si,

criando packages. � Complementar as capacidades do Oracle Server utilizando triggers de

banco de dados. � Integrar a construção de programas através do desenvolvimento de

uma aplicação no nível do banco de dados.


Diferenças entre procedures de banco de dados e de Oracle Forms

Não confunda procedures de banco de dados com Oracle Forms, PL/SQL e rotinas built-in. Procedures Oracle Forms Procedures Armazenadas no banco de dados Armazenadas dentro da aplicação Forms Documentadas no dicionário de dados Documentada dentro da aplicação Forms Executada de qualquer ferramenta ou Executando somente de aplicações aplicação de banco de dados Forms Pode referenciar apenas procedures de Pode referenciar Forms de banco de dados dados A segurança de utilização da procedure A segurança é feita no nível é gerenciada pelo banco de dados de aplicação Forms

Passos para desenvolver um procedure ou função. Editor do sistema ----------- EDIÇÃO ---------- Arquivo Texto Armazenada no Banco de Dados Código Fonte Compilação Código compilado Execução


Criando Procedures Criar uma nova procedure com o comando CREATE PROCEDURE. Com uma lista de argumentos, e definindos as ações que serão executadas por um bloco PL/SQL.

Sintaxe:

CREATE OR REPLACE PROCEDURE nome_da_procedure( argumento mode tipo_do_argumento) IS/AS bloco_PL/SQL;

Argumento � é o nome de uma variável PL/SQL passada para a procedure

Mode � identifica o tipo de argumento ( IN/OUT/IN OUT) Tipo do argumento

� tipo do dado ( datatype)

Bloco PL/SQL � é o corpo da procedure que define as ações que

serão executadas quando a procedure for executada

IS ou AS � essas cláusulas são equivalentes, pode-se

utilizar tanto uma quanto outra

Obs.: A cláusula REPLACE é utilizada quando a procedure já existe. Nunca utilize a cláusula DECLARE no início do bloco PL/SQL.

Trocando valores entre diferentes ambientes através de argumentos. Somente um dos três modos pode ser escolhido para cada argumento (IN,OUT,IN OUT).

IN argumento � Passa o valor do ambiente chamador para a procedure(default).

OUT argumento

� Retorna um valor da procedure para o ambiente chamador.

IN OUT argumento

� Passa um valor do ambiente chamador para a procedure, e a procedure retorna um valor para o ambiente chamador.

Como em qualquer bloco PL/SQL, armazene valores para as procedure em variáveis locais.


Passando valores do ambiente chamador para uma procedure utilizando o argumento IN

Exemplo : Criando uma procedure com o argumento IN. Armazene todas as informações de um novo empregado.

CREATE OR REPLACE PROCEDURE novos_empregados ( v_emp_no IN emp.empno%TYPE, v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_hiredate IN emp.hiredate%TYPE, v_emp_sal IN emp.sal%TYPE, v_emp_comm IN emp.comm%TYPE, v_dept_no IN emp.deptno%TYPE) IS BEGIN INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm,

deptno ) VALUES ( v_emp_no, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_dept_no); COMMIT WORK; END novos_empregados;

Retornando um valor da procedure para o ambiente chamador utilizando o argumento OUT

Exemplo : Criando uma procedure com o argumento OUT Devolver informações sobre um funcionário.

CREATE OR REPLACE PROCEDURE pesquisa_empregado ( v_emp_no IN emp.empno%TYPE, v_emp_name OUT emp.ename%TYPE, v_emp_sal OUT emp.sal%TYPE, v_emp_comm OUT emp.comm%TYPE) IS BEGIN SELECT ename, sal, comm INTO v_emp_name, v_emp_sal, v_emp_comm FROM emp WHERE empno = v_emp_no; END pesquisa_empregado;


Passando um valor do ambiente chamador para a procedure, e a procedure retorna um valor para o ambiente chamador

Exemplo: Criando uma procedure com um argumento IN/OUT.

Transforme a seqüência de sete dígitos de um número para o formato de número de telefone.

CREATE OR REPLACE PROCEDURE formata_telefone (v_phone_no IN OUT varchar2) IS BEGIN v_phone_no := SUBSTR (v_phone_no,1,3) || ‘-’ || SUBSTR (v_phone_no,4,4); END formata_telefone;

Criando Funções

Para criar uma função ou procedure dependerá de que forma será chamada e de que forma espera-se os valores.

Criar uma nova função com o comando CREATE FUNCTION, a qual declara uma lista de argumentos, declara o argumento que irá retornar e define as ações que serão realizadas utilizando blocos PL/SQL. Sintaxe: CREATE OR REPLACE FUNCTION nome_da_funcao ( argumento mode tipo_do_argumento) RETURN tipo_do_dado IS/AS bloco_pl/sql RETURN Identifica o tipo do dado que a função irá retornar.


Retornar um valor de uma função para o ambiente chamador utilizando a cláusula RETURN

Exemplo : Criando uma função.

CREATE OR REPLACE FUNCTION pesquisa_salario ( v_emp_no IN s_emp.empno%TYPE) RETURN NUMBER IS v_emp_sal emp.sal%TYPE := 0; BEGIN SELECT sal INTO v_emp_sal FROM emp WHERE empno = v_emp_no; RETURN (v_emp_sal); END pesquisa_salario;

Gerenciando exceções em tempo de execução

Pode-se gerenciar qualquer tipo de exceção em tempo de execução permitindo propagar para o ambiente chamador ou tomar ações quando essas acontecerem.

RAISE_APPLICATION_ERROR(numero_erro, texto_erro)

Numero_erro � É o número do erro definido pelo usuário. Deve estar entre -20000 e -20999.

Texto_erro � É a mensagem definida pelo usuário.

CREATE OR REPLACE PROCEDURE exclui_funcionario (v_emp_no IN

emp.empno%TYPE) IS BEGIN DELETE FROM EMP WHERE empno = v_emp_no; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20200, ‘Funcionario nao existe’); END IF; COMMIT WORK; END exclui_funcionario;


Executando uma operação no banco de dados, como ROLLBACK, ou customizando uma mensagem de erro para uma exceção ORACLE pode-se tratar essas exceções no bloco EXCEPTION. CREATE OR REPLACE PROCEDURE exclui_funcionario

(v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_sal IN emp.sal%TYPE) IS v_emp_hiredate emp.hiredate%TYPE; v_emp_comm emp.comm%TYPE; v_dept_no emp.deptno%TYPE; BEGIN SELECT deptno INTO v_dept_no FROM emp WHERE empno = v_mgr_no; INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno ) VALUES (s_empno.NEXTVAL, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_dept_no); COMMIT WORK; EXCEPTION WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR (-20201, ‘Gerente nao e um empregado valido.’); END exclui_funcionario;


Pode-se também definir exceções que não são contempladas pelo ORACLE. declare uma exceção associe essa exceção com um número de erro utilizando com comando PRAGMA EXCEPTION_INIT.

CREATE OR REPLACE PROCEDURE exclui_funcionario (v_emp_name IN emp.ename%TYPE, v_emp_job IN emp.job%TYPE, v_mgr_no IN emp.mgr%TYPE, v_emp_sal IN emp.sal%TYPE, v_emp_hiredate emp.hiredate%TYPE, v_emp_comm emp.comm%TYPE, v_dept_no emp.deptno%TYPE) IS e_invalid_manager EXCEPTION; PRAGMA EXCEPTION_INIT (e_invalid_manager, -2291); BEGIN INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (s_empno.NEXTVAL, v_emp_name, v_emp_job, v_mgr_no, v_emp_hiredate, v_emp_sal, v_emp_comm, v_dept_no); COMMIT WORK; EXCEPTION WHEN e_invalid_manager THEN RAISE_APPLICATION_ERROR (-20201, ‘Gerente nao e um empregado valido.’); END exclui_funcionario;

Após escrever uma procedure ou função num arquivo de comandos do SQL*Plus, execute o arquivo de comando para salvar a função ou procedure no banco de dados. Gerencie as funções e procedimentos armazenados no banco de dados com os seguintes comandos :

CREATE OR REPLACE PROCEDURE/FUNCTION

DROP PROCEDURE/FUNCTION


Executando PROCEDURES

De qualquer ambiente PL/SQL, basta simplesmente chamar a procedure com uma chamada direta.

DECLARE v_empno NUMBER := 7654; BEGIN ... exclui_funcionario (v_empno); ... END;

Executando de outra procedure.

CREATE PROCEDURE processa_funcionario (v_emp_no IN emp.empno%TYPE) IS BEGIN ... exclui_funcionario (v_empno); ... END;

Executando do SQL*Plus ou do SQL*DBA. Para isso, é necessário utilizar o comando EXECUTE.

Para entrar com valores via SQL*Plus deve-se utilizar o comando ACCEPT e substituir o parâmetro de entrada da procedure pela variável do ACCEPT iniciado pela string &.

ACCEPT p_empno PROMPT ‘Entre com o numero do funcionario : ‘ EXECUTE exclui_funcionario (&p_empno);


Executando de um Pré-Compilador ou aplicação OCI. void exec_exclui_func () ( EXEC SQL BEGIN DECLARE SECTION; int empno; EXEC SQL END DECLARE SECTION; printf(‘\nEntre com o numero do funcionario:’ ); scanf(‘%d,&empno’); EXEC SQL EXECUTE; begin exclui_funcionario(:empno); end;

EXEC SQL END-EXEC; return; )

De acordo com o ambiente chamador, pode-se executar procedures de um outro usuário (schema) ou de um outro banco de dados.

Exemplo: Executando uma procedure de outro usuário. SQL> EXECUTE Luiz.exclui_funcionario (7654);

Exemplo: Executando uma procedure de outro banco de dados. SQL> EXECUTE Luiz.exclui_funcionario (7654)@pr;

Para uma procedure que contenha vários argumentos, existe três métodos

para especificar seus valores :

Posicional Lista valores na ordem em que foram declarados

SQL> EXECUTE novos_funcionarios ( ‘LUIZ’,’ANALISTA’,7566,3000);

Nomeados � Lista valores em ordem arbitrária, associando cada valor com o nome do argumento utilizando uma sintaxe especial

SQL> EXECUTE novos_funcionarios ( v_emp_sal => 3000, - v_mgr_no=>7566, v_mp_name=>‘LUIZ’,v_emp_job=>’ANALISTA’);

Combinação � Lista os primeiros valores de forma posicional e o restante nomeando

SQL> EXECUTE novos_funcionarios ( ‘LUIZ’,’ANALISTA’, -v_emp_sal=>3000,v_mgr_no=>7566); As maneiras de executar uma função são as mesmas utilizadas para executar uma procedure. Mas lembre-se que uma função retorna um único valor.


Executando uma função de um bloco PL/SQL

DECLARE v_empno NUMBER := 7654; v_sal NUMBER; BEGIN ...

v_sal := pesquisa_salario(v_emp_no); ... END;

Executando uma função de uma procedure CREATE PROCEDURE processa_emp (v_emp_no IN emp.empno%TYPE) IS v_sal NUMBER; BEGIN ... v_sal := pesquisa_salario(v_emp_no); ... END; No SQL*Plus, o valor retornados de uma função é armazenado em variável global.

Executando uma função do SQL*Plus

ACCEPT p_empno PROMPT ‘Entre com o numero do funcionario : ‘ VARIABLE g_sal NUMBER

EXECUTE :g_sal := pesquisa_salario (&p_empno);

PRINT g_sal


Executando uma função de um Pré-compilador ou de uma aplicação OCI

void exec_pesquisa_salario () ( EXEC SQL BEGIN DECLARE SECTION; int empno; float sal; EXEC SQL END DECLARE SECTION; printf (“\nEntre com o numero do funcionario : “); scanf (“%d”, &empno); EXEC SQL EXECUTE; begin :sal := pesquisa_salario(:empno); end; EXEC SQL END-EXEC; printf (“\nO salario e : %7.2f”, sal); return; )


Benefícios das Procedures e Funções

Adicionando benefícios de modularização das aplicações, e muitos outros benefícios de poder armazená-las no banco de dados estão os benefícios abaixo:

Melhorar a segurança e integridade dos dados

• Controle sobre acessos indiretos aos objetos de banco de dados

executados por funcionários sem privilégios. • Assegurar que ações relacionadas sejam executadas conjuntamente.

Melhorar performance

• Reduzir o número de chamadas ao banco de dados e diminuir o tráfico

na rede. • Compartilhar execuções SQL por vários usuários.

Conservar a memória

• Armazenar uma única cópia do fonte no banco de dados ao invés de várias cópias espalhadas em diferentes aplicações.

• Compartilhar SQL ao invés de vários cursores para diferentes aplicações.

Melhorar manutenções

• Modificar rotinas on-line sem interferir com outros usuários • Modificar uma rotina que afetará várias aplicações


Gerenciando Procedures e Funções

Uma procedure ou função armazenada em qualquer estágio do desenvolvimento pode ser revisada. Obter informações sobre documentação e erros de compilação de várias visões do dicionário de dados e comandos interativos. Obter informações em tempo de execução sobre o processo utilizando procedures fornecidas pela Oracle. Informações Descrição Método de acesso Código fonte Texto da procedure Visão do dicionário de dados USER_SOURCE ou pelo comando DECRIBE Código Objeto Código compilado Não há Erro Compilação Erros de sintaxe Visão do dicionário de dados USER_ERRORS ou pelo comando SHOW ERRORS Informações Mensagens e Procedures DBMS_OUTPUT sobre execução variáveis especificadas pelo usuário Para permitir que os usuários possam executar uma procedure ou função, deve ser alterado o privilégio de segurança.


Documentando Procedures e Funções

Obtém-se os nomes das procedures e funções armazenadas de um usuário através da visão do dicionário de dados USER_OBJECTS.

Coluna Descrição

OBJECT_NAME Nome do objeto OBJECT_ID Identificador interno do objeto OBJECT_TYPE Tipo do objeto (PROCEDURE, FUNCTION, PACKAGE ou PACKAGE BODY) CREATED Data da criação do objeto LAST_DDL_TIME Data de modificação do objeto

TIMESTAMP Data de recompilação do objeto

STATUS VALID ou INVALID Para saber algumas informações adicionais, como quem é o proprietário do objeto, pesquise as visões do banco de dados ALL_OBJECTS e DBA_OBJECTS.

Exemplo : Exibir todas as procedure e funções.

SELECT object_name, object_type FROM user_objects WHERE type IN (‘PROCEDURE’, ‘FUNCTION’) ORDER BY object_name; Obtendo o texto das procedures e funções armazenadas através da visão do dicionário de dados USER_SOURCE.

Coluna Descrição

NAME Nome do objeto TYPE Tipo do objeto (PROCEDURE, FUNCTION, PACKAGE

ou PACKAGE BODY) LINE Número de linha do código fonte TEXT Texto da linha do código fonte

Para saber algumas informações adicionais, como quem é o proprietário do

objeto, pesquise as visões do banco de dados ALL_SOURCE e DBA_SOURCE. EXEMPLO : Documentando uma procedure armazenada.

SELECT text FROM user_source WHERE type = ‘PROCEDURE’


AND name = ‘EXCLUI_FUNCIONARIO’ ORDER BY line;


Obtendo os erros de compilação através da visão do dicionário de dados USER_ERRORS

Coluna Descrição

NAME Nome do objeto TYPE Tipo do objeto (PROCEDURE, FUNCTION, PACKAGE

ou PACKAGE BODY) LINE Número de linha do código fonte onde ocorreu o erro POSITION Posição da linha que o erro ocorreu

TEXT Texto da mensagem de erro Para saber algumas informações adicionais, como quem é o proprietário do objeto, pesquise as visões do banco de dados ALL_ERRORS e DBA_ERRORS.

Exemplo : Exibir os erros de sintaxe.

COL pos FORMAT a4 COL text FORMAT a60 TRUNC SET SPACE 2 SELECT line || ‘/’ || position POS, text FROM user_errors WHERE type = ‘PROCEDURE’ AND name = ‘LOG_EXECUTION’ ORDER BY line; Exibir os erros de compilação interativamente utilizando o comando SHOW ERRORS através do SQL*Plus ou SQL*DBA. SHOW ERRORS PROCEDURE log_execucao


Analisando execução de procedures e Funções

Os valores e mensagens de saída de procedure e funções armazenadas com as procedures DBMS_OUTPUT fornecidas pela Oracle. Categoria DBMS_OUTPUT Descrição Operação de PUT Acrescenta texto de uma

procedure saída para a linha corrente de um buffer de saída

NEW_LINE Coloca um marco de fim de linha no buffer de saída

PUT_LINE Combina as ações do

PUT e NEW_LINE Operação de GET_LINE Retorna a linha corrente

de um entrada buffer de saída para a procedure

Retorna a linha corrente

de um entrada buffer de saída para a procedure

GET_LINES Retorna um conjunto de linhas de um buffer de saída para a procedure

Outros ENABLE Habilita as procedures

DBMS_OUTPUT DISABLE Desabilita as procedures

DBMS_OUTPUT

O script dbmsotpt.sql deve ser executado pelo administrador do banco de dados utilizando a conta sys para disponibilizar as procedures DBMS_OUTPUT.


Disponibilizando as procedures DBMS_OUTPUT do SQL*Plus ou SQL*DBA com a opção SERVEROUTPUT

Saída de valores e mensagens dentro do SQL*Plus 1. Habilite a opção SERVEROUTPUT SET serveroutput ON 2. Prepare o texto da mensagem de saída com a procedure PUT DBMS_OUTPUT.PUT(‘texto’); 3. Exiba as mensagem de saída na tela com a procedure NEW_LINE DBMS_OUTPUT.NEW_LINE;

Exemplo : Examinando uma função CREATE OR REPLACE FUNCTION average_sal (v_n IN NUMBER) RETURN NUMBER IS CURSOR emp_cursor IS SELECT empno, sal FROM emp ORDER BY sal DESC; v_total_sal emp.sal%type := 0; v_counter NUMBER; BEGIN FOR r_emp IN emp_cursor LOOP EXIT WHEN emp_cursor%ROWCOUNT > v_n; v_total_sal := v_total_sal + r_emp.sal; v_counter := emp_cursor%ROWCOUNT; DBMS_OUTPUT.PUT (‘Loop = ‘); DBMS_OUTPUT.PUT (v_counter); DBMS_OUTPUT.PUT (‘; Empno = ‘); DBMS_OUTPUT.PUT (r_emp.empno); DBMS_OUTPUT.NEW_LINE; END LOOP; RETUR (v_total_sal/v_counter); END average_sal;

SQL> SET verify OFF

SQL> SET serveroutput ON

SQL> DEFINE p_n = 3

SQL> VARIABLE g_average NUMBER


SQL> EXECUTE :g_average := average_sal (&p_n);


Produzir arquivos de comandos SQL*Plus para facilitar o desenvolvimento de procedures

Sugestões para a criação de um script SQL*Plus

• Pesquise no dicionário de dados se já existe esta procedure • Prepare a procedure • Teste a procedure

Controle de Segurança

Obter os privilégios necessários para exeutar operações com uma procedure ou função armazenada. Operação Privilégio E... CREATE CREATE PROCEDURE Acesso a todos os objetos system privilege referenciados pela ou procedure CREATE ANY PROCEDURE system privilege CREATE OR REPLACE Proprietário da procedure ou CREATE PROCEDURE system privilege ou CREATE ANY PROCEDURE system privilege DROP Proprietário da procedure ou DROP ANY PROCEDURE system privilege EXECUTE Proprietário da procedure ou EXECUTE object privilege ou


EXECUTE ANY PROCEDURE system privilege

Proteger os dados dos objetos referenciados pela procedure, disponibilizando aos usuários somente acesso indireto

Segurança da procedure

• Uma procedure é executada sobre o dominio de segurança de seu

dono. • Para acessar dados através de uma procedure, o usuário tem que ter

privilégio para executar a procedure, e não necessariamente ter acesso ao objeto referenciado por ela.

Exemplo : Disponibilizando acesso indireto ao dado. Provendo acesso da procedure a tabela emp.

SQL> GRANT SELECT, INSERT 2 ON emp 3 TO Luiz;

Provendo acesso a outro usuário diferente do propietário (Luiz). Este acesso deve ser disponibilizado pelo proprietário.

SQL> GRANT EXECUTE 2 ON modify_emp 3 TO pedro;

Gerenciando Procedures e Funções

Para gerenciar as procedures ou funções armazenadas durante o ciclo de desenvolvimento, utilize as visões do dicionário de dados, os comandos SQL e as procedures fornecidas pela Oracle.

Tarefa Estratégia

Obter documentação Exibir as visões do dicionário de dados USER_OBJECTS e USER_SOURCE

Eliminar erros de Pesquisar a visão do dicionário de dados compilação USER_ERRORS

Gerenciando problemas Explorar as rotinas DBMS_OUTPUT prividas pela

de execução Oracle

Facilitar o Produzir scripts SQL*Plus


desenvolvimento

Controlar a segurança Definir privilégios para o proprietário e usuários

Gerenciando as Dependências entre Procedures

Entendendo as dependência que existe quanto as alterações nas definições dos objetos de banco de dados. Procedure View Tabela INVALID INVALID Procedure Procedure Alterações na definição INVALID INVALID

Para validar uma procedure ou função deve-se recompilar as mesmas.


Existem dois tipos distintos de dependência: direta e indireta

Uma procedure ou função depende diretamente de um objeto de banco de dados se este está sendo referenciado dentro do corpo da procedure ou função.

Objeto dependente Objeto referenciado Diretamente

Procedure ou função Tabela

Procedure ou função Visão

Procedure ou função Sequência

Procedure ou função Procedure ou função Uma procedure ou função depende indiretamente de um objeto de banco de dados se este é referenciado por um objeto intermediário. Objeto dependente Objeto intermediário Objeto referenciado indiretamente Procedure ou função Visão Tabela Procedure ou função Visão Visão Procedure ou função Procedure ou função Veja a tabela de referência direta Dependência remota Tipo da dependência Descrição Local Os objetos que estão no mesmo node, o Oracle examina as dependências e recompila

automaticamente os objetos dependentes Remoto Os objetos estão em nodes diferentes, o Oracle


não consegue examinar as dependências


Dependências Locais

Determinar quais objetos de banco de dados devem ser recompilados manualmente através da visão do dicionário de dados USER_DEPENDENCIES

Coluna Descrição

NAME Nome do objeto dependente

TYPE, Tipo do objeto dependente(PROCEDURE, FUNCTION, PACKAGE ou PACKAGE BODY)

REFERENCED_OWNER Usuário do objeto referenciado

REFERENCED_NAME Nome do objeto referenciado

REFERENCED_TYPE Tipo do objeto referenciado

Também examina as visões ALL_DEPENDENCIES e DBA_DEPENDENCIES para saber quem é o proprietário do objeto.

Exemplo : Exibindo dependêncas diretas SELECT name, type, referenced_name, referenced_type FROM user_dependencies WHERE referenced_name IN (‘EMP’, ‘NEW_EMP’);

Exibindo dependências indiretas através de tabelas adicionais

providas pela Oracle chamadas DEPTREE e IDEPTREE. 1. Criar as tabelas DEPTREE e IDEPTREE com a conta do usuário executando o script utldtree.sql. 2. Popular as tabelas DEPTREE e IDEPTREE com as informações de um objeto específico executando a procedure DEPTREE_FILL DEPTREE_FILL (‘tipo_do_objeto’, ‘proprietário_do_objeto’, ‘nome_do_objeto’);


3. Exiba uma representação tabular de todos os objetos dependentes, direta ou indiretamente, pesquisando a tabela DEPTREE.

Coluna Descrição

NESTED_LEVEL Graduação da referência indireta

TYPE Tipo da dependência

OWNER Proprietário do objeto dependente

NAME Nome do objeto dependente

SEQ# Número de ordenação de linhas para saída 4. Exibir a árvore com as mesmas informações pesquisano a visão IDEPTREE, que contém uma única coluna chamada DEPENDENCIES.

Exemplo : Exibir as dependências indiretas SQL> EXECUTE DEPTREE_FILL (‘TABLE’, ‘LUIZ’, ‘EMP’);

Exemplo : exibir todas as dependências, diretas e indiretas SQL> SELECT nested_level, type, name FROM deptree ORDER BY seq#;

Exemplo : exibir a árvore das dependências diretas e indiretas

Após alterar um objeto o banco de dados, recompile todas as suas funções e procedures dependentes.

Sintaxe : Recompilando uma procedure

ALTER PROCEDURE nome_da_procedure COMPILE ALTER FUNCTION nome_da_função COMPILE Para recompilar uma procedure o usuário deve ser dono da procedure ou ter o privilégio ALTER ANY PROCEDURE.


Objeto Referenciado Exemplo de Alteração Resultado da compilação Tabela Tabela removida Erros Tabela Tabela renomeada Erros Tabela Coluna adicionada Sem erros Tabela Tipo da coluna Sem erros se a coluna está referenciada por %ROWTYPE ou %TYPE Procedure Lista de argumento Erros Procedure PL/SQL Sem erros Visão Nome das colunas Erros

Verificar se a recompilação foi bem sucedida através da visão do dicionário de dados USER_OBJECTS, coluna STATUS.

SQL> SELECT object_name, object_type, status 2 FROM user_objects 3 WHERE object_type = ‘PROCEDURE’;

Note que uma alteração de segurança também marca objetos dependentes como inválidos. Objeto Referenciado Exemplo de Alteração Resultado da compilação Tabela Relevante privilégio Erros removido Tabela Não relevante Sem erros privilégio removido As dependências de segurança podem ser examinadas na visão do dicionário de dados USER_TAB_GRANTS.


Gerenciando Dependências Locais

O Oracle recompila objetos invalidados automaticamente quando eles são chamados e antes de serem executados.

Mecanismo de Dependência Local Automático.

1. O Oracle examina dentro do dicionário de dados os relacionamentos de dependência de todos os objetos, tanto diretos quanto indiretos. 2. O Oracle registra dentro do dicionário de dados a situação de cada objeto ( VALID ou INVALID)

• VALID indica que o objeto foi compilado com sucesso e está pronto para ser executado.

• INVALID indica que o objeto precisa ser recompilado antes de ser

executado. 3. Se a existência ou especificação de um objeto de banco de dados alterar, o Oracle marca todos os objetos dependentes como INVALID. 4. Quando uma procedure inválida é chamada em tempo de execução, o Oracle tenta recompilá-lo primeiramente.

• Se a procedure compilar com sucesso, o Oracle altera a situação para VALID e executa a procedure.

• Se a recompilação falhar, o Oracle não altera a situação da procedure e mostra um erro de execução.

5. O Oracle valida os objeto do banco de dados recursivamente É recomendado recompilar as dependências locais manualmente, ao invés de deixar que o Oracle faça a recompilação automática. Assim diminuindo os riscos de interrupções no processo em produção. Quando um objeto de banco de dados é dependente de objetos em diferentes bancos de dados, certifique-se em avisar aos proprietários quando ocorrerem alterações. O Oracle não pode examinar relacionamentos de dependências remotas. O usuário notificado das alterações ocorridas deve recompilar os objetos dependentes manualmente, em alguns casos, esperar pelo mecanismo de dependência remota automático.


Referência Remota Recompilação Manual Recompilação Automática

Procedure ou função Disponível Disponível Tabela, visão ou Disponível Não disponível sequência É recomendado recompilar as dependências remotas manualmente, ao invés de deixar que o Oracle faça a recompilação automática. Assim diminuindo os riscos de interrupções no processo em produção. Os mecanismos automáticos remotos e locais são diferentes. Quando uma procedure local referencia uma procedure remota ou função, o Oracle invalida a procedure local se o Oracle detecta que a procedure remota foi recompilada recentemente.

Mecanismo de Dependência Remota Automático

1. O Oracle registra o ‘timestamp’ dentro do código objeto de todas as procedures quando compilada.

• Quando a procedure remota é recompilada, o Oracle registra o timestamp dentro do código objeto da procedure remota.

• Quando a procedure local é recompilada, o Oracle coloca no código

objeto o timestamp da proceure remota. 2. Quando uma procedure local é chamada em tempo de execução, o Oracle compara os timestamps.

• Se os timestamps são iguais, indicando que a procedure remota não foi recompilada e executa a procedure.

• Se os timestamps não são iguais, indicando que a procedure remota

foi recompilada, o Oracle invalida a procedure local e retorna uma erro em tempo de execução.

3. Se a procedure local, que está marcada como INVALID, é chamada pela segunda vez, o Oracle recompilará a procedure antes de executá-la de acordo com o mecanismo de dependência local automático.

• Assumindo qua não há outros erros de compilação, o Oracle insere o novo timestamp no código objeto da procedure local e executa-a. • Se existirem outros erros de compilação, a situação da procedure

permanece inalterado (INVALID) e o Oracle retorna um erro. Desenvolva uma estratégia para chamar novamente uma procedure local se na primeira vez retornou um erro indicando que os timestamps não estão compatíveis.


Desenvolvendo e Utilizando PACKAGES PACKAGE

Variáveis Cursor Constante Excessões PROCEDURE FUNÇÃO

Grupo de identificadores de programação e rotinas armazenadas

juntas como um pacote ( package ). Construção Descrição Variáveis Identificador que armazena valores atualizáveis Cursor Identificador associado a comandos SQL Constantes Identificador que armazena valores fixos Excessões Identificador de uma condição anormal Procedure Rotina de argumentos Função Rotina com argumentos que retorna somente um valor



Especificação do Package e seu corpo PACKAGE Variável Pública Procedure A Procedure Declaração Pública Especificação PACKAGE Variável Privada Procedure B Procedure Definição Privada Procedure A Procedure Definição Pública Variável Local Corpo do Package Criar um Package em duas partes : a especificação do package e o seu corpo. Faça algumas construções de packages públicas declarando-as dentro da especificação do package; faça outra construção privada declarando somente dentro do corpo do package. Escopo da Descrição Dentro do Package Construção

Pública Disponibiliza as procedures Declarada dentro da e funções para fora do especificação do package e package definida no corpo do package

Privada Não disponibiliza as Declarada e definida procedures e funções para dentro do corpo do package


fora do package Os passos básicos para desenvolver um package são similares aos

de desenvolver uma procedure “stand-alone”. Desenvolvendo um Package

1. Escreva o texto do comando CREATE PACKAGE dentro de um arquivo texto para criar a especificação do package. 2. Escreva o texto do comando CREATE PACKAGE BODY dentro de um arquivo texto para criar o corpo do package. 3. Execute os dois comandos, o qual compilará o código fonte em objeto e armazenará dentro do banco de dados, do SQL*Plus ou SQL*DBA. 4. Chame qualquer construção pública dentro do package de um ambiente Oracle. É recomendado que salve o texto do comando CREATE PACKAGE e do CREATE PACKAGE BODY em dois diferentes arquivos para facilitar posteriores modificações.

Criando PACKAGES

Declarando construções públicas dentro da especificação do package. Sintaxe:

CREATE OR REPLACE PACKAGE nome_do_package IS/AS -- declaracao_de_variaveis -- -- declaracao_de_cursores -- -- declaracao_de_excessoes -- -- declaracao_de_procedures -- -- declaracao_de_funcoes -- END nome_do_package Especifique a opção REPLACE quando a especificação do package já existir.

Exemplo : Criando a especificação do package

CREATE OR REPLACE PACKAGE comm_package IS g_comm_rate NUMBER := 0.1; PROCEDURE reset_comm_rate (v_comm_rate IN NUMBER);


END comm_package; Definir todas construções, públicas e privadas, dentro do corpo do

package. Sintaxe :

CREATE OR REPLACE PACKAGE BODY nome_do_package IS/AS -- declaracao_de_variaveis -- -- declaracao_de_cursores -- -- declaracao_de_excessoes -- -- declaracao_de_procedures -- -- declaracao_de_funcoes -- END nome_do_package Especifique a opção REPLACE quando o corpo do package já existir.

Exemplo :

CREATE OR REPLACE PACKAGE BODY comm_package IS FUNCTION validade_comm_rate (v_comm_rate IN NUMBER) RETURN BOOLEAN IS v_max_comm_rate NUMBER; BEGIN SELECT MAX (comm/sal) INTO v_max_comm_rate FROM emp; IF v_comm_rate > v_max_comm_rate THEN RETURN (FALSE); ELSE RETURN (TRUE); END IF; END validade_comm_rate; PROCEDURE reset_comm_rate (v_comm_rate IN NUMBER) IS v_valid BOOLEAN; BEGIN v_valid := validade_comm_rate (v_comm_rate); IF v_valid = TRUE THEN g_comm_rate := v_comm_rate; ELSE RAISE_APPLICATION_ERROR (-20210, ‘Taxa de comissao invalida’);


END IF; END reset_comm_rate; END comm_package;


Armazenado Packages Gerenciando packages armazenadas dentro do banco de dados com comandos similares aos utilizados nas procedures. Tarefa Comando Criar uma nova especificação de package CREATE PACKAGE Criar um novo corpo do package CREATE PACKAGE BODY Modificar uma especificação de package CREATE OR REPLACE PACKAGE Modificar um corpo de package existente CREATE OR REPLACE PACKAGE

BODY Remover a especificação e o corpo do package DROP PACKAGE Remover somente o corpo do package DROP PACKAGE BODY É necessário modificar ou remover o corpo do package sempre que modificar ou remover a especificação do package.

Executando Packages Depois de armazenar o package no banco de dados, chame a construção do package de dentro de um package ou de fora do package, dependendo se a construção for privada ou pública.

Quando executar uma procedure, função ou package dentro de um package não é necessário colocar o nome do qualificador.

Exemplo : Chamar uma função de uma procedure dentro do package.

CREATE OR REPLACE PACKAGE BODY comm_package IS ... PROCEDURE reset_comm_rate (v_comm_rate IN NUMBER) IS v_valid BOOLEAN; BEGIN v_valid := validade_comm_rate (v_comm_rate); IF v_valid = TRUE THEN g_comm_rate := v_comm_rate; ELSE RAISE_APPLICATION_ERROR (-20210, ‘Taxa de comissao invalida’); END IF; END reset_comm_rate;


END comm_package; Quando chamar uma procedure ou função de fora do package, o qualificador deve ser utilizado.

Exemplo : Chamando uma procedure de um package do SQL*Plus

SQL> EXECUTE comm_package.reset_comm_rate (.15);

Exemplo: Chamando uma procedure de um package de um diferente usuário.

SQL> EXECUTE Luiz.comm_package.reset_comm_rate (.15);

Exemplo : Chamando uma procedure de um package de um banco de dados

remoto.

SQL> EXECUTE Luiz.comm_package.reset_comm_rate (.15)@pr;

Controlando o estado de persistência de uma variável package 1. Inicialize a variável na declaração ou automaticamente, numa procedure que execute somente uma vez. 2. Altere o valor da variável 3. O valor da variável é perdido quando o usuário desativa sua sessão. Controlando o estado de persistência dos cursores no package 1. Abra o cursor automaticamente numa procedure que execute somente uma vez. 2. Pesquisa sucessivas linhas do cursor. 3. Feche o cursor para um término seguro. O estado de uma variável package ou cursor persiste através das transações dentro da sessão. Mas não persiste de uma sessão para outra ou de um usuário para outro.


Gerenciando Packages

Gerenciando o desenvolvimento de um package com os mesmos comandos e estratégias utilizadas para as procedures. Tarefa Estratégia Obter documentação Exibir as visões do dicionário de dados USER_OBJECTS e USER_SOURCE Eliminar erros de Pesquisar a visão do dicionário de dados compilação USER_ERRORS Gerenciando problemas Explorar as rotinas DBMS_OUTPUT privadas pela de execução Oracle Facilitar o Produzir scripts SQL*Plus desenvolvimento Controlar a segurança Definir privilégios para o proprietário e usuários Analise a execução das procedures dentro dos packages utilizando as rotinas DBMS_OUTPUT.

Gerenciando dependências procedurais

Simplifica o gerenciamento das dependências com packages quando referenciando procedures e funções de outras procedures ou funções.

• Se o corpo do package alterar e a especificação do package não alterar, então o procedure externa referenciada pela construção do package não será invalidada.

• Se a especificação do package alterar, a procedure externa

referenciada pelo package será invalidada (INVALID). De outro lado, não há melhorias no gerenciamento de dependências quando uma procedure de um package referencia uma procedure externa. O corpo inteiro do package dependerá da procedure. Se a procedure externa referenciada dentro do package alterar, então o corpo inteiro do package será invalidado, mas a especificação do package não será invalidada.


Como as procedures, recompile os packages inválidas manualmente Tarefa Descrição Documentar Dependências Examine a visão do dicionário de dados USER_DEPENDENCIES Recompilar Manualmente Execute o comando ALTER PACKAGE Recompilar Automaticamente Mecanismos locais e remotos de dependência Recompilando a especificação e o corpo do package.

Sintaxe :

ALTER PACKAGE nome_do_package COMPILE PACKAGE Recompilando somente a especificação do package.

Sintaxe : ALTER PACKAGE nome_do_package COMPILE PACKAGE SPECIFICATION Recompilando somente o corpo do package.

Sintaxe : ALTER PACKAGE nome_do_package COMPILE PACKAGE BODY


Packages já fornecidas

Utilize os packages já fornecidas pela Oracle. Package Funcionalidade DBMS_OUTPUT Saída de informações de procedure armazenadas DBMS_DDL Compila procedures, funções e packages e obtém uma estatística de performance através do comando ANALYZE DBMS_SESSION Altera a sessão do usuário, define a regra para o usuário e reinicializa o estado do package DBMS_TRANSACTION Controla transações lógicas e melhora a performance DBMS_MAIL Liga o ORACLE Server diretamente com Oracle*Mail DBMS_PIPE Envia mensagem do banco de dados para a aplicação DBMS_ALERT Envia um sinal se um evento ocorrer no banco de dados DBMS_LOCK Efetua a sincronização dos locks

Exemplos:

DBMS_OUTPUT.PUT_LINE (v_debug_flag);

DBMS_DDL.ALTER_COMPILE (v_dependet_procedure);

DBMS_SESSION.SET_ROLE (v_new_role);

DBMS_OUTPUT.SEND (v_from, v_ti, v_cc, v_bcc, v_subject, v_reply_to, v_body );

DBMS_PIPE.PACK_MESSAGE(v_info); v_status := DBMS_PIPE.SEND_MESSAGE(‘info_pipe’);

v_status := DBMS_PIPE.RECEIVE_MESSAGE(‘info_pipe’); IF v_status = 0 then DBMS_PIPE.PACK_MESSAGE (v_info); END IF;


DBMS_ALERT>SIGNAL (‘emp_chag_alert’,v_message);

Benefícios do package Melhorar a organização de armazenamento de procedures e funções.

• Agrupar as procedures e funções relacionadas • Resolução de conflitos de nomes de um mesmo usuário

Melhorar o gerenciamento de procedures e funções armazenadas.

• Alterar o corpo do package sem ter que alterar a especificação • Limitar as dependências procedurais

Melhorar a segurança de procedures e funções armazenadas.

• Permitir acesso no package inteira • Esconder o código fonte dos usuários

Prover identificadores para sessões de usuários

• Identificadores de referência globalizados no ambiente ou procedure.

• Reter o estado dos identificadores durante a sessão Melhorar performance.

• Carrega o package na memória na primeira chamada • Reduz o acesso ao disco nas próximas chamadas


Desenvolver Triggers ( gatilhos ) do Banco de Dados SQL> INSERT INTO emp 2 ... Aplicação Tabela EMP CKECK_SAL trigger EMPNO ENAME JOB SAL 7654 LUIZ ANALISTA 16.555,00 BEFORE INSERT row BANCO DE DADOS Desenvolver um trigger de banco de dados pedindo para executar um bloco PL/SQL somente quando um comando de manipulação especifica e executado em uma certa tabela.

Decida qual momento e em qual evento o trigger deve ser executado antes de codificá-lo. Parte Descrição Valores Possíveis Momento de Execução Quando o trigger é disparado BEFORE em relação à um evento AFTER Evento Qual operação de manipulação INSERT dos dados na tabela causará UPDATE o disparo do trigger DELETE Tipo Quantas vezes o corpo do Comandos trigger será executado Linha Corpo Quais as ações que o trigger Bloco PL/SQL


executará

Criando comandos e triggers de linha

Seqüência de disparo dos triggers de banco de dados numa linha. Tabela DEPT DEPTNO DNAME LOC 10 CONTABILIDADE CAMPINAS BEFORE TRIGGER 20 VENDAS SAO PAULO BEFORE ROW TRIGGER 30 ADMINISTRACAO SAO PAULO AFTER ROW TRIGGER 40 COMPRAS SANTOS AFTER TRIGGER

Comando CREATE TRIGGER

Sintaxe: CREATE O REPLACE TRIGGER nome_do_trigger BEFORE/AFTER evento ON nome_da_tabela bloco PL/SQL

onde, evento indica qual é a manipulação do dado que irá disparar o trigger ( INSERT/UPDATE/DELETE )

Exemplo : criando um BEFORE trigger CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp BEGIN IF (TO_CHAR (sysdate,`DY`) IN (`SAT`,`SUN`)) OR (TO_NUMBER (TO_CHAR(sysdate,`HH24`)) NOT BETWEEN 8 AND 18) THEN RAISE_APPLICATION_ERROR (-20500, `Não é possível inluir funcionário fora do horário normal de trabalho’); END IF; END;


Combinando vários eventos dentro de um trigger, utilizando as funções condicionais INSERTING, UPDATING e DELETING.

Exemplo :

CREATE OR REPLACE TRIGGER secure_emp BEFORE DELETE OR INSERT OR UPDATE ON emp BEGIN IF (TO_CHAR (sysdate,`DY`) IN (`SAT`,`SUN`)) OR (TO_NUMBER (TO_CHAR(sysdate,`HH24`)) NOT BETWEEN 8 AND 18) THEN IF DELETING THEN RAISE_APPLICATION_ERROR (-20502, `Nao e possivel

remover funcionario fora do horario normal de trabalho’);

ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (-20503, `Nao e possivel

inserir funcionario fora do horario normal de trabalho’);

ELSIF UPDATING (‘SAL’) THEN RAISE_APPLICATION_ERROR (-20504, `Nao e possivel

alterar a coluna salario do funcionario fora do horario normal de trabalho’);

END IF; END IF; END;


Criando TRIGGERS de linha

Este trigger dispara quando um evento de manipulação de dados afetando linhas em uma tabela. Para se criar esse trigger, basta apenas acrescentar FOR EACH ROW no comando CREATE TRIGGER.

Sintaxe :

CREATE O REPLACE TRIGGER nome_do_trigger BEFORE/AFTER evento ON nome_da_tabela FOR EACH ROW WHEN clausula_de_restricao bloco PL/SQL

onde, clausula_de_restricao é a restrição que determina quando o trigger será executado.

Exemplo: CREATE OR REPLACE TRIGGER audit_emp BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN IF DELETING THEN UPDATE audit_table SET del = del + 1 WHERE user_name = user AND table_name = ÈMP` AND column_name IS NULL; ELSIF INSERTING THEN UPDATE audit_table SET ins = ins + 1 WHERE user_name = user AND table_name = ÈMP` AND column_name IS NULL; ELSIF UPDATING THEN UPDATE audit_table SET upd = upd + 1 WHERE user_name = user AND table_name = ÈMP` AND column_name IS NULL; END IF; END;


Dentro de um trigger de linha, pode-se referenciar o valor de uma coluna após a alteração do dado utilizando o qualificador NEW e o valor antigo da coluna utilizando o qualificador OLD.

Exemplo :

CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN INSERT INTO audit_emp_values ( user_name, timestamp, empno, old_name, new_name, old_job, new_job, old_mgr, new_mgr, old_sal, new_sal) VALUES ( user, sysdate, :old.empno, :old.ename, :new.ename, :old.job, :new.job, :old.mgr, :new.mgr, :old.sal, :new.sal); END; Restringindo a ação do trigger através de certa condição definida pela cláusula WHEN.

Exemplo :

CREATE OR REPLACE TRIGGER audit_emp_values BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW WHEN (new.job = `VENDENDOR’) BEGIN :new.comm := :old.comm * (:new.sal / :old.sal );

Note que os dois pontos dos qualificadores old e new não são utilizados na cláusula WHEN.


TRIGGERS ARMAZENADAS

Gerenciando triggers armazenadas dentro do banco de dados com comandos similares aos comandos para procedures.

Tarefa Comando

Criar um novo trigger CREATE TRIGGER

Modificar um trigger CREATE OR REPLACE TRIGGER existente

Remover um trigger DROP TRIGGER

Diferente das procedures, pode-se desabilitar um trigger quando for

conveniente. Sintaxe:

ALTER TRIGGER nome_do_trigger DISABLE/ENABLE ou ALTER TABLE nome_da_tabela DISABLE/ENABLE ALL TRIGGERS

Quando um trigger é criado, ele fica disponível automaticamente.

Para armazenar um trigger

1. Escreva o comando CREATE TRIGGER num arquivo texto. 2. Execute o arquivo texto

Se a compilação do trigger for bem sucedida, somente o código fonte é armazenado no banco de dados. caso contrário, somente as mensagens de erros são exibidas interativamente.

3. Quando um trigger é executado, é primeiramente recompilado.


GERENCIANDO TRIGGERS

Gerenciando o desenvolvimento de uma trigger com comandos e estratégias diferentes ás empregadas pelas procedures.

Tarefas Triggers

Documentação Examine a visão do dicionário de dados USER_TRIGGERS

Erros Explore a procedure DBMS_OUTPUT

Segurança para o Obter privilégios para criar triggers, para acessar desenvolvedor os objetos referenciados pela trigger e alterar as tabelas associadas.

Segurança para o Não precisa de privilégio especial. usuário

Não é possível compilar triggers manualmente.

Descrição da visão do dicionário de dados USER_TRIGGERS. Coluna Descrição

TRIGGER_NAME O nome da trigger

TRIGGER_TYPE O momento em o trigger será executado ( BEFORE/AFTER )

TRIGGERING_EVENT O comando de manipulação de dado que causa a execução do trigger ( INSERT, UPDATE ou DELETE )

TABLE_OWNER O dono da tabela associada ao trigger

TABLE_NAME Nome da table associada ao trigger

WHEN Condição de restrição

STATUS Disponibilidade do trigger ( ENABLED/DISABLED )

TRIGGER_BODY Texto do bloco PL/SQL

As visões ALL_TRIGGERS e DBA_TRIGGERS contém o dono do trigger (OWNER).

Exemplo :

SQL> SELECT trigger_body 2 FROM user_triggers


3 WHERE name = ‘DERIVE_COMM’;

Obter os privilégios necessários para desenvolver um trigger

Operação Privilégio Requerido Adicionais CREATE CREATE TRIGGER Acesso a todos os objetos ou referenciados pela CREATE ANY TRIGGER procedure Dono da tabela associada Privilégio de ALTER para a tabela associada Privilégio ALTER ANY TABLE

DROP Dono do trigger ou DROP ANY TRIGGER Executar operações de dados válidos.

Regras para ler e escrever dados nos triggers.

1. Não altere as chaves primárias, chaves estrangeiras ou chaves únicas de uma tabela.

2. Não leia dados de uma tabela mutante. Desenvolver triggers de banco de dados dentro de uma aplicação para adicionar melhorias ao Oracle Server. Melhoria Implementação do Servidor Implementação do Trigger Segurança Permitir acesso para um Permitir acesso à tabelas usuário ou grupo de baseado em valores de usuários dados Auditoria Monitorar operações aos Monitorar valores para dados nas tabelas operações aos dados nas tabelas Integridade Declaração de integridade Implementação de regras de dados via constraints complexas de integridade Integridade Implementar funcionalidade Implementar funcionalidade padrão não-padrão Replicação Cópia de tabelas de forma Cópia de tabelas de forma de tabela assíncrona nos snapshots síncrona nos ‘replicas’


Dados derivados Calcular valores derivados Calcular valores derivados manualmente automaticamente Gravando eventos Executa operações Executa operações ( logs ) explicitamente transparentemente

Triggers para controlar segurança

Desenvolver usuários e grupos de usuários no Oracle para controlar a segurança das operações sobres os dados das tabelas.

Controle sobre a segurança dos objetos dentro do servidor

• Privilégios baseados somente no nome do usuário conectado ao banco de dados • Determina acesso a tabelas, visões, sinônimos e seqüências. • Determina pesquisa, manipulação de dados e definição de privilégios.

Exemplo: SQL> GRANT CLERK TO JOB; SQL> GRANT SELECT, INSERT, UPDATE, DELETE 2 ON EMP 3 TO CLERCK;

Desenvolver triggers para manusear requerimentos de segurança mais complexos

Controlar segurança de objetos via triggers

• Privilégios baseados nos valores do banco de dados como a hora do dia, o dia da semana, etc..

• Determinar acessos somente para tabelas. • Determinar somente privilégios de manipulação de dados.

Exemplo: CREATE O REPLACE TRIGGER secure_emp BEFORE INSERT OR UPDATE OR DELETE ON emp DECLARE v_dummy VARCHAR2(1); BEGIN IF (TO_CHAR (sysdate, ‘DY’) IN (‘SAT’,’SUN’)) OR (TO_NUMBER (TO_CHAR(sysdate, ‘HH24’)) NOT BETWEEN 8 AND 17) THEN RAISE_APPLICATION_ERROR (-20506, ‘Alterações somente podem ser feitas durante horario normal de trabalho.’); END IF; SELECT COUNT (*)


INTO v_dummy FROM holiday WHERE holiday_date <> TRUNC (sysdate); EXCEPTION WHEN NO_DATA_OUND THEN RAISE_APPLICATION_ERROR (-20507, ‘Não pode alterar dados nos feriados.’); END;

Auditoria das operações dos dados no Oracle. Auditando objetos no servidor

• Auditar recuperação de dados, manupulação de dados e comandos de

definição de dados. • Escrever relatórios de auditoria para centralizar tabelas de auditoria. • Gerar registros de auditoria por sessão ou por tentativa de acesso. • Capturar tentativas de acesso bem ou mal sucedidas. • Habilitar ou desabilitar dinamicamente.

Exemplo:

SQL> AUDIT INSERT, UPDATE, DELETE 2 ON emp 3 BY ACCESS 4 WHENEVER SUCCESSFUL;


Auditando valores dos dados nos triggers Auditar objetos nos triggers.

• Somente audita manipulação de dados • Escreve relatórios de auditoria para uma tabela de auditoria definida

pelo usuário • Gerar registros de auditoria por comandos para cada linha • Captura somente tentativas bem sucedidas • Habilitar ou desabilitar dinamicamente

Exemplo: CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT O UPDATE ON emp FOR EACH ROW BEGIN IF audit_emp_package.g_reason IS NULL THEN RAISE_APPLICATION_ERROR (-20059, ‘Especificar uma razão para a operação no dado com a procedure set_reason antes de processar.’); ELSE INSERT INTO audit_emp_values (user_name, timestamp, empno, old_ename, new_ename, old_job, new_job, old_mgr, new_mgr, old_sal, new_sal, comments) VALUES (user, sysdate, :old.empno, :old.ename, :new.ename, :old.job, :new.job, :old.mgr, :new.mgr, :old.sal, :new.sql, audit_package.g_reason); END IF; END;


CREATE TRIGGER cleanup_audit_emp AFTER INSERT OR UPDATE OR DELETE ON emp BEGIN audit_emp_package.g_reason := NULL; END;

Incorporar constraints declarativas nas definições das tabelas para proteger a integridade dos dados.

• Reforçar a integridade dos dados no servidor • Reforçar regras padrões de integridade : not null, not unique, primary

key, e foreign key. • Prover valores defaults para constantes • Reforçar constraints estáticas • Habilitar e desabilitar dinamicamente

Exemplo:

SQL> ALTER TABLE emp ADD 2 CONSTRAINT ck_sal CHECK (sal>= 500);


Desenvolver triggers para manusear regras complexas de integridade de dados

• Reforçar verificações de integridade de dados não padrões • Prover valores defaults à variáveis • Reforçar constraints dinamicamente • Habilitar e desabilitar dinamicamente

Exemplo:

CREATE OR REPLACE TRIGGER chek_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal) OR ( new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR (-20508, ‘Não pode diminuir o salário ou aumentá-lo em valores maiores que 10% o valor atual’); END;

Incorporar constraints de integridade referencial com a definição de uma tabela para previnir inconsistência nos dados.

Reforçar integridade referencial no servidor.

• Restringir as atualizações ou remoções • Remoção em cascata • Habilitar ou desabilitar automaticamente

Exemplo:

SQL> ALTER TABLE emp ADD 2 CONSTRAINT fk_emp_deptno 3 FOREIGN KEY (deptno) REFRENCES dept(deptno) 4 ON DELETE CASCADE;


Desenvolver triggers para implementar integridade referencial não-padrão.

Reforçar integridade referencial com triggers

• Atualização em cascata • Definir valores defaults nas atualizações e remoções • Reforçar integridade referencial em sistemas distribuídos • Habilitar e desabilitar dinamicamente

Exemplo:

CREATE OR REPLACE TRIGGER cascade_updates AFTER UPDATE OF deptno ON dept FOR EACH ROW BEGIN UPDATE emp SET emp.deptno = :new.deptno WHERE emp.deptno = :old.deptno; END;

Manter cópias de tabelas automaticamente com snapshots, particularmente em pontos remotos

Copiar tabelas no servidor com snapshots

• Copiar tabelas assincronamente, em intervalos definidos pelo usuário • Snapshots básicos sobre múltiplas tabelas • Leituras somente dos snapshots • Melhorar a performance na manipulação de dados

Exemplo:

CREATE SNAPSOTS emp_copy AS SELECT * FROM emp@pr;


Replicar tabelas com triggers

• Copiar tabelas sincronamente em tempo real • Usualmente replica base de uma simples tabela master • Leitura de réplicas assim como escrever nelas

Exemplo: CREATE OR REPLACE TRIGGER emp_replica BEFORE INSERT OR UPDATE ON emp FOR EACH ROW BEGIN IF INSERTING THEN IF :new.flag IS NULL THEN INSERT INTO emp@pr VALUES (:new.empno, :new.ename, ..., ‘B’); :new.flag = ‘A’; END IF; ELSE IF :new.flag = :old.flag THEN UPDATE emp@pr SET ename = :new.ename, ... flag = :new.flag WHERE empno = :new.empno; END IF; IF :old.flag = ‘A’ THEN :new.flag = ‘B’; ELSE :new.flag = ‘A’; END IF; END IF; END;


Benefícios dos triggers de banco de dados Melhorar a segurança dos dados:

• Prover verificações básicas de segurança sobre a manipulação dos

dados. • Prover auditoria sobre a manipulação dos dados. • Melhorar a integridade dos dados. • Reforçar dinamicamente as constraints de integridade dos dados a

nível de banco de dados. • Reforçar complexas constrains de integridade referencial. • Assegurar que operações de dados relacionadas sejam executadas

juntamente de forma implícita.


ÍNDICE OBJETIVOS: 187

IFERENÇAS ENTRE PROCEDURES DE BANCO DE DADOS E DE ORACLE FORMS 189

CRIANDO PROCEDURES 190

PASSANDO VALORES DO AMBIENTE CHAMADOR PARA UMA PROCEDURE UTILIZANDO O ARGUMENTO IN 191 RETORNANDO UM VALOR DA PROCEDURE PARA O AMBIENTE CHAMADOR UTILIZANDO O ARGUMENTO OUT 191 PASSANDO UM VALOR DO AMBIENTE CHAMADOR PARA A PROCEDURE, E A PROCEDURE RETORNA UM VALOR PARA O AMBIENTE CHAMADOR 192

CRIANDO FUNÇÕES 192

RETORNAR UM VALOR DE UMA FUNÇÃO PARA O AMBIENTE CHAMADOR UTILIZANDO A CLÁUSULA RETURN 193

GERENCIANDO EXCEÇÕES EM TEMPO DE EXECUÇÃO 193

EXECUTANDO PROCEDURES 196

EXECUTANDO UMA FUNÇÃO DE UM BLOCO PL/SQL 198

EXECUTANDO UMA FUNÇÃO DE UMA PROCEDURE 198

EXECUTANDO UMA FUNÇÃO DO SQL*PLUS 198

EXECUTANDO UMA FUNÇÃO DE UM PRÉ-COMPILADOR OU DE UMA APLICAÇÃO OCI 199

BENEFÍCIOS DAS PROCEDURES E FUNÇÕES 200

GERENCIANDO PROCEDURES E FUNÇÕES 201

DOCUMENTANDO PROCEDURES E FUNÇÕES 202

ANALISANDO EXECUÇÃO DE PROCEDURES E FUNÇÕES 205

DISPONIBILIZANDO AS PROCEDURES DBMS_OUTPUT DO SQL*PLUS OU SQL*DBA COM A OPÇÃO SERVEROUTPUT 206

CONTROLE DE SEGURANÇA 208



GERENCIANDO AS DEPENDÊNCIAS ENTRE PROCEDURES 210

EXISTEM DOIS TIPOS DISTINTOS DE DEPENDÊNCIA: DIRETA E INDIRETA 211

DESENVOLVENDO E UTILIZANDO PACKAGES 218

ESPECIFICAÇÃO DO PACKAGE E SEU CORPO 220 CRIANDO PACKAGES 221 EXECUTANDO PACKAGES 224 CONTROLANDO O ESTADO DE PERSISTÊNCIA DE UMA VARIÁVEL PACKAGE 225 GERENCIANDO PACKAGES 226

GERENCIANDO DEPENDÊNCIAS PROCEDURAIS 226

BENEFÍCIOS DO PACKAGE 229

DESENVOLVER TRIGGERS ( GATILHOS ) DO BANCO DE DADOS 230

CRIANDO COMANDOS E TRIGGERS DE LINHA 231 COMANDO CREATE TRIGGER 231 CRIANDO TRIGGERS DE LINHA 233

TRIGGERS ARMAZENADAS 235

PARA ARMAZENAR UM TRIGGER 235

GERENCIANDO TRIGGERS 236

OBTER OS PRIVILÉGIOS NECESSÁRIOS PARA DESENVOLVER UM TRIGGER 237 TRIGGERS PARA CONTROLAR SEGURANÇA 238

AUDITORIA DAS OPERAÇÕES DOS DADOS NO ORACLE. 239

AUDITANDO VALORES DOS DADOS NOS TRIGGERS 240

REPLICAR TABELAS COM TRIGGERS 244

BENEFÍCIOS DOS TRIGGERS DE BANCO DE DADOS 245


ÍNDICE

I - INTRODUÇÃO 1

I.1 - OBJETIVOS DO CURSO 2 I.2 - O QUE É BANCO DE DADOS? 3 I.3 - RDBMS (RELATIONAL DATABASE MANAGEMENT SYSTEM) 4 I.4 - MODELO RELACIONAL 5 I.5 - OPERADORES RELACIONAIS 6 I.6 - PROPRIEDADES DE UM BANCO DE DADOS RELACIONAL 8 I.7 - PROPRIEDADE DE UMA COLEÇÃO DE DADOS TABULAR 9 I.8 - ARQUITETURA DE PRODUTOS ORACLE 10 I.9 - SQL 11 I.10 - CARACTERÍSTICAS DO SQL 11 I.11 - O QUE É SQL*PLUS 12 I.12 - PL/SQL 13 I.13 - PL/SQL NO ORACLE RDBMS 14 I.14 - CONJUNTO DE COMANDOS SQL 15

II - COMANDOS SQL E SQL*PLUS 16

II.1 - CONTEÚDOS 17 II.2 - ATIVAÇÃO DO SQL*PLUS 18 II.3 - ESCREVENDO COMANDOS SQL 19 II.4 - CRIAÇÃO DE TABELAS 20 II.5 - INCLUINDO NOVAS LINHAS EM UMA TABELA 21 II.6 - ALTERANDO LINHAS DE UMA TABELA 24 II.7 - REMOVENDO LINHAS DE UMA TABELA 26 II.8 - O COMANDO TRUNCATE 28 I.9 - EDIÇÃO DE COMANDOS 28 II.10 - OUTROS COMANDOS 32

III - CONSULTAS BÁSICAS 34

III.1 - CONTEÚDO 35 III.2 - SELEÇÃO DE COLUNAS 36 III.3 - OPERADORES ARITMÉTICOS 41 III.4 - USO DE EXPRESSÕES 42 III.5 - OPERADORES DE CARACTERES 44 III.6 - OPERADORES DE COMPARAÇÃO 46 III.7 - OPERADORES LÓGICOS 47 III.8 - OUTROS OPERADORES 49 III.9 - SUBSTITUIÇÃO DE VARIÁVEIS 50 III.10 - ORDENAÇÃO DE RESULTADOS 51 III.11 - A CLÁUSULA WHERE 53 III.12 - CLÁUSULA GROUP BY 54 III.13 - CLÁUSULA HAVING 56 III.14 - OPERAÇÕES DE CONJUNTO 57

III.15 - JUNÇÃO DE TABELAS 62

IV - CONSULTAS AVANÇADAS 68

IV.1 - CONTEÚDO 68 IV.2 - PSEUDO-COLUNAS 69 IV.3 - JUNÇÃO EXTERNA 70 IV.4 - AUTO - JUNÇÃO 73 IV.5 - CONSULTAS ENCAIXADAS 74 IV.6 - CONSULTAS HIERÁRQUICAS 77

V - SQL*PLUS 83

V.1 - SQL*PLUS 83 V.2 - EDIÇÃO DE COMANDOS UTILIZANDO SQL*PLUS 85 V.3 - O COMANDO COLUMN 85 V.4 - OS COMANDOS TTITLE E BTITLE 88 V.5 - SQL*PLUS REPORT 89 V.6 - COMANDOS SET 90 V.7- CLÁUSULAS DE NUMERAÇÃO DE PÁGINA E OUTRAS VARIÁVEIS DO SISTEMA 91 V.8 - O COMANDO DEFINE 92 V.9 - O COMANDO ACCEPT 93

VI - ESTRUTURA DE DADOS 94

VI.1 - ESTRUTURA DE DADOS ORACLE 95 VI.2 - CRIANDO UMA TABELA 95 VI.3 - DIRETRIZES PARA CRIAÇÃO DE NOMES DE TABELAS 96 VI.4 - TIPOS DE COLUNAS 97 VI.5 - A OPÇÃO NULL E NOT NULL 98 VI.6 - CLÁUSULA CONSTRAINT 99 VI.7 - PARÂMETROS DA CONSTRAINT 101 VI.8 - CREATE TABLE 102 VI.9 - CRIAR UMA TABELA COM BASE EM UMA JÁ EXISTENTE 104 VI.10 - ALTERAÇÃO DE UMA TABELA 105 VI.11 - ALTERANDO UMA TABELA 106 VI.12 - O COMANDO RENAME 108 VI.13 - DROP TABLE 109 VI.14 - ELIMINANDO CONSTRAINT DA TABELA 110 VI.15 - DOCUMENTANDO UMA TABELA 110 VI.16 - MANIPULAÇÃO DE VISÕES 111 VI.17 - PARA CRIAR UMA VIEW 115 VI.18 - ALTERANDO DADOS ATRAVÉS DE UMA VIEW 117 VI.19 - DROP TABLE 118 VI.20 - CRIAÇÃO DE SEQUÊNCIAS 120


VI.21 - GERANDO NÚMEROS DA SEQÜÊNCIA UTILIZANDO NEXTVAL 123 VI.22 - ÍNDICES 125

VII - FUNÇÕES 128

VII.1 - FUNÇÕES NUMÉRICAS 129 VII.2 - FUNÇÕES CARACTER 132 II.3 - FUNÇÕES DE AGRUPAMENTO (GRUPO) 137 VII.4 - FUNÇÕES DE CONVERSÃO 139 VII.5 - FUNÇÕES QUE ACEITAM QUALQUER TIPO DE DADO COMO ENTRADA 141 VII.6 - FUNÇÕES DE DATAS 142

VIII - DICIONÁRIO DE DADOS 145

VIII.1 - O DICIONÁRIO DE DADOS ORACLE 146 III.2 - CONTROLE DE TRANSAÇÕES 147 VIII.3 - CONTROLANDO TRANSAÇÕES COM COMANDOS SQL 153 VIII.4 - O COMANDO SAVEPOINT 154 VIII.5 - O COMANDO ROLLBACK 154 VIII.6 - O PARÂMETRO AUTOCOMMIT 155 VIII.7 - CONCORRÊNCIA 156 VIII.8 - TIPOS DE LOCKS 158 VIII.9 - ROWID 159

VIII.10 - USUÁRIOS E SEGURANÇA 160�� !��

��

�"��# �$��%��"�� # ��"�� # ��&��"�'��(�� )��"�%��# ��&*��"��# ��&��"�&��+ �� # ��&��"�)�� "��, ��# ��&��"�-��,�� &'��"��*�� .��(��&��"�� .��(��&��"�� .��/0��&)��"��1�2��&-��"��'��1��/�2��)*��"��%�� $��)��"�� "�+�� )��"��&�� )��"��)�� )%��"��-�� (�� )�

Procedural Option INTRODUÇÃO AO ORACLE ________________ 1

II - COMANDOS SQL E SQL*PLUS __________ 16

III - CONSULTAS BÁSICAS_________________ 34

IV - CONSULTAS AVANÇADAS_____________ 68

V - SQL*PLUS_____________________________ 83

VI - ESTRUTURA DE DADOS _______________ 94

VII - FUNÇÕES___________________________ 128

VIII - DICIONÁRIO DE DADOS ____________ 145

IX - PL/SQL ______________________________ 164

PROCEDURAL OPTION __________________ 187

OBJETIVOS:_____________________________ 188

DIFERENÇAS ENTRE PROCEDURES DE BANCO DE DADOS E DE ORACLE FORMS _ 189

CRIANDO PROCEDURES _________________ 190

CRIANDO FUNÇÕES _____________________ 192

GERENCIANDO EXCEÇÕES EM TEMPO DE EXECUÇÃO _____________________________ 193

EXECUTANDO PROCEDURES ____________ 196

EXECUTANDO UMA FUNÇÃO DE UM BLOCO PL/SQL__________________________________ 198

EXECUTANDO UMA FUNÇÃO DE UMA PROCEDURE ____________________________ 198

EXECUTANDO UMA FUNÇÃO DO SQL*PLUS_________________________________________ 198

EXECUTANDO UMA FUNÇÃO DE UM PRÉ-COMPILADOR OU DE UMA APLICAÇÃO OCI_________________________________________ 199

BENEFÍCIOS DAS PROCEDURES E FUNÇÕES_________________________________________ 200


DOCUMENTANDO PROCEDURES E FUNÇÕES_________________________________________ 202

ANALISANDO EXECUÇÃO DE PROCEDURES E FUNÇÕES _______________________________ 205

DISPONIBILIZANDO AS PROCEDURES DBMS_OUTPUT DO SQL*PLUS OU SQL*DBA COM A OPÇÃO SERVEROUTPUT__________ 206

CONTROLE DE SEGURANÇA _____________ 208


GERENCIANDO AS DEPENDÊNCIAS ENTRE PROCEDURES ___________________________ 210

DESENVOLVENDO E UTILIZANDO PACKAGES_________________________________________ 218

ARMAZENADO PACKAGES _______________ 224

GERENCIANDO DEPENDÊNCIAS PROCEDURAIS __________________________ 226

DESENVOLVER TRIGGERS ( GATILHOS ) DO BANCO DE DADOS _______________________ 230

TRIGGERS ARMAZENADAS ______________ 235

GERENCIANDO TRIGGERS _______________ 236

AUDITORIA DAS OPERAÇÕES DOS DADOS NO ORACLE. ________________________________ 239

REPLICAR TABELAS COM TRIGGERS_____ 244

BENEFÍCIOS DOS TRIGGERS DE BANCO DE DADOS __________________________________ 245


[u][09u [

Procedural Option INTRODUÇÃO AO ORACLE ________________ 1

II - COMANDOS SQL E SQL*PLUS __________ 16

III - CONSULTAS BÁSICAS_________________ 34

IV - CONSULTAS AVANÇADAS_____________ 68

V - SQL*PLUS_____________________________ 83

VI - ESTRUTURA DE DADOS _______________ 94

VII - FUNÇÕES___________________________ 128

VIII - DICIONÁRIO DE DADOS ____________ 145

IX - PL/SQL ______________________________ 164

PROCEDURAL OPTION __________________ 187

OBJETIVOS:_____________________________ 188

DIFERENÇAS ENTRE PROCEDURES DE BANCO DE DADOS E DE ORACLE FORMS _ 189

CRIANDO PROCEDURES _________________ 190

CRIANDO FUNÇÕES _____________________ 192

GERENCIANDO EXCEÇÕES EM TEMPO DE EXECUÇÃO _____________________________ 193

EXECUTANDO PROCEDURES ____________ 196

EXECUTANDO UMA FUNÇÃO DE UM BLOCO PL/SQL__________________________________ 198

EXECUTANDO UMA FUNÇÃO DE UMA PROCEDURE ____________________________ 198

EXECUTANDO UMA FUNÇÃO DO SQL*PLUS_________________________________________ 198

EXECUTANDO UMA FUNÇÃO DE UM PRÉ-COMPILADOR OU DE UMA APLICAÇÃO OCI_________________________________________ 199

BENEFÍCIOS DAS PROCEDURES E FUNÇÕES_________________________________________ 200


DOCUMENTANDO PROCEDURES E FUNÇÕES_________________________________________ 202

ANALISANDO EXECUÇÃO DE PROCEDURES E FUNÇÕES _______________________________ 205

DISPONIBILIZANDO AS PROCEDURES DBMS_OUTPUT DO SQL*PLUS OU SQL*DBA COM A OPÇÃO SERVEROUTPUT__________ 206

CONTROLE DE SEGURANÇA _____________ 208


GERENCIANDO AS DEPENDÊNCIAS ENTRE PROCEDURES ___________________________ 210

DESENVOLVENDO E UTILIZANDO PACKAGES_________________________________________ 218

ARMAZENADO PACKAGES _______________ 224

GERENCIANDO DEPENDÊNCIAS PROCEDURAIS __________________________ 226

DESENVOLVER TRIGGERS ( GATILHOS ) DO BANCO DE DADOS _______________________ 230

TRIGGERS ARMAZENADAS ______________ 235

GERENCIANDO TRIGGERS _______________ 236

AUDITORIA DAS OPERAÇÕES DOS DADOS NO ORACLE. ________________________________ 239

REPLICAR TABELAS COM TRIGGERS_____ 244

BENEFÍCIOS DOS TRIGGERS DE BANCO DE DADOS __________________________________ 245


RESOLUÇÃO DOS EXERCÍCIOS

II - COMANDOS SQL E SQL*Plus

1. CREATE TABLE ALUNO (NOME CHAR(30) NOT NULL, ENDER CHAR(50), CIDADE CHAR(25), ESTADO CHAR(2), CEP NUMBER(5), ANIVERSARIO DATE); 2. INSERT INTO ALUNO VALUES (‘JOSE’, ‘RUA X’, ‘LONDRINA’, ‘PR’, 88888,

‘12-SEP-80’);

INSERT INTO ALUNO VALUES (‘MARIA’, ‘RUA Y’, ‘MARINGA’, ‘PR’, 77777,

‘11-DEC-70’);

INSERT INTO ALUNO VALUES (‘PEDRO’, ‘RUA Z’, ‘CURITIBA’, ‘PR’, 66666,

‘12-SEP-50’); 3. SELECT NOME, ANIVERSARIO FROM ALUNO; 4. SELECT ENDER FROM ALUNO WHERE NOME = ‘JOSE’; 5. SAVE EX24.SQL 6. SELECT SAL*2 FROM EMP WHERE EMPNO = 7566; 7. DELETE FROM EMP WHERE COMM > SAL;

III - CONSULTAS BÁSICAS


1. SELECT DNAME, LOC, SAL/160, SAL + NVL(COMM,0) FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND JOB = ‘SALESMAN’; 2. SAVE EX31.SQL

SELECT DNAME, LOC, SAL/160, SAL + NVL(COMM,0) FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND JOB = ‘SALESMAN’ ORDER BY SAL/160 DESC; 3. SELECT MIN(SAL) FROM EMP; 4. SELECT SUM(SAL) FROM EMP WHERE DEPTNO NOT IN(10,30); 5. SELECT D.DNAME, AVG(E.SAL) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO <> 10 GROUP BY D.DNAME HAVING MAX(SAL) < 4000; 6. SELECT AVG(SAL) FROM EMP, SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

IV - CONSULTAS AVANÇADAS


1. SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM EMP B WHERE E.EMPNO = B.MGR); 2. SELECT F.ENAME FUNCIONARIO, NVL(C.ENAME,’______’) CHEFE FROM EMP F, EMP C WHERE F.MGR = C.EMPNO(+); 3. SELECT E.ENAME, E.SAL, GRADE FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN LOSAL AND HISAL AND GRADE = (SELECT GRADE FROM EMP, SALGRADE WHERE EMP.EMPNO = E.MGR AND EMP.SAL BETWEEN LOSAL AND HISAL); 4. UPDATE EMP E SET SAL = (SAL*0.20) + SAL WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP WHERE E.DEPTNO = DEPTNO); 5. UPDATE EMP E SET JOB = ‘DIRETOR’ WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE JOB = E.JOB); 6. ROLLBACK; 7. UPDATE EMP E1 SET SAL = (SELECT MAX(SAL) FROM SALGRADE, EMP WHERE SAL BETEWEEN LOSAL AND HISAL AND EMPNO = E1.EMPNO);

VI - ESTRUTURA DE DADOS 1. CREATE TABLE EMPREG AS ( SELECT EMPNO, ENAME, SAL FROM EMP);


2. RENAME EMPREG TO TEMP

DROP TABLE TEMP; 3. A) ALTER TABLE ALUNO MODIFY CIDADE NUMBER(35);

B) ALTER TABLE EMP ADD (SAL_DESEJADO NUMBER(11,2);

4. INSERT INTO ALUNO (NOME, ANIVERSARIO, SAL_DESEJADO VALUES (‘VALTER’, ‘23-SEP-76’, 1000); 5. UPDATE EMP SET SAL_DESEJADO = 1000, ENDER = ‘RUA B’, WHERE ENAME = ‘VALTER’); 6. CREATE TABLE ALUNO AS (SELECT NOME, ANIVERSARIO FROM ALUNO); 7. DELETE FROM ALUNO WHERE 8. CREATE VIEW TESTE AS (SELECT DNAME NOME_DEPT, COUNT(EMPNO) NUM_FUNC, MIN(SAL) SAL_MIN, AVG(SAL) SAL_MED, MAX(SAL) SAL_MAX FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME, EMPNO); 9. DESC DEP_SAL 10. UPDATE EMP SET SAL = 3000 WHERE ENAME = ‘SMITH’; 11. ROLLBACK;

Documents

Apostila Introdução ao Oracle Procedural Option para amigos