Manual Pl SQL Completo

__________________________________________________________________________________

__________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

1

BANCO DE DADOS

HISTÓRICO No processamento de dados tradicional, as aplicações (sistemas) eram basicamente

dirigidas a um setor específico da empresa e, conseqüêntemente, sua construção baseava-se nos dados utilizados em tal setor.

Desta forma, o sistema de informação da empresa, como um todo, era constituído por diversos sistemas, possuindo processos e arquivos próprios e independentes.

Problemas : A) A comunicação de dados entre tais empresas, quando necessária era difícil.

(integração); B) Ocorrência de duplicação de dados e tarefas, gerando inconsistência entre os

mesmos. (redundância não controlada); C) Aumento do custo de manutenção, devido ao fato de uma mesma tarefa estar sendo

realizada em dois ou mais processos distintos. Identificados esses problemas, a evolução natural impôs aos sistemas uma eliminação

da duplicação, e integração entre os sistemas (dados). Surge então uma novo problema: Devido à integração, dois ou mais processos passam a compartilhar o mesmo dado

(necess idade de controle de concorrência). Outro problema dessa integração deve-se ao fato de uma mesmo dado poder ter

diferentes estruturas físicas com tipos e tamanhos diferentes, e, por conseguinte, dois processos distintos poderem necessitar de representações diferente de um mesmo dado de acordo com a utilização que façam parte deste (necess idade de independência entre dados e processos (programas))

Surge então o Banco de Dados

BANCO DE DADOS Segundo [Date], o sistema de banco de dados consiste em um sistema de manutenção

de informações por computador que tem por objetivo manter as informações e disponibilizá-las aos seus usuários quando solicitadas.

Segundo [Palmer], um banco de dados é uma coleção de dados, organizados e integrados, que constituem uma representação natural de dados, sem imposição de restrições ou modificações para todas as aplicações relevantes sem duplicação de dados.

Principais Objetivos: A) Permitir a independência entre dados e programas

1. independência lógica - Permite que a visão global dos dados se modifique (evolua de acordo coma as necessidade da empresa) sem que as aplicações existentes tenham que ser alteradas. 2. Independência física - permite que a representação física das estruturas de dados se modifique de acordo com os requisitos de novas aplicações sem que as existentes tenham que ser alteradas.

B) Permitir o controle de redundância de dados

__________________________________________________________________________________


2

Através do controle centralizado dos dados das diversas aplicações que utilizam o banco de dados, podendo estabelecer procedimentos de controle e verificação e também podendo criar padrões.

C) Garantir a integridade dos dados Devido à integração dos dados, duas ou mais aplicações podem vir a compartilhar um mesmo dados concorrentemente com a finalidade de atualização, portanto deve existir um mecanismo que garanta a integridade dos dados. A integridade também deve ser mantida através de logs de atualização (imagens anterior e posterior a uma modificação), possibilitando desfazer alterações corretas feitas por outras aplicações.

D) Garantir a privacidade Garantir que o acesso aos dados possa ser controlada pelo administrador do banco de dados, garantindo a segurança dos dados contra acessos e modificações indevidas.

E) Permitir a facil idade de criação de novas aplicações Sendo o banco de dados criado a partir de um modelo conceitual da empresa, ele deve constituir a base de dados necessária à todas as aplicações da empresa, desta forma, podemos dizer que facilita a criação de novas aplicações.

F) Segurança de dados Sendo que o banco de dados é constituído por informações da empresa, a segurança dessas contra perdas ou destruição deve ser um ponto importante. O banco de dados deve permitir cópias dos dados que possam ser restaurados parcial ou totalmente.

G) Controle automático d e relacionamento entre registros No processamento tradicional, o programador era responsável pelo controle e manutenção do relacionamento entre registros, podendo introduzir uma falta de consistência nos dados. Num banco de dados este controle deve ser automático, a partir da definição do esquema global da empresa.

H) Otimização da uti li zação de espaço de armazenamento Devido ao grande volume de dados envolvidos em um banco de dados, que por sua vez utiliza espaço de armazenamento, que normalmente é limitado, podem ser utilizadas técnicas de compressão de dados e reaproveitamento automático dos espaços gerados por eliminações.

REQUISITOS QUE UM BANCO DE DADOS DEVE SATISFAZER: A) Permitir o controle (redução) da redundância, para evitar a inconsistência dos dados e informações; B) Permitir o compartilhamento dos dados entre aplicações novas e já existentes (acesso concorrente); C) Permitir aplicar restrições de segurança; D) Permitir o controle da integridade, ou seja, assegurar que os dados estão sendo gravados corretamente; E) Permitir a independência entre dados e aplicações, ou seja, as aplicações não dependem de qualquer estrutura de armazenamento ou estratégia de acesso;

__________________________________________________________________________________


3

F) Permitir e manter o relacionamento entre dados.

__________________________________________________________________________________


4

COMPONENTES BÁSICOS DE UM BANCO DE DADOS A) Dados São os dados armazenados em um banco de dados. Exemplo: dados dos empregados de uma empresa, com o número, nome, profissão, salário, etc. Em um banco de dados os dados serão integrados e compartilhados. Integrado - por "integrado" queremos dizer que o banco de dados pode ser imaginado como a única filiação de diversos arquivos de dados que, de outra forma, seriam distintos, eliminando-se total ou parcialmente qualquer redundância entre os mesmos. EMPREGADO ( Número-Empregado, Nome, Endereço, Salário, Número-Departamento) DEPARTAMENTO ( Número-Departamento, Nome-Departamento, Localização) Compartilhado - por "compartilhado" queremos dizer que parcelas isoladas de dados podem ser compartilhadas por diversos usuários num banco de dados, no sentido de que todos os usuários podem ter acesso à mesma parcela de dados, inclusive, ter acesso às mesmas partes de dados no mesmo momento (processo concorrente). B) Hardware Consiste na máquina que vai abrigar o banco de dados. C) Software É a interface entre o banco de dados físico (isto é, os dados que estão armazenados) e os usuários. Ou seja, o usuário só acessa os dados através de um software, que é chamado de sistema de gerenciamento do banco de dados (SGDB) ou DATA BASE MANAGEMENT SYSTEM (DBMS). D) Usuários Os usuários são o conjunto de pessoas que irão acessar as informações armazenadas em um banco de dados.

CLASSES DE USUÁRIOS Existem basicamente 3 classes de usuários, que são: 1. O programador de aplicações É o responsável pela definição dos programas de aplicação que utilizam o banco de dados, utilizando alguma linguagem de programação. 2. O usuário final Normalmente tem acesso ao banco de dados por meio de uma das aplicações on-line desenvolvidas na empresa. O usuário final experto eventualmente pode acessar o banco através da linguagem SQL. 3. O administrador do b anco de dados (DBA) O DBA é o responsável pela administração do banco de dados, utilizando para isso utilitários específicos.

__________________________________________________________________________________


5

ADMINISTRADOR DO BANCO DE DADOS

O administrador de banco de dados é a pessoa (ou grupo de pessoas) responsável pelo controle (administração) do banco de dados. Responsabilidades A) Decidir o conteúdo d e informações do banco de dados

Deve identificar as entidades de interesse da empresa e a informação a registrar em relação a estas entidades. B) Decidir a estrutura e armazenamento e a estratégia

Deve decidir como os dados serão representados no banco de dados, e definir esta representação escrevendo a definição da estrutura de armazenamento (usando DDL). C) Servir de elo de ligação com usuários

Garantir a disponibilidade dos dados que os usuários necessitam. D) Definir os controles de segurança e integridade

Especificar os controles de segurança e integridade através dos comandos da linguagem. E) Definir a estratégia de reserva (Backup) e recuperação

Definir procedimentos de Backup, para que na eventualidade de um erro humano, ou por falha no hardware, ou no sistema operacional o banco de dados pode ser recuperado com um mínimo de demora. F) Monitorar o desempenho e atender as necess idades de modificações

Monitorar o banco de dados e fazer os ajustes necessários para otimizar o banco de dados. G) Executar procedimentos de carga e descarga

Segundo [Leite], existem três tipos de visão dos dados, para melhorar o entendimento da independência de dados.

A) Visão global dos dados

Consiste na descrição de todas as entidades da empresa, assim como de seus relacionamentos, estabelecendo um modelo conceitual para o banco de dados da empresa. B) Visão p arcial dos dados

Consiste na descrição de um subconjunto da visão global que é utilizado por uma classe de aplicações. C) Representação física das estruturas de dados

Consiste na descrição da forma de representação física dos dados nos dispositivos de armazenamento secundário.

Segundo [Leite], também existem três níveis da definição de dados na arquitetura de banco de dados que também são conhecidos como: ESQUEMA INTERNO, ESQUEMA CONCEITUAL E EXTERNO.

__________________________________________________________________________________


6

ESQUEMA CONCEITUAL

O esquema conceitual contém a "visão do mundo real da empresa que está sendo modelada no b anco d e dados". Deve conter informações sobre todas as entidades da empresa, assim como seus atributos, relacionamentos e restrições de acesso. (Estruturas Lógicas)

ESQUEMA EXTERNO A definição de esquema externo é feita a partir do esquema conceitual e que contém a

definição dos dados necessários a uma classe de aplicações específicas. Essa definição é feita de forma a procurar atender às características particulares dessas aplicações. É pelo esquema externo que os programas da classe de aplicações correspondente acessam o subconjunto do banco de dados necessário a seu funcionamento.

ESQUEMA INTERNO O esquema interno contém a descrição da representação de armazenamento físico de

dados. Dessa forma, qualquer representação física pode ser implementada sem restrições. No esquema interno fica também a descrição de como os dados estão armazenados. (Estruturas Físicas).

__________________________________________________________________________________


7

SISTEMA GERENCIADOR DE BANCO DE DADOS (SGDB)

Segundo [Hackathorm], o SGDB, também é conhecido como DATABASE

MANAGEMENT SYSTEM (DBMS), é uma coleção de programas e utilitários para organizar, armazenar, atualizar e recuperar dados. O objetivo principal do SGDB é capturar os dados de forma que modelem adequadamente o mundo real, ou seja, o SGDB deve modelar as entidades importantes (tanto internas quanto externas à organização) que relacionam-se como o negócio da empresa.

Segundo [Leite], as principais estruturas utilizadas nos SGDB são:

A) ESTRUTURAS LÓGICAS O processo de modelagem de uma percepção do mundo real é feito construindo-se

estruturas lógicas formadas pelas entidades e relações relevantes a essa percepção. B) ESTRUTURAS FÍSICAS

As estruturas físicas representam a forma utilizada para organizar os dados nos meios de armazenamento secundário. Para permitir que sejam criadas estruturas físicas convenientes a cada situação, um SGDB deve oferecer diversos tipos de estruturas e, entre as principais temos: Listas encadeadas, arquivos de inversões, estruturas de acesso randômico, arquivos seqüenciais, índices primários e secundários, arquivo de ligações.

FUNÇÕES BÁSICAS DO SGDB 1. Manipulação de dados: organizar o conteúdo dos dados inserindo, atualizando, excluindo e recuperando dados. 2. Definição d e dados: Estruturar os elementos de dados em esquemas lógicos e físicos. 3. Restrições de integridade: Garantir a segurança, integridade e concorrência dos dados. Um SGDB normalmente oferece o seguinte conjunto de funções ou meios:

1. Linguagem de manipulação de dados; 2. Linguagem de definição de dados; 3. Meio de carregamento de dados; 4. Meio de recuperação de dados; 5. interface de programação de banco de dados; 6. Ferramentas de desenvolvimento de aplicações; 7. Ferramentas de questionamento de relatórios;

__________________________________________________________________________________


8

COMPONENTES PARA UM SGDB

1. Primeiro, o SGDB consiste de uma banco de dados construído para realizar operações de baixo nível exigidas para suportar as funções acima.

2. Segundo, o SGDB consiste de uma aplicação feita sob medida, projetada e implementada pelo usuário para realizar alguma função específica do negócio.

3. Terceiro, o SGDB consiste de ferramentas genéricas de última geração que permitem que um usuário projete e implemente uma função específica do negócio.

FUNCIONAMENTO DO SGDB

1. O usuário emite uma solicitação de acesso, usando uma linguagem específica. Por exemplo: Select NR_EMP, NOME, SALARIO FROM TAB_EMP;

2. O SGDB intercepta a solicitação e analisa-a; 3. O SGDB, por sua vez, inspeciona os esquemas externos para aquele usuário e faz o

mapeamento necessário; 4. O SGDB executa as operações necessárias no banco de dados armazenado 5. O SGDB retorna o resultado ao usuário.

COMUNICAÇÕES DE DADOS As solicitações ao banco de dados a partir de uma usuário são normalmente

transmitidas (a partir do terminal ou micro do usuário - que pode estar fisicamente longe do sistema - para alguma aplicação on-line, e dali para o DBMS) na forma de mensagens de comunicação.

As respostas ao usuário (do DBMS e da aplicação on-line de volta ao terminal do usuário) também são transmitidas sob forma de mensagens. Todas essas transmissões de mensagens são efetuadas sob direção de um outro sistema de software, o gerenciador de comunicação dos dados, que não faz parte do DBMS.

MODELO RELACIONAL

Segundo [Hackathorn], no modelo de dados relacional, o mundo parece ser mais simples. Tudo se resume a uma simples tabela, na qual colunas descrevem algum atributo ou característica e as linhas indicam a pessoa ou coisa atual.

Três exigências para um produto que suporte o modelo relacional: 1. Estrutura de dados relacional, na qual todos os dados estão mantidos no banco de

dados, é composta por itens, onde cada um é um valor simples. Todos os valores em uma coluna são dos mesmo tipo. As colunas são nomeadas mas sem ordenamento e cada linha é única, sem nenhuma ordem implícita

2. Linguagem de dados relacional, através da qual todas as operações de banco de dados são realizadas, apesar de haver alternativas.

3. Regras de integridade relacional, para garantir que os dados permaneçam válidos.

Segundo [Date], um banco de dados relacional é um banco de dados que o usuário percebe como uma coleção de relações (TABELAS) normalizadas de vários graus, que se modificam ao longo do tempo.

__________________________________________________________________________________


9

O GRAU DA RELAÇÃO é o número de atributos na relação, ou o que seria equivalente, o número de domínios básicos.

ESTRUTURA RELACIONAL DOS DADOS

