492997

Apostila de Banco de Dados v.4.0

1

Banco de Dados

Prof. Flavio Rezende



2

1 Evolução dos dos Sistemas de de Informação................................................... Informação........................................................................... ........................ 6 2 Introdução Introdução ................................................. ....................................................... ............... 7

2.1 2.2 2.3

Modelo Conceitual de Dados (MCD) .................................. ................ .................................... ................................... ............................ ........... 7 Modelo Lógico de Dados (MLD) ................................... ................. .................................... ................................... .................................. ................. 7 Modelo Físico de Dados (MFD)................ (MFD) .................................. .................................... .................................... ................................... ..................... 7

3 Modelo Conceitual de Dados Dados (MCD) (MCD) ..................................................... ........................ 7

3.2

Entidades Entidades .................. ........................... .................. .................. .................. .................. .................. .................. .................. .................. ................... ................... ................ ....... 9

3.3

Atributos..................... Atributos... .................................... ................................... ................................... .................................... ................................... ................................ ............... 11

3.4

Relacionamentos ................................... ................. ................................... ................................... .................................... ................................... ....................... ...... 12

3.2.1 3.2.2 3.2.3 3.2.4 3.2.5 3.2.6 3.2.7 3.2.8 3.3.1 3.3.2 3.3.3 3.3.4

3.4.1 3.4.2 3.4.3 3.4.4 3.4.5 3.4.6 3.4.7 3.4.8 3.4.9 3.4.10 3.4.11 3.4.12 3.4.13 3.4.14 3.4.15

Especifi Especificaçõe caçõess ............................ .......................................... ............................ ............................ ............................ ............................ ............................ .......................... ............ 9 Eventos Eventos ou Ocorrência Ocorrênciass ............................ .......................................... ............................ ............................ ............................ ............................ ........................ .......... 10 Especifi Especificaçõe caçõess ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ........................ .......... 10 A Dicionarização dos Objetos Modelados...................... Modelados........... ..................... ..................... ...................... ..................... ..................... ................ ..... 10 Regras Básicas para para a Dicionarização de Objetos (Entidades/Classes). (Entidades/Classes)........... ..................... ..................... .............. .... 10 Requisitos a serem atendidos na dicionarização................ dicionarização...... ..................... ...................... ..................... ..................... ..................... .............. 10 Dependênc Dependência ia de Existênci Existênciaa ............................ .......................................... ............................ ........................... ........................... ............................ .................... ...... 11 Dependênc Dependência ia de Identific Identificador........................ ador...................................... ............................ ........................... ........................... ............................ .................... ...... 11 Atributos Atributos Descritivo Descritivoss ........................... ......................................... ............................ ............................ ............................ ............................ ............................ ................ 11 Atributos Atributos Nominativo Nominativoss ........................... ......................................... ............................ ............................ ........................... ........................... ........................... ............. 11 Atributos Atributos Referencia Referenciais is ............................ .......................................... ............................ ............................ ........................... ........................... ........................... ............. 11 Dicionari Dicionarização zação dos Atributos Atributos ............................ .......................................... ............................ ............................ ............................ ............................ ................ 12

Identifi Identificação cação dos Relacionam Relacionamentos entos .......................... ........................................ ............................ ............................ ............................ ...................... ........ 12 Denominaçã Denominaçãoo dos Relaciona Relacionament mentos os ............................ .......................................... ........................... ........................... ............................ .................... ...... 12 Caracterização dos Relacionamentos.................. Relacionamentos............................ ..................... ..................... ..................... ..................... ..................... .................. ....... 12 Grau de Cardinali Cardinalidade dade do Relaciona Relacionament mentoo ............................ .......................................... ............................ ............................ ...................... ........ 12 Relaciona Relacionament mentos os incondici incondicionais onais ............................ .......................................... ............................ ............................ ............................ ........................ .......... 13 Relaciona Relacionament mentos os Condiciona Condicionais................. is............................... ............................ ............................ ............................ ............................ ........................ .......... 13 Quanto à Existência Simultânea de Relacionamentos........... Relacionamentos ..................... ..................... ...................... ..................... ................... ......... 14 Quanto Quanto à Presença Presença de Atributos....... Atributos..................... ............................ ............................ ........................... ........................... ............................ .................... ...... 15 Dicionari Dicionarização zação de Relacionam Relacionamentos.............. entos............................ ............................ ........................... ........................... ............................ .................... ...... 15 Estrutura Estrutura de Generaliz Generalização ação e Especific Especificação ação ........................... ......................................... ............................ ............................ ...................... ........ 15 Especializações Mutuamente Exclusivas (Categorias).......... (Categorias) .................... ..................... ...................... ..................... ................... ......... 15 Especialização Não-mutuamente Exclusivas (Papéis).................... (Papéis)......... ..................... ..................... ..................... ..................... ........... 15 Níveis Níveis de Especiali Especialização zação ............................ .......................................... ............................ ............................ ............................ ............................ ...................... ........ 16 Quando Utilizar a Estrutura de Generalização-Especi Generalização-Especialização........ alização................... ...................... ..................... ................... ......... 16 Como Apresent Apresentar ar os Subgrupos Subgrupos Existente Existentess ............................ .......................................... ............................ ............................ ...................... ........ 16

4 Modelo Lógico de Dados Dados (MLD) (MLD)................................................... ................................................... ............................... 17

4.1 4.2

Passos para a Derivação do Modelo Lógico ................................... ................. ................................... ................................ ............... 17 Implementação do Modelo Relacional....................... Relacional..... .................................... .................................... ................................... ................. 17

4.3

Normalização.............................................. Normalização............................. ................................... .................................... .................................... ................................... ................. 18

4.2.1 4.3.1 4.3.2 4.3.3 4.3.4

Regras Regras de Derivação................... Derivação................................. ............................ ............................ ............................ ............................ ............................ ........................ .......... 18

Benefício Benefícioss da Normaliz Normalização.......................... ação........................................ ............................ ........................... ........................... ............................ .................... ...... 18 Primeira Primeira Forma Forma Normal Normal (1FN)............ (1FN) .......................... ............................ ............................ ............................ ............................ ............................ ................ 19 Segunda Segunda Forma Normal Normal (2FN).............. (2FN) ............................ ............................ ............................ ........................... ........................... ........................... ............. 20 Terceira Terceira Forma Forma Normal Normal (3FN)................... (3FN)................................. ............................ ............................ ............................ ............................ ...................... ........ 21 Prof. Flavio Rezende

Apostila de Banco de Dados v.4.0 4.3.5

4.4

3

Derivação Derivação de Agregações Agregações ........................... ......................................... ............................ ............................ ............................ ............................ ...................... ........ 22

Restrições no Modelo Lógico ................................... .................. ................................... .................................... ................................... .................... ... 22

4.4.1 4.4.2 4.4.3 4.4.4

Restriçõe Restriçõess de Domínio........... Domínio......................... ............................ ............................ ............................ ............................ ............................ ............................ ................ 23 Restriçõe Restriçõess de Integridad Integridade.............. e............................ ............................ ............................ ............................ ............................ ............................ ...................... ........ 23 Restriçõe Restriçõess de Implement Implementação ação ............................ .......................................... ............................ ............................ ............................ ............................ ................ 24 Objetos Objetos básicos básicos de um SGBDR..................................... SGBDR................................................... ............................ ............................ ............................ ................. ... 24

5 Modelo de Entidades Entidades e Relacionamentos Relacionamentos (ER)............................................................ 27

5.1 5.2 5.3 5.4 5.5 5.6

Entidade......................... Entidade....... .................................... ................................... ................................... .................................... ................................... ............................. ............ 27 Relacionamento............................... Relacionamento............. ................................... ................................... .................................... ................................... ............................. ............ 27 Cardinalidade..................................... Cardinalidade................... ................................... ................................... .................................... ................................... .......................... ......... 28 Generalização / Especialização :........................... :.......... ................................... .................................... ................................... ....................... ...... 28 Entidade Fraca.......................... Fraca........ ................................... ................................... .................................... .................................... ................................... ................. 29 Agregação :....................... :..... .................................... ................................... ................................... .................................... ................................... .......................... ......... 30

6 Regras de conversão conversão do Modelo Modelo Conceitual (E-R) para para o Modelo Lógico. Lógico. ............... 31

6.1 6.2 6.3 6.4 6.5

Entidade......................... Entidade....... .................................... ................................... ................................... .................................... ................................... ............................. ............ 31 Entidade Fraca.......................... Fraca........ ................................... ................................... .................................... .................................... ................................... ................. 31 Relacionamentos ................................... ................. ................................... ................................... .................................... ................................... ....................... ...... 32 Especializa Especialização ção ................. .......................... .................. ................... ................... .................. .................. .................. .................. .................. .................. ................ ....... 32 Agregação................ Agregação .................................. ................................... ................................... .................................... .................................... ................................... ................. 33

7 Linguagem Linguagem SQL. ................................................ ....................................................... .... 33

7.1

Introdução Introdução ao SQL .................. ........................... ................... ................... .................. .................. .................. .................. .................. .................. ................ ....... 33

7.2

Operadores SQL ................................... ................. ................................... ................................... .................................... ................................... ....................... ...... 44

7.3

Expressões Negativas............................... Negativas............. ................................... ................................... .................................... ................................... .................... ... 45

7.4

Funções Funções .................. ........................... .................. .................. .................. .................. .................. .................. .................. .................. .................. ................... .................. ........ 48

7.1.1 7.1.2 7.1.3 7.1.4 7.1.5 7.1.6 7.1.7 7.1.8 7.1.9 7.1.10 7.1.11 7.1.12 7.1.13 7.2.1 7.2.2 7.2.3 7.2.4 7.3.1 7.4.1 7.4.2

SQL*Plus.......... SQL*Plus........................ ............................ ............................ ............................ ............................ ............................ ............................ ............................ ...................... ........ 34 PL/SQL.................... PL/SQL.................................. ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................ 36 Sintaxe Sintaxe de um bloco PL/SQL: PL/SQL: ........................... ......................................... ............................ ............................ ............................ ............................ ................ 36 Alguns Alguns Comandos Comandos SQL:.................................... SQL:.................................................. ............................ ............................ ............................ ............................ ................ 36 SELECT SELECT ............................ .......................................... ............................ ............................ ............................ ........................... ........................... ............................ .................... ...... 36 Expressõe Expressõess Aritméti Aritméticas.......... cas........................ ............................ ............................ ............................ ............................ ............................ ............................ ................ 37 Colunas Colunas Sinônimas Sinônimas ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ................. ... 39 Operador Operador de Concatena Concatenação......................... ção....................................... ............................ ............................ ............................ ............................ ...................... ........ 39 Literais................. Literais............................... ............................ ............................ ............................ ............................ ........................... ........................... ............................ .................... ...... 40 Manuseand Manuseandoo Valores Valores Nulos Nulos ........................... ......................................... ............................ ........................... ........................... ............................ .................... ...... 40 Prevenindo a Seleção Seleção de Linhas Duplicadas – Cláusula Cláusula Distinct Distinct .................... .......... ..................... ..................... .............. .... 41 A clausula clausula ORDER BY ............................ .......................................... ............................ ............................ ............................ ............................ ........................ .......... 42 A Clausula Clausula WHERE.............. WHERE ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................ 43 O Operador Operador BETWEEN BETWEEN ........................... ......................................... ............................ ............................ ............................ ............................ ........................ .......... 44 O Operador Operador IN........................................ IN...................................................... ............................ ............................ ........................... ........................... ........................... ............. 44 O Operador Operador LIKE....................................... LIKE..................................................... ............................ ............................ ............................ ............................ ...................... ........ 44 Operador Operador IS NULL................................. NULL............................................... ............................ ............................ ........................... ........................... ........................... ............. 45 Pesquisan Pesquisando do Dados com Múltiplas Múltiplas Condições Condições ............................ .......................................... ............................ ............................ ................. ... 47 Funções Funções Alfanumér Alfanuméricas icas ............................ .......................................... ............................ ............................ ............................ ............................ ........................ .......... 49 Aninhamen Aninhamento to de Funções Funções ........................... ......................................... ............................ ............................ ............................ ............................ ...................... ........ 53


Apostila de Banco de Dados v.4.0 7.4.3 7.4.4 7.4.5 7.4.6 7.4.7 7.4.8 7.4.9 7.4.10

4

Funções Numéricas................................................................................................................... 53 Funções de Data........................................................................................................................ 56 Funções de Conversão.............................................................................................................. 59 Funções que Aceitam Vários Tipos de Entrada de Dados........................................................ 63 Funções de Grupo..................................................................................................................... 65 A cláusula GROUP BY ............................................................................................................ 66 A clausula HAVING ................................................................................................................ 67 A Ordem das clausulas na declaração SELECT....................................................................... 68

7.5

Executando Pesquisas Padrões com Variáveis Substituíveis........................................ 68

7.6

Recuperando valores da base de dados........................................................................... 71

7.7

Seqüências ......................................................................................................................... 74

7.8

Índices................................................................................................................................ 75

7.9

Controle de Concorrência................................................................................................ 75

7.5.1 7.5.2 7.5.3 7.5.4 7.6.1 7.7.1 7.7.2 7.8.1 7.9.1

Única Variável Substituível...................................................................................................... 68 Duplo & para Variáveis substituíveis....................................................................................... 69 O Comando DEFINE................................................................................................................ 70 O comando ACCEPT ............................................................................................................... 70 Comandos de Manipulação de Dados (DML) .......................................................................... 72 Alterando uma seqüência.......................................................................................................... 74 Eliminando uma seqüência....................................................................................................... 74 Recuperando informações sobre Índices: ................................................................................. 75

Tipos de Lock........................................................................................................................... 75

7.10

Declarações.................................................................................................................... 76

7.11 7.12 7.13

Codificação de Comando SQL Dentro de PL/SQL.................................................... 82 Tratamento de Transações........................................................................................... 82 Estruturas de Controle ................................................................................................. 83

7.14

Cursores:........................................................................................................................ 84

7.15

Tratamento de Exceções............................................................................................... 88

7.10.1 7.10.2 7.10.3 7.10.4 7.10.5

7.13.1 7.13.2 7.13.3 7.13.4 7.14.1 7.14.2 7.14.3 7.14.4 7.14.5 7.15.1 7.15.2

Declarações de Variáveis.......................................................................................................... 76 Tipos de Dados PL/SQL........................................................................................................... 77 Subtype..................................................................................................................................... 80 Atribuindo Valores às Variáveis............................................................................................... 81 Escopo de Variáveis ................................................................................................................. 81

IF – THEN – ELSE .................................................................................................................. 83 WHILE-LOOP ......................................................................................................................... 84 FOR-LOOP............................................................................................................................... 84 LOOP........................................................................................................................................ 84 Comandos de Manipulação do cursor:...................................................................................... 85 Close......................................................................................................................................... 86 O Comando For para abrir Cursores:........................................................................................ 87 Atualização na tabela da linha atual do cursor: ........................................................................ 87 Cursores Implícitos:.................................................................................................................. 87 Exceções Predefinidas .............................................................................................................. 88 Exceções Definidas pelo Usuário ............................................................................................. 89

Coloca uma linha no buffer, e mostra na tela. ............................................... ...................... 90

7.16

7.16.1 7.16.2

Subprogramas (Procedures e Functions).................................................................... 90

Parâmetros................................................................................................................................ 91 Procedures ................................................................................................................................ 91


Apostila de Banco de Dados v.4.0 7.16.3 7.16.4 7.16.5 7.16.6

5

Functions: ................................................................................................................................. 91 Executando subprogramas através do SQL* Plus: ................................................................... 91 Eliminando um subprograma:................................................................................................... 91 Análise das dependências:........................................................................................................ 91

7.17

Packages......................................................................................................................... 92

7.18

Triggers.......................................................................................................................... 93

7.17.1 7.17.2 7.17.3 7.18.1 7.18.2

Especificação............................................................................................................................ 92 Body ......................................................................................................................................... 92 Execução de Estruturas Públicas de uma Package: .................................................................. 93 Criação de triggers.................................................................................................................... 93 Triggers possíveis para uma tabela:.......................................................................................... 93

8 Projeto físico de Banco de Dados:.................................... Error! Bookmark not defined.

8.1 8.2 8.3 8.4 8.5

Criação de Tabelas ........................................................................................................... 98 Criação de Views............................................................................................................... 98 Criação de Stored Procedures :....................................................................................... 99 Criação de Functions :...................................................................................................... 99 Criação de triggers : ....................................................................................................... 100

9 Privilégios....................................................................................................................101 10

Exercícios.................................................................................................................104

10.1 10.2 10.3 10.4

Modelo Conceitual e Lógico....................................................................................... 104 SQL............................................................................................................................... 112 PLSQL ......................................................................................................................... 113 Banco de Dados Distribuído....................................................................................... 114



1  

Evolução dos Sistemas de Informação. Nos anos 70 , o programa era o foco principal dos sistemas de informação. Dados e apresentação de telas para usuários, ficavam em segundo plano. Dependência dos programas com relação aos dados armazenados.

Usuário





P R O G R A M A

Dados

O SGBD (Sistema Gerenciador de Banco de Dados) é uma camada lógica entre os programas e os dados, tornando assim os programas independentes dos dados.

Usuário



6

P R O G R A M A

S G B D

Programas  Programas + Arquivos  Programas + SGBD + Banco de Dados Banco de Dados  Coleção de dados.

Características do SGBD Relacional (também conhecidos como SGBDR). Linguagem SQL.  Backup/Recovery.  Segurança.  Integridade.  Controle de concorrência de transações.  Desempenho. 


Dados


2

7

Introdução

2.1 Modelo Conceitual de Dados (MCD) Define-se como Modelo Conceitual aquele em que os objetos, suas características e relacionamentos têm a representação fiel ao ambiente observado. Devemos representar os conceitos e características observados em um dado ambiente, voltando-nos simplesmente ao aspecto conceitual. Nesse nível devem ser ignoradas quaisquer particularidades de implementação, bem como desconsiderada qualquer preocupação com qual será o modo de implementação futura, permanecerá imutável.

2.2 Modelo Lógico de Dados (MLD) Define-se como aquele em que os objetos, suas características e relacionamentos têm a representação de acordo com as regras de implementação e limitantes impostos por algum tipo de tecnologia. Esse modelo deve ser o modelo elaborado respeitando-se e implementando-se os conceitos tais como chave de acesso, controles de chaves duplicadas, itens de repetição (arrays), normalização, ponteiros, headers, integridade referencial, entre outros.

2.3 Modelo Físico de Dados (MFD) É aquele em que a representação dos objetos é feita sob o foco do nível físico de implementação das ocorrências, ou instâncias das entidades e seus relacionamentos. Cada diferente SGBD poderá definir um diferente modo de implementação física das características e recursos necessários para o armazenamento e manipulação das estruturas de dados. No modelo físico podem ser incluídos dois níveis de representação. O primeiro deles diz respeito às ocorrências ou instâncias, seus relacionamentos e disposição física dos elementos. Outro diz respeito à alocação de espaços físicos nos diversos níveis de agrupamento possíveis: tabelas (arquivos), blocos, linhas (registros) e colunas (campos). Integração da Arquitetura de Três Níveis com a Abordagem E-R Uma integração entre a abordagem de modelos conceitual, lógico e físico e a proposta do grupo ANSI-X3-SPARK é possível se forem estabelecidos os níveis de projeto.

3

Modelo Conceitual de Dados (MCD)

3.1.1.1 3.1.1.2 Antes de mais nada, o quê é uma tabela ? Tabela ou Relação: Uma representação lógica da organização dos dados dentro do SGBDR. É uma tabela de valores, o seu nome e o nome das colunas, servem para auxiliar a interpretação do significado de seus valores. Valor : É usado para representar 'alguma coisa'. É a menor partícula de dados do modelo relacional, eles são indivisíveis.



8

Domínio : É um conjunto de valores atômicos, de mesmo tipo. São usados para preencher os valores de uma coluna. ex: Nome char(40) : restringe a coluna Nome a ter seus valores dentro do domínio de no máximo 40 caracteres ou seja um nome de 50 caracteres não é um nome válido para este domínio. Linha ou Tupla : Representa uma linha da tabela, onde cada linha, representa uma coleção de valores.

Coluna ,atributo

MATRICULA 01 02 03 04 ...

NOME Zé Maria João Margarida

SALARIO 200,00 1000,00 800,00 150,00

Linha , Tupla

Valor Conceitos de Chave: Primary Key (PK) :Atributo ou conjunto de atributos de uma tabela que identificam univocamente uma  linha. Toda Primary key deve ser minimal. Foreign Key (FK) : Atributo ou conjunto de atributos de uma tabela que referencia a chave primária em  outra tabela, ou no caso do auto relacionamento, na mesma tabela. Unique Key(UK) : Atributo de uma tabela que tem todas as qualificações para ser uma chave primária.  OBS. Muitos autores representa a chave primária de uma tabela com as colunas sublinhadas, e as chaves estrangeiras com uma linha acima das colunas.



9

Ex: FUNCIONARIOS(MATRICULA , NOME , SALARIO , CPF , DEPTO)

Ex:

UK

PK

FUNCIONARIOS MATRICULA 01 02 03 04 ...

FK para DEPARTAMENTOS, ou seja, todos os valores desta coluna tem que existir na tabela de DEPARTAMENTOS na coluna da PK.


SALARIO 200,00 1000,00 800,00 150,00

CPF 12999666 18765789 49555969 89993393

DEPTO 01 02 02 01

DEPARTAMENTOS DEPTO DESCRICAO 01 Recursos Humanos 02 Diretoria PK

3.1.1.3 Modelo E-R A Abordagem Entidade-Relacionamento A técnica de diagramação é bastante simples e serve como meio de representação dos próprios conceitos por ela manipulados. Utiliza, basicamente, um retângulo para representar as entidades, um losango para representar os relacionamentos e balões para indicar e alocar os atributos.

3.2 Entidades Identificação de Objetos (Entidades ou Classes) Representação gráfica:

Aluno

A estratégia proposta por Shlaer & Mellor, define que deveremos procurar reconhecer objetos, ou elementos individualizados, através da observação de cinco grandes grupos de elementos: - As coisas tangíveis - As funções exercidas por elementos - Interações

3.2.1 Especificações As classificações propostas têm como objetivo servir como um meio de discernimento para casos não facilmente enquadráveis ou mesmo para verificação e validação do processo de modelagem.



3.2.1.1.1

10

Coisas Tangíveis

O grupo de “Coisas Tangíveis” engloba todos os elementos que tenham existência concreta. Funções Definimos como todo o tipo de papel, atribuição, classificação, capacitação, ou outra característica qualquer que, para um dado elemento, especifique não sua existência mas sua atuação no ambiente em que está inserido.

3.2.2 Eventos ou Ocorrências Alguns objetos, ou elementos individualizados, só conseguem ser percebidos, ou caracterizados, enquanto uma certa ação se desenrola. Em outras palavras, quando alguma ação, ou fato, acontece conseguimos definir características que os tornam materializáveis. Enquanto programado, durante sua execução ou após encerrado, esse elemento caracteriza-se como um evento ou ocorrência ao qual podemos fazer alguma referência.

3.2.2.1.1

Interações

Os chamados objetos-interação são resultantes da associação de objetos em função de um processo executado. Os objetos-interação passam a existir de maneira individualizada, além dos objetos que participam dessa interação.

3.2.3 Especificações São elementos que definem características de outros objetos. Representam especificações que quando aplicadas ou seguidas darão origem a objetos.

3.2.4 A Dicionarização dos Objetos Modelados Cada um dos elementos identificados e representados deverá, em um segundo instante, ser definido claramente para que, associando-se seu nome, sua representação e sua definição, sejamos capazes de ter o completo entendimento de conceito que estes procuram transmitir.

3.2.5 Regras Básicas para a Dicionarização de Objetos (Entidades/Classes) Uma das principais tendências quando passamos a dicionarizar um objeto é nos referenciarmos a ele como um meio de armazenamento e não como o próprio objeto que ele representa.

3.2.6 Requisitos a serem atendidos na dicionarização Para dicionarização de um objeto, ou entidade, deveremos ter em mente algo que satisfaça, senão todas, a grande parte das seguintes perguntas: - O que é o elemento? - O que faz o elemento? - Para que serve? - O que engloba essa categoria de elementos? - O que está excluído dessa categoria Prof. Flavio Rezende

Apostila de Banco de Dados v.4.0 -

11

Quando alguém passa a ser, ou deixa de ser, um elemento desse tipo? Sua permanência nessa categoria é imutável?

Considerações sobre Entidades Fortes e Entidades Fracas Essa caracterização se dá através da análise de existência de duas condições básicas: - Dependência de existência - Dependência de identificador Dizemos que uma entidade é fraca se um desses dois tipos de dependência se verificar entre uma entidade A e uma entidade B.

