Home

Apostila SQL & PL/SQL - Prof.Toninho

1. 3 WHERE INDEX NAME EMP ENAME IDX Resultado INDEX NAME INDEX TYPE TABLE NAME EMP ENAME IDX NORMAL EMP 2 18 3 Removendo um ndice Somente o propriet rio do ndice ou usu rios com privil gio DROP ANY INDEX podem remover o ndice Sintaxe DROP INDEX nome indice Exemplo Eliminar o indice emp ename idx SQL gt DROP INDEX nome indice ATEN O Um ndice n o pode ser modificado para altera lo deve se elimin lo e em seguida recri lo 2 19 Usu rio Existem dois tipos de usu rio que podem acessar o banco os usu rios que se autenticam pelo arquivo de senhas e os usu rios que se autenticam pelo dicion rio de dados Os usu rios que se autenticam pelo arquivo de senhas s o os usu rios DBA com privil gios para realizar startup e shutdown no banco Os usu rios que se autenticam pelo dicion rio de dados podem possuir privil gios de DBA mas n o podem realizar startup e shutdown no banco Estes usu rios s o criados e seu perfil fica armazenado no dicion rio de dados sendo assim ele poder realizar apenas as atividades descritas no seu perfil A cria o de um usu rio feita com uma instru o DDL e as especifica es s o armazenadas no dicion rio de dados por isso tamb m tratado como um objeto do banco Sintaxe Simplificada CREATE USER nome usu rio IDENTIFIED BY senha PASSWORD EXPIRE DEFAULT
2. onde nome_usu rio nome do usuario identified by atribui uma senha para o usuario senha senha que sera atribuida ao usuario nao deve iniciar com numeros PASSWORD EXPIRE DEFAULT quando o usuario se conecta ao banco ele deve redefinir a sua senha de autenticac o Este recurso amp valido somente para os usuarios que se autenticam pelo DD Exemplo Criar o usuario EXEMPLO com senha PBD cuja senha devera ser modificada quando a primeira conex o for realizada SQL gt CREATE USER EXEMPLO 2 IDENTIFIED BY PBD 3 PASSWORD EXPIRE ATEN O Para que o usu rio efetue a conex o necess rio conceder lhe privil gios de conex o este t pico ser visto posteriormente 2 19 1 Alterando usu rios As defini es atribu das a um usu rio podem ser alteradas isso pode ser feito pelo DBA ou por usu rios com privil gio ALTER USER Para isso deve ser informado o nome do usu rio o par metro que dever ser alterado e o valor do novo par metro Sintaxe ALTER USER usu rio IDENTIFIED BY senha onde usu rio o nome do usu rio senha especifica a nova senha Exemplo Alterar a senha do usu rio scott para lion SQL gt ALTER USER scott 2 DENTIFIED BY lion 2 19 2 Removendo usuarios Sintaxe drop user nome_usu rio Exemplo Eliminar o usuario exemplo SQL gt drop user exemp
3. Este t pico ser estudado na parte 2 Alojar sistemas de armazenamento e planejar os futuros armazenamentos de requerimentos para o sistema de banco de dados Criagao e armazenamento das estruturas primarias para que os desenvolvedores possam gerar aplicac es Criagao de objetos primarios uma vez que os usuarios tenham constru do uma nova aplicac o Modificar a estrutura do banco de dados para adequ lo as novas aplicac es Garantir a disponibilidade do banco de dados bem como a performance do mesmo Controlar e monitorar os acessos dos usu rios ao banco de dados Seguranca Fazer o backup das informac es e a restaurac o Manter um controle sobre os backup s ATENC O Os usu rios t m acesso ao banco de dados ou aos seus objetos de acordo com os privil gios que Ihe sao conferidos 2 SQL Strutured Query Language Neste capitulo ser o abordadas as estruturas SQL para criac o e manipulac o de tabelas e dados A SQL uma linguagem estruturada que utiliza uma combina o de construtores em lgebra e c lculo relacional e que possibilita n o s a realiza o de consultas como o pr prio nome sugere mas tamb m a manipula o de tabelas e dados apesar de ser conhecida como uma linguagem para consultas possibilita a realiza o de consultas mas tamb m cria o altera o e exclus o de tabelas e manipula es de dados A SQL a linguagem padr o utilizada pelos SGBD relacionais mesmo assim pod
4. um objeto de esquema que pode acelerar a recuperac o de linhas usando um ponteiro Se n o houver um ndice na coluna ocorrer uma an lise em toda a tabela O ndice muito parecido com uma chave e Permite classifica o por outros campos e Acelera a busca de registros espec ficos e A chave prim ria e a chave secund ria s o ndices A diferen a entre chave e ndice que as chaves s o estruturas l gicas usadas para identificar registros em uma tabela e ndices s o estruturas f sicas usadas para otimizar o processamento de dados pois s o objetos no banco de dados que fornecem um mapeamento de todos os valores em uma coluna de uma tabela Os ndices podem ser usados para garantir a unicidade dos elementos inseridos numa coluna ou campo e tamb m para alavancar a performance na busca por registros O aumento de performance obtido quando o crit rio de pesquisa por dados na tabela inclui refer ncia de coluna s indexadas Um ndice fornece acesso direto e r pido s linhas em uma tabela Seu objetivo reduzir a necessidade de E S do disco usando um caminho indexado para localizar dados rapidamente O ndice usado e mantido automaticamente pelo Oracle Server Ap s a cria o de um ndice n o necess ria nenhuma atividade direta do usu rio Os ndices s o l gica e fisicamente independentes da tabela que indexam Isso significa que eles podem ser criados e eliminados a qualquer momento e n o t m nen
5. BOOLEAN Armazena um de tr s poss veis valores TRUE FALSE ou NULL BINARY INTEGER Armazena n meros inteiros entre 2 147 483 647 e 2 147 483 647 PLS INTEGER Armazena n meros inteiros entre 2 147 483 647 e 2 147 483 647 estes valores requerem menos armazenamento e s o mais r pidos que os valores NUMBER e BINARY_INTEGER Exemplos v_nascimento date v_data date sysdate 7 declarac o de vari vel e inicializac o a partir de uma operac o aritmetica v_codigo number 2 not null 10 declarac o da vari vel com a restric o de preenchimento obrigat rio neste caso amp preciso atribuir o valor inicial v UF varchar2 2 SP declara o e inicializa o da vari vel com o valor SP observe que os literais devem ser informados entre aspas simples v_Loc varchar2 2 default RJ declarac o da vari vel cujo valor default RJ v_teste_logico boolean v valorl lt v_valor2 declara o da vari vel v teste logico que ser inicializada com o resultado da express o v_valorl lt v valor2 c const constant number 54 declara o da constante que est sendo inicializada O atributo STYPE pode ser utilizado para declarar vari veis de com a mesma estrutura de uma tabela ou de uma vari vel j existente Sintaxe identificador CONSTANT tabela coluna type variavelS type NOT NULL valor para inicializa
6. Mellanzone 55 03909 70 2200 Mesoz ica Tomate Seco 23 02983 89 21 00 Pomodori Calabresa 1i 19203 89 11 00 Kashu A tabela CARD PIO cont m Os campos Nome da Pizza Ingredientes C digo Pre o e Apelido O campo Nome da Pizza recebeu os dados Mellazone Tomate Seco e Calabresa O 3 registro composto pelo conjunto de dados Calabresa 11 19203 89 11 00 e Kashu A especifica o destas caracter sticas deve seguir os padr es do SGBDR escolhido Cada linha da tabela representa uma tupla ou registro Cada coluna da tabela representa um campo Os campos podem ainda ser classificados como composto multivalorado ou calculado e devem ser implementados de acordo com o modelo projetado que deve atender as especificidades do neg cio A seguir ser o apresentadas as caracteristicas de cada um e Campo de m ltiplas partes ou campo composto armazena dados com valores distintos No exemplo abaixo o campo endere o possui informa es sobre o nome da rua alfanum rico e o n mero do im vel num rico Instrutor Endere o L cia da Silva Pires Rua Sebasti o Marchesone 500 Campo Simples Campo contendo m ltipas partes e Campo multivalorado armazena m ltiplas inst ncias de um mesmo tipo no exemplo abaixo a instrutora L cia ensina diversas categorias Cisco Oracle Asp neste caso o campo Categorias ensinadas multivalorado pois recebe muitos dados sobre o mesmo assu
7. NDICES NO DICION RIO DE DADOS 2 18 3 REMOVENDO UM NDICE 2 19 USU RIO 2 19 1 ALTERANDO USU RIOS 2 19 2 REMOVENDO USUARIOS 2 19 3 PERSONAGENS 2 20 PRIVIL GIOS 2 20 1 PRIVILEGIOS DE SISTEMA 75 2 20 2 PRIVIL GIOS DE OBJETO 76 2 20 3 CONCEDENDO PRIVIL GIOS POR MEIO DE PERSONAGENS 77 2 20 4 REVOGANDO PRIVIL GIOS 78 2 20 5 CONSULTADO OS PRIVILEGIOS 78 3 PL SQL 81 3 1 BLOCOS AN NIMOS 81 3 1 1 OPERADORES E DELIMITADORES 82 3 1 2 VARI VEIS 83 3 1 3 TIPOS DE VARI VEIS 83 3 1 3 1 Tipos de Dados Escalares 85 3 1 4 VARI VEIS DE SUBSTITUI O 86 3 1 5 VARI VEIS DE LIGA O 87 3 1 6 INSTRU ES SELECT EM PL SQL 89 3 1 7 ANINHAMENTO DE BLOCOS E ESCOPO DAS VARI VEIS 91 3 1 8 INSER O DE DADOS 91 3 1 9 ATUALIZANDO DADOS 92 3 1 10 EXCLUS O DE DADOS 92 3 1 11 CONTROLE DE TRANSA ES 92 1 Fundamentac o teorica Neste cap tulo ser o abordados os conceitos necess rios para melhor compreens o e aplica o dos assuntos dos cap tulos posteriores que dizem respeito implementa o de banco relacionais Um bom profissional da rea de banco de dados precisa conhecer os tr s n veis envolvidos em um projeto de bancos de dados conceitual l gico e f sico Nesta disciplina ser abordado basicamente o terceiro n vel no entanto o conhecimento dos n veis f sico e l gico pr requisito para um bom implementador 1 1 Banco de Dados Podemos entender como Banco de Dados Data Base qualqu
8. NOT NULL P PRIMARY KEY R FOREIGN KEY e U UNIQUE KEY STATUS representa o estado em que a restri o se encontra ENABLE significa que a restri o est v lida e esta sendo usada e DISABLE que a restri o esta desabilitada e que por isso n o est em uso SEARCH CONDITION a condi o expressa da restri o Resultado CONSTRAINT_NAME E STATUS SEARCH_CONDITION SYS_C00884 Cc ENABLED EMPNO IS NOT NULL SYS_C00885 Cc ENABLED DEPTNO IS NOT NULL EMP_EMPNO_PK P ENABLED EMP_MGR_FK R ENABLED EMP_DEPTNO_FK R ENABLED EMP ENAME UK U ENABLED 2 8 Inser o de Dados A inser o de dados uma opera o DML Sintaxe INSERT INTO tabela coluna coluna VALUES valor valor Onde Tabela o nome da tabela Coluna o nome da coluna a ser preenchida a lista de colunas pode ser omitida neste caso devem ser informados valores para todas as colunas Valor o valor correspondente para a coluna Os valores de data e caractere devem ser informados entre aspas simples Para verificar a ordem default das colunas de uma tabela e o tipo de dado esperado utilize a instru o describe SQL gt DESCRIBE dept Name Null Type DEPTNO NUMBER 2 NOT NULL DNAME VARCHAR2 14 LOC VARCHAR2 13 Exemplo 1 Instru o completa para inser o de dados SQ
9. nimo empregado select from empregado Internamente o Oracle identifica e localiza o objeto pela referencia original e completa Sintaxe CREATE PUBLIC SYNONYM sin nimo FOR objeto onde PUBLIC cria um sin nimo acessivel a todos os usuarios sin nimo o nome do sin nimo a ser criado objeto identifica o objeto para o qual o sin nimo ser criado ATENC O e O objeto que receber um sin nimo n o pode estar contido em um pacote e Um nome de sin nimo deve ser distinto de todos os outros objetos de propriedade do mesmo usu rio Exemplo Criar um sin nimo de uso p blico para a tabela emp do usu rio Scott SQL gt CREATE PUBLIC SYNONYM empregado 2 FOR Scott emp 2 17 1 Consultando Sin nimos no dicionario de dados As defini es dos sin nimos ficam armazenadas no dicion rio de dados e podem ser consultadas na view USER_SYNONYM Exemplo Consultar todas as definic es dos sin nimos do usuario corrente SQL gt select from user_synonyms Resultado SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK CATALOG SYS CATALOG COL SYS COL 2 17 2 Removendo um Sin nimo A instruc o para se remover um sin nimo DROP SYNONYM nome_sin nimo Exemplo Eliminar o sin nimo empregado SQL gt DROP SYNONYM empregado ATENC O Somente usuarios com privil gios de DBA podem eliminar um sin nimo p blico 2 18 indice Index Um ndice do Oracle Server
10. o j armazenada Reutilizac o Quando declaradas podem ser usadas repetidamente em uma aplicac o simplesmente referenciando as em outras instrucdes incluindo outras instruc es declarativas Facilitar a manutenc o Pode se declarar vari veis baseadas na estrutura das colunas das tabelas ou em outras vari vies TYPE e ROWTYPE Se uma definic o subjacente for alterada a declarac o da vari vel atualizada em tempo de execuc o Isso permite a independ ncia dos dados reduz custos de manutenc o e permite que os programas se adaptem de acordo com as alterac es realizadas no banco de dados Passar valores aos subprogramas PL SQL atrav s de par metros Exibir os resultados em um bloco PL SQL atrav s de vari veis de sa da A declara o e a inicializa o das vari veis feita na se o declarativa de qualquer subprograma pacote ou bloco PL SQL As declarac es alocam espaco de armazenamento para um valor especificam seus tipos de dados e nomeiam a localizac o de armazenamento para que se possa referenci los As declaracdes poder o tamb m atribuir um valor inicial e impor a restri o NOT NULL ATENC O Ao Atribuir novos valores s vari veis na sec o execut vel o valor existente da vari vel substitu do pelo novo e necess rio declarar uma vari vel antes de referenci la em outras instru es incluindo outras instru es declarativas 3 1 3 Tipos de Vari veis Todas as vari veis PL SQL t
11. programac o tais como e declara o de vari vies e tipos pr definidos e Estruturas de sele o e repeti o e Procedimentos e fun es e Tipos de objeto e m todos nas vers es posteriores a 8 Al m de aceitar a manipula o de dados ela tamb m permite que as instru es de consulta da linguagem SQL sejam inclu das em unidades procedurais de c digo e estruturadas em blocos tornando a linguagem SQL uma linguagem avan ada de processamento de transa es Com a linguagem PL SQL poss vel criar blocos de an nimos ou blocos nomeados A linguagem PL SQL estruturada em blocos os programas podem ser divididos em blocos l gicos Um bloco PL SQL dividido em tr s se es DECLARATIVA opcional Se o destinada declara o das vari veis cursores e exce es que ser o utilizadas no bloco EXECUTAVEL obrigat ria tamb m conhecida como corpo do programa rea onde s o descritos os passos necess rios para realiza o da tarefa Podem ser utilizadas instru es SQL e ou PL SQL TATAMENTO DE EXCE ES opcional destinada ao tratamento das exce es geradas no bloco devem ser descritas as a es a serem desempenhadas quando ocorrerem erros 3 1 Blocos An nimos Os blocos an nimos n o ficam armazenados na base de dados e n o podem ser chamados por outros blocos PL SQL eles devem ser compilados a cada utiliza o Pode se incorporar um bloco an nimo em uma aplica o ou pode se ex
12. 13 2 2 3 Convers o de caracteres em n meros 2 13 2 2 4 Convers o caracteres em datas 2 13 2 3 Outras fun es de linha 2 13 3 FUN ES DE GRUPO 2 13 3 1 Agrupando Resultados 2 13 3 2 Restringindo resultados do grupo 2 13 4 SUBCONSULTAS 2 13 4 1 Subconsulta em Consultas 2 13 4 2 Subconsultas de uma linha 2 13 4 3 Subconsultas de v rias linhas 2 13 4 4 Subconsultas de v rias colunas 2 13 4 5 Subconsulta na cl usula FROM 2 13 4 6 Criando uma tabela usando uma subconsulta 2 13 4 7 Inser o a partir de subconsultas 2 13 4 8 Altera o de dados partir de subconsultas 2 13 4 9 Remo o de linhas a partir de subconsultas 2 14 INSTRU ES PARA MANIPULA O DE TABELAS 2 14 1 INSTRU O ALTER TABLE 2 14 1 1 Adicionando colunas 2 14 1 2 Adicionando restri es 2 14 1 3 Modificando colunas 2 14 1 4 Eliminando uma Restri o 2 14 1 5 Desativando e Ativando uma Restri o 2 14 2 ELIMINANDO UMA TABELA 2 15 VIS ES VIEWS 2 15 1 CRIANDO VIS ES 2 15 2 REMOVENDO UMA VIEW 2 15 3 CONSULTANDO VIEWS NO DICION RIO DE DADOS 2 16 SEQUENCIA 2 16 1 CRIANDO UMA SEQUENCIA 2 16 2 CONSULTANDO SEQU NCIAS NO DICION RIO DE DADOS 2 16 3 UTILIZANDO UMA SEQU NCIA 2 16 3 1 Alterando uma Segii ncia 2 16 4 REMOVENDO UMA SEQU NCIA 2 17 SIN NIMO 2 17 1 CONSULTANDO SIN NIMOS NO DICION RIO DE DADOS 2 17 2 REMOVENDO UM SIN NIMO 2 18 NDICE INDEX 2 18 1 CRIANDO UM NDICE 2 18 2 CONSULTANDO
13. 2 ENABLE CONSTRAINT emp_empno_pk Pode se usar a cl usula DISABLE nas instru es CREATE TABLE e ALTER TABLE 15 As views do dicion rio de dados s o tabelas l gicas que cont m informa es sobre os objetos do banco de dados ATENC O Para tornar uma restric o ativa os dados inseridos na coluna devem estar em conformidade com a restric o isto devem respeita caso contrario nao sera possivel 2 14 2 Eliminando uma Tabela A instrugao DROP TABLE remove a definic o de uma tabela Quando uma tabela eliminada o banco de dados perde todos os dados na tabela e todos os indices associados a ela Sintaxe DROP TABLE tabela onde Tabela o nome da tabela Exemplo Eliminar a tabela dept30 SQL gt DROP TABLE dept30 Ao eliminar uma tabela e Todos os dados s o deletados da tabela e As views e sin nimos permanecer o mas ser o inv lidos e Todas as transa es pendentes sofrer o commit e Somente o criador da tabela ou um usu rio com o privil gio DROP ANY TABLE poder remover uma tabela ATEN O A instru o DROP TABLE uma vez executada irrevers vel O Oracle Server n o questiona a a o quando voc emite a instru o DROP TABLE Se voc possuir tal tabela ou tiver um privil gio de n vel superior ent o a tabela ser imediatamente removida 2 15 Vis es Views Uma view uma tabela l gica baseada em uma tabela ou outra view Uma view n o cont m dados pr prios mas
14. Se a chave UNIQUE contiver mais de uma coluna tal grupo de colunas amp considerado uma chave exclusiva composta Podem ser definidas no nivel da coluna ou da tabela Exemplo Criar a tabela dept cujo nome do departamento dname nao podera ser duplicado SQL gt CREATE TABLE dept 2 deptno NUMBER 2 dname VARCHAR2 14 3 4 loc VARCHAR2 13 5 CONSTRAINT dept_dname_uk UNIQUE dname No exemplo anterior a constraint dept_dname_uk esta sendo criada no nivel da tabela 2 7 1 3 Restrigao PRIMARY KEY Uma restri o PRIMARY KEY cria uma nica chave prim ria para cada tabela A restric o PRIMARY KEY uma coluna ou conjunto de colunas que identifica exclusivamente cada linha em uma tabela Essa restric o imp e a exclusividade da coluna ou combinagao de colunas e assegura que nenhuma coluna que seja parte da chave primaria possa conter um valor nulo Pode ser definida no nivel da tabela ou da coluna ATENC O Um indice UNIQUE amp automaticamente criado para uma coluna PRIMARY KEY Exemplo Criar a tabela dept cujo campo numero do departamento deptno devera ser a chave de identificac o do registro Resoluc o 1 Definic o da chave prim ria no nivel da tabela SQL gt CREATE TABLE dept 2 deptno NUMBER 2 3 dname VARCHAR2 14 4 loc VARCHAR2 13 5 CONSTRAINT dept_dname_uk UNIQUE dname 6 CONSTRAINT dept_deptno_pk PRIMARY KEY deptno Res
15. VARCHAR2 9 mgr NUMBER 4 hiredate DATE sal NUMBER 7 2 comm NUMBER 7 2 deptno NUMBER 7 2 NOT NULL CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno ON DELETE CASCAD RFP OU OND 01 HH E TABELA EMP TABELA DEPT empno pk deptno pk Observe que o campo ename dname deptno chave prim ria na job loc tabela DEPT e esta sendo mgr utilizado na tabela EMP hiredate como chave estrangeira Por sal meio ae ee sera possivel relacionar as depino ik tabelas EMP e DEPT Resoluc o 2 Restric o Foreign Key definida no nivel da coluna observe que a instruc o foreign key nao requerida SOL gt CREATE TABLE emp 2 empno NUMBER 4 3 ename VARCHAR2 10 NOT NULL 4 job VARCHAR2 9 5 mgr NUMBER 4 6 hiredate DATE 7 sal NUMBER 7 2 8 comm NUMBER 7 2 9 deptno NUMBER 7 2 constraint emp_deptno_fk 10 REFERENCES dept deptno not null ATENGAO A definic o de uma chave estrangeira composta deve ser feita no nivel da tabela 2 7 1 5 Restri o CHECK Define uma condi o que cada registro deve atender Podem ser utilizados operadores de compara o para delimita o dos valores a serem aceitos para a coluna Exemplo Para evitar erros de digita o do usu rio voc coloca uma restri o do tipo CHECK para o campo SALARIO onde o mesmo deve ser maior que o sal ri
16. como uma janela atrav s da qual os dados das tabelas podem ser vistos ou alterados As tabelas nas quais uma view baseada s o chamadas tabelas base A view armazenada como uma instru o SELECT no dicion rio de dados Podem ser utilizadas para e Restringir o acesso a dados e Facilitar as consultas complexas por exemplo possibilita que usu rios consultem informa es de v rias tabelas sem saber como criar uma instru o de jun o e Permitir a independ ncia dos dados para usuarios ad hoc e programas aplicativos e Apresentar diferentes vis es dos mesmos dados 2 15 1 Criando Vis es Sintaxe CREATE OR REPLACE FORCE NOFORCE VIEW nome_view apelido apelido AS subconsulta WITH CHECK OPTION CONSTRAINT restric o WITH READ ONLY onde CREATE VIEW OR REPLACE Cria uma view ou substitui a view existente nome_view Identificador da view FORCE Cria a view independentemente das tabelas base existirem ou n o NOFORCE Cria a view somente se as tabelas base existirem default apelido especifica apelidos para as colunas selecionadas pela view opcional mas se utilizados devem corresponder ao n mero de colunas selecionadas na consulta consulta da view Opera o de consulta s tabelas base ou a outras views para gerar o resultado da vis o subconsulta uma instru o SELECT completa Voc pode usar apelidos par
17. conter valores nulos por default Deve ser definida no n vel da coluna Exemplo No exemplo acima a restri o NOT NULL esta sendo aplicada as colunas ENAME e DEPTNO da tabela EMP Observe que na linha 3 a restric o est sendo identificada j na linha 9 n o neste caso o Oracle a identificar de acordo com o seu padr o de identificac o SYS_cn SQL gt CREATE TABLE emp 2 empno NUMBER 4 ename VARCHAR2 10 constraint emp_ename_NN NOT NULL job VARCHAR2 9 mgr NUMBER 4 hiredate DATE sal NUMBE comm NUMBER 7 2 deptno NUMBER 7 2 NOT NULL T po J N ON OB WW Para verificar se as colunas da tabela est o ou n o com a restri o NOT NULL utilize a instru o DESCRIBE como segue exemplo abaixo SQL gt DESCRIBE EMP Name Null Type EMPNO NOT NULL NUMBER 4 ENAME VARCHAR2 10 JOB VARCHAR2 9 MGR NUMBER 4 HIREDATE DATE SAL NUMBER 7 2 COMM NUMBER 7 2 DEPTNO NOT NULL NUMBER 2 2 7 1 2 Restric o UNIQUE KEY Uma restric o de integridade UNIQUE KEY requer que cada valor em uma coluna ou conjunto de colunas chave seja exclusivo ou seja duas linhas de uma tabela nao podem ter valores duplicados em uma coluna especifica ou conjunto de colunas A coluna ou conjunto de colunas incluida na definic o da restric o UNIQUE KEY amp chamada de chave exclusiva
18. e de sua eventual manutenc o e Controle de Integridade Deve impedir que aplica es ou acessos pelas interfaces possam comprometer a integridade dos dados e Backups e recupera o de dados Deve possuir op es para realiza o de c pias de seguran a e recupera o de dados Al m das caracter sticas operacionais apresentadas anteriormente o SGBD deve possuir alguns componentes para possibilitar a realiza o das suas tarefas e Gerenciador de Acesso ao Disco O SGBD utiliza o Sistema Operacional para acessar os dados armazenados em disco controlando o acesso concorrente s tabelas do Banco de Dados O Gerenciador controla todas as pesquisas queries solicitadas pelos usu rios os acessos do compilador DML os acessos feitos pelo Processador do Banco de Dados ao Dicion rio de Dados e tamb m aos pr prios dados e Compilador DDL Data Definition Language Processa as defini es do esquema do Banco de Dados acessando quando necess rio o Dicion rio de Dados do Banco de Dados e Dicion rio de Dados Cont m o esquema do Banco de Dados suas tabelas ndices forma de cesso e relacionamentos existentes e Processador do Banco de Dados Manipula requisi es a Base de Dados em tempo de execu o E o respons vel pelas atualiza es e integridade da Base de Dados e Processador de Pesquisas Queries dos usu rios analisa as solicita es e se estas forem consistentes aciona o Processador do Banco de Dados para ac
19. efetivada neste caso as modifica es s o refletidas fisicamente no banco de dados Enquanto isso n o ocorre as modifica es s o refletidas apenas em mem ria e podem ser desfeitas ou descartadas O controle de transa es tamb m permite que as altera es realizadas possam ser visualizadas antes de se tornarem permanentes e que as opera es relacionadas logicamente possam ser agrupadas As instru es respons veis pelo controle das transa es s o o commit e o rollbback A emiss o de um commit confirma as transa es isto efetiva as manipula es de dados realizadas nas tabelas A emiss o de um rollback descarta as transa es que ainda n o tenham sido confirmadas Uma transa o tem in cio quando a primeira instru o SQL execut vel realizada e termina com um dos seguintes eventos e a emiss o de uma instru o COMMIT ou ROLLBACK e a execu o de uma instru o DDL ou DCL nesse caso ocorre um commit autom tico quando o usu rio sai do SQL Plus ou Houver uma falha no computador ou o sistema cair Um commit autom tico ocorre sob as seguintes circunst ncias A instru o DDL emitida A instru o DCL emitida A sa da normal do SQL Plus sem emitir explicitamente COMMIT ou ROLLBACK Um ROLLBACK autom tico ocorre quando ha uma finalizac o anormal do SQL Plus ou queda do sistema Em efetivag o parcial Em efetiva o Abortada Figura 3 Diagrama de estados de uma
20. extens o sal por exemplo exemplo1 sd no prompt do sal digite localizac o do arquivo nome do arquivo por exemplo SQL gt C programas exemplo1 sql No exemplo 1 foi utilizado o comando SQL PRINT para exibi o do resultado do processamento uma outra op o para isso a utiliza o do pacote DBMS output e procedimento put line Sintaxe DBMS output put line mensagem onde mensagem pode Ser uma cadeia de caracteres por exemplo DBMS_output put_line O nome do funcion rio e neste caso dever ser especificada entre aspas simples uma vari vel ou constante por exemplo DBMS output put line v sal anual ou ambas por exemplo DBMS output put line nome do funcion rio v sal anual neste caso est sendo utilizado o operador de concatena o para unir as duas mensagens ATENC O Para a utilizac o deste pacote necess rio ativ lo no SQL PLUS por meio da instru o SET SERVEROUTPUT ON Exemplo 2 programa completo Calcular o salario anual de dado funcionario com base no salario mensal fornecido pelo usuario Exibir o resultado SET SERVEROUTPUT ON ACCEPT p_nome PROMPT Digite nome do Instru es restar o so SOL ACCEPT p_sal_mes PROMPT Digite o valor do salar TONTA Declare v_nome varchar2 30 amp p_nome v_sal number 9 2 amp p_sal_mes v_sal_anual number 9 2 Bloco Begin PL SOL v_sal_anual v_sal 12 DBMS_output p
21. junc o e a coluna contiver uma ampla faixa de valores e a coluna contiver um grande n mero de valores nulos e duas ou mais colunas forem usadas juntas com frequ ncia em uma cl usula WHERE ou em uma condi o de jun o e a tabela for grande e se esperar que a maioria das consultas recupere menos que 2 a 4 das linhas ATEN O e Lembre se de que para aplicar exclusividade deve se definir uma restri o exclusiva na defini o da tabela Em seguida um indice exclusivo ser criado automaticamente e Quando a tabela for atualizada com frequ ncia Se voc tiver um ou mais indices em uma tabela as instru es DML que acessarem a tabela ser o mais lentas 2 18 2 Consultando ndices no dicion rio de dados As defini es do ndice ficam armazenadas no dicion rio de dados poss vel consult las utilizando a view USER INDEXES Tamb m poss vel checar as colunas envolvidas em um ndice consultando a view USER IND COLUMNS ATEN O A view user indexes cont m muitas colunas por isso recomendado que verifique estrutura da view e elabore uma consulta somente com as colunas relevantes pesquisa para isso utilize a instru o DESCRIBE user indexes Exemplo Exibir as informa es sobre o tipo de ndice e o nome da tabela para o indice emp ename idx 1 SELECT INDEX NAME INDEX TYPE TABLE NAME 2 FROM USER INDEXES
22. m um tipo de dados o qual especifica um formato de armazenamento restri es e uma faixa v lida de valores A linguagem PL SQL suporta quatro categorias de tipos de dados Escalares armazenam um nico valor Os principais tipos de dados s o aqueles que correspondem aos tipos de coluna nas tabelas do Oracle Server por exemplo vachar2 number char entre outros a linguagem PL SQL tamb m suporta vari veis booleanas 8 Neste curso a nfase ser nos tipos escalares vari veis de liga o e o tipo composto registro Compostos comporta o armazenamento de diferentes valores Os tipos compostos em PL SQL s o registro tabelas e matrizes Referenciais armazenam valores chamados de indicadores que designam outros itens de programa Um exemplo de tipos referenciais o REF CURSOR LOB large object armazenam blocos de dados n o estruturados como por exemplo texto imagens gr ficas videoclipes e formatos de arquivo para armazenar sons de at 4 gigabytes em tamanho Os tipos de dados LOB fornecem acesso eficiente aleat rio e em intervalos aos dados podendo ser atributos de um tipo de objeto As vari veis LOB podem ser classificadas como O tipo de dados CLOB character large object objeto grande de caractere usado para armazenar blocos grandes de dados com caracteres de um nico byte no banco de dados O tipo de dados BLOB binary large object objeto grande bin rio usado para armazenar objeto bin ri
23. para que se torne somente para leitura SQL gt CREATE OR REPLACE VIEW empvul0 2 employee_number employee_name job_title 3 AS SELECT empno ename job 4 FROM emp 5 WHERE deptno 10 6 WITH READ ONLY Quaisquer tentativas de inserir uma linha ou modifica la usando a view resultara em erro no Oracle Server 01733 nao amp permitida coluna virtual aqui virtual column not allowed here 2 15 2 Removendo uma View Pode se remover uma view sem perder dados porque uma view esta baseada em tabelas subjacentes no banco de dados Sintaxe DROP VIEW view Exemplo Remover a view empvu10 SQL gt DROP VIEW empvul0 A instrugao DROP VIEW remove a definic o da view do banco de dados A eliminac o de views nao tem efeito nas tabelas nas quais ela baseada As views ou outras aplicac es baseadas em views deletadas tornam se inv lidas Apenas o criador ou usuario com o privilegio DROP ANY VIEW podera remover uma view 2 15 3 Consultando views no dicionario de dados As definic es da view ficam armazenadas no dicionario de dados como uma consulta A tabela do dicion rio de dados USER_VIEWS contem o nome e a defini o da view O texto da instru o SELECT que constitui a view armazenado em uma coluna LONG Exemplo Exibir o nome e o texto de definic o das views do usu rio corrente SOL gt SELECT VIEW_NAME H EXT FROM US
24. pia parcial do banco de dados em segmentos de rollback isto quando uma opera o de inser o atualiza o ou exclus o feita no banco de dados o Oracle Server tira uma c pia dos dados antes de serem alterados e os grava no segmento de rollback Para descobrir o nome da restri o pode se consultar as views do dicion rio de dados USER_CONSTRAINTS ou USER_CONS_COLUMNS Antes de executar a consulta verifique os nomes das colunas com a instru o DESCRIBE Exemplo SELECT CONSTRAINT NAME COLUMN NAME FROM USER CONS COLUMNS WHERE TABLE NAME EMP Resultado CONSTRAINT_NAME COLUMN_NAME EMP_DEPTNO_FK DEPTNO EMP_EMPNO_PK EMPNO EMP_ENAME_UK ENAME EMP_MGR_FK MGR SYS_C00884 EMPNO SYS_C00885 DEPTNO 2 14 1 5 Desativando e Ativando uma Restric o Uma restric o pode ser desativada com a utilizac o da clausula DISABLE em conjunto com a instrug o ALTER TABLE Para torna la ativa novamente utiliza se a clausula ENABLE Sintaxe ALTER TABLE tabela DISABLE ENABLE CONSTRAINT restri o CASCADE onde Tabela o nome da tabela Restrig o Nome da constraint que ser eliminada Cascade Esta clausula elimina todas as restric es dependentes Exemplo 1 Desativar a chave primaria da tabela emp SQL gt ALTER TABLE emp 2 DISABLE CONSTRAINT emp_empno_pk CASCADE ne Exemplo 2 Ativar achave primaria da tabela emp SQL gt ALTER TABLE emp
25. 1 FUNDAMENTA O TE RICA 4 1 1 BANCO DE DADOS 1 2 BANCOS DE DADOS RELACIONAIS 1 3 SISTEMAS GERENCIADORES DE BANCOS DE DADOS 1 4 OBJETOS DO BANCO DE DADOS 1 5 USU RIOS DO BANCO DE DADOS 2 SQL STRUTURED QUERY LANGUAGE 2 1 HIST RICO 2 2 CARACTER STICAS DA SQL 2 3 INSTRU ES DDL 2 4 INSTRU ES DML 2 5 INSTRU ES DCL 2 6 AMBIENTE DE TRABALHO 2 7 CRIANDO TABELAS 2 7 1 RESTRI ES CONSTRAINTS 2 7 1 1 Restri o NOT NULL 2 7 1 2 Restri o UNIQUE KEY 2 7 1 3 Restri o PRIMARY KEY 2 7 1 4 Restri o FOREIGN KEY 2 7 1 5 Restri o CHECK 2 7 1 6 Consultando restri es de uma tabela 2 8 INSER O DE DADOS 2 9 ALTERA O DE DADOS 2 10 REMO O DE DADOS 2 11 CONFIRMANDO OU DESCARTANDO TRANSACOES 2 12 OPERADORES 2 12 1 OPERADORES ARITM TICOS 2 12 2 OPERADORES DE COMPARA O 2 12 3 OPERADORES L GICOS 2 13 CONSULTAS 2 13 1 JUNCOES JOIN 2 13 1 1 Jung es id nticas Equi Join 2 13 1 2 Jungdes N o Id nticas No EquiJoin 2 13 1 3 Junc es Externas OuterJoin 2 13 2 FUN ES SQL 2 13 2 1 Fun es de Uma nica Linha 2 13 2 1 1 Fun es de linha para manipula o de caracteres 2 13 2 1 2 Fun es de linha para manipula o de n meros 2 13 2 1 3 Fun es de linha para manipula o de datas 2 13 2 2 Fun es para convers o de dados 2 13 2 2 1 Convers o de datas em caracteres pl fm m a DA E 2 13 2 2 2 Convers o de n meros em caracteres 2
26. CYCLE CACHE n NOCACHE onde nome_seq ncia o identificador da sequ ncia INCREMENT BY n especifica o intervalo entre n meros de sequ ncia onde n um n mero inteiro o default 1 START WITH n especifica o primeiro numero de sequ ncia a ser gerado default 1 MAXVALUE n especifica o valor m ximo que a sequ ncia pode gerar NOMAXVALUE especifica um valor m ximo de 10127 para uma sequ ncia crescente e 1 para uma sequ ncia decrescente NOMAXVALUE a op o default MINVALUE n especifica o valor de sequ ncia m nimo NOMINVALUE especifica um valor m nimo de 1 para uma sequ ncia crescente e 10 26 para uma sequ ncia decrescente NOMINVALUE a op o default CYCLE NOCYCLE especifica que a sequ ncia continue a gerar valores ap s alcan ar seu valor m ximo ou m nimo ou n o gere valores adicionais NOCYCLE a op o default CACHE n NOCACHE especifica quantos valores o Oracle Server alocar previamente e manter na mem ria Por default o Oracle Server colocara em cache 20 valores Exemplo Criar uma sequ ncia identificada por dept deptno A sequ ncia dever iniciar em 91 ser incrementada de 1 em 1 ter como valor m ximo 100 e as demais configuar es default SQL gt CREATE SEQUENCE dept deptno 2 INCREMENT BY 1 3 START WITH 91 4 MAXVALUE 100 5 NOCACHE 6 NOCYCLE O exemplo acima cria uma sequ ncia chamada DEPT_DEP
27. ER_VIEWS Resultado VIEW_NAME TEXT SALES SELECT REPID ORD CUSTID CUSTOMER NAME CUSTNAME PRODUCT PRODID DESCRIP PRODNA Quando a base de dados acessada usando uma view o Oracle Server executa as seguintes operac es e Recupera a defini o da view da tabela do dicion rio de dados USER_VIEWS e Verifica os privil gios de acesso para a tabela base da view e Converte a consulta da view em uma opera o equivalente nas tabelas ou tabela base subjacentes Em outras palavras os dados s o recuperados a partir da s tabela s base ou uma atualizac o feita nela s 2 16 Sequencia Uma sequ ncia um objeto do banco de dados criado pelo usu rio que pode ser compartilhado por v rios usu rios para gerar n meros inteiros exclusivos Pode se usar as sequ ncias para gerar valores de chave prim ria automaticamente A sequ ncia gerada e incrementada ou diminu da por uma rotina Oracle8 interna Esse objeto pode economizar tempo pois capaz de reduzir a quantidade de c digo de aplicac o necess ria para criar uma rotina de gerac o de sequ ncias al m disso acelera a efic cia do acesso a valores de sequ ncia quando est o em cach na mem ria 2 16 1 Criando uma sequencia Sintaxe CREATE SEQUENCE nome_seqti ncia INCREMENT BY n START WITH n MAXVALUE n NOMAXVALUE MINVALUE n NOMINVALUE CYCLE NO
28. L gt INSERT INTO dept DEPTNO DNAME LOC 2 VALUES 70 PRODUCAO MARILIA Neste caso nao existe a necessidade de indicar as colunas que irao receber as inserc es pois est o sendo inseridos valores para todas as colunas ent o o mesmo exemplo poderia ser resolvido assim Exemplo 2 SQL gt INSERT INTO dept 2 VALUES 70 PRODUCAO MARILIA Tamb m possivel a inserc o impl cita de nulos isto pode se deixar de informar uma coluna na inserc o de dados para a qual ser atribu do nulo Exemplo 3 SQL gt INSERT INTO dept deptno dname 2 VALUES 60 I MES A tabela dept cont m as coluna deptno dname e loc no entanto no exemplo anterior estao sendo inseridos valores apenas as colunas deptno e dname neste caso a coluna loc ira conter NULL Exemplo 4 SQL gt INSERT INTO dept 2 VALUES 70 FINANCE NULL J neste outro exemplo o nulo para a coluna loc est sendo explicitado note que a lista de colunas omitida pois est o sendo mencionados os dados para todas as colunas A palavra NULL poderia ser substituida por aspas simples 70 FINANCE Para inserc o de datas o formato default do Oracle DD MON YY mas a data tamb m pode ser informada de acordo com a configurac o do sistema veja o exemplo a seguir Exemplo 5 SQL gt INSERT INTO emp 2 VALUES 2296 AR
29. OMANO SALESMAN 7782 03 02 97 3 1300 NULL 10 O resultado pode ser visualizado executando a consulta SQL gt Select 2 from emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 2296 AROMANO SALESMAN 7782 03 02 97 300 10 1 A instru o select ser abordada com mais detalhes posterioremente 2 9 Alterac o de dados A alterac o de dados uma operac o DML Sintaxe UPDATE tabela SET coluna valor coluna valor WHERE condic o onde Tabela o nome da tabela Coluna o nome da coluna a ser preenchida podem ser atualizados os dados de varias colunas Valor o valor correspondente ou subconsulta para a coluna Condic o uma cl usula opcional e identifica as linhas a serem atualizadas de acordo com uma condic o que pode ser composta por express es de comparac o ou subconsultas Exemplo Alterar o c dido do departamento para 20 do funcion rio com c digo 7782 SQL gt UPDATE emp 2 SET deptno 20 3 WHERE empno 7782 ATENGAO Todas as linhas na tabela s o modificadas quando a cl usula WHERE omitida 2 10 Remoc o de dados A remoc o de dados uma operac o DML Sintaxe DELETE FROM tabela WHERE condic o onde Tabela o nome da tabela Condig o Est cl usula opcional e identifica as linhas a serem eliminadas de acordo com uma condic o que pode ser composta por express es de comparac o ou subconsultas Exemplo SQL gt DELETE F
30. ROM dept 2 WHERE dname PRODUCAO ATENGAO Todas as linhas na tabela ser o removidas se a cl usula WHERE for omitida 2 11 Confirmando ou Descartando transac es Do ponto de vista do usuario uma transac o parece uma simples operac o por exemplo transferir o dinheiro de uma conta corrente para outra o usuario informa os dados requeridos para que a operac o de transfer ncia seja realizada e recebe uma notificac o de conclusao da operac o Por outro lado para que a operac o seja realizada com sucesso sem a ocorr ncia de falhas uma s rie de operac es devem ser realizadas no caso do exemplo de transfer ncia de valores de uma conta para outra de maneira bem simplificada e o dinheiro precisa ser debitado de uma conta portanto uma opera o de atualiza o de dados deve ser realizada nesta conta e o dinheiro precisa ser creditado em outra conta portanto outra opera o de atualiza o de dados deve ser realizada nesta outra conta Sendo assim uma transa o um conjunto de opera es DML que s o realizadas para concluir uma determinada tarefa Para garantir a integridade dos dados necess rio que as transa es assegurem e A consist ncia de dados e A atomicidade todas as opera es devem ser refletidas corretamente no banco ou ent o nenhuma das opera es dever ser realizada e A integridade da base de dados Quando n o ocorrem falhas no processamento das opera es de uma transa o ela pode ser
31. Rapida Novatec 2000 Este guia de consulta r pida oferece as instru es PL SQL de maneira sint tica Oracle8i Administra o de Este livro dedica um cap tulo a Administra o Bancos de Dados Rio de de tabelas e ndices Janeiro Editora Ci ncia Moderna Oracle8i Administra o de Este livro dedica um cap tulo a PL SQL Bancos de Dados Rio de Procedures triggers e packages Janeiro Editora Ci ncia Moderna Morelli Eduardo Terra Oracle 8i Este livro dedica alguns cap tulos a PL SQL Fundamental SQL PL SQL e mas n o aborda blocos an nimos oferece Administra o Editora rica exemplos simples e funcionais e exerc cios 2000 Sites Oracle http otn oracle com sample c Neste site podem ser encontrados ode tech pl sgl index html v rios exemplos sobre PL SQL em ingl s imasters http www imasters com br Apresenta uma s rie de artigos e dicas sobre PL SQL SLQMagazi http www sqlmagazine com b Apresenta uma s rie de artigos e dicas ne r sobre PL SQL BIBLIOGRAFIA Bibliografia Complementar 1 Cougo Paulo S rgio Modelagem Conceitual e projeto de bancos de dados Rio de Janeiro Campus 1997 Bibliografia Basica 2 Silberschatz Abraham Korth Henry F e Sudarshan S Sistema de Banco de Dados Sao Paulo Makron Books 1999 3 Urman Scott Oracle 8 Programa o PL SQL McGraw Hill 1999 4 Morelli Eduardo Terra Or
32. SGBDR apresentam algumas caracter sticas operacionais desej veis s o elas e Controle de Redund ncias A redund ncia consiste no armazenamento de uma mesma informa o em locais diferentes provocando inconsist ncias Por exemplo Quando informa es est o em locais diferentes onde uma atualizada e outra n o Em um Banco de Dados as informa es s se encontram armazenadas em um nico local n o existindo duplica o descontrolada dos dados Quando existem replica es dos dados estas s o decorrentes do processo de armazenagem t pica do ambiente Cliente Servidor totalmente sob controle do Banco de Dados e Compartilhamento dos Dados deve possuir um sistema de controle de concorr ncia ao acesso dos dados garantindo em qualquer tipo de situa o a escrita leitura de dados sem erros Como exemplo imagine que um casal possui uma conta corrente conjunta cujo saldo R 100 00 e ambos tentam efetuar um saque exatamente no valor de R 100 00 no mesmo momento e em locais diferentes e Controle de Acesso deve possuir um sistema de controle dos privil gios de acesso aos dados cada um dos usu rios ou grupos de usu rios do banco e Interfaceamento Formas de acesso gr fico em linguagem natural em SQL ou ainda via menus de acesso n o sendo uma caixa preta somente sendo pass vel de ser acessada por aplica es e Esquematiza o Mecanismos que possibilitem a compreens o do relacionamento existente entre as tabelas
33. TENC O Privil gios de sistema nao sao revogados em cascata Privil gios de objeto sao revogados em cascata 2 20 5 Consultado os privil gios Pode se consultar os privil gios que um usu rio ou personagem possui para isso pode se utilizar as tabelas do dicion rio de dados a seguir s o apresentadas algumas possibilidades Tabela de Dicion rio de Descric o Dados ROLE_SYS_PRIVS Privil gios de sistema concedidos a personagens ROLE_TAB_PRIVS Privil gios de tabela concedidos a personagens USER COL PRIVS MADE Os privil gios de objeto concedidos s colunas dos objetos do usu rio USER COL PRIVS RECD Os privil gios de objeto concedidos ao usu rio em colunas espec ficas USER ROLE PRIVS Personagens acess veis ao usu rio USER TAB PRIVS MADE Os privil gios de objeto concedidos aos objetos do usu rio USER TAB PRIVS RECD _ Os privil gios de objeto concedidos ao usu rio Exemplo Exibir a lista de privil gios de objeto concedidos s colunas dos objetos do usu rio SQL gt select from USER COL PRIVS MADE Resultado GRANTEE TABLE NAME COLUMN NAME GRANTOR EXEMPLO EMP EMPNO SCOTT EXEMPLO EMP ENAME SCOTT EXEMPLO EMP DEPTNO SCOTT Para saber mais consulte Livro Comentarios Este livro aborda 3 PL SQL A linguagem PL SQL Procedural Language SQL uma extens o de linguagem procedural da Oracle Corporation para SQL incorpora muitos recursos de
34. TNO para ser usada na coluna DEPTNO da tabela DEPT A sequ ncia come a em 91 nao permite cach e nao permite o ciclo da sequ ncia ATEN O Se o valor do par metro INCREMENT By for negativo a sequ ncia ser descendente 2 16 2 Consultando Seq ncias no dicion rio de dados As definic es da sequ ncia ficam armazenadas no dicion rio de dados e podem ser consultadas na view USER_SEQUENCES SQL gt select from user_sequences where sequence_name DEPT_DEPTNO Resultado SEQUENCE NAME MIN VALUE MAX VALUE NCREMENT_BY C O CACHE SIZE LAST NUMBER DEPT DEPTNO 1 3 1 Y N 0 1 A coluna LAST NUMBER exibe o pr ximo n mero de sequ ncia dispon vel 2 16 3 Utilizando uma Sequ ncia Para utilizar a sequ ncia pode se fazer refer ncia aos seus valores usando as pseudocolunas NEXTVAL e CURRVAL NEXTVAL retorna o pr ximo valor de sequ ncia dispon vel CURRVAL obt m o valor de sequ ncia atual NEXTVAL e CURRVAL podem ser usadas nos seguintes casos e Na lista SELECT de uma instru o SELECT que n o seja parte de uma subconsulta e Na lista SELECT de uma subconsulta em uma instru o INSERT e Na cl usula VALUES de uma instru o INSERT e Na cl usula SET de uma instru o UPDATE NEXTVAL e CURRVAL n o podem ser usadas nos seguintes casos e Na lista SELECT de uma view e Em uma instru o SELECT com a
35. a as colunas na lista SELECT e n o pode conter a cl usula order by WITH CHECK OPTION especifica que somente linhas acess veis view podem ser inseridas ou atualizadas restri o o nome atribu do restri o CHECK OPTION WITH READ ONLY assegura que as opera es DML n o possam ser executadas nesta view H duas classifica es para views simples e complexa Uma view simples uma que e Seleciona dados a partir de somente uma tabela e N o cont m fun es ou grupos de dados e Pode se executar a DML Uma view complexa uma que e Seleciona dados a partir de v rias tabelas e Cont m fun es ou grupos de dados e Nem sempre pode se executa a DML Exemplo 1 Criar uma view com nome EMPVU10 que contenha detalhes tais como n mero nome e cargo dos funcion rios que trabalham no departamento 10 SQL gt CREATE VIEW empvul0 2 AS SELECT empno ename job 3 FROM emp 4 WHERE deptno 10 A estrutura da view pode ser exibida utilizando o comando DESCRIBE do SQL Plus SQL gt DESCRIBE EMPVU10 Name Null Type EMPNO NOT NULL NUMBER 4 ENAME VARCHAR2 10 JOB VARCHAR2 9 ATEN O Se n o for especificado um nome de restri o para uma view criada com CHECK OPTION o sistema ir atribuir um nome default no formato SYS_Cn A op o OR REPLACE altera a defini o da view sem elimin
36. acle 8i Fundamental SQL PL SQL e Administrac o Editora Erica 2000
37. adi o operador de subtra o operador de multiplica o operador de divis o operador de igualdade lt gt operador de compara o l diferente gt operador de compara o lt operador de compara o maior do que menor do que delimitador de fim express o delimitador de in cio de express o indicador de atributo a terminador de instruc o separador de componentes separador de itens delimitador de cadeia de delimitador de cadeia de caracteres caracteres mensagens indicador de vari vel de operador de exponenciac o ligagao operador de comparac o operador de concatenag o a diferente operador de atribuic o operador de intervalo gt operador de comparac o maior ou igual indicador de coment rio de lt operador de comparac o linha menor ou igual delimitador de in cio de delimitador de fim de coment rio de linha coment rio de linha lt lt delimitador de inicio do texto gt gt delimitador de final do texto do do label etiqueta label etiqueta 3 1 2 Variaveis Os dados podem ser armazenados temporariamente em uma ou mais variaveis e podem ser utilizadas para Manipulac o de valores armazenados As vari veis podem ser usadas para c lculo e manipulac o de outros dados sem acessar o banco de dados ou seja ap s os valores em mem ria n o h necessidade de outros acessos para complemento da informac
38. alorl number 2 7 valor2 number 2 13 BEGIN bloco 1 DECLARE bloco 2 valorl varchar2 30 Valor 1 do bloco 2 BEGIN bloco 2 DECLARE bloco 3 valorl date sysdate BEGIN bloco 3 DBMS OUTPUT PUT LINE Valor T do bloco Boose valorl END bloco 3 DBMS_OUTPUT PUT_LINE Valor 1 do bloco 2 valorl END bloco 2 DBMS_OUTPUT PUT_LINE Valor 1 do bloco 1 valorl END bloco 1 Resultado da execuc o do bloco SOL gt c programas escopo sql Valor 1 do bloco 3 12 05 04 Valor 1 do bloco 2 Valor 1 do bloco 2 Valor 1 do bloco 1 7 3 1 8 Inserc o de Dados Exemplo 6 Adicionar informac es sobre novos funcionarios na tabela EMP declare v_empno emp empnoStype empno_sequence nextval v_ename emp enamestype HARDING v job emp job Stype CLERK v_deptno emp deptnoStype 10 begin insert into emp empno ename job deptno values v_empno v_ename v_job v_deptno end 3 1 9 Atualizando Dados Exemplo 7 Aumentar o salario de todos os funcionarios na tabela EMP e que sejam Analistas Analysts adicionando 2000 ao valor do salario DECLARE v_sal_increase emp salSTYPE 2000 BEGIN UPDATE emp SET sal sal v_sal_increase WHERE job ANALYST END 3 1 10 Exclusao de Dados Exemplo 8 Deletar linhas que
39. amento e Ingl s estruturado de alto nivel A SQL formada por um conjunto bem simples de senten as em ingl s oferecendo um rapido e f cil entendimento e Consulta interativa Prov um acesso rapido aos dados fornecendo respostas ao usuario quase instantaneamente e M ltiplas vis es dos dados Permite ao criador do banco de dados levar diferentes visdes dos dados a diferentes usu rios e Defini o din mica dos dados Pode se alterar expandir ou incluir dinamicamente as estruturas dos dados armazenados 2 3 Instruc es DDL Os comandos DDL Data Definition Language composta entre outros pelos comandos Create que amp destinado a criac o do Banco de Dados das Tabelas que o comp e al m das relac es existentes entre as tabelas Como exemplo de comandos da classe DDL temos os comandos Create Alter e Drop Por meio das instrugdes DDL podem ser realizadas as tarefas de e Cria o altera o e elimina o de objetos do banco de dados como por exemplo tabelas indices sequ ncias Concec o e revogac o de privil gios em objetos Criac o do banco de dados Criac o de usu rios Concec o e revogac o de privil gios a usu rios 2 4 Instruc es DML Os comandos da s rie DML Data Manipulation Language destinados a consultas inser es exclus es e altera es em um ou mais registros de uma ou mais tabelas de maneira simult nea Como exemplo de comandos da classe DML pode se citar os comandos In
40. bu do um valor inicial a nova vari vel conter NULL por default e N o aconselh vel identificar uma vari vel com nome igual ao nome das colunas de tabela usadas no bloco Se as vari veis PL SQL ocorrerem nas instru es SQL e tiverem o mesmo nome que uma coluna o Oracle Server sup e que seja a coluna que esteja sendo referenciada 3 1 3 1 Tipos de Dados Escalares Um tipo de dados escalares armazena um valor nico e n o possui componentes internos Os tipos de dados escalares podem ser classificados em quatro categorias n mero caractere data e booleano Os tipos de dados de caractere e n mero possuem subtipos que associam um tipo b sico a uma restri o Por exemplo INTEGER e POSITIVE s o subtipos do tipo b sico NUMBER Segue abaixo exemplo de tipos de Dados Escalares B sicos Tipo de dado Descri o VARCHAR2 tamanho Armazena caracteres com tamanho vari vel com at 32 767 bytes n o h tamanho default para estas vari veisl NUMBER tamanho Armazena n meros reais ou inteiros precis o DATE Armazena datas e horas entre os per odos de 4712 A C e 9999 D C CHAR tamanho Armazena caracteres de tamanho fixo at 32 767 bytes tamanho default 1 byte LONG Armazena caracteres com tamanho vari vel de at 32 760 bytes a largura m xima de 2 147 483 647 bytes LONG RAW Armazena dados bin rios e strings de byte de at 32 760 bytes estes dados n o s o interpretados pelo PL SQL
41. c o expr default Exemplos v nome emp ename type declara o da vari vel com a mesma estrutura da coluna ename da tabela emp v balance number 7 2 v min balance v balancettype declara o da vari vel com a mesma estrutura da vari vel declarada anteriormente ATEN O uma boa pr tica de programa o a ado o de uma conven o de nomea o para vari veis por exemplo o prefixo v representa uma vari vel c_ representa uma constante 3 1 4 Vari veis de Substitui o Al m das vari veis declaradas no Bloco PL SQL existem as vari veis de substitui o ou de liga o O c digo PL SQL n o tem capacidade de entrada sa da pr pria para isso pode se utilizar o ambiente no qual o c digo PL SQL estiver sendo executado para passar valores para o bloco estes valores s o passados por meio das vari veis de substituic o ATEN O Ao utilizar as vari veis de substitui o os valores s o substitu dos no bloco PL SQL antes que esse bloco seja executado Por isso n o poss vel substituir os diferentes valores para as vari veis de substituic o usando um loop Exemplo ACCEPT p_nome PROMPT Digite o nome do funcionario ACCEPT p_sal_mes PROMPT Digite o valor do salario O comando accept indica que o valor para a vari vel de substituic o p_nome e p sal mes ser o fornecidos pelo teclado O comando PROMPT indica a mensagem que ser exibida para orientar o usu rio quanto ao va
42. cesso aos dados mec nismos que auxiliem na recuperac o dos dados entre outros Os objetos do banco representam estas estruturas e s o criados e manipulados com as instru es DDL Data Definition Language e as suas defini es ficam armazenadas no dicion rio de dados Data Dictionary do banco Como exemplos de objetos pode se citar Tabela Table Vis o View Sequencia Sequence ndice Index Usu rio User Personagem role Procedimentos Procedure Fun es Function Gatilhos Trigger Pacotes Package 1 5 Usu rios do Banco de Dados Os usu rios do banco de dados s o as pessoas que utilizam o banco de dados e seus objetos direta ou indiretamente e est o agrupados nas seguintes categorias e desenvolvedor de aplica es o profissional respons vel pela constru o de aplica es que ir o acessar a base de dados para realizar consultas altera es ou exclus es de dados e usu rio final a pessoa que faz uso dos dados consolidados para tomada de decis o ou simples consulta ou atualiza o Normalmente interage com o banco por interm dio de aplica es e analista de Banco de dados Case o usu rio respons vel pela modelagem dos dados e implementa o do banco de dados nos n veis l gico e f sico junto com o DBA e administrador de banco de dados o profissional que tem as seguintes responsabilidades Instalar e atualizar o banco de dados e as ferramentas de aplica o
43. cesso aos usu rios a compila o das instru es enfim o Sistema Gerenciador do Banco de Dados J a concep o de um banco de dados compreendida pela parte l gica do sistema de banco de dados se d em tr s n veis conceitual l gico e f sico N vel conceitual consiste na identifica o dos dados que dever o ser armazenados e no entendimento de como estes dados ser o utilizados para produ o de informa es Neste nivel n o s o necessariamente consideradas particularidades relacionadas ao modelo de banco de dados que ser utilizado para a implementa o do banco E a etapa de entendimento do neg cio e sua representa o No contexto de desenvolvimento de projetos este n vel est associado a etapa de an lise de requisitos N vel l gico consiste na representa o de como os dados ser o armazenados seguindo uma metodologia De acordo com Cougo essa representa o independente dos dispositivos ou meios de armazenamento f sico das estruturas de dados mas deve ser elaborado respeitando se os conceitos tais como chaves de acesso controles de chaves duplicadas normaliza o integridade referencial entre outros No contexto de desenvolvimento de projetos este n vel est associado etapa de projeto N vel F sico Consiste na implementa o das estruturas de dados dos relacionamentos das chaves enfim a etapa da constru o do banco de dados considerando os aspectos f sicos e as cara
44. ciados a um conjunto de programas para acesso a esses dados tendo como objetivo proporcionar um ambiente tanto conveniente quanto eficiente para a recuperac o de dados O conjunto de dados cont m informac es sobre um assunto em particular este assunto pode ser uma empresa um projeto um negocio entre outros O SGBDR ou RDBMS Relational Database Management System sao sistemas que disponibilizam recursos para implementa o de projetos de bancos de dados relacionais manutenc o e administrac o de suas estruturas dados e usu rios Com conceito de centralizar os dados e torn los dispon veis a v rios usu rios conseguimos incrementar a velocidade ter os dados centralizados tamb m torna mais f cil a administrac o e a seguranca do banco de dados Este conceito chamado de SGBDR cliente servidor Neste tipo de SGBDR os dados ficam fisicamente armazenados em um computador que age como um servidor de banco de dados e os usu rios interagem com esse banco por meio de aplicac es localizadas em seus pr prios computadores denominados clientes Estes sistemas sao amplamente utilizados para administrar grandes volumes de dados compartilhados Atualmente podem ser encontrados diversos SGBDR para quase todos os sistemas operacionais e que podem ser usados para atender inumeras necessidades Alguns exemplos de sistemas gerenciadores de bancos de dados relacionais sao Oracle Sybase MySql SQLServer Access entre outros De maneira geral os
45. ck No exemplo anterior qualquer tentativa de alterac o do numero do departamento para qualquer linha na view falhar porque ela violar a restri o WITH CHECK OPTION Esta clausula especifica que INSERTS e UPDATES executados pela view nao t m permissao de criar linnas que a view nao possa selecionar e portanto ela permite restricdes de integridade e verificac es de validac o de dados a serem impostas aos dados que estiverem sendo inseridos ou atualizados Se houver uma tentativa de executar operac es DML em linhas que a view nao selecionou sera exibido um erro com o nome da restric o se ele tiver sido especificado por exemplo a instru o DML a seguir realiza uma altera o no numero do departamento utilizando a view empvu20 SQL gt UPDATE empvu20 2 SET deptno 10 3 WHERE empno 7788 update empvu20 ERRO na linha 1 ERROR at linel ORA 01402 violac o na cl usula where WITH CHECK OPTION na view view WITH CHECK OPTION where clause violation Neste caso nenhuma linha sera atualizada porque se o numero do departamento fosse alterado para 10 a view nao seria mais capaz de enxergar o funcionario Por isso com a clausula WITH CHECK OPTION a view poder ver apenas funcionarios do departamento 20 e nao permitira que o numero de departamento para esses funcionarios seja alterado na view Exemplo 5 Utilizando a cl usula WITH READ ONLY Alterar as defini es da view empvu10
46. cter sticas do sistema gerenciador de bancos de dados utilizado No contexto de desenvolvimento de projetos este n vel est associado etapa de implementa o ou opera o 1 2 Bancos de dados relacionais conjunto de dados sobre um neg cio organizados por assunto onde cada assunto representado por uma tabela por exemplo uma pizzaria precisa de informa es sobre os fornecedores dos produtos que utiliza o estoque de seus produtos clientes pizzas etc sendo assim podemos separar as os dados que representar o as informa es de fornecedores clientes produtos etc Nos modelos relacionais estes assuntos s o representados por tabelas As tabelas s o compostas por conjuntos de campos denominados registros tuplas que armazenam dados Os dados existem fisicamente e precisam de um contexto para adquirir algum significado S o est ticos isto permanecem no mesmo estado at que sejam modificados por um processo manual ou autom tico Mellanzone 55 03909 70 22 00 Mesoz ica Isoladamente estes dados nao t m nenhum sentido O que Mellanzone Ser o nome de uma pessoa de um supermercado ou de uma pizza E 55 E um c digo Uma soma Uma nota 1 a ER a unten a Alguns autores n o fazem distin o entre dados e informa es mas para evitar interpreta es trataremos dado como uma valor fisicamente registrado no banco de dados e informa o como o significado atribu do a estes valore
47. e objeto Privil gio Tabela Vis o Sequ ncia Procedimento ALTER x x DELETE X x EXECUTE X INDEX x INSERT x x REFERENCES x SELECT x x x UPDATE Sintaxe GRANT privilegio lista de colunas ALL PRIVILEGES ON esquema objeto TO usuario role public WITH GRANT OPTION onde privil gio oprivil gio de objeto a ser concedido lista de colunas especifica as colunas de uma view ou tabela on esquema objeto especifica o nome do objeto to usu rio o nome do usu rio role um personagem ou fun o public concede os privil gios de sistema a todos os usu rios WITH GRANT OPTION permite que o usu rio conceda o privil gio recebido a outro usu rio Exemplo 1 Conceder ao usu rio exemplo o privil gio de inserir dados nas colunas empno ename e deptno da tabela EMP do usu rio scott SQL gt GRANT INSERT empno ename deptno on SCOTT EMP to exemplo Exemplo 2 Conceder ao usu rio exemplo o privil gio de excluir e alterar dados na tabela EMP do usu rio scott podendo inclusive repassar estes privil gios a outros usu rios SQL gt SQL gt GRANT DELETE UPDATE 2 ON SCOTT EMP 3 TO EXEMPLO 4 WITH GRANT OPTION 2 20 3 Concedendo privil amp gios por meio de personagens Exemplo Conceder o privil gio de criar tabelas e vis es para o personagem manager e depois atribuir este personagem aos usu r
48. ecut lo interativamente no SQL Plus Exemplo da estrutura de um bloco em PL SQL DECLARE v variavel varchar2 5 BEGIN Select nome coluna into v variavel 17 Os blocos nomeados procedimentos fun es triggers e packages possuem algumas particularidades que ser o estudadas no 4 semestre s o compilados e armazenados na base de dados e por isso n o precisam ser compilados a cada execu o from nome_tabela EXCEPTION When exception_name then procedimentos a serem executados quando uma determinada excec o ocorrer END Sobre o c digo As palavras chave DECLARE BEGIN e EXCEPTION n o s o sucedidas de ponto e v rgula as demais instruc es sao terminadas com um ponto e v rgula A palavras chave BEGIN e END sao obrigat rias Deve se utilizar uma barra para executar o bloco an nimo PL SQL no buffer de SQL Plus Coloque um ponto para fechar um buffer de SQL Plus Um bloco PL SQL tratado como uma instrugao cont nua no buffer e os ponto e v rgulas no bloco n o fecham ou executam o buffer A edic o do bloco pode ser feita utilizando um editor de textos cujo arquivo dever possuir extens o sql por exemplo teste sql No SQL Plus poss vel definir o editor default para isso deve se selecionar no menu o item Editor Definir Editor 3 1 1 Operadores e delimitadores Operador Descri o Operador Descri o operador de
49. em existir algumas varia es quanto utiliza o Neste curso a SQL ser abordada de acordo com as especificidades do SGBD relacional Oracle 2 1 Hist rico Quando os Bancos de Dados Relacionais estavam sendo desenvolvidos foram criadas linguagens destinadas sua manipula o O Departamento de Pesquisas da IBM desenvolveu a SQL como forma de interface para o sistema de BD relacional denominado SYSTEM R in cio dos anos 70 originalmente cnamada de SEQUEL Structured English Query Language Em 1977 foi revisada e passou a ser chamada de SQL Structured Query Language Em 1986 o American National Standard Institute ANSI publicou um padr o SQL ent o a SQL estabeleceu se como linguagem padr o para Bancos de Dados Relacionais 2 2 Caracter sticas da SQL A SQL possui comandos para a defini o dos dados DDL Data Definition Language comandos para a manipula o de dados DML Data Manipulation Language e uma subclasse de comandos DML a DCL Data Control Language disp e de comandos de controle como Grant e Revoke Algumas caracter sticas da SQL s o e Independ ncia de fabricante A SQL oferecida em praticamente todos os SGBDs e Portabilidade entre computadores A SQL pode ser utilizada desde um PC passando por Workstantions at Mainframes e Redu o de custos com treinamento Devido portabilidade as aplica es podem se movimentar de um ambiente para outro sem necessidade de um novo trein
50. er sistema que re na e mantenha organizada uma s rie de informa es relacionadas a um determinado assunto em uma determinada ordem Um sistema de manuten o de informa es por computador consideradas como significativa ao indiv duo ou a organiza o servida pelo sistema cujo objetivo global manter as informa es atualizadas e torn las dispon veis quando solicitadas para o processo de tomada de decis o O objetivo principal de um Banco de Dados fornecer um ambiente que seja adequado e eficiente para o uso na recupera o e no armazenamento da informa o Uma agenda telef nica cat logo que fica ao lado do aparelho telef nico n o deixa de ser um banco de dados nela temos telefones de v rias pessoas de uma regi o geogr fica organizados pelo sobrenome O computador apenas uma ferramenta eficiente para montar um banco de dados pois nele inserimos muitas informa es e as localizamos de forma extremamente r pida Os bancos de dados implementados em computadores s o divididos em parte L gica e parte F sica A parte f sica constitu da dos equipamentos de hardware necess rios para o armazenamento dos dados processamento das informa es e acesso aos dados como por exemplo processadores mem rias HDs dispositivos de entrada e sa da enfim s o os computadores servidores e clientes A parte l gica o conjunto de softwares que possibilitam a implementa o do modelo projetado o controle de a
51. esso efetivo aos dados e Compilador DML Data Manipulation Language As aplica es fazem seus acessos ao pr compilador DML da linguagem hospedeira que os envia ao Compilador DML Data Manipulation Language onde s o gerados os c digos de acesso ao Banco de Dados Os Sistemas Gerenciadores de Bancos de Dados al m de serem classificados de acordo com o modelo utilizado para o projeto e implementa o tamb m podem ser classificados de como mono usu rio utilizado em computadores pessoais ou multi usu rios por exemplo Cliente servidor localizado ou distribu do se ele for localizado ent o todos os dados ficam armazenados em um mesmo local m quinas ou discos no distribu do os dados ficam distribu dos em v rios locais distintos e ainda um SGBD pode possuir um ambiente homog neo o ambiente composto por um nico SGBD ou um ambiente heterog neo compostos por diferentes SGBDs este assunto ser explorado com mais detalhes em outras disciplinas ao longo do curso 1 4 OBJETOS DO BANCO DE DADOS A criac o de um banco de dados compreende uma s rie de instruc es para defini o de como ser o as caracter sticas do banco onde os dados ser o fisicamente armazenados quantos usu rios irao utilizar o banco simult neamente como ser a configurac o da inst ncia do banco entre outras informacdes Mas n o basta apenas criar o banco necess rio que este banco tenha estruturas que possibilitem o armazenamento de dados o a
52. hum efeito sobre as tabelas base ou outros ndices ATEN O Quando uma tabela eliminada os ndices associados a ela tamb m s o eliminados poss vel criar dois tipos de ndices e ndice exclusivo O Oracle Server cria esse indice automaticamente quando voc define que uma coluna de uma tabela tenha uma restri o PRIMARY KEY ou UNIQUE KEY O nome do ndice o nome dado restri o e ndice n o exclusivo Por exemplo voc pode criar um ndice da coluna FOREIGN KEY para uma jun o em uma consulta a fim de aumentar a velocidade de recupera o 2 18 1 Criando um ndice Sintaxe CREATE INDEX nome ndiceON tabela colunal coluna onde nome_ ndice o nome do ndice tabela o nome da tabela coluna o nome da coluna na tabela a ser indexada Exemplo Criar um ndice para coluna ename da tabela emp SQL gt CREATE INDEX emp_ename_idx 2 ON emp ename poss vel criar varios ndices para uma tabela mas isso n o significa que as consultas ser o aceleradas Cada operac o DML que seja submetida a commit em uma tabela com indices significa que os indices devem ser atualizados Quanto mais indices associados a uma tabela voc tiver maior ser o esforco feito pelo Oracle Server para atualizar todos os indices ap s uma DML Por isso recomenda se a criac o de indices quando e a coluna for usada frequentemente na cl usula WHERE ou em uma condic o de
53. inadas a es no banco de dados Exemplos de privil gios de sistema 16 A Oracle como fun es para evitar confus es ser utilizado o termo personagem tamb m utilizado por Abbey amp Corey em Oracle Guia do usu rio INDEX CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX TABLE CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE SESSION CREATE SESSION ALTER SESSION Sintaxe GRANT privil gio role TO usuario role public WITH ADMIN OPTION onde privil gio o privil gio de sistema a ser concedido usuario o nome do usuario role um personagem fun o ou papel public concede os privil gios de sistema a todos os usu rios WITH ADMIN OPTION permite que o usu rio que est recebendo um privil gio ou as atribui es possa concede las a outros usu rios ou atribui es Exemplo 1 Conceder privil gios de conex o para o usu rio exemplo SQL gt grant connect to exemplo Exemplo 2 Conceder privil gios para criar tabelas views e sequences para o usu rio scott que poder conceder estes privil gios a outros usu rios SQL gt SQL gt GRANT create table create sequence create view 2 TO scott WITH ADMIN OPTION 2 20 2 Privil gios de objeto Os privilegios de objeto permitem que os usuarios acessem e manipulem objetos espec ficos por exemplo inserir linhas em uma tabela de outro usuario Alguns Privil gios d
54. ios BLAKE e CLARK SQL gt GRANT create table create view 2 to manager SQL gt GRANT manager to BLAKE CLARK ATENC O e Para conceder privil gios sobre um objeto ele deve estar no esquema do pr prio usu rio ou ent o o usu rio deve ter recebido os privil gios de objeto WITH GRANT OPTION e Um proprietario de objeto pode conceder qualquer privil gio de objeto sobre o objeto para qualquer outro usu rio ou personagem do banco de dados e O propriet rio de um objeto adquire automaticamente todos os privil gios de objeto sobre seus objetos 2 20 4 Revogando privil gios Sintaxe REVOKE privil gios FROM usu rio cascade contraints onde REVOKE comando para revogar privil gios privil gios lista de privil gios a serem revogados devem ser separados por v rgula FROM usu riolrole indica o usu rio ou role do qual o privil gio ser removido cascade constraints elimina as restri es de integridade de refer ncia definidas pela revoga o usando os privil gios REFERENCES ou ALL Exemplo 1 Revogando privil gios de sistema Revogar os privil gios de create table create sequence e create view do usu rio exemplo SQL gt revoke create table create sequence create view from exemplo Exemplo 2 Revogando privil gios de objeto Revogar os privil gios de exclus o de linhas na tabela SCOTT EMP do usu rio exemplo SQL gt revoke delete 2 on SCOTT EMP from exemplo A
55. itido na inser o Coluna o nome da coluna tipo de dados o tipo de dados e o comprimento da coluna Constraint Est cl usula opcional e especifica as restric es para a coluna ou para a tabela quando o nome da constraint omitido o Oracle assume uma identificac o Convenc es para Nomeac o de Tabelas e Colunas Deve come ar com uma letra Pode ter de 1 a 30 caracteres e Deve conter somente A Z a z 0 9 _ e e N o deve duplicar o nome de outro objeto de propriedade do mesmo usu rio e N o deve ser uma palavra reservada pelo Oracle Server Tipos de Dados Tipo de Dados Descri o VARCHAR2 tamanho Dados de caractere de comprimento vari vel CHAR tamanho Dados de caractere de comprimento fixo NUMBER p s Dados num ricos de comprimento vari vel DATE Valores de data e hora LONG Dados de caractere de comprimento vari vel at 2 gigabytes CLOB Dados de caractere de um byte de at 4 gigabytes RAW e LONG RAW Dados bin rios brutos BLOB Dados bin rios de at 4 gigabytes BFILE Dados bin rios armazenados em um arquivo externo de at 4 gigabytes No exemplo abaixo est sendo criada a tabela DEPT com tr s colunas chamadas DEPTNO DNAME e LOC SQL gt CREATE TABLE dept 2 deptno NUMBER 2 3 dname VARCHAR2 14 4 loc VARCHAR2 13 Table created A instru o Describe utilizada para exibir a estrut
56. la recri la ou reconceder lhe os privil gios de objetos anteriormente concedidos Exemplo 2 Alterar a defini o da view empvu10 para que sejam exibidos apelidos para as colunas da consulta id emp nome e cargo SQL gt CREATE OR REPLACE VIEW empvulO 2 id emp nome cargo 3 AS SELECT empno ename job 4 FROM emp 5 WHERE deptno 10 Exemplo 3 Criar uma view para exibir o nome do departamento e o menor sal rio maior sal rio e a m dia salarial de cada departamento SQL gt CREATE VIEW dept_sum_vu 2 name minsal maxsal avgsal 3 AS SELECT d dname MIN e sal MAX e sal 4 AVG e sal 5 FROM emp e dept d 6 WHERE e deptno d deptno 7 GROUP BY d dname No exemplo anterior esta sendo criada uma view complexa para exibir os nomes de departamento maior sal rio menor sal rio e o sal rio m dio por departamento Note que os nomes alternativos foram especificados para a view Esse um requisito se uma coluna da view derivar de uma fun o ou express o Exemplo 4 Criar uma view identificada por empvu20 com todas as colunas da tabela emp e todos os funcionario do departamento 20 Acrescentar uma clausula para garantir que as manipulac es permanegam no dominio da view SQL gt CREATE OR REPLACE VIEW empvu20 2 AS SELECT 3 FROM emp 4 WHERE deptno 20 5 WITH CHECK OPTION CONSTRAINT empvu20_
57. lo ATENC O Um esquema uma colec o de objetos como por exemplo tabelas views e sequ ncias O esquema pertence a um usuario de banco de dados e tem o mesmo nome do usu rio 2 19 3 Personagens Pode se criar um objeto do banco denominado personagem atribuir privil gios a este personagem e depois atribuir estes privil gios a um usu rio Isso faz com que a concess o e revogac o de privil gios se torne mais f cil de desempenhar e manter Sintaxe CREATE ROLE nome_personagem onde personagem o nome do personagem ou papel a ser criado Exemplo SOL gt CREATE ROLE manager Depois de criado um personagem necess rio atribuir os privil gios a ele 2 20 Privil gios Os privil gios s o os direitos concedidos aos usu rios para executar instru es SQL particulares Os privil gios podem ser de sistema ou objetos O administrador de banco de dados um usu rio de alto n vel que pode conceder aos usu rios acesso ao banco de dados e aos objetos Os usu rios requerem privil gios de sistema para obter acesso aos privil gios de objeto e de banco de dados para manipular o conte do dos objetos no banco de dados Tamb m pode ser fornecido aos usu rios o privil gio de conceder privil gios adicionais a outros usu rios ou a fun es que s o grupos nomeados de privil gios relacionados 2 20 1 Privil gios de sistema Os privil gios de sistema permitem que os usu rios executem determ
58. lor a ser digitado Para atribuir os valores das vari veis de substitui o para as vari veis PL SQL necess rio a utiliza o do amp Exemplo Declare v nome varchar2 30 amp p_nome v sal number 9 2 amp p sal mes 3 1 5 Vari veis de liga o Para declarar uma vari vel de liga o no ambiente SQL Plus utiliza se o comando VARIABLE Exemplo SQL gt VARIABLE q sal anual NUMBER Estas vari veis podem ser declaradas no prompt do SQL ou fora do bloco PL SQL antes d o declare ou depois da Para utilizar estas vari veis no bloco PL SQL deve se precede las de dois pontos para que sejam distinguidas das vari veis PL SQL Exemplo g sal anual v sal 12 A exibi o dos valores armazenados nestas vari veis pode ser feita por meio do comando SQL PRINT Exemplo PRINT g_sal_anual Exemplo 1 programa completo Calcular o salario anual de dado funcionario com base no salario mensal fornecido pelo usuario Exibir o resultado VARIABLE g_sal_anual NUMBER ACCEPT p_nome PROMPT Digite O nome do Instru es FUN TON o oo SQL ACCEPT p_sal_mes PROMPT Digite o valor do Selena na Declare v_nome varchar2 30 v_sal number 9 2 sp nome amp p sal mes Begin Bloco g sal anual v sal 12 PL SOL END PRINT g_sal_anual Instru es SQL Para testar o exemplo 1 escreva o c digo utilizando um editor d textos salve o arquivo com
59. mp where empno 7934 g_nome v_nome g cargo v_cargo end print g_nome print g_cargo ATEN O As instru es SELECT em um bloco PL SQL as consultas devem retornar apenas uma linha mais de uma ou nenhuma linha geram mensagens de erro que podera ser tratado Para consultas que retornam varias linhas devera ser criado um cursos explicito este topico sera abordado posteriormente Exemplo 4 Calcular e exibir a somatoria do salario de todos os funcionarios de um dado departamento SET SERVEOUTPUT ON DECLARE v_soma_sal emp salSTYPE v_deptno NUMBER NOT NULL 10 BEGIN SELECT SUM sal INTO v_soma_sal FRO emp WHERE deptno v_deptno DBMS_output put_line A soma dos sal rios do departamento v_deptno v_soma_sal END 3 1 7 Aninhamento de blocos e Escopo das vari veis As variaveis podem ser utilizadas em determinadas areas do programa um bloco PL SQL pode ser composto por varios blocos aninhados sub blocos cada sub bloco pode possuir suas variaveis No exemplo 5 a variavel valor1 declarada nos 3 sub blocos que comp e o programa mas em cada bloco o seu valor preservado e nao amp gerado nenhum tipo de conflito pois estas variaveis sao locais Exemplo 5 Aninhamento de blocos e escopo de variaveis SET SERVEROUTPUT ON DECLARE bloco 1 v
60. ndo assim o maior grau de simultaneidade e m xima integridade de dados O bloqueio em um banco de dados do Oracle autom tico e n o requer a o do usu rio ATEN O importante lembrar que ap s a emiss o do comando COMMIT n o h mais como reverter a opera o feita anteriormente Exemplo usando rollback Remover todas as linhas da tabela employee SQL gt Delete In cio da transa o transa o ativa uma c pia dos dados antes da altera o armazenada no segmento de rollback 2 From employee Em efetiva o parcial conclus o da opera o A altera o est dispon vel em mem ria as linhas afetadas ficam bloqueadas 14 rows deleted SQL gt ROLLBACK Transa o abortada as modifica es s o desfeitas As linhas afetadas s o desbloqueadas A imagem antiga retirada do segmento de rollback A vers o original mais antiga dos dados no segmento de rollback gravada de volta na tabela Por meio da consist ncia na leitura dos dados que cada usu rio visualiza os dados como eles eram no ltimo commit antes da opera o DML iniciar com isso assegura que Os usu rios n o vejam os dados que estejam sendo alterados As altera es feitas por um usu rio n o interrompam nem entram em conflito com as altera es que outro usu rio esteja fazendo Linhas que est o sendo alteradas s o disponibilizadas apenas para consulta A implementa o da consist ncia de leitura autom tica mant m uma c
61. nto para uma nica instrutora Instrutor Categorias ensinadas L cia da Silva Pires Cisco Oracle Asp Campo Simples Campo multivalorado e Campo calculado armazena um resultado de uma express o matem tica no exemplo abaixo o campo Total o resultado da multiplica o entre os campos Quantidade e Pre o Unit rio Quantidade Pre o Unit rio Total 10 12 00 120 00 Campos Simples Campo calculado O projeto de um banco de dados relacional consiste em um conjunto de tabelas que devem estar relacionadas de maneira que n o exista a necessidade de redund ncias no armazenamento de dados Os relacionamentos entre as tabelas s o estabelecidos por meio dos campos chave prim ria de uma tabela com um campo chave estrangeira de outra tabela ATENC O e Chave Prim ria um campo ou um conjunto de campos que identifica unicamente cada registro da tabela sendo assim um registro n o pode conter neste campo um dado que j esteja armazenado em outro registro e Chave Estrangeira um campo ou conjunto de campos usado para estabelecer um relacionamento entre duas tabelas Na tabela de origem deve ser chave prim ria No exemplo abaixo as tabelas EMP e DEPT cont m respectivamente informac es sobre os funcion rios e sobre os departamentos de uma empresa e para que n o exista necessidade de armazenar os dados sobre o nome do departamento e a localizac o dos mesmos para cada registro de funci
62. o m nimo de R 200 00 desta forma ao tentar inserir valor menores que 200 a restri o ser ativada e ser apresentado um erro para o usu rio Resolu o 1 Restri o criada no nivel da tabela SQL gt CREATE TABLE emp empno NUMBER 4 ename VARCHAR2 10 NOT NULL job VARCHAR2 9 mgr NUMBER 4 hiredate DATE sal NUMBER 7 2 constraint emp_sal_ck check sal gt 200 dry a E o IN MB WUN Resolu o 2 Restri o criada no n vel da coluna SQL gt CREATE TABLE emp empno NUMBER 4 ename VARCHAR2 10 NOT NULL job VARCHAR2 9 mgr NUMBER 4 hiredate DATE sal NUMBER 7 2 check sal gt 200 YHOO BW DN 2 7 1 6 Consultando restric es de uma tabela A instru o SELECT que ser estudada com mais detalhes em um t pico posterior utilizada para realizac o de consultas Sintaxe Select nome_colunal nome_coluna2 nome_colunaN from nome_tabelal nome_tabelaN where condicao No exemplo a seguir ser o consultadas as restric es definidas para a tabela emp SOL gt SELECT CONSTRAINT_NAME CONSTRAINT_TYPE 2 STATUS SEARCH CONDITION 3 FROM USER CONSTRAINTS 4 WHERE TABLE NAME EMP onde CONSTRAINT NAME o nome da restri o CONSTRAINT TYPE tipos de restri es aos quais os campos est o envolvidos onde C
63. oluc o 2 Definic o da chave prim ria no nivel da coluna SQL gt CREATE TABLE dept 2 deptno NUMBER 2 CONSTRAINT dept_deptno_pk PRIMARY KEY 3 dname VARCHAR2 14 4 loc VARCHAR2 13 5 CONSTRAINT dept_dname_uk UNIQUE dname ATENC O A definic o de uma chave prim ria composta deve ser feita no nivel da tabela SQL gt CREATE TABLE pk_composta 2 valorl NUMBER 2 3 valor2 NUMBER 2 4 CONSTRAINT dept_deptno_pk PRIMARY KEY valorl valor2 2 7 1 4 Restrigao FOREIGN KEY E uma restric o de integridade referencial designa uma coluna ou combinac o de colunas como a chave estrangeira e estabelece um relacionamento entre a chave prim ria ou uma chave exclusiva na mesma tabela ou em uma tabela diferente Um valor de chave estrangeira deve corresponder a um valor existente na tabela m e ou ser NULL As chaves estrangeiras s o baseadas nos valores dos dados sendo puramente l gicas e n o ponteiros f sicos Pode ser definida no n vel da tabela ou da coluna Exemplo No exemplo a seguir o DEPTNO foi definida como a chave estrangeira na tabela EMP tabela filha ou dependente ela faz refer ncia a coluna DEPTNO da tabela DEPT tabela m e ou referenciada Resoluc o 1 Restric o Foreign Key definida no nivel da tabela SOL gt CREATE TABLE emp 2 empno NUMBER 4 3 ename VARCHAR2 10 NOT NULL 4 job
64. on rios elas s o relacionadas por meio dos campos DEPTNO que na tabela EMP chave estrangeira e na tabela DEPT chave prim ria Tabela EMP Tabela DEPT Empno Ename Deptn Deptno Dname Loc O 7839 KING 10 zz 10 ACCOUTING NEW YORK 7782 CLARK 10 O 20 RESEARCH DALLAS 7566 JONES 20 SALES CHICAGO 7698 BLAKE 30 40 OPERATIONS BOSTON PK FK Como os dados sobre entidades diferentes s o armazenados em tabelas diferentes talvez voc amp precise combinar duas ou mais tabelas para responder a uma pergunta especifica Por exemplo talvez seja necess rio saber a localizag o do departamento no qual um funcionario trabalha Nesse cen rio voc precisa de informac es da tabela EMP que contem dados sobre funcion rios e da tabela DEPT que cont m informac es sobre departamentos O recurso de relacionar dados de uma tabela a dados de outra permite organizar informac es em unidades gerenci veis separadas E possivel manter logicamente os dados dos funcion rios separadas dos dados dos departamentos armazenando os em uma tabela separada 9 As tabelas emp e dept bem como os dados nela inseridos fazem parte de um banco de dados exemplo dispon vel no Oracle PK Primary Key Chave Primaria FK Foreing Key Chave Estrangeira 1 3 Sistemas Gerenciadores de Bancos de Dados De acordo com Silberschatz um SGBD constitu do por um conjunto de dados asso
65. os grandes no banco de dados em linha dentro de uma linha de tabela ou fora de linha fora da linha de tabela O tipo de dados BFILE binary file arquivo bin rio usado para armazenar objetos grandes bin rios em arquivos do sistema operacional fora do banco de dados O tipo de dados NCLOB objeto grande de caractere do idioma nacional usado para armazenar blocos grandes de dados NCHAR de byte nico ou de bytes m ltiplos de largura fixa no banco de dados dentro e fora de linha Declara o de Vari veis Sintaxe identificador CONSTANT tipo de dados NOT NULL valor para inicializa o expr default onde dentificador o nome da vari vel Os identificadores n o devem ter mais de 30 caracteres O primeiro caracter deve ser uma letra os demais podem ser letras n meros ou s mbolos especiais N o devem ser palavras reservadas nem possuir espa os entre os caracteres CONSTANT restringe as vari veis para que o seu valor n o possa ser alterado as constantes devem ser inicializadas tipo de dados s o tipos de dados escalares compostos referenciais ou LOB NOT NULL indica preenchimento obrigat rio vari veis NOT NULL devem ser inicializadas expr uma express o PL SQL que pode ser uma literal uma outra vari vel ou uma express o que envolve operadores e fun es ATEN O e Para inicializar a vari vel utiliza se operador de atribuic o ou a palavra reservada DEFAULT Se n o for atri
66. os n meros de sequ ncia futuras s o afetados e A sequ ncia deve ser eliminada e recriada para reiniciar a sequ ncia em um n mero diferente e Alguma valida o executada Por exemplo n o poss vel impor um novo MAXVALUE menor do que o n mero de sequ ncia atual 2 16 4 Removendo uma Sequ ncia Ap s remover a sequ ncia do dicion rio de dados n o ser poss vel fazer refer ncia ela Sintaxe DROP SEQUENCE nome seqgii ncia Exemplo Remover a sequencia dept deptno SQL gt DROP SEQUENCE dept deptno ATEN O Somente o propriet rio da sequ ncia ou usu rios com privil gio DROP ANY SEQUENCE podem remover uma sequ ncia 2 17 Sin nimo Um sin nimo um nome alternativo para um objeto do banco de dados Este recurso pode ser empregado para facilitar o acesso aos objetos ou para esconder a verdadeira identidade de um objeto Por exemplo a tabela EMP faz parte do esquema do usu rio Scott supondo que um outro usu rio que tenha privil gios utilize est tabela frequentemente para realizar consulta para isso precisar fazer refer ncia ao esquema e a instru o seria apresentada da seguinte maneira Select from Scott emp com uso de um sin nimo para fazer referencia ao objeto este poderia ser tratado por um nome alternativo por exemplo empregado desta forma qualquer usu rio que necessite fazer uso da tabela emp do usu rio Scott poderia faz lo utilizando o sin
67. palavra chave DISTINCT e Em uma instru o SELECT com as cl usulas GROUP BY HAVING ou ORDER BY e Em uma subconsulta de uma instru o SELECT DELETE ou UPDATE Exemplo 1 Utilizando NEXTVAL Inserir um registro na tabela dept utilizando a sequence dept deptno para fornecer o c digo do departamento o nome do departamento MARKETING e a sua localiza o SAN DIEGO SQL gt INSERT INTOdept deptno dname loc 2 VALUES dept deptno NEXTVAL 3 MARKETING SAN DIEGO Exemplo 2 Utilizando CURRVAL Consultar o pr ximo valor da sequ ncia dept_deptno SQL gt SELECT dept_deptno CURRVAL 2 FROM dual 2 16 3 1 Alterando uma Sequ ncia poss vel alterar as especifica es de uma sequ ncia para isso necess rio informar o cl usula que sofrer altera es as demais cl usulas permanecer o com os valores anteriores Sintaxe Alter SEQUENCE nome segii ncia INCREMENT BY n MAXVALUE n NOMAXVALUE MINVALUE n NOMINVALUE CYCLE NOCYCLE CACHE n NOCACHE La SS l Exemplo 3 Aterar a sequence dept_deptno o valor maximo devera ser modificado para 99999 e as demais configurac o dever o permanecer as mesmas SQL gt ALTER SEQUENCE dept_deptno 2 MAXVALUE 999999 ATENC O e Somente o propriet rio ou usu rios que possuam o privil gio ALTER podem alterar a sequ ncia e Somente
68. pertencam ao departamento 10 da tabela EMP DECLARE v_deptno emp deptno TYPE 10 BEGIN DELETE FROM emp WHERE deptno v_deptno END 3 1 11 Controle de Transac es O controle da l gica das transa es realizado com as instru es COMMIT e ROLLBACK SQL tornando permanentes as alterac es em alguns grupos de bancos de dados e descartar outros Assim como ocorre com o Oracle Server as transa es DML s o iniciadas no primeiro comando seguindo uma instru o COMMIT ou ROLLBACK e s o finalizadas na pr xima instru o COMMIT ou ROLLBACK correta Essas a es podem ocorrer em um bloco PL SQL ou como resultado dos eventos no ambiente do host por exemplo o encerramento de uma sess o SQL Plus automaticamente compromete a transa o pendente Para saber mais consulte Livros Livro Comentarios SILBERSCHATZ Abraham KORTH Henry F e SUDASRSHAN S Sistema de Banco de dados Sao Paulo Makron Books 1999 Este livro aborda os principais conceitos sobre bancos de dados Urman Scott Oracle 8 Programa o PL SQL McGraw Hill 1999 Este um livro que trata do comeco ao fim do assunto PL SQL aborda o tema com profundidade e assuntos complementares nao tratados nesta apostila uma tima refer ncia para quem quiser se aprofundar mais Est uma edi o Portuguesa Oliveira Celso H Poderoso de Oracle 8i Pl Sql Guia De Consulta
69. plusw exe A seguir ser solicitada a identifica o do usu rio e Figura 1 icone do nome do banco que ser utilizado Alguns usu rios e SQLI Plus senhas default s o Log On Usuario Senha Privil gios Scott Tiger usuario User Name scort Internal Oracle administrador 5 ens a y E O Ap s a autentica o da conex o ser Eauzword aberto o ambiente do SQL Plus A Base NR ferramenta oferece um menu com opc es para manipulac o de arquivos Canal configura o do ambiente e outras As instru es s o digitadas no prompt e submetidas execu o ap s o enter Figura 2 Autentica o e conex o Tamb m podem ser executados Como ferramenta gr fica poder ser utilizado por exemplo o ORACLE NAVIGATOR uma ferramenta gr fica do Oracle que permite ao desenvolvedor a cria o e manuten o de objetos no banco de dados PL SQL a linguagem procedural do SQL do ORACLE composta essencialmente de todos os comandos SQL padr o e outras instru es tais como estruturas de sele o estruturas de repeti o recursos de manipula o de cursores outras que permitem utilizar o SQL de forma procedural Ser estudada com mais detalhes na parte 3 da disciplina arquivos com extens o sql para isso deve se digitar o comando execute seguido do caminho e nome do arquivo Todos os exemplos apresentados neste material e exercicios serao trabalhados a principio neste ambien
70. s de acordo com o seu contexto de utiliza o Outra caracteristica dos dados que eles sao estaticos ou seja permanecem no mesmo estado at que sejam modificados por algum processo manual ou automatico Os dados tornam se informa es quando s o associados a um contexto e transmitem significados l gicos s pessoas Nome da Pizza Ingredientes C digo Pizza Pre o da pizzza Apelido da Pizza Melineono 55 03909 70 22 00 Mesoz ica Os dados s o armazenados em campos Um campo a menor estrutura dentro de um banco de dados relacional Cada campo possui um conjunto de caracter sticas tais como identificador do campo tipo de dado que ser armazenado tamanho do dado e restri es por exemplo Campo Identificador Tipo de dado Tamanho Restri es do campo Nome da Pizza nome pizza alfanum rico 20 preenchimento obrigat rio Ingredientes ingredientes num rico 2 preenchimento obrigat rio chave inteiro estrangeira C digo da Pizza codigo_pizza num rico 8 chave prim ria inteiro Pre o da pizza preco num rico real 5 nenhuma Apelido da pizza apelido alfanum rico 15 n o pode ser repetido em outro registro O conjunto de campos sobre um assunto comp e um registro Um registro equivale a uma linha de uma tabela e tamb m conhecido como tupla por exemplo Tabela CARD PIO Nome da Pizza Ingredientes Codigo Pre o Apelido
71. sert Inser o de dados Update Altera o de dados Delete Remo o de dados Commit Confirma o das manipula es Rollback Desist ncia das manipula es Select Seleciona linhas de dados de tabelas ou vis es 2 5 Instru es DCL Os comandos da s rie DCL Data Control Language s o utilizados para controlar os privil gios de usu rios com eles poss vel e permitir a um usu rio que se conect ao banco 7 Select Existem autores que a classificam com instru o do DRL Linguagem para Recupera o de Dados e permitr que crie objetos no banco e permitir que consulte objetos do banco entre outros e cancelar os privil gios de um usu rio ou personagem Para isso est o dispon veis as instru es Grant utilizada para conceder privil gios aos usu rios Revoke Utilizada para cancelar os privil gios dos usu rios 2 6 Ambiente de trabalho Ser utilizada como ferramenta para edi o e execu o de instru es SQL o SQL Plus O SQL PLUS uma ferramenta da Oracle usada como interface para acesso ao banco de dados Oracle n o gr fica e capaz de reconhecer e executar instru es SQL e PL SQL As instru es podem ser escritas diretamente no prompt ou em um editor de textos O SQL Plus pode ser acessado a partir de um duplo clique no icone do aplicativo ou selecionando de diretamente o arquivo que est localizado no diret rio SQL Plus 3 2 c oracle ora81 bin sql
72. te Para a edi o de instru es no SQL PLUS deve se considerar algumas recomendacoes e Nao feita distinc o entre mai sculas de min sculas a menos que indicado e As instru es SQL podem ser digitadas em uma ou mais linhas e As palavras chave n o podem ser divididas entre as linhas nem abreviadas e As cl usulas s o em geral colocadas em linhas separadas para melhor legibilidade e facilidade de edi o e As guias e endenta es podem ser usadas para tornar o c digo mais leg vel Em geral as palavras chave s o digitadas em letras mai sculas todas as outras palavras como nomes de tabela e colunas s o digitadas em min sculas e Dentro do SQL Plus uma instru o SQL digitada no prompt SQL e as linhas subsequentes s o numeradas Isso chama se buffer de SQL Somente uma instru o pode ser a atual a qualquer momento dentro do buffer 2 7 Criando Tabelas Est uma opera o DDL Para criar uma tabela necess rio que o usu rio tenha privil gio e uma rea para armazenamento A sintaxe simplificada para cria o de tabelas Sintaxe CREATE TABLE esquema tabela nome da coluna tipo do dado DEFAULT expr constraint da coluna oF constraint da tabela onde Esquema o nome do propriet rio da tabela quando omitido a tabela criada no esquema do usu rio corrente Tabela o nome da tabela DEFAULT expr especifica um valor default que ser utilizado quando um dado for om
73. transa o fonte Silberschatz 1999 Uma transa o pode estar e ativa enquanto suas opera es est o sendo executadas e em efetiva o parcial ap s a execu o da ltima opera o antes do commit e em falha quando alguma das opera es n o pode ser realizada normalmente e abortada quando a transa o desfeita rollback e o banco de dados volta ao estado anterior ao in cio da transa o e em efetiva o quando conclu da com sucesso commit Exemplo usando commit Atualizar a tabela EMP e definir o n mero de departamento para o funcion rio 7782 Clark como 10 e depois confirmar a altera o SQL gt UPDATE emp Inicio da transac o transac o ativa uma c pia dos 2 SET deptno 10 dados antes da alterac o armazenada no segmento de rollback 3 WHERE empno 7782 Em efetivac o parcial conclus o da operac o A altera o est dispon vel em mem ria as linhas afetadas ficam bloqueadas SQL gt COMMIT Em efetivac o a alterac o amp refletida fisicamente no banco As linhas afetadas sao desbloqueadas A imagem antiga retirada do segmento de rollback 1 Segmento de rollback arquivo que armazena uma c pia dos dados de uma tabela que est o envolvidos em transa es O bloqueio impl cito ocorre para todas as instru es SQL exceto SELECT O mecanismo de bloqueio default do Oracle automaticamente usa o n vel mais inferior da restri o aplic vel fornece
74. ura de uma tabela SQL gt DESCRIBE dept Name Null Type DEPTNO NUMBER 2 DNAME VARCHAR2 14 LOC VARCHAR2 13 2 7 1 Restri es constraints As restri es imp em regras que podem ser no nivel da coluna ou no nivel da tabela Sao utilizadas para impedir que dados inv lidos sejam digitados nas tabelas garantindo assim a consist ncia dos dados Os seguintes tipos de restri o sao v lidos no Oracle e NOT NULL Imp e a inser o obrigat ria de dados nas colunas com esta restric o e UNIQUE Campos com est restri o nao aceitam dados com valores j inseridos em outros registros e PRIMARY KEY Define uma ou mais colunas como chave prim ria da tabela e FOREIGN KEY Define uma ou mais colunas como chave estrangeira da tabela e CHECK Especifica uma lista de valores que ser o utilizados para validar a inserc o de um dado Todas as restri es s o armazenadas no dicion rio de dados as restri es nao nomeadas recebem ser o identificadas pelo oracle com o formato SYS_cn onde n um n mero inteiro para criar um nome de restric o exclusivo As restric es podem ser definidas enquanto a tabela est sendo criada ou adicionadas ap s sua criac o podendo ainda ser desativadas temporariamente 2 7 1 1 Restri o NOT NULL A restri o NOT NULL assegura que os valores nulos n o sejam permitidos na coluna As colunas sem uma restric o NOT NULL podem
75. ut_line O salario anual de v_nome v_sal_anual END Note que no exemplo 2 n o est sendo utilizada a vari vel de liga o g_sal_anual pois a solicita o da exibi o do resultado do processamento est sendo feita na rea execut vel do bloco PL SQL 3 1 6 Instru es SELECT em PL SQL Recuperar dados do banco de dados com SELECT Sintaxe SELECT colunas INTO vari veis registro FRO tabela WHERE condi o onde colunas lista de colunas que retornar o dados consulta podem incluir fun es de linhas de grupo ou express es SQL into cl usula obrigat ria usada para especificar os nomes das vari veis que armazenar o os valores que o SQL retornar a partir da cl usula SELECT Deve se oferecer uma vari vel para cada coluna seguindo a mesma ordem variaveis nome das vari vies que ir o armazenar o valor recuperado registro o registro PL SQL para armazenar os valores recuperados tabela especifica o nome da tabela do banco de dados condi o composta de nomes de coluna express es constantes e operadores de compara o incluindo as vari veis PL SQL e operadores Exemplo 3 Recuperar o nome e o cargo do funcion rio para o c digo indicado variable g nome varchar2 15 variable g cargo varchar2 15 DECLARE v nome emp enameStype v_cargo emp job type BEGIN select ename job into v_nome v_cargo from e

Apostila SQL & PL/SQL - Prof.Toninho

Contents

Download Pdf Manuals

Related Search

Related Contents

Apostila SQL &amp; PL/SQL - Prof.Toninho

Contents

Download Pdf Manuals

Related Search

Related Contents

Apostila SQL & PL/SQL - Prof.Toninho