Terminologia utilizada: DOMÍNIO, ATRIBUTO, TUPLA, CHAVE PRIMÁRIA, RELAÇÃO. Em resumo:

• RELAÇÃO é também chamada de tabela, que é um conjunto bidimensional composto por linhas e colunas onde estão armazenados os dados do banco de dados.

• TUPLA corresponde à uma linha da tabela.

• ATRIBUTO corresponde à uma coluna da tabela.

• CHAVE PRIMÁRIA é um identificador único para a tabela, isto é, uma coluna (ou combinações de colunas) com a propriedade de, a qualquer momento, nenhum par de linhas da tabela possa conter o mesmo valor naquela coluna (ou combinação de colunas).

• DOMÍNIO é um grupo de valores, a partir dos quais um ou mais atributos (COLUNAS) retiram seus valores reais.

Exemplo: O domínio da coluna CITY é o conjunto de todos os valores legais de cidades. Exemplo: Banco de dados composto por três tabelas: S, P, E SP.

PROPRIEDADES DAS RELAÇÕES

• Não há tuplas duplicadas; • As tuplas não seguem um ordenamento (de cima para baixo, na ordem de entrada); • Os atributos não seguem um ordenamento (da esquerda para a direita); • Todos os valores são atômicos;

Em todas as posições de linha-e-coluna da tabela há sempre, precisamente, um valor, nunca um conjunto de valores, ou, ainda de forma equivalente: as relações não contém grupos repetidores. Diz-se da relação que satisfaz esta condição que é normalizada.

REGRAS DE INTEGRIDADE RELACIONAL Chaves primárias

Por definição, toda relação tem, pelo menos, uma chave candidata. Na prática, a maioria das relações têm exatamente uma, mas é possível que algumas tenham duas ou mais. Para determinada relação, escolhemos uma das chaves candidatas para ser a chave primária, e então as restantes (se houver), são chamadas de chaves alternadas.

A chave primária serve para identificar de forma única uma tupla (LINHA), para garantir que não existam tuplas duplicadas. Exemplo: Na relação de fornecedores o número do fornecedor é que pode identificar de forma única cada fornecedor. Chaves externas (estrangeiras)

Em geral, uma chave externa é um atributo (ou combinação de atributos) numa relação R2, cujos valores são necessários para eqüivaler à chave primária de uma relação R1 (R1 e R2 são necessariamente distintos).

Exemplo: Os atributos S# e P# da relação SP são exemplos de chaves externas.

__________________________________________________________________________________


10

Não é necessário que uma chave externa seja um componente da chave primária na relação que a contém.

Exemplo: DEPT (dept#, dname#, loc) EMP (emp#, ename, mgr#, salary, dept#) O atributo EMP.DEPT# é chave externa em relação a EMP (eqüivalendo à chave

primária DEPT.DEPT# da relação DEPT), contudo não é componente da chave primária EMP.EMP# da relação EMP.

Na definição de chave externa, duas relação R1 e R2 são “NÃO NECESSARIAMENTE DISTINTAS”.

O atributo EMP# é chave primária, e MGR# é chave externa, eqüivalendo à chave primária da mesma relação EMP. Integridade da identidade

Não se permite a nenhum atributo que participe da chave primária de uma relação básica que aceite valores nulos. Integridade referencial

As chaves externas que estabelecerem as ligações e implementarem relacionamentos, devem ter valor igual a alguma chave primária de identidade (TABELA) existente. Não pode haver chave externa apontando para uma chave primária inexistente.

__________________________________________________________________________________


11

DICIONÁRIO DE DADOS (DD)

O dicionário de dados pode ser considerado um banco de dados dos sistema, com informações relativa à diversos objetos do interesse do próprio SGDB.

Como exemplo, temos as tabelas, as visões, os índices, os usuários, os privilégios de acesso e outros. O DD pode ser ativo ou passivo. ATIVO quando é respeitado pelo SGDB e consultado em tempo de execução. PASSIVO quando tem função meramente documental.

Como o dicionário de dados é composto basicamente por tabelas e visões, obedecendo a estrutura de dados relacional, podemos então consultar o DD utilizando a linguagem SQL.

LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL)

Um banco de dados projetado através do modelo ER, deve ser mapeado para um esquema relacional através de uma linguagem de definição de dados (DDL). A linguagem que será utilizada neste contexto será SQL padrão ANSI/ISO. COMANDOS CREATE TABLE Cria uma tabela e define suas colunas e outras propriedades. CREATE VIEW Define uma visão sobre uma ou mais tabelas e /ou sobre outras visões. CREATE INDEX Cria índice para uma tabela. ALTER TABLE Inclui ou redefine uma coluna numa tabela existente (não consta no padrão ANSI). DROP TABLE Exclui uma tabela. DROP VIEW Exclui uma visão. DROP INDEX Exclui um índice.

__________________________________________________________________________________


12

LINGUAGEM DE MANIPULAÇÃO DE DADOS (DML) A manipulação do banco de dados é feita através de uma ou mais linguagens

comerciais, que devem respeitar as restrições de integridade do banco de dados. A linguagem utilizada neste contexto será o SQL. COMANDOS SELECT Seleciona linhas e colunas de uma ou mais tabelas. INSERT Inclui linhas numa tabela ou visão. UPDATE Muda o valor de uma ou mais campos de uma tabela. DELETE Exclui linhas de uma tabela.

EXEMPLO DE UM BANCO DE DADOS

O exemplo do banco de dados é composta por 3 tabelas: a tabela EMP que contém dados dos empregados, a tabela DEPT que contém dados dos departamentos e a tabela SALGRADE que contém a grade salarial. A) ESTRUTURA DAS TABELAS DO BANCO DE DADOS SQL> desc 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) DEPTO NUMBER(2)

__________________________________________________________________________________


13

SQL> desc dept Name Null? Type ------------------------------------------- ------------------ ---------------- DEPTO NUMBER(2) DNAME CHAR(14) LOC CHAR(13) SQL> desc sagrade Name Null? Type ------------------------------------------- ------------------ ------------------ GRADE NUMBER LOSAL NUMBER HISAL NUMBER B) DADOS ARMAZENADOS NAS TABELAS SQL> select * from EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTO ---------- ----------- -------------- ------ -------------- ------- --------- ---------

7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7696 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-MAI-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7782 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-91 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected. SQL> select * from DEPT; DEPTO DNAME LOC -------- ------------------- ---------------

10 ACCOUTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

SQL> select * from SALGRADE;

GRADE LOSAL HISAL ---------- ----------- -----------

1 700 1200 2 1201 1400 3 1401 2000

__________________________________________________________________________________


14

4 2001 3000 5 3001 9999

LINGUAGEM SQL A linguagem SQL é própria para a realização de operações como recuperar dados

(QUERY), atualizar ou eliminar dados, alterar colunas de tabelas, criar e eliminar tabelas e qualquer outras modificações que se deseje fazer em um banco de dados relacional.

Os comandos SQL podem ser divididos em quatro grupos de comandos: 1. Query; 2. Definição de dados; 3. Manipulação de dados; 4. Controle de dados;

Em 1986 a ANSI adotou o SQL como uma linguagem padrão para banco de dados relacional - RDBMS.

BENEFÍCIOS • É uma linguagem não procedural; • Permite trabalhar com várias tabelas; • Permite utilizar o resultado de uma instrução SQL em outra instrução SQL (sub-queries); • Não necessita especificar o método de acesso ao dado; • É uma linguagem para vários usuários como: A) Administrador do sistema; B) Administrador do banco de dados (DBA); C) Programadores de aplicações; D) Pessoal da agência e tomada de decisão; • É de fácil aprendizado; • Permite a utilização dentro de uma linguagem procedural como C, COBOL, FORTRAN, Pascal e PL/I - SQL embutida.

OPERAÇÕES DE RECUPERAÇÃO: QUERY A estrutura básica para a recuperação de informações do banco de dados, consiste de 2

cláusulas: FORMATO: SELECT coluna1, coluna2, ..., colunan FROM nome-da-tabela; A cláusula SELECT relaciona as colunas que se quer presentes no resultado da

recuperação. A cláusula FROM relaciona a tabela ou tabelas que devem ser pesquisadas na

execução da query. O SELECT e o FROM são necessários em todas as consultas e sempre devem

aparecer antes de qualquer outra cláusula. O resultado de uma recuperação SQL é sempre uma tabela. Para recuperar todos os valores de todas as colunas da tabela, usa-se o asterisco (*)

após a palavra SELECT.

__________________________________________________________________________________


15

RECUPERAÇÃO SIMPLES SELECT * FROM emp; ou SELECT empno, ename, job, mgr, hiredate, sal, comm, depto FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTO ---------- ----------- -------------- ------ -------------- ------- --------- ---------

7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7696 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-MAI-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7782 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-91 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected. A linguagem SQL suporta as seguintes expressões simples:

ADIÇÃO ( + ) SUBTRAÇÃO ( - ) MULTIPLICAÇÃO ( * ) DIVISÃO ( / )

Observação:

Caso a expressão aritmética contenha mais de um operador, a prioridade é *, / e depois +, -, sempre da esquerda para a direita quando existir vários operadores com a mesma prioridade. Também podem ser utilizados parênteses para especificar a ordem de execução dos operadores.

A) RECUPERAÇÃO USANDO EXPRESSÕES ARITMÉTICAS

Recuperar os números dos empregados, salários e salário anual. SQL> SELECT empno, sal, sal*12 FROM emp;

__________________________________________________________________________________


16

EMPNO SAL SAL*12 ---------- ------- -----------

7369 800 9600 7499 1600 19200 7521 1250 15000 7566 2975 35700 7654 1250 15000 7698 2850 34200 7782 2450 29400 7788 3000 36000 7839 5000 60000 7844 1500 18000 7876 1100 13200 7900 950 11400 7902 3000 36000 7934 1300 15600

14 rows selected SQL> SELECT ename, sal +250 * 12 FROM emp; ENAME SAL+250*12 ---------- --------------- SMITH 3800 ALLEN 4600 . . . . . . MILLER 4300 SQL>SELECT ename, (sal + 250) * 12 FROM emp; ENAME (SAL+250)*12 -------------- ---------------- SMITH 12600 ALLEN 22200 WARD 18000 . . . . . . MILLER 18600

B) APELIDOS DE COLUNAS Para criar um apelido para uma coluna, entre com o apelido depois do nome da coluna

no comando SELECT. SQL> SELECT ename, sal * 12 SAL_ANUAL, comm FROM emp; ENAME SAL_ANUAL COM ----------- ------------------ ---------- SMITH 9600 ALLEN 19200 300 WARD 15000 500 JONES 35700

__________________________________________________________________________________


17

MARTIN 15000 1400 . . . . . . . . . MILLER 15600

C) OPERADOR DE CONCATENAÇÃO

O operador de concatenação permite ligar uma coluna a outra. SQL> SELECT empno||ename EMPREGADO FROM emp; EMPREGADO -------------------------- 7369SMITH 7499ALLEN 7521WARD 7566JONES . . . 7934MILLER

E) MANUSEIO DE VALORES NULOS Sempre que existir um valor nulo na tabela, este deve ser tratado, pois toda expressão

feita com valor nulo o resultado é nulo. Um valor nu lo é diferente de zero. A função NVL converte um valor nulo para outro valor numérico. SQL> SELECT ename, sal, sal * 12 + comm SAL_ANAUAL FROM emp; ENAME SAL SAL_ANUAL ----------- --------- --------------- SMITH 800 ALLEN 1600 19500 WARD 1250 15500 JONES 2975 MARTIN 1250 16400 . . . . . . . . . MILLER 1300

SQL> SELECT ename, sal, sal * 12 + NVL(comm,0) SAL_ANUAL FROM emp;

ENAME SAL SAL_ANUAL ----------- --------- --------------- SMITH 800 9600 ALLEN 1600 19500

__________________________________________________________________________________


18

WARD 1250 15500 JONES 2975 35700 . . . . . . . . . MILLER 1300 15600

A função NVL (comm, 0) substitui por 0 os valores nulos, para que não afetem a soma.

Na multiplicação utiliza-se 1 no lugar de um valor nulo para não afete a multiplicação.

CLÁUSULA DISTINTICT A cláusula distinct é utilizada para eliminar os valores duplicados na saída (no

resultado). A) Recuperação não utili zando a cláusula DISTINCT: Obtenha todos os números dos departamentos da tabela EMP. SQL> SELECT deptno FROM emp;

DEPTNO -------------- 20 30 30 20 30 30 10 20 10 30 20 30 20 10

14 rows selected. A) Recuperação util izando a cláusula DISTINCT: Obtenha todos os números dos departamentos da tabela EMP. SQL> SELECT DISTINCT deptno FROM emp; DEPTNO -----------

10 20 30

DISTINCT pode ser utilizado para múltiplas colunas. SQL> SELECT DISTINCT deptno, job FROM emp; DEPTNO JOB ----------- --------------- 10 CLERK

__________________________________________________________________________________


19

10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANGER 30 SALESMAN 7 rows selected.

CLÁUSULA ORDER BY A cláusula ORDER BY é utilizada para ordenar (classificar) as linhas da tabela . A ordem do resultado pode aparecer em ordem decrescente (DESC) ou ascendente

(ASC) que é default. A cláusula order by sempre deve aparecer por último no comando select.

A) Obtenha o nome, a profissão, o solário de todos os empregados em ordem ascendente de nome do empregado.

SQL> SELECT ename, job, sal FROM emp 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 14 rows selected. B) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de nome do empregado. SQL> SELECT ename, job, sal FROM emp ORDER BY ename DESC; ENAME JOB SAL ----------- -------------- --------- WARD SALESMAN 1250 TURNER SALESMAN 1500 SMITH CLERK 800 SCOTT ANALYST 3000 MILLER CLERK 1300

__________________________________________________________________________________


20

