Manual Pl Sql Completo

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 (necessidade 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 (necessidade 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

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

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 facilidade 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 de 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 utilizaçã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; __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

3

__________________________________________________________________________________ F) Permitir e manter o relacionamento entre dados.

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

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, eliminandose 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 banco de dados (DBA) O DBA é o responsável pela administração do banco de dados, utilizando para isso utilitários específicos.

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

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 de 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 necessidades 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 parcial 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.

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

6

__________________________________________________________________________________

ESQUEMA CONCEITUAL O esquema conceitual contém a "visão do mundo real da empresa que está sendo modelada no banco de 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).

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

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 de 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;

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

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. __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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. __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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.

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

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.

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

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 ------------------------------------------EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTO

Null? -----------------NOT NULL

Type --------------NUMBER(4) CHAR(10) CHAR (9) NUMBER(4) DATE NUMBER(7,2) NUMBER(7,2) NUMBER(2)

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

13

__________________________________________________________________________________ SQL> desc dept Name Null? Type --------------------------------------------------------------------------DEPTO NUMBER(2) DNAME CHAR(14) LOC CHAR(13)

SQL> desc sagrade Name ------------------------------------------GRADE LOSAL HISAL

Null? ------------------

Type -----------------NUMBER NUMBER NUMBER

B) DADOS ARMAZENADOS NAS TABELAS SQL> select * from EMP; EMPNO ---------7369 7499 7521 7566 7654 7698 7782 7782 7839 7844 7876 7900 7902 7934

ENAME ----------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

JOB -------------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

MGR -----7902 7698 7696 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

HIREDATE -------------17-DEC-80 20-FEB-81 22-FEB-81 02-APR-81 28-SEP-81 01-MAI-81 09-JUN-81 09-DEC-82 17-NOV-81 08-SEP-81 12-JAN-83 03-DEC-91 03-DEC-81 23-JAN-82

SAL COMM DEPTO ----------------------800 20 1600 300 30 1250 500 30 2975 20 1250 1400 30 2850 30 2450 10 3000 20 5000 10 1500 500 30 1100 20 950 30 3000 20 1300 10

14 rows selected. SQL> select * from DEPT; DEPTO -------10 20 30 40

DNAME ------------------ACCOUTING RESEARCH SALES OPERATIONS

LOC --------------NEW YORK DALLAS CHICAGO BOSTON

SQL> select * from SALGRADE; GRADE ---------1 2 3

LOSAL ----------700 1201 1401

HISAL ----------1200 1400 2000

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

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.

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

15

__________________________________________________________________________________

RECUPERAÇÃO SIMPLES SELECT * FROM emp; ou SELECT empno, ename, job, mgr, hiredate, sal, comm, depto FROM emp; EMPNO ---------7369 7499 7521 7566 7654 7698 7782 7782 7839 7844 7876 7900 7902 7934

ENAME ----------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

JOB -------------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

MGR -----7902 7698 7696 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

HIREDATE -------------17-DEC-80 20-FEB-81 22-FEB-81 02-APR-81 28-SEP-81 01-MAI-81 09-JUN-81 09-DEC-82 17-NOV-81 08-SEP-81 12-JAN-83 03-DEC-91 03-DEC-81 23-JAN-82

SAL COMM DEPTO ----------------------800 20 1600 300 30 1250 500 30 2975 20 1250 1400 30 2850 30 2450 10 3000 20 5000 10 1500 500 30 1100 20 950 30 3000 20 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 ARITMÉTICAS

USANDO

EXPRESSÕES

Recuperar os números dos empregados, salários e salário anual. SQL> SELECT empno, sal, sal*12 FROM emp; __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 ---------SMITH ALLEN . . . MILLER

SAL+250*12 --------------3800 4600 . . . 4300

SQL>SELECT ename, (sal + 250) * 12 FROM emp; ENAME -------------SMITH ALLEN WARD . . . MILLER

(SAL+250)*12 ---------------12600 22200 18000 . . . 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 ----------SMITH ALLEN WARD JONES

SAL_ANUAL -----------------9600 19200 15000 35700

COM ---------300 500

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

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 nulo é 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 ----------SMITH ALLEN WARD JONES MARTIN . . . MILLER

SAL --------800 1600 1250 2975 1250 . . . 1300