3.2.7 Dependência de Existência Se B depender de A para existir, teremos em B uma entidade fraca, enquanto que A, se não depender de ninguém para existir, será considerada, será considerada uma entidade forte.

3.2.8 Dependência de Identificador Esse segundo critério de definição de entidades fortes e entidades fracas, tem sua importância reconhecida sob o ponto de vista de projeto lógico, onde as chaves identificadoras, utilizadas como diferenciadores entre instâncias dos elementos, ou como método de endereçamento de registros, passando a ter papel vital durante o processo de projeto de estruturas de dados.

3.3 Atributos O Papel dos Atributos Ao observarmos objetos em um ambiente, estaremos, na verdade, reconhecendo tais elementos através da identificação de suas características próprias. Essas características, inerentes a cada um desses objetos, serão, em princípio, comuns a todos os objetos, ou elementos individualizados, pertencentes a um mesmo conjunto. Classificação dos Atributos quanto a sua finalidade Após termos identificado os atributos de cada um dos objetos, podemos, sob o ponto de vista de classificação quanto a sua finalidade, enquadra-los em três grandes grupos:

3.3.1 Atributos Descritivos Todo e qualquer atributo que seja capaz de demonstrar, ou representar, características formadoras, ou pertencentes, a um objeto poderá vir a ser enquadrado como descritivo.

3.3.2 Atributos Nominativos Engloba todos aqueles atributos que, além de cumprirem a função de descritivos, também servem como definidores de nomes ou rótulos de identificação aos objetos aos quais pertencem. Qualquer atributo que possa identificar um objeto, é chamado de Atributo Nominativo.

3.3.3 Atributos Referenciais Os Atributos classificados como Referenciais são aqueles que não pertencem ao objeto onde estão alocados, mas fazem algum tipo de citação, ou ligação, desse objeto com um outro objeto.



12

3.3.4 Dicionarização dos Atributos A Dicionarização dos Atributos identificados nos objetos por nós observados é, complementarmente à dicionarização dos próprios objetos e entidades, o único modo efetivo de caracterização do universo observado. A Dicionarização deve procurar trazer ao conhecimento público toda e qualquer informação que seja de valia para o processo de compreensão e unificação de conceitos.

3.4 Relacionamentos 3.4.1 Identificação dos Relacionamentos Através do mapeamento dos relacionamentos estaremos sendo capazes de demonstrar como um objeto se comporta em relação aos demais, qual seu grau de dependência de outros objetos, qual a associação de dados existentes entre eles, entre outros fatores. Devemos estar atentos para que o mapeamento desses relacionamentos possa agregar todas as características semânticas existentes em um certo tipo de associação. Se um Relacionamento mapeado espelha a realidade, ele poderá envolver quaisquer tipos ou instâncias de objetos. Dois tipos básicos de Relacionamentos: 1- Relacionamentos entre instâncias de objetos de diferentes tipos – associa instâncias de um objeto de um tipo a outras de outro tipo. 2- Relacionamentos entre instâncias de um mesmo tipo de objeto – esse tipo de relacionamento caracteriza um caso especial onde no papel de objetos formadores, ou participantes, do relacionamento encontraremos o mesmo tipo de objeto.

3.4.2 Denominação dos Relacionamentos No processo de reconhecimento e identificação dos relacionamentos existentes entre os objetos devemos buscar uma denominação que represente o conceito observado. Essa denominação será, do ponto de vista semântico e conceitual, extremamente importante para a correta construção do modelo.

3.4.3 Caracterização dos Relacionamentos Um relacionamento demonstra uma associação entre objetos, de igual ou diferentes tipos. Esses Relacionamentos têm, todavia, características diversas que devem ser observadas e mapeadas. A Caracterização dos Relacionamentos deverá ser feita baseada no atendimento de alguns requisitos: 1- Grau, ou Cardinalidade, do Relacionamento; 2- Número de elementos que participam do relacionamento; 3- Condição de participação dos elementos no relacionamento; 4- Condição de estabelecimento do relacionamento.

3.4.4 Grau de Cardinalidade do Relacionamento Durante o processo de reconhecimento e entendimento de um relacionamento, estaremos sempre nos defrontando com regras de estabelecimento das associações entre os elementos, extraídas do próprio



13

ambiente observado. Em muitos casos, uma regra que seja verdadeira em um ambiente X observado poderá não ser verdadeira em outro ambiente Y. Processo para a Definição e Notação de um Relacionamento Junto ao elemento que representa o relacionamento, estaremos representando o grau de associação, ou cardinalidade. Possibilidades de Estabelecimento de Graus de relacionamento Deveremos enquadrar os relacionamentos analisados em três grandes grupos, os quais, para fins de modelagem, cumprem a total finalidade de expressar a semântica de associação entre os elementos: - 1:1 (Um para Um) – Um elemento do tipo A só se relaciona com um elemento do tipo B, e viveversa; - 1:N (Um para Muitos) – Apesar de um elemento do conjunto A poder se associar a N elementos do conjunto B, o contrário não é verdadeiro, cada elemento do conjunto B só poderá estar associado a um elemento do conjunto A; - M:N (Muitos para Muitos) – Um elemento qualquer de um conjunto A pode se associar a nenhum, 1 ou vários elementos do conjunto B, e vice-versa. Mudanças na Notação de Relacionamentos M:N Algumas notações acabam por induzir o modelador para que, se o relacionamento for de grau 1:1 ou grau 1:N, seja utilizada uma representação e se o grau do relacionamento for M:N seja utilizada outra. Isso deve-se ao fato de essas notações estarem bastante orientadas aos modelos lógicos e não aos modelos conceituais. Número de Elementos que participam do Relacionamento Um Relacionamento pode se estabelecer entre dois ou mais elementos, e não somente, no máximo, dois elementos. Em Relacionamentos Ternários, Quaternários ou de maiores graus podem estar mascarando um objeto não percebido, o qual concentra sobre si relacionamentos com os demais. A estrutura necessária para a derivação de um relacionamento Ternário é a Agregação. Presença dos Elementos no relacionamento Outro tipo de conceito que deve ser manipulado quando se tratam os relacionamentos entre os objetos é a obrigatoriedade ou não da participação dos elementos nas associações passíveis de serem estabelecidas. Alguns elementos de um dado conjunto A podem não participar em nenhuma associação com elementos de um conjunto B. Do mesmo modo, qualquer um dos elementos do conjunto B pode não estabelecer associação com algum elemento do conjunto A.

3.4.5 Relacionamentos incondicionais Nos Relacionamentos Incondicionais não será denotado nenhum tipo de opcionalidade quanto à participação de elementos nas associações. Nesse tipo de representação precisaremos de informações adicionais para reconhecer se o relacionamento sempre segue o grau descrito (1:1, 1:N, M:N), ou se, por omissão, existem regras de exceção não qualificadas.

3.4.6 Relacionamentos Condicionais



14

Nesse tipo de representação, estaremos, graficamente, demonstrando quando um ou outro elemento permite a ausência de associações. Deveremos expandir o conceito de grau do relacionamento e pensar em dois tipos de grau: Mínimo e Máximo. Grau Máximo é a determinação de relacionamentos como associações 1:1, 1:N e M:N. Ou seja, qual é o menor valor possível de participação dos elementos do conjunto A e B no relacionamento.

3.4.7 Quanto à Existência Simultânea de Relacionamentos Essa característica leva-nos a uma situação na qual será preciso definir quando a existência de uma associação impacta ou impede a existência de outra. Podemos caracterizar três tipos de associações entre objetos: - Relacionamentos Independentes – Agrupa todo e qualquer relacionamento que possa vir a ser estabelecido sem que haja necessidade de avaliação simultânea de outro relacionamento; - Relacionamentos Contingentes – Aqueles que, tendo dependência uns com os outros, impõem o estabelecimento simultâneo de associações entre os vários elementos envolvidos. - Relacionamentos Mutuamente Exclusivos – Se a associação de um elemento for estabelecida através de um dos relacionamentos, não poderá ser estabelecida pelos demais.



15

3.4.8 Quanto à Presença de Atributos Durante o processo de modelagem, iremos nos defrontar com situações nas quais deveremos manipular informações (dados) que pertençam à associação dos objetos do ambiente observado. Isso fará com que seja necessário expandir nosso entendimento sobre o mapeamento de atributos e de relacionamentos. Classificação dos Relacionamentos: - Com Atributos – tem um nome, um significado, um grau mínimo e máximo, pode ser independente, contingente, mutuamente exclusivo, e adicionalmente, possui dados a serem manuseados. - Sem Atributos – se estabelece entre elementos de dois, ou mais, conjuntos e para o qual não são identificadas informações a serem mantidas ou mapeadas. Mudanças na Representação em Função de Relacionamentos com Atributos Outro motivo que tem levado os modeladores a já no nível conceitual terem de criar estruturas do tipo ‘Entidade’ para espelhar relacionamentos é o fato de o relacionamento conter atributos a serem mapeados.

3.4.9 Dicionarização de Relacionamentos Para que realmente seja entendido o papel de um relacionamento, muitas vezes será necessário agregar algum tipo de informação adicional sobre o relacionamento estabelecido entre as entidades. O conhecimento da cardinalidade, utilizando-se graus mínimos e máximos, e a utilização de nomes significativos podem ajudar mas, freqüentemente, não são suficientes para o completo entendimento dos modelos. Elementos de Caracterização Semântica Adicionais Qualquer modelo poderia ser construído com um mínimo suficiente de semântica baseado nos conceitos de entidades, atributos e relacionamentos. Com o passar do tempo, novas características semânticas passaram a demonstrar ser necessárias para o completo entendimento dos modelos.

3.4.10 Estrutura de Generalização e Especificação O principal objetivo do processo de abstração e identificação dos objetos é reconhecer agrupamentos e distinções entre conjuntos de objetos identificados. Entretanto, nem sempre a separação de instâncias desses objetos, ou elementos, se dará por conjuntos estritamente distintos.

3.4.11 Especializações Mutuamente Exclusivas (Categorias) A partir da observação e identificação dos elementos e subconjuntos existentes em um conjunto, sempre que um elemento do conjunto global, pertencer a um dos subconjuntos, não poderá pertencer simultaneamente, a outro.

3.4.12 Especialização Não-mutuamente Exclusivas (Papéis) Caracterizar, especificamente, se um elemento pertence a uma especificação com ou sem exclusividade deverá ser o objetivo do processo de investigação das regras estabelecidas pelo ambiente.



16

3.4.13 Níveis de Especialização Uma característica, que podemos encontrar, quanto à formação de subconjuntos especializados, é a da existência de diferentes níveis de agrupamento. Partindo de um conjunto global, poderemos estabelecer um primeiro nível de subconjuntos e, dentro desses, voltar a estabelecer novos subconjuntos.

3.4.14 Quando Utilizar a Estrutura de Generalização-Especialização Como regras práticas para definir se uma especialização deve ou não ser explicitada no modelo, devemos avaliar os seguintes passos: - Regra 1: Existe algum atributo que seja aplicável a somente um subconjunto de elementos e não a todos? Se sim, então é desejável a criação da especialização. - Regra 2: Existe algum relacionamento que seja aplicável a somente um subconjuntos de elementos e não a todos? Se sim, então é desejável a criação da especialização. - Regra 3: Não estaremos ‘poluindo’ o modelo com a inserção de detalhes desnecessários? Não, não estaremos. Então é possível a criação da especialização. - Regra 4: A finalidade de nosso modelo é procurar incorporar o máximo de elementos gráficos para que a compreensão não dependa de análise de elementos complementares, tais como dicionário de dados, documentos, anexos? Sim, é. Então, é possível a criação da especialização. Essas regras nos levam a ter duas situações distintas: 1- A especialização deverá ser explicitada; 2- A especialização pode ser explicitada.

3.4.15 Como Apresentar os Subgrupos Existentes Uma habilidade está disponível para ser utilizado junto á estrutura de Generalização-Especialização: a definição dos critérios. A partir desse critério teremos, então , a formação dos subgrupos pela divisão dos elementos pertencentes ao conjunto global. Utilizando a Estrutura de Gen-Espec em Auto-Relacionamentos A utilização da estrutura de Generalização-Especialização pode ser um recurso bastante interessante para acrescentar semântica ao casos de auto-relacionamento. Um auto-relacionamento se dá quando diferentes instâncias de um mesmo tipo de objeto se relacionam entre si.



4

17

Modelo Lógico de Dados (MLD)

4.1 Passos para a Derivação do Modelo Lógico Vimos que a geração de um modelo lógico deveria ser antecedida pela obtenção de um modelo conceitual. A grande vantagem dessa proposta, é que a partir do modelo conceitual gerado, podemos aplicar regras predefinidas em função da tecnologia a ser empregada e , assim, obter os modelos necessários. Podemos definir que o processo de obtenção de um modelo lógico a partir de um modelo conceitual segue os seguintes passos: 1- Obter o modelo conceitual; 2- Definir o tipo de implementação; 3- Aplicar as regras de derivação específicas; 4- Adaptar o modelo às necessidades. Adaptações no Modelo Lógico Derivado Visa a criar estruturas de implementação que atendam às reais expectativas das aplicações. Conceitos Utilizados na Derivação de Modelos Relacionais A visão básica estabelecida era de que registros de um arquivo assemelhavam-se a tuplas de uma relação. Uma relação, por sua vez, assemelhava-se a uma arquivo onde cada uma de suas tuplas, contendo elementos de domínios distintos, agregados de forma predefinida, podia ser entendida com um registro e seus campos.

4.2 Implementação do Modelo Relacional Conceitos 1- Chave Candidata e Chave Primária – A Não-existência de elementos repetidos em uma tabela é uma necessidade básica a ser cumprida para o estabelecimento de um modelo relacional realmente aderente aos conceitos de ‘relação’, foi introduzido o conceito de Chave Identificadora da tabela. Identificar domínios nos quais sabemos que a existência de valores instanciados não será repetida, podemos eleger esses domínios, ou colunas, como Candidatas a Chave. Basta que exista mais de um domínio que apresente a característica de não repetir seus valores durante as instanciações para todas as linhas da tabela. Dentre essas várias colunas deveremos escolher uma e elegela como Chave Identificadora ou Chave Primária. 2- Chave Estrangeira – Uma Chave Estrangeira recebe esse nome para denotar algumas características importantes: - Ela é uma chave, portanto, identifica de modo único uma tupla; - Ela não está em seu local original, mas sim em um local para onde foi migrada; - Ela tem um local de origem e lá possui suas características originais (é uma chave primária). Uma Chave Estrangeira é uma chave primária de uma tabela que aparece repetida em outra. 3- Domínios de Atributos – Existe um conjunto de valores distintos que podem ser atribuídos a uma coluna. Entretanto, sabemos que pela diferente natureza de cada uma das colunas poderemos ter diferentes valores sendo válidos às instanciações.



18

4.2.1 Regras de Derivação O processo de obtenção de um modelo relacional é baseado na execução de três atividades distintas: - Normalização das Estruturas de Dados; - A Derivação de Estruturas de Agregação e Generalização-Especialização; - A Derivação de Relacionamentos.

4.3 Normalização Quando os pontos passam a estar claros, aí então passamos a efetivamente encarar o processo de normalização como uma alavanca para o processo de construção de banco de dados. A Normalização não é um processo com finalidade restritiva mas sim com caráter organizativo. A Normalização pode ocorrer em três instantes distintivos: 1- Durante o processo de concepção do modelo conceitual; 2- Durante a derivação do modelo lógico; 3- Após a derivação do modelo lógico. Normalização durante o processo de concepção do Modelo Conceitual O grau de normalização que ele pode apresentar dependerá de vários fatores como, por exemplo: - Abrangência do Modelo – Quanto mais abrangente for um modelo, mais chances, caso ele tenha sido adequadamente concebido, haverá de que ele tenha um baixo grau de normalização. - Facilidade de abstração e compreensão – O correto entendimento dos elementos presentes em um ambiente a ser modelado poderá nos levar mais facilmente à concepção de um modelo normalizado. - Conhecimento prévio do ambiente – O fato de que já temos conhecimento prévio do ambiente a ser modelado, e assim do próprio resultado a ser obtido, tanto no nível conceitual como no nível lógico, nos introduzirá a previamente enxergar o ambiente de modo normalizado. Normalização durante a derivação do Modelo Lógico Caso tenha concebido seu modelo conceitual e agora pretenda transforma-lo em um ambiente lógico para implementação em ambiente relacional, você deverá ter certeza de que ele respeita os preceitos da tecnologia relacional. Normalização após o Processo de Derivação do Modelo Lógico Caso você execute o processo de normalização em um dos outros dois instantes, acabará por criar tabelas cuja estrutura não está normalizada, o que resultará em deficiências e anomalias de projeto que, mais cedo ou mais tarde, terão de sofrer os ajustes necessários.

4.3.1 Benefícios da Normalização 1- Estabilidade do Modelo Lógico – Por estabilidade entendemos a capacidade de um modelo manterse inalterado face a mudanças que venham a ser percebidas ou introduzidas no ambiente que tenha sido modelado. 2- Flexibilidade – Tanto de processos como de utilização de estruturas de dados. Capacidade de adaptação a demandas diferenciadas, a expansão e redução, a omissão ou presença, etc. 3- Integridade – As estruturas de dados obtidas pelo processo de modelagem necessitam de recursos para que os dados a serem nelas armazenados possam ter ‘qualidade’. 4- Economia – O aspecto economia, quando se aborda o tema normalização, tem sido visto, basicamente, sob o prisma da economia de espaço para armazenamento. Prof. Flavio Rezende


19

5- Fidelidade ao Ambiente Observado – Representação de um ambiente observado, através de um modelo, deve procurar retratar, independentemente do grau de abstração, as características observadas, assim como elas efetivamente são.

4.3.2 Primeira Forma Normal (1FN) A definição para a primeira forma normal, assim como para as demais, é bastante formal e rigorosa. Não poderia e nem deveria ser diferente. Como esses processos agem sobre estruturas relacionais e foram definidas por pessoas ligadas ao ambiente matemático, herdaram predominantemente o formalismo presente nessas áreas. Isso é bom, por certo lado, pois torna inequívoca sua interpretação. Entretanto por outro lado, temos percebido que boa parte da dificuldade de entendimento, no que diz respeito a formas normais, advém, justamente de dificuldade em “traduzir o formalismo para termos do dia-a-dia”. Definição da Primeira Forma Normal Diz-se que um modelo está na primeira forma normal se: Está integrado por tabela; As linhas da tabela são unívocas; As linhas não contêm itens repetitivos; Os atributos são atômicos; Os atributos não contêm valores nulos.

“... As linhas da tabela são unívocas” Também quando mostramos os conceitos envolvidos no ambiente relacional, falamos sobre a obtenção de identificadores únicos para uma tabela visando assegurar a diferenciação entre duas linhas de uma tabela. “... As linhas não contêm itens repetitivos” Grupos repetitivos são estruturas de dados artificiais (vetores) criadas para facilitar o manuseio de ocorrências repetitivas de elementos de dados. “... Os atributos são atômicos” Estruturas de agrupamento de itens de dados tornaram-se bastante comuns junto à linguagem tais como o COBOL.

“... Os atributos não contêm valores nulos” A definição da 1FN impondo a não existência de valores nulos era historicamente, uma restrição bastante clara. Seguindo-se definição formal de uma relação não d everíamos, realmente, ter um domínio sem instanciação de valor.



20

Do ponto de vista prático atual, não há mais restrição quanto à existência de valores nulos em colunas de uma tabela para que ela esteja na primeira forma normal (1FN).

Processo para obtenção da Primeira Forma Normal (1FN) Definir chaves candidatas e escolher a chave primária da tabela Esse passo que em um primeiro instante parece trivial, pode nos levar a uma série de considerações de ordem prática. Em princípio, a escolha da chave da tabela deve ser feita observando-se os atributos existentes na tabela e escolhendo-se um ou mais deles para que concatenados possam servir como meio de diferenciação entre luma linha e outra (função básica da chave primária). Transformar atributos compostos em atômicos Esse passo é bastante simples e implica poucas decisões. Atributos compostos, que claramente tenham elementos distintos em sua estrutura, deverão ser separadas.

Em cada tabela eliminar grupos repetitivos gerando novas tabelas, uma para cada ocorrência de Item Repetitivo, mantendo os valores dos demais itens A execução dessa atividade, isoladamente, poderá nos parecer bastante estranha. Ao olharmos a Estrutura tabular que temos antes e depois dessa atividade, seremos levados a acreditar que tínhamos antes uma estrutura bem melhor do que depois de sua execução.

4.3.3 Segunda Forma Normal (2FN) O intuito da segunda forma normal (2FN) é avançar, ainda mais na direção de uma estrutura de dados que não contenha anomalias. Poderão existir casos onde somente aplicando-se as regras da primeira forma normal já venhamos a obter uma estrutura acabada. O que isso significa? Que na verdade, nossa tabela já está normalizada, naturalmente, nas segunda e terceira formas normais. Para constatar isso, teremos de tentar aplicar regras da segunda e terceira formas normais da tabela. Se verificarmos que não existem as características indicadas pela segunda e terceira forma normais da tabela, então teremos comprovado sua normalização natural. Muitas vezes bastará que tenhamos aplicado as regras da segunda forma normal sobre uma tabela para que ela também já esteja normalizada na terceira forma normal. Processo para obtenção da Segunda Forma Normal (2FN) 1. Definir as colunas que não participaram da chave primária da tabela; 2. Para cada uma das colunas identificadas, analisar se seu valor é determinado por parte ou pela totalidade da chave; 3. Para as colunas dependentes parcialmente da chave -

Criar novas tabelas onde a chave primária será a coluna da chave original que determinou o valor da coluna; Prof. Flavio Rezende

Apostila de Banco de Dados v.4.0 -

21

Excluir da tabela original as colunas dependentes parcialmente da chave.

4.3.4 Terceira Forma Normal (3FN) Visando a transformar a estrutura de dados obtidas até a segunda forma normal em uma estrutura isenta das anomalias constatadas, deveremos buscar a terceira forma normal.

Definição da Terceira Forma Normal Uma tabela está na terceira forma normal se está na 2FN, e se nenhuma coluna não pertencente à chave fica determinada transitivamente por esta.

Processo para obtenção da Terceira Forma Normal (3FN) 1. Identificar as colunas que não participam da chave primária da tabela; 2. Para cada uma das colunas identificadas, analisar se seu valor é determinado por alguma outra coluna não pertencente à chave; 3. Para as colunas dependentes transitivamente da chave: -

Criar novas tabelas onde a chave primária será a coluna que determinou o valor da coluna analisada. Agregar a essas tabelas as colunas dependentes transitivamente. Excluir da tabela de origem as colunas dependentes transitivamente das chaves mantendo, porém, a coluna determinante da transitividade na tabela.

Derivação de Relacionamentos O tratamento dos relacionamentos, estruturas de Generalização-Especialização e agregação também deverá ser feito durante o processo de derivação. Relacionamento 1:1 sem Atributos O caso mais elementar de relacionamento entre duas entidades é o de cardinalidade 1:1. Dentro desse tipo de relacionamento, por sua vez, o caso mais freqüente de ser encontrado é o de relacionamento 1:1 sem atributos. Relacionamento 1:1 com Atributos



22

A existência de relacionamentos 1:1 com atributos não é algo muito comum. Poderíamos até dizer que é, realmente, um rigor extremo do projeto conceitual. Entretanto, como no nível conceitual eles poderão existir, apresentando 3 métodos para deriva-los.

Relacionamentos M:N com ou sem Atributos Para a derivação dos relacionamentos M:N, independentemente da existência ou não de atributos nesse relacionamento, deveremos aplicar uma mesma estratégia. Essa estratégia será baseada no fato de que se um relacionamento M:N representa a interseção dos elementos de um conjunto A com um conjunto B, então deveremos representar fisicamente os possíveis pares formados pela agregação dos elementos de A e B. Derivação de Estruturas de Generalização-Especificação O processo de derivação de uma estrutura de Generalização-Especificação não nos levará diretamente a um modelo lógico para a implementação. Ele na verdade, nos fornecerá um novo modelo conceitual simplificado onde a estrutura, antes existente, será substituída, simplesmente, por entidades e relacionamentos comuns. Estaremos substituindo a notação especial utilizada para representar essa estrutura por elementos comuns (entidade e relacionamentos) e, após isso, aplicando sobre eles as mesmas regras de derivação já vistas. A partir desse ponto teremos obtido os elementos lógicos resultantes da derivação. Durante o processo de derivação, poderemos adotar quatro diferentes estratégias para tratar essas estruturas: 1. Criar uma tabela para a entidade generalizada e uma tabela para cada entidade especializada; 2. Criar somente uma tabela para a entidade generalizada e migrar todos os atributos e relacionamentos especializados para esta tabela; 3. Criar somente tabelas para as entidades especializadas e migrar todos os atributos e relacionamentos generalizados para cada uma dessas tabelas; 4. Utilizar as estratégias 3 e 4 de maneira combinada, migrando atributos e relacionamentos das entidades especializadas para entidade generalizada e/ou vice-versa.