MARTIN SALESMAN 1250 KING PRESIDENT 5000 JONES MANAGER 2975 JAMES CLERK 950 FORD ANALYST 3000 CLARK MANAGER 2450 BLAKE MANAGER 2850 ALLEN SALESMAN 1600 ADAMS CLERK 1100 14 rows selected. C) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de profissão e crescente de salário. SQL> SELECT ename, job, sal FROM emp ORDER BY job DESC, SAL ENAME JOB SAL ----------- --------------- --------- WARD SALESMAN 1250 MARTIN SALESMAN 1500 TURNER SALESMAN 1500 ALLEN SALESMAN 1600 KING PRESIDENT 5000 CLARK MANEGER 2450 BLAKE MANAGER 2850 JONES MANAGER 2975 ADAMS CLERK 1100 MILLER CLERK 1300 SCOTT ANALYST 3000 FORD ANALYST 3000 14 rows selected. D) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de profissão e salário. SQL> SELECT ename, job, sal FROM emp ORDER BY job DESC, sal DESC; ENAME JOB SAL -------------- ---------------- ------- ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250 MARTIN SALESMAN 1250 KING PRESIDENT 5000 JONES MANAGER 2975 BLAKE MANEGER 2850 CLARK MANAGER 2450 MILLER CLERK 1300 ADAMS CLERK 1100 JAMES CLERK 950 SMITH CLERK 800 SCOTT ANALYST 3000

__________________________________________________________________________________


21

FORD ANALYST 3000

14 rows selected.

CLÁUSULA WHERE

A cláusula WHERE é utilizada para especificar a condição ou condições que as linhas selecionadas devem satisfazer.

As condições da cláusula WHERE podem ser valores de colunas, valores literais (seqüência de caracteres), expressões aritméticas ou funções.

Seqüência de caracteres e datas devem ser englobadas entre aspas simples. WHERE especifica a condição desejada, que pode assumir os operadores =, !=, >, >=,

<, <=; operadores booleanos AND, OR e NOT; e parênteses para indicar uma ordem desejada.

OPERADORES LÓGICOS A cláusula WHERE pode assumir os seguintes operadores lógicos:

Igual ( = ) Maior ( > ) Maior ou Igual ( >= ) Menor ( < ) Menor ou Igual ( <= ) SQL> SELECT ename, sal, FROM emp WHERE deptno = 10; ENAME SAL -------------- ------- CLARK 2450 KING 5000 MILLER 1300 SQL> SELECT ename, sal, FROM emp WHERE sal > 2000; ENAME SAL ----------- ------ JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000

6 rows selected.

OPERADORES BOOLEANOS

A cláusula WHERE pode assumir os seguintes operadores booleanos: • AND; • OR; • NOT.

__________________________________________________________________________________


22

AND - As duas condições devem ser satisfeitas para que a consulta retorne uma ou mais linhas da tabela.

Sintaxe: SELECT colunas FROM nome-da-tabela WHERE condição1 AND condição2;

Recuperação Quali ficada:

Obtenha os números dos empregados do departamento 20 com salário maior 1500. SQL> SELECT ename, sal, depto FROM emp WHERE deptno = 20 AND sal > 1500; ENAME SAL DEPTNO ----------- ------ ----------- JONES 2975 20 SCOTT 3000 20 FORD 3000 20

OR - Quando uma ou ambas as condições forem satisfeitas. Sintaxe: SELECT colunas FROM nome-da-tabela WHERE condição1 AND condição2; Obtenha os números dos empregados do departamento 20 ou que possuem salário

maior 1500. SQL> SELECT ename, sal, depto FROM emp WHERE deptno = 20 OR sal > 1500; ENAME SAL DEPTNO ----------- --------- ----------- SMITH 800 20 ALLEN 1600 30 JONES 2975 20 BLAKE 2850 30 CLARK 2450 10 SCOTT 3000 20 KING 5000 10 ADAMS 1100 20 FORD 3000 20 9 rows selected.

USANDO AND E OR NA MESMA CONSULTA

__________________________________________________________________________________


23

Ao usar AND e OR na mesma consulta, você geralmente deve usar parêntese para tornar a consulta mais clara. A colocação dos parênteses pode alterar completamente o resultado. SQL> SELECT ename, job, hiredate, sal, deptno

FROM emp WHERE sal > 500 AND job ='CLERK' AND (hiredate='03-DEC-81' OR hiredate='17-DEC-80);

ENAME JOB HIREDATE SAL DEPTNO ---------- --------- -------------- ------ ----------- SMITH CLERK 17-DEC-80 800 20 JAMES CLERK 03-DEC-81 950 30 2 rows selected. SQL> SELECT ename, job, hiredate, sal, deptno

FROM emp WHERE sal > 500 AND job = 'CLERK' OR (hiredate = '03-DEC-81' AND hiredate = '17-DEC-80');

ENAME JOB HIREDATE SAL DEPTNO ---------- --------- ------------- ------ ----------- SMITH CLERK 17-DEC-80 800 20 ADAMS CLERK 12-JAN-83 1100 20 JAMES CLERK 03-DEC-81 950 30 MILLER CLERK 23-JAN-82 1300 10

OPERADORES SQL

A) Operador BETWEEN ...AND ... O operador BETWEEN..AND... testa os valores da coluna, e recupera os valores que

estão entre um valor mínimo e um valor máximo inclusive especificados no BETWEEN. Obtenha os nomes e salários dos empregados que possuírem salários entre os valores

1000 e 2000. SQL> SELECT ename, sal

FROM emp WHERE sal BETWEEN 1000 AND 2000;

ENAME SAL ----------- ------- ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 ADAMS 1100 MILLER 1300

__________________________________________________________________________________


24

6 rows selected.

B) Operador IN O operador IN testa os valores da coluna, e recupera somente os valores que estão na

lista de valores especificados pelo operador IN. Obtenha os números, os salários e os gerentes dos empregados que possuírem os

gerentes com os números 7902, 7566, 7788. SQL> SELECT empno, sal, mgr

FROM emp WHERE mgr in (7902, 7566, 7788);

EMPNO SAL MGR ---------- ------ ------- 7369 800 7902 7788 3000 7566 7876 1100 7788 7902 3000 7566 4 rows selected. Observação Caso forem usados caracteres ou data na lista, especificá-los entre aspas simples.

C) Operador LIKE O operador LIKE permite recuperar linha da tabela sem saber exatamente o valor que

está sendo procurado, utilizando para isso os símbolos (%) e ( _ ). O símbolo de sublinhado ( _ ) é usado para representar um único caractere. O símbolo de percentagem (%) é usado para representar uma cadeia de caracteres de

qualquer tamanho (inclusive zero). Obtenha os nomes dos empregados que iniciam seu nome com S.

SQL> SELECT ename FROM emp WHERE ename LIKE 'S%';

ENAME ------------------------------ SMITH SCOTT 2 rows selected. Observação O LIKE só pode ser usado com cadeia de caracteres ou com dados gráficos, e não com dados numéricos.

D) Operador IS NULL

__________________________________________________________________________________


25

O operador IS NULL testa os valores da coluna, e recupera somente as linhas da tabela que possuírem o valor null.

Obtenha os números dos empregados que não possuírem gerente, ou seja o campo

número do gerente (mgr) é igual a nulo. SQL> SELECT ename, mgr

FROM emp WHERE mgr IS NULL;

ENAME MGR -------------- ------- KING

E) Utilizando a negação (NOT) Os operadores BETWEEN...AND..., IN, LIKE, e IS NULL podem ser combinados com o

operador de negação NOT. Operador Sign ificado NOT BETWEEN NÃO ENTRE DOIS VALORES ESPECIFICADOS NOT IN NÃO ENTRE UMA LISTA DE VALORES NOT LIKE NÃO ENTRE UM PADRÃO ESPECIFICADO IS NOT NULL NÃO IGUAL AO VALOR NULL

__________________________________________________________________________________


26

AMBIENTE SQL

A conexão em um ambiente SQL é feita através do programa SQL*PLUS. O SQL*PLUS é um programa desenvolvido pela ORACLE CORPORATION, que

permite executar os comando SQL interativamente ou executar os comando SQL a partir de um arquivo de comando (SCRIPTS). O SQL*PLUS, permite formatar resultados, configurar parâmetros e editar e armazenar comando SQL.

A) COMO ENTRAR NO AMBIENTE? EXEMPLO : C:SQLPLUS

2. Informar o usuário e a senha ENTER USER-NAME : SCOTT ENTER PASSWORD : TIGER 3. Em Seguida aparece o prompt do SQL SQL> 4. Digitar os comandos do SQL SQL> SELECT * FROM emp;

B) EDITAR OS COMANDOS SQL 1. Quando você digita um comando SQL, ele é armazenado em uma parte da memória chamada BUFFER SQL, e permanece neste buffer até que outro comando SQL seja digitado. 2. Se você digitou [ENTER] antes de terminar o comando, o SQL*PLUS abre uma linha nova numerada para continuar a digitando o comando SQL. 3. O terminador do comando SQL é o ponto-e-vírgula (;). 4. Enquanto o comando SQL está no buffer, ele pode ser editado utilizando os seguintes comandos

COMANDOS ABREVIAÇÃO DESCRIÇÃO

APPEND texto A texto Adiciona um texto no final da linha corrente CHANGE C/velho/novo/ Altera o texto velho pelo texto novo na linha

corrente CLEAR BUFFER CL BUFF Exclui todas as linhas do Buffer SQL DEL Exclui a linha corrente

INPUT I texto Abre uma nova linha numerada após a linha corrente com um texto

LIST L Lista todas as linhas do buffer LIST n L n Lista uma linha especificada LIST m n L m n Lista um bloco especificado por m (início) e n (fim)

__________________________________________________________________________________


27

RUN R Mostra e executa o comando SQL no buffer / Executa o comando SQL no buffer

D) OUTROS COMANDOS DO SQL*PLUS

COMANDOS DESCRIÇÃO

SAVE nome-de-arquivo Permite salvar o conteúdo do buffer SQL em um arquivo GET nome-de-arquivo Busca para o buffer SQL o conteúdo do arquivo START nome-de-arquivo Executa um arquivo de comandos SQL SPOOL nome-de-arquivo Fecha o arquivo SPOOL DESCRIBE ou DESC Mostra a estrutura da tabela do B.D. EXIT Sai do SQL*PLUS HOST comando Executa um comando do sistema operacional PROMPT texto Mostra um texto

FUNÇÕES INTEGRADAS NO SQL As funções são utilizadas para manipular campos (itens de dados). Estas funções

utilizam um ou mais argumentos, retornando um valor. Um argumento pode ser uma constante, variável ou uma coluna. FORMATO function_name(argumento1, argumento2, ...)

A) FUNÇÕES PARA MANIPULAR CARACTERES a.1) LOWER Converte em letras minúsculas. Exemplo: Obtenha o nome de todos os departamentos em letras minúsculas. SQL> SELECT LOWER(dname)

FROM dept; a.2) UPPER Converte em letras maiúsculas. Exemplo: Obtenha o nome de todos os departamentos em letras maiúsculas. SQL> SELECT UPPER(dname)

FROM dept; a.3) INITCAP Converte a primeira letra de cada palavra para maiúscula. Exemplo: Obtenha o nome e a localização de todos os departamentos, mostrando a primeira letra do nome e localização em maiúscula. SQL> SELECT INITCAP(dname), INITCAP(LOC)

FROM dept; a.4) LENGH

__________________________________________________________________________________


28

Retorna o número de caracteres ou dígitos de uma coluna ou de uma cadeia de caracteres (literal). Exemplo Obtenha o número de caracteres do literal 'SQL COURCE', das colunas deptno e dname. SQL> SELECT LENGH('SQL COURSE'), LENGH(deptno), LENGH(dname)

FROM dept;

B) FUNÇÕES PARA MANIPULAR NÚMEROS b.1) ROUND Retorna o arredondamento de colunas, expressões ou valores para n casas decimais. Se n é omitido, nenhuma casa decimal é colocada. Exemplo: SQL> SELECT ROUND(45.923,1), ROUND(45.923), ROUND(SAL/32,2)

FROM emp WHERE deptno = 10;

SQL>SELECT ROUND(45.473), ROUND(45.4634,2)

FROM dual; b.2) TRUNC Retorna o truncamento de colunas, expressões ou valores em n casas decimais. Se n é omitido nenhuma casa decimal é truncada. Exemplo: SQL> SELECT TRUNC(45.923, 1), TRUNC(45.923), TRUNC(45.452), TRUNC(SAL/32,2)


b.3) SORT Retorna a raiz quadrada de uma coluna ou valor. Se a coluna ou valor forem menores que zero, então retorna um valor nulo. Exemplo: SQL> SELECT sal, SORT(sal), comm, SORT(comm), SORT(144)

FROM emp WHERE comm > 0;

b.4) SIGN

• Retorna -1 se a coluna, ou expressão, ou valor forem negativos; • Retorna 0 se a coluna, ou expressão, ou valor forem zero; • Retorna 1 se a coluna, ou expressão, ou valor forem positivos;

Exemplo: SQL> SELECT sal - comm, sign(sal - comm), comm - sal, sign(comm - sal)


__________________________________________________________________________________


29

b.5) ABS Retorna o valor absoluto de uma coluna ou valor. Exemplo: SQL> SELECT sal, comm, comm-sal, ABS(com-sal), ABS(-35)

FROM emp WHERE comm > 0;

b.6) MOD (valor1, valor2) Retorna o resto da divisão do valor1, dividido pelo valor2. Exemplo: SQL> SELECT sal, comm, MOD(sal, comm), MOD(100,40)

FROM emp WHERE deptno=30 ORDER BY comm;

C) FUNÇÕES PARA MANIPULAR DATAS c.1) SYSDATE Retorna a data e hora corrente. Exemplo: Obtenha a data corrente: SQL> SELECT SYSDATE

FROM dual; OPERAÇÕES ARITMÉTICAS É possível adicionar e subtrair números constantes de datas. DATA + Número de dias DATA - Número de dias DATA - DATA DATA + Número/24 Exemplo: SQL> SELECT hiredate, hiredate+7, hiredate -7, sysdate - hiredate

FROM emp WHERE hiredate LIKE '%JUN%';

SQL> SELECT ename, hiredate

FROM emp WHERE ename='TURNER';

SQL> SELECT SYSDATE - hiredate

FROM emp WHERE ename='TURNER';

c.2) MONTHS_BETWEEN Retorna o número de meses entre duas datas. Exemplo:

__________________________________________________________________________________


30

SQL> SELECT MONTHS_BETWEEN (SYSDATE, hiredate) MONTHS_BETWEEN('01-JAN-84','05-NOV-88') FROM emp WHERE MONTHS_BETWEEN (SYSDATE, hiredate) > 160;

SQL> SELECT MONTHS_BETWEEN('26-AUG-94','26-AUG-94')

FROM dual; c.3) ADD_MONTHS Adiciona n número de meses na data, onde n deve ser um inteiro e pode ser negativo. Exemplo: SQL> SELECT hiredate ADD_MONTHS(hiredate,3), ADD_MONTHS(hiredate,-3)


