Baixe o app para aproveitar ainda mais
Prévia do material em texto
Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 1 – Parte V (Programação: Linguagem SQL) Programação em Linguagem SQL 36 páginas Em Conformidade com o Padrão ISO ♦♦ IINNTTRROODDUUÇÇÃÃOO ♦♦ PPAADDRROONNIIZZAAÇÇÃÃOO ♦♦ EEXXEEMMPPLLOO ♦♦ TTIIPPOOSS DDEE DDAADDOOSS ♦♦ TTIIPPOOSS DDEE EERRRROOSS ♦♦ OOPPEERRAADDOORREESS ♦♦ FFUUNNÇÇÕÕEESS ♦♦ PPAALLAAVVRRAASS RREESSEERRVVAADDAASS ♦♦ LLIINNGGUUAAGGEEMM DDEE DDEEFFIINNIIÇÇÃÃOO DDEE DDAADDOOSS ((DDDDLL)) ♦♦ LLIINNGGUUAAGGEEMM DDEE MMAANNIIPPUULLAAÇÇÃÃOO DDEE DDAADDOOSS ((DDMMLL)) ♦♦ LLIINNGGUUAAGGEEMM DDEE CCOONNTTRROOLLEE DDEE DDAADDOOSS ((DDCCLL)) ♦♦ EEMMBBEEDDDDEEDD SSQQLL ♦♦ MMÓÓDDUULLOOSS AARRMMAAZZEENNAADDOOSS ((SSQQLL//PPSSMM)) ♦♦ GGAATTIILLHHOOSS ((““TTRRIIGGGGEERRSS””)) ♦♦ NNOOVVIIDDAADDEESS DDOO SSQQLL33 ((OOUU SSQQLL 9999)) ♦♦ OOBBSSEERRVVAAÇÇÕÕEESS ♦♦ EEXXEERRCCÍÍCCIIOOSS V Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 2 – Parte V (Programação: Linguagem SQL) 1 - INTRODUÇÃO Desenvolvida e implementada pelo Laboratório de Pesquisas da IBM em San Jose (hoje o Centro de Pesquisa Almaden) entre os anos de 1964 e 1969, a Linguagem SQL (“Structured Query Language”), inicialmente chamada “SEQUEL”, foi criada como uma forma de interface entre os usuários e o primeiro Sistema de Gerenciamento de Bancos de Dados Relacional denominado System R. Por apresentar-se promissora devido a sua simplicidade e o grande poder de consulta em Bancos de Dados Relacionais, a linguagem SQL é atualmente um dos tópicos de maior importância científica e comercial. SQL é muito mais do que uma simples linguagem de consulta. Entre seus principais atrativos está a pequena quantidade de comandos necessários para se realizar todas as operações necessárias, incluindo inclusive as operações com relação, ou seja, seleção, união, diferença e interseção. 2 - PADRONIZAÇÃO Devido ao grande interesse demonstrado, diversas tentativas de padronização da SQL foram realizadas de modo a criar um conjunto de comandos e operandos de uso comum entre os diversos ambientes, possibilitanto maior interação entre seus usuários e dados. O “American National Standart Institute” (ANSI) publicou em 1986 um padrão, a IBM publicou seu próprio padrão e outros padrões surgiram desde então, como é o caso do SQL2 (na qual se baseia esta apostila) e posteriormente SQL3 ou o agora chamado SQL-99. Independente da versão, os comandos da SQL podem ser divididos em três conjuntos: • Linguagem de Definição de Dados (DDL): inclui comandos para definir, alterar e remover tabelas e índices; • Linguagem de Manipulação de Dados (DML): inclui comandos para inserir, remover, atualizar e consultar os dados armazenados nas tabelas; • Linguagem de Controle de Dados (DCL): incluir comandos para se trabalhar em ambiente multi-usuário, estabelecendo níveis de segurança e transações. Os comandos de manipulação de dados podem ser diretamente utilizadas através de um ambiente interativo ou indiretamente como uma sub-linguagem embutida (Embedded SQL) em linguagens de aplicação (ou linguagens hospedeiras) como Cobol (ISO 1989 de 1985), Fortran (ISO 1539 de 1980), C (ISO/IEC1 9899), Ada, PL/1 (ISO/IEC 6160 de 1979), Pascal (ISO/IEC 10206 de 1990) e MUMPS. 3 - EXEMPLO Considere um problema de modelagem em que a empresa construtora de veículos especiais “Star Trek” necessita armazenar em seu Banco de Dados informações sobre as peças que utiliza em cada projeto de veículo e os fornecedores destas peças. O Banco de Dados deve ser capaz 1 ISO/IEC - International Organization for Standardization / International Electrotechnical Commission Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 3 – Parte V (Programação: Linguagem SQL) de oferecer respostas rápidas e precisas sobre as peças, fornecedores e projetos realizados bem como as associações entre estes elementos, ou seja, a quantidade de peças fornecida por uma determinada empresa e utilizadas em um projeto. As PEÇAS são identificadas por um código sendo utilizado a cor das gavetas onde estão colocadas as peças para uma localização visual mais rápida por parte dos funcionários. Para a especificação das compras são necessários o nome e o preço atual de cada peça. Para a emissão correta das notas de compra e dos pagamentos é necessário o nome dos FORNECEDORES bem como a cidade e um código de identificação. A categoria de cada fornecedor é utilizada para indicar a qualidade de seus produtos e serviços. O PROJETOS de veículos construídos pela “Star Trek” possuem um nome, código de identificação, a duração para a montagem do veículo e o custo de cada veículo (incluindo peças e serviços). O seguinte Diagrama Entidade-Relacionamento representa semanticamente esta necessidade: Para a apresentação da sintaxe dos comandos considere o seguinte Esquema de Dados para um Banco de Dados Relacional : PEÇA (PeNro, PeNome, PePreço, PeCor) FORNECEDOR (FNro, FNome, FCidade, FCateg) PROJETO (PNro, PNome, PDuração, PCusto) FORNECE_PARA (PeNro, FNro, PNro, Quant) Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 4 – Parte V (Programação: Linguagem SQL) Para este exemplo, considere o seguinte Banco de Dados Relacional representado aqui por tabelas (colunas e linhas). Suponha que estas tabelas apresentam-se na mesma ordem em que estão fisicamente armazenados os registros dos arquivos correspondentes às relações. Peça PeNro PeNome PePreço (dólar) PeCor PE1 Cinto 22 Azul PE2 Volante 18 Vermelho PE3 Lanterna 14 Preto PE4 Limpador 09 Amarelo PE5 Painel 43 Vermelho Fornecedor FNro FNome FCidade FCateg F1 Plastec Campinas B F2 C&M São Paulo D F3 Kirurgic Campinas A F4 Piloto’s Piracicaba A F5 Equipament São Carlos C Projeto PNro PNome PDuração (mês) PCusto (dólar) P1 Detroit 5 43.000 P2 Pegasus 3 37.000 P3 Alfa 2 26.700 P4 Sea 3 21.200 P5 Paraíso 1 17.000 Fornece_para PeNro FNro PNro Quant PE1 F5 P4 5 PE2 F2 P2 1 PE3 F3 P4 2 PE4 F4 P5 3 PE5 F1 P1 1 PE2 F2 P3 1 PE4 F3 P5 2 Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 5 – Parte V (Programação: Linguagem SQL) 4 - TIPOS DE DADOS Os tipos de dados mais usuais são: INTEGER ou INT (-32768 a 32767), SMALLINT (0 a 65535), FLOAT (-2147483648 a 2147483647), REAL (1.17E-38 a 3.4E+38), DOUBLE PRECISION (2.2E-308 a 1.7E+308), DECIMAL (i,j) onde i é o total de casas decimais e j é o total de casas decimais depois do ponto (até 18), CHAR (n) onde n é a quantidade fixa de caracteres (até 60000), VARCHAR (n) onde n é a quantidade máxima de caracteres (até 60000), BIT(n) onde n é a quantidade fixa de bits, BIT VARYING(n) onde n é a quantidade máxima de bits, DATE que são dez posições no formato aaaa-mm-dd, TIME que são 8 posições no formato hh:mm:ss, TIME(i) onde i é uma fração de segundo (ex.: 67 centésimos de segundo) e TIMESTAMP. Pode-se encontrar outros tipos reconhecidos pelos compiladores. 5 - TIPOS DE ERROS Cada implementação possui sua forma de tratar e informar sobre os erros ocorridos durante a execução de qualquer comando. Pode-se encontrar diversos tipos de erros gerados pelos compiladores, normalmente utilizando uma variável global de retorno (ex. SQLCODE) 6 - OPERADORES Lógicos Aritméticos Relacionais AND OR NOT + - * / < <= > >= <> = LIKE, BETWEEN Conjunturais =ANY <ANY >ANY >=ANY <=ANY <>ANY <ALL >ALL <>ALL EXIST NOT EXIST IN NOT IN 7 - FUNÇÕES AVG - obtém o valor médio de uma coluna COUNT - obtém a soma da quantidade de linhas analisadas MAX - obtém o maior valor de uma coluna MIN - obtém o menor valor de uma coluna SUM - obtém a soma de valores de uma coluna 8 - PALAVRAS RESERVADAS As palavras reservadas estão apresentadas nesta apostila em letras maiúsculas, porém devido a falta de uma padronização mais rígida da linguagem SQLpode-se encontrar outros símbolos reservados aos compiladores. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 6 – Parte V (Programação: Linguagem SQL) 9 - LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL) CCCrrriiiaaarrr uuummm dddooommmííínnniiiooo (((dddooommmaaaiiinnn))) CREATE DOMAIN nome_domínio AS tipo [DEFAULT valor] [CONSTRAINT nome_restrição CHECK (expressão condicional)]; NNNoootttaaasss ♦ Esta é a forma mais simples de estabelecer uma restrição de integridade. EEExxxeeemmmppplllooosss NENHUMA CIDADE PODE ESTAR FORA DO CONJUNTO PERMITIDO CREATE DOMAIN cidades AS CHAR(27) CONSTRAINT icidades CHECK (VALUE in (´Piracicaba´,´São Paulo´,´Ribeirão Preto´)); NENHUM PREÇO PODE SER NULO CREATE DOMAIN preço AS integer CONSTRAINT ipreço CHECK (VALUE IS NOT NULL); CCCrrriiiaaarrr uuummmaaa RRReeessstttrrriiiçççãããooo dddeee IIInnnttteeegggrrriiidddaaadddeee GGGeeerrraaalll (((AAAsssssseeerrrtttiiiooonnn))) CREATE ASSERTION nome_restrição CHECK (expressão condicional); NNNoootttaaasss ♦ Escopo da restrição são todas as tabelas e suas colunas EEExxxeeemmmppplllooo NENHUM PROJETO PODE TER CUSTO MENOR QUE A MÉDIA CREATE ASSERTION icusto CHECK (VALUE > (SELECT AVG(PCusto) FROM Projeto) ); CCCrrriiiaaarrr uuummm EEEsssqqquuueeemmmaaa (((SSSccchhheeemmmaaa))) CREATE SCHEMA [nome_esquema] [AUTHORIZATION nome_usuário] [ DEFAULT CHARACTER SET nome_conjunto_de_caracteres [ definição_de_elementos ] ; NNNoootttaaasss ♦ Em um mesmo SBD (SQL-environment) podemos ter vários Catálogos (sem uma especificação, por parte do padrão, de como devem ser criados, atualizados e removidos), coleções de Esquemas (SQL-Schemas), especificando um Banco de Dados no mesmo local físico ou não (distribuído). Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 7 – Parte V (Programação: Linguagem SQL) ♦ Cada Catálogo deve incluir um Esquema especial, denominado Information Schema2, que mantem descritores (informações gerais) de todos o conteúdo de seus Esquemas. ♦ Na visualização de um Information Schema, serão apresentadas apenas as informações criadas pelo usuário corrente do Módulo. ♦ Nome_conjunto_de_caracteres especifica o Character Set usado no esquema para atribuição de nome e comparação de caracteres. Caso não seja especificado então utiliza-se o Character Set default. ♦ Caso o nome_esquema não seja especificado então será utilizado o nome_usuário para sua identificação ♦ Caso o nome_usuário não seja especificado então o Id do usuário do Módulo é utilizado. ♦ Em definição_de elementos é possível colocar os comandos CREATE para : domínios, tabelas, visões, restrições, Character Sets, Collations e Translations, e também comandos GRANT para estabelecimento de privilégios de acesso. EEExxxeeemmmppplllooo CRIAR UM ESQUEMA PARA ARMAZENAR ELEMENTOS DO BANCO DE DADOS DA SUBSIDIÁRIA RIBEIRÃO E SUA TABELA DE FUNCIONÁRIOS CREATE SCHEMA Ribeirão AUTHORIZATION JJLucas; CREATE TABLE Ribeirão.Funcionario ( Cod integer, Nome char(50), End char(90) PRIMARY KEY (Cod) ); 2 O conteúdo de um Information Schema envolve normalmente as seguintes tabelas: - Information_Schema_Catalog_Name : descreve apenas o nome do catálogo deste Information Schema - Schemata : descreve todos os Esquemas deste Catálogo - Domains : descreve todos os domínios deste Catálogo - Tables : descreve todas as tabelas deste Catálogo (persistentes e temporárias tipo 2 e 3) - Views : descreve todas as visões deste Catálogo - Columns : descreve todas as colunas de todas as tabelas especificadas em Tables - Table_Privileges : Descreve todos os privilégios em todas as tabelas especificadas em Tables - Columns_Privileges : descreve todos os privilégios de todas as colunas das tabelas em Tables - Usage_Privileges : descreve todos os privilégios em todas as colunas, Collations e Transações - Domain_Constraints : descreve todas as restrições de domínio em Domains - Table_Constraints : descreve todas as restrições de tabelas em Tables - Referencial_Constraints : descreve todas as restrições referenciais - Check_Constraints : descreve todas as restrições de check - Key_Column_Usage : descreve todas chaves candidatas e estrangeiras - Assertions : descreve todas as restrições de integridade gerais - Characteres_sets : descreve todos os Character Sets - Collations : descreve todos os Collations - Translations : descreve todas as Translations - View_Table_Usage : descreve para cada visão quais são as tabelas vinculadas - View_Column_Usage : descreve para cada visão quais são as colunas vinculadas - Constraint_Table_Usage : descreve para cada restrição quais são as tabelas vinculadas - Constraint_Column_Usage : descreve para cada restrição quais são as colunas vinculadas - Column_Domain_Usage : descreve as colunas vinculadas aos domínios - SQL_Language : descreve o SQL usado (SQL/86, SQL/89 SQL/92) em SQL-implementation Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 8 – Parte V (Programação: Linguagem SQL) CCCrrriiiaaarrr uuummm CCCooonnnjjjuuunnntttooo dddeee CCCaaarrraaacccttteeerrreeesss CREATE CHARACTER SET [nome_esquema].nome_conjunto [AS] GET conjunto_de_caracteres_existente [COLLATE collation | COLLATION FROM collation_origem ]; NNNoootttaaasss ♦ Conjunto de Caracteres é formado por todos os caracteres suportados pelo ambiente de programação (SQL-implementation). O SQL-implementation deve suportar ao menos um Conjunto de Caracteres, chamado SQL_TEXT, composto de: letras (A .. Z, a .. z); digitos (0..9), caracteres especiais (“espaço” “ % & ‘ ( ) * + - . / : : < = > ? _ |) . ♦ Conjunto existente especifica . ♦ COLLATE e COLLATION FROM são duas formas de se estabelecer uma collation padrão CCCrrriiiaaarrr uuummmaaa CCCooollllllaaatttiiiooonnn CREATE COLLATION [nome_esquema].nome_collation FOR nome_conjunto_de_caracteres FROM collation_origem [ PAD SAPACE|NO PAD ] ; NNNoootttaaasss ♦ Collation é uma regra associada a um Conjunto de Caracteres (Character Set) que governa a comparação de seqüências de caracteres deste conjunto. ♦ Collation_origem especifica a ordenação dos caracteres. As opções são: nome_collation, para basear a ordenação em outra já definida; DESC (nome_collation), para basear a ordenação na ordem inversa de outra collation.; DEFAULT, para usar a ordenação default. CCCrrriiiaaarrr uuummmaaa tttaaabbbeeelllaaa (((tttiiipppooo 111 ---pppeeerrrsssiiisssttteeennnttteee))) eee dddeeefffiiinnniiirrr sssuuuaaasss cccooollluuunnnaaasss eee rrreeessstttrrriiiçççõõõeeesss... CREATE TABLE [nome_esquema] nome_tabela (nome_coluna tipo|Domínio [NOT NULL] [SET DEFAULT valor],.) PRIMARY KEY (nome_coluna,...) [CONSTRAINT (nome_restrição)] [UNIQUE (nome_coluna),....] [FOREIGN KEY (nome_coluna,...) REFERENCES nome_tabela [(nome_coluna,...)] [MATCH FULL/PARTIAL] /* Default FULL /* [ON DELETE NO ACTION/CASCADE/SET NULL/SET DEFAULT] [ON UPDATE NO ACTION/CASCADE/SET NULL/SET DEFAULT ]]; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 9 – Parte V (Programação: Linguagem SQL) NNNoootttaaasss ♦ MATCH é uma opção ignorada se a chave estrangeira for simples ou todos os componentes da chave estrangeiro têm restrição NOT NULL. Esta opção permite estabelecer o resultado de comparação entre chaves primárias compostas e suas chaves estrangeiras compostas correspondentes em condições em que nulos são inseridos em atributos de uma chave estrangeira. ♦ Sem MATCH, teremos integridade satisfeita quando: ao menos um atributo da estrangeira for nulo. ♦ Com PARTIAL, teremos integridade satisfeita quando: todos os atributos da estrangeira forem nulos, ou aqueles atributos não nulos forem iguais aos correspondentes atributos da chave primária. ♦ Com FULL, teremos integridade satisfeita quando: todosos atributos da estrangeira forem nulos, ou todos os atributos forem não nulos e iguais aos correspondentes atributos da chave primária. ♦ Suponha uma Chave Primária CP e uma correspondente chave estrangeira CE, contendo os atributo Atr1 e Atr2, então podemos esquematizar que: Inserção de CE com Atr1=Null, Atr2=Null Atr1=Null, Atr2= valor Atr1=valor, Atr2=valor Sem Match Verdadeira Verdadeira Falso Com Partial Verdadeira Depende3 Depende Com Full Verdadeira Falso Depende EEExxxeeemmmppplllooosss CRIAR A TABELA FORNECE_PARA CREATE TABLE Fornece_para ( PeNro CHAR(5) NOT NULL, FNro CHAR(5) NOT NULL, PNro CHAR(5) NOT NULL, Quant INT ) PRIMARY KEY (PeNro, FNro, PNro) FOREIGN KEY (PeNro) REFERENCES Peça ON DELETE CASCADE FOREIGN KEY (FNro) REFERENCES Fornecedor ON DELETE NULL FOREIGN KEY (PNro) REFERENCES Projeto ; CCCrrriiiaaarrr uuummmaaa tttaaabbbeeelllaaa ííínnndddiiiccceee... CREATE INDEX nome_tabela_índice ON nome_tabela ( nome_coluna [ASC|DESC],...); NNNoootttaaasss ♦ Uma tabela indexadora criada previamente para uma consulta diminui o tempo necessário para a apresentação da resposta. ♦ Não é um comando padrão. 3 O resultado da verificação da integridade referencial dependerá de encontrar ou não o “valor” especificado na chave primária correspondente. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 10 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo::: CRIAR ÍNDICEDE NOMES PARA PEÇAS CREATE INDEX nomes_peças ON Peça (PeNome ASC); CCCrrriiiaaarrr uuummmaaa tttaaabbbeeelllaaa vvviiisssãããooo... CREATE VIEW nome_tabela_visão [lista_atributos] AS (comando_select) [WITH [CASCADED/LOCAL] CHECK OPTION]; /*default é CASCADED*/ NNNoootttaaasss ♦ Lista_atributos especifica o nomes dos atributos na visão ♦ WITH CHECK OPTION determina que inserções e remoções na visão terão que ser verificadas segundo as condições estabelecidas no WHERE do comando de seleção correspondente. ♦ As opções CASCADE e LOCAL referem-se a verificação de condições quando se estabelece “visões de visões”. Suponha duas visões V1 e V2 (derivada de V1) derivadas de uma relação R, e deseja-se realizar uma atualização de registros em V2 então: V1 V2 Sem CHECK LOCAL CASCADE Sem CHECK Sem verificações Verificado Sobre Condições de V1 Verificado Sobre Condições de V1 e R LOCAL Verificado Sobre condições de V2 Verificado Sobre Condições de V1 e V2 Verificado Sobre Condições de R, V1 e V2 CASCADE Verificado Sobre condições de V2 Verificado Sobre Condições de V1 e V2 Verificado Sobre Condições de R, V1 e V2 EEExxxeeemmmppplllooo::: CRIAR UMA VISÃO COM OS BONS FORNECEDORES COM ÓTIMA QUALIDADE CREATE VIEW FORNECEDORES_BONS AS (SELECT * FROM Fornecedor WHERE Fornecedor.FCateg = ‘A’); AAAlllttteeerrraaarrr aaa eeessstttrrruuutttuuurrraaa dddeee uuummmaaa tttaaabbbeeelllaaa (((pppeeerrrmmmaaannneeennnttteee,,, ttteeemmmpppooorrrááárrriiiaaa tttiiipppooo 222 ooouuu 333))) ALTER TABLE nome_tabela [ADD [COLUMN] nome_coluna tipo [NOT NULL] ] [DROP [COLUMN] nome_coluna [CASCADE/RESTRICT] ] [ALTER [COLUMN] nome_coluna DROP DEFAULT/SET [DEFAULT] valor] [DROP CONSTRAINT nome_restrição [CASCADE/RESTRICT] ] [ADD CONSTRAINT nome_restrição]; NNNoootttaaasss Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 11 – Parte V (Programação: Linguagem SQL) ♦ RESTRICT impedirá a remoção de colunas que são referenciadas em visões ou restrições de integridade. ♦ CASCADE realizará a remoção de colunas e das visões e restrições vinculadas. EEExxxeeemmmppplllooo::: ALTERAR A TABELA PEÇA ALTER TABLE Peça ADD COLUMN material CHAR(20) ; RRReeemmmooovvveeerrr uuummm EEEsssqqquuueeemmmaaa... DROP SCHEMA nome_esquema CASCADE/RESTRICT; NNNoootttaaasss ♦ RESTRICT impedirá a remoção de um esquema não vazio. ♦ CASCADE realizará a remoção do esquema e também de todos os elementos contidos e descritos no esquema e inclusive todos os elementos externos ao esquema e que referenciam elementos do esquema. EEExxxeeemmmppplllooo::: REMOVER O ESQUEMA DE VENDAS DROP SCHEMA vendas CASCADE ; RRReeemmmooovvveeerrr uuummmaaa tttaaabbbeeelllaaa (((pppeeerrrmmmaaannneeennnttteee,,, tttiiipppooo 222 ooouuu 333))) eee tttooodddaaasss aaasss sssuuuaaasss tttaaabbbeeelllaaasss dddeee ííínnndddiiiccceeesss... DROP TABLE nome_tabela CASCADE/RESTRICT ; NNNoootttaaasss ♦ RESTRICT impedirá a remoção de tabelas que são referenciadas em visões ou restrições de integridade. ♦ CASCADE realizará a remoção de tabelas e das visões e restrições vinculadas. EEExxxeeemmmppplllooo::: REMOVER A TABELA DE PEÇAS DROP TABLE peças CASCADE ; RRReeemmmooovvveeerrr uuummmaaa tttaaabbbeeelllaaa ííínnndddiiiccceee... DROP INDEX nome_tabela_índice; NNNoootttaaasss ♦ Não é um comando padrão. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 12 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo::: REMOVER ÍNDICE PARA PEÇA DROP INDEX código_peça ; RRReeemmmooovvveeerrr uuummmaaa tttaaabbbeeelllaaa vvviiisssãããooo... DROP VIEW nome_tabela_virtual; EEExxxeeemmmppplllooo::: ELIMINAR A VISÃO COM OS FORNECEDORES COM ÓTIMA QUALIDADE DROP VIEW FORNECEDORES_BONS; AAAdddiiiccciiiooonnnaaarrr uuummm cccooommmeeennntttááárrriiiooo aaa uuummmaaa tttaaabbbeeelllaaa... COMMENT ON TABLE nome_tabela IS comentário; EEExxxeeemmmppplllooo::: INSERIR COMENTÁRIO NA TABELA PROJETOS COMMENT ON TABLE Projeto IS Representa os projetos de veículo para montagem; 10 - LINGUAGEM DE MANIPULAÇÃO DE DADOS (DML) CCCooonnnsssuuullltttaaarrr dddeee uuummmaaa ooouuu mmmaaaiiisss tttaaabbbeeelllaaasss... SELECT [DISTINCT] nome_coluna,.... FROM nome_tabela, .... [WHERE (condições)] [GROUP BY nome_coluna, ....] [HAVING (condições)] [{INTERSECT | MINUS | UNION} comando_select] [ORDER BY nome_coluna {ASC | DESC}, ....]; NNNoootttaaasss::: ♦ Cada cláusula permite a especificação de algum elemento referente ás operações relacionadas a álgebra relacional : SELECT - o que deseja-se na tabela de resultado FROM - de onde retirar os dados necessários WHERE - condições para busca dos resultados GROUP BY - agrupamento de resultados HAVING - condições para a definição de grupos na tabela de resultados INTERSECT - permite a interseção de tabelas Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 13 – Parte V (Programação: Linguagem SQL) MINUS - permite a diferença entre tabelas UNION - permite a união de tabelas ORDER BY - estabelece a ordenação lógica da tabela de resultados ♦ Este comando se tornou o mais importante da linguagem SQL devido ao seu poder de consulta. A possibilidade de utilização das operações relacionais (união, diferença, interseção e obviamente seleção) com extrema simplicidade na manipulação das tabelas despertou o interesse dos pesquisadores e projetista de gerenciadores de Bancos de Dados. EEExxxeeemmmppplllooo 111 ––– “““ bbbááásssiiicccooo “““ OBTER O NOME DE TODAS AS PEÇAS SELECT PeNome PeNome FROM Peça; Cinto Volante Lanterna Limpador Painel EEExxxeeemmmppplllooo 222 ––– DDDiiissstttiiinnncccttt OBTER O CÓDIGO DE TODAS AS PEÇAS FORNECIDAS SELECT DISTINCT PeNro PeNro FROM Fornece_para; PE1 PE2 PE3 PE4 PE5 EEExxxeeemmmppplllooo 333 ––– WWWhhheeerrreee OBTER O NOME E CÓDIGO DOS FORNECEDORES DE CAMPINAS SELECT FNome, FNro FNome FNro FROM Fornecedor Plastec F1 WHERE FCidade = ‘Campinas’; Kirurgic F3 EEExxxeeemmmppplllooo 444 ––– OOOpppeeerrraaadddooorrreeesss AAArrriiitttmmmééétttiiicccooosss OBTER O NOME E A DURAÇÃO EM DIAS DE CADA PROJETO SELECT PNome, (PDuração * 30) AS Dias FNome Dias FROM Projeto; Detroit 150 Pegasus 90 Alfa 60 Sea 90Paraíso 30 Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 14 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo 555 ––– OOOpppeeerrraaadddooorrreeesss RRReeelllaaaccciiiooonnnaaaiiisss OBTER O NOME DOS PROJETOS DE CUSTO MENOR QUE $28000 SELECT PNome PNome FROM Projeto Alfa WHERE PCusto < 28000; Sea Paraíso EEExxxeeemmmppplllooo 666 ––– OOOpppeeerrraaadddooorrreeesss LLLóóógggiiicccooosss OBTER O NOME DAS PEÇAS DE COR VERMELHA E COM PREÇO MAIOR QUE $25 SELECT PeNome PeNome FROM Peça Painel WHERE (PeCor = ‘Vermelho’ AND PePreço > 25); EEExxxeeemmmppplllooo 777 ––– OOOrrrdddeeerrr BBByyy OBTER, EM ORDEM DECRESCENTE DE PREÇO, O NOME DAS PEÇAS DE COR VERMELHA E COM PREÇO MAIOR QUE $ 15 SELECT PeNome PeNome FROM Peça Painel WHERE (PeCor = ‘Vermelho’ Volante AND PePreço > 15 ) ORDER BY PePreço DESC; EEExxxeeemmmppplllooo 888 ––– IIInnn OBTER, EM ORDEM CRESCENTE DE PREÇO, O NOME DAS PEÇAS DE COR VERMELHA OU AMARELA E COM PREÇO DE $09, $ 18, $ 22 , $40 OU $ 90 SELECT PeNome PeNome FROM Peça Limpador WHERE ((PeCor = ‘Vermelho’ Volante OR PeCor = ‘Amarelo‘) AND PePreço IN (09, 18, 22, 40, 90) ) ORDER BY PePreço ASC; EEExxxeeemmmppplllooo 999 ––– NNNooottt OBTER O NOME DAS PEÇAS CUJO PREÇO NÃO É $ 09, $14, $60 SELECT PeNome PeNome FROM Peça Cinto WHERE PePreço NOT IN (09, 14, 60); Volante Painel Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 15 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo 111000 ––– LLLiiikkkeee OBTER O NOME DOS FORNECEDORES RESIDENTES EM CIDADES INICIADAS POR S SELECT Fnome FNome FROM Fornecedor C&M WHERE Fcidade LIKE ´S%´; Equipament EEExxxeeemmmppplllooo 111111 ––– BBBeeetttwwweeeeeennn OBTER O NOME DOS PROJETOS COM PREÇO ENTRE $20000 E $30000 SELECT Pnome PNome FROM Projeto Alfa WHERE (PCusto BETWEEN 20000 AND 30000); Sea EEExxxeeemmmppplllooo 111222 ––– “““RRReeelllaaaçççõõõeeesss EEExxxppplllíííccciiitttaaasss””” OBTER O CÓDIGO DOS FORNECEDORES PARA O PROJETO P5 SELECT Fornece_para.Fnro FNro FROM Fornece_para F4 WHERE Fornece_para.PNro = ´P5´; F3 EEExxxeeemmmppplllooo 111333 ––– “““JJJoooiiinnn””” OBTER O NOME DAS PEÇAS UTILIZADAS NO PROJETO P4 SELECT Peça.PeNome PeNome FROM Peça, Fornece_para Cinto WHERE (Fornece_para.PNro = ´P4´ Lanterna AND Fornece_para.PeNro = Peça.PeNro); EEExxxeeemmmppplllooo 111444 ––– “““JJJoooiiinnn cccooommm IIINNN ::: 111 SSSuuubbb---SSSeeellleeecccttt””” OBTER O NOME DAS PEÇAS UTILIZADAS NO PROJETO P5 SELECT Peça.PeNome PeNome FROM Peça Limpador WHERE Peça.PeNro IN ( SELECT DISTINCT Fornece_para.PeNro PeNro FROM Fornece_para PE4 WHERE Fornece_para.PNro = ´P5´ ); Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 16 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo 111555 ––– “““JJJoooiiinnn cccooommm IIINNN ::: 222 SSSuuubbb---SSSeeellleeeccctttsss eeemmm mmmeeesssmmmooo nnnííívvveeelll””” OBTER O NOME DAS PEÇAS FORNECIDAS POR AMBOS OS FORNECEDORES F4 E F3 SELECT Peça.PeNome PeNome FROM Peça Limpador WHERE Peça.PeNro IN ( SELECT Fornece_para.PeNro PeNro FROM Fornece_para PE4 WHERE Fornece_para.FNro = ´F4´) AND Peça.PeNro IN ( SELECT Fornece_para.PeNro PeNro FROM Fornece_para PE3 WHERE Fornece_para.FNro = ´F3´ ); PE4 EEExxxeeemmmppplllooo 111666 ––– “““JJJoooiiinnn cccooommm IIINNN ::: 333 nnnííívvveeeiiisss dddeee SSSuuubbb---SSSeeellleeeccctttsss””” OBTER O NOME DAS PEÇAS UTILIZADAS NOS PROJETOS COM DURAÇÃO MAIOR QUE 3 MESES SELECT Peça.PeNome PeNome FROM Peça Painel WHERE Peça.PeNro IN ( SELECT Fornece_para.PeNro PeNro FROM Fornece_para PE5 WHERE Fornece_para.PNro IN ( SELECT PNro PNro FROM Projeto P1 WHERE PDuração > 3 ) ); EEExxxeeemmmppplllooo 111777 ––– “““JJJoooiiinnn cccooommm AAANNNYYY””” OBTER O NOME DAS PEÇAS FORNECIDAS POR ALGUM FORNECEDOR DE PIRACICABA SELECT Peça.PeNome PeNome FROM Peça Limpador WHERE Peça.PeNro IN ( SELECT Fornece_para.PeNro PeNro FROM Fornece_para PE4 WHERE Fornece_para.FNro =ANY ( SELECT FNro FNro FROM Fornecedor F4 WHERE FCidade = ´Piracicaba´)); Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 17 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo 111888 ––– “““JJJoooiiinnn cccooommm AAALLLLLL””” OBTER O NOME DAS PEÇAS NÃO FORNECIDAS POR FORNECEDORES DE CATEGORIA A SELECT Peça.PeNome PeNome FROM Peça Cinto WHERE Peça.PeNro IN ( Volante SELECT DISTINCT.PeNro Painel PeNro FROM Fornece_para PE4 WHERE Fornece_para.FNro <>ALL ( PE2 SELECT FNro PE5 FNro FROM Fornecedor F3 WHERE FCateg = ´A´ )); F4 EEExxxeeemmmppplllooo 111999 ––– “““JJJoooiiinnn cccooommm EEEXXXIIISSSTTT””” OBTER O NOME DOS FORNECEDORES QUE FORNECEM A PEÇA PE2 SELECT Fnome FNome FROM Fornecedor C&M WHERE EXIST ( SELECT * FROM Fornece_para WHERE (Fornece_para.Fnro = Fornecedor.FNro AND Fornece_para.PeNro = ´PE2´) ); EEExxxeeemmmppplllooo 222000 ––– GGGrrrooouuuppp BBByyy OBTER A QUANT. DE CADA PEÇA UTILIZADA EM TODOS O PROJETOS SELECT PeNro, SUM (Quant) AS TOTAL PeNro Total FROM Fornece_para PE1 5 GROUP BY PeNro ; PE2 2 PE3 2 PE4 5 PE5 1 EEExxxeeemmmppplllooo 222111 ––– HHHaaavvviiinnnggg OBTER O CÓDIGO DAS PEÇAS QUE SÃO UTILIZADAS EM UMA QUANTIDADE SUPERIOR A 1 UNIDADE NA SOMATÓRIA DE TODOS OS PROJETOS SELECT PeNro, SUM (Quant) AS TOTAL PeNro TOTAL FROM Fornece_para PE1 5 GROUP BY PeNro PE2 2 HAVING SUM (Quant) > 1 ; PE3 2 PE4 5 Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 18 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo 222222 ––– UUUnnniiiooonnn OBTER O CÓDIGO DAS PEÇAS COM PREÇO MENOR QUE $ 20 OU QUE POSSUEM A COR VERMELHA SELECT PeNro PeNro FROM Peça PE2 WHERE PePreço < 20 PE3 PeNro UNION PE4 PE2 SELECT PeNro PE3 FROM Peça PeNro PE4 WHERE PeCor = ´Vermelho´ ; PE2 PE5 PE5 EEExxxeeemmmppplllooo 222333 ––– IIInnnttteeerrrssseeecccttt OBTER O CÓDIGO DAS PEÇAS COM PREÇO MENOR QUE $ 20 E QUE POSSUEM A COR VERMELHA SELECT PeNro PeNro FROM Peça PE2 WHERE PePreço < 20 PE3 PeNro INTERSECT PE4 PE2 SELECT PeNro FROM Peça PeNro WHERE PeCor = ´Vermelho´ ; PE2 PE5 EEExxxeeemmmppplllooo 222444 ––– MMMiiinnnuuusss OBTER O CÓDIGO DAS PEÇAS COM PREÇO MENOR QUE $ 20 E QUE NÃO POSSUEM A COR VERMELHA SELECT PeNro PeNro FROM Peça PE2 WHERE PePreço < 20 PE3 PeNro MINUS PE4 PE2 SELECT PeNro PE4 FROM Peça PeNro WHERE PeCor = ´Vermelho´ ; PE2 PE5 EEExxxeeemmmppplllooo 222555 ––– “““VVVaaarrriiiááávvveeeiiisss””” OBTER O CÓDIGO DOS FORNECEDORES CONCORRENTES ENTRE SI SELECT X.FNro, Y.FNro X.FNro Y.Fnro FROM Fornece_para AS X, Fornece_para As Y F4 F3 WHERE (X.PeNro = Y.PeNro F3 F4 AND X.FNro <> Y.FNro) ; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 19 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo 222666 ––– “““NNNaaatttuuurrraaalll JJJoooiiinnn””” OBTER O NOME DAS PEÇAS FORNECIDAS QUE TENHAM O PREÇO MAIOR QUE $20 SELECT DISTINCT PeNome PeNome FROM (Peça NATURAL JOIN Fornece_para) Cinto WHERE PePreço > 20 ; Painel EEExxxeeemmmppplllooo 222777 ––– “““EEEqqquuuiii JJJoooiiinnn””” OBTER O NOME DAS PEÇAS FORNECIDAS QUE NÃO SÃO VERMELHAS E AS SUAS CORES SELECT DISTINCT PeNome, PeCor PeNome PeCor FROM (Peça JOIN Fornece_para ON PeNro = PeNro) Cinto Azul WHEREPeCor <> ´Vermelho´ ; Lanterna Preto Limpador Amarelo EEExxxeeemmmppplllooo 222888 ––– “““OOOuuuttteeerrr JJJoooiiinnn””” OBTER TODAS AS INFORMAÇÕES SOBRE TODAS AS PEÇAS SELECT * FROM (Peça LEFT OUTER JOIN Fornece_para ON PeNro = PeNro); PeNro PeNome PePreço PeCor FNro PNro Quant PE1 Cinto 22 Azul F5 P4 5 PE2 Volante 18 Vermelho F2 P2 1 PE2 Volante 18 Vermelho F3 P5 2 PE3 Lanterna 14 Preto F3 P4 2 PE4 Limpador 09 Amarelo F4 P5 3 PE4 Limpador 09 Amarelo F3 P5 2 PE5 Painel 43 Vermelho F1 P1 1 PeNro PeNome PePreço PeCor FNro PNro Quant PE1 Cinto 22 Azul F5 P4 5 PE2 Volante 18 Vermelho F2 P2 1 PE2 Volante 18 Vermelho F3 P5 2 PE3 Lanterna 14 Preto F3 P4 2 PE4 Limpador 09 Amarelo F4 P5 3 PE4 Limpador 09 Amarelo F3 P5 2 PE5 Painel 43 Vermelho F1 P1 1 PeNro PeNome PePreço PeCor FNro PNro Quant PE1 Cinto 22 Azul F5 P4 5 PE2 Volante 18 Vermelho F2 P2 1 PE2 Volante 18 Vermelho F3 P5 2 PE3 Lanterna 14 Preto F3 P4 2 PE4 Limpador 09 Amarelo F4 P5 3 PE4 Limpador 09 Amarelo F3 P5 2 PE5 Painel 43 Vermelho F1 P1 1 PE6 Calota 70 Azul NULL NULL NULL Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 20 – Parte V (Programação: Linguagem SQL) IIInnnssseeerrriiirrr dddaaadddooosss eeemmm uuummmaaa tttaaabbbeeelllaaa INSERT INTO nome_tabela ( nome_coluna, ......) VALUES (valor, .....) ; NNNoootttaaasss ♦ EEExxxeeemmmppplllooo::: INSERIR UMA NOVA PEÇA PE6 INSERT INTO Peça (PeNro, PeNome. PePreço, PeCor) VALUES (´PE6´,´Espelho´,12,´Azul´) ; AAAtttuuuaaallliiizzzaaarrr dddaaadddooosss eeemmm uuummmaaa tttaaabbbeeelllaaa UPDATE nome_tabela SET nome_coluna = valor [WHERE (condição_de_localização)] ; NNNoootttaaasss ♦ A condição_de_localização permite a seleção de nenhum, um ou mais registros. EEExxxeeemmmppplllooo::: ALTERAR A PEÇA PE6 PARA QUE O PREÇO SEJA $ 25 UPDATE Peça SET PePreço = 25 WHERE (PeNro = ´PE6´) ; RRReeemmmooovvveeerrr dddaaadddooosss eeemmm uuummmaaa tttaaabbbeeelllaaa DELETE FROM nome_tabela WHERE (condição_de_localização) ; NNNoootttaaasss ♦ A condição_de_localização permite a seleção de nenhum, um ou mais registros. EEExxxeeemmmppplllooo::: REMOVER A PEÇA PE6 DELETE FROM Peça WHERE (PeNro = ´PE6´) ; 11 - LINGUAGEM DE CONTROLE DE DADOS (DCL) Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 21 – Parte V (Programação: Linguagem SQL) CCCrrriiiaaarrr uuummm uuusssuuuááárrriiiooo CREATE USER nome_usuário IDENTIFIED BY senha; EEEssstttaaabbbeeellleeeccceeerrr ppprrriiivvviiilllééégggiiiooosss dddeee aaaccceeessssssooo GRANT {ALL|ALTER|DELETE|INDEX|INSERT|SELECT|UPDATE} [nome_coluna]|EXECUTE ON PROCEDURE nome_módulo ON nome_tabela,... TO {nome_usuário,...|PUBLIC }; NNNoootttaaasss ♦ PUBLIC permite o estabelecimento de privilégios para todos os usuários. EEExxxeeemmmppplllooosss::: PERMITIR QUE JOÃO E MARTA ATUALIZEM O PREÇO DAS PEÇAS GRANT UPDATE PePreço ON Peça TO João, Marta; PERMITIR QUE LUIZ EXECUTE UM PROCEDIMENTO ARMAZENADO GRANT EXECUTE ON PROCEDURE Cria_novo_projetoTO Luiz; RRReeevvvooogggaaarrr ppprrriiivvviiilllééégggiiiooosss dddeee aaaccceeessssssooo REVOKE {ALL|ALTER|DELETE|INDEX|INSERT|SELECT|UPDATE} [nome_coluna,...]|EXECUTE ON PROCEDURE|TRIGGER nome_módulo ON nome_tabela,... TO {nome_usuário,...|PUBLIC }; NNNoootttaaasss ♦ PUBLIC permite a revogação de privilégios para todos os usuários. EEExxxeeemmmppplllooo::: RETIRAR A PERMISSÃO DE TODOS OS USUÁRIO, IMPEDINDO-OS DE REALIZAR QUALQUER MANIPULAÇÃO DA TABELA DE FORNECEDORES REVOKE ALL ON Fornecedor TO PUBLIC; EEEssstttaaabbbeeellleeeccceeerrr uuummmaaa rrreeessstttrrriiiçççãããooo mmmooommmeeennntttââânnneeeaaa dddeee aaaccceeessssssooo LOCK TABLE nome_tabela IN {SHARE|EXCLUSIVE} MODE ; EEExxxeeemmmppplllooo::: RESTRINGIR O ACESSO À TABELA DE PROJETOS, PERMITINDO QUE OUTROS USUÁRIOS APENAS LEIAM DADOS MAS NÃO POSSAM INSERIR, REMOVER OU ATUALIZAR LOCK TABLE Projeto IN EXCLUSIVE MODE ; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 22 – Parte V (Programação: Linguagem SQL) 12 - EMBEDDED SQL IIInnniiiccciiiaaarrr cccooommmaaannndddooo dddeee SSSQQQLLL EXEC-SQL ou &SQL FFFiiinnnaaallliiizzzaaarrr cccooommmaaannndddooo dddeee SSSQQQLLL END-EXEC ou ) ou ; EEExxxeeemmmppplllooo::: TRECHO DE PROGRAMA PARA CONSULTA O SALÁRIO DE UM EMPREGADO ................ { Inicialização do programa } &SQL Begin Declare Section; resp : char; Emp : Record of Nome : string [30]; Sal, Cod : integer ; End; &SQL End Declare Section; ................. { Trecho de programa } resp := ´s´; While (resp = ´s´) or (resp = ´S´) do Begin Write (´Entre com o Código do empregado : ´); readln(Emp.Cod); &SQL SELECT Nome, Salario INTO :Emp.Nome , :Emp.Sal FROM Empregado WHERE Cod = :Emp.Cod; Writeln(´O empregado ´,Emp.Nome,´recebe R$ ´,Emp.Sal); Write(´Deseja consultar outro (s/n) ? ´); readln (resp); End; ................. { continua o programa } IIInnniiiccciiiaaarrr ssseeeçççãããooo dddeee dddeeeccclllaaarrraaaçççõõõeeesss dddeee vvvaaarrriiiááávvveeeiiisss BEGIN DECLARE SECTION; FFFiiinnnaaallliiizzzaaarrr ssseeeçççãããooo dddeee dddeeeccclllaaarrraaaçççõõõeeesss dddeee vvvaaarrriiiááávvveeeiiisss END DECLARE SECTION; NNNoootttaaasss Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 23 – Parte V (Programação: Linguagem SQL) ♦ Todas variáveis usada em um comando SQL deve ser ter sua declaração entre o inicio e o fim de seção. As variáveis SQLSTATE e SQLCODE não necessitam ser declaradas ♦ As variáveis devem ser declaradas nos tipos compatíveis com os atributos vinculados DDDeeefffiiinnniiirrr TTTrrraaannnsssaaaçççãããooo SET TRANSACTION [READ WRITE\READ ONLY] [WAIT\NO WAIT] [[ISOLATION LEVEL] {SNAPSHOT[TABLE STABILIT]\ READ COMMITED [[NO] RECORD_VERSION]}] [RESERVING nome_tabela, ... [FOR [SHARED/PROTECTED] {READ\WRITE}], .... ; NNNoootttaaasss ♦ READ WRITE/READ ONLY - define o motivo do acesso ♦ WAIT/NO WAIT - espera ou não em uma fila até o término das transações concorrentes ♦ ISOLATION LEVEL - como a transação age em relação as transações concorrentes ♦ SNAPSHOT - a transação retém uma visão estática do BD ♦ TABLE STABILITY - não pode escrever sobre esta visão ♦ READ COMMITED - a transação retém uma visão não estática do BD ♦ NO RECORD_VERSION - visão alterada apenas quando a última transação terminar ♦ RECORD_VERSION - visão alterada em cada transação encerrada ♦ RESERVING - reservar previamente as tabelas para evitar o deadlock ♦ Uma Transação pode atualizar uma Tabela Temporária se estiver especificada READ ONLY EEExxxeeemmmppplllooo::: ESTABELECER UMA TRANSAÇÃO QUE UTILIZA AS TABELAS PEÇA, FORNECEDOR E PROJETO SET TRANSACTION ISOLATION LEVEL READ COMMITTED NO RECORD_VERSION WAIT RESERVING Peça, Fornecedor FOR SHARED WRITE, Projeto FOR PROTECTED WRITE; EEEfffeeetttiiivvvaaarrr (((cccooonnnfffiiirrrmmmaaarrr))) aaasss ooopppeeerrraaaçççõõõeeesss dddaaa tttrrraaannnsssaaaçççãããooo COMMIT ; RRReeejjjeeeiiitttaaarrr (((nnneeegggaaarrr))) aaasss ooopppeeerrraaaçççõõõeeesss dddaaa tttrrraaannnsssaaaçççãããooo ROLLBACK ; NNNoootttaaasss ♦ A efetivação ou rejeição ocorrem sobre a última transação realizada. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 24 – Parte V (Programação: Linguagem SQL) DDDeeefffiiinnniiiçççãããooo dddeee uuummm CCCuuurrrsssooorrr DECLARE nome_cursor CURSOR FOR (comando_select) FOR UPDATE OF nome_coluna\DELETE ; EEEssstttaaabbbeeellleeeccceeerrr ooo iiinnníííccciiiooo dddeee uuutttiiillliiizzzaaaçççãããooo dddeee uuummm CCCuuurrrsssooorrr OPEN nome_cursor ; EEEssstttaaabbbeeellleeeccceeerrrooo fffiiimmm dddeee uuutttiiillliiizzzaaaçççãããooo dddeee uuummm CCCuuurrrsssooorrr CLOSE CURSOR nome_cursor ; MMMooovvveeerrr ooo CCCuuurrrsssooorrr pppeeelllooosss rrreeegggiiissstttrrrooosss dddeee uuummm aaarrrqqquuuiiivvvooo FETCH [NEXT\PRIOR\FIRST\LAST\ {default NEXT} ABSOLUTE i\ RELATIVE i] nome_cursor INTO variável,.. ; NNNoootttaaasss ♦ Cursor é um tipo de variável especial que indica a tupla corrente). ♦ No início de todo comando SQL o cursor se posiciona antes da primeira tupla ♦ ABSOLUTE i - posiciona na i-ésima tupla da relação ♦ RELATIVE i - posiciona i posições adiante da tupla corrente (indicada pelo cursor) EEExxxeeemmmppplllooo::: TRECHO DE PROGRAMA PARA ATUALIAZAR O SALÁRIO DE UM EMPREGADO { O arquivo EMPREGRADO tem os campos Cod, Nome e Salario } ................ { Inicialização do programa } &SQL Begin Declare Section; Adicional : real; Loop : char; Emp : Record of Nome : string [30]; Sal, Cod : integer ; &SQL End Declare Section; ................. { Trecho de programa } &SQL DECLARE C_EMP CURSOR FOR SELECT Nome FROM Empregado WHERE :Emp.Cod = Cod; FOR UPDATE OF Salario ; ................. &SQL OPEN C_EMP; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 25 – Parte V (Programação: Linguagem SQL) &SQL FETCH C_EMP INTO :Emp.Nome; while SQLCODE = O do {SQLCODE é uma variável global de retorno p/ comandos SQL } Begin Writeln (Emp.Nome); Write (´Entre com a bonificação : ´); Readln (Adicional); &SQL UPDATE Empregado SET Salario = Salario + :Adicional WHERE CURRENT OF Empregado; {CURRENT é uma variável que indica a tupla corrente} &SQL FETCH C_EMP INTO :Emp.Nome; end; &SQL CLOSE CURSOR C_EMP; ................. { continua o programa } DDDeeeccclllaaarrraaarrr uuummmaaa tttaaabbbeeelllaaa (((ttteeemmmpppooorrrááárrriiiaaa TTTiiipppooo 111))) eee dddeeefffiiinnniiirrr sssuuuaaasss cccooollluuunnnaaasss eee rrreeessstttrrriiiçççõõõeeesss... DECLARE LOCAL TEMPORARY TABLE MODULE.nome_tabela (nome_coluna tipo,....... (CONSTRAINT (nome_restrição ) [ON COMMIT {PRESERVE|DELETE} ROWS ]; NNNoootttaaasss ♦ A tabela temporária tipo 1 deve ser declarada em um programa hospedeiro de SQL. ♦ A referencia a tabela deve ser referenciada como MODULE.nome_tabela. ♦ A tabela pode ser acessada apenas nos procedimentos do módulo de programa que contem este comando. ♦ Este comando deve fisicamente preceder todas as definições de procedures e cursores ♦ Em ON COMMIT defini-se se a tabela mantida ou não após o commit. Default é DELETE. ♦ ASSERTION, DOMAIN e restrições de tabelas vinculadas a uma tabelas persistente não podem referenciar tabelas tipo 1. ♦ Visões não podem referenciar tabelas tipo 1. ♦ A tabela é automaticamente removida no final da seção. DDDeeeccclllaaarrraaarrr uuummmaaa tttaaabbbeeelllaaa (((ttteeemmmpppooorrrááárrriiiaaa TTTiiipppooo 222 eee 333))) eee dddeeefffiiinnniiirrr sssuuuaaasss cccooollluuunnnaaasss eee rrreeessstttrrriiiçççõõõeeesss... DECLARE {LOCAL|GLOBAL} TEMPORARY TABLE MODULE.nome_tabela (nome_coluna tipo,....... (CONSTRAINT (nome_restrição ) [ON COMMIT {PRESERVE|DELETE} ROWS ]; NNNoootttaaasss ♦ A tabela temporária tipo 2 deve ser declarada LOCAL e a tabela tipo 3 como GLOBAL. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 26 – Parte V (Programação: Linguagem SQL) ♦ A tabela tipo 2 é “instanciada” (efetivamente criada) quando é acessada pela primeira vez em um Módulo. Cada Módulo tem uma versão da tabela declarada. ♦ A tabela tipo 3 é “instanciada” quando é acessada pela primeira vez em uma SQL-Session. Cada Seção tem um versão da tabela declarada. ♦ A tabela tipo 2 e 3 podem ser removidas (DROP) e alteradas (ALTER) 13 – ROTINAS ARMAZENADAS (SQL/PSM4) EEExxxeeecccuuutttaaarrr uuummmaaa rrroootttiiinnnaaa EXECUTE PROCEDURE|FUNCTION nome_rotina [parâmetro,...] EEExxxeeemmmppplllooo::: EXECUTAR O PROCEDIMENTO AUMENTAR O SALÁRIO EXECUTE PROCEDURE Aumentar_salario ; CCCrrriiiaaarrr ooouuu DDDeeefffiiinnniiirrr uuummm ppprrroooccceeedddiiimmmeeennntttooo ooouuu fffuuunnnçççãããooo CREATE|DECLARE PROCEDURE|FUNCTION nome_rotina ( [IN | OUT | INOUT parâmetro tipo_de_dado, ...]) [RETURNS tipo_de_dado] [LANGUAGE nome] [SPECIFIC nome_único] AS [DECLARE VARIABLE variável tipo_de_dado>, ....] BEGIN (comandos) END; NNNoootttaaasss ♦ CREATE é utilizado quando este comando segue uma criação de Esquema. ♦ DECLARE é utilizado quando este comando segue uma criação de Módulo ♦ Rotinas poderão ter nomes repetidos devendo ser identificados pelo seu nome em SPECIFIC ♦ RETURN é utilizado apenas pela funções ♦ LANGUAGE permite especificar a linguagem não qual está escrita a rotina EEExxxeeemmmppplllooo::: OBTER A MÉDIA DE PREÇOS DAS PEÇAS CREATE FUNCTION média_preços (IN PePreço REAL) RETURNS (REAL) AS BEGIN SELECT AVG(PePreço) 4 Persistent Stored Modules Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 27 – Parte V (Programação: Linguagem SQL) INTO :média_preços; FROM Peça END; RRReeemmmooovvveeerrr uuummmaaa rrroootttiiinnnaaa DROP PROCEDURE|FUNCTION nome_rotina ; VVViiisssuuuaaallliiizzzaaarrr aaasss dddeeepppeeennndddêêênnnccciiiaaasss dddeee uuummmaaa rrroootttiiinnnaaa SHOW PROCEDURE|FUNCTION nome_rotina ; CCCrrriiiaaarrr uuummm MMMóóóddduuulllooo CREATE MODULE [nome_módulo] [NAMES ARE nome_conjunto_caracteres ] LANGUAGE nome [SCHEMA nome_esquema] [AUTHORIZATION usuário] [PATH lista_esquemas] [lista_definição_tabelas_temporárias] definição_elementos_do_módulo END; 14 – GATILHOS (“TRIGGERS”) DDDeeefffiiinnniiirrr uuummm GGGaaatttiiilllhhhooo CREATE TRIGGER nome_gatilho FOR nome_tabela [ACTIVE\INACTIVE] {BEFORE\AFTER} {DELETE\INSERT\UPDATE}[POSITION número] AS [DECLARE VARIABLE variável tipo_de_dado>, ....] BEGIN (comandos) END; NNNoootttaaasss::: ♦ ACTIVE - (Default.) especifica que a ação do trigger tem efeito. ♦ INACTIVE - especifica que a ação do trigger não tem efeito ♦ BEFORE - especifica que o trigger é executado antes da operação associada. ♦ AFTER - especifica que o trigger é executado depois da operação associada. ♦ DELETE \ INSERT \ UPDATE - especifica a operação que causa a execução do trigger ♦ POSITION número - especifica a ordem de execução de triggers sobre a mesma ação e sobre as mesmas tabelas ou visões. O número deve variar entre 0 e 32767, inclusive. O menor número Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 28 – Parte V (Programação: Linguagem SQL) executa primeiro. Default: 0 = primeiro trigger a executar. Triggers com os mesmos números são executados em ordem randômica. EEExxxeeemmmppplllooo::: ATUALIZAR O SALÁRIO DE EMPREGADOS CREATE TRIGGER SAVE_SALARY_CHANGE FOR Empregado AFTER UPDATE AS BEGIN IF (OLD.Salario <> NEW.Salario) THEN INSERT INTO SALARY_HISTORY (EMP_NO,CHANGE_DATE,UPDATER_ID,OLD_SALARY,PERCENT_CHANGE) VALUES (OLD.Codigo, "now", USER, OLD.Salario, (NEW.Salario - OLD.Salario) * 100 / OLD.Salario); END; NNNoootttaaasss::: ♦ NEW.coluna novo valor da coluna em uma operação de INSERT ou UPDATE. ♦ OLD.coluna velho valor da coluna antes da operação de UPDATE ou DELETE. ♦ Deve existir uma grande preocupação quanto ao testes individuais dos códigos de cada trigger, pelo fato que equivocados encadeamento de triggers podem levar a situações de inconsistência ou mesmo de looping infinito. RRReeemmmooovvveeerrr uuummm GGGaaatttiiilllhhhoooDROP TRIGGER nome_gatilho ; AAAlllttteeerrraaarrr uuummm GGGaaatttiiilllhhhooo ALTER TRIGGER nome_gatilho [ACTIVE\INACTIVE] {BEFORE\AFTER} {DELETE\INSERT\UPDATE}[POSITION número] AS [DECLARE VARIABLE variável tipo_de_dado>, ....] BEGIN (comandos) END; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 29 – Parte V (Programação: Linguagem SQL) EEExxxeeemmmppplllooo::: COLOCAR UM GATILHO INATIVO ALTER TRIGGER Verifica_Salario INACTIVE; 15 – NOVIDADES DO SQL3 Os conjuntos de tipos de dados do SQL-92 e suas operações são bastante limitados, consequentemente muitos problemas do mundo real não conseguem ser codificados eficientemente ou então são extremamente difíceis de se codificar. CADASTRO SIMPLES DE COMPRA E VENDA CREATE TABLE operação ( Nome varchar(30), Taxa float, Data_Venda date, Data_Compra date, Juros float ); ATUALIZAÇÃO DE JUROS UPADATE operação SET Juros = Taxa * (Data_Venda – Data_Compra); O problema do comando UPDATE acima é que as operações de datas realizadas no SQL- 92 baseiam-se no calendário Juliano, isto é, o calendário padrão é o juliano que possui a seguinte característica a verificação de ano bissexto para o calculo da diferença de dias (ex. 15 de março - 15 de fevereiro = 28 ou 29 dias, dependendo se o ano é ou não bissexto). No entanto, nos EUA para transações financeiras o calendário padrão é o calendário de Wall Street, onde 15 de março - 15 de fevereiro = 30 dias, independentemente do ano. Isto é, o calendário de Wall Street possui 12 meses, todos com 30 dias. O programador pode criar uma função que transforme esta data juliana no padrão desejado de Wall Street. Toda operação com datas deverá chamar esta rotina. Mas a rotina de tratamento de datas do SQL não pode ser substituída pela rotina criada pelo usuário e também temos queda de performance pois sempre que precisarmos operar com datas será utilizado o código feito pelo usuário que é mais lento que o código do próprio SQL. A solução está baseada em conceitos Objeto- Relacionais. TIPOS ESTENDIDOS DE DADOS Os tipos estendidos de dados estão disponíveis em um SGBD Objeto Relacional e tem como conseqüência a eliminação dos problemas de simulação de tipos responsáveis por vários problemas de eficiência. Temos assim duas diferenças fundamentais entre: • Relacionais - a noção de domínio inclui somente como as representações de tipos são armazenadas e não existe nenhum comportamento associado ao domínio; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 30 – Parte V (Programação: Linguagem SQL) • Objeto-Relacionais - um tipo de dados é definido como uma representação armazenada em um tipo particular de informação juntamente com o apropriado operador e funções. O tipo de dados é informação e também operações. FUNÇÕES DEFINIDAS O programador, além de criar seus próprios tipos, pode também definir suas próprias funções. OPERADORES DEFINIDOS E por último, temos os operadores que assim como os tipos e funções também podem ser definidos pelo usuário. OBJETOS COMPLEXOS Objetos Complexos são objetos compostos de inúmeros tipos. Podem ser de três formas: • Registros • Conjuntos • Referência. TIPOS COMPOSTOS Tipos compostos, ou registros, podem ser formados por tipos definidos pelo SGBD ou por tipos definidos pelo usuário. CRIAR O TIPO TELEFONE CREATE TYPE telefone_t ( area varchar(3), numero varchar(7), descricao varchar(20) ); CRIAR O TIPO EMPREGADO CREATE TYPE empregado_t ( nome varchar(30), data_inicio date, salario int, endereço varchar(30), cidade varchar(30), estado varchar(2), CEP int ) ; CRIAR TABELA DE EMPREGRADOS CREATE TABLE emp OF TYPE empregado_t; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 31 – Parte V (Programação: Linguagem SQL) CONSTRUTORES DE TIPOS CONJUNTOS E REFERÊNCIA No Informix, sempre que um tipo T for definido, um tipo conjunto de T (SET(T)) também pode ser considerado um tipo. Da mesma forma, se um tipo T ( seja ele um tipo fixo do SGBD ou definido pelo usuário ), REF(T) – referência de T – também pode ser definido. MANIPULANDO OBJETOS COMPLEXOS Funções definidas pelo usuário recebem argumentos ou retornam resultados. As Funções podem aparecer na cláusula FROM. A “notação ponto cascata” deve ser utilizada para referenciar atributos. TIPOS COMPLEXOS & FUNÇÕES DEFINIDAS PELO USUÁRIO Para manipulação de tipos complexos a extensão da linguagem SQL implementada no Informix ( antigo Illustra ) permite que se escrevam funções que retornem ou recebam tipos compostos como parâmetro. TIPOS COMPLEXOS & FUNÇÕES NA CLÁUSULA FROM Em SQL tradicional, apenas tabelas podem ser colocadas na cláusula FROM de uma consulta. Porém em banco de dados orientado a objetos uma tabela pode ser tratada como um “container” de instâncias de um tipo compostos, desta forma o uso de funções que retornem instâncias de um certo tipo também podem ser colocadas na cláusula FROM. CRIAR FUNÇÃO OBTER TELEFONE CREATE FUNCTION calçado_telefone RETURN telefone_t AS SELECT telefone FROM dept WHERE deptnome = “calçados”; OBTER TELEFONE SELEC number FROM calcado_telefone WHERE area = 510; TIPOS COMPLEXOS & “NOTAÇÃO PONTO CASCATA” Tipos composto podem ser formados por diversas “partes” onde cada uma pode ser de um tipo. Um comando SELECT pode ser criado para selecionar apenas algumas partes de algum tipo composto. Para isto, o Informix permite que uma notação por ponto indique quais são as partes com as quais desejamos trabalhar. OBTER TELEFONE SELECT telefone.numero FROM dept WHERE dnome = “calcados” ; Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 32 – Parte V (Programação: Linguagem SQL) CONJUNTOS As mesmas extensões que são aplicadas a tipos compostos também são aplicadas a conjunto: Funções definidas pelo usuário recebem argumentos ou retornam resultados. Funções podem aparecer na cláusula FROM. A “notação ponto cascata” para referenciar atributos. REFERÊNCIA Referência são substitutos naturais para um relacionamento primary key-foreing key. Cada objeto num Banco de Dados Objeto-Relacional deve ser dotado de um identificador único e inalterável. Este identificador, conhecido como Object Identifier ( OID ) é atribuído a um objeto no ato de sua criação e em nenhum momento pode ser alterado. No SQL tradicional, para garantir Integridade Referencial é utilizado uma chave estrangeira que associa uma coluna de uma tabela a outra coluna de outra tabela. Isto pode ser inconveniente pois o valor destas chaves podem ser alterados a qualquer momento. Se, por exemplo, alteramos o valor de uma coluna que está sendo referenciada por outra, devemos tomar alguma ação como : impedir que seja feito ou altera todas as instâncias que referenciem a coluna alterada. Com o uso de um OID isto não serIA um problema pois toda a linha da coluna referenciada possui o mesmo OID. Como o mesmo não é alterado e a coluna que referencia faz referencia a este OID qualquer alteração não quebrará a Integridade Referencial do Banco. CONJUNTO DE REFERÊNCIA Assim como conjunto de tipos compostos, podemos Ter uma coluna que armazene um conjunto de referencias. Desta forma , colunas multivaloradas que eram impossíveis em banco de dados relacionais podem ser utilizadas em banco de dados objeto-relacional. OUTROS TIPOS DE CONSTRUTORES • List • Stack • Queue • Array • Insertable Array Array - pode-se associar um valor a qualquer elemento do array, a inserção de um valor no meio do array não faz com que o seu tamanho seja alterado. O array continua com o mesmo número de posições. Insertable Array - consideremos a necessidade de um editor de texto, onde as linhas de um documento são um array de strings de tamanho variável.Quando inserirmos um caracter no meio do array, vamos querer que todas as linhas subsequentes à inserção sejam descidas de um, isto é, todos os números das linhas serão aumentados e o array também. CREATE TYPE empregado ( nome varchar(30), data_inicio date, salário float, endereço varchar(30), cidade varchar(30), estado char(2), cep int); Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 33 – Parte V (Programação: Linguagem SQL) CREATE TABLE emp of type empregado; OBTER O SALÁRIO DE ABRIL DE TODOS OS EMPREGADOS RESIDENTES NO CEP 36400 SELECT salário[4] FROM emp WHERE cep=36400; Estamos usando aqui um array simples como construtor e veja como o nosso trabalho é facilitado, basta pegarmos a posição 4 do array que corresponderá aos salários de abril. Agora vamos contrastar esta solução com a implementada usando-se SQL-92. CREATE TABLE compensação( nome varchar(30), mês varchar(30), salário int); E a pesquisa ficaria assim: SELECT salário FROM compensação WHERE mês= "Abril" AND nome IN (SELECT nome FROM emp WHERE cep=36400); HERANÇA "O suporte à herança é a característica mais importante das linguagens objeto- relacionais tornando-as muito mais poderosas que as linguagens relacionais.". Stonebraker, Micheal .Assim como na extensão de tipos e nos objetos complexos, a herança permite ao usuário criar tipos novos. Mas em se tratando de herança, essa criação de tipos deve ser feita com cuidado porque o uso incontrolado pode gerar uma explosão de tipos que poderão comprometer a hierarquia dos mesmos devido à dificuldade de gerenciamento. Os SGBD’s OR suportam herança de dois tipos: de dados e de funções(métodos).O ponto básico da herança é que ela é definida em função dos tipos de dado criando uma hierarquia. Herança de dados - Aplica-se a tipos compostos e tabelas associadas a tipos. Possui como principal problema a ambigüidade. CREATE TYPE pessoa_t ( nome varchar(30) ); CREATE TYPE empregado_t ( salario int, Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 34 – Parte V (Programação: Linguagem SQL) datainicio date, endereço varchar(30), cidade varchar(30), estado varchar(30), cep int) UNDER pessoa_t; CREATE TYPE estudante_t ( gpa float) UNDER pessoa_t; A definição de herança é feita na cláusula under onde é especificado de quais tipos o tipo em questão irá herdar os campos. No exemplo acima, o tipo estudante_t herda o campo nome do tipo pessoa_t. Podemos ter também herança múltipla. CREATE TYPE estudante_emp_t ( porcentagem float) UNDER empregado_t , estudante_t ; O tipo estudante_emp_t herda todos os campos dos tipos empregado_t e estudante_t. O problema de ambigüidade ocorre quando um tipo de dados pode herdar seus campos de múltiplos supertipos. Tabelas - Tabelas não associadas a tipos são consideradas de tipo anônimo e portanto não podem fazer parte da hierarquia. Por isso deve ocorrer a associação de tipos a tabelas. CREATE TABLE pessoa OF TYPE pessoa_t; CREATE TABLE emp OF TYPE empregado_t UNDER pessoa; CREATE TABLE estudante OF TYPE estudante UNDER pessoa; CREATE TABLE estudante_emp OF TYPE estudante_emp_t UNDER estudante, emp; As tabelas são associadas a tipos e herdam os campos(colunas) das tabelas especificadas na cláusula under. CREATE TABLE pessoa OF TYPE pessoa_t; CREATE TABLE emp OF TYPE empregado_t SELECT nome FROM emp WHERE salario = 10.000 ; ! Pesquisa ocorre nas tabelas emp e estudante_emp Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 35 – Parte V (Programação: Linguagem SQL) SELECT nome FROM (only)emp WHERE salario = 10.000 ; ! Pesquisa ocorre somente na tabela emp Herança de funções - É a característica mais poderosa de herança nas linguagens OR. Aplica-se a funções definidas pelo usuário. È definida em função dos tipos dos argumentos das funções. CREATE FUNCTION overpaid (empregado_t) RETURN Boolean AS RETURN salario > (SELECT salario FROM emp WHERE nome = ‘Joe’ ); A hierarquia de tipos fica modificada pela inclusão do método overpaid. O escopo dos comandos SQL é determinado dinamicamente; SELECT nome FROM (only)emp WHERE overpaid; ! Overpaid avaliada na tabela emp SELECT nome FROM emp WHERE overpaid; ! Overpaid avaliada nas tabelas emp e estudante_emp O SGBD OR permite a herança automaticamente da função overpaid de um supertipo, e também suportam polimorfismo, ou seja , criação de funções de mesmo nome mas com significados diferentes e aplicadas a múltiplos tipos. CREATE FUNCTION overpaid (estudante_t) RETURNS Boolean as RETURN salario > (SELECT salario FROM emp WHERE nome = ‘Bill’); Com essa nova função, a hierarquia fica assim: SELECT nome FROM (only)emp WHERE overpaid; ! Overpaid avaliada na tabela emp Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 36 – Parte V (Programação: Linguagem SQL) SELECT nome FROM emp WHERE overpaid; ! Overpaid avaliada nas tabelas emp e estudante_emp 16 - OBSERVAÇÕES " Foram apresentados apenas alguns dos comandos mais representativos da SQL. " Geralmente existem diversas forma de se realizar uma mesma consulta utilizando o comando SELECT, então é importante ficar atento para o comando que tenha o melhor desempenho de execução. ❋
Compartilhar