Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 BANCO DE DADOS RELACIONAL UM BANCO DE DADOS RELACIONAL USA RELAÇÕES OU TABELAS BIDIMENSIONAIS PARA ARMAZENAR INFORMAÇÕES. POR EXEMPLO:, VOCE GOSTARIA DE ARMAZENAR INFORMAÇÕES SOBRE TODOS OS FUNCIONARIOS DA SUA EMPRESA, EM UM BANCO DE DADOS RELACIONAL. VOCE CRIARA ALGUMAS TABELAS PARA ARMAZENAR INFORMAÇÕES SOBRE SEUS EMPREGADOS, COMO UMA TABELA DE FUNCIONARIOS, UMA TABELA DE DEPARTAMENTO E UMA TABELA DE SALARIO, CARGO, ETC..... 2 MODELOS DE DADOS MODELOS SÃO RASCUNHOS DO PROJETO, ENGENHEIROS CONSTROEM UM MODELO DE UM CARRO PARA TRABALHAR OS DETALHES ANTES DE COLOCAREM O MESMO NA PRODUÇÃO. DA MESMA MANEIRA, PROJETISTAS DE SISTEMAS, PROJETAM DESENVOLVENO MODELOS PARA EXPLORAR IDEIAS E MELHORAR O ENTENDIMENTO DO PROJETO DO BANCO DE DADOS. 3 MODELO DE ENTIDADE E RELACIONAMENTO CRIAR UMA ENTIDADE DE RELACIONAMENTO A PARTIRDE ESPECIFICAÇÕES OU NARRATIVAS DO NEGOCIOS. CENARIO: DEPARTAMENTO POSSUI UM OU MAIS FUNCIONARIOS EXISTEM DEPARTAMENTOS QUE NÃO POSSUEM FUNCIONARIOS TB_DEPTO CODDEPTO NOMEDEPTO TB_FUNCIONARIO CODDEPTO MATRICULA NOMEFUNC 4 RELACIONANDO MULTIPLAS TABELAS CADA LINHA DE DADOS EM UMA TABELA É IDENTIFICADA UNICAMENTE POR UMA PRIMARY KEY ( PK ). VOCE PODE LOGICAMENTE RELACIONAR DADOS A PARTIR DE MULTIPLAS TABELAS USANDO AS FOREIGN KEY ( FK). CODDEPTO NA TB_DEPTO É PRIMARY KEY CODDEPTO NA TB_FUNCIONARIO É FOREIGN KEY CADA TABELA CONTEM DADOS QUE DESCREVEM EXATAMENTE UMA ENTIDADE. COMO OS DADOS ESTAO EM ENTIDADES DIFERENTES, ESTES SÃO ARMAZENADOS EM TABELAS DIFERENTES. VOCE PODE NECESSITAR COMBINAR DUAS OU MAIS TABELAS PARA RESPONDER UMA QUESTAO PARTICULAR. A HABILIDADE DE RELACIONAR DADOS DE UMA TABELA PARA OUTRA, PERMITE VOCE ORGANIZAR INFORMAÇÕES EM UNIDADES GERENCIAVEIS SEPARADAS. TB_FUNCIONARIO CODDEPTO MATRICULA NOMEFUNC TB_DEPTO CODDEPTO NOMEDEPTO 5 PROPRIEDADES DE UM BANCO DE DADOS RELACIONAL • PODE SER ACESSADO E MODIFICADO POR EXECUÇÕES DE COMANDOS SQL • CONTEM UMA COLEÇÃO DE TABELAS COM PONTEIROS FISICOS • UTILIZA UM CONJUNTO DE OPERADORES. 6 BANCO DE DADOS ORACLE - CONCEITOS BÁSICOS LIMITES DO ORACLE RDBMS ITEM LIMITE Tabela na Base de dados Não há limites. Linhas por Tabelas Não há limites Colunas por tabelas 254 Índices por tabelas Não há limites Tabelas ou views joined em uma query Não há limites Níveis de ninho de subqueries 30 Caracteres em um nome 255 Colunas por índices 16 7 SQL ( STRUCTURED QUERY LANGUAGE ) SQL PERMITE VOCE COMUNICAR COM O SERVIDOR E TER AS SEGUINTES VANTAGENS: • EFICIENCIA • FACIL DE APRENDER E USAR • FUNCIONALIDADE COMPLETA. SQL PERMITE VOCE DEFINIR, RETORNAR E MANIPULAR DADOS EM TABELAS. 8 DECLARAÇÕES SQL DML – DATA MANIPULATION LANGUAGE • SELECT • INSERT • UPDATE • DELETE DDL – DATA DEFINITION LANGUAGE • CREATE • ALTER • DROP • RENAME • TRUNCATE DCL – DATA CONTROL LANGUAGE • GRANT • REVOKE DTL – DATA TRANSACTION LANGUAGE • COMMIT • ROLLBACK 9 DECLARAÇÃO DDL – DATA DEFINITION LANGUAGE CREATE CRIA OBJETOS NO BANCO DE DADOS. Sintaxe: CREATE TABLE NOME-DA-TABELA ( COLUNA1 TIPO-DE-DADO null/not null, COLUNA2 TIPO-DE-DADO null/not null, ... ); CRIANDO TABELAS TIPOS DE DADOS(DATATYPES) Char(n) Tamanho Fixo, pode conter uma seqüência de 1 a 255 bytes alfanuméricos; Varchar2(n) Tamanho Variável, pode conter uma seqüência de 1 a 2000 bytes - alfanuméricos. Long Tamanho Variável até 2 Gigabytes alfanuméricos Nota : só pode existir uma coluna long em cada tabela Number(p,s) Numérico com sinal e ponto decimal, Sendo precisão de 1 a 38 dígitos Raw Binário - Variável até 255 bytes Long Raw Binário - Variável até 2 gigabytes - imagem Date Data c/ hora, minuto e segundo 10 COMANDO CREATE TABLE CREATE TABLE DEPT (DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR2(14), LOC VARCHAR2(13)); CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB CHAR(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) NOT NULL); 11 ALTER ALTERA OBJETOS NO BANCO DE DADOS. Sintaxe: RENAME ALTER TABLE NOME-DA-TABELA RENAME TO NOVO-NOME-DA-TABELA; COLUMN ALTER TABLE NOME-DA-TABELA ADD COLUNA TIPO-DE-DADO; MODIFY ALTER TABLE NOME-DA-TABELA MODIFY COLUNA TIPO-DE-DADO; DROP COLUMN ALTER TABLE NOME-DA-TABELA DROP COLUMN COLUNA; 12 DROP EXCLUI TABELAS. Sintaxe: DROP TABLE NOME-DA-TABELA RENAME RENOMEA TABELAS. Sintaxe: RENAME TABLE NOME-DA-TABELA TO NOVO-NOME-DA-TABELA TRUNCATE LIMPA OS DADOS DA TABELA Syntaxe: TRUNCATE TABLE [ { database_name .[ schema_name ] . | schema_name . } ] table_name [ ; ] 13 DECLARAÇÃO DML – DATA MANIPULATION LANGUAGE SELECT RETORNA DADOS DO BANCO DE DADOS Sintaxe: SELECT COLUNA FROM NOME-DA-TABELA WHERE PREDICATES; INSERT INSERE NOVAS LINHAS Sintaxe: INSERT INTO NOME-DA-TABELA (COLUNA-1, COLUNA-2, ... COLUNA-N) VALUES (VALOR-1, VALOR-2, ... VALOR-n); UPDATE ALTERA LINHAS JÁ EXISTENTES Sintaxe: UPDATE NOME-DA-TABELA SET COLUNA = EXPRESSÃO WHERE CONDIÇÕES; 14 DELETE EXCLUI LINHAS. Sintaxe: DELETE FROM NOME-DA-TABELA WHERE CONDIÇÕES; DECLARAÇÃO DTL – DATA TRANSACTION LANGUAGE COMMIT, ROLLBACK GERENCIA AS ALTERAÇÕES FEITAS POR DECLARAÇÕES DML. 15 DECLARAÇÃO DCL – DATA CONTROL LANGUAGE GRANT, REVOKE CONCEDE E EXCLUI DIREITOS DE ACESSO. Syntaxe: GRANT PRIVILEGES ON OBJECT TO USER; GRANT SELECT, INSERT, UPDATE, DELETE ON SUPPLIERS TO SMITHJ; GRANT ALL ON SUPPLIERS TO SMITHJ GRANT SELECT ON SUPPLIERS TO PUBLIC; Syntaxe: REVOKE PRIVILEGES ON OBJECT FROM USER; REVOKE DELETE ON SUPPLIERS FROM ANDERSON; REVOKE ALL ON SUPPLIERS FROM ANDERSON REVOKE ALL ON SUPPLIERS FROM PUBLIC; 16 DECLARAÇÕES BASICAS DO SELECT SELECT [distinct] {* , coluna , [alias], ....} FROM tabela WHERE condições GROUP BY colunas HAVING condições do grupo ORDER BY {coluna, expr} [asc / desc] ALIAS PODE SER USADO PARA NOMEAR UMA COLUNA * refere-se a todas as colunas DISTINCT pode ser usado para eliminar duplicações FROM indica de qual tabela as colunas originam WHERE restringe a consulta para registros que satisfaçam uma condição. pode conter valores para as colunas, expressões e literais. AND / OR pode ser usado na clausula where para construir condições mais complexas. and tem prioridade sobre or. ( ) Pode ser usado para forçar uma prioridade ORDER BY sempre aparece por ultimo. especifica ordem de classificação. uma ou mais colunas podem ser especificadas 17 ASC indica ordem ascendente na classificação,sendo o default DESC reverte a ordem da classificação default, devendo ser especificado depois do nome da coluna 18 FUNÇÕES SQL OPERADORES ARITMÉTICOS ( + ), ( - ),( * ), ( / ) EXEMPLO: Select ename, sal, comm, sal+comm from emp where job = 'salesman'; EXEMPLO: Select ename, sal, comm from emp Where comm > 0.25 * sal EXEMPLO: Select ename, comm/sal, comm,sal from emp where job = 'Salesman' order by comm/sal asc; EXEMPLO: Select ename, sal, comm,12*(sal+comm) from emp where job = 'Salesman'; EXEMPLO: Select ename, sal, sal/22, round(sal/22,0), round(sal/22,2) from emp where emp where deptno = 30; 19 EXEMPLO: select ename, sal, sal/22, trunc(sal/22,0=, trunc(sal/22,2) from emp where deptno = 30; FUNÇÕES DE GRUPO AVG, COUNT, MAX, MIN, SUM Funções EXEMPLO: Resultado AVG AVG(SAL) MEDIA DO VALOR DE SAL COUNT COUNT(CO MM) COUNT(*) CONTA NUMERO DE LINHAS COM COMM CONTA O NUMERO DE LINHAS CFE. MAX MAX(SAL) MAIOR VALOR DE SAL. MIN MIN(SAL) MÍNIMO VALOR DE SAL. SUN SUM(SAL) SOMATÓRIA DOS VALORES DE COMM UTILIZANDO FUNÇÕES DE GRUPO EXEMPLO: 1) SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO; EXEMPLO: 2) SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO; 20 FUNÇÕES DE DATA FUNÇÕES EXEMPLO: RESULTADO ADD_MONTHS ADD_MONTHS(HIREDATE,5) ADICIONA 5 MESES NA DATA HIREDATE MONTHS_BETWEEN MONTHS_BETWEEN(HIREDATE,SY SDATE) CALCULA O NÚMERO DE MESES BETWEEN ENTRE AS DATAS NEXT_DAY NEXT_DAY(HIREDATE,'FRIDAY') PROCURA UMA SEXTA- FEIRA APÓS HIREDATE LAST_DAY LAST_DAY(SYSDATE) RETORNA A DATA TOMANDO COMO PARAMETRO O 'FMT' TRUNC TRUNC(SYSDATE,FMT) TRUNCA A DATA PARA A PRIMEIRA DATA DO 'FMT' FUNÇÕES DE CONVERSÃO Funções EXEMPLO: Resultado TO_CHAR TO_CHAR(SYSDADE,’YY’) CONVERTE COLUNAS DO TIPO NUMBER E DATA PARA CHAR. TO_DATE TO_DATE(‘15/05/90’,’DD/MM/YY’) CONVERTE COLUNAS DO TIPO CHAR PARA O FORMATO DE DATA. TO_NUMBE R TO_NUMBER(SUBSTR(‘$150’,2,3)) CONVERTE AS 3 ULTIMAS (EM FORMATO CHAR) PARA MUMBER. USANDO && PARA SUBSTITUIR VARIÁVEL USANDO O DUPLO &, VOCÊ PODE REUSAR UMA VARIÁVEL SEM COLOCAR NO PROMPT A CADA VEZ. EXEMPLO: SELECT EMPNO, ENAME, JOB, &&_NOME_DA_COLUNA FROM EMP ORDER BY &COLUMN_NAME 21 RESULTADO: ENTER VALUE FOR NOME_DA_COLUNA: DEPTNO EMPNO ENAME JOB DEPTONO ----------- ------------------ ------------------------- -------------------- 7839 King President 10 7782 Clark Manager 10 COMANDOS DE COMPARAÇÃO DE VALORES BETWEEN FAZ UMA PESQUISA ENTRE UMA FAIXA DE VALORES PARA UM CAMPO DA TABELA. NOT BETWEEN FAZ UMA PESQUISA DESCARTANDO UMA FAIXA DE VALORES. EXEMPLO: SELECT * FROM FUNCIONARIO WHERE IDADEFUNC BETWEEN 18 AND 30; SELECT * FROM FUNCIONARIO WHERE IDADEFUNC NOT BETWEEN 18 AND 30; IN CONSULTA A PRESENÇA DE UM CAMPO EM UM CONJUNTO DE VALORES 22 NOT IN CONSULTA A NÃO PRESENÇA DE UM CAMPO EM UM CONJUNTO DE VALORES EXEMPLO: SELECT * FROM FUNCIONARIO WHERE CDCIDFUNC IN ( 10, 20, 30 ,35); LIKE COMPARA A EXISTÊNCIA DE UMA CARÁTER EM UMA DETERMINADA POSIÇÃO EM UMA STRING. ‘_‘ TESTA A EXISTÊNCIA DE UM CARÁTER NÃO NULO NA POSIÇÃO; ‘%’ REPRESENTA QUALQUER SEQÜÊNCIA DE N CARACTERES. NOT LIKE COMPARA A NÃO EXISTÊNCIA DE UM CARÁTER EM UMA DETERMINADA POSIÇÃO EM UMA STRING. EXEMPLO: SELECT * FROM FUNCIONARIO WHERE NOMEFUNC LIKE ‘RUDINEY%’ ESTA CONSULTA TRAZ TODOS OS FUNCIONÁRIOS COM PRIMEIRO NOME RUDINEY NÃO IMPORTA O RESTO DO NOME. EXEMPLO: SELECT * FROM FUNCIONARIO WHERE NOMEFUNC LIKE’%DA SILVA’ 23 NESTE CASO LOCALIZA TODOS OS FUNCIONÁRIOS COM SOBRENOME “ DA SILVA” . NULL VERIFICA SE O VALOR DO CAMPO COMPARADO É ZERO OU VAZIO; EXEMPLO: NOT NULL VERIFICA SE O VALOR DO CAMPO COMPARADO NÃO É ZERO OU VAZIO; EXEMPLO: EXIST VERIFICA SE O RESULTADO DO CÁLCULO DA SUBCONSULTA REPRESENTADA POR “SELECT * FROM “ NÃO É VAZIA. EXEMPLO: SELECT X FROM A WHERE EXISTS (SELECT * FROM B WHERE Y=X) O CAMPO X DA RELAÇÃO A SERÁ SELECIONADO, SE EXISTIR UM ELEMENTO Y=X NA RELAÇÃO B. 24 NOT EXISTS VERIFICA SE O RESULTADO DO CALCULO DA SUBCONSULTA REPRESENTADA POR “SELECT * FROM “ É VAZIA. FUNÇÕES COUNT - NÚMERO DE VALORES DA COLUNA SUM - SOMA DOS VALORES DA COLUNA AVG - MÉDIA DOS VALORES DA COLUNA MAX - MAIOR VALOR DA COLUNA MIN - MENOR VALOR DA COLUNA COUNT EXEMPLO: SELECT COUNT(*) FROM ALUNOS; SUM EXEMPLO: SELECT SUM(IDADE) FROM ALUNOS; AVG EXEMPLO: SELECT AVG(IDADE) FROM ALUNOS; MAX , MIN 25 EXEMPLO: SELECT MAX(IDADE), MIN(IDADE) FROM ALUNOS; GRUPO BY Forma grupos com as tuplas da tabela especificada na cláusula from, que possuem o mesmo valor no atributo especificado na cláusula grupo by; Para ter resultado em ordem, deve ser especificado também a clausula order by(após a clausula grupo by). EXEMPLO: SELECT CURSO_ALU, COUNT(CURSO_ALU) FROM ALUNOS GROUP BY CURSO_ALU; HAVING Having é para os grupos o que where é para as linhas. Em outras palavras, é utilizado para eliminar grupos, onde where é utilizado para eliminar linhas. EXEMPLO:: SELECT CURSO_ALU, AVG(IDADE) FROM ALUNOS GROUP BY CURSO_ALU HAVING AVG(IDADE) > 18; 26 UPPER / LOWER Maiúsculo, minúsculo EXEMPLO:: SELECT UPPER ('nome') FROM DUAL; SELECT LOWER ('NOME') FROM DUAL; FUNÇÕES NUMÉRICAS ABS ( col / m ) VALOR ABSOLUTO DE UMA COLUNA OU DO VALOR M ABS(-5)=5 EXEMPLO:: SELECT ABS(-5) FROM DUAL; CEIL(col / n) ARREDONDA PARA MENOR INTEIRO MAIOR OU IGUAL A COLUNA, EXPRESSÃO OU VALOR N CEIL(5.1) = 6 CEIL(5)= 5 EXEMPLO:: SELECT CEIL(5.1) FROM DUAL; 27 FLOOR (col / n) ARREDONDA PARA O MAIOR INTEIRO MENOR OU IGUAL A COLUNA, EXPRESSÃO OU VALOR N FLOOR(5.1)=5 FLOOR(5)=5 EXEMPLO:: SELECT FLOOR(5.1) FROM DUAL; MOD (M,N ) RESTO DA DIVISÃO DE M POR N MOD(5,2)=1 EXEMPLO:: SELECT MOD(5,2) FROM DUAL; ROUND ARREDONDA COLUNA OU VALOR M PARA NCASAS DECIMAIS ROUND(2.3456,2)= 2,35 EXEMPLO:: SELECT ROUND(2.3456,2)= 2,35 FROM DUAL; 28 SQRT RAIZ QUADRADA DE COLUNA OU SQRT (4) = 2 EXEMPLO: SELECT SQRT(4) FROM DUAL; TRUNC Trunca coluna ou valor para n casas decimais EXEMPLO:: SELECT TRUNC(2.3456,2) FROM DUAL SELECT TRUNC (SYSDATE) FROM DUAL FUNÇÕES DE CARACTERES ASCII CODIGO ASCII DO PRIMEIRO CARACTER DE C EXEMPLO:: SELECT ASCII(‘AGUA’) FROM DUAL; CHR CARACTERE DE CODIGO ASCII IGUAL A N CHR(65) = ‘A’ EXEMPLO:: SELECT CHR(65) FROM DUAL; 29 INITCAP RETORNA CADA PALAVRA DE UMA STRING COM O PRIMEIRO CARACTERE EM MAISCULO INITCAP(‘SÃO PAULO’) = ‘SÃO PAULO’ EXEMPLO:: SELECT INITCAP(‘SÃO PAULO’) FROM DUAL; INSTR ( C1, C2 [,M[,N]]) A POSIÇÃO DA N-ESIMA OCORRENCIA DE C2 EM C1, INICIANDOUSCA NA POSIÇÃOM. VALOR 1 É ASSUMIDO PARA M E N EM CASO DE OMISSÃO INSTR (‘SÃO PAULO’, ‘A’, 1, 2)=6 EXEMPLO:: SELECT INSTR (‘SÃO PAULO’, ‘A’, 1,2) FROM DUAL LENGTH RETORNA O NUMERO DE CARACTERES LENGTH (‘SÃO PAULO’)=9 EXEMPLO:: SELECT LENGTH(‘SAO PAULO’) FROM DUAL;30 LPAD (C1, N [,C2]) CONCATENA C2 Á ESQUERDA DE C1, O SUFICIENTE PARA QUE C1 ATINJA O ASSUMIDO ‘ ’ ( ESPAÇOS) LPAD(‘SP’, 5 ‘ - ’)= ‘---SP’ EXEMPLO:: SELECT LPAD(‘SP’, 5 ‘ - ’) FROM DUAL LTRIM (C, CONJ_C) REMOVE TODOS OS CARACTERES DE C ATE O PRIMEIRO CARACTER QUE NÃO ESTIVER PRESENTE EM CONJ-C LTRIM (‘AGUA’, ‘AG’)=’UA’ EXEMPLO:: SELECT LTRIM (‘AGUA’, ‘AG’) FROM DUAL REPLACE (C,DE_C [,PARA_C]) TROCA TODOS AS OCORRENCIAS DE DE_C EM C PARA REPLACE (‘XUXU’,’X’, ‘CH’)=’CHUCHU’ EXEMPLO:: SELECT REPLACE (‘XUXU’,’X’, ‘CH’) FROM DUAL RPAD (C1, N [,C2]) CONCATENA C2 A DIREITA DE C1, O SUFICIENTE PARA QUE C1 ATINJA O TAMANHO N. CASO C2 SEJA OMITIDA É ASSUMIDO ‘ ‘ ( ESPAÇOS ) RPAD(‘SP’, 5 ‘ - ’)= ‘SP---’ EXEMPLO:: SELECT RPAD(‘SP’, 5 ‘ - ’)= ‘SP---’ FROM DUAL 31 RTRIM (C, CONJ_C) REMOVE TODOS OS CARACTERES DE C, A PARTIR DO ULTIMO, ATE ENCONTRAR O PRIMEIRO CARACTER QUE NÃO ESTIVER PRESENTE EM CONJ_C RTRIM(‘AGUA’, ‘UA’)=’AG’ EXEMPLO:: SELECT RTRIM(‘AGUA’, ‘UA’)=’AG’ FROM DUAL SUBSTR (C,M[,N]) A SUBSEQUENCIA DE C COM INICIO NA POSIÇÃO M COM N CARACTERES. CASO N SEJA OMITIDO RETORNA DA POSIÇÃO M ATE O FIM SUBSTR(‘SÃO PAULO’, 5) = ‘PAULO’ EXEMPLO:: SELECT SUBSTR(‘SÃO PAULO’, 5) FROM DUAL DECODE (EXPR, S1, R1, SE EXPR FOR IGUAL A S1, ENTAO, DEVOLVE R1, [S2,R2] … DEFAULT) CASO DEFAULT SEJA OMITIDO E NAO HAJA ‘CASAMENTO’, O VALOR NULL É RETORNADO EXEMPLO:: SELECT DECODE (CODFORNECEDOR , 1, ‘DAVENE’, 2, ‘WELLA’, ‘FORNECEDOR NÃO CADASTRADO’) FROM TB_PRODUTOS GREATEST (EXPR1, EXPR2, ...) DEVOLVE O MAIOR VALOR DA LISTA EXPR1, EXPR2, .... EXEMPLO:: SELECT GREATEST ( 100,200,300) FROM DUAL 32 LEAST (EXPR1, EXPR2, ...) DEVOLVE O MENOR VALOR DA LISTA EXPR1, EXPR2, ... EXEMPLO:: SELECT LEAST ( 100,200,300) FROM DUAL NVL (COL/VAL, X) CASO COLUNA OU VALOR SEJA NULO, DEVOLVE X EXEMPLO:: SELECT NVL(CODFORNECEDOR, 999) FROM TB_PRODUTOS PSEUSO_COLUNAS PSEUDO-COLUNAS SÃO COLUNAS QUE PODEM SER CONSULTADAS EM QUALQUER TABELA, COMO UMA COLUNA NORMAL, MAS NÃO PODEM SER ALTERADAS. CURRVAL N NOME_SEQ.CURRVAL VALOR CORRENTE DA SEQUENCIA NOME_SEQ NEXTVAL N NOME_SEQ.NEXTVAL PROXIMO VALOR DA SEQUENCIA NOME_SEQ NULL VALOR NULO ROWID IDENTIFICAÇÃO INTERNA DE UMA LINHA EXEMPLO:: SELECT CODFORNECEDOR, ROWID FROM TB_PRODUTOS 33 ROWNUM NUMERO QUE INDICA A ORDEM DE SEQÜÊNCIA DA LINHA SELECIONADA EM UMA CONSULTA EXEMPLO:: SELECT CODFORNECEDOR, ROWNUM FROM TB_PRODUTOS SYSDATE DATA E HORA CORRENTE EXEMPLO:: SELECT SYSDATE FROM DUAL UID NUMERO QUE IDENTIFICA UNICAMENTE O USUÁRIO CORRENTE EXEMPLO:: SELECT UID FROM DUAL USER NOME DO USUÁRIO CORRENTE EXEMPLO:: SELECT USER FROM DUAL 34 INTEGRIDADE REFERENCIAL INTEGRIDADE NO ORACLE TODAS AS REGRAS DE INTEGRIDADE DE DADOS E ENTIDADE SÃO DEFINIDOS POR OBJETOS CHAMADOS CONSTRAINT. QUE PODEM SER DEFINIDOS QUANDO DA CRIAÇÃO DA TABELA OU POSTERIOR VIA CHAMANDO ALTER TABLE. OS CONSTRAINTS SUPORTADOS SÃO : * NOT NULL * UNIQUE KEY * PRIMARY KEY * FOREIGN KEY * CHECK CONSTRAINTS IN-LINE EXEMPLO: CREATE TABLE EMPREGADO ( CD_EMPREGADO NUMBER (6,0) PRIMARY KEY, ............................................................................... ............................................................................... ); CONSTRAINTS OUT-OF-LINE EXEMPLO: CREATE TABBLE EMPREGADO ( CD_EMPREGADO NUMBER (6,0) NOT NULL, ..................................................................... ..................................................................... CONSTRAINT PK_EMPREGADO PRIMARY KEY (CD_EMPREGADO) ); 35 NOTA : QUANDO O CONSTRAINT FOR DEFINIDO SEM NOME, O ORACLE DEFINE UM NOME PARA O MESMO - SYS_C00N - ONDE N É UM NÚMERO SEQÜENCIAL CRESCENTE. PRIMARY KEY CONSTRAINT VALOR ÚNICO QUE IDENTIFICA CADA LINHA DA TABELA. EXEMPLO: CREATE TABLE EMPRESA ( .......................................................................................................... CONSTRAINT PK_EMPRESA PRIMARY KEY (CD_EMPRESA); CHECK CONSTRAINT AS VALIDAÇÕES DE COLUNAS SÃO FEITAS UTILIZANDO O CHECK CONSTRAINT. EXEMPLO: CREATE TABLE FILIAL ( CD_EMPRESA NUMBER (6,0) CONSTRAINT FK1_FILIAL REFERENCE EMPRESA (CD_EMPRESA), CD_FILIAL NUMBER (4,0) CONSTRAINT CK_FILIAL CHECK (CD_FILIAL BETWEEN 1 AND 9999) DISABLE, CD_TIPO VARCHAR2 (20) CONSTRAINT CK_TIPO_FILIAL CHECK (CD_TIPO IN ('MATRIZ', 'FILIAL', 'FABRICA')), DT_CADASTRO DATE, CONSTRAINT PK_FILIAL PRIMARY KEY (CD_EMPRESA, CD_FILIAL)); 36 FOREIGN KEY CONSTRAINT • DEVE ESTAR ASSOCIADA A UMA PRIMARY KEY OU UNIQUE KEY DEFINIDA ANTERIORMENTE. • PODE ASSUMIR VALOR NULO OU IGUAL AO DA CHAVE REFERENCIADA. • NÃO EXISTE LIMITE PARA UM NÚMERO DE FOREIGN KEYS. • GARANTE A CONSISTÊNCIA COM A PRIMARY KEY REFERENCIADA. • PODE FAZER REFERÊNCIA A PRÓPRIA TABELA. • NÃO PODE SER CRIADA PARA VIEWS, SYNONYNS E REMOTE TABLE EXEMPLO: CONSTRAINT FK1_FILIAL FOREIGN KEY (CD_EMPRESA) REFERENCES EMPRESA (CD_EMPRESA) OU CONSTRAINT FK1_FILIAL FOREIGN KEY (CD_EMPRESA) REFERENCES EMPRESA CHECK CONSTRAINT AS VALIDAÇÕES DE COLUNAS SÃO FEITAS UTILIZANDO O CHECK CONSTAINT. EXEMPLO: CREATE TABLE FILIAL ( CD_EMPRESA NUMBER(6,0) CONSTRAINT FK1_FILIAL REFERENCES EMPRESA (CD_EMPRESA), CD_FILIAL NUMBER(4,0) CONSTRAINT CK_FILIAL CHECK (CD_FILIAL BETWEEN 1 AND 9999) DISABLE, CD_TIPO VARCHAR2(20) CONSTRAINT CK_TIPO_FILIAL CHECK (CD_TIPO IN (´MATRIZ´, ´FILIAL´,´FABRICA´, )), DT_CADASTRO DATE, CONSTRAINT PK_FILIAL PRIMARY KEY (CD_EMPRESA, CD_FILIAL)); 37 DEFAULT SPECIFICATION PODEMOS ATRIBUIR VALORES DEFAULT PARA COLUNAS, VISANDO FACILITAR A INSERÇÃO DE DADOS CREATE TABLE FILIAL ( CD_EMPRESA NUMBER(6,0) CONSTRAINT FK1_FILIAL REFERENCES EMPRESA (CD_EMPRESA), CD_FILIAL NUMBER(4,0) CONSTRAINT CK_FILIAL CHECK (CD_FILIAL BETWEEN 1 AND 9999) DISABLE, CD_TIPO VARCHAR2(20) CONSTRAINT CK_TIPO_FILIAL CHECK (CD_TIPO IN (´MATRIZ´, ´FILIAL´,´FABRICA´, )), DT_CADASTRO DATE DEFAULT SYSDATE , CONSTRAINT PK_FILIAL PRIMARY KEY (CD_EMPRESA, CD_FILIAL)); OBS: PARA ATIVAR A CONSTRAINT : ALTER TABLE FILIAL ENABLE CONSTRAINT FK_FILIAL; 38 JOINS - JUNÇÕES UMA JUNÇÃO É UTILIZADA QUANDO UMA CONSULTA SQL REQUER DADOS DE MAIS DE UMA TABELA. AS LINHAS DE UMA TABELA PODEM JUNTAR-SE COM AS DE OUTRA TABELA, DE ACORDO COM VALORES COMUNS EXISTENTES EM COLUNAS CORRESPONDENTES. QUANDO UMA CONDIÇÃO JOIN NÃO É UTILIZADA, O RESULTADO DA CONSULTA É DENOMINADO PRODUTO, E TODAS AS COMBINAÇÕES DAS LINHAS SÃO EXIBIDAS, OU SEJA, EM UMA CONSULTA ENVOLVENDO 2 TABELAS, POR EXEMPLO:, PARA CADA LINHA DA PRIMEIRA TABELA, SERIAM APRESENTADOS TODAS AS LINHAS DE OUTRA TABELA. UM PRODUTO TENDE A GERAR UM NUMERO MUITO GRANDE DE LINHAS, E O RESULTADO NÃO E USUAL. DEVE-SE INCLUIR UMA CONDIÇÃO JOIN EM UMA CLAUSULA WHERE, A MENOS QUE VOCÊ TENHA UMA NECESSIDADE ESPECIFICA PARA COMBINAR TODAS AS LINHAS DAS TABELAS EM REFERENCIA. 39 EQUI-JOIN ( EQUI-JUNÇÃO) JUNÇÃO / UNIAO POR IGUALDADE. EXEMPLO: SELECT DEPARTAMENTO.ID NDEP, DEPARTAMENTOS.NOMENOMEDEP, EMPREGADOS.ID NEMP, EMPREGADOS.NOME NOMEEMP, FROM DEPARTAMENTO, EMPREGADOS WHERE DEPARTAMENTO.ID = ID_DEPARTAMENTO SELECT * FROM TB_FORNECEDOR A, TB_PRODUTOS B WHERE A.CODFORNECEDOR = B. CODFORNECEDOR 40 OUTER JOIN ( JUNÇÃO EXTERNA ) CASO UMA LINHA NÃO SATISFAÇA A CONDIÇÃO JOIN, ENTÃO A LINHA NÃO APARECERA NO RESULTADO DA CONSULTA EXEMPLO:: SELECT PARA LISTAR TODOS OS PRODUTOS, OS QUE TEM FO RNECEDOR OU NÃO SELECT * FROM TB_FORNECEDOR A,TB_PRODUTOS B WHERE A.CODFORNECEDOR (+) = B.CODFORNECEDOR 41 AUTO – JUNÇÃO É POSSÍVEL UTILIZANDO ALIAS, EFETUAR UM JOIN DE UMA TABELA PARA ELA MESMA, COMO SE FOSSEM DUAS TABELAS DISTINTAS. PARA DESCOBRIR QUAIS GERENTES ESTÃO GANHANDO MENOS QUE O DOBRO DO SALÁRIO DE ALGUM DE SEUS SUBORDINADOS EXEMPLO:: SELECT GER.NOME GERENTE , GER.SALARIO, MAX (*TRAB.SALARIO) FROM EMPREGADOS TRAB, EMPREGADOS GER WHERE TRAB.ID_CHEFE = GER.ID GROUP BY GER.NOME, GER.SALARIO HAVING MAX (2*TRAB.SALARIO) > GER.SALARIO 42 SUBQUERIES UMA CONSULTA ENCAIXADA CONSISTE EM UMA CLAUSULA SELECT ENCAIXADA EM OUTRO COMANDO SELECT, O QUAL RETORNA RESULTADOS INTERMEDIÁRIOS. UMA CONSULTA SQL PODE FAZER PARTE DA CONDIÇÃO DA CLAUSULA WHERE DE OUTRO COMANDO SQL. UMA DECLARAÇÃO SELECT PODE SER CONSIDERADA COMO UM BLOCO DE CONSULTA. A DECLARAÇÃO SELECT INTERNA E EXECUTADA PRIMEIRO, PRODUZINDO UM RESULTADO. O SELECT PRINCIPAL E ENTÃO PROCESSADO E USA O VALOR RETORNADO PELO SELECT INTERNO PARA COMPLETAR SUA CONDIÇÃO DE PESQUISA. EXEMPLO:: SELECT CODPRODUTO, NOME, VALORVENDASUG FROM TB_PRODUTOS WHERE VALORVENDASUG > ( SELECT AVG)VALOVENDASUG) FROM TB_PRODUTOS) 43 PL/SQL PL/SQL É UMA EXTENSAO PARA A LINGUAGEM SQL, COM FACILIDADES PROJETADAS DE LINGUAGEM DE PROGRAMAÇÃO. DECLARAÇÕES DE MANIPULAÇÃO DE DADOS E CONSULTAS SQL SÃO INCLUIDAS DENTRO DE UNIDADES DE CODIGO PROCEDURAL. 44 BENEFICIOS DE PL SQL • É PORTAVEL • PODE DECLARAR IDENTIFICADORES • PODE PROGRAMAR COM ESTRUTURAS DE CONTROLE DE PROGRAMAÇÃO • PODE TRATAR ERROS 45 PL/SQL PORTABILIDADE APLICAÇÕES ESCRITAS EM PL/SQL SÃO PORTÁVEIS PARA QUALQUER MÁQUINA QUE RODE ORACLE RDBMS COM PL/SQL. INTEGRAÇÃO COM RDBMS • VARIÁVEIS PL/SQL PODEM SER DEFINIDAS A PARTIR DE DEFINIÇÕES DAS COLUNAS DAS TABELAS. • REDUÇÃO DE MANUTENÇÃO DAS APLICAÇÕES, POIS ESTAS SE ADAPTAM AS MUDANÇAS DA BASE DE DADOS. CAPACIDADE PROCEDURAL • COMANDOS DE CONTROLE DE FLUXO, COMANDOS DE REPETIÇÕES E TRATAMENTOS DE ERROS; PRODUTIVIDADE • DESENVOLVIMENTO DE PROCEDURES E TRIGGERS NO ORACLE FORMS E ORACLE REPORTS. • DESENVOLVIMENTO DE DATABASE TRIGGERS, PROCEDURES E FUNCTIONS A NÍVEL DO BANCO DE DADOS 46 PRINCIPAIS CARACTERÍSTICAS- PL/SQL ESTRUTURA DE BLOCO DECLARE - OPCIONAL • VARIÁVEIS, CURSORES, EXCEPTIONS DEFINIDAS PELO USUÁRIO BEGIN – OBRIGATÓRIO • SQL • PL/SQL EXCEPTION – OPCIONAL • AÇÕES QUE SÃO EXECUTADAS QUANDO OCOREM OS ERROS END – OBRIGATÓRIO 47 BLOCO PL/SQL COM SUB-BLOCO DECLARE • DEFINIÇÃO DE VARIÁVEIS BEGIN • COMANDOS • DECLARE • DEFINIÇÃO DE VARIÁVEIS BEGIN • COMANDOS EXCEPTION • TRATAMENTO DE ERROS END; END; 48 EXEMPLO: DECLARE V_VARIAVEL VARCHAR2(5); BEGIN SELECT NOME_COLUNA INTO V_VARIÁVEL FROM TABLE_NAME; EXEPTION WHEN EXCEPTION_NAME THEN ..... END; OBS: SEMPRE COLOQUE UM (;) NO FIM DE SQL OU UM PL/S QL. TIPOS DE BLOCOS Anonymous Procedure Function Declare Begin ....... Exception End; Procedure name is Begin ....... Exception End; Function name return data type is Begin ....... Return value; Exception End; UTILIZA COMANDOS SQL SELECT, INSERT, UPDADE, DELETE, ROLLBACK, COMMIT, SAVAPOINT 49 DECLARAÇÃO DE VARIÁVEIS E CONSTANTES DATATYPES MAIS UTILIZADOS • CHAR • VARCHAR2 • INTERGER • NUMBER • DATE • BOOLEAN DECLARE V_DATA DATE; V_DEPTNO NUMBER(2) := 10; V_LOCATION VARCHAR2(13) := ‘ATLANTA’; V_COMM CONTANT NUMBER :=1400; EXEMPLO: DECLARE NOME CHAR(30); SALARIO NUMBER(11,2); DEPART NUNBER(4); DTNASC DATE; SIM BOOLEAN; CONT NUMBER(6) :=0; PERC CONSTANT NUMBER(4,2):= 36.00; O ATRIBUTO %TYPE DECLARA A VARIÁVEL DE ACORDO COM UMA COLUNA DEFINIDA NO BANCO DE DADOS; 50 EXEMPLO: V_ENAME EMP.ENAME%TYPE; V_BALANCE NUMBER(7,2); V_MIN_BALANCE V_BALANCE%TYPE :=10; CONCEITO DE TRANSAÇÃO È UMA SEQUENCIA DE COMANDOS SQL DELIMITADOS POR COMANDOS CONNECT, DISCONECT OU AINDA POR COMANDOS COMMIT. COMANDOS A SEREM UTILIZADOS EM PL/SQL 1. COMMIT 2. ROLLBACK 3. SAVEPOINT NOME-DO-PONTO 4.ROLLBACK TO SAVEPOINT NOME-DO-PONTO 51 EXEMPLO: DE CONTROLE DE TRANSAÇÃO DECLARE <DEFINIÇÕES DE VARIÁVEIS> BEGIN INSERT INTO TEMP VALUE (NULL,1,’LINHA 1’); SAVEPOINT PONTOUM; INSERT INTO TEMP VALUE (NULL,2,’LINHA 2’); SAVEPOINT PONTODOIS; INSERT INTO TEMP VALUE (NULL,3,’LINHA 3’); SAVEPOINT PONTOTRES; <COMANDOS> ROLLBACK SAVEPOINT PONTODOIS; <COMANDOS> COMMIT; END; 52 FUNÇÕES UTILIZADAS EM PL/SQL PODEMOS CONTAR COM O USO DE FUNÇÕES DE CARACTERES, NUMÉRICAS, DATA, CONVERSÃO, DENTRE OUTRAS. EXEMPLO:: DECLARE CARGO_ATUAL CHAR(10); Begin SELECT UPPER(SUBSTR(CARGO,1,10)) INTO CARGO_ATUAL FROM FUNCIONARIO WHERE CD_FUNC = 2150; End; 53 EM COMANDOS PL/SQL PODEMOS UTILIZAR AS SEGUINTES FU NÇÕES: FUNÇÕES DE ERRO SQLERRM, SQLCODE FUNÇÕES DE CARACTERES ASCII, CHR, INITCAP, LENGTH, LOWER, LPAD, RPAD, LTRIM, RTRIM, SUBSTR, UPPER. FUNÇÕES NUMÉRICAS ABS, CEIL, FLOOR,MOD, POWER, ROUND, SIGN, TRUNC, SQRT........ FUNÇÕES DE DATA ADD_MONTHS, LAT_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, ROUND...... 54 CONTROLE DE FLUXO COMANDO IF IF <condição> THEN <comandos> END IF; IF <condição> THEN <comandos> ELSE <comandos> END IF; IF <condição> THEN <comandos> ELSIF <condição> THEN <comandos> END IF; 55 IF <condição> THEN <comandos> ELSIF <condição> THEN <comandos> ELSE <comandos> END IF; IF <condição> THEN IF <condição> THEN <comandos> END IF; END IF; 56 EXEMPLO: DECLARE QUANT NUMBER(3); BEGIN SELECT ES.NR_QTD INTO QUANT FROM ESTOQUE ES WHERE CD_PROD = 30; IF QUANT > 0 AND QUANT < 3000 THEN UPDATE ESTOQUE SET NR_QTD = QUANT + 1 WHERE CD_PROD = 30; ELSIF QUANT >= 3000 THEN INSERT INTO ALERTA(PROD,ERRO) VALUES(30,’MÁXIMO’);ELSE INSERT INTO ALERTA(PROD,ERRO) VALUES(30,’MÍNIMO’); END IF; END; 57 COMANDOS DE REPETIÇÃO SÃO UTILIZADOS PARA EXECUTAR REPETIDAMENTE O CÓDIGO ESCRITO DENTRO DELES. • LOOP • FOR LOOPs • WHILE LOOP • CURSOR FOR LOOPs 58 EXEMPLO: COMANDO LOOP DECLARE X NUMBER := 0; COUNTER NUMBER := 0; BEGIN LOOP X := X + 1000; COUNTER := COUNTER + 1; IF COUNTER > 4 THEN EXIT; END IF; DBMS_OUPUT.PUT_LINE (X ||’ ‘|| COUNTER || ’LOOP’); END LOOP; END; / 59 EXEMPLO: COMANDO FOR .. LOOP DECLARE A,B NUMBER(3):= 0; BEGIN FOR A IN 1..25 LOOP B:= B + 1; DBMS_OUTPUT.PUT_LINE (‘LOOP1 - ‘||B); END LOOP; END; 60 EXEMPLO: COMANDO WHILE .. LOOP DECLARE X NUMBER(3); Y VARCHAR2(30); K DATE; J NUMBER(3); BEGIN X:= 0; WHILE X<= 100 LOOP K:= SYSDATE-X; Y := 30; INSERT INTO TESTE VALUES (X,Y,K); X := X + 1; END LOOP; COMMIT; END; 61 CURSOR CURSOR < nome-cursor > [parâmetro tipo,...] IS < comando select > EXEMPLO:: CURSOR MEU_CURSOR IS SELECT ENAME, EMPNO, SAL FROM EMP ORDER BY SAL DESC; 62 EXEMPLO: DE PL/SQL UTILIZANDO CURSOR DECLARE CURSOR C1 IS SELECT ENAME, EMPNO, SAL FROM EMP ORDER BY SAL DESC; MY_ENAME CHAR(10); MY_EMPNO NUMBER (4); MY_SAL NUMBER (7,2); BEGIN OPEN C1; FOR I IN 1..100 LOOP FETCH C1 INTO MY_ENAME, MY_EMPNO, MY_SAL; EXIT WHEN C1%NOTFOUND; DBMS_OUPUT.PUT_LINE (MY_SAL || ‘ ‘ || MY_EMPNO || ‘ ‘ || MY_ENAME); END LOOP; CLOSE C1; END; 63 EXCEÇÕES SÃO USADAS NO PL/SQL PARA LIDAR COM QUAISQUER ERROS QUE OCORRAM DURANTE A EXECUÇÃO DE UM BLOCO. HÁ DOIS TIPOS DE EXECEÇÕES, AS DEFINIDAS INTERNAMENTE PELA PL/SQL E AS DEFINIDAS PELO USUÁRIO. NESTA PARTE VEREMOS APENAS ALGUMAS DELAS SINTAXE: EXCEPTION WHEN <nome-exeception> THEN <comandos>; WHEN <nome-exception> THEN <comandos>; 64 EXEMPLO: EXCEPTION NO_DATA_FOUND - Quando um select não retorna nenhuma linha TOO_MANY_ROWS - Quando um select retorna mais de uma linha OTHERS - Qualquer tipo de erro 65 EXEMPLO: DECLARE NOME CHAR(15); CARGO CHAR(10); BEGIN SELECT ENAME, JOB INTO NOME, CARGO FROM EMP WHERE EMPNO = 1111; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO PROBLEMA (ERRO,DTERRO) VALUES (‘REGISTRO INEXISTENTE’,SYSDATE); WHEN TOO_MANY_ROWS THEN INSERT INTO PROBLEMA (ERRO,DTERRO) VALUES (‘MUITOS REGISTROS’,SYSDATE); WHEN OTHERS THEN INSERT INTO PROBLEMA (ERRO,DTERRO) VALUES (‘OUTRO ERRO QUALQUER’,SYSDATE); END; 66 EXEMPLO: DECLARE NM VARCHAR2(30); BEGIN SELECT NOME INTO NM FROM TESTE WHERE IDADE=30; DBMS_OUTPUT.PUT_LINE(NM); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('IDADE INEXISTENTE'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('MULTIPLAS LINHAS'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OUTRA SAIDA'); END; 67 RETORNANDO ERROS SQLERR - Retorna o número do erro SQLERRM – Retorna o número e a descrição do erro 68 EXEMPLO: BEGIN INSERT INTO TESTE VALUES (50,45,SYSDATE); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('ERRO - '||SQLERRM); END; 69 EXCEPTION ■ DUP_VAL_ON_INDEX ■ Chave Duplicada ■ INVALID_CURSOR ■ Operação Ilegal com Cursor ■ INVALID_NUMBER ■ Conversão inválida p/numeric ■ LOGIN_DENIED ■ Usuário/Senha Inválida ■ NO_DATA_FOUND ■ Nenhuma linha retornada ■ NOT_LOGGED_ON ■ Usuário não conectado ■ OTHERS Erro não declarado em exceptions ■ PROGRAM_ERROR ■ Problema Interno ■ STORAGE_ERROR ■ Falta de Memória ■ TIMEOUT_ON_RESOURCE ■ Tempo de espera ■ TOO_MANY_ROWS ■ Retorna Muitas Linhas ■ TRANSACTION_BACKED_OUT ■ Volta Atrás uma transação ■ VALUE_ERROR ■ Erro Conversão,Expressão ■ ZERO_DIVIDE Divisão por zero EXERCÍCIO 70 ESCREVA UM BLOCO PL/SQL PARA SELECIONAR O NOME DO CLIENTE TRATANDO OS ERROS DE NENHUM VALOR ENCONTRADO E MUITOS VALORES ENCONTRADOS. ADICIONE TAMBÉM UMA EXCEÇÃO GERAL PARA COBRIR OUTRO ERRO QUE VENHA A OCORRER. EXCEPTIONS DEFINIDAS PELO USUÁRIO EXEMPLO: DECLARE 71 X NUMBER := 0; Y NUMBER := 0; ESTOURO EXCEPTION; BEGIN FOR X IN 1..1000 LOOP Y:= X + (X/2); IF Y > 1000 THEN RAISE ESTOURO; END IF; END LOOP; EXCEPTION WHEN ESTOURO THEN DBMS_OUTPUT.PUT_LINE('ESTOURO DE CAPACIDADE'); END; PROCEDURE SINTAXE: PROCEDURE nome_procedure IS 72 variable1 data type; ... BEGIN comandos; ... EXCEPTION when ... END nome_procedure; EXEMPLO: PROCEDURE CREATE OR REPLACE PROCEDURE CHECK_SALARY (EMP_ID INTEGER, INCREASE REAL) IS CURRENT_SALARY REAL; 73 SALARY_MISSING EXCEPTION; BEGIN SELECT SAL INTO CURRENT_SALARY FROM EMP WHERE EMPNO = EMP_ID; IF CURRENT_SALARY IS NULL THEN RAISE SALARY_MISSING; ELSE UPDATE EMP SET SAL = SAL + INCREASE WHERE EMPNO = EMP_ID; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO EMP_AUDIT VALUES (EMP_ID, ‘NO SUCH NUMBER’); WHEN SALARY_MISSING THEN (EMP_ID, ‘SALARY IS NULL’); END CHECK-SALARY; / FORMA DE CHAMADA CHECK_SALARY(EMP_NUM,AMOUNT); PACKAGE É UM OBJETO DO DATABASE QUE CONTÉM UM GRUPO DE FUNÇÕES RELACIONADAS. UM PACKAGE PODE CONTER: • PROCEDURES 74 • FUNCTIONS • CURSOR DEFINITIONS • VARIABLES AND CONTANTS • EXCEPTION DEFINITIONS EXEMPLO: PACKAGE CREATE REPLACE PACKAGE EMP_ACTION IS PROCEDURE HIRE_EMPLOYEE (EMPNO NUMBER, ENAME CHAR, .....); PROCEDURE FIRE_EMPLOYEE (EMP_ID NUMBER); END EMP_ACTIONS; / 75 CREATE OR REPLACE PACKAGE BODY EMP_ACTIONS IS PROCEDURE HIRE_EMPLOYEE (EMPNO NUMBER, ENAME CHAR, .....); IS BEGIN INSERT INTO EMP VALUES (EMPNO, ENAME, .....); END HIRE_EMPLOYEE; PROCEDURE FIRE_EMPLOYEE(EMP_ID NUMBER) IS BEGIN DELETE FROM EMP WHERE EMPNO = EMP_ID; END FIRE_EMPLOYEE; END EMP_ACTIONS; / DATABASE TRIGGER São programas PL/SQL, utilizados para completar a integridade, segurança e regras de negócios retirando estas tarefas dos programas. Eles estão ligados a uma tabela e associado a um Evento.CARACTERÍSTICA 76 . Disparado automaticamente quando a ocorrência do Evento PARTES DE UM DATABASE TRIGGER PARTE DESCRIÇÃO TRIGGER TYPE TRIGGERING EVENT TRIGGER RESTRICTION TRIGGER ACTION BEFORE / AFTER (comando, linha) INSERT / UPDATE / DELETE WHEN (clause opcional) PL/SQL BLOCK UTILIZAÇÃO – DATABASE TRIGGERS TRIGGER TYPE EXEMPLO: DE UTILIZAÇÃO BEFORE STATEMENT BEFORE ROW AFTER ROW AFTER STATEMENT Garantir segurança e integridade Calcular campos derivados para nova linha Auditoria a nível de valor ou linha Auditoria em geral PREDICADOS CONDICIONAIS EM DATABASE TRIGGERS • INSERTING • UPDATING • DELETING 77 EXEMPLO: DATABASE TRIGGER COM PREDICADO CONDICIONAL CREATE TRIGGER TOTAL_SALARY AFTER DELETE OR INSERT OR UPDATE OF DEPTNO,SAL ON EMP FOR EACH ROW BEGIN IF DELETING THEN UPDATE DEPT_BUDGET SET TOTAL_SAL = TOTAL_SAL - : OLD.SAL 78 WHERE DEPTNO =:OLD.DEPTNO; END IF; IF INSERTING THEN UPDATE DEPT_BUDGET SET TOTAL_SAL = TOTAL_SAL + :NEW.SAL WHERE DEPTNO =:NEW.DEPTNO; END IF; IF UPDATING THEN UPDATE DEPT_BUDGET SET TOTAL_SAL = TOTAL_SAL + (:NEW.SAL -:OLD.SAL); WHERE DEPTNO =:OLD.DEPTNO; END IF; END; ATIVANDO E DESATIVANDO DATABASE TRIGGER Quando criamos um database trigger ele fica automaticamente ativado. As vezes temos necessidade de desativá-los como por EXEMPLO:: . Os objetos referenciados pelo database trigger não estão disponíveis; . Vamos recuperar dados da Tabela 79 Podemos desativar/ativar um database trigger isoladamente ou todos relacionados a uma tabela específica. ALTER TRIGGER [schema.] trigger_name {ENABLE | DISA BLE } EXEMPLO: ALTER TRIGGER TRG_EMPRESA DISABLE; ALTER TRIGGER TRG_EMPRESA ENABLE; ALTER TABLE [schema.] table_name { ENABLE | DISABLE } ALL TRIGGERS EXEMPLO: ALTER TABLE EMPRESA DISABLE ALL TRIGGERS; ALTER TABLE EMPRESA ENABLE ALL TRIGGERS; ELIMINANDO UM DATABASE TRIGGER 80 DROP TRIGGER [ schema.] trigger_name EXEMPLO: DROP TRIGGER TRG_EMPRESA; VIEW DO DIOCIONÁRIOS COM INFORMAÇÕES DE DATABASE TR IGGERS . USER_TRIGGERS . DBA_TRIGGERS 81 DATABASE TRIGGER – REPLICAÇÃO DE TABELA CREATE TRIGGER REP_EMPRESA BEFORE INSERT OR UPDATE OR DELETE ON EMPRESA FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO EMPRESA@SPAULO VALUES (:NEW.CD_EMPRESA,:NEW.NM_RAZAO_SOCIAL); ELSIF UPDATING THEN UPDATE EMPRESA@SPAULO SET NM_RAZAO_SOCIAL =:NEW.NM_RAZAO_SOCIAL; WHERE CD_EMPRESA =:OLD.CD_EMPRESA ELSE DELETE FROM EMPRESA@SPAULO WHERE CD_EMPRESA =:OLD.CD_EMPRESA; END IF; END;
Compartilhar