c.4) NEXT_DAY Retorna a data do próximo dia da semana informado. Exemplo: SQL> SELECT hiredate, NEXT_DAY(hiredate, 'FRIDAY'), NEXT_DAY(hiredate, 6)


c.5) LAST_DAY Retorna a data do último dia do mês. Exemplo: SQL> SELECT SYSDATE, LAST_DAY(SYSDATE), hiredate, LAST_DAY(hiredate)


D) FUNÇÕES DE CONVERSÃO d.1) TO_CHAR Converte um formato de data default para um formato alternativo especificado. TO_CHAR(date, 'date picture') default format date = DD-MON-YY Formatos de datas (date picture)

Date picture Sign ificado

YYYY Ano Y, YYY Ano com vírgula MM Mês MONTH Nome do mês MON Nome do mês com abreviação WW ou W Mês do ano ou mês

__________________________________________________________________________________


31

DDD ou DD ou D Dia do ano, mês ou semana DAY Nome do dia da semana HH ou HH12 Hora do dia (1 - 12) HH24 Hora do dia (0 - 23) MI Minuto SS Segundos Sufixos

TH Número DDTH 14TH

Exemplo: SQL> SELECT SYSDATE

FROM dual; TO_CHAR também é utilizado para converter tipo de dado NUMBER para tipo de dado CHAR (novo formato). TO_CHAR (number, 'number picture') FORMATO DE NÚMEROS (Number picture) Number Picture Significado Exemplo 9 Numérico (o número de 9's determina o

tamanho) 999999 = 1234

0 Mostra zero à esquerda 099999 = 001234 $ Sinal de Dólar $999999 = $1234 .(ponto) Especifica posição do ponto decimal 999999.99 = 1234.00 , (vírgula) Especifica posição da vírgula 999,999 = 1,234 d.2) TO_NUMBER Converte o número armazenado para um tipo de dado NUMBER. Exemplo: SQL> SELECT ename, job, sal

FROM emp WHERE sal > TO_NUMBER('1500');

d.3) TO_DATE Permite formatar para outros formatos de datas. Exemplo: SQL> SELECT ename, hiredate

FROM emp WHERE hiredate = TO_DATE('JUNE 9, 1981', 'MONTH DD, YYYY');

Obs.: O formato default da data é: DD-MON-YY Exemplo: hiredate = 09-JUN-81. d.4) NVL

__________________________________________________________________________________


32

Converte um valor nulo para um valor especificado. Exemplo: SQL> SELECT ename, sal comm, sal * 12 + NVL(comm, 0), NVL(comm, 1000)


d.5) GREATEST Retorna o maior valor de uma lista de valores. Exemplo: SQL> SELECT ename, sal, comm, GREATEST(sal, comm), GREATEST(1000,2000)


d.6) LEAST Retorna o menor valor de uma lista de valores. Exemplo SQL> SELECT ename, sal, comm, LEAST(sal, comm), LEAST(1000, 2000)


d.7) VSIZE Retorna o número de bytes que são utilizados para armazenar internamente os dados (colunas ou valores) Exemplo: SQL> SELECT hiredate, VSIZE(hiredate), sal, VSIZE(sal), ename, VSIZE(ename)


E) FUNÇÕES PARA MANIPULAR GRUPOS

• As funções de grupos servem para manipular um conjunto de linhas (ROWS) da tabela. • Retornam um resultado baseado num grupo de linhas (ROWS). • Por default todas as linhas (ROWS) da tabela são tratadas com um grupo. • A cláusula GROUP BY é usada para dividir as linhas da tabela em grupos menores.

FUNÇÕES DE GRUPO e.1) AVG Retorna a média dos valores da coluna, ignorando valores nulos. Exemplo: Obtenha a média de salários de todos os empregados. SQL> SELECT ename, sal, AVG(sal)

FROM emp;

__________________________________________________________________________________


33

SQL> SELECT AVG(sal ) FROM emp;

e.2) COUNT Retorna o número de ocorrências da expressão, ou coluna. Usando *, retorna todas as ocorrências das linhas incluindo, duplicadas e nulas. Exemplo: Obtenha o número de empregados da empresa SQL> SELCT COUNT(*)

FROM emp; Exemplo: Obtenha o número de empregados do departamento 10 SQL> SELECT COUNT(*)

WHERE deptno = 10; e.3) SUM Retorna a soma dos valores da coluna, ignorando valores nulos. Exemplo: Obtenha a soma de todos os salários dos empregados. SQL> SELECT SUM(sal)

FROM emp; Exemplo: Obtenha a soma de todos os salários do departamento 10. SQL> SELECT SUM(sal)


e.4) MAX Retorna o valor máximo de uma coluna. Exemplo: Obtenha o maior salário dos empregados. SQL> SELECT MAX(sal)

FROM emp; Exemplo: Obtenha o maior salário do departamento 20. SQL> SELECT MAX(sal)


e.5) MIN Retorna o valor mínimo de uma coluna. Exemplo:

__________________________________________________________________________________


34

Obtenha o menor salário dos empregados. SQL> SELECT MIN(sal)

FROM emp; Exemplo: Obtenha o menor salário do departamento 10. SQL> SELECT MIN(sal)


__________________________________________________________________________________


35

CLÁUSULA GROUP BY A cláusula GROUP BY referencia uma coluna especificada da tabela relacionada na

cláusula FROM e agrupa as linhas com base nos valores iguais dessa coluna. O resultado da cláusula GROUP BY particiona a tabela em um conjunto de grupos, não

efetuando qualquer tipo de ordenação.

Exemplo: Obtenha o maior salário de cada profissão. SQL> SELECT job, MAX(sal )

FROM emp GROUP BY job;

Exemplo: Obtenha o menor salário de cada profissão. SQL> SELECT job, MIN(sal)


Exemplo: Obtenha o maior salário da profissão CLERK. SQL> SELECT MAX(sal)

WHERE job = 'CLERK' GROUP BY job;

Exemplo: Obtenha o menor salário da profissão CLERK. SQL> SELECT MIN(sal)

WHERE job = 'CLERK' GROUP BY job;

Exemplo: Obtenha a média salarial por departamento. SQL> SELECT deptno, AVG(sal)

FROM emp GROUP BY deptno;

Observação: a cláusula WHERE só pode ser usada em funções de grupos em casos especiais

CLÁUSULA HAVING A cláusula HAVING especifica uma restrição sobre a tabela agrupada que resultou de

uma cláusula GROUP BY anterior e elimina os grupos que não satisfazem a condição estabelecida.

A cláusula HAVING é usada para especificar a qualidade que um grupo deve ter para ser incluído no resultado. Ela efetua para os grupos a mesma função que a cláusula WHERE efetua para as linhas.

A cláusula HAVING é sempre utilizada junto com a cláusula GROUP BY, sendo que o HAVING é especificado sempre após o GROUP BY.

__________________________________________________________________________________


36

Exemplo: Obtenha a média de salário dos departamentos que possuem mais de 3 empregados. SQL> SELECT deptno, AVG(sal)

FROM emp HAVING COUNT(*) > 3;

Exemplo: Obtenha as profissões que o maior salário é igual ou maior que 3000.

SQL> SELECT job, MAX(sal)

FROM emp HAVING MAX(sal) >= 3000 GROUP BY job;

__________________________________________________________________________________


37

SUBQUERIES (SUBCONSULTAS) Uma subquery é uma cláusula SELECT aninhada com outra cláusula SELECT e que

retorna um resultado intermediário.

Exemplo: SELECT coluna1, coluna2, ..........

FROM tabela WHERE coluna = (SELECT coluna1, coluna2,........ FROM tabela WHERE condição);

A) SUBQUERY DE UMA LINHA

Exemplo: Obtenha o nome, profissão e salário do empregado que possui o menor salário. SQL> SELECT MIN(sal)

FROM emp; {recupera o menor salário de todos os empregados}

SQL> SELECT ename, job, sal

FROM emp WHERE sal = (SELECT MIN(sal) FROM emp) {o resultado acima é usado como parâmetro nesta query}

Como as subqueries aninhadas são processadas Uma subquery é composta por duas cláusulas SELECT, o SELECT principal e select interno. O comando SELECT interno é executado primeiro, produzindo um resultado. No exemplo acima o resultado é 800. O SELECT principal é processado usando o valor retornado pelo SELECT interno.

Observação: sempre que o SELECT interno retorna uma linha da tabela podem ser utili zadas os operadores =, !=, <, <=, >, >=. Exemplo: Obtenha o nome, profissão, de todos os empregados que possuem a mesma profissão do empregado BLAKE. SQL> SELECT job

FROM emp WHERE ename = 'BLAKE';

SQL> SELECT ename, job

FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'BLAKE');

Observação: O SELECT interno retorna a profissão de BLAKE que é MANAGER, e o SELECT externo retorna o no me dos empregados com profissão de MANAGER. B) SUBQUERIES QUE RETORNAM MAIS DE UMA LINHA DA TABELA b.1) OPERADOR IN Relembrando, o operador IN testa (verifica) os valores que estão em uma lista de valores.

__________________________________________________________________________________


38

Exemplo: Obtenha o nome, salário e número do departamento dos empregados que recebem o menor salário de cada empregado. SQL> SELECT MIN(sal)

FROM emp GROUP BY deptno;

SQL> SELECT ename, sal, deptno

FROM emp WHERE sal in (SELECT MIN(sal) FROM emp GROUP BY deptno)

Observação: Sempre que aparecer uma SUBQUERY, onde, no resultado retornam mais de uma l inha da tabela, util iza-se o operador IN. b.2) OPERADORES ANY E ALL

Os operadores ANY e ALL são utilizados para subqueries que retornam mais de uma linha da tabela, e requerem o uso dos operadores igual ( = ), menor ( > ), maior ( < ), menor ou igual (>= ), maior ou igual ( <= ) ou diferente ( !=), aplicado ao resultado de uma subconsulta.

ANY Compara um valor com cada valor da lista de valores que foi retornado pela subquery. Exemplo: Obtenha o nome, salário, profissão e número do departamento de todos empregados que recebem um salário maior que o menor salário dos empregados do departamento 30. SQL> SELECT DISTINCT sal


SQL> SELECT ename, sal, job, deptno

FROM emp WHERE sal > ANY (SELECT DISTINCT sal FROM emp WHERE deptno = 30);

Observação: Quando a ANY é util izado, frequentemente a cláusula DISTINCT é utili zada para prevenir que a mesma linha seja selecionada várias vezes.

ALL Compara um valor com todos os valores da lista de valores retornados pela subquery. Exemplo: Obtenha o nome, salário, profissão e número do departamento de todos os empregados que recebem um salário maior, que todos os salários dos empregados do departamento 30. SQL> SELECT DISTINCT sal


SQL> SELECT ename, sal, job, deptno

FROM emp

__________________________________________________________________________________


39

WHERE sal > ALL (SELECT DISTINCT sal FROM emp WHERE deptno = 30); b.3) OPERADOR NOT O operador NOT também pode utilizado com os operadores IN, ALL e ANY. Utili zação do ORDER BY Não é possível utilizar o ORDER BY em um SELECT interno, a regra é utilizar somente um ORDER BY em uma subquery e este deve ser o último comando do SELECT, ou seja no SELECT mais externo. Aninhamento d e subqueries Também é possível aninhar (encadear) várias queries uma dentro da outra, não existindo limites. C) CLÁUSULA HAVING COM SUBQUERIES ANINHADAS A cláusula WHERE refere-se a uma linha da tabela e o HAVING a um grupo de linhas especificadas na cláusula GROUP BY. Exemplo: Obtenha o número dos departamentos, onde os empregados recebem um salário médio maior que a média salarial do departamento 30. SQL> SELECT AVG(sal)


SQL> SELECT deptno, AVG(sal) FROM emp HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno = 30) GROUP BY deptno;

Exemplo: Obtenha a profissão dos empregados que recebem a mais alta média salarial. SQL> SELECT MAX(AVG(sal))


SQL> SELECT job, AVG(sal)

FROM emp GROUP BY job HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY job);

Observação: O SELECT interno encontra a média salarial par aos grupos de profissões, e a função MAX encontra a maior média salarial dos grupos, que é o valor (5000), este é utili zado pela cláusula HAVING. A cláusula GROUP BY é necessária no SELECT principal para agrupar as profissões.

__________________________________________________________________________________


40

EXTRAIR DADOS DE MAIS DE UMA TABELA

Sempre que é necessário extrair dados de mais de uma tabela um JOIN é feito, também conhecido como ENCADEAMENTO.

A finalidade do JOIN é obter informações que não estão em uma única tabela, e é especificado utilizando-se a cláusula WHERE.

SELECT coluna1, coluna2,....FROM tabela1, tabela2,...... WHERE condição de join;

JOIN-EQUI-JOIN (SIMÉTRICO) O JOIN-EQUI-JOIN ocorre quando a condição de JOIN compara a igualdade entre as

colunas da tabela 1 as colunas da tabela 2. Podemos então observar que o relacionamento entre as duas tabelas (emp e dept) é

uma condição de JOIN "EQUI-JOIN", pois os valores da coluna deptno em ambas as tabelas são iguais e o operador igual ( = ) é utilizado.

Exemplo: Obtenha o nome e a profissão de todos os empregados e o nome dos departamentos

em que os empregados estão lotados. Para resolver este exemplo, é necessário comparar (condição de join) o valor da coluna

deptno da tabela emp cpm o mesmo valor da coluna deptno da tabela dept, extraindo os nomes dos departamentos.

SQL> SELECT ename, job, dname

FROM emp, dept WHERE emp.deptno=dept.deptno;

Exemplo:

Obtenha o nome, a profissão de todos os empregados, o número e nome do departamento em que os empregados estão lotados.

SQL> SELECT ename, job, deptno, dname

FROM emp, dept WHERE emp.deptno = dept.deptno;

Neste exemplo ocorrerá o seguinte erro:

ERROR at line 1: ORA-00918: columm ambiguously defined

PROBLEMA DE AMBIGÜIDADE Sempre que existirem colunas com o mesmo nome em tabelas diferente é necessário

qualificar estas colunas informando o nome da tabela conforme o exemplo: SQL> SELECT ename, job, dep.deptno, dname

FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY dept.deptno;

APELIDOS (ALIAS) PARA TABELAS