4.3.5 Derivação de Agregações A agregação foi um elemento introduzindo no modelo conceitual para representar de modo mais claro uma relação não-binária. O resultado obtido foi um elemento com características de entidade e de relacionamento simultaneamente. Para deriva-lo iremos tratar primeiramente seu aspecto ligado ao relacionamento e, depois, o aspecto ligado à entidade.

4.4 Restrições no Modelo Lógico O assunto “restrição” será tratado sob três aspectos bastante distintos. São eles:



-

23

Restrições de Domínio; Restrições de Integridade; Restrições de Implementações;

4.4.1 Restrições de Domínio A preocupação com restrições de domínios, para colunas de uma tabela, é um trabalho final executado durante a fase de definição dessas tabelas através da linguagem DDL de um SGBD específico. Dentre as restrições de domínio que devemos tratar estão: -

O tipo de dado da coluna; O tipo de representação interna escolhida para o dado; As características conceituais de presença ou não desse dado; A especificação explícita de intervalos para domínios contínuos; A especificação explícita de um conjunto de valores par domínios discretos; A definição de alguns tipos especiais de dados.

4.4.2 Restrições de Integridade O aspecto relativo à restrições de integridade não afetará diretamente a atribuição de valores isoladamente em uma linha de uma coluna. Normalmente estará envolvendo diferentes linhas da mesma tabela ou de tabelas diferentes. Em alguns casos terá a função de estabelecer (implementar) no modelo lógico algumas das restrições tecnológicas do modelo relacional e outras vezes, simplesmente, terá a função de manter a integridade (fidelidade,correteza, coesão, etc...) entre os dados armazenados no banco de dados.

Dentre as restrições de integridade que devemos tratar estão: -

A definição de unicidade de valores em certas colunas das tabelas A restrição de referência de uma chave estrangeira a um valor de chave primária inexistente; A restrição de referência de uma chave estrangeira a uma coluna ou composição de colunas, de uma chave primária inexistente; A referência de uma chave estrangeira a uma tabela inexistente; A exclusão de ocorrências (tuplas) em modo cascata em estruturas tipo “ hierarquia” ; A alteração de ocorrências (tuplas) em modo cascata em estruturas do tipo “ hierarquia” ; A manutenção de sincronismo entre tabela e os índices da tabela.



24

4.4.3 Restrições de Implementação O tratamento das restrições de implementação deverá ocorrer já durante a fase de derivação de modelo lógico. As decisões sobre quais estratégias utilizar durante o processo de derivação de relacionamentos e das estruturas de agregação e generalização-especialização dependerão das restrições de implementação impostas pelo SGBD que se esteja utilizando. Dentre as restrições de implementação que possivelmente serão encontradas estão: -

A existência de itens de repetição; Implementação direta ou manuseio transparente de estruturas de generalização-especialização.

4.4.4 Objetos básicos de um SGBDR     

Tabela. Índice. View. Stored Procedure. Trigger.

Tabela ou Relação: Uma representação lógica da organização dos dados dentro do SGBDR. É uma tabela de valores, o seu nome e o nome das colunas, servem para auxiliar a interpretação do significado de seus valores. Valor : É usado para representar 'alguma coisa'. É a menor partícula de dados do modelo relacional, eles são indivisíveis. Domínio : É um conjunto de valores atômicos, de mesmo tipo. São usados para preencher os valores de uma coluna. ex: Nome char(40) : restringe a coluna Nome a ter seus valores dentro do domínio de no máximo 40 caracteres ou seja um nome de 50 caracteres não é um nome válido para este domínio. Linha ou Tupla : Representa uma linha da tabela, onde cada linha, representa uma coleção de valores.

Coluna ,atributo

MATRICULA 01 02 03 04 ...


SALARIO 200,00 1000,00 800,00 150,00

Linha , Tupla Valor

Conceitos de Chave: Primary Key (PK) :Atributo ou conjunto de atributos de uma tabela que identificam univocamente uma  linha. Toda Primary key deve ser minimal.


Apostila de Banco de Dados v.4.0  

25

Foreign Key (FK) : Atributo ou conjunto de atributos de uma tabela que referencia a chave primária em outra tabela, ou no caso do auto relacionamento, na mesma tabela. Unique Key(UK) : Atributo de uma tabela que tem todas as qualificações para ser uma chave primária.

OBS. Muitos autores representa a chave primária de uma tabela com as colunas sublinhadas, e as chaves estrangeiras com uma linha acima das colunas. Ex: FUNCIONARIOS(MATRICULA , NOME , SALARIO , CPF , DEPTO)

Ex:

PK

FUNCIONARIOS MATRICULA NOME 01 Zé 02 Maria 03 João 04 Margarida ...

Chave Candidata

SALARIO 200,00 1000,00 800,00 150,00

CPF 12999666 18765789 49555969 89993393

DEPTO 01 02 02 01

FK para DEPARTAMENTOS, ou seja, todos os valores desta coluna tem que existir na tabela de DEPARTAMENTOS na coluna da PK.

DEPARTAMENTOS DEPTO DESCRICAO 01 Recursos Humanos 02 Diretoria PK

Índices : É um conjunto de colunas da tabela, que são ordenadas visando uma maior performance nas consultas. OBS: O Banco de Dados Relacional permite a recuperação de um valor de qualquer coluna, independente da existência de índices para a coluna.

View : Restringe o acesso do usuário aos dados. Ex: Pode-se restringir o acesso do usuário a coluna de SALARIO. FUNCIONATIOS_V01 MATRICULA NOME 01 Zé 02 Maria 03 João 04 Margarida ...

CPF 12999666 18765789 49555969 89993393

DEPTO 01 02 02 01



26

Stored Procedure : Um programa escrito em SQL que pode ser executado por uma aplicação, a diferença é que o processamento será executado no servidor. Trigger: Um programa escrito em SQL que está obrigatoriamente associado a pelo menos um evento(insersão, alteração ou exclusão de linhas) em uma tabela, e será executado de acordo com este evento. O trigger é usado para implementar restrições de integridade.



5

27

Modelo de Entidades e Relacionamentos (ER).

5.1 Entidade Representa um objeto ou um conceito do mundo real. Ex: Aluno , disciplina , curso , funcionário e etc... Representação gráfica:

Aluno

Atributo – Representa uma característica da entidade, uma informação sobre a entidade. Ex: No caso de uma entidade ‘aluno’, podemos citar como atributo a sua matrícula , nome , endereço e outros. Tipos de Atributo: Atributos Identificadores : Deve ser capaz de identificar univocamente uma instância da entidade.  Ex: Para a Faculdade, a matrícula do aluno é capaz de identificá-lo. Representação gráfica:



Atributos Simples : Apenas uma característica ou informação da entidade. Ex: O nome de um aluno é uma informação útil. Representação gráfica:

5.2 Relacionamento Representa um relacionamento entre duas entidades. Ex: O histórico escolar relaciona as disciplinas cursadas com o aluno. Representação gráfica:

histórico

Aluno

histórico Prof. Flavio Rezende

Disciplina


28

5.3 Cardinalidade Representa quantas vezes uma entidade pode se relacionar com a outra. OBS. Podemos indicar numericamente o mínimo e o máximo de vezes que uma entidade pode se relacionar com a outra..

Aluno

N

histórico

N

Disciplina

5.4 Generalização / Especialização : É quando temos duas ou mais entidades com alguns atributos em comum e outros não. Na especialização os filhos devem ter o mesmo identificador do pai. Matrícula Nome Endereço

TempoMagistério

NívelDiretoria

A especialização pode ser ({ Total|Parcial},{Exclusiva,Superposição}) Total : Quando todos os elementos da entidade pai, deve obrigatoriamente existir em pelo menos uma entidade filha. Pessoa (T,E)

Homem

Mulher



29

Parcial : Quando pode existir elementos na entidade pai e não existir nas entidades filhas.

Funcionário (P,S)

Professor

Diretor

Exclusiva : Quando um elemento de uma especialização não pode existir em outra entidade do mesmo nível.

Pessoa (T,E)

Homem

Mulher

Sobreposição : Quando um elemento de uma especialização pode existir em outra entidade do mesmo nível. Funcionário (P,S)

Professor

Diretor

5.5 Entidade Fraca O conceito de entidade fraca é quando uma entidade precisa do identificador de outra entidade para ajudar a se identificar. Ex:

Prédio

Endereco

N Apartamento


Endereco NumeroApartamento


30

Importante : Uma entidade fraca TEM que se relacionar com a entidade forte no mínimo uma vez e no máximo uma vez

5.6 Agregação : É quando há a necessidade de ligar um relacionamento a outro relacionamento. Ex: matrícula Disciplina

Aluno

ou Nota matrícula Disciplina

Aluno

Nota



6

31

Regras de conversão do Modelo Conceitual (E-R) para o Modelo Lógico.

Nesta fase devemos identificar como tipo de colunas apenas os tipos genéricos, deixando para se preocupar com os tipos mais específico de dados no projeto físico, a não ser que o SGB D já tenha sido escolhido. Ex: char(n),Int, Datetime , etc...

6.1 Entidade Toda entidade vira tabela e seu(s) atributo(s) identificadores formará a chave primária (PK). Matrícula Nome Endereco

Aluno Aluno(Matricula, Nome, Endereco) Aluno Matricula

Nome

Eendereco

6.2 Entidade Fraca A entidade fraca vira tabela e carrega o atributo identificador da entidade forte. A chave primária(PK) é formada concatenando-se o atributo identificador da entidade forte com o seu atributo identificador.

Prédio

Endereco

N Apartamento

NumeroApartamento QtdQuartos



32

Apartamento(Endereco, NumeroApartamento, QtdQuartos) Apartamento Endereco NumeroApartamento

QtdQuartos

6.3 Relacionamentos Cardinalidade 1:N : A tabela cujo a conectividade é N carregará o identificador da entidade onde a conectividade é 1 e formará uma chave estrangeira(FK). Obs. Caso o relacionamento tenha um mínimo 1, basta definir a FK não permitindo valores nulos.

Cardinalidade 1:1 : A tabela que conter o menor número de linhas carregará o identificador da entidade onde conter o maior número de linhas e formará uma chave estrangeira(FK), nesta chave estrangeira deve ser criado um índice único(será visto mais tarde em Projeto Físico ). Cardinalidade N:N : O relacionamento transforma-se em tabela, carregando os identificadores das entidades que ele relaciona. Esses identificadores concatenados, formarão a chave primária(PK), e cada um dos identificadores separadamente serão chaves estrangeiras(FKs) para suas entidades/tabelas de origem. Obs. Caso o relacionamento seja com repetição, ele terá obrigatoriamente atributo identificador, neste caso, este atributo também fará parte da PK.

6.4 Especialização Segue a regra para entidade, ou seja, vira tabela com as entidades filhas carregando a PK da entidade pai, sendo que a PK da entidade filha é uma FK para entidade pai. OBS: No caso da especialização é preciso analisar caso a caso.

A

B

C

O caso mais genérico é transformar todas as entidades, pais e filhas em tabelas. Obs. No caso da especialização Total e Exclusiva pode-se colocar um campo a mais na entidade pai para saber em qual das filhas ela se encontra. Porém a duas outras maneiras de se implementar:



33

No caso da especialização ser Total e Exclusiva e somente neste caso, poderemos transformar apenas as entidades filhas em tabelas. A ultima opção é transformar tudo em apenas uma tabela, porém o caso mais recomendado é o da especialização Parcial com Superposição.

6.5 Agregação No caso da Agregação, terá de um lado do relacionamento uma entidade, e do outro lado, outro relacionamento. Neste caso deveremos tratar o relacionamento da ponta, como se fosse uma entidade.

7

Linguagem SQL.

7.1 Introdução ao SQL A linguagem SQL é subdividida em várias sub-linguagens. Através do comando select é implementado todas as operações algébricas. Sintaxe básica para o comando select. select [distinct] campo1,campo2,campo3 from tabela1[, tabela2, tabela3...] [where condição] onde: distinct : Serve para eliminar as linhas redundantes condição : Qualquer condição de seleção para restringir o resultado final. Ex1. select NOME from ALUNOS where ID_CURSO = 2

seleção Este comando simples utiliza a operação algébrica da seleção, reparem que é feito uma seleção para o nome dos alunos apenas com o ID_CURSO = 2. O resultado deste comando será o conjunto de todos os alunos que estão matriculados no curso 2. Ex2. select ALUNOS.NOME , CURSO.NOME Produto cartesiano from ALUNOS , CURSOS where ALUNOS.ID_CURSO = CURSOS.ID_CURSO

Com o produto cartesiano de ALUNOS com CURSOS e a seleção da PK de CURSOS com a FK de ALUNOS temos a junção ou o join.

Seleção



34

Comandos SQL realizam tarefas como: pesquisar dados inserir, alterar e apagar linhas em uma tabela criar, modificar e apagar objetos do Banco de Dados controlar acesso para Banco de Dados e objetos do Banco de Dados garantir a consistência do banco de dados • • • • •

7.1.1 SQL*Plus SQL*Plus é uma interface na qual os comandos SQL podem ser entendidos e executados. Comandos SQL*Plus são feitos no prompt do SQL>, eles não entram no buffer. Comando

Descrição

SAVE arquivo

permite o corrente contexto do SQL buffer ser salvo em um arquivo

GET arquivo

chama texto previamente salvo

START arquivo

executa um comando previamente salvo em um arquivo. Comandos de arquivo são discutidos na Unidade 10

ED arquivo

usa editor padrão em ordem para edição do arquivo salvo.

SPOOL arquivo

escreve todos os comandos subsequentes de saída no arquivo nomeado. O arquivo SPOOL é estendido por .LIS (LST em algum sistemas)

SPO(OL) OFF/OUT

OFF fecha o arquivo SPOOL e OUT fecha o arquivo SPOOL e emite o arquivo para impressora.

DESC(RIBE) tabela

mostra a estrutura de uma tabela.

HELP

invoca o interno sistema de ajuda ORACLE

$O/S comando HOST comando

invoca um comando do sistema operacional. mesma função acima

CONN(ECT) usuário/senha

Invoca outro usuário ORACLE

EXIT

sai SQL*Plus



35

Comando

Descrição

PROMPT texto ; / EXEC

mostra o texto quando executa o comando arquivo. mostra o texto que está no buffer. Executa o texto que está no buffer. Executa um simples comando SQL como por exemplo uma stored procedure.



36

7.1.2 PL/SQL É uma linguagem de programação sofisticada que permite processar códigos diretamente no servidor, tornando o acesso rápido e eficiente. Procedimentos compatíveis: declaração de variáveis. tarefas (X := Y + Z) controles condicionais (IF, THEN, ELSE, ELSIF, GOTO) Loop (FOR, WHILE, EXIT, WHEN) manuseio de exceções (exception) • • • • •

7.1.3 Sintaxe de um bloco PL/SQL: DECLARE Procedure Procedure BEGIN Procedure SQL Procedure SQL END

Área de declaração de variáveis

Comandos

7.1.4 Alguns Comandos SQL: SELECT

Esse é o comando mais comum, usado para retirar dados de uma Tabela

INSERT UPDATE DELETE

São os comandos usados para o preenchimento de novas linhas, modificando linhas existentes e removendo linhas não desejadas das tabelas.

CREATE ALTER DROP

São usados dinamicamente para configurar, modificar e remover várias estruturas de dados como: tabelas, visões, índices.

GRANT REVOKE

Usados para dar ou remover privilégios e direitos de acesso de um usuário ou grupo de usuários ao Banco de Dados ORACLE e às estruturas dentro dele.

7.1.5 SELECT SELECT * (asterisco seleciona todos os campos) FROM EMP(nome da tabela)

Exemplo 1: SELECT SELECT EMPNO, ENAME,MGR FROM EMP;

Resultado da pesquisa: EMPNO ENAME 7369 SMITH

MGR 7902 Prof. Flavio Rezende


7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934

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

37

7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

Exemplo 2: SELECT * FROM EMP;

EMPNO 7369 7499 7521 7566 7654 7698 7782 7788 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 PRESIDEN SALESMAN CLERK CLERK ANALYST CLERK

MGR 7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

HIREDATE 17/12/80 20/02/81 22/02/81 02/04/81 28/09/81 01/05/81 09/06/81 19/04/87 17/11/81 08/09/81 23/05/87 03/12/81 03/12/81 23/01/82

MGR SAL 7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

COMM DEPTNO 20 300 30 500 30 20 1400 30 30 10 20 10 0 30 20 30 20 10

7.1.6 Expressões Aritméticas Expressões Aritméticas podem conter nome de colunas, valores numéricos, constantes e aritméticos: Operadores Descrições + Adição Subtração * Multiplicação / Divisão Exemplo: SELECT FROM

ENAME SMITH ALLEN WARD JONES MARTIN BLAKE CLARK

ENAME, SAL*12, COM EMP;

SAL*12 9600 19200 15000 35700 15000 34200 29400

COMM 300 500 1400


operadores


SCOTT KING TURNER ADAMS JAMES FORD MILLER

36000 60000 18000 13200 11400 36000 15600

38

0

A prioridade é *,/, então +,-. No exemplo seguinte a multiplicação (250*12) é avaliada primeiro; então o valor do salário é adicionado no resultado da multiplicação (3000). Exemplo: Select NOME, sal + 250 * 12 from emp;

ENAME SAL+250*12 SMITH 3800 ALLEN 4600 WARD 4250 JONES 5975 MARTIN 4250 BLAKE 5850 CLARK 5450 SCOTT 6000 KING 8000 TURNER 4500 ADAMS 4100 JAMES 3950 FORD 6000 MILLER 4300 Parênteses podem ser usados para especificar a ordem na qual serão executados es operadores, se, por exemplo, a adição e requerida antes da multiplicação. Exemplo: Select NOME, (sal + 250) * 12 from emp;


(sal + 250) * 12 12600 22200 18000 38700 18000 37200 32400 39000 63000 21000 16200 14400 39000 18600



39

7.1.7 Colunas Sinônimas Quando mostramos o resultado de uma pesquisa, o SQL*Plus normalmente usa-se o nome das colunas selecionadas como cabeçalho. Você pode modificar o cabeçalho de uma coluna usando sinônimos(alias). Exemplo: SELECT SAL*12 GANHO_ANUAL, COMM FROM EMP;

GANHO_ANUAL 9600 19200 15000 35700 15000 34200 29400 36000 60000 18000 13200 11400 36000 15600

COMM 300 500 1400

0

7.1.8 Operador de Concatenação O Operador de Concatenação (||) permite que as colunas sejam juntadas com outras colunas, expressões aritméticas ou valores constantes para criar uma expressão alfanumérica. Para combinar COD e NOME e obter o sinônimo EMPREGADO, observe o exemplo abaixo: Exemplo: SELECT FROM

EMPNO||ENAME EMPREGADO EMP;

EMPREGADO 7369SMITH 7499ALLEN 7521WARD 7566JONES 7654MARTIN 7698BLAKE 7782CLARK 7788SCOTT 7839KING 7844TURNER 7876ADAMS 7900JAMES 7902FORD 7934MILLER



40

7.1.9 Literais Literais são um ou mais caracteres, expressões ou números, incluídos na lista do SELECT. Um literal na lista do SELECT terá uma saída para cada linha retornada. Literais de livre formatos de textos podem ser incluídos no resultado da pesquisa e são tratados como uma coluna na lista do SELECT. Datas e caracteres alfanuméricos devem ser colocados entre aspas simples(‘); números não precisam de aspas simples. As declarações seguintes contém literais selecionados com concatenação e colunas sinônimas. Exemplo: SELECT EMPNO||'-'||ENAME EMPREGADO, 'WORKS IN DEPARTMENT', DEPTNO FROM EMP;

EMPREGADO 7369-SMITH 7499-ALLEN 7521-WARD 7566-JONES 7654-MARTIN 7698-BLAKE 7782-CLARK 7788-SCOTT 7839-KING 7844-TURNER 7876-ADAMS 7900-JAMES 7902-FORD 7934-MILLER

'WORKS IN DEPARTMENT' WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT

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

7.1.10 Manuseando Valores Nulos Um valor nulo é um valor indisponível e desconhecido. O valor nulo não é zero. Zero é um número. Se algum valor de uma coluna em uma expressão for nulo, o resultado será nulo. Exemplo: SELECT ENAME, SAL*12+COMM GANHO_ANUAL FROM EMP;


GANHO_ANUAL 19500 15500 16400

18000

Na ordem para realizar o resultado para todos os empregados, é necessário converter os valores nulos para numéricos. Nós usamos a função NVL para converter valores nulos para não nulos.



41

NVL conta com dois argumentos: 1- uma expressão 2- um valor que será retornado caso o valor do primeiro argumento for nulo. Note que você pode usar a função NVL para converter qualquer tipo de valor nulo. NVL(COLUNA_DATA_NULA,’30-OCT-74’) NVL(COLUNA_NUMÉRICA_NULA,21) NVL(COLUNA_ALFANUMÉRICA_NULA,’QUALQUER VALOR’)

7.1.11 Prevenindo a Seleção de Linhas Duplicadas – Cláusula Distinct Para eliminar valores duplicados no resultado, incluímos o DISTINCT Exemplo sem a clausula distinct:

Para eliminar os valores Duplicados:

SELECT DEPTNO FROM EMP;

SELECT DISTINCT DEPTNO FROM EMP;

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

DEPTNO 10 20 30

Várias colunas podem ser especificadas depois do DISTINCT Exemplo: SELECT DISTINCT DEPTNO, JOB FROM EMP;

DEPTNO JOB --------------10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN



42

7.1.12 A clausula ORDER BY A clausula ORDER BY pode ser usada para ordenar as linhas. Se usado, o ORDER BY deve ser a última clausula da declaração SELECT. Para ordenar pelo NOME, faça: Exemplo: SELECT ENAME, JOB, SAL*12, DEPTNO 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*12 13200 19200 34200 29400 36000 11400 35700 60000 15000 15600 36000 9600 18000 15000

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

Ordenação descendente Para inverter essa ordem, use o comando DESC(Decrescente) depois do nome das colunas da clausula ORDER BY. Exemplo: SELECT FROM ORDER BY

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

ENAME, JOB, HIREDATE DATA DESC EMP HIREDATE DESC;

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

DATA 23/05/87 19/04/87 23/01/82 03/12/81 03/12/81 17/11/81 28/09/81 08/09/81 09/06/81 01/05/81 02/04/81 22/02/81 Prof. Flavio Rezende


ALLEN SMITH

SALESMAN CLERK

43

20/02/81 17/12/80

Ordenação por várias colunas. É possível na clausula ORDER BY usar mais de uma coluna. O limite de colunas é o número de colunas da tabela. Se algumas ou todas são invertidas especifique DESC depois de alguma ou cada uma das colunas. A clausula ORDER BY é usada na pesquisa. O comando não altera a ordem dos dados que estão armazenados no Banco de Dados ORACLE.

7.1.13 A Clausula WHERE A clausula WHERE corresponde aos Operadores de Restrições da Álgebra Relacional. Sintaxe: SELECT coluna(s) FROM tabela(s) WHERE certa condição a ser encontrada

A clausula WHERE pode comparar valores em uma coluna, valores literais, expressões aritméticas ou funções. Operadores de comparação são usados na clausula WHERE e podem ser divididos em duas categorias: Lógicos e SQL. Operadores Lógicos Esses operadores testam as seguintes condições: = igual a > maior que >= maior e igual a < menor que <= menor e igual a Alfanuméricos e Datas na clausula WHERE devem estar entre aspas simples. Para listar os nomes, números, emprego e departamentos de todos os escriturários(CLERK): SELECT ENAME, EMPNO, JOB, DEPTNO FROM EMP WHERE JOB = 'CLERK';

ENAME SMITH ADAMS JAMES MILLER

EMPNO 7369 7876 7900 7934

JOB CLERK CLERK CLERK CLERK

DEPTNO 20 20 30 10

Para encontrar todos os nomes de departamentos com número maior que 20, faça: SELECT DEPTNO, DNAME FROM DEPT WHERE DEPTNO > 20;

DEPTNO 30 40

DNAME SALES OPERATIONS



44

Comparando uma coluna com outra coluna na mesma linha: Exemplo: SELECT ENAME, SAL, COMM FROM EMP WHERE COMM > SAL;

ENAME ---------MARTIN

SAL COMM --------- --------1250 1400

7.2 Operadores SQL Existem quatro operadores SQL que operam com todos os tipos de dados: BETWEEN ... AND ... IN(Lista) LIKE IS NULL

Entre dois valores (inclusive) Compara uma lista de valores Compara um parâmetro alfanumérico É um valor nulo

