Baixe o app para aproveitar ainda mais
Prévia do material em texto
Inserir Título Aqui Inserir Título Aqui Linguagens de Banco de Dados SQL Básico Responsável pelo Conteúdo: Prof. Me. Alexander Gobbato Paulino Albuquerque Revisão Textual: Prof.ª Dr.ª Selma Aparecida Cesarin Nesta unidade, trabalharemos os seguintes tópicos: • Utilizando Oracle; • Organização; • Características de Tipos de Dados no Oracle; • Instrução SELECT; • Instrução WHERE; • Operadores Lógicos; • Sequências; • Estrutura para Armazenamento de Dados; • Manipulando Informações. Fonte: iStock/Getty Im ages Objetivos Nesta unidade, iremos abordar alguns pontos iniciais para começarmos a entender a Lin- guagem utilizada nos Bancos de Dados. O objetivo é demonstrar os comandos DCL, DML e DDL. Com a demonstração desses comandos, será possível a criação e a manutenção das estruturas (Table, Constrans, Index e Sequence), pesquisar (SELECT) as informações salvas e manipulá-las (INSERT, DELETE e UPDATE). Caro Aluno(a)! Normalmente, com a correria do dia a dia, não nos organizamos e deixamos para o último momento o acesso ao estudo, o que implicará o não aprofundamento no material trabalhado ou, ainda, a perda dos prazos para o lançamento das atividades solicitadas. Assim, organize seus estudos de maneira que entrem na sua rotina. Por exemplo, você poderá escolher um dia ao longo da semana ou um determinado horário todos ou alguns dias e determinar como o seu “momento do estudo”. No material de cada Unidade, há videoaulas e leituras indicadas, assim como sugestões de materiais complementares, elementos didáticos que ampliarão sua interpretação e auxiliarão o pleno entendimento dos temas abordados. Após o contato com o conteúdo proposto, participe dos debates mediados em fóruns de discussão, pois estes ajudarão a verificar o quanto você absorveu do conteúdo, além de propiciar o contato com seus colegas e tutores, o que se apresenta como rico espaço de troca de ideias e aprendizagem. Bons Estudos! SQL Básico UNIDADE SQL Básico Contextualização Nos dias de hoje, todo bom desenvolvedor precisa ter habilidades numa Linguagem de Programação, seja ela Microsoft, Java ou qualquer outra. Em conjunto com a Programação, ocorre a necessidade de utilizarmos comandos que possam, de alguma forma, consultar, inserir, alterar e excluir informações de um determinado Banco de Dados. Para ser um desenvolvedor completo ou, até mesmo, um profissional que administra Dados ou um Banco de Dados, é de suma importância compreender os recursos de um SGDB (Sistema Gerenciador de Banco de Dados); por- tanto, para você, que almeja conhecer os elementos de consulta, manipulação e criação de Dados em um Banco, esta Unidade será o início do aprendizado de SQL (Structured Query Language – Linguagem de Consulta Estruturada) de um bom profissional. 6 7 Utilizando Oracle Para nossas aulas, iremos utilizar o software de Banco de Dados Oracle. O Oracle Database 11g Express Edition (Oracle Database XE) é uma versão básica sim- plificada do Banco de Dados Oracle. Para o acesso ao Banco de Dados, utilizaremos a ferramenta disponibilizada pelo Oracle, chamada de SqlDeveloper. Essa ferramenta é totalmente gratuita e pode ser baixada direta- mente do site da Oracle. Para a instalação do Banco e utilização da ferramenta de acesso, você pode consultar o Material Complementar, no qual irá encontrar um arquivo que irá auxiliá-lo na instalação e na configuração. Organização Vamos agora entender como serão a estrutura e os elementos de aprendizado nesta Unidade. Na Linguagem SQL, existem algumas divisões que servem para um propósito espe- cífico, como: • DQL (Data Query Language – Linguagem de Consulta de Dados) – Permite consultar (SELECT) os Dados armazenados; • DML (Data Manipulation Language – Linguagem de Manipulação de Dados) – Permite utilizar os comandos de manipulação de Dados no Banco (INSERT, UPDA- TE e DELETE); • DDL (Data Definition Language – Linguagem de Definição de Dados) – Permite utilizar os comandos para criação (CREATE) de Tabelas, índices e view. Permite, também, que a estrutura das Tabelas seja alterada (ALTER), assim como a exclusão da estrutura (DROP); • DCL (Data Control Language – Linguagem de Controle de Dados) – Per- mite utilizarmos comandos de controle de acesso/permissão ao Banco por usuário (GRANT) e excluir (REVOKE); • DTL (Data Transaction Language – Linguagem de Transação de Dados) – Permite utilizarmos comando de gerenciamento de transações (BEGIN), confirma- ção (COMMIT) ou cancelamento (ROLLBACK) das instruções. Muito bem! Agora que já vimos os segmentos e propósitos de cada item que existe nesta Unidade, iremos dar o primeiro passo para a utilização: vamos entender os tipos de Dados com os quais podemos trabalhar no Banco. 7 UNIDADE SQL Básico Características de Tipos de Dados no Oracle Antes de começar a utilizar a Linguagem de Consulta de Dados, é necessário entender- mos os tipos de Dados que podemos trabalhar nas consultas e estruturas. No Oracle, encontramos tipos de Dados numéricos, textos e datas. Vejamos, então, os tipos de Dados, conforme a documentação do próprio Oracle. Tipo de Dado Texto Quadro 1 - Tipo de Dado Texto Tipo de Dados Oracle 11g Explicação char(tam) Máximo de 2000 bytes. Onde o tam é o número de caracteres a serem armazenados. Tamanho de texto fixo. nchar(tam) Máximo de 2000 bytes. Onde o tam é o número de caracteres a serem armazenados. Tamanho de texto fixo. nvarchar2(tam) Máximo de 4000 bytes. Onde o tam é o número de caracteres a serem armazenados. Cadeia de comprimento variável. varchar2(tam) Máximo de 4000 bytes. Onde o tam é o número de caracteres a serem armazenados. Tamanho da variável de texto.Máximo de 32KB in PLSQL. long Máximo de 2GB. Cadeia de comprimento variável. raw Máximo de 2000 bytes. Cadeias binárias de comprimento variável. long raw Máximo de 2GB. Cadeias binárias de comprimento variável. Fonte: oracle.com Quadro 2 - Tipo de Dado Numérico Tipo de Dado Oracle 11g Explicação number(p,s) A precisão pode variar de 1 to 38. Onde p é a precisão e s é a escala. A escala pode variar de -84 to 127. Por exemplo, o número (7,2) é um número que possui 5 dígitos antes do decimal e 2 dígitos após o decimal. numeric(p,s) A precisão pode variar de 1 to 38. Onde p é a precisão e s é a escala. Por exemplo, o número (7,2) é um número que possui 5 dígitos antes do decimal e 2 dígitos após o decimal. float dec(p,s) A precisão pode variar de 1 to 38. Onde p é a precisão e s é a escala. Por exemplo, dec (3,1) é um número que tem 2 dígitos antes do decimal e 1 dígito após o decimal. decimal(p,s) A precisão pode variar de 1 to 38. Onde p é a precisão e s é a escala. Por exemplo, dec (3,1) é um número que tem 2 dígitos antes do decimal e 1 dígito após o decimal. integer int smallint real double precision Fonte: oracle.com 8 9 Quadro 3 - Tipo de Dado Data Tipo de Dado Oracle 11g Explicação date Uma data entre 1 de janeiro de 4712 a.C. e 31 de dezembro de 9999 d.C. Timestamp (fractional seconds precision) A precisão de segundos fracionados deve ser um número entre 0 e 9 (o padrão é 6). Inclui ano, mês, dia, hora, minuto e segundos. Por exemplo: timestamp(6). timestamp (fractional seconds precision) with time zone A precisão de segundos fracionados deve ser um número entre 0 e 9 (o padrão é 6). Inclui ano, mês, dia, hora, minuto e segundos; com um valor de deslocamento do fuso horário. Por exemplo: timestamp (5) com fuso horário. timestamp (fractional seconds precision) with local time zone A precisão de segundos fracionados deve ser um número entre 0 e 9 (o padrão é 6). Inclui ano, mês, dia, hora, minuto e segundos; com um fuso horário expresso como fuso horário da sessão. Por exemplo: timestamp (4) com fuso horário local. interval day (day precision) to month A precisão do ano é o número de dígitos no ano (o padrão é 2). Período de tempo armazenado em anos e meses. Por exemplo: intervalo ano (4) a mês. intervalday (day precision) to second (fractional seconds precision) A precisão do dia deve ser um número entre 0 e 9 (o padrão é 2). Período de tempo armazenado em dias, horas, minutos e segundos Por exemplo: dia do intervalo (2) para o segundo (6). A precisão de segundos fracionados deve ser um número entre 0 e 9 (o padrão é 6). Fonte: oracle.com Quadro 4 - Tipo de Dados Objetos Tipo de Dado Oracle 11g Explicação bfile Tamanho máximo do arquivo de 264-1 bytes. Armazene até (4 gigabytes -1) * (o valor do parâmetro CHUNK do armazenamento LOB). blob Armazene até (4 gigabytes -1) * (o valor do parâmetro CHUNK do armazenamento LOB). Armazena objetos grandes binários não estruturados. clob Armazene até (4 gigabytes -1) * (o valor do parâmetro CHUNK do armazenamento LOB) de Dados de caracteres. Armazena Dados de caracteres de um byte e de vários bytes. nclob Armazene até (4 gigabytes -1) * (o valor do parâmetro CHUNK do armazenamento LOB) de Dados de texto de caracteres. Armazena Dados unicode. Fonte: oracle.com Oracle – https://docs.oracle.com/ Como vocês observaram, existem vários tipos de Dados com os quais podemos traba- lhar. É importante saber diferenciá-los para que possamos ter melhor aproveitamento das instruções que iremos ver logo a seguir. Com base nas informações de tipo de Dados, podemos então nos aprofundar no co- mando DQL, no qual começaremos com a instrução SELECT. 9 Patrick Highlight UNIDADE SQL Básico Para melhor aproveitamento, iremos utilizar o próprio schema da Oracle HR. Esse Ban- co de Dados já está disponível no momento da instalação do Oracle 11g. Se você ainda não instalou, procure o documento em Materiais Complementares e refaça passo a passo a instalação. Veja a estrutura das Tabelas que iremos utilizar, logo a seguir. Figura 1 - Human Resources (HR) Schema Depois dessas informações, que são extremamente importantes para os exemplos que serão demonstrados a partir daqui, começaremos, então, a entender o funcionamento do comando SELECT. Instrução SELECT A declaração SELECT é usada para recuperar registros de uma ou mais Tabelas em um Ban- co de Dados Oracle. Na instrução SELECT, devemos informar os nomes dos campos ou, se prefeirir, utilizar o caractere coringa * (asterisco). O asterisco é compreendido como a rela- ção de todos as colunas existentes na Tabela abaixo. Sintaxe SELECT expression FROM table_name WHERE conditions Parâmetros ou Argumentos Expression • As colunas ou cálculos que você deseja recuperar. Use * se desejar selecionar todas as colunas. 10 Patrick Highlight Próxima página. 11 table_name • As Tabelas das quais você deseja recuperar registros. Deve haver pelo menos uma Tabela listada na cláusula FROM. WHERE conditionsopcional • As condições que devem ser atendidas para que os registros sejam selecionados. Se não forem fornecidas condições, todos os registros serão selecionados. Todas as instruções a seguir podem ser executas na ide SQLDeveloper. Caso você ainda não tenha baixado, veja o Material Complementar e siga as instruções. Vamos executar algumas seleções na Tabela employees. Os dois exemplos a seguir mostram os campos e todos os registros (Dados) gravados na Tabela. Exemplo – Selecionando todos os campos da Tabela SELECT *FROM employees; Veja que ao utilizar o ‘*’, todos os campos da Tabela Employees são retornados. Figura 2 - Tabela Employees Você agora deve estar se perguntando, como faço para retornar apenas as colunas que preciso? A resposta é: FÁCIL! Basta colocarmos os nomes das colunas em que queremos visualizar a informação. Exemplo – Selecionando campos individuais da Tabela SELECT employee_id, first_name, last_name FROM employees; 11 UNIDADE SQL Básico Essa instrução retorna somente os campos employee_id, first_name e last_name da tabela employees. Figura 3 Por enquanto está tudo indo tranquilo, certo? Muito bem, já que vimos que é possível retornar as informações de uma Tabela, acredito que vocês devem estar se perguntando: “E se não quero exibir todos os registros? O que devo fazer?” Simples. Para isso, iremos executar um comando de restrição de resultado do comando SELECT. Para selecionarmos quais registros devem ser exibidos, iremos entender o funcionamento do comando WHERE. Instrução WHERE A cláusula WHERE é usada para filtrar os resultados das instruções SELECT, UPDATE ou DELETE. Sintaxe WHERE conditions Parâmetros ou Argumentos Conditions • As condições que devem ser cumpridas para que os registros possam ser selecionados. À instrução WHERE devemos informar quais valores ou conjunto de valores queremos selecionar dentre o total de Dados armazenado na Tabela. Sempre que queremos restringir o retorno, utilizamos uma coluna e indicamos qual valor estamos querendo pesquisar da Tabela. 12 13 Vamos ver os exemplos a seguir para entendermos essa nova instrução. Exemplo: SELECT *FROM employees WHERE last_name = ‘Lee’; A declaração SELECT acima retornaria todas as linhas da Tabela employees, na qual os campos last_name for igual a Lee. Como o * é usado no SELECT, todos os campos da Tabela employees apareceriam no conjunto de resultados. Figura 4 Chegamos a um ponto em que vimos como restringir o resultado de uma seleção utilizando a cláusula WHERE; porém, podemos ir além dele informar apenas um campo; podemos utilizar os operadores lógicos para criar expressões e aumentar o critério de pesquisa dos Dados. Vamos, então, entender o funcionamento dos operadores. Operadores Lógicos Uso de um Operador Lógico no qual duas ou mais condições produzem ao final um úni- co resultado: uma linha só poderá ser retornada se o resultado da condição for verdadeiro. A Tabela a seguir mostra os operadores lógicos disponíveis em SQL: • AND retorna TRUE, se ambas as condições forem verdadeiras; • OR retorna TRUE, se uma das condições for verdadeira. Vamos trabalhar alguns exemplos agora na utilização dos operadores lógicos. Exemplo - Usando condição AND SELECT *FROM employees WHERE department_id = 10 AND salary<= 6000; Na declaração SELECT usou-se a condição AND para retornar todos os departament_id do código 10 e cujo salary é menor ou igual a 6000. Figura 5 13 UNIDADE SQL Básico Exemplo - Usando condição OR SELECT employee_idFROM employees WHERE fi rst_name = ‘Steven’ OR fi rst_name = ‘Bruce’; Nesse caso, essa declaração SELECT retornaria todos os valores do employee_id onde o first_name é igual a Steven ou Bruce. Figura 6 Exemplo - Usando condição AND e OR SELECT *FROM employees WHERE (job_id = ‘IT_PROG’ AND fi rst_name = ‘Alexander’) OR (salary> 5000); Este exemplo retornaria todos os empregados que possuem o job_id igual a IT_PROG e o first_name seja Alexander, bem como todos os empregados cujo salary é superior a 5000. Os parênteses determinam a ordem em que as condições AND e OR são avaliadas, assim como você aprendeu na ordem das operações na aula de Matemática! Figura 7 Operador BETWEEN O Operador BETWEEN compara uma faixa de valores, inclusive o menor e o maior valor. Suponhamos que nós quisemos ver os empregados cujo salário está entre 1000 e 2000: SELECT First_name, Last_name, Salary FROM employees; WHERE Salary BETWEEN 10000 AND 20000; 14 15 Resultado: Figura 8 Operador IN O Operador IN compara os valores especificados dentro de uma lista. Para encontrar empregados que tenham um dos três números de Manager, utilize o seguinte comando: SELECT First_name, Last_name, Salary, Manager_id FROM Employees WHERE Manager_id IN (101, 145, 149); Resultado Figura 9 15 UNIDADE SQL Básico Operador LIKE Algumas vezes, você precisa procurar valores que não conhece exatamente. Usando o Operador LIKE, é possível selecionar linhas combinando parâmetros alfanuméricos. O caracter % é utilizado como coringa nas pesquisas. SELECT First_name, Last_name, Salary FROM Employees WHERE First_name like ‘S%’; Resultado Figura 10 Operador IS NULL O Operador IS NULL verifica quais campos estão com valores nulos.Para encontrar, unicamente, todos os empregados que não tenham gerente, você testarão um valor nulo: SELECT First_name, Last_name, Salary, Manager_id FROM Employees WHERE Manager_id IS Null; 16 17 Resultado Figura 11 Para concluir o item SELECT, vimos como selecionar os registros de uma Tabela, colocando condições de filtro. Nos itens relacionados acima, vimos como funcionam os Operadores Lógicos (AND e OR) e também os Operadores BETWEEN, IN, NULL. Caso queiramos trabalhar com a negatividade desses 3 últimos, podemos inserir a palavra NOT, ou seja, NOT BETWEEN, NOT IN e, para o NULL, trabalhamos com a expressão IS NOT NULL. Uma dúvida deve estar pairando sobre a mente de vocês: “Se eu posso retornar as infor- mações da Tabela, será que posso modificar a ordem de resultado do comando SELECT?” A resposta para essa pergunta é SIM!. Vamos ver como fazer? A cláusula ORDER BY Normalmente, a ordem das linhas retornadas de uma pesquisa é indefinida. A cláusula ORDER BY pode ser usada para ordenar as linhas. Se usado, o ORDER BY, deve ser sempre a última cláusula da declaração SELECT. Para inverter essa ordem, acrescente o comando DESC (Decrescente) depois do nome das colunas da cláusula ORDER BY. RE SU M O SELECT Seleciona no mínimo uma coluna Sinônimo (Alias) Pode ser usado para colunas unicamente na lista do SELECT * Indica todas as colunas DISTINCT Pode ser usado para eliminar duplicações FROM Tabela Indica a Tabela de onde as colunas originam Where Restringe a pesquisa para linhas que encontram a condição. Ele pode conter colunas, e literais. And / Or Podem ser usados na cláusula WHERE para construir queries mais complexas nas condições, AND tem prioridade sobre o OR. () Pode ser usado para forçar prioridade Order by Especifica a ordem do Select. Uma ou mais colunas podem ser especificadas. ASC Ordem ascendente é o padrão ordem de ordenação. Não precisa ser especificado. DESC Inverte a ordem padrão de ordenação e deve ser especificada depois do nome da coluna. 17 UNIDADE SQL Básico Até o item 4, vimos as instruções para retorno de Dados de uma Tabela. A partir de agora, iremos estudar os elementos que permitem criar, alterar ou excluir a estrutura das Tabelas e também os elementos de performance. Vamos começar, então? Sequências Uma sequência é um objeto no Oracle que é usado para gerar uma sequência de números. Essa técnica é muito utilizada quando você precisa criar um número exclusivo para atuar como uma chave primária. “Uma sequência (sequence) é um objeto de Banco de Dados criado pelo usuário, que pode ser compartilhado por vários usuários para gerar números inteiros exclusivos de acordo com regras especificadas no momento que a sequência é criada. A sequência é gerada e incrementada (ou diminuída) por uma rotina interna do Oracle. Normalmente, as sequências são usadas para criar um valor de chave primária que deve ser exclusivo para cada linha de uma Tabela” https://bit.ly/2JY8Fzg Criando uma SEQUENCE Criando uma SEQUENCE CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; Parâmetros e Argumentos sequence_name Nome da sequência que irá criar. Criando uma SEQUENCE CREATE SEQUENCE pessoa_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; 18 19 A sequência foi chamada de pessoa_seq e o primeiro número sequencial a ser utilizado é o 1 e cada número subsequente aumentaria em 1. Ele armazenará até 20 valores de desempenho. Se você omitir a opção MAXVALUE, sua sequência será automaticamente padrão para: MAXVALUE 999999999999999999999999999 Para recuperar o próximo valor na ordem de sequência, você precisa usar nextval. pessoa_seq.NEXTVAL; Excluindo SEQUENCE Excluindo SEQUENCE DROP SEQUENCE sequence_name; sequence_name Nome da sequência. Excluindo DROP SEQUENCE pessoa_seq; Estrutura para Armazenamento de Dados Em um Banco de Dados relacional, organizamos as informações em Tabelas; por sua vez, as Tabelas possuem colunas para as quais, obrigatoriamente, devemos informar um tipo de dado. As colunas que foram criadas podem receber algum tipo de restrição quanto à informa- ção que está sendo inserida, ou mesmo alterada; por isso, antes de começarmos a criar as estruturas, precisamos entender o que são as CONSTRAINTS. Restrições – CONSTRAINT As restrições são regras básicas estabelecidas para o preenchimento de uma ou mais colunas da Tabela e são definidas ao final da especificação de cada coluna ou ao final do comando: • Primary Key (PK ou Chave Primária) – Está restrição cria um índice único para um conjunto de colunas ou uma coluna para Chave Primária e especifica que esta coluna não pode conter valores nulos; 19 UNIDADE SQL Básico • Unique – Esta restrição especifica uma coluna ou combinação de colunas que terá seus valores únicos na Tabela; • Foreign Key (FK ou Chave Estrangeira) – Estabelece um relacionamento entre a chave estrangeira e a chave primária da Tabela referenciada; • Check – Especifica uma condição que deve ser verdadeira, obedecendo uma regra do negócio; • Not Null – Determina que a coluna tem preenchimento obrigatório. Instrução CREATE TABLE Sintaxe CREATE TABLE table_name ( Coluna1tipo_de_dado[ NULL | NOT NULL ], Coluna2tipo_de_dado[ NULL | NOT NULL ], ... Coluna_n tipo_de_dado[ NULL | NOT NULL ], [Constraints ] ); Parâmetros ou Argumentos table_name Nome da Tabela que deseja criar. Coluna1, Coluna2, ... Coluna_n Nome da coluna que será criada na Tabela. Cada coluna, necessariamente, terá um tipo (number, varchar2, date, char). A coluna também deverá ser informada se aceita valores nulos ou se é obrigado, por default, o Banco de Dados assume que o valor é null. Constraints Restrições que devem ser aplicadas às colunas; não é obrigatório a criação da constraint. Exemplo CREATE TABLE tb_pessoa ( cd_pessoa number(10) NOT NULL, nm_pessoa varchar2(50) NOT NULL, cidade varchar2(50) ); No exemplo acima, foram criadas 3 colunas. A primeira coluna, com o tipo de Dado Numérico, com no máximo 10 dígitos, e duas colunas com os tipos de Dados Texto, que permite no máximo 50 posições. 20 21 Observe que nesse exemplo não foram criadas restrições na instrução CREATE. Por exemplo, poderíamos, nesse momento, ter inserido uma restrição à coluna cd_pessoa, informando que ela é a chave primária. Veja no exemplo a seguir a instrução final. CREATE TABLE tb_pessoa ( cd_pessoa number(10), nm_pessoa varchar2(50) NOT NULL, cidade varchar2(50), CONSTRAINT pessoa_pk PRIMARY KEY (cd_pessoa) ); Grande parte dos analistas utiliza o comando CREATE TABLE basicamente para informar o nome da Tabela, nome dos campos e seus tipos. Qualquer outra manutenção que precise ser feita, será por meio do comando ALTER TABLE. Na instrução ALTER TABLE é possível acrescentar campos, regras, modificar tipos de Dados, alterar nome de campos, alterar restrições e excluir campos e restrições. Instrução INDEX Um índice é um método de ajuste de desempenho que permite uma recuperação mais rápida dos registros. Sintaxe CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, ... column_n) [ COMPUTE STATISTICS ]; Parâmetros ou Argumentos UNIQUE A combinação de valores é única. index_name Nome do index. table_name Nome da Tabela no qual o índice será criado. column1, column2, ... column_n Colunas que farão parte do índice. 21 UNIDADE SQL Básico COMPUTE STATISTICS Indica ao SGDB que serão coletadas informações estatísticas durante a utilização. As estatísticas são usadas pelo otimizador para escolher um “plano de execução” quando as instruções SQL são executadas. Agora, então, iremos fazer um exemplo para a criação de índice baseado na Tabela tb_pessoa. O campo que receberá a criação do índice é o campo nm_pessoa; portanto, qualquer consulta utilizando campo nm_pessoa como filtro terá uma resposta mais eficiente do Banco quanto ao tempo de retorno. Exemplo CREATE INDEXpessoa_idx ON tb_pessoa (nm_pessoa); CREATE INDEX pessoa_idx ON tb_pessoa (nm_pessoa) COMPUTE STATISTICS; Também é possível criar índices informando mais de 1 coluna. Renomeando um INDEX Sintaxe ALTER INDEX index_name RENAME TO new_index_name; Parâmetros ou Argumentos index_name Nome do índice que deseja alterar. new_index_name Nome do novo índice. Exemplo ALTER INDEX pessoa_idx RENAME TO pessoa_index_nome; 22 23 Excluindo INDEX Sintaxe DROP INDEX index_name; Parâmetros ou Argumentos index_name Nome do índice a excluir Exemplo DROP INDEX pessoa_index_nome; Instrução ALTER TABLE A instrução ALTER TABLE é, basicamente, usada para adicionar, modificar ou excluir colunas e restrições das Tabelas já criadas. Qualquer tipo de alteração que o DBA necessite fazer, pode ser realizada por meio do comando ALTER TABLE, não importa qual o tipo de alteração; pode ser tanto inclusão de campo, quanto exclusão de regra. Qualquer alteração numa estrutura já existente deve ser realizada por meio do ALTER TABLE. O primeiro comando visto aqui para a criação da estrutura foi o CREATE TABLE; porém, o que acontece se nós nos esquecermos de criar um campo, uma restrição ou mesmo precisarmos alterar um tipo de dado em algo que já foi criado? Primeira coisa: não precisam entrar em pânico; o que temos de fazer é modificar a estrutura existente. É simples. Vamos ver os exemplos a seguir, então! Adicionando uma coluna ou várias colunas ALTER TABLE table_name ADD column_name column-definition; Exemplo ALTER TABLE tb_pessoa ADD id_cpf varchar2(11); 23 UNIDADE SQL Básico Sintaxe ALTER TABLE table_name ADD (column_1 column-definition, column_2 column-definition, ... column_n column_definition); Exemplo ALTER TABLE tb_pessoa ADD (id_cpf varchar2(11), Id_cnpf varchar2(14)); Como vocês perceberam, nos exemplos acima, foi demonstrado o comando para inserir elementos novos em uma estrutura já existente. Nos exemplos a seguir, iremos demonstrar a utilização do comando para modificar os valores de uma estrutura já existente. No caso da modificação de tipos de Dados, devemos ter certeza da alteração, para que não ocorra perda de informações nos valores que já estão salvos nas Tabelas. Modificando uma coluna na Tabela Sintaxe ALTER TABLE table_name MODIFY column_name column_type; O comando MODIFY é utilizado para alterar o tipo do campo na Tabela; deve-se tomar certo cuidado com a modificação para que os Dados não sejam alterados e venhamos a ter problemas futuramente. ALTER TABLE tb_pessoa MODIFY nm_pessoa varchar2(100) NOT NULL; No exemplo, acima o campo nm_pessoa, sobre uma modificação de tamanho, foi alte- rado o tamanho de 50 para 100 e passou a ser obrigado, quando foi informado a cláusula NOT NULL. 24 25 Modificando várias colunas na Tabela Sintaxe ALTER TABLE table_name MODIFY (column_1 column_type, column_2 column_type, ... column_n column_type); Exemplo ALTER TABLE tb_pessoa MODIFY (nm_pessoa varchar2(100) NOT NULL, cidade varchar2(75) DEFAULT São Paulo’ NOT NULL); Nesse exemplo, foram aplicadas 2 modificações na Tabela e nas colunas já existentes. O campo nm_pessoal passou a ter 100 posições e também de preenchimento obrigado, e o campo nm_cidade passou a ter 75 posições, preenchimento obrigatório e também um valor default, caso na hora da inserção não seja informado nenhum valor, será preenchido com o conteúdo São Paulo. Excluindo uma coluna na Tabela A única restrição para a exclusão de uma coluna é que ela não seja um campo PK ou FK. Ao excluir um campo de uma Tabela, os Dados também não estarão mais disponíveis para o campo eliminado. ALTER TABLE table_name DROP COLUMN column_name; Exemplo ALTER TABLE tb_pessoa DROP COLUMN cidade; Perfeito! Até agora estamos indo muito bem! Vimos como criar estrutura e alterar estrutura e, para concluirmos, este tópico, iremos verificar a criação das restrições que irão garantir a integridade dos Dados que serão sal- vos, alterados ou excluídos de uma Tabela. 25 UNIDADE SQL Básico Criando PRIMARY KEY Sintaxe ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n); Exemplo ALTER TABLE tb_pessoa ADD CONSTRAINT pessoa_pk PRIMARY KEY (cd_pessoa); Nessa instrução, foi criada uma chave primária na tabela tb_pessoa com o nome de pessoa_pk. A chave primária foi associada à coluna cd_pessoa. Existe também a possibilidade de se criar a chave primária composta: ALTER TABLE tb_pessoa ADD CONSTRAINT pessoa_pk PRIMARY KEY (cd_pessoa,id_cpf); Criando FOREIGN KEY A criação de uma chave estrangeira é uma forma de mantermos os Dados íntegros nas Tabelas. Uma chave estrangeira significa que os valores de uma Tabela A devem também existir em uma Tabela B. A Tabela referenciada é chamada de Tabela Pai, enquanto a Tabela com a chave ex- terna é chamada de Tabela Filho. A chave estrangeira na Tabela Filho, geralmente, faz referência a uma chave primária na Tabela Pai. Quando a Tabela possuir chaves primárias compostas, a Tabela Filho também deve ter as mesmas colunas, para que a relação seja correta. Uma chave estrangeira pode ser definida em uma instrução CREATE TABLE ou em uma instrução ALTER TABLE. Para exemplificarmos a criação de foreign key, iremos criar uma Tabela. CREATE TABLE tb_dependente ( cd_dependente number(10), cd_pessoa number(10), nm_depentente varchar2(50) NOT NULL, id_sexo char(1), CONSTRAINT dependente_pk PRIMARY KEY (cd_ dependente) ); 26 27 Sintaxe ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n); Exemplo ALTER TABLE tb_dependente ADD CONSTRAINT dependente_fk FOREIGN KEY (cd_pessoa) REFERENCES tb_pessoa(cd_pessoa); Criando CHECK A restrição CHECK é usada para criar validações nas linhas de cada Tabela. Devemos saber alguns detalhes sobre essa restrição; por exemplo, nas restrições criadas nessa co- luna só podem ser referenciadas as colunas da própria Tabela em questão. Não é possível criar uma subquery para a criação da restrição e não é permitido referenciar outras Tabelas. A restrição CHECK pode ser criada tanto na instrução CREATE TABLE, quanto na instrução ALTER TABLE. Sintaxe ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition); Exemplo ALTER TABLE tb_dependente ADD CONSTRAINT dependente_ck CHECK (id_sexo IN (‘M’, ‘F’)); Nesse exemplo, a coluna id_sexo está com uma restrição na qual só será permitido in- serir valor M ou F. Lembrando-se de que o Oracle é case sensitive; então, existe diferença entre as letras maiúsculas e minúsculas. Todas as restrições que foram criadas no Banco podem ser desabilitadas, habilitadas ou excluídas. Devemos ter olhar crítico para a execução desses comandos, porque estamos modificando as restrições das colunas, ou seja, podemos alterar alguma regra que possa transformar as Tabelas numa verdadeira bagunça. 27 UNIDADE SQL Básico Desabilitando Constraints Qualquer ação aqui deve ser bem estudada; todas as alterações de regras podem ser a aplicados para as constraints PRIMARY KEY, FOREIGN KEY e CHECK. Sintaxe ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; Parâmetros ou Argumentos table_name Nome da Tabela que deseja alterar. constraint_name Nome da restrição que foi criada. Sintaxe ALTER TABLE table_name ENABLE CONSTRAINT constraint_name; ALTER TABLE table_name DROP CONSTRAINT constraint_name; Você pode, também, querer alterar o nome de uma coluna ou o nome de uma Tabela e manter toda a estrutura já criada. A instrução é muito simples. Vejamos a seguir as instruções para mudar o nome de campos e Tabelas. Lembrando-se de que essa alteração não altera o tipo de Dados, nem os valores já armazenados. Renomeando o nome da coluna Sintaxe ALTER TABLE table_name RENAME COLUMN old_name TO new_name; Exemplo ALTER TABLE tb_pessoa RENAME COLUMN id_cpf TO nr_cpf; 28 29 Alterandonome da tabela Sintaxe ALTER TABLE table_name RENAME TO new_table_name; Exemplo ALTER TABLE tb_pessoa RENAME TO pessoa; Caros(as) alunos(as), estamos quase finalizando os elementos de DDL. Resumindo, até aqui, já finalizamos as instruções de alteração da estrutura e, para fechar, iremos demons- trar a instrução de exclusão de estruturas. Instrução DROP TABLE A instrução DROP TABLE remove ou exclui a Tabela do Banco de Dados. Sintaxe DROP TABLE table_name [ CASCADE CONSTRAINTS ] [ PURGE ]; Parâmetros e Argumentos table_name Nome da Tabela que deseja excluir. CASCADE CONSTRAINTS Esse comando é opcional. Caso seja relacionado, no momento da exclusão das Ta- belas, as restrições também são excluídas, fazendo com que não fique nenhuma infor- mação no Banco. PURGE Esse comando é opcional. Caso seja relacionado, as Tabelas e suas dependências serão excluídas fisicamente de todas os lugares e não será permitido recuperá-las. Exemplo DROP TABLE tb_pessoa; DROP TABLE tb_pessoaCASCADE CONSTRAINTS; DROP TABLE tb_pessoaPURGE; Devemos só ficar atentos na hora da exclusão da Tabela, se existe algum tipo de re- lacionamento com outra Tabela. 29 UNIDADE SQL Básico Manipulando Informações Nos exemplos a seguir, iremos utilizar a Linguagem de Manipulação de Dados, que são comandos que modificam o conteúdo das Tabelas, ou seja, podemos incluir Dados, alterar Dados já existentes ou até mesmo excluir informações. Os comandos que serão abordados são INSERT, UPDATE e DELETE. Nesses exemplos, iremos utilizar a estrutura criada nos tópicos anteriores como as tabelas tb_pessoa e tb_dependentes. Caso não tenha essas Tabelas criadas, retorne aos tópicos anteriores e execute as instruções de CREATE TABLE das respectivas tabelas tb_pessoa e tb_dependente. Instrução INSERT A instrução INSERT é usada para gravar um registro em uma Tabela. Sintaxe INSERT INTO table_name (column1, column2, ... column_n ) VALUES (expression1, expression2, ... expression_n ); Parâmetros e Argumentos table_name Nome da Tabela. column1, column2, ... column_n Colunas nas quais os valores serão inseridos. expression1, expression2, ... expression_n Valores associados às colunas. Exemplo INSERT INTO tb_pessoa (cd_pessoa, nm_pessoa, cidade, id_cpf) VALUES (1000, ‘Alexander Gobbato’, ‘São Paulo’, ‘00000000000’); 30 31 Você pode verificar se o comando foi executado corretamente realizando a instrução SELECT na tabela tb_pessoa, que deverá ter o seguinte conteúdo armazenado: Figura 13 Instrução UPDATE A instrução UPDATE é usada para atualizar um registro em uma Tabela. No comando de utilização, devemos ficar atentos à cláusula WHERE. Essa cláusula não é obrigatória, mas se não a utilizarmos, as alterações podem ser feitas em todos os registros; portanto, todo cuidado é pouco! Caso você não se recorde do funcionamento da instrução WHERE, eu recomendo uma nova leitura do tópico 4.3 e seus subtópicos. UPDATE table SET column1 = expression1, column2 = expression2, ... column_n = expression_n [WHERE conditions]; Parâmetros e Argumentos table_name Nome da Tabela. column1, column2, ... column_n Colunas nas quais os valores serão alterados. expression1, expression2, ... expression_n Novos valores que serão atualizados no Banco; cada expressão corresponde a uma coluna. WHERE conditions Opcional; condições que devem ser cumpridas para que os registros sejam alterados. Exemplo - Alterando uma única coluna UPDATE tb_pessoa SET nm_pessoa = ‘Alexander Gobbato P. Albuquerque’ WHERE cd_pessoa = 1000; 31 UNIDADE SQL Básico Você pode verificar se o comando foi executado corretamente realizando a instrução SELECT na Tabela tb_pessoa, que deverá ter o seguinte conteúdo armazenado: Figura 12 Exemplo - Alterando várias coluna UPDATE tb_pessoa SET nm_pessoa = ‘Gobbato’, Cidade = ‘Rio Grande do Sul’ WHERE cd_pessoa = 1000; Figura 13 Instrução DELETE A instrução DELETE é usada para excluir um registro numa Tabela. No comando de utilização, devemos ficar atentos à cláusula WHERE. Ela não é obrigatória, mas se não a utilizarmos, as exclusões podem ser feitas em todos os registros; portanto, todo cuidado é pouco! Sintaxe DELETE FROM table [WHERE conditions]; Parâmetros e Argumentos table_name Nome da tabela. WHERE conditions Opcional; as condições que devem ser cumpridas para que os registros sejam excluídos. DELETE FROM tb_pessoa WHERE nm_pessoa = ‘Gobbato’; 32 33 Após a exclusão, ao executarmos o comando de SELECT, o resultado será o seguinte: Chegamos ao final desta Unidade e podemos ver quais funcionalidades são pertinentes à criação de estrutura de Dados e quais funcionalidades são próprias para o manuseio da informação. Assim, finalizamos aqui o nosso conteúdo sobre os comandos DQL, DDL e DML. 33 UNIDADE SQL Básico Material Complementar Indicações para saber mais sobre os assuntos abordados nesta Unidade: Livros Oracle Database 11g SQL Oracle Database 11g SQL. Bookman, 2008. Banco de Dados: Implementação em SQL, PL/SQL e Oracle 11g PUGA, Sandra. Banco de Dados: implementação em SQL, PL/SQL e oracle 11g. São Paulo: Pearson Education do Brasil, 2014. 328p.; Vídeos Primeiro diagrama E-R usando Oracle SQL Developer Data Modeler https://youtu.be/DQQPiP_9DCs 34 35 Referências PUGA, Sandra. Banco de Dados: implementação em SQL, PL/SQL e oracle 11g. São Paulo: Pearson Education do Brasil, 2014. 328p. Price, Jason. Oracle Database 11g SQL – Domine SQ e PL/SQL no Banco de Dados Oracle. Bookman. 35 Inserir Título Aqui Inserir Título Aqui Linguagens de Banco de Dados SQL Avançado Responsável pelo Conteúdo: Prof. Me. Alexander Gobbato Paulino Albuquerque Revisão Textual: Prof. Esp. Claudio Pereira do Nascimento Nesta unidade, trabalharemos os seguintes tópicos: • Introdução; • Trabalhando com Campo Data; • Inserindo uma Data; • Funções; • Funções de Conversão; • Funções de Grupo; • Subqueries (Sub-Consultas); • Trabalhando com Junção de Informações de Tabelas. Fonte: iStock/Getty Im ages Objetivos • Demonstrar a funcionalidade de algumas funções existentes no Oracle para manuseio de Dados do tipo data, texto e número; • Demonstrar, também, o funcionamento das ligações entre Tabelas para que possam ser exibidos Dados de Tabelas distintas, mas interligadas por PK e FK. Caro Aluno(a)! Normalmente, com a correria do dia a dia, não nos organizamos e deixamos para o último momento o acesso ao estudo, o que implicará o não aprofundamento no material trabalhado ou, ainda, a perda dos prazos para o lançamento das atividades solicitadas. Assim, organize seus estudos de maneira que entrem na sua rotina. Por exemplo, você poderá escolher um dia ao longo da semana ou um determinado horário todos ou alguns dias e determinar como o seu “momento do estudo”. No material de cada Unidade, há videoaulas e leituras indicadas, assim como sugestões de materiais complementares, elementos didáticos que ampliarão sua interpretação e auxiliarão o pleno entendimento dos temas abordados. Após o contato com o conteúdo proposto, participe dos debates mediados em fóruns de discussão, pois estes ajudarão a verificar o quanto você absorveu do conteúdo, além de propiciar o contato com seus colegas e tutores, o que se apresenta como rico espaço de troca de ideias e aprendizagem. Bons Estudos! SQL Avançado UNIDADE SQL Avançado Contextualização Os Bancos de Dados Relacionais estão organizados em forma de Tabela (linhas e colunas). Para transformarmos esses Dados em informações, temos de, na maioria das vezes, unir essas Tabelas. A Linguagem utilizada no Banco de Dados é um recurso importante; é por meio dela que os comandos são executados nos Bancos Relacionais, permitindo, então, que a in- formação seja exibida num relatório ou numa simples consulta. Trabalhar com formatações de Dados é uma atividade corriqueira dos desenvolve- dores; realizar o tratamento dos Dados também requer recurso que, muitas vezes, já existem em funções no próprioBanco. Por exemplo, se precisarmos gerar um Relatório Financeiro com base em n Tabe- las, esses Dados devem estar bem formatados para que possamos apresentar. Isso será um diferencial com o qual muitos desenvolvedores não se preocupam. 6 7 Introdução Nesta Unidade, iremos continuar usando o ambiente que foi criado anteriormente. Caso necessário, reveja o Material Complementar e realize todo o procedimento nova- mente para ter uma ideia onde pode efetuar os testes. Para iniciarmos esta Unidade, iremos começar trabalhando com um tipo de Dado que pode ser exibido de várias formas; tudo irá depender do que deverá ser exibido. Você deve estar se perguntando: mas como isso é possível? Bem, iremos mostrar nos exemplos a seguir que isso é na verdade muito simples de se resolver. Durante o decorrer desta Unidade, iremos utilizar várias Tabelas para demonstrar os comandos; iremos trabalhar com a base de Dados HR, criar algumas Tabelas para exemplo e utilizar a Tabela DUAL. A dual é uma Tabela criada pelo Oracle junto com o Dicionário de Dados. Consiste em, exatamente, uma coluna cujo nome é fictício e um registro. O valor desse registro é X. Trabalhando com Campo Data Para trabalharmos com o tipo de data no Oracle, precisamos ter conhecimento mí- nimo sobre suas características e algumas funções que possam nos ajudar tanto na hora da inclusão, quanto na hora da alteração. Quando precisarmos trabalhar com um valor do tipo data, devemos utilizar o data type DATE. CREATE TABLE tb_pessoa (cd_pessoanumber(10) NOT NULL, nm_pessoa varchar2(50) NOT NULL, cidade varchar2(50), dt_nasc date, CONSTRAINT pessoa_pkprimarykey (cd_pessoa) ); O tipo de Dado DATE do Oracle é um tipo especial, capaz de armazenar datas que vão de 4712 a.C. a 9999 d.C., mas, além de armazenar informações de século, ano, mês e dia, ele também é capaz de armazenar informações de hora, minuto e segundo. 7 UNIDADE SQL Avançado Inserindo uma Data Para registrarmos uma informação no campo do tipo data, precisamos colocar o valor entre aspas. Mais adiante, iremos aprender a melhor forma de inserir a data utilizando a função TO_DATE. A função TO_DATE converte um determinado texto em uma data válida para o Oracle. Sintaxe INSERT INTO tb_pessoa (cd_pessoa, nm_pessoa, cidade, dt_nasc) VALUES (1, ‘Alexander Gobbato’, ‘São Paulo’, TO_DATE(‘09-07-1980’,’DD- MM-YYYY’)); Após a inserção dos Dados na Tabela tb_pessoa, você pode executar o comando SELECT para visualizar as informações. Caso não utilize nenhum tipo de forma- tação de data, o Sistema irá utilizar o padrão do Sistema Operacional em que está instalado o Oracle. Figura 1 Agora você deve estar se perguntando: Por que tive de usar a função TO_DATE para inserir um valor de data e ao consultar ele usa o padrão do Sistema Operacional? Bom, vamos às respostas. Primeiro que, ao utilizar a função TO_DATE, estamos informando ao Banco qual o valor do dia, do mês e do ano e, segundo, sendo um campo data, temos de informar os valores corretamente. Ok. Comando de inserção com data finalizado. Agora, podemos trabalhar com a visualização de um conteúdo que foi inserido como data para se visualizar e recupe- rar as informações que pertencem ao tipo de Dado DATE, o Oracle oferece algumas funções para formatar. A função TO_CHAR transforma qualquer tipo de Dado em texto; quando aplicamos essa função em um Dado do tipo DATE, podemos manipular a saída de Dados de diver- sas formas; as possibilidades são diversas. 8 9 Funções para Trabalhar com Data O Oracle permite ao usuário formatar o campo data com uma série de opções, como demonstrado na Tabela a seguir. Tabela 1 Máscara Significado SCC ou CC Século; ‘S’ faz preceder de ‘-’ as datas ‘BC’ (Antes de Cristo). YYYY ou SYYYY Século; ‘S’ faz preceder de ‘-’ as datas ‘BC’ (Antes de Cristo). YYY ou YY ou Y Últimos 3 dígitos do ano ou 2 últimos ou último dígito do ano. Y,YYY Ano com separador dos milhares nessa posição. SYEAR ou YEAR Ano por extenso (em inglês); ‘S’ faz preceder de ‘-’ as datas ‘BC’ (Antes de Cristo). BC ou AD Indicador de BC/AD (Antes de Cristo / Depois de Cristo). B.C. ou A.D. Indicador de BC/AD com ponto. Q Trimestre do ano. MM Mês em número de 1...12. MONTH Nome do mês preenchido com espaços até 9 caracteres (Setembro). MON Nome do mês com três letras (JAN, FEB, MAY,...). WW, IW ou W Semana do ano formato Oracle, semana do ano formato ISO ou semana do mês. DDD ou DD ou D Dia do ano, mês ou semana. DAY Nome do dia da semana, por extenso, preenchido com espaços até 9 caracteres. DY Nome do dia da semana com dois caracteres. J Dia do calendário Juliano; o número de dias desde 31 de Dezembro de 4713 A.C. AM ou PM Indicador de AfterMoon (AM) ou Post Moon (PM). A.M. ou P.M. Indicador de AfterMoon ou Post Moon com pontos. HH ou HH12 Hora do dia em formato 1-12. HH24 Hora do dia em formato 0-23. MI Minutos. SS Segundos. SSSSS Segundos decorridos desde a meia noite. FM Prefixo que pode ser acrescentado aos códigos anteriores; utilizado em MONTH ou DAY, suprime o preenchimento de espaços; usado com números suprime os zeros à esquerda. TH, SP, SPTH, THSP Sufixos que convertem um número em ordinal ou por extenso; usando os dois, fica número por extenso ordinal. Fonte: https://goo.gl/DjxS8Q 9 UNIDADE SQL Avançado Exemplo SELECT nm_pessoa,dt_nasc, TO_CHAR(dt_nasc,’DAY, DD “de” MONTH “de” YYYY’) FROM TB_PESSOA; Conforme você pode visualizar no retorno a seguir, utilizamos uma sequência de elementos para formatarmos um campo DATE. Como comentado na Tabela anterior, aplicamos a função TO_CHAR no campo DT_NASC com a formatação DAY que retorna o dia da semana, DD que retornar o dia do mês, MONTH que retorna o nome do mês por extenso, e YYYY que retorna o ano com 4 dígitos. Figura 2 Nos exemplos a seguir, iremos utilizar a Tabela criada pelo Oracle, chamada DUAL. Essa Tabela é usada para nos auxiliar em alguns testes. Iremos trabalhar com algumas funções nativas do Oracle, que são as Funções Numéricas, Funções de Data, Funções de Texto e Funções de Conversão. A Tabela Dual é muito utilizada pelos desenvolvedores, DBAs e pela própria Oracle para fazer operações com select, onde não é necessário fazer extração de Dados em Tabelas. Funções Você sabia que as funções são chamadas de sub-rotinas e são muito utilizadas em programação? Um dos grandes benefícios é não precisar copiar o código todas as vezes que preci- sar executar aquela operação. No Oracle existem várias funções que podem ser utiliza- das juntamente com as expressões. As Funções Numéricas são utilizadas, por exemplo, para trabalhar com o ar- redondamento de valores nas casas decimais; as Funções de Texto, por exemplo, 10 11 podem juntar informações de vários lugares diferentes e transformar numa única informação; as Funções de Data podem trabalhar com contas entre um determi- nado período etc. O importante da função é que ela já está programada e sempre irá devolver al- gum valor. Vamos ver alguns exemplos de funções, a seguir. Funções Numéricas Figura 3 Exemplo Arredondar as casas decimais para 2 casas. SELECT ROUND(1200.185,2) FROM DUAL; Resultado Figura 4 11 UNIDADE SQL Avançado Funções Textos Figura 5 Exemplo Transformar o texto de minúsculo para maiúsculo. SELECT UPPER(‘alexandergobbato paulino albuquerque’) FROM DUAL; Resultado Figura 6 Exemplo Converter cada inicial da palavra para maiúscula. SELECT INITCAP(‘alexandergobbato paulino albuquerque’) FROM DUAL; Resultado Figura 7 12 13 Funções Data Figura 8 Exemplo Retornar a data atual do servidor. SELECT SYSDATE FROM DUAL; Resultado Figura 9 Exemplo Formatar o campo data para exibir no formato DIA-MÊS-ANO. SELECT TO_CHAR(SYSDATE,’DD-MM-YYYY’) FROM DUAL; Resultado Figura 10 Existe a Função chamada de Função de Conversão. Essa função é utilizada pelo de- senvolvedor como uma espécie de programação condicional; com ela podemos testar ou verificarcondições e, então, exibir um determinado valor no final. Mas, espere um pouco, você deve estar se perguntando agora: “Eu irei programar?” Bom, a resposta é: “quase”. Calma! 13 UNIDADE SQL Avançado Vamos explicar. A função de conversão irá impor alguns testes, como se fossem perguntas que de- vem ser respondidas com verdadeiro ou falso e, dependendo do resultado da pergunta, determinada sentença irá ser executada. Vamos ver nos exemplos a seguir como a utilização é simples. Funções de Conversão Existem duas formas de utilizar as funções; uma é chamada de Decode e a outra de Case; e ambas podem chegar ao mesmo resultado. Vamos ver, então, o funcionamento. Função DECODE Sintaxe DECODE (expression, search, result [, search, result]... [, default]) Parâmetros ou Argumentos • expression O valor a comparar. Ele é convertido automaticamente para o tipo de Dados do primeiro valor de pesquisa antes de comparar. • search O valor que é comparado contra a expressão. Todos os valores de pesquisa são convertidos automaticamente para o tipo de Dados do primeiro valor de pesquisa antes de comparar. • result O valor retornado, se a expressão for igual à pesquisa. • default Opcional. Se nenhuma correspondência for encontrada, a função DECODE retornará o default. Se o padrão for omitido, a função DECODE retornará NULL (se nenhuma correspondência for encontrada). No exemplo a seguir, iremos exibir uma informação como base no valor registrado no campo supplier_id, fazendo uma comparação com uma linguagem de programa- ção, a sintaxe DECODE se assemelha à instrução SWITCH CASE. SELECT supplier_name, DECODE(supplier_id, 10000, ‘IBM’, 10001, ‘Microsoft’, 10002, ‘Hewlett Packard’, ‘Gateway’) result FROM suppliers; 14 15 Função CASE Sintaxe CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END Parâmetros ou Argumentos • expression Opcional. É o valor que será comparando à lista de condições. • condition_1, condition_2 ... condition_N As condições devem ser todas do mesmo tipo. As condições são verificadas na or- dem em que foram criadas. Uma vez que uma condição seja verdadeira, a declaração CASE retornará o resultado e não avaliará as condições seguintes. • result Os retornos devem ser todos do mesmo tipo. Quando a condição é verdadeira, o valor é retornado. Exemplo No exemplo a seguir, iremos exibir uma informação como base no valor registrado no campo owner, fazendo uma comparação com uma linguagem de programação, a sintaxe CASE se assemelha à instrução IF/ELSE. SELECT table_name, CASE owner WHEN ‘SYS’ THEN ‘The owneris SYS’ WHEN ‘SYSTEM’ THEN ‘The owneris SYSTEM’ ELSE ‘The ownerisanothervalue’ END FROM all_tables; 15 UNIDADE SQL Avançado Funções de Grupo As Funções de Grupo trabalham em conjunto com vários registros ao mesmo tem- po e retornam apenas um registro como resultado. A função de grupo irá agrupar os registros com valores iguais e juntamente com as funções de grupos irão fornecer como resultado um único registro de retorno. Função de Agregação As Funções de Agregação efetuam operações em vários valores para criar resultados de resumo. Futuros especialistas em Banco de Dados, vamos testar, então, algumas funções de agregações existentes. Para os exemplos a seguir, iremos utilizar a estrutura HR, que é criada no momento da instalação do Oracle. Lembre-se de acessar o Material Complementar e, caso não tenha instalado o Oracle, providencie a instalação para que os testes possam ser executados. a) A função SUM retorna o valor somado de uma expressão: no exemplo a seguir, queremos exibir a soma de todos os salários da Tabela Employees, mas o agrupamento só será realizado onde os salários forem maiores do que 5000; então, vamos por partes: primeiro, vamos fazer uma pesquisa retor- nando todos os salários maiores do que 5000. SELECT salary FROM employees WHERE salary> 5000; O resultado dessa query deve retornar um total de 58 linhas com os salários maiores que 5000. Figura 11 16 17 Ao utilizar a função de agregação no campo salary, o Banco irá executar a fun- ção e, então, retornar um único valor. SELECT SUM(salary) AS “Salário Total” FROM employees WHERE salary> 5000; Resultado Figura 12 b) A função COUNT retorna a contagem de uma expressão: no exemplo a seguir, queremos exibir o total de funcionários que possuem a informação no campo salary maior do que 7500. Um detalhe importante que deve ser mencionado: a função COUNT só contabiliza valores diferentes de NULL. SELECT COUNT(*) AS “Número de funcionários” FROM employees WHERE salary> 7500; Resultado Figura 13 Veja você, verificando o resultado acima, podemos chegar à conclusão que ape- nas 39 funcionários possuem um salário maior do que 7500. As funções a seguir, MIN e MAX, têm por finalidade retornar o menor e o maior valor. Vamos ver, então, o funcionamento delas. c) A função MIN retorna o valor mínimo de uma expressão. SELECT MIN(salary) AS “Menor Salário” FROM employees; Resultado Figura 14 17 UNIDADE SQL Avançado d) A função MAX retorna o valor máximo de uma expressão. SELECT MAX(salary) AS “Maior Salário” FROM employees; Resultado Figura 15 e) A função AVG retorna o valor médio de uma expressão. SELECT AVG(salary) AS “Média Salarial” FROM employees WHERE salary> 25000; Vimos, então, que essas 5 funções de agregação são bem úteis e simples, mas existem mais exemplos que podem ser utilizados. Veja o link disponível em: https://goo.gl/2Wp4jE. Muito bem! Juntamos os registros, exibimos o resultado em apenas um retorno, mas, ainda assim, poderíamos melhorar esse resultado; e aí provavelmente vem a pergunta: “Será que isso é possível?” Sim. Podemos criar níveis de agrupamentos e para isso iremos utilizar a cláusula GROUP BY. Essa cláusula é usada em uma instrução SELECT para coletar Dados em vários regis- tros e agrupar os resultados por uma ou mais colunas. Sintaxe SELECT expression1, expression2, ... expression_n, aggregate_function (aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n; Parâmetros ou Argumentos • expression1, expression2, ... expression_n As expressões que não estão encapsuladas dentro de uma função agregada e de- vem ser incluídas na cláusula GROUP BY. 18 19 • aggregate_function Pode ser uma função como as funções SUM, COUNT, MIN, MAX ou AVG. • aggregate_expression Essa é a coluna ou expressão em que a função agregada será usada. • tables As Tabelas das quais você deseja recuperar registros. Deve haver pelo menos uma Tabela listada na cláusula FROM. • WHERE Conditions Opcional. As condições que devem ser atendidas para que os registros sejam selecionados. Juntamente com a cláusula GROUP BY, podemos utilizar a Cláusula HAVING. A cláusula HAVING não é obrigatória, mas, se for usada, deve vir após a cláusula GROUP BY para restringir o resultado dos agrupamentos de linhas. Vamos dar uma olhada em alguns exemplos para entender melhor as cláusulas GROUP BY e HAVING. Exemplos Nos exemplos a seguir, podemos exibir o total de salário pago por Departamento. Observe que o agrupamento ocorrerá pela coluna department_id. SELECT department_id, SUM(salary) AS “Total Salário” FROM employees GROUP BY department_id; Resultado Figura 16 Caso você queira restringir/filtrar o resultado de um agrupamento, devemos uti- lizar a instrução HAVING. Nesse exemplo, iremos acrescentar a cláusula instrução criada acima. 19 UNIDADE SQL Avançado Veja o resultado. SELECT department_id, SUM(salary) AS “Total Salário” FROM employees GROUP BY department_id HAVING SUM(salary) > 25000; Resultado Figura 17 A cláusula HAVING irá filtrar os resultados para que somente Departamentos com somatório superiores a 25000 sejam retornados. Vejamos, agora, uma série de agrupamentos, inclusive com a utilização da instru-ção WHERE, na qual o papel dessa instrução é filtrar os registros para que o agrupa- mento possa ser realizado. SELECT department_id, COUNT(*) AS “Número de Empregados” FROM employees WHERE salary> 4500 GROUP BY department_id HAVING COUNT(*) >3; Resultado Figura 18 20 21 A cláusula HAVING irá filtrar os resultados para que somente Departamentos com mais de 3 funcionários, que possuem salário superior a 4500, sejam retornados. SELECT department_id, MIN(salary) AS “Menor Salário” FROM employees GROUP BY department_id HAVING MIN(salary) < 4200; Resultado Figura 19 A cláusula HAVING retornará apenas os Departamentos nos quais o salário mí- nimo está a seguir de 4200. SELECT department_id, MAX(salary) AS “Maior Salário” FROM employees GROUP BY department_id HAVING MAX(salary) > 4500; Resultado Figura 20 A cláusula HAVING retornará apenas os Departamentos cujo salário máximo é maior que 4500. 21 UNIDADE SQL Avançado Subqueries (Sub-Consultas) Uma subquerie é uma consulta dentro de outra consulta. As subqueries podem ser criadas na cláusula WHERE, na cláusula FROM ou na cláusula SELECT. Na cláusula WHERE, essas subqueries são chamadas subconsultas aninhadas. Exemplo WHERE SELECT * FROM all_tablestabs WHERE tabs.table_name IN (SELECT cols.table_name FROM all_tab_columnscols WHERE cols.column_name = EMPLOYEE_ID); Resultado Figura 21 Exemplo FROM SELECT department_name, subquery1.total_amt FROM departments, (SELECT department_id, SUM(salary) AS total_amt FROM employees GROUP BY department_id) subquery1 WHERE subquery1.department_id = departments.department_id; Resultado Figura 22 22 23 Exemplo SELECT SELECT tbls.owner, tbls.table_name, (SELECT COUNT(column_name) AS total_columns FROM all_tab_columnscols WHERE cols.owner = tbls.owner AND cols.table_name = tbls.table_name) subquery2 FROM all_tablestbls; Resultado Figura 23 Trabalhando com Junção de Informações de Tabelas Joins Os Joins são usados para retornar Dados de várias Tabelas. Os Joins são utilizando sempre que há a necessidade de buscar informações em 2 ou mais Tabelas juntas. Há quatro tipos de joins: • INNER JOIN (Junção Simples); • LEFT OUTER JOIN (LEFT JOIN); • RIGHT OUTER JOIN (RIGHT JOIN); • FULL OUTER JOIN (FULL JOIN). Inner Join O INNER JOIN retorna todas os registros comuns entre as Tabelas. 23 UNIDADE SQL Avançado SELECT departments.department_id, departments.department_name, employees.first_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id; Todas os registros das Tabelas de employees e departments que possuem um valor correspondente do department_id nas Tabelas de employees e departments. Resultado Figura 24 Left Outer Join Neste tipo de ligação são retornados todos os registros especificados à esquerda da condição ON e apenas os registros de outra Tabela em que os valores de ambas são iguais. SELECT departments.department_id, departments.department_name, employees.first_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id; Todos os registros da Tabela de employees e apenas os registros da Tabela de departments em que os campos unidos são iguais. Se um valor de department_id na Tabela de employees não existir na Tabela de departments, todos os campos na Tabela de departments serão exibidos como nulos ( - ). Resultado Figura 25 24 25 Right Outer Join Neste tipo de ligação são retornados todos os registros especificados à direita da condi- ção ON e apenas os registros de outra Tabela em que os valores de ambas são iguais. SELECT departments.department_id, departments.department_name, employees.first_name FROM employees RITGH JOIN departments ON employees.department_id = departments.department_id; Todos os registros da Tabela de departments e apenas os registros da Tabela employees em que os campos unidos são iguais. Se um valor de department_id na Tabela de departments não existir na Tabela de employees, todos os campos na Tabela de employees serão exibidos como nulos. Resultado Figura 26 Join Full Neste tipo de ligação, todos os registros da Tabela da esquerda e da Tabela da direita são retornados, caso a condição não seja satisfatória, serão exibidos valores nulos no lugar em que a condição de junção não é atendida. SELECT departments.department_id, departments.department_name, employees.first_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id; Todos os registros da Tabela de employees e todos os registros da Tabela departments e sempre que a condição de associação não for atendida, valores nulos serão exibidos no resultado. 25 UNIDADE SQL Avançado Se um valor de department_id na Tabela de employees não existir na Tabela de departments, todos os campos na Tabela de departments serão exibidos como nulo. Essa mesma condição vale para a Tabela de employees. Resultado Figura 27 SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL A B A B SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key A B SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key A B SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL A B SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key A B SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key A B SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key is NULL OR B.Key IS NULL Figura 28 Fonte: https://goo.gl/d7xeKm 26 27 Material Complementar Indicações para saber mais sobre os assuntos abordados nesta Unidade: Sites Oracle Help Center Funções Oracle. https://goo.gl/BJKtA2 w3schools SQL Joins. https://goo.gl/PkAq7t Oracle Trabalhando com campo data. https://goo.gl/x7oiUM Livros Oracle Database 11g SQL PRICE, Jason. Oracle Database 11g SQL – Domine SQ e PL/SQL no Banco de Dados Oracle. Porto Alegre: Bookman, 2009. 27 UNIDADE SQL Avançado Referências PUGA, Sandra. Banco de Dados: implementação em SQL, PL/SQL e Oracle 11g. São Paulo: Pearson Education do Brasil, 2014. 328p. PRICE, Jason. Oracle Database 11g SQL – Domine SQ e PL/SQL no Banco de Dados Oracle. Porto Alegre: Bookman, 2009. 28 Inserir Título Aqui Inserir Título Aqui Linguagens de Banco de Dados Programando em SQL I Responsável pelo Conteúdo: Prof. Me. Alexander Gobbato Paulino Albuquerque Revisão Textual: Prof. Esp. Claudio Pereira do Nascimento Nesta unidade, trabalharemos os seguintes tópicos: • Introdução; • O que é PL/SQL? • Estrutura de Repetição WHILE LOOP; • Instrução de Repetição FOR LOOP; • Instrução Exit; • Cursores. Fonte: iStock/Getty Im ages Objetivos • Abordar o conceito de programação para banco de dados PL/SQL; • Demonstrar que os comandos de banco de dados vão além de montar estrutura para o armazenamento de informações e de manipulação de informações; • Demonstrar o funcionamento das variáveis, a utilização de estrutura condicionais e estrutura de repetição e por fim introduziremos o conceito de cursor para armazenarmos os resultados em memória. Caro Aluno(a)! Normalmente, com a correria do dia a dia, não nos organizamos e deixamos para o último momento o acesso ao estudo, o que implicará o não aprofundamento no material trabalhado ou, ainda, a perda dos prazos para o lançamento das atividades solicitadas. Assim, organize seus estudos de maneira que entrem na sua rotina. Por exemplo, você poderá escolher um dia ao longo da semana ou um determinado horário todos ou alguns dias e determinar como o seu “momento do estudo”. No material de cada Unidade, há videoaulas e leituras indicadas, assim como sugestões de materiais complementares, elementos didáticos que ampliarão sua interpretação e auxiliarãoo pleno entendimento dos temas abordados. Após o contato com o conteúdo proposto, participe dos debates mediados em fóruns de discussão, pois estes ajudarão a verificar o quanto você absorveu do conteúdo, além de propiciar o contato com seus colegas e tutores, o que se apresenta como rico espaço de troca de ideias e aprendizagem. Bons Estudos! Programando em SQL I UNIDADE Programando em SQL I Introdução Olá pessoal, nesta unidade continuaremos usando o ambiente que foi criado ante- riormente. Caso necessário, reveja o material complementar e realize todos os proce- dimentos novamente para ter um ambiente onde possa efetuar os testes. Vamos relembrar, além de comando de DDL (Data Definition Language) e DML (Data Manipulation Language), podemos trabalhar com programação para banco de dados. A programação que iremos estudar se aplica nas criações de Cursores, Triggers, Procedimentos e Funções. Iremos abordar os seguintes itens dos comandos PL/SQL como criação de variá- veis, estrutura de repetições, estrutura condicional para então criarmos os cursores e entender então o que é um bloco de programação PL/SQL O que é PL/SQL? Linguagem PL/SQL (Procedural Language extensions to SQL) foi introduzida no ano de 1988 como parte do conjunto de tecnologias que compunha a versão 6.0 do SGBD Oracle. Permite a criação e desenvolvimento de programas que são armazenados, compilados e executados no servidor de banco de dados Oracle. Quando Utilizar PL/SQL? Uma das vantagens de utilizar PL/SQL é o fato de a linguagem tornar possível a cons- trução de aplicações eficientes para a manipulação de grandes volumes de dados. Como o bloco PL/SQL é executado no SGDB, os dados manipulados não precisam entrar ou sair do SGBD, portanto, não há a necessidade de se trafegar pela rede. A eficiência da PL/SQL também é garantida através da sua forte integração com a linguagem. É possível executar comandos de SQL diretamente de um bloco PL/SQL, sem a necessidade da utilização de APIs intermediárias (como ODBC ou JDBC). Resumindo, com os comandos PL/SQL podemos alterar, excluir, inserir, selecionar, criar variáveis, constantes, cursores, realizar tratamentos de erros/exceções e trabalhar com estrutura de repetições e decisões. Este link lhe dará uma ideia do que é API, vale a pena a leitura. Disponível em: https://goo.gl/hSc8Sh. Você sabia que um código escrito em PL/SQL pode ser utilizado em diferentes ver- sões do SGBD, ou seja, na maioria dos casos, os códigos são compatíveis mesmo entre versões diferentes. Isso é muito interessante, não concordam? Agora que já falamos um pouquinho sobre bloco PL/SQL, vamos começar então em um item muito importante, como iniciar um bloco PL/SQL? 6 7 Figura 1 Vamos ver um exemplo práticos substituindo os elementos por comando de verdade. Figura 2 O resultado após a execução do código é: Figura 3 Vejamos entende esse trecho de código. Na linha 1 declaramos o início do bloco PL/SQL, na linha 2 criamos as declara- ções, nesse caso criamos uma variável do tipo texto e atribuímos a essa variável o valor ‘Alexander Gobbato P. Albuquerque’. Na linha seguinte (3) criamos o bloco onde será inserido os comandos, o bloco é ini- ciado com o comando BEGIN e é finalizado com o comando END (linha 5). Toda a programação será inserida entre esses comandos. O comando dbms_output.put_line imprime o resultado na tela, ou seja, exibe alguma informação. E agora, o que é essa variável que o professor mencionou acima? As variáveis podem auxiliar os programadores no armazenamento de dados tem- porariamente enquanto um determinado código está em execução, vejamos então a sintaxe do comando para a criação de uma variável. variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_ value] 7 UNIDADE Programando em SQL I Parâmetros ou Argumentos • variable_name O nome a atribuir à variável. • [CONSTANT] Opcional. Se especificado, o valor da variável é constante e não pode ser alterado. • datatype O tipo de dados a atribuir à variável. Vamos relembrar um pouquinho, vocês já viram que na criação de uma estrutura de tabelas devemos definir tipos de dados para as colunas. Para as variáveis temos o mesmo conceito, ou seja, podemos criar tipos de dados texto, numérico, datas e booleanos. • Texto (char, varchar2); • Numérico (number); • Data (Date, TimeStamp); • Booleanos (Boolean). Figura 4 Resultado: Figura 5 A cláusula INTO atribui o resultado das colunas escritas no comando SELECT para as respectivas variáveis, ou seja, o valor da coluna first_name será armazenada na variável nome, o valor da coluna last_name será armazenada na variável sobrenome. Temos também o elemento %Type. 8 9 %TYPE É muito comum a utilização do atributo %type. O atributo %type permite declarar um item de dados do mesmo tipo de dados como uma variável ou coluna declarada anteriormente (sem saber qual é esse tipo). Se a declaração do item referenciado for alterada, a declaração do item de referência será alterada de acordo. O atributo %TYPE é particularmente útil ao declarar variáveis para manter os valores do banco de dados. A sintaxe para declarar uma variável do mesmo tipo que uma coluna é: variable_name table_name.column_name%TYPE; Vamos por em prática esse exemplo: Observem no bloco PL/SQL abaixo a variável nome e sobre estão definidas com o mesmo tipo das colunas da tabela employees. Figura 6 Resultado: Figura 7 Agora que já sabemos para que serve e como utilizar uma variável, já podemos então começar a falar de estrutura de decisão. A instrução IF-THEN-ELSE é utilizada para que se possa oferecer condições para que o sistema execute o código quando a condição for verdadeira (TRUE) ou quando a condição verificada seja falsa (FALSE). Existem algumas formas de utilizarmos o comando de declaração IF-THEN-ELSE. 9 UNIDADE Programando em SQL I Sintaxe (IF-THEN) IF condition THEN {...statements to execute when condition is TRUE...} END IF; A sintaxe IF-THEN é utilizada apenas quando queremos que o programa execute a condição quando for VERDADEIRA. Sintaxe (IF-THEN-ELSE) IF condition THEN {...statements to execute when condition is TRUE...} ELSE {...statements to execute when condition is FALSE...} END IF; A sintaxe IF-THEN-ELSE é utilizada quando temos instruções diferentes a serem executadas quando a condição for VERDADEIRA das instruções quando a condição for FALSA. Sintaxe (IF-THEN-ELSIF) IF condition1 THEN {...statements to execute when condition1 is TRUE...} ELSIF condition2 THEN {...statements to execute when condition2 is TRUE...} END IF; A sintaxe IF-THEN-ELSIF é utilizada quando você tem mais de 2 condições para testar e para cada condição de instruções diferentes. Sintaxe (IF-THEN-ELSIF-ELSE) IF condition1 THEN {...statements to execute when condition1 is TRUE...} ELSIF condition2 THEN {...statements to execute when condition2 is TRUE...} ELSE {...statements to execute when both condition1 and condition2 are FALSE...} END IF; 10 11 A sintaxe IF-THEN-ELSIF-ELSE é utilizada quando você tem mais de 2 condições para testar e para cada condição de instruções diferentes. Caso as condições forem FALSAS, o programa irá executar as instruções que foram inseridas na opção ELSE. Quando uma condição for (VERDADEIRO) TRUE, a instrução IF-THEN-ELSE somente execu- tará a instrução correspondente e não será avaliada as demais instruções. Se nenhuma condição for atendida, a parte ELSE da instrução IF-THEN-ELSE será executa- da. É importante notar que as opções ELSIF e ELSE não são obrigatórias. Após essas explicações da sintaxe, você deve estar se perguntando: “Como posso aplicar essa estrutura de decisão em um caso real?” Para demonstrar a utilização, vamos utilizar a estrutura da base de dados HR. Vamos imaginar a seguinte situação, se você pesquisar a tabela EMPLOYEES, irá ver que existe uma coluna chamada SALARY, se o valor nessa coluna for menor do 3000, devemos exibir a informação‘REAJUSTAR EM 25%’, se o valor for maior ou igual a 3000 e menor do que 7000, exibir ‘REAJUSTAR EM 10%’ e para qualquer outro valor exibir ‘REAJUSTAM EM 3%’. Para resolver esse problema, iremos mon- tar um bloco PL/SQL com estrutura de decisão. Figura 8 Resultado: Figura 9 No exemplo acima vimos o funcionamento da estrutura de repetição, que aten- de somente quando a variável tiver apenas um único valor, e se caso o retorno do SELECT retornasse uma quantidade maior de registros, como poderíamos resolver esse desafio? A resposta vem logo abaixo, utilizando a estrutura de repetição. 11 UNIDADE Programando em SQL I Estrutura de Repetição WHILE LOOP A estrutura de repetição WHILE LOOP é utilizando quando o programador não tem ao certo a quantidade de vezes que as instruções precisam ser executadas. Sintaxe: WHILE condition LOOP {...statements...} END LOOP; Parâmetros ou Argumentos • condition Avaliar cada passagem pelo loop. Se a condição for avaliada como VERDADEI- RA, a instrução da estrutura de repetição é executada. Se a condição for avaliada em FALSO, a estrutura de repetição é finalizada. • statements Instruções a serem executadas. Como a instrução WHILE é avaliada antes de executar a estrutura de repetição, é possível que a instrução nunca seja executada caso o resultado da condição seja FALSO. Exemplo: WHILE monthly_value <= 4000 LOOP monthly_value:= daily_value * 31; END LOOP; Neste exemplo WHILE LOOP, a repetição se encerraria quando o valor mensal excedesse 4000 conforme especificado na condição abaixo: WHILE monthly_value <= 4000 O WHILE LOOP continuará enquanto o valor mensal <= 4000. E uma vez que o valor mensal é> 4000, o ciclo terminará. 12 13 Instrução de Repetição FOR LOOP A instrução FOR LOOP permite que você execute as instruções por uma quantidade de vezes definida. Sintaxe: FOR loop_counter IN [REVERSE] lowest_number..highest_number LOOP {...statements...} END LOOP; Parâmetros ou Argumentos • loop_counter Variável controladora da repetição. • [REVERSE] Opcional. Se especificado, o contador de loop contará no sentido inverso. • lowest_number O valor inicial para loop_counter. • highest_number O valor final para loop_counter. • statements Instruções a serem executadas. A instrução FOR LOOP é utilizada quando queremos executar as instruções e o número de vezes que será definido na estrutura. Se precisarmos inverter a contagem, ou seja, se o REVERSE for especificado, então o número mais alto será o valor inicial para loop_counter e o valor mais baixo será o valor final para loop_counter. Exemplo: FOR Lcntr IN 1..20 LOOP LCalc := Lcntr * 31; END LOOP; Este exemplo acima, o FOR LOOP será executado 20 vezes. O contador chamado Lcntr começará em 1 e terminará em 20. 13 UNIDADE Programando em SQL I Você pode usar o modificador REVERSE para executar o FOR LOOP na or- dem inversa. Exemplo: FOR Lcntr IN REVERSE 1..15 LOOP LCalc := Lcntr * 31; END LOOP; Este exemplo FOR LOOP será loop 15 vezes. No entanto, como REVERSE é es- pecificado, o contador chamado Lcntr começará em 15 e terminará em 1, ou seja, um loop do final para o início. Instrução Exit A instrução EXIT é mais usada para encerrar instruções LOOP, ou seja, mesmo que o LOOP não chegou ao fim, é possível interromper o processo e sair da estru- tura de repetição. Sintaxe: EXIT [WHEN boolean_condition]; Parâmetros ou Argumentos • Boolean_condition Opcional. É a condição para terminar o LOOP. Exemplo: LOOP montante := valor * 31; EXIT WHEN montante > 4000; END LOOP; Vamos ver na prática então como funciona os elementos de estrutura de repetição. Observe nos exemplos abaixo que mesmo utilizando comando de repetição diferente podemos obter o mesmo resultado. Na estrutura que utiliza o WHILE, precisamos obrigatoriamente definir a variável que será a responsável por controlar a repetição e não podemos esquecer de incre- mentá-la para que não fique em um loop infinito. 14 15 Figura 10 Resultado: Figura 11 Já no exemplo utilizando a estrutura FOR, a variável de controle não precisa ser criada na declaração, a variável já estará criada implicitamente nessa estrutura. Figura 12 Resultado: Figura 13 15 UNIDADE Programando em SQL I Figura 14 Resultado: Figura 15 Deve ter ficado uma pergunta na mente de vocês, para que eu vi os tipos de estru- turas de repetição? A resposta é: “Porque nós precisaremos verificar várias informa- ções que estarão em memória!”. Como assim? Vamos lá, quando a nossa instrução de seleção retornar mais de um registro e precisarmos analisar todos os registros, estes serão colocados em memória. Para percorremos essas informações na memória, precisamos saber como funciona a estrutura de repetição, pois bem, a esse conjunto de registro que ficou armazenado em memória iremos alocá-los em cursores. Cursores Os cursores são recursos empregados na manipulação de informações. Por meio dos cursores podemos utilizar instruções SELECT, como também INSERÇÃO, AL- TERAÇÃO e EXCLUSÃO. Nos exemplos a seguir iremos utilizar a tabela chamada employees que pertence a base de dados HR. No momento da instalação do Oracle 11g, essa tabela foi criada e inserido valores nela, qualquer dúvida, consulte o material das unidades anteriores para a instalação e configuração. 16 17 Os cursores são utilizados para que se possa armazenar os valores de retorno da instrução SELECT, permitindo que acessemos o valor que está em memória. Vamos ao passo a passo para a criação do cursor e suas funcionalidades. No exemplo a seguir, a instrução SELECT armazena o retorno da instrução em uma variável de nome cur1. CURSOR cur1 IS SELECT first_name FROM employees; Para acessarmos os valores que foram armazenados no cursor, ou seja, colocados em memória, devemos abrir o cursor. OPEN cur1; A finalidade de usar um cursor, na maioria dos casos, é recuperar as linhas do cursor para que algum tipo de operação possa ser executado. Depois de declarar e abrir o cursor, a instrução FETCH irá buscar as linhas do cursor que foi aberto. Sintaxe: FETCH cursor_name INTO variable_list; Parâmetros ou Argumentos • cursor_name O nome do cursor que você deseja buscar linhas. • variable_list A lista de variáveis, delimitada por vírgula, que você deseja armazenar o conjunto de resultados do cursor. No exemplo a seguir, o valor que foi armazenado na memória está sendo repassado para a variável cnumber. FETCH cur1 into cnumber; Podemos utilizar uma lista de variáveis para recuperamos informações dos cursores, para isso, a quantidade de campos do SELECT deve ser a mesma quantidade de variáveis que se deve indicar depois da instrução INTO. Inclusive os tipos dos campos e os tipos das variáveis devem ser iguais. 17 UNIDADE Programando em SQL I Como estamos trabalhando com cursores e estes deixam valores em memória, é importante removermos os valores da memória fechando o cursor. CLOSE c1; Quando trabalhamos com cursores, é importante determinarmos o status dele. O cursor pode ter um status de Aberto/Fechado, por exemplo. Veja as opções de possíveis status do cursor. %ISOPEN • Retorna TRUE se o cursor estiver aberto, FALSE se o cursor estiver fechado. %FOUND ou %NOTFOUND • Retorna INVALID_CURSOR se o cursor for declarado, mas não aberto; ou se o cursor foi fechado; • Retorna NULL se o cursor estiver aberto, mas a busca não foi executada; • Retorna TRUE se uma busca foi executada; • Retorna FALSE se nenhuma linha foi retornada. %ROWCOUNT • Retorna INVALID_CURSOR se o cursor for declarado, mas não aberto; ou se o cursor foi fechado. • Retorna o número de linhas buscadas. • O atributo ROWCOUNT não fornece a contagem real de linhas até que você tenha percorrido todo o cursor. Em outras palavras, você não deve confiar nesse atributo para informar quantas linhas estão em um cursor depois de aberto. O principal uso do DBMS_OUTPUT é na depuração de blocos PL/SQL, sua procedure prin- cipal
Compartilhar