__________________________________________________________________________________


41

Para criar um apelido para uma tabela, defina-o na cláusula FROM. O apelido pode então ser utilizado como qualificador tanto na cláusula WHERE, quanto na cláusula SELECT. Os apelidos também são chamados de LABELS TEMPORÁRIOS, e podem ter até 30 caracteres, mas quanto menos melhor. Exemplo: SQL> SELECT ename, job, D.deptno, dname

FROM emp E, dept D WHERE E.deptno = D.deptno ORDER BY D.deptno;

JOIN NON-EQUI-JOIN (NÃO SIMÉTRICO) O JOIN NON-EQUI-JOIN ocorre sempre que um join de colunas de duas tabelas, nas

quais a coluna de ligação de uma tabela não é igual à coluna de ligação correspondente de outra tabela.

O operador de comparação pode ser qualquer operador diferente de igual ( = ). Exemplo:

O join entre as tabelas emp e salgrade é do tipo NON-EQUI-JOIN, onde a grade salarial dos empregados está armazenado na tabela salgrade, e o seu salário deve estar entre (BETWEEN) um valor máximo e mínimo esta grade.

O operador BETWEEN é utilizado para construir a condição. SQL> SELECT E.ename, E.sal, S.grade

FROM emp E, salgrade S WHERE E.sal BETWEEN S.losal AND S.hisal;

OUTRO MÉTODO DE FAZER JOIN Fazendo um join da tabela com ela mesma. SQL> SELECT E.name emp_name, E.sal emp_sal, M.ename mgr_name, M.sal mgr_sal

FROM emp E, emp M WHERE E.mgr = M.empno AND E.sal < M.sal;

Observação: Os apelidos E e M para a tabela emp significa que, E são os empregados e M são o s gerentes.

OPERADORES SET (SET OPEARATORS) UNION A união de duas relações é o conjunto de todas as linhas que estão em uma ou outra relação, ignorando as duplicada, ou seja, retorna a união de dois SELECTs, ignorando as linhas duplicadas. SQL> SELECT job

FROM emp WHERE deptno = 10 UNION SELECT job FROM emp

__________________________________________________________________________________


42

WHERE deptno = 30; INTERSECT A interseção é o conjunto de todas as linhas que estão simultaneamente em ambas as relações, ou seja, retorna a interseção de dois SELECTs. SQL> SELECT job

FROM emp WHERE deptno = 10 INTERSECT SELECT job FROM emp deptno = 30;

MINUS A diferença é o conjunto de todas as linhas que estão em apenas uma das relações, ou seja, retornam a subtração de dois SELECTs. SQL> SELECT job

FROM emp WHERE deptno = 10 MINUS SELECT job FROM emp WHERE deptno = 30;

REGRAS PARA UTILIZAR UNION, INTERSECT e MINUS 1. A cláusula SELECT deve selecionar o mesmo número de colunas. 2. As colunas correspondentes devem ser o mesmo tipo de dado. 3. As linhas duplicadas são automaticamente eliminados. 4. Os nomes das colunas do primeiro SELECT é que aparecem no resultado. 5. A cláusula ORDER BY deve aparecer no final do comando. 6. A cláusula ORDER BY somente pode ser usada indicando o número da coluna. 7. Os operadores UNION, ITERSECT e MINUS podem ser utilizados em subqueries. 8. As colunas SELECTs são executadas de cima para baixo. 9. Vários SET OPERATORS podem ser utilizados. Para indicar a seqüência de execução

deve-se utilizar-se parêntese.

TIPOS DE DADOS Um banco de dados relacional consiste de tabelas, e para criar as tabelas do banco de

dados, é necessário especificar para cada coluna o tipo de dado e seu tamanho máximo. O SQL suporta basicamente os seguintes tipos de dados: A) CHAR (W) Especifica um tipo de dado que é composto de letras, números e caracteres especiais,

onde o W determina o comprimento máximo em caracteres. Não pode ser maior que 255. B) NUMBER Especifica um tipo de dado que é composto por dígitos de 0 a 9. O sinal de mais ( + ),

menos (- ) e ponto decimal são opcionais. C) NUMBER (W) Especifica um tipo de dado que é composto por dígitos de 0 a 9. O sinal de mais ( + ),

menos (- ) e ponto decimal são opcionais. O W especifica o comprimento máximo, não podendo ser maior que 38.

D) NUMBER(W,D)

__________________________________________________________________________________


43

Idem a NUMBER(W), sendo que o D determina o número de dígitos após o pontos decimal.

E) DATE Especifica um tipo de dado para armazenar data e hora. F) LONG Especifica um tipo de dado para qualquer tipo de caracter, podendo armazenar até

65.535 caracteres. Não é permitido definir mais de uma coluna por tabela. Exemplos: NOME_ALUNO CHAR(20) coluna NOME_ALUN com valor caractere, com comprimento

de 20 caracteres.

NUMERO NUMBER(4) coluna NUMERO com valor numérico, com comprimento máximo de 4 dígitos.

SALARIO NUMBER(8,3) coluna salário com valores numéricos, com comprimento

máximo de 8 dígitos, sendo que 3 dígitos para ponto decimal. DATA_MATRÍCULA DATE Coluna DATA_MATRICULA com valor de data. FIGURA Coluna FIGURA com valores LONG.

__________________________________________________________________________________


44

DATA DEFINITION LANGUAGE - DDL Um banco de dados relacional consiste de tabelas, e do ponto de vista do usuário nada

mais que tabelas. A primeira tarefa é criar essas tabelas, utilizando o grupo de comando DDL que permite

criar essas tabelas (CREATE TABLE..........), alterar a estrutura das tabelas (ALTER TABLE...............) e remover tabelas (DROP TABLE.................).

ESTRUTURAS DE DADOS

• Tabelas podem ser criadas a qualquer hora; • O comprimento do armazenamento dos dados é variável, onde somente os

caracteres/números armazenados ocupam espaço, espaços à esquerda e à direita não são armazenados;

• Na criação da tabela, não é necessário especificar o tamanho da tabela; • A estrutura da tabela pode ser alterada ON-LINE;

REGRAS PARA CRIAÇÃO DAS TABELAS

• O nome da tabela deve iniciar com uma letra de A - Z ou a - z; • Pode conter letras, números e o caracter especial ( _ ); • O nome da tabela em letras minúsculas ou maiúsculas é o mesmo; • O nome da tabela não pode ser maior que 30 caracteres; • No nome da tabela não deve ser utilizado palavras reservadas do SQL;

CRIAR UMA TABELA Para definir e criar uma tabela o comando CREATE TABLE é utilizado com a seguinte sintaxe: CREATE TABLE nome-da-tabela (nome_coluna1 tipo_dado(tamanho)) [NULL/NOT NULL], (nome_coluna2 tipo_dado(tamanho)) [NULL/NOT NULL], (nome_coluna3 tipo_dado(tamanho)) [NULL/NOT NULL], ............................ (nome_colunan tipo_dado(tamanho)) [NULL/NOT NULL], Se for especificado a restrição NOT NULL, cada linha deve ter valor diferente de NULL para esta coluna. Se não for especificado nada, a opção NULL default é utilizada. Exemplo: CREATE TABLE dept ( deptno NUMBER(2) not null, dname CHAR(12), loc CHAR(12);

__________________________________________________________________________________


45

CRIAR UMA TABELA UTILIZANDO A ESTRUTURA DE OUTRA

Utilizando o CREATE TABLE e SELECT, podemos criar uma tabela utilizando a estrutura de outra tabela e ao mesmo tempo carregar a nova tabela com dados, conforme a condição especificada. CREATE TABLE nome_tabela [(nome_coluna1 tipo_dado(tamanho) NULL/NOT NULL], (nome_coluna2 tipo_dado(tamanho) NULL/NOT NULL, .................... (nome_colunan tipo_dado(tamanho) NULL/NOT NULL], AS SELECT comando_select;

• A tabela será criada com as colunas e com as linhas especificadas no SELECT; • Caso sejam definidos as colunas no CREATE TABLE, o número de colunas deve ser igual as

do comando SELECT. Exemplo1: Criar uma tabela com as colunas número, nome, profissão e salário dos empregados do departamento 30. CREATE TABLE TAB30

AS SELECT empno, ename, job, sal FROM emp WHERE deptno = 30;

Exemplo 2: Criar uma tabela com as colunas nome, salário e grade salarial de todos os empregados. CREATE TABLE EMP_SALS (nome, salario, gradesal)

AS SELECT ename, sal, grade FROM emp WHERE emp.sal BETWEEN losal AND hisal;

Exemplo utili zando SAVEPOINT e ROLLBACK: INSERT INTO dept (deptno, dname, loc) VALUES (60, TESTE 60', 'loc 60') SAVEPOINTS APOS_INSERT_60 INSERT INTO DEPT (deptno, dname, loc) VALUES (70, TESTE 60', 'loc 70') ROLLBACK APOS_INSERT_60;

__________________________________________________________________________________


46

IMPORTÂNCIA DA TRANSAÇÃO As transações dão uma maior flexibilidade e controle quando se trabalha com dados e

garante a consistência dos dados quando ocorre um erro de usuário ou de sistema.

CONTROLE DA INTEGRIDADE

Para manter o controle da integridade do banco de dados, os comando COMMIT e ROLLBACK são utilizados. Estes comandos também fazem parte do padrão ANSI da linguagem SQL.

CRIAÇÃO DE USUÁRIOS COM RESPECTIVAS OPÇÕES DE ACESSO SINTAXE: ORACLE6 GRANT opção_acesso TO usuário IDENTIFIED BY senha; Exemplo: GRANT CONNECT, RESOURCE TO HANS IDENTIFIED BY SENHA1; SINTAXE: ORACLE7 CREATE USER usuário IDENTIFIED BY senha

CONCEDER PRIVILÉGIOS À NÍVEL DE TABELA O OWNER da tabela pode dar privilégios de acesso as tabelas especificadas. privilégios: SELECT, INSERT, UPDATE, DELETE, ALL objeto: nome_tabela, nome_visão SINTAXE: GRANT privilégio ON objeto TO usuário; Exemplo: GRANT SELECT ON DEPT TO HANS;

ANULAR PRIVILÉGIOS QUE FORAM CONCEBIDOS SINTAXE: REVOKE privilégio ON objeto FROM usuário; Exemplo: REVOKE DELETE ON EMP FROM SCOTT; Observação: Para verif icar quais os usuários e seus privi légios e sobre quais tabelas, visões ou seqüências, selecionar as tabelas do d icionário de dados USER-TAB_GRANTS ou USER_COL_GRANTS;

__________________________________________________________________________________


47

SEGURANÇA DO BANCO DE DADOS O controle de segurança é feito em vários níveis de segurança: A) - Sistema Operacional O sistema Operacional estabelece um procedimento de LOGIN, ou seja, para abrir uma sessão , informa-se um usuário válido e sua senha; B) - Banco de Dados - ORACLE O banco de dados ORACLE também, estabelece um procedimento de LOGIN, ou seja, para conectar-se ao banco de dados, informa-se um usuário válido e sua senha; O administrador do banco de dados (DBA) fornece opções de acesso aos usuários quando são criados, que podem ser: CONNECT - Habilita o usuário a conectar-se ao banco de dados ORACLE e acessar qualquer objeto (TABELA, VISÃO, ETC.) conforme privilégios definidos para aquele usuário; RESOURCE - Habilita o usuário a criar tabelas, seqüências e índices; DBA - Habilita o usuário a criar outros usuários e definir opções de acesso. C) TABLESPACE O usuário só pode alocar uma determinada área na TABLESPACE, definido pelo DBA. D) TABLE (TABLE) O criador das tabelas, chamado de OWNER, é o dono das tabelas criadas e para que outros usuários tenham acesso as mesmas tabelas, o OWNER deve dar privilégios de INSERT, UPDATE e DELETE para cada usuário específico ou para todos usuários (PUBLIC). E) DATE (DADOS) Se o usuário tem acesso a uma tabela, todos os dados desta tabela estão disponíveis para aquele usuário, mas se existir a necessidade de restringir o acesso aos dados de uma tabela específica, isto é possível através da criação de um VIEW, onde o OWNER pode dar acesso somente a determinadas linhas (ROWS) e colunas.

CONTROLE DE CONCORRÊNCIA

Uma das tarefas do RDBMS é o controle da concorrência, que é o acesso aos mesmos dados por vários usuários.

Se não existir um controle de concorrência apropriado, vários dados podem ser alterados incorretamente, comprometendo a integridade dos dados.

Para manter a consistência e integridade do banco de dados, um mecanismo de bloqueio é implantado.

O QUE É O BLOQUEIO (LOCK)?

__________________________________________________________________________________


48

O bloqueio é um mecanismo que é utilizado para controlar o acesso concorrente dos dados em um sistema multi-usuário.

O bloqueio previne que alterações simultâneas não ocorram no mesmo pedaço de dado por dois ou mais usuários, garantindo que uma tabela ou coluna não seja alterada enquanto está ocorrendo uma alteração na tabela.

O ORACLE permite que vários usuários façam leitura ao mesmo dado ao mesmo tempo (concorrente), isto significa que não é necessário fazer um bloqueio (LOCK), quando o banco de dados é acessado para recuperar dados (LEITURA).

Os usuários que fazem leitura não bloqueiam os usuários que fazem escrita, e os usuários que fazem escrita não bloqueiam os usuários que fazem leitura.

QUANDO É NECESSÁRIO O BLOQUEIO (LOCK)?

O bloqueio sempre é necessário quando um usuário tentar fazer alguma alteração no banco de dados.

QUANDO O BLOQUEIO (LOCK) É LIBERADO?

O bloqueio é liberado quando ocorre um COMMIT ou ROLLBACK com sucesso, ou seja, quando a transação é finalizada.

TIPOS DE BLOQUEIO

A) Bloqueio DDL - DATA DICTIONARY LOCK • O controle de acesso é definido nos objetos do Banco de Dados; • É utilizado nas operações dos comandos SQL que modificam o DICIONÁRIO DE DADOS; EX.: CREATE TABLE, ALTER TABLE, DROP TABLE; • Automaticamente controlado pelo núcleo do banco de dados;