7.2.1 O Operador BETWEEN Testa um faixa de valores inclusive. Exemplo: 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

7.2.2 O Operador IN Testa os valores especificados em uma lista. Para encontrar empregados que tenham um dos três números de MAT, faça: SELECT EMPNO, ENAME, SAL, MGR FROM EMP WHERE MGR IN (7902,7566,7788)

EMPNO 7369 7788 7876 7902

ENAME SMITH SCOTT ADAMS FORD

SAL 800 3000 1100 3000

MGR 7902 7566 7788 7566

Podemos usar o comando select dentro da cláusula in.

7.2.3 O Operador LIKE Algumas vezes você precisa procurar valores que não conhece exatamente. Usando o operador LIKE é possível selecionar linhas combinando parâmetros alfanuméricos. Dois símbolos podem ser usados para construir uma linha de procura. Símbolo: % Várias seqüência de zero ou mais caracteres



_

45

um número desejado de caracteres

Para listar todos os empregados os quais o nome começa com a letra S, faça: SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%';

ENAME ---------SMITH SCOTT Eles podem ser usados para encontrar um determinado número de caracteres. Por exemplo para listar todos empregados que tenham exatamente quatro caracteres de tamanho do nome: SELECT ENAME FROM EMP WHERE ENAME LIKE '____';

ENAME ---------WARD KING FORD O % e o _ podem ser usados em várias combinações com literais alfanuméricos.

7.2.4 Operador IS NULL Para encontrar unicamente todos os empregados que não tenham gerente, você testará um valor nulo: SELECT ENAME, MGR FROM EMP WHERE MGR IS NULL;

ENAME ---------KING

MGR -----

7.3 Expressões Negativas Os operadores seguintes são testes de negação: <> NOT BETWEEN NOT IN NOT LIKE IS NOT NULL

diferente (todos sistemas operacionais) tudo que estiver fora da faixa tudo que não esteja na lista tudo que não contenha a linha de caracteres tudo que não for nulo

Para encontrar empregados que tenham o salário fora da faixa, faça: SELECT

ENAME, SAL



46

FROM EMP WHERE SAL NOT BETWEEN 1000 AND 2000;

ENAME SMITH JONES BLAKE CLARK SCOTT KING JAMES FORD

SAL 800 2975 2850 2450 3000 5000 950 3000

Para encontrar os empregados os quais o cargo não comece com a letra M, faça: SELECT ENAME, JOB FROM EMP WHERE JOB NOT LIKE 'M%';

ENAME SMITH ALLEN WARD MARTIN SCOTT KING TURNER ADAMS JAMES FORD MILLER

JOB CLERK SALESMAN SALESMAN SALESMAN ANALYST PRESIDEN SALESMAN CLERK CLERK ANALYST CLERK

Para encontrar todos os empregados que tenham um gerente, faça: SELECT ENAME, MGR FROM EMP WHERE MGR IS NOT NULL;

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

MGR 7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

Nota: Se um valor nulo é usado em uma comparação, então o operador de comparação deve ser IS ou IS NOT NULL. Se esses operadores não forem usados e valores nulos forem comparados, o resultado será sempre FALSO.



47

Por exemplo, COMM <> NULL será sempre FALSO. O resultado será falso porque um valor nulo não pode ser igual ou não igual a qualquer outro valor.

7.3.1 Pesquisando Dados com Múltiplas Condições Os operadores AND e OR devem ser usados para fazer composições de expressões lógicas. O predicado AND espera que ambas as condições sejam verdadeiras, enquanto que o predicado OR espera que uma das condições seja verdadeira. Nos seguintes exemplos as condições são as mesmas, o predicado é diferente. Veja como o resultado é dramaticamente alterado. Para encontrar todos os escriturários que ganhem entre 1000 e 2000, faça: SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000 AND JOB = 'CLERK';

COD ----- 7876 7934

ENAME --------ADAMS MILLER

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

SAL ---------1,100.00 1,300.00

Para encontrar todos os empregados que são escriturários ou todos que ganhem entre 1000 e 2000 faça: SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000 OR JOB = 'CLERK';

EMPNO 7369 7499 7521 7654 7844 7876 7900 7934

ENAME SMITH ALLEN WARD MARTIN TURNER ADAMS JAMES MILLER

JOB CLERK SALESMAN SALESMAN SALESMAN SALESMAN CLERK CLERK CLERK

SAL 800 1600 1250 1250 1500 1100 950 1300

Você pode combinar AND e OR na mesma expressão lógica. Quando AND e OR aparecerem na mesma clausula WHERE, o AND é realizado primeiro depois o OR. Se AND não interfere sobre o OR a seguinte declaração SQL retornará todos os gerentes com salário acima de 1500 e todos os vendedores: SELECT FROM WHERE

EMPNO, ENAME, JOB, SAL, DEPTNO EMP SAL > 1500


Apostila de Banco de Dados v.4.0 AND OR

JOB = 'MANAGER' JOB = 'SALESMAN';

EMPNO 7499 7521 7566 7654 7698 7782 7844

ENAME ALLEN WARD JONES MARTIN BLAKE CLARK TURNER

JOB SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER SALESMAN

48

SAL DEPTNO 1600 30 1250 30 2975 20 1250 30 2850 30 2450 10 1500 30

Todos os operadores são organizados em uma hierarquia. Numa Expressão, as operações são feitas na ordem de sua precedência, do maior para o menor. Onde os operadores de igual precedentes são usados próximo a outro, eles são feitos da esquerda para direita. 1- Todos os comparativos e Operadores SQL tem igual precedente: =, !=, <, >, <=, >=, BETWEEN ... AND ..., IN, LIKE, IS NULL. 2- NOT (para inverter o resultado das expressões lógicas: WHERE NOT (SAL>2000)) 3- AND 4- OR Sempre que você estiver em dúvida sobre qual dos dois operadores será feito primeiro quando a expressão é avaliada, use parênteses aumentar a legibilidade.

Suponha que você queira encontrar todos os gerentes, em vários departamentos, e todos os escriturários no departamento 10 unicamente: SELECT * FROM EMP WHERE JOB = 'MANAGER' OR (JOB = 'CLERK' AND DEPTNO = 10);

EMPNO 7566 7698 7782 7934

ENAME JONES BLAKE CLARK MILLER

JOB MANAGER MANAGER MANAGER CLERK

MGR 7839 7839 7839 7782

HIREDATE 02/04/81 01/05/81 09/06/81 23/01/82

SAL COMM 2975 2850 2450 1300

DEPTNO 20 30 10 10

O parênteses acima não é necessário, por que o AND é precedente ao OR, mas ele esclarece o significado da expressão.

7.4 Funções As Funções são usadas para manipular dados. Elas aceitam um ou mais argumentos e retornam um valor. Um argumento é uma constante, refere-se à variável, expressão ou coluna. Sintaxe: função_nome (agrumento1,argument2,...)

Funções podem ser usadas para: Cálculos sobre datas

•



• • •

49

modificar valores de itens individuais manipular saída para grupos de linhas alterar formatos de datas para mostrá-los

Existem diferentes tipos de funções: • • • • • •

ALFANUMÉRICAS NUMÉRICAS DATA CONVERSÃO FUNÇÕES QUE ACEITAM VÁRIOS TIPOS DE DADOS GRUPO

Algumas funções operam unicamente sobre linhas simples; outras manipulam grupos de linhas. Funções de Linha Única: agem sobre cada linha retornada na pesquisa retornam um resultado por linha esperam um ou mais argumento do usuário podem ser aninhadas podem ser usadas com variáveis do usuário, colunas, expressões; podem ser usadas, por exemplo, nas cláusulas SELECT, WHERE, ORDER BY. • • • • •

7.4.1 Funções Alfanuméricas Funções Alfanuméricas aceitam dados alfanuméricos e podem retornar valores alfanuméricos ou numéricos. A função seguinte influencia na construção de valores alfanuméricos.

7.4.1.1 LOWER LOWER(col/value)

fornece valores alfanuméricos os quais estão em letra maiúscula ou minúscula e retornam em letra minúscula

Para mostrar o nome dos departamentos em letra minúscula e a constante CURSO_SQL, faça: SELECT LOWER(DEPTNO), LOWER('CURSO_SQL') FROM DEPT;

LOWER(DEPTNO) 10 20 30 40

7.4.1.2 UPPER UPPER(col/value)

LOWER('CURSO_SQL') curso_sql curso_sql curso_sql curso_sql

fornece caracteres alfanuméricos, os quais estão em letra maiúscula ou minúscula e retorna em letra maiúscula.

No exemplo seguinte, a função UPPER força o usuário entrar em letra maiúscula. SELECT ENAME FROM EMP WHERE ENAME = UPPER('&NOME'); Enter value for NOME : smith



50

ENAME ---------SMITH

7.4.1.3 INITCAP INITCAP(col/value) força a primeira letra da palavras ser em maiúscula e o resto minúscula Exemplo: SELECT INITCAP(DNAME), INITCAP(LOC) FROM DEPT;

INITCAP(DNAME) Accounting Research Sales Operations

INITCAP(LOC) New York Dallas Chicago Boston

7.4.1.4 LPAD e RPAD LPAD(col/value,n,’caracter’) Preenche a coluna ou valor literal da esquerda para o total tamanho de n posições. Os principais espaços estão preenchidos com o ‘caracter’. Se o caracter for omitido o valor padrão é espaços. SELECT LPAD(DNAME,20,'*'), LPAD(DNAME,20), LPAD(DEPTNO,20,'.') FROM DEPT;

LPAD(DNSMR,20,'*') LPAD(DNAME,20 ) *************RESEACH ***************SALES **********OPERATIONS ***********ACCOUTING

RPAD(col/value,n,’caracter’)

RESEACH SALES OPERATIONS ACCOUNTING

LPAD(DEPTNO,20,'.') ..................20 ..................30 ..................40 ..................10

preenche a coluna ou valor literal da direita para o total tamanho de n posições. Os espaços a direita são preenchidos com o ‘caracter’. Se o ‘caracter’ for omitido o preenchimento fica em branco.

Exemplo: SELECT RPAD(DNAME,20,'*'), RPAD(DNAME,20), RPAD(DEPTNO,20,'.') FROM DEPT;

RPAD(DNAME,20,'*') ACCOUTING*********** RESEACH************* SALES*************** OPERATIONS**********

RPAD(DNAME,20) ACCOUNTING RESEACH SALES OPERATIONS

RPAD(DEPTNO,20,'.') 10.................. 20.................. 30.................. 40..................

A segunda coluna é alinhada para a direita com brancos por padrão.



7.4.1.5 SUBSTR SUBSTR(col/value,pos,n)

51

Retorna uma linha de n caracteres da coluna ou valor literal, iniciando na posição número pos. Se n é omitido a linha é extraída da posição pos até o fim.

Exemplo: SELECT SUBSTR('ORACLE',2,4), SUBSTR(DNAME,2), SUBSTR(DNAME,3,5) FROM DEPT;

SUBSTR('ORACLE',2,4) RACL RACL RACL RACL

SUBSTR(DNAME,2) CCOUNTING ESEARCH ALES PERATIONS

SUBSTR(DNAME,3,5) COUNT SEARC LES ERATI

Note que os valores estão alinhados para a esquerda. Isso porque SQL*Plus sempre mostra dados alfanuméricos alinhados para a esquerda por default.

7.4.1.6 INSTR INSTR(col/value,’caracter’) INSTR(col/value,’caracter’,pos,n)

encontra a primeira ocorrência do ‘caracter’. encontra a posição do caracter na coluna ou valor literal iniciando na posição que se encontra dentro de uma repetição desta variável ou literal.

SELECT DNAME, INSTR(DNAME,'A'), INSTR(DNAME,'ES'), INSTR(DNAME,'C',1,2) FROM DEPT;

DNAME ACCOUNTING RESEARCH SALES OPERATIONS

INSTR(DNAME,'A') 1 5 2 5

INSTR(DNAME,'ES') 0 2 4 0

INSTR(DNAME,'C',1,2) 3 0 0 0

7.4.1.7 LTRIM e RTRIM Removem específicos caracteres de um linha. LTRIM(col/value,’caractere(s)’) Se o caracter não é especificado cortará os brancos da esquerda. Exemplo: SELECT DNAME, LTRIM(DNAME,'A'), LTRIM(DNAME,'AS'), LTRIM(DNAME,'ASOP') FROM DEPT;


LTRIM(DNAME,'A') CCOUNTING RESEARCH SALES OPERATIONS

RTRIM(col/value,’caractere(s)’)

LTRIM(DNAME,'AS') CCOUNTING RESEARCH LES OPERATIONS

LTRIM(DNAME,'ASOP') CCOUNTING RESEARCH LES ERATIONS

Se os caracteres não forem especificados serão removidos os brancos.

SELECT DNAME, RTRIM(DNAME,'G'), RTRIM(DNAME,'GHS'), RTRIM(DNAME,'N') FROM DEPT;




52

RTRM(DNAME,'G') ACCOUNTIN RESEARCH SALES OPERATIONS

7.4.1.8 SOUNDEX SOUNDEX(col/value)

RTRIM(DNAME,'GHS') ACCOUNTIN RESEARC SALE OPERATION

RTRIM(DNAME,'N') ACCOUNTING RESEARCH SALES OPERATIONS

retorna uma linha de caracteres representando o som da palavra para uma coluna ou um valor literal. Esta função retorna a fonética representação de uma palavra.

SELECT ENAME, SOUNDEX(ENAME) FROM EMP WHERE SOUNDEX(ENAME) = SOUNDEX('FRED');

NOME SOUNDEX(NOME) ----------- -------------FORD F630

7.4.1.9 LENGTH LENGTH(col/value)

retorna o número de caracteres na coluna ou valor literal.

SELECT LENGTH(‘CURSO SQL’), LENGTH(DEPTNO), LENGTH(DNAME) FROM DEPT;

LENGTH(‘CURSO SQL’) 9 9 9 9

LENGTH(DEPTNO) 2 2 2 2

LENGTH(DNAME) 10 8 5 10

Note que a função INSTR, LENGTH retornam um valor numérico.

7.4.1.10

TRANSLATE e REPLACE

As funções TRANSLATE e REPLACE são usadas para substituir caracteres. TRANSLATE(col/value, from,to ) Faz a substituição de um ou mais caracteres por outros. Exemplo: SELECT ENAME, TRANSLATE(ENAME,'C','P'), JOB, TRANSLATE(JOB,'AR','IT') FROM EMP WHERE DEPTNO = 10;

ENAME CLARK KING MILLER

TRANSLATE(ENAME,'C','P' PLARK KING MILLER

REPLACE(col/value,linha,linha_alterada)

JOB MANAGER PRESIDENT CLERK

TRANSLATE(JOB,'AR','IT') MINIGET PTESIDENT CLETK

Retorna o valor da coluna com toda a ocorrência da linha de alteração. Se a linha alterada for omitida todo a linha especificada será removida.



SELECT FROM

53

JOB, REPLACE(JOB,'SALESMAN','S ALESPERSON') EMP;

JOB, REPLACE(JOB,'SALESMAN','SALESPERSON') CLERK CLERK SALESMAN SALESPERSON SALESMAN SALESPERSON MANAGER MANAGER SALESMAN SALESPERSON MANAGER MANAGER MANAGER MANAGER ANALYST ANALYST PRESIDENT PRESIDENT SALESMAN SALESPERSON CLERK CLERK CLERK ANALYST CLERK

CLERK ANALYST CLERK

A Função REPLACE é um complemento da função TRANSLATE que substitui caracteres um a um e o REPLACE substitui um linha por outra.

7.4.2 Aninhamento de Funções Funções de linhas únicas podem ser aninhadas para várias finalidades. Se funções são aninhadas, elas são avaliadas de dentro para fora. Exemplo: X(D(A(B(C(caracter))))) ordem lógica de execução “C,B,A,D e X.”

7.4.3 Funções Numéricas As funções aceitam entrada de números e retornam valores numéricos.

7.4.3.1 ROUND ROUND(col/value,n) SELECT

FROM WHERE

arredonda uma coluna, expressão ou valor para n casas decimais. Se n for negativo, os números para esquerda do decimal são arredondados.

ROUND(45.923,1), ROUND(45.923), ROUND(45.323,1), ROUND(45.323,-1), ROUND(SAL/32,2) EMP DEPTNO = 10;

ROUND(45.923,1) 45.9 45.9 45.9

7.4.3.2 TRUNC TRUNC(col/value.n)

ROUND(45.923) 46 46 46

ROUND(45.323,1) 45.3 45.3 45.3

ROUND(45.323,-1) 50 50 50

ROUND(SAL/32,2) 76.56 156.25 40.63

trunca a coluna, expressão ou valor para n casas decimais. Se n é negativo



54

os números para esquerda das casas decimais são truncados para zero. SELECT

TRUNC(45.923,1), TRUNC(45.923), TRUNC(45.323,1), TRUNC(45.323,-1), TRUNC(SAL/32,2) EMP DEPTNO = 10;

FROM WHERE

TRUNC(45.923,1) 45.9 45.9 45.9

TRUNC(45.923) 45 45 45

7.4.3.3 CEIL CEIL(col/value)

TRUNC(45.323,1) 45.3 45.3 45.3

TRUNC(45.323,-1) 40 40 40

TRUNC(SAL/32,2) 76.56 156.25 40.62

Arredonda acima.

Exemplo: SELECT CEIL(SAL), CEIL(99.9), CEIL(101.76), CEIL(-11.1) FROM EMP WHERE SAL BETWEEN 3000 AND 5000;

CEIL(SAL) 3000 5000 3000

CEIL(99.9) 100 100 100

7.4.3.4 FLOOR FLOOR(col/value)

CEIL(101.76) 102 102 102

CEIL(-11.1) -11 -11 -11

Arredonda para baixo.

SELECT FLOOR(SAL), FLOOR(99.9), FLOOR(101.76), FLOOR(-11.1) FROM EMP WHERE SAL BETWEEN 3000 AND 5000;

FLOOR(SAL) 3000 5000 5000

FLOOR(99.9) 99 99 99

7.4.3.5 POWER POWER(col/value,n)

FLOOR(101.76) 101 101 101

FLOOR(-11.1) -12 -12 -12

eleva uma coluna, expressão ou valor para uma potência; n pode ser negativo mas deve ser um número, se não um erro será retornado

SELECT SAL, POWER(SAL,2), POWER(SAL,3), POWER(50,5) FROM EMP WHERE DEPTNO = 10;

SAL 2450 5000 1300

POWER(SAL,2) 6002500 25000000 1690000

POWER(SAL,3) 1,471E+10 1,250E+11 2,197E+09

POWER(50,5) 312500000 312500000 312500000



7.4.3.6 SQRT SQRT(col/value)

55

encontra a raiz quadrada da coluna ou valor. Se a coluna ou valor for menor que zero será retornado nulo.

SELECT SAL, SQRT(SAL), SQRT(40), SQRT(COMM) FROM EMP WHERE COMM > 0;

SAL 1600 1250 1250

SQRT(SAL) 40 35,355339 35,355339

7.4.3.7 SIGN SIGM(col/value)

SQRT(40) 6,3245553 6,3245553 6,3245553

SQRT(COMM) 17,320508 22,36068 37,416574

retorna -1 se a coluna, expressão ou valor for negativo ou zero e 1 se for positivo.

SELECT SAL-COMM, SIGN(SAL-COMM), COMM-SAL, SIGN(COMM-SAL) FROM EMP WHERE DEPTNO = 30;

SAL-COM SIGN(SAL-COM) COM-SAL SIGN(SAL-COM) 1300 1 -1300 -1 750 1 -750 -1 -150 -1 150 1 1500 1 -1500 -1 Freqüentemente a função SIGN é usada para testar se um valor é menor, maior ou igual a um segundo valor. O seguinte exemplo apresenta todos os empregados os quais o salário é maior que sua comissão: SELECT ENAME, SAL, COMM FROM EMP WHERE SIGN(SAL-COMM) = 1;

NOME ALLEN WARD TURNER

SAL 1600 1250 1500

7.4.3.8 ABS ABS(col/value) SELECT FROM WHERE

COM 300 500 0

encontra o valor absoluto de um coluna, expressão ou valor.

SAL, COMM, COMM-SAL, ABS(COMM-SAL), ABS(-35) EMP DEPTNO = 30;

SAL 1600 1250 1250 2850 1500 950

7.4.3.9 MOD MOD(val1,val2)

COM 300 500 1400

COM-SAL -1300 -750 150

ABS(COM-SAL) 1300 750 150

0

-1500

1500

ABS(-35) 35 35 35 35 35 35

encontra o resto da divisão val1 por val2 Prof. Flavio Rezende


56

SELECT SAL, COMM, MOD(SAL,COMM), MOD(100,40) FROM EMP WHERE DEPTNO = 30 ORDER BY COMM;

SAL 1500 1600 1250 1250 2850 950

COM 0 300 500 1400

MOD(SAL,COM) MOD(100,40) 1500 20 100 20 250 20 1250 20 20 20

7.4.4 Funções de Data Funções de data operam sobre datas do ORACLE. Todas as funções de datas retornam valores de tipo data exceto MONTHS_BETWEEN o qual retorna um valor numérico. Armazenamento de Datas no ORACLE Século Ano Mês Dia Horas Minutos Segundos • • • • • • •

O padrão de data mostrados nas pesquisas é DD-MON-YY.

7.4.4.1 Sysdate Sysdate é uma coluna que retorna a data e horário corrente. Você pode usar o SYSDATE como uma outra coluna qualquer. Por exemplo, você pode mostrar data corrente selecionando o sysdate de uma tabela simulada chamada DUAL. A tabela DUAL é uma tabela do sistema e deve ser permitido acessá-la para todos os usuários. Ela contém uma coluna DUMMY e uma linha com o valor X. A tabela DUAL é usada quando você quer retornar apenas uma linha. Para mostrar a data corrente: SELECT SYSDATE FROM DUAL;

SYSDATE -------01/12/00 Você poderia facilmente selecionar o sysdate da tabela EMP, mas seriam retornados tantos sysdate quanto o número de linhas na tabela EMP. Usando Operadores Aritméticos

Devido ao fato das datas serem armazenadas como número, é possível fazer cálculos com elas usando operadores aritméticos tal como adição e subtração. Você pode adicionar e subtrair números bem como data. As operações que você pode realizar são: data + número Adicionando um número de dias em uma data, produzindo uma nova data data – número subtraindo um número de dias de uma data, produzindo uma nova data Prof. Flavio Rezende


data – data data+número/24

57

subtraindo uma data de outra, produzindo um número de dias adicionando um número de horas em uma data produzindo um nova data

Exemplo: SELECT HIREDATE, HIREDATE +7, HIREDATE -7, SYSDATE - HIREDATE FROM EMP WHERE HIREDATE LIKE '%12%';

HIREDATE 17/12/80 03/12/81 03/12/81

HIREDATE +7 24/12/80 10/12/81 10/12/81

HIREDATE -7 10/12/80 26/11/81 26/11/81

7.4.4.2 MONTHS_BEETWEEN MONTHS_BETWEEN(data1,data2)

SELECT FROM WHERE

SYSDATE- HIREDATE 7289,7189 6938,7189 6938,7189

encontra o número de meses entre data 1 e data2. O resultado pode ser positivo ou negativo. Se a data 1 for posterior a data2, então o resultado será positivo, se a data 1 for menor que a data 2 o resultado será negativo.

MONTHS_BETWEEN(SYSDATE,HIREDATE), MONTHS_BETWEEN('01-01-84','05-11-88') EMP MONTHS_BETWEEN(SYSDATE, HIREDATE)> 59;

MONTHS_BETWEEN(SYSDATE,DATA) 239,50724 237,41047 237,34595 235,99111 230,1524 235 233,76531 163,44273 228,50724 230,79756 162,31369 227,95885 227,95885 226,31369

MONTHS_BETWEEN('01-JAN-84','05-NOV-88') -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903 -58,12903

A parte não inteira do resultado representa uma parcela do mês.

7.4.4.3 ADD_MONTHS ADD_MONTHS(data,n) adiciona n números de meses na data; n deve ser inteiro e pode ser negativo. SELECT HIREDATE, ADD_MONTHS(HIREDATE,3), ADD_MONTHS(HIREDATE,-3) FROM EMP WHERE DEPTNO = 20;

HIREDATE 17/12/80

ADD_MONTHS( HIREDATE,3) ADD_MOSTHS(HIREDATE,-3) ADD_MOSTHS(HIR EDATE,-3) 17/03/81 17/09/80



02/04/81 19/04/87 23/05/87 03/12/81

58

02/07/81 19/07/87 23/08/87 03/03/82

02/01/81 19/01/87 23/02/87 03/09/81

7.4.4.4 NEXT_DAY NEXT_DAY(data1,’caracter’)

data do próximo dia especificado da semana. Caracter deve ser um número representado um dia, ou o dia da semana descrito em inglês.

SELECT HIREDATE, TO_CHAR(HIREDATE,’DAY’), FROM EMP WHERE DEPTNO = 20;

HIREDATE 17/12/80 02/04/81 19/04/87 23/05/87 03/12/81

TO_CHAR(HIREDATE,’DAY’) QUARTA-FEIRA QUINTA-FEIRA DOMINGO SÁBADO QUINTA-FEIRA

NEXT_DAY(HIREDATE,5)

NEXT_DAY(HIREDATE,5) 18/12/80 09/04/81 23/04/87 28/05/87 10/12/81

7.4.4.5 LAST_DAY LAST_DAY(data) encontra a data do ultimo dia do mês da data especificada SELECT SYSDATE, LAST_DAY(SYSDATE), HIREDATE, LAST_DAY(HIREDATE), LAST_DAY('15-02-88') FROM EMP WHERE DEPTNO = 20;

SYSDATE 01/12/00 01/12/00 01/12/00 01/12/00 01/12/00

LAST_DAY(SYSDATE) 31/12/00 31/12/00 31/12/00 31/12/00 31/12/00

HIREDATE 17/12/80 02/04/81 19/04/87 23/05/87 03/12/81

LAST_DAY(HIREDATE) 31/12/80 30/04/81 30/04/87 31/05/87 31/12/81

LAST_DAY(‘15-02-88’) 29/02/88 29/02/88 29/02/88 29/02/88 29/02/88

7.4.4.6 ROUND A função ROUND pode ser aplicada para datas. ROUND(data) ROUND(data,’MONTH’) ROUND(data,’YEAR’)

retorna a data com o horário em 12:00(meio-dia) Usamos isso quando comparamos datas que tenham diferentes horários. retorna o primeiro dia do mês da data, se a data estiver na primeira metade do mês; se não retorna o primeiro do mês seguinte. retorna o primeiro dia do ano da data se data estiver na primeira metade do ano; se não retorna o primeiro do ano seguinte.

SELECT SYSDATE, ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR') FROM DUAL;

SYSDATE 04-DEC-89

ROUND(SYSDATE,'MONTH') 01-DEC-89


ROUND(SYSDATE,'YEAR') 01-JAN-90


7.4.4.7 TRUNC TRUNC(data,’caracter’)

59

encontra a data do primeiro dia do mês quando caracter = ‘MONTH’. Se o caracter = ‘YEAR’ ele encontra o primeiro dia do ano.

SELECT SYSDATE, TRUNC(SYSDATE,'MONTH'), TRUNC(SYSDATE,'YEAR') FROM DUAL;

SYSDATE 04-DEC-89

TRUNC(SYSDATE,'MONTH') 01-DEC-89

TRUNC(SYSDATE,'YEAR') 01-JAN-89

TRUNC é usado se você quiser remover o horário do dia. O horário contido no dia é removido por default.

7.4.5 Funções de Conversão SQL possui um número de funções para controlar os tipos de conversão de dados. Essas funções convertem um valor de um tipo de dado para outro. TO_CHAR(número,data,’formato’) TO_CHAR(número,data,’f ormato’) converte números e datas para formatos alfanuméricos TO_NUMBER(caracter) converte alfanuméricos para numéricos. TO_DATE(‘caracter’,’formato’) TO_DATE(‘caracter’,’for mato’) converte um alfanumérico representando uma data, para um valor de data de acordo com o formato especificado. Se o formato é omitido o formato padrão é ‘DD-MON-YY’.

7.4.5.1 TO_CHAR TO_CHAR(data,’máscara’)

Especifica que a data está sendo convertida para um novo formato na saída. Para converter a data corrente do formato padrão (DD-MON-YY) para uma nova máscara:

SELECT TO_CHAR(SYSDATE,'DAY, DD MONTH YYYY') FROM DUAL;

TO_CHAR(SYSDATE,'DAY, DD MONTH YYYY') ---------------------------------------------------------------------------SEXTA-FEIRA , 01 DEZEMBRO 2000 Note que: •

•

A ‘máscara’ deve estar entre aspas simples e pode ser incluída em vários formatos. A coluna e ‘máscara’ devem ser separadas por uma vírgula. DAY e MONTH na saída são espaçados automaticamente com brancos no tamanho de 9 caracteres

TO_CHAR pode também ser usado para extrair o horário de um único dia, e mostrá-lo no formato especificado. SELECT TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'HH:MI:SS') ---------------------------------------------------------------------------08:16:24 A função TO_CHAR é também usada para converter um valor do tipo numérico para um valor do tipo alfanumérico.