SAL_ANUAL --------------19500 15500 16400 . . .

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

SAL --------800 1600

SAL_ANUAL --------------9600 19500

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

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 utilizando 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 utilizando 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 ----------10

JOB --------------CLERK

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

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 ----------ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD

JOB -----------------CLERK SALESMAN MANAGER MANAGER ANALYST CLERK MANAGER PRESIDENT SALESMAN CLERK ANALYST CLERK SALESMAN SALESMAN

SAL -------1100 1600 2850 2450 3000 950 2975 5000 1250 1300 3000 800 1500 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 ----------WARD TURNER SMITH SCOTT MILLER

JOB -------------SALESMAN SALESMAN CLERK ANALYST CLERK

SAL --------1250 1500 800 3000 1300

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

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 ----------WARD MARTIN TURNER ALLEN KING CLARK BLAKE JONES ADAMS MILLER SCOTT FORD

JOB --------------SALESMAN SALESMAN SALESMAN SALESMAN PRESIDENT MANEGER MANAGER MANAGER CLERK CLERK ANALYST ANALYST

SAL --------1250 1500 1500 1600 5000 2450 2850 2975 1100 1300 3000 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 -------------ALLEN TURNER WARD MARTIN KING JONES BLAKE CLARK MILLER ADAMS JAMES SMITH SCOTT

JOB ---------------SALESMAN SALESMAN SALESMAN SALESMAN PRESIDENT MANAGER MANEGER MANAGER CLERK CLERK CLERK CLERK ANALYST

SAL ------1600 1500 1250 1250 5000 2975 2850 2450 1300 1100 950 800 3000

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

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 ----------JONES BLAKE CLARK SCOTT KING FORD

SAL -----2975 2850 2450 3000 5000 3000

6 rows selected.

OPERADORES BOOLEANOS A • • •

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

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

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 Qualificada: 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 ----------JONES SCOTT FORD

SAL DEPTNO ---------------2975 20 3000 20 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 ----------SMITH ALLEN JONES BLAKE CLARK SCOTT KING ADAMS FORD

SAL DEPTNO ------------------800 20 1600 30 2975 20 2850 30 2450 10 3000 20 5000 10 1100 20 3000 20

9 rows selected.

USANDO AND E OR NA MESMA CONSULTA __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 ---------SMITH JAMES

JOB --------CLERK CLERK

HIREDATE -------------17-DEC-80 03-DEC-81

SAL DEPTNO ---------------800 20 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 ---------SMITH ADAMS JAMES MILLER

JOB --------CLERK CLERK CLERK CLERK

HIREDATE ------------17-DEC-80 12-JAN-83 03-DEC-81 23-JAN-82

SAL DEPTNO ---------------800 20 1100 20 950 30 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 ----------ALLEN WARD MARTIN TURNER ADAMS MILLER

SAL ------1600 1250 1250 1500 1100 1300

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

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 ---------7369 7788 7876 7902

SAL MGR -----------800 7902 3000 7566 1100 7788 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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 -------------KING

MGR -------

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 NOT BETWEEN NOT IN NOT LIKE IS NOT NULL

Significado NÃO ENTRE DOIS VALORES ESPECIFICADOS NÃO ENTRE UMA LISTA DE VALORES NÃO ENTRE UM PADRÃO ESPECIFICADO NÃO IGUAL AO VALOR NULL

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

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

APPEND texto CHANGE

A texto C/velho/novo/

CLEAR BUFFER DEL

CL BUFF

INPUT

I texto LIST LIST n LIST m n

L Ln Lmn

DESCRIÇÃO Adiciona um texto no final da linha corrente Altera o texto velho pelo texto novo na linha corrente Exclui todas as linhas do Buffer SQL Exclui a linha corrente Abre uma nova linha numerada após a linha corrente com um texto Lista todas as linhas do buffer Lista uma linha especificada Lista um bloco especificado por m (início) e n (fim)

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

27

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

D) OUTROS COMANDOS DO SQL*PLUS COMANDOS SAVE nome-de-arquivo GET nome-de-arquivo START nome-de-arquivo SPOOL nome-de-arquivo DESCRIBE ou DESC EXIT HOST comando PROMPT texto