B) Bloqueio DML - DATA MANIPULATION LOCK • Bloqueio IMPLÍCITO - É controlado pelo ORACLE, e ocorre automaticamente quando os comandos, INSERT, UPDATE e DELETE são utilizados por um usuário; • Bloqueio Explícito - É controlado pelo usuário, e ocorre quando o usuário especifica o bloqueio desejado na tabela, através dos comandos SQL SELECT...FROM, UPDATE e LOCK TABLE, anulando o bloqueio default. O bloqueio é liberado através de um COMMIT ou ROLLBACK;

NÍVEL DE BLOQUEIO

O bloqueio pode ser feito de duas formas, à nível de tabela ou nível de linha (ROW); • O bloqueio de tabela - Toda tabela fica bloqueada; • O bloqueio de linha (ROW) - Uma linha individual da tabela fica bloqueada.

DEADLOCK

__________________________________________________________________________________


49

Um DEADLOCK pode ocorrer quando dois ou mais usuários estão tentando acessar o mesmo objeto do banco de dados.

REMOVER UMA TABELA Para remover uma tabela do banco de dados, é utilizado o comando DROP TABLE,

conforme sintaxe: DROP TABLE nome-da-tabela; Exemplo: DROP TABLE dept; Importante: A remoção de uma tabela provoca a remoção de todos os dados, todos

os índices e todas as visões. Somente o criador das tabelas ou DBA pode removê-las.

RENOMEAR UMA TABELA O comando RENAME permite que o criador das tabelas possa alterar o nome das

tabelas ou visões. RENAME nome_velho TO nome_novo Exemplo: RENAME emp TO employee; Importante: Todas as aplicações/programas/report que utilizam a tabela renomeada,

também devem ser alterados.

DICIONÁRIO DE DADOS

O DICIONÁRIO DE DADOS é uma das partes mais importantes do banco de dados. Ele é composto por um conjunto de tabelas e visões que possuem uma série informações sobre o banco de dados.

Exemplo: • O nome dos usuários; • Direitos de acesso; • Nome das tabelas, visões, índices, sinônimos, seqüências, etc.

O dicionário de dados é criado quando o banco de dados é criado, e somente pode ser alterado pelo núcleo do banco de dados.

A) ACESSO AO DICIONÁRIO DE DADOS Os usuários podem acessar os dados do dicionário através do comando SELECT,

buscando as informações necessárias. O usuário não pode incluir, alterar ou excluir dados do dicionário de dados, pois compromete toda integridade do banco de dados.

O núcleo do banco de dados (RDBMS) altera automaticamente o dicionário quando ocorrem alterações no banco de dados.

B) TABELAS E VISÕES DO DICIONÁRIO DE DADOS

__________________________________________________________________________________


50

As tabelas do dicionário de dados são automaticamente criadas na criação do banco de dados (CREATE DATABASE) e o criador das tabelas é o usuário SYS. As tabelas são raramente acessadas, pois as informações contidas nas tabelas são de difícil compreensão

Para facilitar o acesso as informações, são criadas visões e todos os usuários têm acesso à elas.

As visões são classificadas em 3 grupos: USER_xxxx Visões com este prefixo possuem informação sobre os objetos criados pelo usuário, ou

seja, dos quais é dono (OWNER). ALL_xxxx O usuário pode acessar objetos aos quais recebeu direitos de acesso e dos quais é o

dono. DBA_xxxx Somente o usuário DBA pode acessar estas visões.

VISÕES QUE NÃO POSSUEM UM DOS PREFIXOS ACIMA

DICTIONARY - Contém todas as tabelas, visões e sinônimos do dicionário de dados que o usuário pode acessar.

Para obter o conteúdo da tabela dictionary: SELECT * FROM DICTIONARY;

Visões do Dicionário Alguns exemplos do que é mostrado

ALL_CATALOG Contém todas as tabelas, visões, sinônimos, seqüências que o usuário pode acessar.

ASALL_INDEXES Descrição de todos os índices das tabelas que o usuário pode acessar. ALL_OBJECTS Todos os objetos que o usuário pode acessar. ALL_SEQUENCES Descrição das seqüências que o usuário pode acessar. ALL_SYNONYMS Todos os sinônimos que o usuário pode acessar. ALL_TABLES Descrição das tabelas que o usuário pode acessar. ALL_USERS Informações sobre todos os usuários do banco de dados. ALL_VIEWS Descrição das visões que o usuário pode acessar. USER_CATALOG Tabelas, visões, sinônimos, seqüências que o usuário pode acessar. USER_INDEXES Índices criados pelo usuário. USER_OBJECTS Objetos criados pelo usuário. USER_SEQUENCES Seqüências criadas pelo usuário. USER_TABLES Descrição das tabelas criadas pelo usuário.

C) COMO ACESSAR INFORMAÇÕES DO DICIONÁRIO DE DADOS Verificar em qual visão estão as informações que o usuário deseja. Exemplo: SQL> DESC USER_OBJECTS;

NAME TYPE

__________________________________________________________________________________


51

-------------------------- ------------------- OBJECT_NAME CHAR(20) OBJECT_ID NUMBER(38) OBJECT_TYPE CHAR(10) CREATED DATE MODIFIED DATE

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, CREATED, MODIFIED

FROM USER_OBJECTS WHERE OBJECT_NAME = 'emp';

OBJECT_NAME OBJECT_TYPE CREATED MODIFIED ------------------- ------------------- -------------- -------------- EMP TABLE 02-MAY-93 01-JUN-94

__________________________________________________________________________________


52

DATA MANIPULATION LANGUAGE - DML A DML é um grupo de comandos SQL utilizados para INSERIR novas linhas, ALTERAR

linhas e EXCLUIR linhas.

A) INSERIR LINHAS NA TABELA O comando INSERT é utilizado para inserir novas linhas (ou ROWS) na tabela.

INSERT INTO nome_da_tabela (nome_coluna1, nome_coluna2, ............) VALUES(valor1, valor2, .....................................);

Exemplo: Inserir o departamento número 50, com nome MARKETING e localização SAN JOSÉ. INSERT INTO dept (depto, dname, loc) VALUES(50, 'MARKETING','SAN JOSÉ');

Observação Colunas com tipo de dado CHAR e DATE, devem ser colocadas entre aspas simples. Para inserir uma data ou hora em outro formato u til iza-se a função TO_DATE.

A.1) UTILIZANDO SUBSTITUIÇÃO DE VARIÁVEIS

INSERT INTO dept (deptno, dname, loc) VALUES(&dept_number, '&dept_name','&location');

Observação: Sempre que o INSERT é executado, um prompt com o nome da variável é mostrado.

A.2) COPIAR LINHAS (ROWS) DE OUTRA TABELA INSERT INTO nome_tabela [(coluna1, coluna2,............)] SELECT comando_select;

Exemplo: INSERT INTO tabd10 (empno, ename, sal, job, hiredate) SELECT empno, ename, sal, job, hiredate FROM emp WHERE deptno = 10;

Observação: A tabela TABD10 deve ser criada antes.

B) ALTERAR LINHAS DA TABELA - SINTAXE O comando UPDATE é utilizado para alterar valores das linhas da tabela.

UPDATE nome_tabela SET nome_coluna1 = valor/expressão

__________________________________________________________________________________


53

[ , nome_coluna2 = valor/expressão, ...........] WHERE condição;

Exemplo: Alterar a profissão para vendedor e aumentar o salário em 10% para o empregado com nome SCOTT. UPDATE emp SET job = 'VENDEDOR' sal = sal *1.1 WHERE ename = 'SCOTT';

C) EXCLUIR LINHAS DA TABELA - SINTAXE O comando DELETE é utilizado para excluir/remover uma ou mais linhas da tabela. DELETE FROM nome_tabela WHERE condição; Exemplo:

Excluir todos os empregados que pertencem ao departamento 10. DELETE FORM emp WHERE deptno = 10; Observação: Cuidado - se WHERE for omitido, todas as l inhas da tabela são exc luídas.

__________________________________________________________________________________


54

VISÕES, SEQÜÊNCIAS E ÍNDICES

VIEW - VISÃO Uma VIEW é derivada de uma tabela ou outra VIEW, normalmente é utilizada para

restringir o acesso a determinados dados de uma tabela. A VIEW não existe fisicamente, é uma tabela virtual.

A) CRIAR UMA VIEW - SINATXE CREATE VIEW nome_view AS SELECT comando_select; Exemplo: Criar uma view. CREATE VIEW d10emp AS SELECT empno, ename FROM emp WHERE deptno = 10;

__________________________________________________________________________________


55

PARA INSERIR DADOS UTILIZANDO UMA VIEW INSERT INTO d10emp (empno, ename) VALUES (7999, 'JOÃO GOIABA'); Observação: Os dados do INSERT são armazenados na tabela EMP. PARA RECUPERAR OS DADOS DE UMA VIEW SELECT * FROM d10emp ORDER by ename; B) REMOVER UMA VIEW - SINTAXE DROP VIEW nome_view; Exemplo: DROP VIEW d10emp;

SEQUENCE - SEQÜÊNCIA O ORACLE permite gerar uma seqüência numérica automática, que por exemplo pode

ser utilizada como CHAVE PRIMÁRIA única. CREATE SEQUENCE nome_sequência [INCREMENTE BY n] [START WITH n] [MAXVALUE n] [MINVALUE n]; Exemplo CREATE SEQUENCE dept_seq INCREMENT BY 10 START WITH 10 MAXVALUE 10000;

GERAÇÃO DO PRÓXIMO NÚMERO SEQUENCIAL Para gerar o próximo número sequencial, utiliza-se o NEXTVAL. Exemplo: INSERT INTO dept VALUES(dep_seq.NEXTVAL, 'ACCOUTING', 'NEW YORK');

UTILIZAÇÃO DO NÚMERO SEQUENCIAL Para referir-se ao número sequencial gerado, utiliza-se o CURRVAL. Exemplo: INSERT INTO dept

__________________________________________________________________________________


56

(dep_seq.CURRVAL, 'ACCOUTING', 'NEW YORK');

INDEXES - ÍNDICES O ORACLE permite a criação de índices para recuperação de dados do banco de dados.

O método utilizado é o B-TREE. A) CRIAR ÍNDICES - SINTAXE CREATE [UNIQUE] INDEX nome_índice ON nome_tabela(coluna1, coluna2,.......); Exemplo: CREATE INDEX i_name ON emp (ename); CREATE UNIQUE INDEX i_empno ON emp (empno); B) EXCLUIR ÍNDICES DROP INDEX nome_índice; Exemplo: DROP INDEX i_name; C) QUANDO O ÍNDICE É UTILIZADO? O ORACLE decide quando é apropriado a utilização do índice normalmente quando a

cláusula WHERE é utilizado e as tabelas possuem uma quantidade significativa de linhas na tabela

PROCESSO TRANSACIONAL TRANSAÇÃO Uma transação é uma operação executada no banco de dados que corrompe uma série de alterações em uma ou mais tabelas. Exemplo: Retirar dinheiro no caixa do banco da sua conta concorrente. Uma transação começa com o primeiro comando executável DML (INSERT, UPDATE, DELETE) e termina quando encontra uma das seguintes situações:

• Um comando COMMIT/ROLLBACK; • Um comando DDL; • Um erro ocorre (por exemplo, um DEADLOCK); • Um erro de máquina (hardware, reset, queda de energia elétrica); • Finalizar o SQL*PLUS (EXIT);

Ao finalizar/terminar a transação, o próximo comando automaticamente indica a próxima transação.

__________________________________________________________________________________


57

COMMIT • O comando COMMIT torna as alterações feitas no banco de dados como permanentes no

banco de dados; • Exclui todos os SAVEPOINTS em uma transação; • Termina a TRANSAÇÃO; • Libera todos os bloqueios (LOCKS) executados pela transação.

COMMIT IMPLÍCITO O COMMIT IMPLÍCITO ocorre nas seguintes situações:

• Antes de um comando DDL; • Após um comando DDL; • Ao terminar o SQL*PLUS (EXIT);

Exemplo de transação: Incluir novo departamento na empresa. INSERT INTO dept (deptno, dname, loc) VALUES(50, 'MARKETING','SAN JOSE'); COMMIT;

__________________________________________________________________________________


58

GERANDO RELATÓRIOS EM SQL A geração de relatórios será executada em ambiente SQL*PLUS, utilizando os seguintes comandos:

A) COMANDO SET O comando SET controla o ambiente no qual o SQL*PLUS está operando, podendo-se fazer uma ambiente default, colocando os comandos SET's no arquivo LOGIN.SQL. Sempre que o SQL*PLUS for executado este arquivo é lido, configurando o SQL*PLUS. Para verificar que variáveis de ambiente estão configuradas, utiliza-se SQL> SHOW ALL; Variáveis util izadas

PAGESIZE [24 n] Especifica o número de linhas por página. Exemplo: SQL>SET PAGESIZE 20;

LINESIZE [80 n] Especifica o tamanho da linha Exemplo: SQL> SET LINESIZE 80

FEEDBACK [6 n OFF/ON] Especifica o número de registros mostrados no comando SELECT. O parâmetro ON/OFF mostra ou não.

ECHO [OFF/ON] Mostra ou não a execução dos comandos SQL*PLUS, quando da utilização do comando START ou @. Exemplo: SQL> SET ECHO ON

HEADING [OFF/ON] Mostra ou não o nome das colunas no resultado do SELECT. Exemplo: SQL> SET HEADING OFF

PAUSE [OFF/ON] Especifica pausa para mostrar no vídeo os resultados, teclando ENTER a cada pausa. Exemplo: SQL> SET PAUSE ON

__________________________________________________________________________________


59

B) COLUMN O comando COLUMN estabelece opções para coluna. COL[UMN] nome_coluna/alias formato opções

Formato Sign ificado Exemplo Resultado

An Alfanumérico, n tamanho 9 Numérico 999999 1234 0 Força zeros à esquerda 099999 001234 $ Sinal de dólar flutuante $999999 $1234 . (ponto) Ponto decimal 999999.99 1234.00 , (vírgula) Vírgula 999,999 1,234 MI Sinal de menos 999999MI 1234-

Opções:

HEADING Especifica um cabeçalho para cada coluna.

JUSTFY Permite especificar o alinhamento da coluna, pode ser: LEFT, CENTER, RIGHT DEFAULT CHAR/DATE é JUSTFY LEFT NUMBER é JUSTFY RIGHT