60

Formatos Numéricos Máscara Significado

Exemplo

9

999999

1234

0999999 $999999 999999.99 999,999 999999MI 999999PR 99.999EEEE

001234 $1234 1234.00 1,234 1234<1234> 1.234E+03

9999V99

123400

B9999.99

1234.00

posição numérica (número de 9s determinam a largura mostrada) mostra zeros mostra sinal de dólar ponto decimal na posição especificada vírgula na posição especificada sinal de menos à direita(valores negativos) parênteses para números negativos notação científica(formato de conter quatro Es unicamente) multiplica pela décima potência 10n(n = número 9s depois da V mostra valores zero em branco, não zero

0 $ . , MI PR EEEE V B SELECT SAL, FROM EMP;

TO_CHAR(SAL,'$9,999')

SAL TO_CHAR(SAL,'$9,999') 800 $800 1600 $1,600 1250 $1,250 2975 $2,975 1250 $1,250 2850 $2,850 2450 $2,450 3000 $3,000 5000 $5,000 1500 $1,500 1100 $1,100 950 $950 3000 $3,000 1300 $1,300 Os formatos das máscaras são opcionais. Se a ‘máscara’ é omitida, a data é convertida para um alfanumérico que é padrão DD-MON-YY. Se a ‘máscara’ não é especificada, o número é convertido para alfanumérico. Formatos de Data Máscara

SCC ou CC YYYY ou SYYYY YYY ou YY ou Y Y,YYY

Significado

Século, prefixo ‘S’ “BC”data com ‘-’ Ano, prefixo ‘S’ “BC” data com ‘-’ Último 3, 2 ou 1 digito(s) do ano Ano com vírgula nessa posição



SYEAR ou YEAR BC ou AD B.C. ou A.D. Q MM MONTH MON WW ou W DDD ou DD ou D DAY DY J AM ou PM A.M. ou P.M. HH ou HH12 HH24 MI SS SSSSS /.,etc. “...”

61

Ano, soletrado na saída ‘S’ prefixo “BC” data com ‘-’ BC/AD período BC/AD indicador com períodos Um quarto do Ano Mês nome do mês, espaçamento com brancos do tamanho de 9 caracteres nome do mês, 3 letras abreviadas Semana do ano ou mês dia do ano, mês ou semana nome do dia, espaçado com brancos com 9 caracteres de tamanho nome do dia, 3 letras abreviadas data Juliana, o número de dias desde 31 dezembro 4713 antes de Cristo Indicador meridiano indicador meridiano com períodos horas do dia (1-12) horas do dia (0-23) minuto segundos segundos passado meia-noite(0-86399) pontuação é reproduzida no resultado cotas de linhas são representadas no resultado.

Os sufixos abaixo devem ser adicionados em frente dos códigos: TH Ex.: SELECT TO_CHAR(SYSDATE,'DAY, DDTH MONTH YYYY') FROM DUAL; SEXTA-FEIRA , 01ST DEZEMBRO 2000 SP Ex.: SELECT TO_CHAR(SYSDATE,'DAY, DDSP MONTH YYYY') FROM DUAL; SEXTA-FEIRA , ONE DEZEMBRO 2000 SPTH ou thsp Ex.: SELECT TO_CHAR(SYSDATE,'DAY, DDSPTH MONTH YYYY') FROM DUAL; SEXTA-FEIRA , FIRST DEZEMBRO 2000 DAY Day Month Ddth DdTh

MONDAY Monday July 14th 14Th

7.4.5.2 TO_NUMBER No seguinte exemplo a função TO_NUMBER é usada para transformar um número armazenado como um alfanumérico para um tipo numérico: SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL > TO_NUMBER('1500');

EMPNO ENAME 7499 ALLEN 7566 JONES

JOB SALESMAN MANAGER

SAL 1600 2975



7698 7782 7788 7839 7902

BLAKE CLARK SCOTT KING FORD

62

MANAGER MANAGER ANALYST PRESIDENT ANALYST

2850 2450 3000 5000 3000

7.4.5.3 TO_DATE Para mostrar todos os empregados admitidos em 4 de junho de 1984 (não formato padrão), nós podemos usar a função TO_DATE: SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE = TO_DATE ('September EMPNO ----7844

ENAME ---------TURNER

8, 1981','Month dd, yyyy');

HIREDATE --------08-SEP-81

O conteúdo é convertido para data e comparado com o valor de DATA. Para entrar um linha na tabela EMP com a data não no formato padrão: INSERT INTO EMP (EMPNO, DEPTNO,HIREDATE) VALUES (7777,20,TO_DATE('19/08/90 00:00:00','DD/MM/YY HH:MI:SS'));



63

7.4.6 Funções que Aceitam Vários Tipos de Entrada de Dados 7.4.6.1 DECODE DECODE é a mais potente função do SQL. Ele facilita pesquisas condicionais fazendo o trabalho de ‘ferramentas’ ou comandos ‘IF-THEN-ELSE’. Sintaxe: DECODE(col/expressão, procurado1,resultado1, procurado2,resultado2...,padrão)

Col/expressão é comparado com cada um dos valores procurados e retorna o resultado se a col/expressão é igual ao valor procurado. Se não for encontrado nenhum dos valores procurados, a função DECODE retorna o valor padrão. Se o valor padrão for omitido ele retornará um valor nulo. O seguinte exemplo decodifica os cargos dos tipos MANAGER e CLERK unicamente. Os outros cargos serão padrão, alterados para UNDEFINED: SELECT ENAME, JOB, DECODE(JOB,'CLERK','WORKER', 'MANAGER','BOSS') FROM EMP;


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

DECODE WORKER BOSS BOSS BOSS

WORKER WORKER WORKER

Para mostrar a gratificação percentual dependendo do grau do salário: SELECT GRADE, DECODE(GRADE,'1','15%', '2','10%', '3','8%', '4’,’5%') BONUS FROM SALGRADE;

GRADE BONUS FROM SALGRADE 1 15% 2 10% 3 8% 4 5% 5



64

Esse exemplo ilustra que com a função DECODE, o valor retornado é forçado a ter um tipo de dado no terceiro argumento.

7.4.6.2 NVL NVL(col/valor,valor)

converte um valor nulo para um um valor valor desejado. Tipo de dados devem combinar(col/valor e valor).

SELECT SAL*12+NVL(COMM,0), NVL(COMM,1000), SAL*12+NVL(COMM,1000) FROM EMP WHERE DEPTNO = 10;

SAL*12+NVL(COM,0) 29400 60000 15600

NVL(COM,1000) 1000 1000 1000

7.4.6.3 GREATEST GREATEST(col/valor1,col/valor2,...) GREATEST(col/valor1,col/valor2,.. .)

SAL*12+NVL(COM,1000) 30400 61000 16600

retorna o maior da lista de valores. Todos os col/valores são convertidos para um valor antes da comparação.

SELECT SAL, COMM, GREATEST(1000,2000), GREATEST(SAL,COMM) FROM EMP WHERE DEPTNO = 30;

SAL COMM GREATEST(1000,2000) GREATEST(SAL,COMM) 1600 300 2000 1600 1250 500 2000 1250 1250 1400 2000 1400 2850 2000 1500 0 2000 1500 950 2000 Na função GREATEST quando na lista de valores existe um valor nulo ele é considerado como o maior.

7.4.6.4 LEAST LEAST(col/valor1,col/valor2,...) LEAST(col/valor1,col/valor2,.. .)

retorna o menor valor de um lista de valores. Todos os valores são convertidos antes da comparação.

SELECT SAL, COMM, LEAST(1000,2000), LEAST(SAL,COMM) FROM EMP WHERE DEPTNO = 30;

SAL 1600 1250 1250 2850 1500 950

COMM 300 500 1400 0

LEAST(1000,2000) 1000 1000 1000 1000 1000 1000

LEAST(SAL,COMM) 300 500 1250 0

Na função LEAST quando na lista de valores existe um valor nulo ele é considerado como o menor.



7.4.6.5 VSIZE VSIZE(col/valor)

65

retorna o número de bytes interno do ORACLE representando um col/valor.

SELECT DEPTNO, VSIZE(DEPTNO), VSIZE(HIREDATE), VSIZE(SAL), VSIZE(ENAME) FROM EMP WHERE DEPTNO = 10;

SETOR 10 10 10

VSIZE(SETOR) 2 2 2

VSIZE(DATA) 7 7 7

VSIZE(SAL) 3 2 2

VSIZE(NOME) 5 4 6

7.4.7 Funções de Grupo Funções de grupo operam sobre conjuntos de linhas. Elas retornam resultados baseados sobre um grupo de linhas, antes que um resultado por linha tenha retornado como uma função de linha única. Como padrão todas as linhas de um tabela são trilhadas como um grupo. A clausula GROUP BY da declaração do SELECT é usada para agrupar as linhas em menores grupos. As funções de grupos são listadas abaixo: Função

AVG([DINSTINCT/ALL]n) COUNT([DINSTINCT/ALL]expr*) MAX([DISTINCT/ALL]expr) MIN([DISTINCT/ALL]expr) MIN([DISTINCT/ ALL]expr) STDDEV([DISTINCT/ALL]n) SUM([DISTINCT/ALL]n) VARIANCE([DISTINCT/ALL],n) VARIANCE([DISTINCT /ALL],n)

Valor Retornado

Valor médio de n, ignorando os valores nulos. Contador * conta todas as linhas selecionadas, incluindo duplicadas e linhas nulas valor máximo da expressão valor mínimo da expressão Desvio padrão de n, ignorando valores nulos. Valor soma de n, ignorando valores nulos. variação de n, ignorando valores nulos.

Todas as funções acima operam sobre um número de linhas (por exemplo, uma tabela inteira) e são portanto funções de GRUPO. DISTINCT faz uma função de grupo considerar valores não duplicados; ALL considera todos os valores. Todas as funções de grupo exceto o COUNT(*) ignoram os valores nulos.

7.4.7.1 AVG Para calcular a média salarial dos empregados, faça: SELECT AVG(SAL) FROM EMP;

AVG(SAL) ---------2073,2143 Note que as linhas da tabela EMP são trilhadas num único grupo.

7.4.7.2 MIN Uma função de grupo pode ser usada para subconjunto de linhas de uma tabela usando a clausula WHERE. Para encontrar o mínimo salário ganho por um escriturário, faça: SELECT MIN(SAL)


Apostila de Banco de Dados v.4.0 FROM WHERE

66

EMP JOB = 'CLERK';

MIN(SAL) --------800

7.4.7.3 COUNT Para encontrar o número de empregados do departamento 20, faça: SELECT COUNT(*) FROM EMP WHERE DEPTNO = 20;

COUNT(*) -------5

7.4.8 A cláusula GROUP BY Pode ser usada para dividir as linhas de uma tabela em um grupo menor. Funções de grupo devem ser usadas para resumir informações por cada grupo. Para calcular a média salarial de cada grupo de cargo, faça: SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;

JOB ANALYST CLERK MANAGER PRESIDENT SALESMAN

AVG(SAL) 3000 1037.5 2758.33333 5000 1400

7.4.8.1 Grupos dentro de Grupos Podemos então usar a clausula GROUP BY para prover resultados para grupos dentro de grupos. Para mostrar a media salarial mensal faturado por cada cargo dentro de um departamento, faça: SELECT DEPTNO, JOB, AVG(SAL) FROM EMP GROUP BY DEPTNO, JOB;

DEPTNO 10 10 10 20 20 20 30 30 30

JOB CLERK MANAGER PRESIDENT ANALYST CLERK MANAGER CLERK MANAGER SALESMAN

AVG(SAL) 1300 2450 5000 3000 950 2975 950 2850 1400

7.4.8.2 Funções de Grupo e Resultados Individuais A seguinte declaração SQL retorna o máximo salário para cada grupo:



67

SELECT MAX(SAL), JOB FROM EMP GROUP BY JOB;

MAX(SAL) 3000 1300 2975 5000 1600

OCUP ANALYST CLERK MANAGER PRESIDENT SALESMAN

7.4.9 A clausula HAVING Use a clausula HAVING se você quiser especificar qual grupo será mostrado. Para mostrar a média salarial para todos os departamentos que tiverem mais de três empregados, faça: SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING COUNT(1) > 3;

DEPTNO 20 30

AVG(SAL) 2175 1566.6667

Para mostrar só os cargos, onde o máximo salário é maior ou igual a $3000, faça: SELECT JOB, MAX(SAL) FROM EMP HAVING MAX(SAL)>=3000 GROUP BY JOB;

OCUP ANALYST PRESIDENT

MAX(SAL) 3000 5000

A clausula HAVING deve preceder uma clausula GROUP BY e é recomendado que seja colocada primeiro, pois é mais lógico. A clausula WHERE não pode ser usada para restringir itens de grupo. A seguinte declaração da clausula WHERE é errada. SELECT DEPTNO, AVG(SAL) FROM EMP WHERE AVG(SAL) > 2000 GROUP BY DEPTNO;

ERROR at line 3: ORA-0934: set function is not allowed here Você pode unicamente usar WHERE para restringir linhas individuais. Para restringir colunas de grupos usa-se a clausula HAVING: SELECT DEPTNO, AVG(SAL) FROM EMP



68

GROUP BY DEPTNO HAVING AVG(SAL) > 2000;

SETOR 10 20

AVG(SAL) 2916.66667 2175

7.4.10 A Ordem das clausulas na declaração SELECT. SELECT

coluna(s)

FROM

tabela(s)

WHERE

condição linha

GROUP BY

coluna(s)

HAVING

condição de grupo de linhas

ORDER BY

coluna(s);

7.5 Executando Pesquisas Padrões com Variáveis Substituíveis 7.5.1 Única Variável Substituível Você pode usar variáveis substituíveis para representar valores, em tempo de execução. Uma variável pode ser uma idéia de como um valor pode ser armazenado temporariamente. Uma variável é representada por um único "e" comercial(&), e o valor é atribuído na mesma. A seguinte declaração apresenta ao usuário um número de departamento na execução: SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = &DEP_NR;

Enter value for DEP_NR : 10 EMPNO 7782 7839 7934


SAL 2450 5000 1300

O exemplo acima usa a condição WHERE SETOR = 10 Com o único "e" comercial o usuário é solicitado toda vez que o comando é executado porque a variável não é definida e consequentemente o valor digitado não é salvo. Valores alfanuméricos ou datas, precisam ser incluídos entre aspas simples na entrada. Para evitar a entrada das aspas simples na execução, declara-se a variável entre aspas simples. Na declaração seguinte, as variáveis estão incluídas entre aspas simples, só que as aspas simples não são requeridas na execução: SELECT FROM

ENAME, DEPTNO, SAL*12 EMP



69

WHERE JOB = '&JOB';

Enter value for OCUP: MANAGER ENAME DEPTNO SAL*12 JONES 20 35700 BLAKE 30 34200 CLARK 10 29400 O tamanho da variável é indefinido e o valor será pedido toda vez que for executada a declaração. É ainda possível entrar com um nome de coluna de um tabela na execução. No seguinte exemplo você entrará com um expressão aritmética: SELECT DEPTNO, &ARITHMETIC_EXPRESSION FROM EMP; Enter value for arithmetic_expression: sal/12

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

&ARITHMETIC_EXPRESSION 66,666667 133,33333 104,16667 247,91667 104,16667 237,5 204,16667 250 416,66667 125 91,666667 79,166667 250 108,33333

7.5.2 Duplo & para Variáveis substituíveis Se uma variável é prefixada com um duplo "e" comercial(&&), o SQL*Plus preenche o valor da variável com o primeiro valor fornecido na execução da declaração SQL. Exemplo: SELECT ENAME, DEPTNO, JOB FROM EMP WHERE DEPTNO = &&SETOR_PLEASE; Enter value for SETOR_please: 10


DEPTNO 10 10 10

JOB MANAGER PRESIDENT CLERK

É perguntado uma vez e não mais.



70

7.5.3 O Comando DEFINE Um valor pode ser atribuído para uma variável usando o comando DEF[INE] do SQL*Plus. O valor atribuído pode ser referenciado na declaração SELECT ou pelo nome de variável predefinido de um (&). Sintaxe: SQL>DEFINE var = valor

No exemplo seguinte, uma variável tem seu conteúdo definido como uma expressão aritmética que calcula a remuneração. Na subsequente declaração, a variável REM é referenciada para um número de vezes. A variável é então esvaziada usando UNDEF(INE): DEFINE REM = "SAL*12+NVL(COMM,0)"; SELECT ENAME, JOB, &REM FROM EMP ORDER BY &REM; UNDEFINE REM NOME

OCUP

---------SMITH JAMES ADAMS WARD MILLER MARTIN TURNER ALLEN

---------CLERK CLERK CLERK SALESMAN CLERK SALESMAN SALESMAN SALESMAN

SAL*12+NVL(COM,0) ---------------- -9600 11400 13200 15500 15600 16400 18000 19500

As Aspas duplas em volta da expressão são opcionais a menos que a expressão tenha espaços.

7.5.4 O comando ACCEPT O comando ACCEPT permite criar uma variável. O ACCEPT é geralmente usado num arquivo comando. Esta variável então pode ser referenciada na declaração do SQL. Existem benefícios em usar o ACCEPT para definir Variáveis Substituíveis. Dados tipo Data podem ser checados. A mensagem de entrada de dados pode ser mais explicativa Valores da resposta podem ser escondidos A sintaxe do comando é: • • •

ACC(EPT)variable(NUMERICO/ALFANUMERICO)(PROMPT/NOPROMPT 'texto') (HIDE)

Sintaxe

Descrição

NUMBER/CHAR

determina o tipo de variável. Se o valor entrado for inválido uma mensagem será mostrada. mostra o texto se for especificado faz o ACCEPT omitir uma linha aguardando a entrada esconde entrada para o usuário, por exemplo, no caso de senha.

PROMPT ‘texto’ NOPROMPT HIDE Exemplos

SQL> ACCEPT SALARY NUMBER PROMPT 'Salary figure : '



71

Salary figure : 30000 SQL> ACCEPT PASSWORD CHAR PROMPT 'Password : ' HIDE Password : SQL> ACCEPT COM NUMBER NOPROMPT 500 SQL> DEFINE DEFINE SALARY DEFINE PASSWORD DEFINE COM

= = =

30000 (NUMBER) "FREEBIES" (CHAR) 500 (NUMER)

Como fazer variáveis permanecerem definidas? Até que você as redefina UNDEF(INE) ou até você sair do SQL*Plus. Duas outras maneiras para definir uma variável:

SQL> ACCEPT variável (tipo) (PROMPT 'texto') (HIDE) SQL> COLUMN nome coluna NEW_VALUE variável

7.6 Recuperando valores da base de dados Você pode atribuir resultados de querys para variáveis, porém tomando cuidado para que a query sempre retorne apenas uma linha. Ex.: SELECT NOTAFISCAL , DATA INTO V_NOTAFISCAL , V_DATA WHERE NUMERO_VENDA = 2334; O comando acima atribui os valores dos campos NOTAFISCAL e DATA para as variáveis previamente definidas V_NOTAFISCAL e V_DATA respectivamente.



72

7.6.1 Comandos de Manipulação de Dados (DML) 7.6.1.1 Insert Sintaxe: INSERT INTO nome_tabela(campo1,campo2...) VALUES (valor1,valor2...);

Ex: INSERT INTO SALGRADE(GRADE,LOSAL,HISAL) VALUES(6, 1500, 3000);

Sintaxe: INSERT INTO nome_tabela(campo1,campo2...) SELECT campo1, campo2 FROM nome_tabela2;

Ex: create table SALGRADE_TEMP (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE_TEMP(GRADE,LOSAL,HISAL) SELECT GRADE,LOSAL,HISAL FROM SALGRADE;

7.6.1.2 Update Sintaxe: UPDATE nome_tabela SET campo1 = valor1 [,campo2 = valor2...] [ WHERE condição]

Ex: UPDATE SALGRADE_TEMP SET HISAL = ‘2000’ WHERE LOSAL < 2000;

7.6.1.3 Delete Sintaxe: DELETE FROM nome_tabela [ WHERE condição]

Ex: DELETE FROM SALGRADE_TEMP

WHERE LOSAL < 1000;

7.6.1.4 Create Table.......As Select Sintaxe: CREATE TABLE nome_da_tabela (nome_da_coluna [restrições] [, nome_da_coluna [restrições]] [, restrições])

AS SELECT.....

Ex.:



73

CREATE TABLE SALGRADE_TTEMP AS SELECT GRADE, LOSAL FROM SALGRADE_TEMP;

7.6.1.5 Rename Sintaxe: RENAME nome_antigo_da_tabela TO nome_novo_da_tabela

Ex.: RENAME SALGRADE_TTEMP TO SALGRADE_T;



74

7.7 Seqüências São estruturas criadas no banco de dados que retornam valores diferentes a cada acesso. Por default este valor cresce seqüencialmente.

Sintaxe: CREATE SEQUENCE nome_da_sequencia [STAR WITH valor_inicial] (default1) [INCREMENT BY incremento] (default1) [MAXVALUE valor_maximo_da_sequencia/NOMAXVALUE] [MINIVALUE valor_minimo_da_sequencia/NOMINVALUE] [CYCLE/NOCYCLE] (se for cíclica, quando atingir o máximo volta ao início)

Ex.: CREATE SEQUENCE SEQ START WITH 6;

Para pegar o valor corrente da seqüência, utiliza-se o valor CURRVAL e para pegar o próximo valor, utiliza-se NEXTVAL. Somente se inicia uma seqüência com o primeiro NEXTVAL referente a ela. Sintaxe: SELECT nome_da_sequencia.CURRVAL from Dual; SELECT nome_da_sequencia.NEXTVAL from Dual;

Ex.: SELECT SEQ.NEXTVAL from Dual; 6 SELECT SEQ.CURRVAL from Dual; 6 SELECT SEQ.NEXTVAL from Dual; 7

7.7.1 Alterando uma seqüência Sintaxe: ALTER SEQUENCE nome_da_sequencia Opção

Ex.: ALTER SEQUENCE SEQ INCREMENT BY 2;

7.7.2 Eliminando uma seqüência Sintaxe: DROP SEQUENCE nome_da_sequencia

Ex.: DROP SEQUENCE SEQ;



75

7.8 Índices São estruturas que permitem a recuperação rápida de dados. Sintaxe: CREATE [UNIQUE] INDEX nome_do_indice ON nome_da_tabela (nome_da_coluna ASC/DESC[,nome_da_coluna ASC/DESC]...]}

Ex.: CREATE INDEX FONE ON CLIENTE (nr_fone); Os índices não podem ser alterados. É necessário excluí-los e cria-los novamente. Para excluir um índice utiliza-se o comando DROP INDEX.

7.8.1 Recuperando informações sobre Índices: Algumas informações sobre os índices(INDEX_NAME, TABLE_OWNER, TABEL_NAME) são armazenadas numa tabela de controle chamada USER_INDEXES e podem ser recuperados a partir do seguinte comando: Sintaxe: SELECT * FROM USER_INDEXES WHERE TABLE_NAME = ‘nome_da_tabela’;

O nome da tabela além de estar entre aspas simples deve ser digitado em letras maiúsculas. Também podem ser recuperadas informações sobre colunas(INDEX_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, COLUMS_LENGTH). Estas informações ficam armazenadas na tabela de controle USER_IND_COLUMNS.

7.9 Controle de Concorrência O uso da função lock mantém a consistência do banco de dados no caso de acesso simultâneo.

7.9.1 Tipos de Lock -

Lock implícito – gerado internamente pelo banco.

-

Lock explícito – criado via comando SQL e pode ser gerado a partir dos comandos Lock table e Select for update.

7.9.1.1 Lock table – sobrepõe o lock implícito Sintaxe: LOCK TABLE [nome_da_tabela] IN ROW SHARE / ROW EXCLUSIVE/ SHARE UPDATE / SHARE / SHARE ROW EXCLUSIVE / EXCLUSIVE MODE [NOWAIT]

Nome_da_tabela – indica qual tabela sofrerá o bloqueio. ROW SHARE – nenhum outro usuário poderá bloquear exclusivamente a tabela. ROW EXCLUSIVE – permite acesso simultâneo à tabela, bloqueando linhas individuais. SHARE UPDATE – Somente bloqueia linhas simples, permitindo a continuidade na consulta de dados. SHARE – os outros usuários podem consultar os dados mas não podem alterá-los. SHARE ROW EXCLUSIVE – nenhum outro usuário poderá bloquear a tabela. Diferentemente dos outros bloqueios tipo SHARE, somente um usuário por vez poderá fazer o COMMIT na tabela.



76

EXCLUSIVE – só o usuário gerador do bloqueio poderá fazer alterações. NOWAIT – libera o processo, caso não for possível efetuar o bloqueio. Ex.: LOCK TABLE BLOQUEIO In Exclusive Mode; O LOCK termina com o fim da transação (COMMIT/ROLLBACK).

7.9.1.2 Select for Update Bloqueia antecipadamente as linhas que serão alteradas ou excluídas até o fim da trasação. Sintaxe: SELECT... FOR UPDATE OF nome_da_coluna1 [,nome_da_colunaN] [NOWAIT]

Ex.: SELECT COD, TIPO_BLOQ FROM BLOQUEIO WHERE COD = 1 FOR UPDATE OF COD;

O uso do Select for Update não é permitido caso o comando select tenha as cláusulas DISTINCT, GROUP BY, UNION, INTERSECT ou MINUS e funções de grupo(Count, AVG, Sum, Max, etc)

7.10 Declarações 7.10.1 Declarações de Variáveis Variáveis são declaradas na seção de declaração de variáveis. Em geral a sintaxe para a declaração de variáveis é: Sintaxe : Nome_variavel type [CONSTANT] [NOT NULL] [:=value]

Onde:

Nome_variável : é o nome da variável. Type : é o tipo que a variável irá assumir. CONSTANT : se utilizado, a variável precisa ser inicializada com um valor, e este valor não pode ser alterado ao longo do programa. NOT NULL : significa que a variável não poderá assumir NULL ao longo do programa. Quando utilizada a variável deve ser inicializada com um valor. Value : é um valor para inicializar a variável. Ex: DECLARE Const_Temp

CONSTANT V_SEQ

NUMBER(5) := 15 ; NUMBER(5) ;

BEGIN ...... END;



77

7.10.2 Tipos de Dados PL/SQL Existem basicamente duas categorias de tipos de dados PL/SQL, que são escalar, composto.

7.10.2.1 Escalar Tipos escalares podem ser divididos em seis famílias: Number, character, date, raw, rowid, boolean. NUMBER •

Number - numérico com tamanho máximo de 38 caracteres. Na especificação de tipos de

dados numéricos com casas decimais, primeiro é informado o número total de dígitos, que inclui as casas decimais, cujo número de dígitos estará separado do primeiro por uma virgula. Number (4) - no máximo 4 caracteres numéricos. Number (12,2) - no máximo 12 números (10 inteiros e 2 decimais). Number (-3,8) - 0 inteiros e 8 decimais, em que somente as 3 últimas podem ter valor. Este tipo de variavel possui alguns subtipos derivados: Decimal - subtipo idêntico ao tipo Number. Dec, DoublePrecision, Integer, Int, Numeric, Real e Smalllnt - são subtipos do tipo Number que apresentam, como diferença deste, apenas diferentes faixas de valores permitidos. Float - permite armazenar valores de até 126 dígitos binários. PLS_Integer - armazena valores numéricos, positivos e negativos, entre -2147483647 e 2147483647. Este tipo de variável requer menor espaço de armazenamento do que uma variável do tipo Number e permite melhor desempenho em cálculos do que os tipos Binaryinteger ou Number. Binary-Integer - armazena valores numéricos, positivos e negativos, entre -2147483647 e 2147483647. Este tipo de variável requer menor espaço de armazenamento do que uma variável do tipo Number e possui quatro subtipos derivados desta: Natural - pode armazenar valores entre 0 e 2147483647. NaturaIN - armazena valores entre 0 e 2147483647 e não pode receber valores nulos. Positive - pode armazenar valores entre 1 e 2147483647. PositiveN – armazena valores entre I e 2147483647 e não pode receber valores nulos. • • •

• •

• •

•

• • • •

CHARACTER • • •

•

•

Char - alfanumérico de tamanho fixo, máximo de 255 caracteres. Character - subtipo idêntico ao tipo Char. Varchar2 - alfanumérico de tamanho máximo de 2.000 caracteres. A principal diferença deste

tipo para o tipo Char é que com o tipo Varchar2 o numero de caracteres que não for utilizado não ocupa espaço no banco de dados. Varchar e String - subtipos idênticos ao tipo Varchar2, mas são utilizados apenas para manter compatibilidade com versões diferentes ou anteriores do SQL. Long - alfanumérico com tamanho máximo de 2G. (O tamanho não pode ser informado.) Só pode existir um por tabela e não pode ser utilizado na cláusula WHERE de consultas. DATE

•

Date - data e hora (formato-padrão: DD-MON-YY, 26-APR-74).



78

ROWID •

Rowid - utilizado para armazenar valores de Rowid, selecionados de linhas de tabelas. Este

tipo normalmente é empregado quando se quer eliminar ou alterar um registro, o qual tenha sido previamente selecionado, visto que Rowid é uma forma rápida de acessar uma linha em uma tabela. Formato do Rowid: OOOOOOFFFBBBBBBSSS. Onde OOOOOO- identifica o objeto, FFF-identifica o Data File, BBBBBB- identifica o bloco dentro do Data file e o SSS identifica a linha dentro do bloco. RAW •

Raw - armazena valores hexadecimais com tamanho variável (máximo de 2K). Normalmente,

•

este tipo de campo é utilizado para armazenamento de imagens. Long Raw - armazena valores hexadecimais com tamanho variável (máximo de 2G). Também utilizado para armazenamento de imagens. BOOLEAN

•

Boolean - permite armazenar os valores TRUE, FALSE ou NULL. No momento de utilizar este

tipo de variáveis em atribuições, pode-se atribuir um dos valores permitidos explicitamente ou uma condição(>, <, =, <>, ...) cujo valor de veracidade será atribuído à variável.

7.10.2.1.1 Usando %TYPE

Na maioria dos casos os programas em PL/SQL irá manipular dados que estão armazenados em tabelas do Banco de Dados. Neste caso a variável deve ser declarada com o mesmo tipo do campo da tabela. Ex: DECLARE V_DEPTNO SCOTT.DEPT.DEPTNO%TYPE ; V_EMPNO SCOTT.EMP.EMPNO%TYPE ; BEGIN ...

No exemplo acima V_DEPTNO é declarada com o mesmo tipo do campo DEPTNO da tabela DEPT do schema SCOTT. V_EMPNO é declarada com o mesmo tipo do campo EMPNO da tabela EMP do schema SCOTT.

7.10.2.2 Tipos de Dados Composto(TABLES e RECORDS PL/SQL). Ambas as composições são tipos definidos pelo usuário, para usálos, é necessário primeiro definir um tipo RECORD ou TABLE e depois declarar uma variável com o tipo que foi definido.

7.10.2.2.1 RECORDS Sintaxe:



79

TYPE record_type IS RECORD ( Campo1 type1 [NOT NULL] [:=exp1], Campo1 type2 [NOT NULL] [:=exp2], ... Campo1 type3 [NOT NULL] [:=exp3])

Ex: DECLARE TYPE T_EMP EMPNO ENAME JOB

IS RECORD ( NUMBER(4), VARCHAR2(10), VARCHAR2(9));

R_EMP T_EMP; BEGIN ... R_EMP.EMPNO := 1; ... END;

No exemplo acima T_EMP é declarado como um tipo RECORD com os campos EMPNO, ENAME, JOB. Depois R_EMP é declarado como o tipo T_EMP.

7.10.2.2.2 TABLE

Tables são similares a vetores, porém é implementado de uma maneira diferente pelo Oracle. Sintaxe TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER

Onde: TYPE : indica a declaração de um tipo definido pelo usuário. Tabletype : é o nome do tipo que está sendo criado. IS TABLE OF : significa que o tipo será do tipo TABLE. Type : Indica que será um tipo TABLE deste tipo. Podendo ser um tipo RECORD. INDEX BY BINARY_INTEGER : faz parte da sintaxe. Futuramente pode ser indexado por outros

tipos, porém na versão atual apenas por BINARY_INTEGER. Ex:

DECLARE TYPE T_NAME IS TABLE OF SCOTT.EMP.ENAME%TYPE INDEX BY BINARY_INTEGER; TYPE T_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER; V_NAME T_NAME; V_DATE T_DATE; BEGIN ... V_NAME(1) := ‘JONNAS’; V_DATE(1) := SYSDATE; ... END;

No exemplo acima são definido dois tipos T_NAME e T_DATE como sendo do tipo TABLE. As variáveis V_NAME e V_DATE são declaradas como T_NAME e T_DATE respectivamente. Ao longo do bloco V_NAME de índice 1 recebe ‘JONNAS’ e V_DATE de índice 1 recebe a data atual do sistema.



80

7.10.2.2.3 Atributos do tipo TABLE. Atributo Tipo Retornado

Descriçào

COUNT DELETE EXISTS FIRST LAST NEXT PRIOR

Retorna o número de linhas na tabela Deleta uma linha na tabela Retorna true se existe o índice na tabela Retorna o índice da primeira linha da tabela Retorna o índice da última linha da tabela Retorna o índice da próxima linha Retorna o índice da linha anterior

NUMBER N/A BOOLEAN BINARY_INTEGER BINARY_INTEGER BINARY_INTEGER BINARY_INTEGER

7.10.2.3 Usando %ROWTYPE Assim como o %TYPE, o %ROWTYPE fará com que a variável assuma a mesma estrutura de uma linha da tabela, ou seja, a variávela será um tipo RECORD que terá todos os campos da tabela que aparece na declaração. Ex: DECLARE R_DEPT SCOTT.DEPT%ROWTYPE ; BEGIN ... R_DEPT.DEPTNO := 1; ... END;

No exemplo acima R_DEPT é declarada com o mesma estrutura da tabela DEPT do schema SCOTT. Logo abaixo, o campo DEPTNO do RECORD R_DEPT recebe o valor 1.

7.10.3 Subtype Podem ser definidos pelo usuário e servem para colocar restrições opcionais aos tipos já existentes. Um subtipo deve ser antes baseado em um tipo já existente: Sintaxe: SUBTYPE nome_do_subtipo IS nome_do_tipo_base;

Ex.: DECLARE

--Baseado no Tipo escalar(Date) ou subtipo (Natural) padrão Subtype Subtype

EmpDate Contador

Is Date; Is Natural;

--Baseado no tipo varchar2 e restrição de tamanho máximo (50) AuxNome AuxNome Subtype

Varchar2(50); Nome

Is Aux_nome%Type;

--Subtipo predefinido Subtype

Character

Is Char;

--Baseado em um tipo tabela Type

NameTab

Subtype

EnameTab

--Baseado em um tipo Type

TpTime

Is TABLE OF Varchar2(10) INDEX BY BINARY INTEGER; IS NameTab;

registro Is RECORD


Apostila de Banco de Dados v.4.0 (minuto hora Momento

Subtype

81 Integer, Integer); Is TpTime;

--Baseado em uma coluna Subtype

TpCodigo

is Cliente.CdCliente%Type;

--Baseado no registro de um cursor Cursor Subtype

Cur-Pais Rec-Pais

is Select * From Pais; is Cur_Pais%Rowtype;

--Utilizando subtipos criados NmClienteAux ContAux Begin

Nome, Contador;

Null; End;

7.10.4 Atribuindo Valores às Variáveis Pode-se utilizar o operador (:=), ou a partir de um comando SELECT atribuir valores com o uso da cláusula INTO. Neste caso o comando deve retornar apenas uma linha. Ex: Select Qt_Produto, VI_Unitario Into From Where

Qtdd, Valor Item_Nota_Fiscal nr_nota = 1;

Total := Qtdd * Valor; Existe := (Conta < 1200);

7.10.5 Escopo de Variáveis Quando uma variável é definida dentro de um bloco, ela não é reconhecida fora do bloco, ou seja, ela é local para este bloco e global para os sub-blocos. Como um bloco somente pode incluir variáveis locais e globais, blocos isolados não poderão referenciar variáveis declaradas em outros blocos. Se uma variável global for declarada em um sub-bloco, a declaração local prevalecerá, Ex.: Declare Nm Produto VI_Custo Begin

Produto.Nm_Produto%Type; Number;

--Estão disponíveis VI_Custo de tipo number e Nm_Produto. Declare VI_Custo NVI_Custo Begin

Varchar2; Number;

_Custo de tipo varchar2, NVI Csto e Nm Produto. --Estão disponíveis Vl End;

--Estão disponíveis VI-Custo de tipo number e Nm Produto. End;



82

7.11 Codificação de Comando SQL Dentro de PL/SQL Comandos como INSERT, DELETE, UPDATE e SELECT e as funções (manipulações de strings, numéricas, de datas e genéricas), podem ser utilizados dentro de blocos PL/SQL. O comando SELECT receberá, obrigatoriamente, a cláusula INTO, pois o resultado do mesmo não poderá ser visualizado no momento de sua execução, e será armazenado em variáveis ou estruturas. O comando SELECT deve ser criado para que retorne somente uma linha selecionada. Caso nenhuma linha seja retornada ocorrerá um erro do tipo "no_data_found" e se mais de uma linha for retomada ocorrerá um erro do tipo "too_many_rows'. Ex.: Declare Nome Cliente.Nm_Cliente%Type; Codigo Cliente.Cd_Cliente%Type; Begin SELECT Nm_cliente, Cd_Cliente INTO Nome, Codigo FROM Cliente WHERE Cd_Cliente = &Cod; End;

7.12 Tratamento de Transações Comandos de terminação das transações: CONNECT/DISCONNECT Comandos DDL (que gere alterações no dicionário de dados Encerramento explícito por meio dos comandos COMMIT/ ROLLBACK. Savepoints: COMMIT - finaliza a transação efetivando as atualizações no banco, de forma que os demais usuários (sessões) consigam acessar essas alterações. ROLLBACK - finaliza a transação desfazendo todas as alterações feitas no banco durante a transação. ROLLBACK TO - desfaz as alterações realizadas no banco a partir da primeira instrução após o ponto especificado. Ou seja, permite desfazer apenas parte de uma transação. 0 ponto a partir do qual será desfeita a transação deve ter sido especificado com o comando SAVEPOINT. SAVEPOINT - permite a especificação de um ponto de processamento dentro de uma transação. Sintaxe COMMIT: ROLLBACK; SAVEPOINT Nome_do_Ponto; ROLLBACK TO Nome-do-Ponto; • • •

•

•

•

•

Ex.: Declare Nr_Nova_Nota Nota_Fiscal.Nr_Nota%Type; Begin -- insere dados nas tabelas Empresa e Filial Insert into Empresa (Cd_Empresa, Nm_Empresa) Values (3, 'Empresa teste 3'); Insert into Filial (Cd_Empresa, Cd_Filial, Nm_Filial) Values (3, 1, 'Filial I - BC'); SavepointLocal; -- insere dados nas tabelas Estado e município



83

Insert into Estado (Cd_Pais, Sg_EStado, Nm_Estado) Values (1, 'SP', 'São Paulo'); Insert into municipio (Cd_Pais, Sg_Estado, cd_Municipio, Nm_municipio) Values (1, ‘SP’, 10, 'Sorocaba'); Savepoint Pessoa; -- Insere dados na tabela Cliente Insert into Cliente (Cd_Cliente,Nm_Cliente,Ds-Endereco,Cd-Municipio,Sg_Estado,Nr_Cep, NR_DDD,Nr_Fone,le_Sexo,Ie_Fisica_juridica,Cd_Estado_Civil,Cd_Pais, Ie_Situacao) Values (50,'Fulano da Silva','Estrada Geral de Ida e Vinda', 10, 'Sp', '890103301’, 047, 2312331, 'M', 'S',1, 1,'A'); Savepoint Notas; -- Insere dados nas tabelas Nota_Fiscal e Item_Nota_Fiscal Select Nvl (Max (Nr_Nota) , 0) + 1 Into Nr_Nova_Nota From Nota_Fiscal; Insert into Nota_Fiscal (Nr_Nota, Cd_Cliente, Dt_Emissao, le_Tipo_Nota) Values (Nr_Nova_Nota, 50, sysdate, ‘C’); Insert into Item_Nota_Fiscal (Nr_Nota, Nr_ltem, Cd_Produto, Qt_Produto, VI_Unitario) Values (Nr_Nova_Nota, 1 , 1, 15, 3.25); Rollback to Pessoa; Commit; End,

7.13 Estruturas de Controle 7.13.1 IF – THEN – ELSE Sintaxe: IF expreção_booleana1 THEN Sequencia_de_comandos1; [ELSIF expreção_booleana2 THEN Sequencia_de_comandos2;] [ELSE Sequencia_de_comandos3;] END IF;

Ex: IF V_SALARIO < 100 THEN V_FUCIONARIO := ‘COITADO’; V_PRIVILEGIOS := 0; ELSIF V_SALARIO = 100 THEN V_FUNCIONARIO := ‘ESTA MELHORANDO’; V_PRIVILEGIOS := 0; ELSE V_PRIVILEGIO := 1; END IF;



84

7.13.2 WHILE-LOOP Sintaxe: WHILE condição LOOP Sequencia_de_comandos1; END LOOP;

Ex.: WHILE V_CONTADOR < 50 LOOP V_CONTADOR := V_CONTADOR +1; END LOOP ;

7.13.3 FOR-LOOP Sintaxe: FOR variável_contador IN [REVERSE ] valor_inicial..valor_final

LOOP Sequencia_de_comandos1;

END LOOP;

Ex: FOR V_CONTADOR IN 1..50 LOOP V_TESTE := V_CONTADOR; END LOOP;

7.13.4 LOOP Sintaxe LOOP Relação de comandos IF Condição_de_saida then EXIT

END LOOP;

7.14 Cursores: •

  

Guarda resultados de uma seleção em memória, permitindo a manipulação deste resultado de uma maneira procedural; deve ser declarado na área de declarações; o nome não pode ser igual ao da tabela; para dar um nome a uma coluna da seleção basta colocar o nome do alias logo após a definição da coluna.

Sintaxe: CURSOR nome_do_cursor IS SELECT ... [FOR UPDATE OF colunas]

Em que Relação_de_parâmetros pode Ter o seguinte formato: Nome_do_parâmetro tipo_de_dado {:= / DEFAULT} valor_inicial. Obs.: O tamanho do parâmetro não pode ser declarado, somente seu tipo. Ex.:



85

CURSOR Preferencia IS SELECT DISTINCT I.Cd_Produto, NM_Produto Nome FROM Item_Nota_Fiscal I, Nota_Fiscal N, Produto P WHERE N.Cd_Cliente = cliente AND N.Nr_Nota = I.Nr_Nota AND I.Cd_Produto = P.Cd_Produto;

7.14.1 Comandos de Manipulação do cursor: 7.14.1.1 Open: Cria numa área de memória conhecida como Private SQL Area, uma tabela com um ponteiro apontando para o primeiro registro. Parâmetros devem ser passados neste momento. Sintaxe: Open Preferencia ;

7.14.1.2 Fetch Transfere as linhas armazenadas no cursor, para variáveis, além de posicionar o ponteiro no próximo registro do cursor. A lista de variáveis que aparece na sintaxe do comando FETCH deve ter o mesmo número de variáveis, na mesma seqüência e com tipos correspondentes às colunas selecionadas no comando SELECT da declaração do cursor. Sintaxe: FETCH nome_do_cursor INTO lista_de_variáveis

Ex.1: Declare Cursor Preferencia IS Select Distinct

I.Cd_Produto, Rpad(P.Nm_Produto, 30) Nome

From Item_Nota_Fiscal I, Nota_Fiscal N, Produto P Where N.Cd Cllente = cliente N.Nr_Nota = I.Nr_Nota I.Cd_Produto = P.Cd_Produto; Codigo Produto.Cd_Produto%Type; Nome Produto.Nm_Produto%Type; Begin ... Fetch Preferencia Into Codigo, Nome; Dbms_Output.Put_Line ('Produto: ‘||Nome); ........ End; Ex. 2: Declare Cursor Preferencia (Cliente Number) IS Select Distinct I.Cd_Produto,



86

Rpad(P.Nm_Produto, 30) Nome From Item_Nota_Fiscal I, Nota_Fiscal N, Produto P Where N.Cd_Cliente = Cliente And N.Nr_Nota = I.Nr_Nota And I.Cd_Produto = P.Cd_Produto; Reg_Pref Preferencia %RowType; Begin ..... Fetch Preferencia Into Reg_Pref; Dbms_Output.Put_Line ('Produto: '|| Reg_Pref.Nome); ..... End; Para cada cursor, quatro atributos podem ser verificados a cada execução do comando FETCH:. Nome_do_cursor%FOUND - retorna TRUE caso o FETCH consiga retornar alguma linha e

FALSE, caso contrário. Se nenhum FETCH tiver sido executado será retornado NULL.

Nome_do_cursor%NOTFOUND - retorna FALSE caso o FETCH consiga retornar alguma linha e

TRUE, caso contrário. Se nenhum FETCH tiver sido executado será retornado NULL.

Nome_do_cursor%ROWCOUNT - retorna o número de linhas já processadas pelo cursor. Se

nenhum FETCH tiver sido executado será retornado 0 (zero)

Nome_do_cursor%ISOPEN - retorna TRUE caso o cursor esteja aberto e FALSE, caso contrário.

7.14.2 Close Libera a área de memória utilizada pelo cursor. Sintaxe: CLOSE nome_do_cursor

Ex.: CLOSE Preferencia; Exemplo Completo: Declare Cursor Preferencia (Cliente Number) IS Select Distinct I.Cd_Produto, Rpad(P.Nm_Produto, 30) Nome From Item_Nota_Fiscal I, Nota_Fiscal N. Produto P Where N.Cd_Cllente = Cliente And N.Nr_Nota = I.Nr_Nota And I.Cd_Produto = P.Cd_Produto; Reg_Pref Preferencia %Rowtype; Begin Open Preferencia (1); Loop Fetch Preferencia Into Reg_Pref; Exit When Preferencia%Notfound;



87

Dbms_output.Put_line (‘Produto: ‘||Reg_Pref.Nome); End Loop; Close Preferncia; End;

7.14.3 O Comando For para abrir Cursores:     

Cria a variável do tipo registro Abre o cursor Realiza a cópia das linhas (Fetch) Controle o final do cursor Fecha o mesmo

Para sair do laço, o cursor deve ser fechado explicitamente com o comando close. Sintaxe: FOR Nome_da_variavel_tipo_registro IN Nome_do_cursor (Lista_de_parametros) LOOP Relação_de_comandos END LOOP;

7.14.4 Atualização na tabela da linha atual do cursor: Declara-se o cursor como For Update e a atualização será feita com base no indicador de linha corrente (CURRENT OF). O ROWID do registro será carregado com os demais itens do cursor e no UPDATE, a comparação será feita internamente, com o ROWID. Sintaxe: CURSOR nome_do_cursor IS SELECT .... FOR UPDATE OF campos_a_atualizar UPDATE ... WHERE CURRENT OF nome_do_cursor

7.14.5 Cursores Implícitos: Atributos de cursores que podem ser verificados com significado e retorno: SQL%FOUND – retorna true se algum registro foi afetado ou se retornou algum registro;  SQL%NOTFOUND – retorna true se nenhum registro foi afetado.  SQL%ROWCOUNT – retorna numero de registros afetados ou a última quantidade de registros  afetados(deverá ser sempre 1) SQL%ISOPEN – sempre retornará false 



88

7.15 Tratamento de Exceções As exceções podem ser predefinidas ou definidas pelo usuário. Sintaxe: EXCEPTION WHEN nome_da_exceção THEN Relação_de_comandos WHEN nome_da_exceção THEN Relação_de_comandos

7.15.1 Exceções Predefinidas Este tipo de exceção é disparada implicitamente quando, no bloco PL/SQL, uma regra Oracle é violada ou um limite de sistema é excedido. Estas exceções podem ser identificadas por um nome e um número.             

 

CURSOR_ALREADY_OPEN (ORA-06511, SQLCODE -06511) ocorre quando se tenta abrir um cursor que já está aberto. DUP_VAL_ON_INDEX (ORA-00001, SQLCODE -00001) ocorre na tentativa de armazenar um valor duplicado em uma coluna de uma tabela que possui chave única ou primária. INVALID_CURSOR (ORA-01001, SQLCODE -01001) ocorre quando se tenta executar uma operação ilegal com um cursor, como fechar um cursor que não esteja aberto. INVALID_NUMBER (ORA-01722, SQLCODE -01722) ocorre na tentativa de converter uma string em número, caso a string não represente um número válido. LOGIN_DENIED (ORA-01017, SQLCODE -01017) ocorre na tentativa de conexão com o banco com um username/passvvord invalido. NO_DATA_FOUND (ORA-01403, SQLCODE -01403) ocorre quando um comando SELECT ... lNTO não retornar nenhuma linha. NOT LOGGED_ON (ORA-01012, SQLCODE -01012) ocorre na tentativa de acessar o banco de dados sem que se esteja conectado a ele. PROGRAM ERROR (ORA-06501, SQLCODE -06501) ocorre em caso de problemas internos do PL/SQL. ROWTYPE_MISMATCH (ORA-06504, SQLCODE -06504) ocorre se o retorno do cursor e a variável PL/SQL para retorno de um cursor sejam de tipos incompatíveis. STORAGE_ERROR (ORA-06500, SQLCODE -06500) ocorre se não houver memória suficiente para a execução de um bloco PL/. TIMEOUT_ON_RESOURCE (ORA-00051, SQLCODE -00051) ocorre quando acontecer um timeout enquanto o Oracle estiver aguardando um recurso. TOO_MANY ROWS (ORA-01422, SQLCODE -01422) ocorre quando um comando SELECT... INTO retornar mais de uma linha. VALUE-ERROR (ORA-06502, SQLCODE -06502) ocorre quando houver um erro aritmético, de conversão, truncagem ou tamanho, como quando um valor numérico for selecionado para dentro de uma variável caracter, ou o valor for maior do que o declarado para a variável. ZERO-DIVIDE (ORA-01476, SQLCODE -01476) ocorre na tentativa de dividir qualquer número por zero. OTHERS permite tratar outros erros, com a ajuda das funções SQLCODE e SQLERRM, que retorna o número do erro Oracle e o texto da mensagem de erro, respectivamente.

Variáveis que possuam restrição de limites (inferior e superior) pela definição de tipo ou subtipo dispararão restrição de VALUE-ERROR caso estes limites sejam ultrapassados.



89

7.15.2 Exceções Definidas pelo Usuário Precisam ser declaradas e chamadas explicitamente pelo comando RAISE. Somente podem ser declaradas na área de declarações de um bloco PL/SQL, subprograma ou package. Sintaxe: Declare Nome_da_exceção EXCEPTION; Begin Relação_de_comandos If ........then RAISE Nome_da_exceção; End if; Relação_de_comandos Exception WHEN Nome_da_exceção THEN Relação_de_comandos End;

7.15.2.1 Utilizando OTHERS ou PRAGMA EXCEPTION_INIT Um pragma renomeia um erro oracle para o compilador, permitindo escrever um tratamento específico, sem que uma exception seja explicitamente chamada. Os pragmas são chamados em tempo de compilação e não afetam o significado do programa. Deve ser declarado na área de declarações de um bloco, subprograma ou package e precisa Ter o nome da exceção previamente declarado. Sintaxe: Declare Nome_da_exceção Exception; Pragma Exception_Init (nome_da_exceção, código_erro); Begin Relação de comandos Exception When Nome_da_exceção then Relação_de_comandos End;

7.15.2.2 Raise_Application_Error É uma procedure que permite a emissão de mensagens de erro, definidas pelo usuário. Os erros podem ser relatados e evita-se o retorno de exceções não tratadas. Sintaxe: RAISE_APPLICATION_ERROR (código_erro, ‘texto’)



7.15.2.3

90

DBMS_output.put_line()

Coloca uma linha no buffer, e mostra na tela. SQL> set serveroutput on; SQL> exec dbms_output.put_line(sysdate); 10-MAR-01

7.16 Subprogramas (Procedures e Functions) São blocos PL/SQL, armazenados na base de dados e chamados sempre que necessários. O nome de um subprograma pode ter, no máximo, 30 caracteres. No momento de sua criação pode ser incluído o parâmetro OR REPLACE, o qual substituirá um subprograma já existente por uma nova versão. As vantagens dessa opção é manter os privilégios existentes, criar mesmo que haja erro de sintaxe, marcar objetos dependentes para compilação. Para que um subprograma seja criado como Público utilizam-se os seguintes comandos: GRANT EXECUTE ON Nome_do_Subprograma TO PUBLIC; CREATE PUBLIC SYNONYM Nome_do_Subprograma FOR Sinônimo_Público_do_Subprograma;

Informações dos subprogramas são armazenadas na tabela USER_OBJECTS, a qual pode ser acessada pelo usuário para obter dados como nome, tipo, data de criação, data de compilação, etc. Já o texto das procedures e functions encontram-se na tabela USER_SOURCE. Exemplo de como obter o código de um subprograma:

SELECT TEXT FROM USER_SOURCE WHERE NAME = ‘Nome_do_subprograma em maiúsculo’ ORDER BY LINE; A lista de argumentos de um subprograma é obtida através do comando DESCRIBE. Sintaxe: DESCRIBE nome_da_procedure / function

Os erros de compilação podem ser obtidos de duas formas: Ex.1: SELECT LINE, POSITION, TEXT FROM USER_ERRORS WHERE NAME = ‘Nome_subprograma em maiúsculo’ ORDER BY LINE Ou Ex.2: SHOW ERRORS PROCEDURE Nome_subprograma;



91

7.16.1 Parâmetros Podem ser de entrada, saída ou entrada/Saída: IN (padrão) - passa um valor do ambiente chamador para o subprograma e este valor não  pode ser alterado dentro do subprograma. OUT- passa um valor do subprograma para o ambiente chamador.  IN OUT- passa um valor do ambiente chamador para o suprograma; este valor pode ser  alterado dentro do subprograma e retornado com o valor atualizado para o ambiente chamador.

7.16.2 Procedures CREATE OR REPLACE PROCEDURE Nome_Proc [(Argumento[{IN | OUT | IN OUT}] tipo, ....... Argumento [{IN | OUT | IN OUT}] tipo] {IS | AS} Corpo_procedimento

Onde, nome_procedimento é como se chama o procedimento, argumento é o nome de um parâmetro de procedimento, tipo é o tipo do parâmetro associado e corpo_procedimento é um bloco de PL/SQL que constitui o código do procedimento.

7.16.3 Functions: Retornam resultado ou valor. Podem ser utilizadas em atribuições a variáveis ou como argumento em comando Select. CREATE OR REPLACE FUNCTION Nome_função [(Argumento[{IN | OUT | IN OUT}] tipo, ....... Argumento [{IN | OUT | IN OUT}] tipo] RETURN tipo_retorno {IS | AS} Corpo_função

Tipo_retorno é o tipo do valor que a função devolve.

7.16.4 Executando subprogramas através do SQL* Plus: EXECUTE Nome_Procedure(Lista_de_parâmetros) SELECT Nome_Função(Lista_de_parâmetros) FROM DUAL

7.16.5 Eliminando um subprograma: DROP PROCEDURE / FUNCTION

nome_do_Procedimento / nome_da_função

7.16.6 Análise das dependências: Um subprograma pode depender diretamente de tabelas, visões, seqüências e outros subprogramas. Pode ainda possuir dependências indiretas de outros objetos. Por exemplo, se uma procedure A depender diretamente de uma function B, que depende diretamente de uma tabela C, então a procedure A dependerá indiretamente da tabela C. Quando um objeto com o qual o subprograma possui uma relação de dependência for modificado, o status do subprograma na tabela USER_OBJECT ficará como INVALID.



92

Recompilando um subprograma que esteja como o status INVALID: ALTER PROCEDURES / FUNCTION nome_da_procedure / nome_da_function COMPILE

Relações de dependência dos objetos podem ser encontradas na tabela USER_DEPENDENCIES. Exemplo de como verificar os objetos que dependem de um outro objeto: SELECT NAME, TYPE FROM USER_DEPENDENCIES WHERE REFERENCED_NAME = ‘Nome_Objeto_que_se_quer_alterar’;

7.17 Packages São objetos equivalentes a bibliotecas que guardam Procedures, Functions, definições de cursores, variáveis e constantes, definições de exceções.

7.17.1 Especificação Na área de especificação são feitas as declarações públicas (variáveis, constantes, cursores, exceções e subprogramas que estarão disponíveis para uso externo à package. Sintaxe: CREATE OR REPLACE PACKAGE

Nome_da_package IS

Para procedures e functions só os cabeçalhos (interface) PROCEDURE Nome_da_Procedure (Lista_de_Parâmetros); FUNCTION Nome_da_Function (lista_de_Parâmetros Declaração de variáveis, constantes, exceções e cursores públicos END Nome_da_Package;

7.17.2 Body No corpo são feitas as declarações privadas, que estarão disponíveis internamente à package Sintaxe: CREATE OR REPLACE PACKAGE BODY Nome_da_Package IS Declaração de variáveis, constantes, exceções e cursores privados PROCEDURE Nome_da_Procedure (Lista_de_Parâmetros) IS BEGIN END; FUNCTION Nome_da_Function (Lista_de_Parâmetros) RETURN tipo BEGIN RETURN END; END;

Para eliminar uma Package, utiliza-se DROP PACKAGE e DROP PACKAGE BODY.

A lista de erros de uma package é obtida através do comando: SHOW ERRORS PACKAGE Nome_da_Package SHOW ERRORS PACKAGE BODY Nome_da_Package



93

7.17.3 Execução de Estruturas Públicas de uma Package: Subprogramas podem ser executados em vários ambientes: EXECUTE Nome_da_Package.Nome_procedures(Lista_de_Parâmetros) SELECT Nome_da_Package.Nome_Função(Lista_de_Parâmetros) FROM DUAL. Recompilando Packages:

ALTER PACKAGE nome_da_Package COMPILE PACKAGE --compila a especificação e o corpo ALTER PACKAGE nome_da_Package COMPILE PACKAGE ESPECIFICATION --a especificação ALTER PACKAGE nome_da_Package COMPILE PACKAGE BODY --compila o body

7.18 Triggers São disparados implicitamente quando ocorrem eventos (INSERT, UPDATE, DELETE) em uma tabela. O Trigger deve estar obrigatoriamente associado a uma tabela. Sintaxe: CREATE OR REPLACE TRIGGER nome_da_trigger {BEFORE / AFTER} DELETE OR INSERT OR UPDATE OF (nome_coluna1, nome_tabela REFERENCING [OLD as antigo] [NEW as novo] FOR EACH ROW WHEN condição DECLARE Variáveis, constantes, etc. BEGIN ..... END ;

nome_coluna2,

....)

ON

Os tempos de uma trigger podem ser: BEFORE – disparada antes do evento  AFTER – disparada depois do evento  A trigguer pode ser disparada pelos eventos INSERT, UPDATE e DELETE. No uso do UPADATE, as colunas devem ser especificadas após a palavra OF. Uma trigger pode ainda ser do tipo COMANDO, que manipula dados dentro de uma tabela e executa uma única vez; ou do tipo LINHA, que manipula linhas de uma tabela e pode ser executada uma ou mais vezes.

7.18.1 Criação de triggers São permitidas até 12 triggers para cada tabela, incluindo todas as combinações possíveis entre tempos, eventos de disparo e tipos de triguer. Também não são permitidas triggers com o mesmo tempo, evento de disparo e tipo para uma mesma tabela.

7.18.2 Triggers possíveis para uma tabela: BEFORE UPDATE Linha BEFORE UPDATE comando BEFORE DELETE linha BEFORE DELETE comando BEFORE INSERT linha BEFORE INSERT comando AFTER UPDATE linha Prof. Flavio Rezende


94

AFTER UPDATE comando AFTER DELETE linha AFTER DELETE comando AFTER INSERT linha AFTER INSERT comando Não se permite no corpo de uma trigger o uso de comandos COMMIT e ROLLBACK. Também não podem ser alteradas chaves primárias, únicas ou estrangeiras.

O comando DROP TRIGGER serve para eliminá-la. Podem ainda se habilitadas e desabilitadas: ALTER TRIGGER nome_da_trigger ENABLE ALTER TRIGGUER nome_da_trigger DISABLE ALTER TABLE nome_da_tabela ENABLE ALL_TRIGGERS (habilita todas de uma tabela) ALTER TABBLE nome_da_tabela DISABLE ALL_TRIGGERS (desabilita todas de uma tabela). As informações sobre triggers são encontradas na tabela USER_TRIGGERS.



95

Na hora da definição da precisão do campo devemos ficar atentos para não economizar demais a ponto de definir o campo matricula do funcionário como NUMBER(2) e a empresa fica limitada a ter no máximo 99 funcionários, e nem gastar demais, pois sempre é bom lembrar que quanto menor o tamanho da linha, mais linhas caberão em um bloco, e quanto mais linhas couberem em um bloco, mais linhas irão para a memória a cada leitura do HD e menos espaço em disco irá ocupar. EX. de criação de uma tabela. CREATE TABLE FUNCIONARIO( MATRICULA NUMBER(3) NOT NULL , NOME VARCHAR(2) NOT NULL ID_DEPTO NUMERIC (2) NOT NULL) STORAGE( INITIAL 5M NEXT 10M PCTINCREASE 50 MINEXTENTS 2 MAXEXTENTS 200) TASBLESPACE USER;

STORAGE: INITIAL : Tamanho inicial da tabela. NEXT : Indica o tamanho que a tabela vai crescer quando ficar cheia. PCTINCREASE : A cada vez que a tabela crescer ela vai crescer o tamanho da clausula NEXT mais esta porcentagem. MINEXTENTS : número mínimo de extensões de uma tabela. MAXEXTENTS : número máximo de extensões em uma tabela. TABLESPACE : Nome da área que a tabela vai ficar. Esta área está associada a um arquivo físico.

Índices: Índices são estruturas auxiliares que estão sempre ligados a uma tabela. Servem para agilizar as consultas diretas e indiretas. Devemos tomar certos cuidados ao criar índices, pois quanto mais índices em uma tabela, maior será o tempo de insert, update e delete de uma tabela, sem contar que aumenta o consumo de disco. Quando criar índices: Quando temos consultas constantes por um determinado campo da tabela que retorne menos que 5% do tamanho total da tabela, como por exemplo o campo NOME da tabela de CLIENTES (consultas diretas).



96

Quando temos uma tabela muito grande que possui uma FK para uma outra tabela que sofre constantes deleções (consultas indiretas) DOCUMENTO NUMERO DATA VALOR 01 01/01/1996 200,00 ... ... ... 200000 01/01/1999 100,00 LANCAMENTO_CONTABIL NUM_LANC DOCUMENTO(FK) 01 01 02 01 ... ... 01 02 ..

200000 200000 ...

DATA VALOR 01/01/1996 -200,00 01/01/1996 200,00 ... ... ... 01/01/1999 -100,00 01/01/1999 100,00 ... ...

Repare que a coluna DOCUMENTO da tabela de LANCAMENTO_CONTABIL é uma FK para a tabela de DOCUMENTO. Quando o usuário quiser excluir uma linha na tabela DOCUMENTO o SGBD terá que verificar se existe algum lancamento contábil para este documento, e se não houver um índice para a coluna de DOCUMENTO o SGBD fará uma leitura da tabela inteira. OBS. Sempre que se cria uma PK em uma tabela, o SGBD cria automaticamente um índice único para a coluna ou colunas que formam a PK. Neste caso a ordem de como se cria a PK também influencia, pois se na tabela de LANCAMENTO_CONTABIL fosse criado DOCUMENTO,NUM_LANC , não precisaria criar um índice para DOCUMENTO, pois o SGBD poderia aproveitar o próprio índice criado da chave primária. Criando Índices para implementar cardinalidades 1:1. Ex. TABELA1(COLUNA1,COLUNA2,COLUNA3) TABELA2 (COLUNA1, COLUNA2,COLUNA3) Digamos que a a TABELA1 possui um relacionamento de 1:1 com a TABELA2. A COLUNA3 da TABELA2 possui uma FK para a TABELA1, desta maneira está implementado um relacionamento 1:N , para implementar um relacionamento 1:1 devemos criar um índice único para a FK, para garantir que a os valores da COLUNA3 não se repitirão. Sintaxe para a criação de Índice. CREATE [UNIQUE] INDEX index_name ON table_name (COLUNA [{ASC | DESC}]) [TABLESPACE tablespace_name] [STORAGE( INITIAL 5M NEXT 10M



97

PCTINCREASE 50 MINEXTENTS 2 MAXEXTENTS 200)] A cláusula STORAGE é igual a da TABELA.



98

7.19 Criação de Tabelas Toda a tabela criada no banco de dados ela tem que possuir um o wner (dono), que é exatamente o usuário que cria a tabela. Toda tabela no momento da criação, não devem possuir foreign keys, as foreign keys devem ser incluidas no final da criação de todas as tabelas, pois se no momento da criação de uma tabela ela referenciar outra tabela que ainda não foi criada, o SGBD retornará uma mensagem de erro e não criará a tabela. Ex: CREATE TABLE ALUNOS (MATRICULA VARCHAR2 (12) NOT NULL , NOME VARCHAR2 (40) NOT NULL , ID_CURSO NUMBER (2) NOT NULL ); ALTER TABLE ALUNOS ADD CONSTRAINT ALUNOS_PK PRIMARY KEY (MATRICULA); CREATE TABLE CURSOS (ID_CURSO NOME

NUMBER (2) NOT NULL , VARCHAR2 (40) NOT NULL);

ALTER TABLE CURSOS ADD CONSTRAINT CURSOS_PK PRIMARY KEY (ID_CURSO); . . . ALTER TABLE ALUNOS ADD CONSTRAINT ALUNOS_CURSOS_FK FOREIGN KEY (ID_CURSO) REFERENCES CURSOS (ID_CURSO);

7.20 Criação de Views Para que tenha uma camada lógica entre a aplicação e os dados, ou para reduzir a visão dos usuários aos dados, é necessário que os acessos sejam feitos as views. CREATE VIEW ALUNOS_V00 AS SELECT * FROM ALUNOS; CREATE VIEW CURSOS_V00 AS SELECT * FROM CURSOS;



99

7.21 Criação de Stored Procedures : Stored procedures é um procedimento que quando chamado é executado no servidor, pelo próprio SGBD. Escrito em uma linguagem do próprio SGBD (no caso do Oracle 8: PL\SQL e J ava). Sintaxe: CREATE [OR REPLACE] PROCEDURE procedure_name [(parametro1 [{IN|OUT|IN OUT}] type, ... parametro2 [{IN|OUT|IN OUT}] type)] AS declarative section BEGIN executable section . . . EXCEPTION exception section END procedure_name ; Exemplo: CREATE OR REPLACE PROCEDURE INSERIR_ALUNO( P_MATRICULA IN ALUNOS.MATRICULA%TYPE, P_NOME IN ALUNOS.NOME%TYPE, P_ID_CURSO IN ALUNOS.ID_CURSO%TYPE ) AS BEGIN INSERT INTO ALUNOS(MATRICULA , NOME , ID_CURSO) VALUES (P_MATRICULA ,P_NOME,P_ID_CURSO); END INSERIR_ALUNO ;

7.22 Criação de Functions : As funções podem ser usadas inclusive na clausula SELECT. Por exemplo eu poderia ter uma função que ao passar a matrícula de um aluno, a função retorne o nome do aluno. Desta maneira eu poderia fazer um SELECT apenas na tabela de MATRICULA e retornar o nome do aluno, sem precisar ir na tabela ALUNOS. Sintaxe: CREATE [OR REPLACE] FUNCTION funcion_name [(parametro1 [{IN|OUT|IN OUT}] type, ... parametro2 [{IN|OUT|IN OUT}] type)] RETURN return_type {IS|AS} BEGIN .. END function_name ; Exemplo: CREATE OR REPLACE FUNCTION NOME_ALUNO ( P_MATRICULA IN ALUNOS.MATRICULA%TYPE) RETURN ALUNOS.NOME%TYPE



100

IS V_NOME ALUNOS.NOME%TYPE; BEGIN SELECT NOME INTO V_NOME FROM ALUNOS WHERE MATRICULA = P_MATRICULA; RETURN V_NOME; END NOME_ALUNO;

7.23 Criação de triggers : Trigger é um procedimento que é executado automaticamente pelo SGBD, de acordo com um evento em uma tabela(insert , delete , update). Este procedimento é executado no servidor. Sintaxe : CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} [INSERT|DELETE|UPDATE] ON table_name [FOR EACH ROW [WHEN trigger_condition]] BEGIN . . . END triger_name ; Exemplo: CREATE OR REPLACE TRIGGER MATRICULA_I BEFORE INSERT OR UPDATE ON MATRICULA FOR EACH ROW DECLARE V_NUM_LINHAS NUMBER ; BEGIN SELECT COUNT(*) INTO V_NUM_LINHAS FROM DBO.ALUNOS A, DBO.CURSOS C, DBO.CURRICULO R WHERE A.ID_CURSO = C.ID_CURSO AND C.ID_CURSO = R.ID_CURSO AND A.MATRICULA = :NEW.MATRICULA AND R.ID_DISCIPLINA = :NEW.ID_DISCIPLINA; IF V_NUM_LINHAS = 0 THEN RAISE_APPLICATION_ERROR (-20001 , 'DISCIPLINA NAO FAZ PARTE DO CURRICULO'); END IF; END MATRICULA_I;



8

101

Privilégios.

8.1 Privilégios Os privilégios de sistemas são dados a um usuário para permitir que o usuário execute atividades no Banco , como criar tabelas , criar índices , criar uma sessão , criar um objeto procedural , entre outros . Já os privilégios de objeto , como o próprio nome diz , são relativos a ações que um usuário poderá fazer em um objeto de outro usuário , como selecionar , inserir linhas , deletar linhas , atualizar linhas , executar um procedimento. Os privilégios são dados através do comando grant , e revogados através do comando revoke , aproveitando o exemplo acima , podemos dizer que dois usuário foram criados , aluno e professor , ambos precisam ter o privilégio de criar sessões e criar tabelas para poderem criar suas tabelas e conectarem-se ao banco , além disso , pela desejamos que somente os professores vejam os dados da tabela veículos do aluno, então , o usuário aluno deverá fornecer ao usuário professor o privilégio de selecionar a tabela veículos.

Sintaxe: GRANT privilégio TO [user_name|role] Onde:

É o privilégios que estamos querendo atribuir. Podemos atribuir vários privilégios em um só comando, basta separa-los por vírgula. [user_name|role] : Nome do usuário ou grupo de usuários que receberá o privilégio. Privilégio :

REVOKE privilégio FROM [user_name|role] Onde:

Privilégio : É o privilégios que estamos querendo revogar. [user_name|role] : Nome do usuário ou grupo de usuários que terá o privilégio revogado.

8.2 Roles Quando falamos em conceder permissões sobre objetos a nível de sistema , falamos em algo como 80 tipos diferentes de privilégios , sendo que destes , vários são dados em conjunto a um usuário específico ou a um perfil de usuários. Com a finalidade de facilitar a administração , podemos definir as roles como agrupamentos de privilégios , tanto de sistema quanto de objeto , que tem por objetivo gerar perfis de privilégios que podem ser associados a usuários ou a outras roles , facilitando a manutenção (Figura 16).



102

Privilégios

Role

Usuário

Create table

R_estacio R_Estacio

Aluno1 Aluno2 Aluno3 Aluno4 Aluno5

Create Session

. . .

Aluno98 Aluno99

8.2.1 Criando uma ROLE Sintaxe: CREATE ROLE ROLE_NAME;

8.2.2 Atribuindo uma ROLE para um usuátio Sintaxe: GRANT ROLE_NAME TO USERNAME

8.2.3 Atribuindo uma ROLE para outra ROLE Sintaxe : GRANT ROLE_NAME TO ROLE_NAME

Exemplo de Implementação

Por questão de segurança contra usuários mal intencionados ou simplesmente usuários que não sabem direito o que fazem , devemos deixar os usuários conseguir acessarem exatamente o que eles devem acessar. Para isto devemos fazer uma matriz de objetos x perfil de usuário, para identificar exatamente quais os privilégios a serem dado a cada perfil de usuário. Não é conveniente dar privilégios de acesso diretamente as tabelas, por questão de manutenibilidade e flexibilidade em momentos críticos. Os acessos devem ser feitos sempre a views. É conveniente que as alterações na base de dados seja feita sempre atravéz de stored procedure. Isto elimina uma boa parte de inconsistências na base de dados, tendo em vista que os acessos serão pré-definidos dentro das procedures. Garante a implementação de restrições de integridade, e ainda tem um ganho de performance, tendo em vista que os comandos já ficam précompilados. É claro que muitas vezes por questão de prazo de projeto nem sempre há tempo para se implementar uma solução deste tipo. Sendo assim, os acessos de consulta devem ser sempre a views, e as alterações sempre por stored procedure. 1. Identificar quais diferentes perfis terá o sistema. 2. Identificar os privilégios de cada perfil (matriz objetos x perfil de usuário). 3. Identificar em qual pefil cada usuário se encaixa.



103

Vejamos um exemplo de matriz objetos x perfil de usuário. V C C E O O N N M G D T P E A A R R S B A E I N L C I I D A A D E VENDAS_V00

X

X

X

ITENSVENDA_V00

X

X

X

FORNECEDORES_V00

X

X

FORNECIMENTO_V00

X

X

PRODUTOS_V00

X

X

X

Existem dois comandos básicos para gerenciamento de privilégios:

GRANT : Atribui um privilégio SINTAXE: GRANT privilégio ON nome_objeto TO usuario ou perfil Ex: GRANT SELECT ON VENDAS_V00 TO GERENCIA;

REVOKE : Retira um privilégio REVOKE privilégio ON nome_objeto FROM usuario ou perfil Ex: REVOKE SELECT ON VENDAS FROM GERENCIA;



9

104

Exercícios

9.1 Modelo Conceitual e Lógico Faça o modelo de entidades e relacionamentos para os caso: abaixo, especificando todas as Restrições de Integridade caso necessário. 1) Um diretor de cinema deseja saber quais os filmes que ele já fez e quais os atores que trabalharam em cada filme. 2) Uma empresa deseja saber quais os seus departamentos e quais os funcionários que neles estão alocados. Sabendo-se que um funcionário tem que estar alocado a um e somente um departamento, e um departamento pode Ter vários funcionários quanto nenhum. 3) Uma empresa deseja saber quanto e quais os produtos seus vendedores vendem. Podendo saber qual foi o vendedor responsável por uma determinada venda. 4) Uma empresa deseja acompanhar os preços de cada produto que seus fornecedores fornecem, através dos pedidos de compra já enviados para os fornecedores. 5) Uma empresa deseja guardar informações sobre seus clientes e que produtos eles compram. Guardando também informações de todos os produtos que um cliente comprou em uma determinada nota fiscal. 6) Considerando os exercícios 2,3,4 e 5 como se fosse a mesma empresa. Faça o modelo completo para atender a esta empresa.



105

7) Farmácia Farma Vida Uma rede de farmácias deseja colocar seus remédios disponíveis para venda na internet. Cada remédio deve ser identificado por um código que depende do laboratório que o fornece. Cada Laboratório deve ser identificado pelo seu cgc. Cada remédio só pode ser fornecido por um e apenas um laboratório. A venda é identificada unicamente pela nota fiscal, e deve conter a data da venda, o status da venda e qual o funcionário fez a entrega. Cada venda deve possuir vários itens de venda, sendo que em cada item deve possuir o remédio, o preço do remédio e a quantidade. O cliente deve ser identificado pelo cpf e deve ser guardado no sistema o nome, e-mail e endereço do cliente. A venda é paga a vista e em dinheiro no momento da entrega do remédio. A entrega do remédio é feita apenas por um funcionário que é identificado pela sua matrícula.



106

8) TV a Cabo NetCat Uma empresa de TV a cabo deseja automatizar seu serviço de cobrança. Para isto todos os requisitos abaixo devem ser preenchidos. Um Canal deve ser identificado por seu número, e deve conter o nome do canal. Um pacote é um conjunto de canais que é disponibilizado ao cliente mediante uma assinatura. Cada pacote criado pela empresa, tem que conter no mínimo um canal, ou vários e um canal só pode pertencer a um e somente um pacote. O pacote deve possuir como informações o nome do pacote, e o preço além dos canais que ele contém. Para um cliente ser cadastrado ele precisa fazer uma assinatura de ao menos um pacote. Para um cliente deve ser guardado o cpf, nome, endereço, e o seu e_mail. Um cliente deve ser identificado pelo seu cpf . Uma assinatura deve possuir um e somente um cliente, um ou vários pacotes, data de ativação da assinatura, data de desativação da assinatura e o endereço de instalação.



