Baixar Agora - Maceiras Sistemas
Propaganda
GENNARI & PEARTREE PROJETOS E SISTEMAS BANCO DE DADOS DB2 Desenvolvida para o programa de treinamento Unibanco por: Luiz Roberto de Souza ÍNDICE ANALÍTICO 1. BANCO DE DADOS DB2.................................................................................................................. 2 1.1. INTRODUÇÃO ................................................................................................................................. 2 1.2. CATÁLOGO .................................................................................................................................... 2 1.3. TABELAS DB2 ............................................................................................................................... 2 1.4. TIPOS DE DADOS DB2 .................................................................................................................... 2 1.5. CHAVES ......................................................................................................................................... 3 2. RELACIONAMENTOS .................................................................................................................... 4 2.1. TIPOS DE RELACIONAMENTOS ........................................................................................................ 4 2.2. INTEGRIDADE REFERENCIAL .......................................................................................................... 5 2.3. REGRAS DE INTEGRIDADE REFERENCIAL DO DB2 .......................................................................... 5 3. A LINGUAGEM SQL ........................................................................................................................ 6 3.1. INTRODUÇÃO ................................................................................................................................. 6 3.2. DB2 INTERACTIVE (DB2 INTERATIVO) – SPUFI ........................................................................... 6 3.3. OPERADORES RELACIONAIS ........................................................................................................... 7 3.4. COMANDOS DA STRUCTURED QUERY LANGUAGE ......................................................................... 7 3.5. TABELAS EXEMPLO ........................................................................................................................ 8 3.6. SELECT BÁSICO ........................................................................................................................... 9 3.7. SELECT AVANÇADO ................................................................................................................... 14 3.8. UPDATE..................................................................................................................................... 15 3.9. DELETE ..................................................................................................................................... 15 3.10. INSERT .................................................................................................................................... 16 4. O DB2 E O COBOL ......................................................................................................................... 17 4.1. LINGUAGEM HOSPEDEIRA ........................................................................................................... 17 4.2. SQLCA ....................................................................................................................................... 18 4.3. VARIÁVEIS HOST ......................................................................................................................... 18 4.4. SQLCODE .................................................................................................................................. 19 4.5. TRABALHANDO COM CONJUNTO DE REGISTROS ........................................................................... 19 4.5.1. Cursor ................................................................................................................................. 19 5. EXERCÍCIOS ................................................................................................................................... 20 DB2 e Linguagem SQL Página 1 1. BANCO DE DADOS DB2 1.1. Introdução O DB2 é um gerenciador de bancos de dados relacional, desenvolvido pela IBM para acessar e manipular as bases de dados. Faz uso da linguagem SQL como DML (Data Manipulation Language – Linguagem de Manipulação de Dados). Roda apenas em ambiente MVS, mas existe também uma versão de banco de dados relacional desenvolvida para o DOS/VSE denominada SQL/DS. Seu manuseio é idêntico ao do DB2 em termos de programação. Este curso é introdutório, e nele aprenderemos os conceitos básicos do DB2, e os principais comandos da linguagem SQL, utilizada para acesso as bases. 1.2. Catálogo O catálogo é um elemento muito importante no banco de dados DB2. Trata-se na verdade de um arquivo no qual são armazenadas as definições de todos os elementos: as tabelas com suas respectivas características e os índices. 1.3. Tabelas DB2 A estrutura de arquivo de uma tabela DB2 pode ser comparada a de um arquivo DBASE/CLIPPER. A tabela DB2 estará dividida em linhas (que chamaremos de registros) e colunas (que serão os nossos campos). Analogamente ao DBASE, quando se cria uma tabela DB2, cada um dos campos do registro precisa ser previamente definido e nomeado. Cada uma dessas colunas, com seu respectivo nome, será a referência que o DB2 terá para tratar cada dado que será armazenado. Observe com isso, uma diferença gritante com os arquivos que utilizávamos em Cobol. O chamado Registro 0. Os arquivos do VSAM por exemplo apenas contém informações gravadas no disco, agrupadas por linhas. De dentro do nosso programa determinamos como cada informação será acessada, dando seus nomes na DATA DIVISION e podendo inclusive tratar cada registro de maneira diferente (header, detalhe e trailler). Na tabela DB2 o nome do campo também é obrigatoriamente gravado na tabela. 1.4. Tipos de dados DB2 Os tipos de dados, como já sabemos, definem os tipos de informações que podem ser armazenadas pelos campos. Os campos são definidos durante a criação da tabela, procedimento que deve ser realizado apenas pelo DBA (Data Base Administrator). Ao programador cabe saber o que quer dizer cada definição. DB2 e Linguagem SQL Página 2 Observe a tabela: Tipo DB2 Descrição Correspondente COBOL CHAR Alfanumérico PIC X DECIMAL Numérico PIC 9 COMP-3 DATE Valores do tipo data PIC X(10) TIME Valores do tipo hora PIC X(06) TIMESTAMP Valores de data com hora PIC X(26) INT Inteiros com sinal PIC S9(9) COMP SMALLINT Inteiros menores PIC S9(4) COMP OBSERVAÇÕES Decimal todos os campos numéricos para o DB2 são armazenados no formato compactado. Portanto, sempre serão acessados no Cobol através de variáveis COMP-3. Date os valores definidos como date são armazenados no formato aaaammdd, compactado. Porém, os comandos de acesso ao DB2 sempre receberão e enviarão as datas no formato aaaa-mm-dd (depende do adotado pe lo cliente). Time os comandos de acesso ao DB2 sempre receberão e enviarão os horas no formato hh:mm:ss. Valor NULL Null representará a ausência de informação em um determinado campo. Não será 0 (zero) nem espaço em branco. Por exemplo, na digitação de um saldo, zero será um valor absolutamente válido porém, deixar o campo sem digitar nada não será permitido. Cabeçalho ou Registro 0 Codigo Nome Fone Registro 1 001 Fulano de Tal 2527070 Registro 2 002 Ciclano da Silva 9714698 Registro 3 003 Jurema Emengarda 2345696 Registro 4 004 Eustákio Parreira 2302156 Registro 5 005 Astrogildo Figueira 2236652 1.5. Chaves Uma chave é um campo que será utilizado para a criação de um índice e, consequentemente, utilizado como argumento de pesquisa em uma tabela. A chave primária será o primeiro índice de uma tabela. É também aquele que torna o registro único, ou seja, nenhum outro na tabela inteira será igual a ele. Uma chave estrangeira é constituída de um ou mais campos de uma tabela que se referem à chave primária de uma outra tabela. Ela indica um relacionamento entre as duas. DB2 e Linguagem SQL Página 3 2. RELACIONAMENTOS Os dados são representados na forma de tabelas, que podem possuir diversas associações entre si, as quais serão chamadas de relacionamentos. Suponha as tabelas de Produtos e Fornecedores a seguir: Campos Código Produto Nome Fornecedor Código Fornecedor Razão Social 001 A 01 01 Empresa S/A 002 B 01 02 Indústria Ltda. 003 C 03 03 Fábrica Ind. e Com. 004 D 02 04 Siderúrgica do Ferro 005 E 01 05 Mineradora de Minério 006 F 04 06 Eng. Engenharia Ltda. 007 G 05 008 H 06 009 I 02 010 J 04 Registros Determinaremos para a tabela Produtos o campo Código Produto como sendo chave primária. Isso quer dizer que ele nunca se repetirá na tabela (haverá apenas um produto de código 001, um produto de código 002 e assim por diante). Na tabela Fornecedores o campo Código Fornecedor será a chave primária com as mesmas características. Isso representa que foram criados até então dois índices diferentes que poderão ser utilizados para localizar registros de forma mais rápida. Em um dado momento, pode ser interessante saber quem é o fornecedor de um determinado produto, bem como obter outras informações deste fornecedor. Primeiramente, devemos determinar o que há de comum entre os registros das duas tabelas. Observaremos então que cada uma das tabelas possui um campo destinado a armazenar o código do fornecedor. Este campo pode ser um elo de ligação entre elas. Sendo assim, se tomarmos como referência a tabela Produtos, o campo Código Produto será a chave primária e Código Fornecedor da tabela Fornecedores será uma chave estrangeira. Uma observação muito importante deve ser levada em consideração: os campos que são utilizados para ligação entre duas tabelas devem possuir as mesmas propriedades. Na associação lógica entre as ocorrências das entidades, as chaves primárias e estrangeiras serão utilizadas como argumentos de um operador relacional da linguagem SQL. 2.1. Tipos de relacionamentos Relacionamentos um para muitos Em um relacionamento do tipo um para muitos, qualquer ocorrência de uma entidade pode estar associada à várias ocorrências em outra entidade. Qualquer outra deve estar DB2 e Linguagem SQL Página 4 associada à apenas uma ocorrência da primeira. Por exemplo, um fornecedor pode fornecer vários produtos. Relacionamentos muitos para muitos Em um relacionamento deste tipo, qualquer ocorrência de ambas as entidades pode estar associada à várias ocorrências da entidade oposta. Exemplo: EMPREGADOS PROJETOS Josefina Natal Pafúncio Qualidade Godofredo Supondo Empregados como tabela primária, e a chave primária de Projetos como estrangeira, Josefina e Godofredo estão alocados no projeto Natal (n:1). Porém, observe que Pafúncio sozinho está alocado nos projetos Natal e Qualidade (1:n). 2.2. Integridade referencial A integridade referencial garante que qualquer valor apontado pela chave estrangeira tenha um valor correspondente na chave primária à qual está associada. Por exemplo, na tela de cadastro de produtos não é possível especificar um código de fornecedor que não tenha sido previamente cadastrado na tabela fornecedores. No DB2 a integridade referencial é implementada através da programação SQL. Em outros bancos de dados, como Access do ambiente Windows, existe o objeto relacionamento criado juntamente com os demais objetos do sistema. Neste caso, através de propriedades predefinidas, a integridade pode ser estabelecida automaticamente. 2.3. Regras de integridade referencial do DB2 Uma chave primária tem valor único e não nulo; Uma chave estrangeira é nula ou tem valor correspondente na chave primária; Todas as tabelas associadas entre si via regra de integridade referencial forma um conjunto chamado de ESTRUTURA REFERENCIAL ou REFERENTIAL STRUCTURE; Uma tabela com chave primária definida é chamada de PARENT TABLE ou TABELA MÃE; Uma tabela com chave estrangeira definida é chamada de DEPENDENT TABLE ou TABELA DEPENDENTE; Uma linha da PARENT TABLE é chamada de PARENT ROW ou LINHA MÃE se existir pelo menos uma linha na tabela dependente com chave estrangeira correspondente à sua chave primária; Uma linha de uma DEPENDENT TABLE é chamada de DEPENDENT ROW ou LINHA DEPENDENTE se existir uma linha mão com valor de chave primária correspondente à da sua chave estrangeira. DB2 e Linguagem SQL Página 5 Quando ocorre a deleção de uma linha da tabela mãe, podem ocorrer situações conforme segue: CASCADE todas as linhas dependentes são eliminadas juntamente com a mãe; SET NULL regra onde as chaves estrangeiras das linhas dependentes são atualizadas com valor nulo após a eliminação da mãe. RESTRICT a eliminação da linha mãe é impedida. Neste caso somente as linhas que não são mãe podem ser eliminadas. 3. A LINGUAGEM SQL 3.1. Introdução A SQL (Structured Query Language ou Linguagem Estruturada de Consultas) originou-se inicialmente em 1970 de uma linguagem chamada Sequel. Na verdade, em inglês, muitas vezes tem seu nome pronunciado como Sequel em lugar de ess cue ell. Hoje ela possui diversas variações e é utilizada para acesso a muitas bases de dados diferentes. Pode ser encarada também como uma espécie de linguagem universal para acesso a bancos de dados. Encontraremos SQL em DB2, Delphi, Access, Visual Basic e na grande maioria dos gerenciadores de bancos de dados atuais. Isso significa que um programador conhecedor de SQL tem uma grande vantagem e pode trabalhar em vários ambientes e bases de dados diferentes. 3.2. DB2 Interactive (DB2 Interativo) – SPUFI O DB2 Interativo é constituído por um conjunto de programas que possibilitam a execução imediata de alguma tarefa na base de dados. O SPUFI (SQL Processing Using File Input – Processador de SQL Usando Arquivo de Entrada) é uma ferramenta para executar instruções SQL. Trabalha com um arquivo de entrada e um arquivo de saída. Dirige o usuário para um ciclo de trabalho que consiste basicamente de três fases: EDIT edição de arquivo de entrada onde é codificada uma ou mais instruções SQL; EXECUTE interpreta e executa as instruções constantes do arquivo de entrada. BROWSE mostra o arquivo de saída como um relatório do resultado da execução DB2 e Linguagem SQL Página 6 DB2 SPUFI Edit Execute Browse Input Output Select ... Relatório BANCO DE DADOS 3.3. Operadores relacionais Operador Significado = Igual <> ou ¬= Diferente > Maior >= Maior ou igual < Menor <= Menor ou igual ¬> Não maior ¬< Não menor 3.4. Comandos da Structured Query Language Abaixo, veremos uma listagem dos comandos SQL que utilizaremos em nossos programas Cobol. Depois veremos a sintaxe de cada um deles e exemplos de utilização. DML (Data Manipulation Language – Linguagem de Manipulação de Dados) SELECT pesquisa de dados UPDATE atualização de dados DELETE deleção de dados INSERT inserção de dados DB2 e Linguagem SQL Página 7 DDL (Data Definicion Language – Linguagem de Definição de Dados) CREATE definição de um objeto (tabela, índice, etc.) ALTER alteração da definição de um objeto DROP deleção de um objeto (não confundir com dados) CONTROL GRANT fornece um privilégio REVOKE revoga um privilégio 3.5. Tabelas exemplo As tabelas a seguir serão utilizadas como base para a execução de todos os comandos que iremos aprender: PRODUTOS Codigo Nome Preco Fornecedor 001 Drive CD-ROM 2X 13,01 04 002 Ensacador de fumaça 45,32 01 003 Placa FAX/Modem 2.400 bps 0,50 03 004 Prego de areia 7,42 01 56,23 03 2,37 04 007 Enxugador de gelo seco 12,36 01 008 Impressora matricial Rima XT 180 10,00 03 5,00 02 150,00 02 005 Calibrador de pneu de trem 006 Microcomputador PC 4,77 MHz 009 Tesoura d’água 010 Vídeo Cassete Betamax FORNECEDORES Codigo Razao Cidade 01 ACME & Pernalonga Associados Bauru 02 Agapê Corp. Pederneiras 03 Itautreco São Paulo 04 Ibeême do Brasil Ltda. Bauru DB2 e Linguagem SQL Página 8 3.6. SELECT básico Pesquisa por registros em uma tabela, podendo retornar um registro único (apenas o primeiro encontrado) ou gerar um conjunto de registros que poderá ser tratado independentemente. Dentro do Cobol o conjunto gerado será chamado de cursor. Sintaxe: SELECT [DISTINCT] <campos> FROM <tabela> WHERE <condição> ORDER BY <campo> [ASC | DESC] Não há necessidade dos argumentos do comando SELECT serem colocados em linhas diferentes. Tal codificação é utilizada apenas para efeitos de melhor entendimento do programa que está sendo escrito. Exemplos: Para todos os registros de uma tabela disponibilizando apenas alguns campos SELECT CODIGO, NOME, PRECO FROM PRODUTOS Para selecionar todos os registros de uma tabela disponibilizando também todos os campos SELECT * FROM PRODUTOS Todas as vezes que se quiser selecionar todos os campos de uma tabela, basta substituir a lista de nomes de campos por um asterisco (*). DB2 e Linguagem SQL Página 9 Selecionar todos os campos que satisfaçam uma determinada condição Aqueles que possuam preço superior a 10 SELECT * FROM PRODUTOS WHERE PRECO > 10 Codigo Nome Preco Fornecedor 001 Drive CD-ROM 2X 13,01 04 002 Ensacador de fumaça 45,32 01 005 Calibrador de pneu de trem 56,23 03 007 Enxugador de gelo seco 12,36 01 010 Vídeo Cassete Betamax 150,00 02 Outros exemplos de condições SELECT * FROM PRODUTOS WHERE PRECO >= 10 AND PRECO <=50 SELECT * FROM PRODUTOS WHERE PRECO < 10 OR PRECO > 50 SELECT * FROM PRODUTOS WHERE FORNECEDOR <> 1 Observe que podem ser utilizadas também múltiplas condições, ligadas através dos operadores lógicos AND e OR. O uso de parênteses para impor a ordem em que as expressões lógicas serão resolvidas também é permitido conforme segue: SELECT * FROM PRODUTOS WHERE FORNECEDOR = 1 AND (PRECO < 10 OR PRECO > 30) Codigo Nome 002 Ensacador de fumaça DB2 e Linguagem SQL Preco Fornecedor 45,32 01 Página 10 Procurando por um conjunto predefinido de valores SELECT * FROM PRODUTOS WHERE FORNECEDOR IN (2 , 3 , 4) O conjunto resultante será: Codigo Nome 001 Drive CD-ROM 2X 003 Placa FAX/Modem 2.400 bps 005 Calibrador de pneu de trem 006 Microcomputador PC 4,77 MHz 008 Impressora matricial Rima XT 180 009 Tesoura d’água 010 Vídeo Cassete Betamax Preco Fornecedor 13,01 04 0,50 03 56,23 03 2,37 04 10,00 03 5,00 02 150,00 02 Observe que a expressão FORNECEDOR IN (2 , 3 , 4) substitui a expressão FORNECEDOR = 2 OR FORNECEDOR = 3 OR FORNECEDOR = 4 Selecionando registros em que determinado campo esteja em um intervalo fechado de valores SELECT * FROM PRODUTOS WHERE PRECO BETWEEN 10 AND 50 O conjunto resultante será: Codigo Nome Preco Fornecedor 001 Drive CD-ROM 2X 13,01 04 002 Ensacador de fumaça 45,32 01 007 Enxugador de gelo seco 12,36 01 008 Impressora matricial Rima XT 180 10,00 03 A cláusula BETWEEN (entre) determina todos os registros cujo campo esteja entre o valor inicial e o valor final especificados, inclusive. DB2 e Linguagem SQL Página 11 Sendo assim, PRECO BETWEEN 10 AND 50 é igual a PRECO >= 10 AND PRECO <= 50 Selecionando registros através de pesquisas parciais SELECT * FROM FORNECEDORES WHERE RAZAO LIKE ‘A%’ O conjunto resultante será: Codigo Razao Cidade 01 ACME & Pernalonga Associados Bauru 02 Agapê Corp. Pederneiras A cláusula LIKE permite que sejam utilizados dois caracteres como curingas para estabelecer um padrão a ser utilizado na consulta. O caracter % especifica uma seqüência de caracteres quaisquer e o caracter _ (sublinhado) indica a substituição de um único caracter. Sendo assim, RAZAO LIKE ‘A%’ traria todos os fornecedores cuja razão social começa com a letra ‘A’, não importando quais ou quantos caracteres viessem depois. Este caracter também pode ser utilizado no meio ou no final da cláusula LIKE. Observe agora outros exemplos dos curingas da cláusula LIKE: Padrão Exemplos de retorno ‘LU%’ Luiz Luciane Luís Lucas Luzia ‘LUI_’ Luiz Luis ‘LU_Z Luiz Roberto Luíz Carlos Luiz Augusto DB2 e Linguagem SQL Página 12 OBS Para negar os operadores LIKE e BETWEEN basta inserir o operador lógico NOT antes deles. Por exemplo, o comando abaixo localiza todos os registros em que o preço não esteja entre 10 e 50 inclusive: SELECT * FROM PRODUTOS WHERE PRECO NOT BETWEEN 10 AND 50 Selecionando registros com valores nulos SELECT * FROM PRODUTOS WHERE PRECO IS NULL Colocando como condição da cláusula WHERE a expressão <CAMPO> IS NULL, serão localizados todos os registros onde o campo especificado tenha valor nulo. Lembre-se, conforme foi visto antes, que NULO é diferente de BRANCOS ou de ZEROS, representa a ausência de qualquer valor. Selecionando registros com valores não nulos É bastante parecido com o exemplo anterior. A diferença é que o operador lógico NOT será inserido antes do valor NULL. SELECT * FROM FORNECEDORES WHERE CIDADE IS NOT NULL Selecionando apenas um registro Como visto até aqui, o comando SELECT gera um conjunto de registros que poderá ser processado da melhor maneira pelo programa. Porém pode existir o caso em que apenas um registro nos interessará. SELECT DISTINCT * FROM FORNECEDORES WHERE CODIGO = 1 Codigo Razao 01 ACME & Pernalonga Associados Cidade Bauru A cláusula DISTINCT localiza apenas o primeiro registro que satisfaça a condição estabelecida pela cláusula WHERE, ignorando quaisquer outros que possam existir. DB2 e Linguagem SQL Página 13 Ordenando o conjunto resultante Exemplo SELECT NOME PRECO FROM PRODUTOS WHERE PRECO > 10 ORDER BY NOME OBSERVAÇÕES O SELECT anterior trará todos os registros de produtos que satisfaçam a condição porém, o resultado estará em ordem alfabética de NOME; As colunas classificadas (através da cláusula ORDER BY) devem obrigatoriamente ser referenciadas pelo SELECT; Podemos especificar colunas para o ORDER BY fornecendo o número que indica a posição de cada uma no SELECT. A única cláusula que permite isso é a ORDER BY; ORDER BY classifica uma tabela resultante (conjunto). Sendo assim deve ser sempre a última linha a aparecer numa instrução SELECT e deve estar sozinha. Selecionando registros de tabelas diferentes SELECT PRODUTOS.CODIGO, PRODUTOS.NOME, PRODUTO.FORNECEDOR FORNECEDORES.CODIGO, FORNECEDORES.RAZAO FROM PRODUTOS, FORNECEDORES WHERE PRODUTOS.NOME = ‘Enxugador de gelo seco’ AND FORNECEDORES.CODIGO PRODUTOS.FORNECEDOR = Observe que para especificar os campos que interessavam foi utilizado o formato TABELA.CAMPO. Este formato não é obrigatório a não ser que existam campos com o mesmo nome nas duas tabelas. Neste caso, não há como o SQL “saber” de qual das duas está sendo feita a seleção. 3.7. SELECT avançado Transforma dados de uma coluna em um único valor. Dados numéricos SUM total dos valores da coluna AVG média dos valores da coluna DB2 e Linguagem SQL Página 14 3.8. UPDATE Atualiza (altera) dados dos campos de uma tabela Sintaxe: UPDATE TABELA SET <CAMPO 1> = <VALOR 1> <CAMPO 2> = <VALOR 2> ... <CAMPO N> = <VALOR N> WHERE <CONDIÇÃO> A cláusula WHERE é de suma importância. Se a mesma não for especificada juntamente com uma condição para determinar quais registros serão atualizados, todos serão, independente do valor que possuírem. Exemplos O exemplo a seguir grava o valor 10 no campo CODIGO da tabela PRODUTOS em todos os registros em que o CODIGO é igual a 10. UPDATE PRODUTOS SET CODIGO = 10 WHERE CODIGO = 1 O próximo exemplo reajusta o PRECO em 10% em todos os registros onde PRECO seja inferior a 10. UPDATE PRODUTOS SET PRECO = PRECO * 1.10 WHERE PRECO < 10 3.9. DELETE Deleta um ou mais registros de uma tabela. O número de registros a ser deletados é determinado pela condição imposta na cláusula WHERE. Sintaxe DELETE FROM <TABELA> WHERE <CONDIÇÃO> Deve-se também atentar para a condição estabelecida na cláusula WHERE. Se a mesma for omitida todos os registros da tabela serão apagados. DB2 e Linguagem SQL Página 15 Exemplos Deletar todos os fornecedores de Pederneiras: DELETE FROM <FORNECEDORES> WHERE CIDADE = ‘PEDERNEIRAS’ Deletar todos os produtos do fornecedor 1 cujo preço seja superior a 50: DELETE FROM <PRODUTOS> WHERE PRECO > 50 AND FORNECEDOR = 1 Deletar todos os produtos que sejam dos fornecedores 1, 3 ou 5: DELETE FROM PRODUTOS WHERE FORNECEDOR IN (1 , 3 , 5) 3.10. INSERT Insere uma nova linha na tabela. O comando INSERT permite que não sejam codificados todos os campos. Os não obrigatórios não requerem que um valor seja especificado. Se isso acontecer, o DB2 automaticamente atribuirá valor NULL para os campos. Quando o campo for do tipo DATE e o mesmo permitir a inserção de valores NULL, deverá ser especificado tal valor, do contrário o DB2 retornará SQLCODE –180 (data inválida) e a inserção será mal sucedida. Sintaxe: INSERT INTO <TABELA> (CAMPO1, CAMPO2, CAMPO3, CAMPO4, ..., CAMPON) VALUES (VALOR1, VALOR2, VALOR3, VALOR4, ..., VALORN) Exemplo: Inserindo um novo registro na tabela de fornecedores INSERT INTO FORNECEDORES (CODIGO, RAZAO, CIDADE) VALUES (05, ‘Filipis’, ‘Bauru’) Gerando uma tabela AUXILIAR com todos os registros da tabela PRODUTOS INSERT INTO AUXILIAR DB2 e Linguagem SQL Página 16 SELECT * FROM PRODUTOS Gerando uma tabela AUXILIAR com todos os PRODUTOS do FORNECEDOR 01: INSERT INTO AUXILIAR SELECT * FROM PRODUTOS WHERE FORNECEDOR = 1 OBS Para os dois últimos exemplos, as tabelas devem ter a mesma estrutura definida. 4. O DB2 E O COBOL 4.1. Linguagem Hospedeira Os exemplos de comandos SQL vistos até aqui nos serviram para aprender a sintaxe e funcionamento da linguagem de maneira fácil. Porém, dentro do DB2 essa não é a ,maneira que utilizaremos para acessar as bases de dados. Na verdade, já vimos que o DB2 é um software que controla os acessos ao banco de dados, não uma linguagem de programação. Sendo assim, ele precisa ser acessado a partir de programas codificados em linguagens como Cobol ou CSP. Observe o esquema a seguir: PROGRAMA COBOL SQL TABELA DB2 SQLCA Podemos dizer que o Cobol servirá como linguagem hospedeira para o DB2 e fará todos os acessos à base de dados através de linguagem SQL. Todos os comandos do DB2 (SQL) que serão executados dentro de um programa Cobol deverão ser precedidos da linha EXEC SQL e seguidos por END-EXEC, conforme sintaxe geral a seguir: EXEC SQL <COMANDOS SQL> END-EXEC DB2 e Linguagem SQL Página 17 O programa Cobol, antes de ser compilado, passará por um Translator. Depois será executado a partir de um utilitário DB2, em JCL1 próprio. Para que tudo isso funcione e que a linguagem Cobol possa atuar como hospedeira para o DB2, precisaremos de alguns elementos que possibilitem essa comunicação. Esses elementos são a SQLCA e as variáveis host. 4.2. SQLCA SQLCA é o nome dado à área de comunicação entre o programa e o DB2. O layout dessa área precisa ser copiado para o programa, bastando para tanto, incluir na WORKINGSTORAGE SECTION o comando: EXEC SQL INCLUDE SQLCA END-EXEC O comando INCLUDE é utilizado para copiar no programa books de registros do DB2. Pode ser utilizado tanto para a SQLCA, como para registros de tabelas, catalogadas através do utilitário DCLGEN. A SQLCA também disponibiliza um recurso muito importante chamado SQLCODE, um campo que contém o código de retorno dos acessos efetuados e deverá sempre ser testado após a execução de cada EXEC SQL codificada na PROCEDURE DIVISION. Entenda este código de retorno como sendo a File Status do DB2. 4.3. Variáveis Host São variáveis criadas para fazer a permuta de valores entre o DB2 (SQL) e a linguagem de programação utilizada como hospedeira, no nosso caso o Cobol. Podem ser definidas normalmente na WORKING-STORAGE SECTION do programa seguindo as correspondências de tipos de dados que são apresentadas no início dessa apostila, mas o DB2 oferece um utilitário para facilitar a definição das hosts. O DCLGEN pode gerar descrições em Cobol para os campos das tabelas. As definições são geradas em books que podem depois ser copiados através do INCLUDE. Criar as definições manualmente pelo programa pode ser vantagem, uma vez que tabelas diferentes podem conter nomes iguais e, neste caso, o programador poderá diferenciar os nomes das variáveis. Sempre que um comando SQL se referenciar a uma variável host, está deverá ter seu nome precedido por dois pontos (:). Exemplo 1 Job Control Language, é a linguagem que controla a execução de todas as tarefas no mainframe. Neste caso, não faremos uso da linguagem nativa, mas sim de uma implementada pelo próprio DB2. DB2 e Linguagem SQL Página 18 MOVE 2904 TO CH-CLI. EXEC SQL SELECT CODIGO, NOME, TELEFONE FROM CLIENTES WHERE CODIGO = :CH-CLI 4.4. SQLCODE Como visto anteriormente é o código de retorno da execução de comandos SQL. Possui quatro posições no formato: S 9 9 9 Podemos dizer que existem três tipos de SQLCODE, conforme segue: SQLCODE negativo aconteceu um erro grave ou de hardware no acesso. Pode exigir o cancelamento do programa. SQLCODE zero a operação foi concluída com sucesso. SQLCODE positivo indica warnings (avisos) que podem gerar um caminho diferente a ser executado pelo programa sem que haja necessariamente cancelamento. Principais códigos de retorno do SQL Código Descrição -180 Data inválida -803 Chave duplicada -922 Falta de autorização ZEROS Comando concluído com êxito +100 Chave não encontrada ou Fim da query de múltiplas linhas ou Fim do arquivo (tabela) 4.5. Trabalhando com conjunto de registros 4.5.1. Cursor Suponha a seguinte situação: tem-se uma tabela com 50.000 registros; um programa deverá realizar uma atualização em todos os registros dessa tabela que satisfaçam a uma determinada condição qualquer. DB2 e Linguagem SQL Página 19 5. EXERCÍCIOS Exercício 1 É dada a tabela PRODUTOS com a seguinte estrutura: CODPROD DECIMAL (2) NOME CHAR (30) TIPO DECIMAL (1) EMBALAGEM CHAR (10) ALIQUOTA DECIMAL (2) VALIDADE DATE PRECO DECIMAL (7,2) a) Inserir um registro com os dados que quiser b) Alterar o tipo de todos os produtos de 5 para 9 c) Aumentar em duas unidades o valor da alíquota d) Excluir todos os registros com produto vencido. Suponha a data atual igual a 1998-06-16 e) Atualize o preço em 23.71% em todos os registros f) Calcule o preço utilizando a alíquota como base. Fórmula: preço = preço + alíquota % g) Exclua todos os produtos que custem menos de R$ 10 e sejam embalados em caixa h) Suponha uma tabela de nome PROD-AUX de estrutura identica a tabela PRODUTOS. h.1) copie todos os registros de produtos para ela h.2) copie os registros de produtos vencidos para ela h.3) todos os produtos com preço entre 200 e 950 Exercício 2 Utilizando a tabela PRODUTOS do exercício anterior, codificar um programa COBOL/SQL que faça a inclusão de um registro nesta tabela. OBS: 1 – Acessar os valores via console 2 – Codificar as variáveis HOST na WORKING 3 – Carregar a SQLCA DB2 e Linguagem SQL Página 20 4 – Testar SQLCODE. Exercício 3 Ainda utilizando a tabela PRODUTOS, codifique um programa para acessar via console um código de produto, consistindo conforme seu tamanho. Procurar por esse código na tabela exibindo os demais dados do produto. Exercício 4 São dadas as tabelas PEDIDOS e COMPOSIÇÃO conforme segue: PEDIDOS: NUMPED DECIMAL (7) DATAPED DATE QTDEPED DECIMAL (3) VLTOTPED DECIMAL (8,2) COMPOSICAO: NUMPED DECIMAL (7) SEQ DECIMAL (1) CODPROD DECIMAL (4) PRECOPROD DECIMAL (8,2) Codificar em linguagem SQL as seguintes querys (consultas): a) da tabela PEDIDOS, o pedido de número 100 b) os pedidos com mais de 3 produtos c) selecionar os registros onde a quantidade de produtos seja superior a 10 e o valor total esteja entre 200 e 600, inclusive. d) Todos os pedidos, cada qual com sua respectiva composição e) Todos os registros da composição onde o preço do produto seja superior a 50 e com a respectiva data e valor total do pedido f) Todos os pedidos que possuam 4, 6 ou 10 produtos com preço de cada produto g) Todos os pedidos do mês de maio h) O segundo produto do pedido número 2048 i) Os pedidos com problemas (não possuem composição correspondente) DB2 e Linguagem SQL Página 21 Exercício 5 CODIGO DECIMAL (4) NOME CHAR (30) ENDERECO CHAR (50) STATUS CHAR (1) LIMITE DECIMAL (2) a) Codifique um programa para adicionar 10% ao limite dos clientes cujo status seja igual a “E” (Especial). b) Codifique um programa para excluir da tabela todos os clientes que tenham limite inferior a 500 e status igual a “I” (Inadimplente) Obs: - Usar cursor para os dois programas - imprimir o relatório dos registros processados a medida que o processamento (deleção ou atualização) é efetuado - exibir estatística de processamento ao final. Exercício 6 É dada a tabela VENDAS com a estrutura abaixo relacionada: NUM_PEDIDO DECIMAL(5) DATA_VENDA DATE NUM_NF DECIMAL(7) VALOR_PEDIDO DECIMAL(8,2) SITUACAO CHAR(1) (A = ATIVO ou C = CANCELADO ou R=REAJUSTADO) 1 – São dadas as tabelas ATIVOS e CANCELADOS de estrutura idêntica (inclusive nos nomes dos campos) à tabela VENDAS. Codifique um programa batch seguindo os procedimentos abaixo discriminados: Ler toda a tabela VENDAS, armazenando-a em um cursor. Processar este cursor da primeira a última linha, gravando as tabelas ATIVOS e CANCELADOS, utilizando para tanto o campo SITUACAO. Ao final, imprimir estatística de processamento: TOTAL DE REGISTROS LIDOS: Z.ZZZ.ZZ9 TOTAL DE REGISTROS ATIVOS: Z.ZZZ.ZZ9 TOTAL DE REGISTROS CANCELADOS: ZZ.ZZZ.ZZ9 2 – Na tabela VENDAS, onde o campo STATUS determine que um registro precisa ser reajustado, atualize em 2,37% o valor total do pedido. DB2 e Linguagem SQL Página 22 3 – É dada a tabela RESUMO, com estrutura descrita abaixo: DATA_VENDA DATE SITUACAO CHAR(1) VALOR_TOTAL DECIMAL(16, 2) Codificar um programa, seguindo as instruções: Classificar a tabela VENDAS por DATA_VENDA e SITUACAO. Gerar a tabela RESUMO, onde cada data de venda será representada em dois registros. O primeiro com o valor total de vendas canceladas no dia e o segundo com o total de vendas ativas no dia.. Ao final, exibir estatísticas de processamento: NÚMERO DE REGISTROS LIDOS: ZZ.ZZZ.ZZ9 NÚMERO DE REGISTROS GRAVADOS: ZZ.ZZZ.ZZ9 NÚMERO DE REGISTROS CANCELADOS: ZZ.ZZZ.ZZ9 NÚMERO DE REGISTROS ATIVOS: ZZ.ZZZ.ZZ9 VALOR TOTAL CANCELADO: ZZ.ZZZ.ZZ9 VALOR TOTAL ATIVO: R$ ZZ.ZZZ.ZZ9,99 DIFERENÇA CANCELADO/ATIVO: R$ ZZ.ZZZ.ZZ9,99 DB2 e Linguagem SQL Página 23