NULL string Especifica uma string quando o valor é nulo. Exemplo: COL deptno FORMAT 099 HEADING 'Dept.' COL job FORMAT A9 HEADING 'Profissão' JUSTFY RIGHT COL empno FORMAT 9999 HEADING 'Número| Empregado' COL sal FORMAT 99,999.99 HEADING 'Salário| Mensal' COL comm FORMAT 99,999.99 HEADING 'Comissão| Anual' NULL 'Nulo' COL rem FORMAT 999,999.999 HEADING 'Total| Remuneração' SELECT deptno, job, empno, sal, comm, sal * 12 + NVL(comm,0), rem FROM emp; Observação: O caractere barra vertical ( | ) faz a con catenação. SQL> START C:\DBA\REPORT1 Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- ------------------ ------------ ------------ --------------- ---------------- 020 CLERK 7369 800.00 Nulo 9,600.00 030 SALESMAN 7499 1,600.00 300.00 19,500.00 030 SALESMAN 7521 1,250.00 500.00 15,500.00 020 MANAGER 7566 2,975.00 Nulo 35,700.00

__________________________________________________________________________________


60

030 SALESMAN 7654 1,250.00 1,400.00 16,400.00 030 MANAGER 7698 2,850.00 Nulo 34,200.00 010 MANAGER 7782 2,450.00 Nulo 29,400.00 020 ANALYST 7788 3,000.00 Nulo 60,000.00 030 SALESMAN 7844 1,500.00 500,00 18,500.00 020 CLERK 7876 1,100.00 Nulo 13,200.00 030 CLERK 7900 950,00 Nulo 11,400.00 020 ANALYST 7902 3,000.00 Nulo 36,000.00 010 CLERK 7934 1,300.00 Nulo 15,600.00 14 rows selected.

C) COMANDOS TTITLE E BTITILE

TTITLE Mostra um cabeçalho no topo de cada página centralizada. TTITLE "string"

BTITLE Mostra um rodapé de cada página centralizada. BTITLE "string" Para verif icar o TTITLE e BTITLE corrente SQL> TTITLE SQL> BTITLE Para cancelar o TTITLE e BTITLE corrente SQL> TTITLE OFF SQL> BTITLE OFF Exemplo: TTITLE 'Company Report | Produced by Porsonalel DEPT' BTITLE 'Company Confidential'

D) VARÁVEIS DO SISTEMA SQL.PNO Página corrente SQL.LNO Número de linha SQL.USER Username SQL.SQLCODE Último erro numa operação SQL

E) FORMATANDO O RELATÓRIO COM TTITLE E BTITLE

__________________________________________________________________________________


61

TTITLE [formato OFF/ON] BTITLE [formato OFF/ON] formato:

SKIP n Pula n linhas. Se n for omitido, então pula uma linha.

LEFT Alinhamento à esquerda.

CENTER Alinhamento centralizado.

RIGHT Alinhamento à direita.

FORMAT Especifica o formato. Exemplo: TTITLE LEFT FORMAT 0999 'PAGE: 'SQL.PNO - RIGHT 'Produced by | Accouting' SKIP 2 - CENTER 'Confidential Sales Report' SKIP - CENTER '--------------------------' SKIP 2 - BTITLE CENTER 'End of Report' SKIP- CENTER '----------------------' SQL> SELECT ename, job, sal comm FROM emp WHERE comm is not NULL;

__________________________________________________________________________________


62

F) COLOCAR DATA NO RELATÓRIO UTILIZANDO NEW_VALUE O NEW_VALUE especifica um novo valor para coluna Exemplo: COLUMN SYSDATE NEW_VALUE TODAY NO PRINT SELECT SYSDATE FROM SYS.DUAL; TTITLE LEFT 'Date : ' TODAY RIGHT FORMAT 999 'Page: '- SQL.PNO SKIP - LEFT 'USER: ' SQL.USER SKIP - CENTER 'A Report With Reformated Date ' SKIP 2 Observação: NEW_VALUE especifica um novo valor para coluna. A coluna SYSDATE recebe um novo valor da variável TODAY. NOPRINT suprime a impressão do SYSDATE. Exemplo: TTITLE LEFT 'Date: ' TODAY

G) QUEBRA DE RELATÓRIOS O comando BREAK permite definir quebras a nível de coluna, página e relatório. Exemplo: Fazer totalizações no final do relatório. BREAK ON REPORT Opções: PAGE Pula de página quando o valor da coluna é alterado. SKIP n Pula n linhas quando o valor é alterado. DUP[LICATE] Valores duplicados. Default é NODUP. Exemplo 01: BREAK ON REPORT ON deptno PAGE ON job SKIP 2 BREAK ON REPORT ON deptno PAGE ON job DUP Exemplo 02: BREAK ON deptno SKIP 1 ON job ON REPORT SELECT deptno, job, empno, comm, sal*12+nvl(comm,0) rem FROM emp ORDER BY deptno, job; Para cancelar opções de BREAK

__________________________________________________________________________________


63

SQL> CLEAR BREAKS Para mostrar opções de BREAK especificadas SQL> BREAK

H) CÁLCULOS A NÍVEL DE QUEBRA O comando COMPUTE permite definir cálculos em quebras estabelecidas no comando BREAK. FORMATO COMPUTE clause(s) OF column(s) ON BREAK(s) OF Especifica a coluna ou expressão no qual o valor é calculado. ON Especifica o item de dado ou tabela usado para quebra. Clause(s) AVG Valor médio, em tipo NUMBER. COUNT Contador de valores não nulos, em todos os tipos. MAX Valor máximo, em tipo NUMBER e CHAR. MIN Valor mínimo, em tipo NUMBER e CHAR. NUMBER Contador de linhas, em todos os tipos. SUM Somador em valores não nulos, em tipo NUMBER. Para cancelar o comando COMPUTE SQL> CLEAR COMPUTES Para verif icar valores do COMPUTE SQL> COMPUTE Exemplo: BREAK ON deptno SKIP 1 ON job ON REPORT COMPUTE AUG SUM OF sal comm deptno REPORT SELECT deptno, empno, sal, comm, sal*12+NVL(comm,0) rem

__________________________________________________________________________________


64

FROM emp ORDER BY deptno, job; Exemplo 01 COL deptno FORMAT 099 HEADING ‘Dept.’ COL job FORMAT A9 HEADING ‘Profissão’ JUSTFY RIGHT COL empno FORMAT 9999 HEADING ‘Número | Empregado’ COL sal FORMAT 99,999.99 HEADING ‘Salário | Mensal’ COL comm FORMAT 99,999.99 HEADING ‘Comissão | Anual’ NULL ‘Nulo’ COL rem FORMAT 999,999.99 HEADING ‘Total | Remuneração’ SELECT deptno, job, empno, sal, comm, sal*12+nvl(comm,0) rem FROM emp; SQL> start c:\dba\report1 Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- 020 CLERK 7369 800.00 Nulo 9,600.00 030 SALESMAN 7499 1,600.00 300.00 19,500.00 030 SALESMAN 7521 1,250.00 500.00 15,500.00 020 MANAGER 7566 2,975.00 Nulo 35,700.00 030 SALESMAN 7654 1,250.00 1,400.00 16,400.00 030 MANAGER 7698 2,850.00 Nulo 34,200.00 010 MANAGER 7782 2,450.00 Nulo 29,400.00 020 ANALYST 7788 3,000.00 Nulo 36,000.00 010 PRESIDENT 7839 5,000.00 Nulo 60,0000.00 030 SALESMAN 7844 1,500.00 500.00 18,500.00 020 CLERK 7876 1,100.00 Nulo 13,200.00 030 CLERK 7900 950.00 Nulo 11,400.00 020 ANALYST 7902 3,000.00 Nulo 36,000.00 010 CLERK 7934 1,300.00 Nulo 15,600.00 14 rows selected. Exemplo 02 COL deptno FORMAT 099 HEADING ‘Dept.’ COL job FORMAT A9 HEADING ‘Profissão’ JUSTFY RIGHT COL empno FORMAT 9999 HEADING ‘Número | Empregado’ COL sal FORMAT 99,999.99 HEADING ‘Salário | Mensal’ COL comm FORMAT 99,999.99 HEADING ‘Comissão | Anual’ NULL ‘Nulo’ COL rem FORMAT 999,999.99 HEADING ‘Total | Remuneração’ TTITLE ' Empresa Fantasma | Relação de Empregados' BTITLE ' Relatório Confidencial' SELECT deptno, job, empno, sal, comm, sal*12+nvl(comm,0) rem FROM emp; SQL> set pagesize 20 SQL> start c:\dba\report2 Tue Oct 18 page 1

__________________________________________________________________________________


65

Empresa Fantasma Relação de Empregados Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- 020 CLERK 7369 800.00 Nulo 9,600.00 030 SALESMAN 7499 1,600.00 300.00 19,500.00 030 SALESMAN 7521 1,250.00 500.00 15,500.00 020 MANAGER 7566 2,975.00 Nulo 35,700.00 030 SALESMAN 7654 1,250.00 1,400.00 16,400.00 030 MANAGER 7698 2,850.00 Nulo 34,200.00 010 MANAGER 7782 2,450.00 Nulo 29,400.00 020 ANALYST 7788 3,000.00 Nulo 36,000.00 010 PRESIDENT 7839 5,000.00 Nulo 60,0000.00 030 SALESMAN 7844 1,500.00 500.00 18,500.00 020 CLERK 7876 1,100.00 Nulo 13,200.00

Relatório Confidencial

Tue Oct 18 page 2 Empresa Fantasma Relação de Empregados

Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- -------------- 030 CLERK 7900 950.00 Nulo 11,400.00 020 ANALYST 7902 3,000.00 Nulo 36,000.00 010 CLERK 7934 1,300.00 Nulo 15,600.00 Relatório Confidencial 14 rows selected. Exemplo 03 COL deptno FORMAT 099 HEADING ‘Dept.’ COL job FORMAT A9 HEADING ‘Profissão’ JUSTFY RIGHT COL empno FORMAT 9999 HEADING ‘Número | Empregado’ COL sal FORMAT 99,999.99 HEADING ‘Salário | Mensal’ COL comm FORMAT 99,999.99 HEADING ‘Comissão | Anual’ NULL ‘Nulo’ COL rem FORMAT 999,999.99 HEADING ‘Total | Remuneração’ TTITLE LEFT FORMAT 0999 ' Page :' SQL.PNO - RIGHT ' Relatório Produzido Pelo RH' SKIP 2 - CENTER ' Confidencial' SKIP - CENTER '-------------' SKIP 2 BTITLE CENTER ' Entregar para Fulano' SKIP - CENTER '---------------------' SKIP -

__________________________________________________________________________________


66

SELECT deptno, job, empno, sal, comm, sal*12+nvl(comm,0) rem FROM emp; SQL> start c:\dba\report3 Page : 0001 Relatório Produzido Pelo RH Confidencial ------------ Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- 020 CLERK 7369 800.00 Nulo 9,600.00 030 SALESMAN 7499 1,600.00 300.00 19,500.00 030 SALESMAN 7521 1,250.00 500.00 15,500.00 020 MANAGER 7566 2,975.00 Nulo 35,700.00 030 SALESMAN 7654 1,250.00 1,400.00 16,400.00 030 MANAGER 7698 2,850.00 Nulo 34,200.00 010 MANAGER 7782 2,450.00 Nulo 29,400.00 020 ANALYST 7788 3,000.00 Nulo 36,000.00 Page : 0002 Relatório Produzido Pelo RH Confidencial ------------ Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- 010 PRESIDENT 7839 5,000.00 Nulo 60,0000.00 030 SALESMAN 7844 1,500.00 500.00 18,500.00 020 CLERK 7876 1,100.00 Nulo 13,200.00 030 CLERK 7900 950.00 Nulo 11,400.00 020 ANALYST 7902 3,000.00 Nulo 36,000.00 010 CLERK 7934 1,300.00 Nulo 15,600.00 14 rows selected. Exemplo 04 SET PAGESIZE 20 SET LINESIZE 80 COL deptno FORMAT 099 HEADING ‘Dept.’ COL job FORMAT A9 HEADING ‘Profissão’ JUSTFY RIGHT COL empno FORMAT 9999 HEADING ‘Número | Empregado’ COL sal FORMAT 99,999.99 HEADING ‘Salário | Mensal’ COL comm FORMAT 99,999.99 HEADING ‘Comissão | Anual’ NULL ‘Nulo’ COL rem FORMAT 999,999.99 HEADING ‘Total | Remuneração’ COL SYSDATE NEW_VALUE DATA NOPRINT SELECT SYSDATE FROM DUAL; TTITLE LEFT ' Data :' DATA RIGHT FORMAT 0999 ' Page :' SQL.PNO SKIP - LEFT ' Usuário :' SQL.USER - RIGHT ' Relatório Produzido pelo RH' SKIP 2 - CENTER ' Relação de Empregados' SKIP - CENTER '-------------------------' SKIP 2

__________________________________________________________________________________


67

BTITLE CENTER 'Condifencial' SKIP - CENTER '------------' BREAK ON DEPTNO SKIP 1 ON JOB ON REPORT COMPUTE AVG SUM OF sal comm ON DEPTNO JOB REPORT SELECT deptno, job, empno, sal, comm, sal*12+nvl(comm,0) rem FROM emp ORDER BY deptno, job; SQL> start c:\dba\report4 Data :18-Oct-94 Page :0001 Usuário :SCOTT Relatório Produzido Pelo RH Relação de Empregados ------------------------- Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- 010 CLERK 7934 1,300.00 Nulo 15,600.00

*********** --------------- --------------- AVG 1,300.00 SUM 1,300.00 .00 MANAGER 7782 2,450.00 Nulo 29,400.00 *********** --------------- --------------- AVG 2,450.00

Confidencial ------------ Data :18-Oct-94 Page :0002 Usuário :SCOTT Relatório Produzido Pelo RH Relação de Empregados ------------------------- Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- SUM 2,450.00 .00

010 PRESIDENT 7839 5,000.00 Nulo 60,000.00 *********** --------------- AVG 5,000.00 SUM 5,000.00 .00

***** --------------- --------------- AVG 2,916.67


Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- SUM 8,750.00 .00

__________________________________________________________________________________


68

020 ANALYST 7788 3,000.00 Nulo 36,000.00

7902 3,000.00 Nulo 36,000.00 *********** --------------- --------------- AVG 3,000.00 SUM 6,000.00 .00

Confidencial ------------ Data :18-Oct-94 Page :0004 Usuário :SCOTT Relatório Produzido Pelo RH Relação de Empregados ------------------------ Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- -------------- ------------- --------------- --------------- --------------- 020 CLERK 7369 800.00 Nulo 9,600.00