DESCRIÇÃO Permite salvar o conteúdo do buffer SQL em um arquivo Busca para o buffer SQL o conteúdo do arquivo Executa um arquivo de comandos SQL Fecha o arquivo SPOOL Mostra a estrutura da tabela do B.D. Sai do SQL*PLUS Executa um comando do sistema operacional 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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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) FROM emp WHERE deptno = 10;

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) FROM emp WHERE deptno = 30;

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

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:

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

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) FROM emp WHERE deptno = 20;

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) FROM emp WHERE deptno = 10;

c.5) LAST_DAY Retorna a data do último dia do mês. Exemplo: SQL> SELECT SYSDATE, LAST_DAY(SYSDATE), hiredate, LAST_DAY(hiredate) FROM emp WHERE deptno = 20;

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 YYYY Y, YYY MM MONTH MON WW ou W

Significado Ano Ano com vírgula Mês Nome do mês Nome do mês com abreviação Mês do ano ou mês

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

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 DDTH

Número 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 9 Numérico (o número de 9's determina o tamanho) 0 Mostra zero à esquerda $ Sinal de Dólar .(ponto) Especifica posição do ponto decimal , (vírgula) Especifica posição da vírgula

Exemplo 999999 = 1234 099999 = 001234 $999999 = $1234 999999.99 = 1234.00 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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

32

__________________________________________________________________________________ Converte um valor nulo para um valor especificado. Exemplo: SQL> SELECT ename, sal comm, sal * 12 + NVL(comm, 0), NVL(comm, 1000) FROM emp WHERE deptno = 30;

d.5) GREATEST Retorna o maior valor de uma lista de valores. Exemplo: SQL> SELECT ename, sal, comm, GREATEST(sal, comm), GREATEST(1000,2000) FROM emp WHERE deptno = 30;

d.6) LEAST Retorna o menor valor de uma lista de valores. Exemplo SQL> SELECT ename, sal, comm, LEAST(sal, comm), LEAST(1000, 2000) FROM emp WHERE deptno = 30;

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) FROM emp WHERE deptno = 30;

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; __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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) FROM emp WHERE deptno = 10;

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) FROM emp WHERE deptno = 20;

e.5) MIN Retorna o valor mínimo de uma coluna. Exemplo: __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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) FROM emp WHERE deptno = 10;

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

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) FROM emp GROUP BY job; 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. __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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;

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

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 utilizadas 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 nome 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. __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 linha da tabela, utiliza-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 FROM emp WHERE deptno = 30; SQL> SELECT ename, sal, job, deptno FROM emp WHERE sal > ANY (SELECT DISTINCT sal FROM emp WHERE deptno = 30);

Observação: Quando a ANY é utilizado, frequentemente a cláusula DISTINCT é utilizada 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 FROM emp WHERE deptno = 30; SQL> SELECT ename, sal, job, deptno FROM emp __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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.

Utilizaçã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 de 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) FROM emp WHERE deptno = 30; 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)) FROM emp GROUP BY job; 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 é utilizado pela cláusula HAVING. A cláusula GROUP BY é necessária no SELECT principal para agrupar as profissões.

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

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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 os 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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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. 2. 3. 4. 5. 6. 7. 8. 9.

A cláusula SELECT deve selecionar o mesmo número de colunas. As colunas correspondentes devem ser o mesmo tipo de dado. As linhas duplicadas são automaticamente eliminados. Os nomes das colunas do primeiro SELECT é que aparecem no resultado. A cláusula ORDER BY deve aparecer no final do comando. A cláusula ORDER BY somente pode ser usada indicando o número da coluna. Os operadores UNION, ITERSECT e MINUS podem ser utilizados em subqueries. As colunas SELECTs são executadas de cima para baixo. 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)

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

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.

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

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);

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

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 utilizando 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; __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 verificar quais os usuários e seus privilégios e sobre quais tabelas, visões ou seqüências, selecionar as tabelas do dicionário de dados USERTAB_GRANTS ou USER_COL_GRANTS; __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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)? __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 ALL_CATALOG ASALL_INDEXES ALL_OBJECTS ALL_SEQUENCES ALL_SYNONYMS ALL_TABLES ALL_USERS ALL_VIEWS USER_CATALOG USER_INDEXES USER_OBJECTS USER_SEQUENCES USER_TABLES