107

9) Supermercado pela Internet

Uma rede de Supermercados deseja disponibilizar seus produtos para serem vendidos na internet, e deseja ter um sistema para controlar os itens abaixo. Fornecimento: Cada pedido deve ser feito a um fornecedor. Cada pedido deve ter um item de pedido ou mais, onde cada item de pedido deve guardar o produto , o preço do fornecedor e a quantidade. Para que haja um melhor controle sobre os fornecedores, deve existir um cadastro de fornecedores e os produtos que eles fornecem. Um fornecedor deve constar no cadastro, mesmo que ele nunca tenha fornecido nenhum produto. Venda de Produtos: Uma venda só pode ser realizada caso o cliente já seja cadastrado, sendo assim , caso o cliente não exista deve ser recolhido os dados cadastrais do cliente, que deve ser inserido antes de fazer a compra. Desta maneira o ciente só poderá fazer qualquer operação caso ele forneça o seu usuário e senha. Caso o usuário já exista e ele tenha esquecido sua senha, ele deve poder se cadastrar novamente, porém ao invés de inserir um novo cliente, os seus dados devem ser apenas atualizados. Uma vez que o cliente forneça seu usuário e senha para usar o sistema, aparecerá para ele uma lista com todas as seções do supermercado, o cliente deve escolher uma determinada seção e aparecerá todas as gôndolas da seção, o cliente deve então selecionar uma gôndola onde será disponibilizado para ele todos os produtos da gôndola. O cliente deve poder comprar diversos produtos em apenas uma compra. Ao término da compra o cliente deve fornecer o número e a bandeira do cartão de crédito que ele usará para pagar a compra feita e receberá via e_mail a lista de todos os produtos que ele comprou contendo o número da nota fiscal que identificará sua compra. A cada produto selecionado pelo usuário, deve ser atualizado na quantidade em estoque do produto. Cartões de Crédito : Para uma empresa aceitar uma determinada bandeira(Visa , CrediCard , Amex , etc...) de cartão de crédito, ela deve se cadastrar junto a bandeira, primeiramente ela deve possuir uma conta em algum banco com que a bandeira trabalha. Possuindo a conta, a loja recebe uma identificação(para uso da bandeira do Cartão de Crédito) assim como uma máquina onde será digitado o número do cartão e o valor da compra. A restituição do dinheiro para a empresa será feito em um determinado dia do mês, que é especificado quais as compras foram restituídas. Cada cartão ganha um percentual em cima de cada transação que é feita, podendo este percentual variar de bandeira para bandeira, por exemplo: se um cliente faz uma compra de 100 reais com um cartão Visa, digamos que o Visa trabalha com um percentual de 5%, então o Visa irá depositar na conta da empresa apenas 95 reais referentes a esta transação. Cada Bandeira trabalha apenas com determinados Bancos.