7876 1,100.00 Nulo 13,200.00 *********** --------------- --------------- AVG 950.00 SUM 1,900.00 .00 MANAGER 7566 2,975.00 Nulo 35,700.00 *********** --------------- ---------------

Confidencial ------------- Data :18-Oct-94 Page :0005 Usuário :SCOTT Relatório Produzido Pelo RH Relação de Empregados ------------------------- Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- AVG 2,975.00 SUM 2,975.00 .00

***** --------------- --------------- AVG 2,175.00 SUM 10,875.00 .00

030 CLERK 7900 950,00 Nulo 11,400.00

Confidencial ------------ Data :18-Oct-94 Page :0006 Usuário :SCOTT Relatório Produzido Pelo RH Relação de Empregados ------------------------------ Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

__________________________________________________________________________________


69

------- --------------- ------------- --------------- --------------- --------------- *********** --------------- --------------- AVG 950,00 SUM 950,00 .00

030 MANAGER 7698 2,850.00 Nulo 34,200.00 *********** --------------- AVG 2,850.00 SUM 2,850.00 .00

Confidencial ---------------- Data :18-Oct-94 Page :0007 Usuário :SCOTT Relatório Produzido Pelo RH Relação de Empregados ------------------------- Dept.

Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- 030 SALESMAN 7499 1,600.00 300,00 19,500.00

7654 1,250.00 1,400.00 16,400.00 7844 1,500.00 500,00 18,500.00 7521 1,250.00 500,00 15,500.00 *********** --------------- --------------- AVG 1,400.00 675,00 SUM 5,600.00 2,700.00


Profissão

Número Empregado

Salário Mensal

Comissão Anual

Total Remuneração

------- --------------- ------------- --------------- --------------- --------------- ***** --------------- --------------- AVG 1,566.67 675,00 SUM 9,400.00 2,700.00

--------------- ---------------

AVG 2,073.21 675,00 SUM 29,025.00 2,700.00

Confidencial ---------------- 14 rows selected.

__________________________________________________________________________________


70

PL/SQL O PL/SQL é uma linguagem de 3ª de geração que tem por objetivo processar informações do banco de dados ORACLE. Características

• Permite recuperar, incluir, atualizar e excluir dados de tabelas ; • Permite criar variáveis e constantes; • Permite utilizar comandos condicionais e de repetição.

Estrutura do PL/SQL Um bloco PL/SQL é basicamente composto de uma área de declaração, de comandos e de exceções;

DECLARE . . definir variáveis e constantes . . BEGIN . . /* ---------Comentários------------*/ comandos (exemplo: SELECT ......) . atribuição de variáveis . condições (exemplo: IF...ELSE...END IF) . comando (COMMIT) END:

DEFINIÇÃO DE VARIÁVEIS E CONSTANTES Na definição de variáveis ou constante s, deve-se definir o tipo de dado e tamanho.

Pode-se especificar também que uma variável não pode conter valor nulo utilizando a restrição "NOT NULL". Neste caso ela deve ser iniciada com um valor no ato da definição. VARIÁVEIS São áreas em memórias que s ervem para armazenar dados, podendo conter diversos valores ao longo da execução do programa. CONSTANTES Também são áreas de memória que servem para armazenar dados, mas que possui valor fixo e deve ser inicialmente no ato da declaração e seguido da palav ra "CONSTANT".

TIPOS DE DADOS CHAR Variáveis ou constantes que armazenam valores alfanuméricos com no máximo de 255 caracteres. Caso não seja informado o tamanho, o default '1 1. Exemplo: PAGAMENTO CHAR(40);

__________________________________________________________________________________


71

NUMBER Variáveis ou constantes que armazenam valores numéricos com no máximo de 38 caracteres. Caso não seja informado o tamanho, o default é 38. Também pode ser definido precisão escalar. Exemplo: RECIBO NUMBER(2); CODIGO CONSTANT NUMBER(4) :=1; DATE Variáveis ou constantes que armazenam data, hora (com minutos e segundo e o século). Exemplo: DATA_RECEBE DATE; BOOLEAN Variáveis ou constantes que armazenam TRUE, FALSE ou NULL. Exemplo: FLAG BOOLEAN;

DECLARAÇÃO DE ATRIBUTOS Para declarar uma variável, constante ou coluna com a mesma definição da coluna de uma tabela, o atributo %TYPE pode ser utilizado. Formato nome_tabela.nome_coluna%TYPE; Exemplo: v_empno emp.empno%TYPE;

COMANDOS DE ATRIBUIÇÃO No PL/SQL o comando de atribuição é o sinal de dois-pontos seguido do sinal de

igualdade ( := ). Exemplo: RESULTADO:=NUMERO1/(NUMERO2+NUMERO3);

COMANDOS DE SQL DML (DATA MANIPULATION LAGUAGE)

Para manipular os dados do banco de dados, utiliza-se os comandos INSERT, DELETE, UPDATE e SELECT. O comando SELECT tem que ter a cláusula INTO para relacionar as variáveis onde serão armazenados os valores selecionados. Para controlar as transações com o banco de dados, utiliza-se os comandos COMMIT, SAVEPOINT e ROLLBACK.

Para garantir que os dados a serem manipulados não serão alterados ou excluídos por outros usuários, utiliza-se o comandos LOCK TABLE ou o comando SELECT com a cláusula FOR UPDATE.

__________________________________________________________________________________


72

COMANDO CONDICIONAL (IF...THEN...END IF) O comando IF é utilizado para executar um ou mais comandos de acordo com uma determinada condição. Sintaxe: IF condição THEN comandos.... [ELSEIF condição THEN comandos] [ELSE comandos] END IF Exemplo: REM criado em 05/12/92 /* ** Exemplo de PL/SQL */ DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE of quantity; IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis Racket Purchased',SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of Tennis Rackets', SYSDATE); END IF; COMMIT END; / */ * *This block debits account 3 by $500 onl if there are sufficient ** funds to cover the withdraw. ** ** copyright © 1989, 1992 by Oracle Corporati on */ DECLARE acct_balance NUMBER(11,2); acct CONSTANT NUMBER(4) := 3; debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT bal INTO acct_balance FROM accounts

__________________________________________________________________________________


73

FOR UPDATE OF bal; IF acct_balance >= debit-amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO temp VALUES (acct, acct_balance, 'infficient funds'); --insert account, current balance, and message END IF; COMMIT; END; /

COMANDO DE REPETIÇÃO (FOR...LOOP...END LOOP) O comando FOR permite a execução de n vezes um conjunto de comandos. Sintaxe: FOR contador IN [REVERSE] inicial...final LOOP relação_de_comandos END LOOP /* ** This example illustrates block structure and scope rules. An ** outer block declares two variables named X and COUNTER, and loops four ** times. Inside the loop is a sub-block that also declares a variable ** named X. The values inserted into the TEMP table show that the two ** X's are indeed different. ** */ DECLARE X NUMBER := 0; COUNTER NUMBER := 0; BEGIN FOR i IN 1..4 LOOP X:=X +1000; COUNTER:=COUNTER + 1; INSERT INTO temp VALUES (X, COUNTER, 'in OUTER loop'); /*start an inner block */ DECLARE X NUMBER := 0; --this is a local version of x BEGIN FOR i IN 1..4 LOOP X:=X+1; --this increments the local X COUNTER := COUNTE +1; INSERT INTO temp VALUES (X, COUNTER, 'inner loop'); END LOOP; END; END LOOP; COMMIT; END; /

COMANDO DE REPETIÇÃO (WHILE...LOOP...END LOOP)

__________________________________________________________________________________


74

O comando WHILE é utilizado para executar uma relação de comandos até que a condição se torne FALSA. Sintaxe: WHILE condição LOOP relação_de_comandos END LOOP */ ** This block finds the first employee who has a salary over $4000 ** and is higher in the chain of command then employee 7902 ** */ DECLARE salary emp.sal%TYPE; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno CONSTANT NUMBER(4) := 7902; BEGIN SELECT sal, mgr INTO salary, mgr_num FROM emp WHERE empno = starting_empno; WHILE salary < 4000 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, slary, last_name); COMMIT; END; /

COMANDO DE REPETIÇÃO (LOOP...END LOOP) O comando LOOP é utilizado para executar uma relação de comandos até que a condição definida na saída se torne verdadeira. Sintaxe: LOOP relação_de_comandos END LOOP

__________________________________________________________________________________


75

COMANDO EXIT O comando EXIT é utilizado para interromper a execução de um comando Sintaxe: EXIT [WHEN condição] /* ** Programa exemplo, utilizando FOR...LOOP...END LOOP ** */ DECLARE X NUMBER := 100; BEGIN FOR i NIM 1..10 LOOP IF MOD(i,2) = 0 THEN --i is even INSERT INTO temp VALUES (i, X, 'i is even'); ELSE INSERT INTO temp VALUES (i, X, 'i is odd'); END IF; X:=X + 100; END LOOP; COMMIT; END; /

__________________________________________________________________________________


76

TRATAMENTO DE ERROS (EXCEPTION HANDLERS) Utilizando o EXCEPTION, quando ocorre um erro interno (EXCEPTION), a execução normal do bloco PL/SQL é parada e o controle é transferido para a parte do bloco PL/SQL que trata os erros, que é o EXCEPTION. Quando terminar o tratamento do erro, o controle da execução retorna o próximo comando executável do bloco PL/SQL. Estrutura do bloco PL/SQL utili zando EXCEPTION DECLARE ... ... ... BEGIN ... ... ... EXCEPTION ... ... ... END; Tratadores de EXCEPTION PRÉ-DEFINADOS NO_DATA_FOUND Quando não existirem dados para retornar no comando select. OTHERS Quando ocorrem outros erros. Sintaxe: WHEN tratador_exception THEN comando1; comando2; comando3; Exemplo: DECLARE v_ename CHAR(15); v_job CHAR(10); BEGIN SELECT ename, job INTO v_ename, v_job FROM emp WHERE empno = 1111; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO taberr VALUES ('Erro no Select'); END; /

__________________________________________________________________________________


77

CURSORES Às vezes o PL/SQL necessita de uma área de trabalho para armazenar dados recuperados por um comando SQL. Esta área recebe um nome, e é conhecido como CURSOR.

CURSOR EXPLÍCITOS O comando SELECT normalmente recupera mais de um registro (ROWS) da tabela, e estes registros podem ser armazenados em um CURSOR definido para serem processados mais tarde. Para trabalhar com cursores, os seguintes passos devem ser seguidos:

• Declarar o cursor (DECLARE); • Abrir o cursor (OPEN); • Buscar os dados do cursor (FETCH); • Fechar o cursor (CLOSE).

A) DECLARANDO O CURSOR Sintaxe: CURSOR nome_cursor IS comando_select; B) ABRINDO O CURSOR Sintaxe: OPEN nome_cursor; C) BUSCANDO OS DADOS ARMAZENADOS NO CURSOR Sintaxe: FETCH nome_cursor INTO variáveis_declaradas; D) FECHANDO O CURSOR Sintaxe: CLOSE nome_cursor; Exemplo: DECLARE name CHAR(12); hdate DATE; oldcomm NUMBER(7,2); CURSOR sell IS SELECT ename, hiredate, comm FROM emp WHERE deptno = 30 AND sal > 1000; BEGIN OPEN sell; LOOP FETCH sell INTO name, hdate, oldcomm; IF name = 'SCOTT' THEN EXIT END IF; END LOOP; CLOSE sell; END; /

__________________________________________________________________________________


78

ATRIBUTOS QUE PODEM SER USADOS ATRIBUTO %NOTFOUND Ao atributo é atribuído verdadeiro, se o FETCH não encontrar registros (rows) para buscar, caso contrário é atribuído FALSO. Exemplo: LOOP FETCH sell INTO name, hdate, oldcomm; EXIT WHEN sell%NOTFOUND; .............. END LOOP; ATRIBUTO %FOUND Este atributo é o contrário do %NOTFOUND. Ao atributo é atribuído verdadeiro, se o FETCH encontrar registros (rows) para buscar, caso contrário é atribuído FALSO. Exemplo: LOOP FETCH sell INTO name, hdate, oldcomm; IF sell%FOUND THEN INSERT INTO tabela01 VALUES (...); ELSE EXIT END IF; ....... END LOOP; /* ** Exemplo PL/SQL utilizando CURSOR ** */ DECLARE num1 data_table.n1%TYPE; -- Declare variables num2 data_table.n2&TYPE; -- to be of same type as num3 data_table.n3%TYPE; --database columns result temp.num_col1%TYPE; CURSOR c1 is SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND; --the c1%NOTFOUND condition evaluates -- to TRUE when FETCH finds no more rows /*calculate and store the results*/ result:=num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP;

__________________________________________________________________________________


79

CLOSE c1; COMMIT END; / /* ** ** Programa exemplo, utilizando CURSOR ** */ DECLARE CURSOR c1 IS SELECT ename, empno, sal FROM emp ORDE BY sal DESC; -- start with highest-paid employee my_ename CHAR(10); my_empno NUMBER(4); my_sal NUMBER(7,2); BEGIN OPEN c1; FOR i IN 1..5 LOOP FETCH c1 INTO my_ename, my_empno, my_sal; EXIT WHEN c1%NOTFOUND; /*in case the number requested is more*/ /*than the total number of employees*/ INSERT INTO temp VALUES (my_sal, my_empno, my_ename); COMMIT; END LOOP; CLOSE c1; END; / */ ** Thies block does some numeric processing on data that ** comes from experiment #1. The results are stored in ** the TEMP table. ** */ DECLARE num1 data_table.n1%TYPE; -- Declare variables num2 data_table.n2&TYPE; -- to be of same type as num3 data_table.n3%TYPE; --database columns result temp.num_col1%TYPE; CURSOR c1 is SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND;

__________________________________________________________________________________


80

--the c1%NOTFOUND condition evaluates -- to TRUE when FETCH finds no more rows /*calculate and store the results*/ result:=num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT END;

/ /* ** ** This block does some numeric processing on data that comes ** from experiment #1. The results are stored in the TEMP table. ** */ DECLARE result temp.num_col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1; BEGIN FOR c1rec IN c1 LOOP /* calculate an store the results*/ result:=c1rec.n2/(c1rec.n1 + c1rec.n3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; COMMIT END; /

Documents

Manual Pl SQL Completo