Alguns exemplos do que é mostrado Contém todas as tabelas, visões, sinônimos, seqüências que o usuário pode acessar. Descrição de todos os índices das tabelas que o usuário pode acessar. Todos os objetos que o usuário pode acessar. Descrição das seqüências que o usuário pode acessar. Todos os sinônimos que o usuário pode acessar. Descrição das tabelas que o usuário pode acessar. Informações sobre todos os usuários do banco de dados. Descrição das visões que o usuário pode acessar. Tabelas, visões, sinônimos, seqüências que o usuário pode acessar. Índices criados pelo usuário. Objetos criados pelo usuário. Seqüências criadas pelo usuário. 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

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

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 ------------------EMP

OBJECT_TYPE ------------------TABLE

CREATED -------------02-MAY-93

MODIFIED -------------01-JUN-94

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

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 utiliza-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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 linhas da tabela são excluídas.

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

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;

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

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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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.

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

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;

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

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 utilizadas

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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

59

__________________________________________________________________________________

B) COLUMN O comando COLUMN estabelece opções para coluna. COL[UMN] nome_coluna/alias formato opções Formato An 9 0 $ . (ponto) , (vírgula) MI

Significado Alfanumérico, n tamanho Numérico Força zeros à esquerda Sinal de dólar flutuante Ponto decimal Vírgula Sinal de menos

Exemplo

999999 099999 $999999 999999.99 999,999 999999MI

Resultado

1234 001234 $1234 1234.00 1,234 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 concatenação. SQL> START C:\DBA\REPORT1 Número Salário Comissão Total Dept. Profissão Empregado Mensal Anual 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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 verificar 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 SQL.LNO SQL.USER SQL.SQLCODE

Página corrente Número de linha Username Último erro numa operação SQL

E) FORMATANDO O RELATÓRIO COM TTITLE E BTITLE __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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' SKIPCENTER '----------------------'

SQL> SELECT ename, job, sal comm FROM emp WHERE comm is not NULL;

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

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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 verificar 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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

64

__________________________________________________________________________________ FROM emp ORDER BY deptno, job; Exemplo 01 COL COL COL COL COL COL

deptno job empno sal comm rem

FORMAT FORMAT FORMAT FORMAT FORMAT FORMAT

099 A9 9999 99,999.99 99,999.99 999,999.99

HEADING HEADING HEADING HEADING HEADING HEADING

‘Dept.’ ‘Profissão’ JUSTFY RIGHT ‘Número | Empregado’ ‘Salário | Mensal’ ‘Comissão | Anual’ NULL ‘Nulo’ ‘Total | Remuneração’

SELECT deptno, job, empno, sal, comm, sal*12+nvl(comm,0) rem FROM emp; SQL> start c:\dba\report1

Dept. ------020 030 030 020 030 030 010 020 010 030 020 030 020 010

Profissão --------------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

Número Salário Empregado Mensal --------------------------7369 800.00 7499 1,600.00 7521 1,250.00 7566 2,975.00 7654 1,250.00 7698 2,850.00 7782 2,450.00 7788 3,000.00 7839 5,000.00 7844 1,500.00 7876 1,100.00 7900 950.00 7902 3,000.00 7934 1,300.00

Comissão Total Anual Remuneração ----------------------------Nulo 9,600.00 300.00 19,500.00 500.00 15,500.00 Nulo 35,700.00 1,400.00 16,400.00 Nulo 34,200.00 Nulo 29,400.00 Nulo 36,000.00 Nulo 60,0000.00 500.00 18,500.00 Nulo 13,200.00 Nulo 11,400.00 Nulo 36,000.00 Nulo 15,600.00

14 rows selected. Exemplo 02 COL deptno FORMAT 099 HEADING COL job FORMAT A9 HEADING COL empno FORMAT 9999 HEADING COL sal FORMAT 99,999.99 HEADING COL comm FORMAT 99,999.99 HEADING COL rem FORMAT 999,999.99 HEADING TTITLE ' Empresa Fantasma | Relação de Emprega dos' BTITLE ' Relatório Confidencial'

‘Dept.’ ‘Profissão’ JUSTFY RIGHT ‘Número | Empregado’ ‘Salário | Mensal’ ‘Comissão | Anual’ NULL ‘Nulo’ ‘Total | Remuneração’

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

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

65