Produtos: O cadastro de produto deve conter o nome do produto, seu preço unitário e a quantidade de unidade de medida em que é vendido. Cada produto deve possuir uma unidade de medida (ml , Kg , Litros e etc..) ex. um Iogurte da XPTO é vendido em uma garrafa de 200ml, o '200' é a quantidade e o 'ml' a unidade de medida. Cada produto deve estar em uma gôndola e cada gôndola deve se localizar em uma seção.



108

10) Hospital SafeLife O Hospital SafeLife deseja desenvolver um sistema para controlar das internações de seus pacientes. O Hospital mantém convênio com vários planos de saúde e atende a dois tipos de pacientes: pacientes conveniados (pacientes que possui plano de saúde das instituições conveniadas) e pacientes particulares pacientes que não possuem plano de saúde das instituições conveniadas) Os pacientes conveniados podem possuir mais de um plano de saúde. De todos os pacientes é necessário saber o seu nome, endereço e telefone. Dos pacientes particulares é importante conhecer a sua renda mensal. O Hospital possui várias enfermarias, cada uma com vários leito para internação. As enfermarias possuem nome e localização e os leitos apenas o número. Quando um paciente é internado (conveniado ou particular), o Hospital registra o leito ocupado pelo paciente, a data da internação e da alta do paciente. As internações dos pacientes conveniados são custeado por um único plano de saúde, dentre os que o paciente é associado.