__________________________________________________________________________________ Empresa Fantasma Relação de Empregados

Dept. ------020 030 030 020 030 030 010 020 010 030 020

Profissão --------------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK

Número Salário Empregado Mensal --------------------------7369 800.00 7499 1,600.00 7521 1,250.00 7566 2,975.00 7654 1,250.00 7698 2,850.00 7782 2,450.00 7788 3,000.00 7839 5,000.00 7844 1,500.00 7876 1,100.00 Relatório Confidencial

Tue Oct 18

Comissão Total Anual Remuneração ----------------------------Nulo 9,600.00 300.00 19,500.00 500.00 15,500.00 Nulo 35,700.00 1,400.00 16,400.00 Nulo 34,200.00 Nulo 29,400.00 Nulo 36,000.00 Nulo 60,0000.00 500.00 18,500.00 Nulo 13,200.00

page 2 Empresa Fantasma Relação de Empregados

Dept. ------030 020 010

Profissão --------------CLERK ANALYST CLERK

Número Salário Empregado Mensal --------------------------7900 950.00 7902 3,000.00 7934 1,300.00

Comissão Total Anual Remuneração ---------------------------Nulo 11,400.00 Nulo 36,000.00 Nulo 15,600.00

Relatório Confidencial 14 rows selected.

Exemplo 03 COL deptno FORMAT 099 HEADING COL job FORMAT A9 HEADING COL empno FORMAT 9999 HEADING COL sal FORMAT 99,999.99 HEADING COL comm FORMAT 99,999.99 HEADING COL rem FORMAT 999,999.99 HEADING TTITLE LEFT FORMAT 0999 ' Page :' SQL.PNO RIGHT ' Relatório Produzido Pelo RH' SKIP -2 CENTER ' Confidencial' SKIP CENTER '-------------' SKIP 2 BTITLE CENTER ' Entrega r para Fulano' SKIPCENTER '---------------------' SKIP-

‘Dept.’ ‘Profissão’ JUSTFY RIGHT ‘Número | Empregado’ ‘Salário | Mensal’ ‘Comissão | Anual’ NULL ‘Nulo’ ‘Total | Remuneração’

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

66

__________________________________________________________________________________ SELECT deptno, job, empno, sal, comm, sal*12+nvl(comm,0) rem FROM emp; SQL> start c:\dba\report3

Page : 0001

Dept. ------020 030 030 020 030 030 010 020

Profissão --------------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST

Relatório Produzido Pelo RH Confidencial -----------Número Salário Empregado Mensal --------------------------7369 800.00 7499 1,600.00 7521 1,250.00 7566 2,975.00 7654 1,250.00 7698 2,850.00 7782 2,450.00 7788 3,000.00

Page : 0002

Dept. ------010 030 020 030 020 010

Profissão --------------PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

Comissão Total Anual Remuneração ----------------------------Nulo 9,600.00 300.00 19,500.00 500.00 15,500.00 Nulo 35,700.00 1,400.00 16,400.00 Nulo 34,200.00 Nulo 29,400.00 Nulo 36,000.00

Relatório Produzido Pelo RH Confidencial -----------Número Salário Empregado Mensal --------------------------7839 5,000.00 7844 1,500.00 7876 1,100.00 7900 950.00 7902 3,000.00 7934 1,300.00

Comissão Total Anual Remuneração ----------------------------Nulo 60,0000.00 500.00 18,500.00 Nulo 13,200.00 Nulo 11,400.00 Nulo 36,000.00 Nulo 15,600.00

14 rows selected. Exemplo 04 SET PAGESIZE 20 SET LINESIZE 80 COL deptno FORMAT 099 HEADING COL job FORMAT A9 HEADING COL empno FORMAT 9999 HEADING COL sal FORMAT 99,999.99 HEADING COL comm FORMAT 99,999.99 HEADING COL rem FORMAT 999,999.99 HEADING COL SYSDATE NEW_VALUE DATA NOPRINT

‘Dept.’ ‘Profissão’ JUSTFY RIGHT ‘Número | Empregado’ ‘Salário | Mensal’ ‘Comissão | Anual’ NULL ‘Nulo’ ‘Total | Remuneração’

SELECT SYSDATE FROM DUAL; TTITLE LEFT ' Data :' DATA RIGHT FORMAT 0999 ' Page :' SQL.PNO SKIP LEFT ' Usuário :' SQL.USER RIGHT ' Relatóri o Produzido pelo RH' SKIP 2CENTER ' Relação de Empregados' SKIP CENTER '-------------------------' SKIP 2 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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 Usuário :SCOTT

Dept. Profissão --------------------010 CLERK *********** AVG SUM MANAGER *********** AVG

Data :18-Oct-94 Usuário :SCOTT

Dept. -------

Profissão --------------SUM 010 PRESIDENT *********** AVG SUM ***** AVG

Data :18-Oct-94 Usuário :SCOTT

Dept. ------SUM

Profissão ---------------

Page :0001 Relatório Produzido Pelo RH Relação de Empregados ------------------------Número Salário Empregado Mensal --------------------------7934 1,300.00 --------------1,300.00 1,300.00 7782 2,450.00 --------------2,450.00 Confidencial ------------

Comissão Total Anual Remuneração ----------------------------Nulo 15,600.00 --------------.00 Nulo ---------------

29,400.00

Page :0002 Relatório Produzido Pelo RH Relação de Empregados ------------------------Número Salário Comissão Total Empregado Mensal Anual Remuneração ------------------------------------------------------2,450.00 .00 7839 5,000.00 Nulo 60,000.00 --------------5,000.00 5,000.00 .00 --------------- --------------2,916.67 Confidencial -----------Page :0003 Relatório Produzido Pelo RH Relação de Empregados ------------------------Número Salário Comissão Total Empregado Mensal Anual Remuneração ------------------------------------------------------8,750.00 .00

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

68

__________________________________________________________________________________ 020 ANALYST *********** AVG SUM

7788 7902

3,000.00 Nulo 3,000.00 Nulo ----------------------------3,000.00 6,000.00 .00 Confidencial ------------

Data :18-Oct-94 Usuário :SCOTT

Dept. Profissão -------------------020 CLERK *********** AVG SUM MANAGER ***********

Page :0004 Relatório Produzido Pelo RH Relação de Empregados -----------------------Número Salário Empregado Mensal --------------------------7369 800.00 7876 1,100.00 --------------950.00 1,900.00 7566 2,975.00 --------------Confidencial -------------

Comissão Total Anual Remuneração ----------------------------Nulo 9,600.00 Nulo 13,200.00 --------------.00 Nulo ---------------

Data :18-Oct-94 Usuário :SCOTT

Dept. -------

Profissão --------------AVG SUM

***** AVG SUM 030 CLERK

Profissão

35,700.00

Page :0005 Relatório Produzido Pelo RH Relação de Empregados ------------------------Número Salário Comissão Total Empregado Mensal Anual Remuneração ------------------------------------------------------2,975.00 2,975.00 .00 --------------- --------------2,175.00 10,875.00 .00 7900 Confidencial ------------

950,00 Nulo

Data :18-Oct-94 Usuário :SCOTT

Dept.

36,000.00 36,000.00

11,400.00

Page :0006 Relatório Produzido Pelo RH Relação de Empregados -----------------------------Número Salário Empregado Mensal

Comissão Anual

Total Remuneração

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

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 Usuário :SCOTT

Dept. Profissão --------------------030 SALESMAN

*********** AVG SUM

Page :0007 Relatório Produzido Pelo RH Relação de Empregados ------------------------Número Salário Comissão Total Empregado Mensal Anual Remuneração ------------------------------------------------------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 ----------------------------1,400.00 675,00 5,600.00 2,700.00 Confidencial ------------

Data :18-Oct-94 Usuário :SCOTT

Dept. Profissão --------------------***** AVG SUM

AVG SUM

Page :0008 Relatório Produzido Pelo RH Relação de Empregados ------------------------Número Salário Comissão Total Empregado Mensal Anual Remuneração ----------------------------------------------------------------------------------1,566.67 675,00 9,400.00 2,700.00 --------------2,073.21 29,025.00 Confidencial ----------------

--------------675,00 2,700.00

14 rows selected.

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

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); __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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.

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

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 __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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) __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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

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

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; /

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

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 utilizando 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; /

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

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; / __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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; __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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; __________________________________________________________________________________________ Copyright © 2003 SmarttNet Solution Provider - All rights reserved.

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; /

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