109

11) Curso Bom Aluno No Curso Bom Aluno todos os professores são lotados em um único Departamento. Cada Departamento, identificado por um nome, é sempre chefiado por um de seus professores. Dos professores é importante saber o seu nome, endereço e titulação. Os professores podem ministrar mais de u ma disciplina (eventualmente, um professor pode não ministrar nenhuma disciplina), mas toda disciplina é ministrada por um único professor. Das disciplinas é importante conhecer o nome e carga horária. As disciplinas são cursadas pelos alunos do curso, que possuem nome, endereço e número de matrícula. Um aluno pode não estar cursando nenhuma disciplina ou no mínimo duas e no máximo 4, Os alunos são avaliados mediante a realização de provas, que são realizadas em datas e salas previamente definidas (a prova de uma disciplina é realizada em uma única sala), O sistema deve registrar o grau obtido pelos alunos, em cada prova. As salas, onde as provas são realizadas, pertencem a três diferentes edifícios. Cada edifício possui um nome e as salas um número.



110

12) Rede de Hotéis A rede de hotéis Cínco Estrelas deseja desenvolver um sistema para controle de reservas e hospedagens. A rede possui hotéis em várias cidades do país. Em algumas cidades, a rede possui mais de um hotel. Dos hotéis é importante saber o seu nome, endereço, valor da diária e a sua classificação (número de estrelas). Além disso, para que os hotéis possam ser univocamente identificados, no sistema atual eles possuem um código numérico. Os hotéis possuem um número variado de apartamentos, sendo todos iguais. Os apartamentos são sempre identificados por um número sequencial (1 O 1, 102 ... 201, 202 ... ). A rede de hotéis possui uma central onde são ofetuadas as reservas para todos os seus hotéis. Ao reservar um apartamento, a central registra a data prevista da chegada e da saída do hóspede. É comum um hóspede reservar de mais de um apartamento para uma mesma época. Quando o hotel recebe um hóspede para hospedagem, ele informa a Central de Reservas a data do inicio da hospedagem e a data prevista da saída do hóspede. O término da hospedagem é informado por ocasião da saída do hóspede do hotel. É comum ocorrerem hospedagens sem reserva prévia e um mesmo hóspede pode ocupar, simultaneamente, mais de um apartamento. A central possui um cadastro de todos os seus hóspedes contendo o nome, endereço e telefone. Alguns hóspedes, considerados especiais, recebem um desconto variado sobre o valor da diária.



111

13) CIA. Aérea Ao viajar de avião, é costume o passageiro efetuar reserva em um vôo antes da sua chegada ao aeroporto. Considere uma Cia. Aérea Hipotética e um passageiro viajando de uma cidade para outra. O passageiro solicita os serviços de um agente de viagem, que telefona para a Central de Reservas da Cia. para efetuar as reservas. O atendente da Central de Reservas solicita os detalhes do itinerário do passageiro - ele deseja ir de Recife para o Rio de Janeiro, onde participará de um -.urso que começará às 09.00h da segunda-feira, 16 de junho. O atendente faz uma consulta ao sistema de informações de vôos da Cia, e descobre que existe um vôo chegando às 20:30h do domingo, 15 de julho. O agente de viagem diz que este vôo atende ao passageiro e o atendente volta a consultar o sistema para verificar a existência de lugares vagos. O vôo escolhido esta com a classe executiva lotada, mas existe um vôo mais cedo com lugares vagos, que faz escala em Salvador e chega ao Rio às 21:00h. Como o passageiro dá preferência ao primeiro vôo, seu nome é incluído na lista de espera deste vôo, além de ter lugar confirmado no vôo mais cedo, O agente relaciona as duas reservas, para que o lugar confirmado possa ser liberado, caso o da lista de espera venha a ser confirmado. O passageiro fornece os detalhes adicionais da reserva, tais como janela ou corredor, fumante ou não fumante, cardápio de carne ou vegetais e, então, a reserva é concluída. O bilhete de passagem é emitido duas semanas antes da data de partida. Neste ano fiscal, a Cia. Hipotética tem como objetivo aumentar o lucro sobre a venda de passagens em 5%. Para atingir este objetivo, a Cia. resolveu melhorar a classe executiva de várias maneiras- prover um serviço personalizado de alocação de assentos para passageiros regulares, criar uma sala d e espera no principal aeroporto do país Hipotético, dando publicidade a esses dois novos serviços através de anúncios e marketing direto. Voltando ao passageiro, ele chega ao check-in para efetuar o CIA. Aérea Ao viajar de avião, é costume o passageiro efetuar reserva em um vôo antes da sua chegada ao aeroporto. Considere uma Cia. Aérea Hipotética e um passageiro viajando de uma cidade para outra. O passageiro solicita os serviços de um agente de viagem, que telefona para a Central de Reservas da Cia. para efetuar as reservas. O atendente da Central de Reservas solicita os detalhes do itinerário do passageiro - elo deseja ir de Recife para o Rio de Janeiro, onde participará de um -.urso que começará às 09-.00h da segunda-feira, 16 de junho. O atendente faz uma consulta ao sistema de informações de vôos da Cia, e descobre que existe um vôo chegando às 20:30h do domingo, 15 de julho. O agente de viagem diz que este vôo atende ao passageiro e a atendente volta a consultar o sistema para verificar a existência de lugares vagos. O vôo escolhido esta com a classe executiva lotada, mas existe um vôo mais cedo com lugares vagos, que faz escala em Salvador e chega ao Rio às 21:00h. Como o passageiro dá preferência ao primeiro vôo, seu nome é incluído na lista de espera deste vôo, além de ter lugar confirmado no vôo mais cedo. O agente relaciona as duas reservas, para que o lugar confirmado possa ser liberado, caso o da lista de espera venha a ser confirmado. O passageiro fornece os detalhes adicionais da reserva, tais como janela ou corredor, fumante ou não fumante, cardápio de carne ou vegetais e, então, a reserva é concluída. O bilhete de passagem é emitido duas semanas antes da data de partida. Neste ano fiscal, a Cia. Hipotética tem como objetivo aumentar o lucro sobre a venda de passagens em 5%. Para atingir este objetivo, a Cia. resolveu melhorar a classe executiva de várias maneiras- prover um serviço p ersonalizado de alocação de assentos para passageiros regulares, criar uma sala de espera no principal aeroporto do país Hipotético, dando publicidade a esses dois novos serviços através de anúncios e marketing direto. Voltando ao passageiro, ele chega ao check-in para efetuar o embarque. Como ele tem uma reserva confirmada, a Cia, sabe que há ligares suficientes no avião. Neste momento, é alocado um assento enumerado específico para o passageiro. O representante da Cia. verifica a Disponibilidade de assentos na classe executiva da aeronave, observa a Existência de diversos lugares vazios e que o passageiro tem preferência por corredor e pela área de não fumantes. Isto é confirmado com o Passageiro perguntando se ele deseja seu lugar usual. Um dos assentos no corredor da área de não fumantes é alocado ao passageiro. Adicionalmente, 5 representante verifica que o passageiro optou pelo cardápio de vegetais e confirma se esta continua sendo opção.



112

9.2 SQL Faça o comando SQL para os seguintes casos abaixo, marcando no comando onde aparece cada operação algébrica. Considere o modelo abaixo.

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

Uma relação de matricula e nome de todos os alunos. Uma relação com o nome de todos os cursos ordenados pelo nome de forma ascendente Uma relação de todos os alunos que iniciem com ‘ZE’ Uma relação contendo matricula, nome e código do curso de cada aluno. Resultado ordenado pelo nome do aluno. Uma relação contendo a matricula e nome dos alunos e o nome de cada curso que estão cursando. Uma relação contendo a matricula e nome dos alunos e o nome de cada disciplina que os alunos estão matriculados. Resultado ordenado por nome do aluno Uma relação contendo o código e nome da disciplina, a matricula e nome de cada aluno matriculado. O resultado deve ser ordenado pelo nome da disciplina e pelo nome do aluno Uma relação contendo o código e nome da disciplina, a matricula e nome de cada aluno matriculado. O resultado deve ser ordenado pelo nome da disciplina e pelo nome do aluno Uma relação contendo o código e nome da disciplina, a matricula e nome de cada aluno matriculado. Caso a disciplina não possua nenhum aluno matriculado, deve aparecer somente o nome da disciplina. O resultado deve ser ordenado pelo nome da disciplina e pelo nome do aluno Uma relação contendo o nome do curso, a matricula e o nome de cada aluno matriculado. Caso o curso não possua nenhum aluno, deve retornar apenas o nome do curso. Resultado ordenado pelo nome do curso e nome do aluno. Uma relação contendo o nome dos cursos que não possuem nenhum aluno matriculado. Uma relação contendo o nome de todas as disciplinas que não possuem nenhum aluno matriculado. Prof. Flavio Rezende

Apostila de Banco de Dados v.4.0 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27.

113

Uma relação contendo o nome das disciplinas que os alunos faltam fazer para se formar. Uma relação contendo o nome dos alunos que já cursaram todas as disciplinas de seu curso. Retornar a quantidade de alunos Retornar a quantidade de cursos Uma relação contendo o nome do curso e a quantidade de alunos que estão matriculados. Uma relação contendo o a matricula e nome do aluno, e a média das médias finais de cada disciplina. Uma relação contendo o código e nome da disciplina, a maior nota obtida pelos alunos, a média de todas as notas finais, a menor nota e a quantidade de alunos que já cursaram cada disciplina. Nome dos alunos que já cursaram a mesma disciplina mais de uma vez. Resultado ordenado por nome do aluno. Nome de todos os alunos que já cursaram mais de 3 períodos, não importando a ordem. Nome de todos os alunos que possuem CR maior que 6.5. Nome de todos os alunos e a média das notas de todas as disciplinas. Somente deve ser considerada as disciplinas que tiveram média maior ou igual a 7. Nome de todos os alunos que não estão cursando nenhuma disciplina no momento e já cursaram mais de 3 períodos distintos. Nome de todas as disciplinas do curso de REDES, que não possuem nenhum aluno matriculado no momento, possui média das notas maior que 7 e não teve nenhuma nota final menor que 4. Nome de todos os alunos matriculados no curso de REDES e nome de todos os alunos que já cursaram mais de uma disciplina. Resultado ordenado por nome do aluno. Nome de todos os alunos que estão cursando mais de uma disciplina e nome dos alunos que já cursaram mais de 3 disciplinas.

9.3 PLSQL

28. Desenvolva uma procedure para inserir na tabela de matricula somente as disciplinas que fazem parte do curso do aluno. 29. Desenvolva uma procedure para que um aluno se matricule em uma disciplina que ele já tenha cursado com média final maior ou igual a 7 30. Desenvolva uma procedure que deve receber o ano período (tabela de histórico) e transfira cada linha da tabela de matricula para o histórico contendo zero para a média final. Cada linha transferida deve ser uma transação. 31. Desenvolva uma procedure que receba como parâmetro o ano período, matricula do aluno, código da disciplina e a media final e altere o valor da média final do aluno. 32. Desenvolva um trigger para impedir que um aluno se matricule em uma disciplina que não faz parte do curso do aluno 33. Desenvolva um trigger para impedir que um aluno se matricule em uma disciplina que ele já tenha cursado com média final maior ou igual a 7 34. Desenvolva um trigger para impedir que seja feito qualquer comando dml na tabela HISTORICO fora do período de 8:00 as 19:00. 35. Desenvolva um trigger para fazer a auditoria na tabela de histórico, recuperando o usuário do banco que fez a alteração, o usuário da rede, a máquina e o horário que foi executado a alteração. Deve ser criado uma tabela de auditoria para o histórico.



9.4

114

Banco de Dados Distribuído

Uma rede de supermercados que atua em todo o território nacional, deseja melhorar os seus serviços aumentando a performance e a disponibilidade de seus sistemas. Pede-se: 1- Matriz de SISTEMAS x SITES 2- Matriz de SISTEMAS x TABELAS 3- Matriz TABELAS x SITES 4- Tabelas de sentido de replicação. 5- Quais serão as transações distribuídas. Sistema de Controle de Cartão de Crédito. O sistema de controle de cartão de crédito, controla o pagamento por parte das bandeiras de cartões de crédito e deve rodar no escritório de contabilidade no (RJ). Para conferir o total que uma bandeira deve depositar na conta da rede, deve ser calculado o somatório de todas as vendas realizada com a determinada bandeira dentro do mês.Para isto o sistema deve acessar as seguintes tabelas: Vendas ItensVenda Bandeiras Bancos Agencias Agencias_Tel Contas BancosBandeiras • • • • • • • •

Sistema de Controle de Pedidos O sistema de controle de pedidos tem o objetivo de controlar todos os pedidos de produtos feitos aos fornecedores, assim como o cadastro de todos os produtos. Deve rodar no escritório do RJ, SP e BA. Cada escritório pode fazer pedidos para qualquer supermercado de qualquer estado ou município. As tabelas utilizadas são: Fornecedores Pedidos Produtos ItensPedido UnidadeMedida Fornecedores_Tel • • • • • •

Sistema de Vendas O sistema de vendas deve controlar as vendas para os clientes. Este sistema deve rodar nos supermercados. O cadastro de Clientes deve ser replicados entre os supermercados. As tabelas utilizadas são: Produtos UnidadeMedida Vendas ItensVenda Secao Gondola Clientes Clientes_Tel • • • • • • • •



115


492997

Recommend Documents