Baixe PLSQL banco de dados e outras Resumos em PDF para Linguagem Lógica em Banco de Dados, somente na Docsity!
Banco de Dados – Introdução ao Oracle: SQL e PL/SQL Nome: Introdução ao Oracle: SQL e PL/SQL Volume 1 Professor: Ricardo Soares da SIlva DEFINIÇÃO DE BANCO DE DADOS RELACIONAL Um banco de dados relacional é um conjunto de relações ou tabelas bidimensionais para armazenar informações. Por exemplo, informações sobre todos os funcionários de uma empresa. Em um banco de dados relacional, você cria várias tabelas para armazenar informações diferentes sobre funcionários, como tabelas de funcionários, departamentos e salários. MODELO DE RELACIONAMENTO DE ENTIDADES Em um sistema eficiente, os dados são divididos em categorias ou entidades distintas. Um modelo de relacionamento de entidades (ER) é uma ilustração de várias entidades em uma empresa e dos relacionamentos entre elas. Um modelo de relacionamento de entidades é derivado de narrativas ou especificações comerciais e é criado durante a fase de análise do ciclo de vida de desenvolvimento do sistema. Os modelos para relacionamento de entidades separam as informações necessárias para uma empresa das atividades desempenhadas dentro dela. Embora as empresas possam alterar suas atividades, o tipo de informações tende a permanecer constante. Portanto, as estruturas de dados também tendem a ser constantes. Benefícios do Modelo de Relacionamento de Entidades. Documenta as informações da organização em formato claro e preciso. Fornece uma imagem clara do escopo das necessidades de informações. Fornece um mapa ilustrado facilmente compreendido para o desingn do banco de dados. Oferece uma estrutura eficiente para a integração de várias aplicações. Componentes-chave Entidade: Um item importante sobre o qual é necessário obter informações. Os exemplos são departamentos, funcionários e pedidos. Atributo: Um item que descreve ou qualifica uma entidade. Por exemplo, para a entidade de funcionários, os atributos são o número, o nome e o cargo do funcionário, além do número do departamento e assim por diante. Cada um desses atributos é necessário ou opcional. Esse estado é chamado de opcionalidade. Relacionamento: Uma associação nomeada entre entidades que demonstra opcionalidade e grau. Os exemplos são funcionários e departamentos, além de pedidos e itens.
ATRIBUTO IDENTIFICADOR
Chave Primária:
- É aquele atributo que identifica de forma única cada ocorrência na entidade – linha preenchida na tabela. - Exemplo: Empregado Chave primária (num-matricula) Atributos (nom-empregado, dat-nascimento, dat- admissão, sal-bruto) Chave concatenada ou composta:
- Quando o identificador é formado por mais de um atributo na formação da chave primária. - Exemplo: Dependente Chave primária (cod-dependente, empregado. num-matricula) Atributos (nom-empregado, dat-nascimento) Chave Estrangeira:
- É um atributo importado de outra entidade, para implementar logicamente o relacionamento 1:N. - Exemplo: Departamento Chave primária (cod-depto) Atributos (nom-depto, sgl-depto) Laboratório Chave-primária (cod-laboratório) Atributos (nom-laboratório) Chave estrangeira (departamento.cod-depto)
Instruções SQL SELECT Recuperação de Dados INSERT UPDATE DML (Data Manipulation Language) DELETE CREATE ALTER DROP DDL (Data Definition Language) RENAME TRUNCATE COMMIT ROLLBACK Controle de transação SAVEPOINT GRANT REVOKE DCL (Data Control Language) SELECT Recupera dados do Banco de Dados. INSERT UPDATE DELETE Inclui novas linhas, alteras linhas (informações) existentes e remove linhas indesejáveis no Banco de dados, respectivamente. CREATE ALTER DROP RENAME TRUNCATE Cria novos objetos (usuários, tabelas, views, etc), altera objetos, mata objetos, renomeia tabelas e trunca as tabelas, respectivamente no Banco de Dados. COMMIT ROLLBACK SAVEPOINT Gerencia as alterações feitas por instruções DML, confirmando ou cancelando. É possível agrupar as alterações dos dados em transações lógicas. GRANT REVOKE Fornece ou remove direitos de acesso ao banco de Dados e estrutura contidas à ele para novos usuários de Banco. Sobre PL/SQL O PL/SQL (Procedural Language/SQL) é uma extensão de linguagem procedural da Oracle Corporation para SQL, a linguagem de acesso a dados padrão para bancos de dados relacionais. O PL/SQL oferece recursos de engenharia de software modernos, como, por exemplo, a encapsulação de dados, o tratamento de exceções, a ocultação de informações, a orientação de objeto e assim por diante, trazendo os recursos de programação mais modernos para o Oracle Server.
Tabelas que serão utilizadas neste Curso: EMP -> Traz informações sobre os Empregados. DEPT -> Traz informações sobre todos os Departamentos. SALGRADE -> Traz informações sobre salários de várias classes. CRIANDO INSTRUÇÕES SQL BÁSICAS Instrução SELECT básica SELECT [ DISTINCT ] { *, coluna [ apelido ],...} FROM tabela. Da forma mais simples, uma instrução SELECT deve incluir o seguinte:
- SELECT especifica as colunas as serem exibidas.
- FROM especifica a tabela que contém as colunas listadas na cláusula SELECT. Na Sintaxe: SELECT é uma lista de uma ou mais colunas DISTINCT suprime os itens duplicados
- seleciona todas as colunas coluna seleciona uma ou mais colunas nomeadas (específicas) apelido fornece cabeçalhos diferentes às colunas selecionadas FROM tabela especifica a tabela contendo as colunas Criando e executando instruções SQL Instruções SQL não fazem distinção entre maiúsculas e minúsculas. Podem ser digitadas em uma ou mais linhas. Geralmente separadas para melhor visualização. Não podem ser divididas as palavras. Dentro do SQL*Plus, uma instrução SQL é digitada no prompt SQL e as linhas subsequentes são numeradas, isso chama-se buffer de SQL. Coloque um ponto-e-vírgula ( ; ) no final da última cláusula. Selecionando todas as colunas da tabela dept: SQL> Select * 2 from dept; Selecionando colunas específicas da tabela dept: Pode-se selecionar mais de um campo da tabela separando por vírgula. SQL> Select deptno, loc 2 from dept; Expressões aritméticas: Criar expressões com dados NUMBER e DATE usando operadores aritméticos.
Usando apelidos nas colunas Renomeia um cabeçalho de coluna. É útil para cálculos. A cláusula AS é opcional. Segue imediatamente o nome da coluna. Necessita de aspas duplas caso contenha espaços ou caracteres especiais ou faça distinção entre maiúsculas e minúsculas. Select ename “Nome”, sal * 12 as “Salario Anual” From emp; Nome Salario Anual
Operador de concatenação Concatena colunas ou strings de caractere a outras colunas É representado por duas barras verticais ( || ) - pipe. Cria uma coluna resultante que é uma expressão de caracteres. Select ename || job as “Empregados” From emp; Empregados
KINGPRESIDENT BLAKEMANAGER ... Strings Literais de caracteres: Uma literal é um caracter, um número ou uma data incluída na lista SELECT. Os valores literais de caractere e data devem estar entre aspas simples. Cada string de caractere é gerada uma vez para cada linha retornada. Select ename || ‘ é o ‘ || job As “Detalhes de empregados” From emp; Detalhes de empregados
KING é o PRESIDENT BLAKE é o MANAGER ... select ename || ’: ‘ || ‘1’ || ‘Salario Mensal = ‘ || sal As “Mensal” from emp; Mensal
KING : 1 Salario Mensal = 5000 BLAKE : 1 Salario Mensal = 2850 ... Eliminando linhas duplicadas Por default a exibição das consultas é de todas as linhas, inclusive as duplicadas. Para eliminá-las, deveremos utilizar a cláusula DISTINCT. Select deptno From emp; DEPTNO
10 30 10 20 ... select distinct deptno from emp; DEPTNO
10 20 30 No exemplo mostrado, a tabela EMP contém na verdade, quatorze linhas, mas há somente três números de departamento exclusivos na tabela. Você pode especificar várias colunas após o qualificador DISTINCT, o mesmo afeta todas as colunas selecionadas e o resultado representa uma combinação distinta das colunas. select distinct deptno, job from emp; DEPTNO JOB
10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST ... SQL * PLUS É o ambiente (ferramenta Oracle) onde estaremos manipulando todos os comandos SQL e PL/SQL.
DEL - DEL n - DEL m n Deleta a linha atual. Deleta a linha n. Deleta linhas de m a n. I[NPUT] Insere um número indefinido de linhas I[NPUT] texto Insere uma linha consistindo em texto L[IST] Lista todas as linhas no buffer de SQL L[IST] n Lista uma linha (especificada pelo n ) L[IST] m n Lista uma faixa de linha ( de m a n ) R[UN] Exibe e executa a instrução SQL atual no buffer. N Especifica a linha que deve se tornar a linha atual N texto Substitui a linha n pelo texto 0 texto Insere uma linha antes da linha 1. Comandos de Edição do SQLPlus Comando Descrição* SAV[E] nome de arquivo [.ext] [REP[LACE]APP[END]] Salva o conteúdo atual do buffer de SQL para um arquivo. Use APPEND para adicionar um arquivo existente; use REPLACE para substituir um arquivo existente. A extensão é .sql. GET nome do arquivo [.ext] Salva o conteúdo de um arquivo salvo anteriormente para o buffer de SQL. A extensão default para o nome de arquivo é .sql. STA[RT] nome do arquivo [.ext] Executa um arquivo de comando salvo anteriormente. @ nome de arquivo Executa um arquivo de comando salvo anteriormente (o mesmo que START) ED[IT] Chama o editor e salva o conteúdo do buffer para um arquivo chamado afiedt.buf. ED[IT] nome do arquivo [.ext] Chama o editor para editar o conteúdo de um arquivo salvo. SPO[OL] [ nome do arquivo ext]| OFF|OUT] Armazena os resultados da consulta em um arquivo. OFF fecha o arquivo periférico. OUT fecha o arquivo periférico e envia os resultados do arquivo para a impressora do sistema. EXIT Sai do código SQL*Plus. RESTRIGINDO E CLASSIFICANDO DADOS Limitando linhas selecionadas (Cláusula WHERE)
SELECT [ DISTINCT ] { *, coluna [ apelido ],...} FROM tabela [ WHERE condição(ões)]; A clausula WHERE é formada por três elementos: Nome da coluna Operadores de comparação Nome da coluna, constante ou lista de valores. Usando a cláusula WHERE No select abaixo será recuperado o nome, cargo e nr. Do departamento de todos os funcionários cujo cargo é CLERK Select ename, job, deptno From emp Where job=’CLERK’; ENAME JOB DEPTNO
JAMES CLERK 30 SMITH CLERK 20 ADAMS CLERK 20 MILLER CLERK 10 Strings de Caractere e Datas As strings de caractere e valores de data aparecem entre aspas simples. Os valores de caractere fazem distinção entre maiúsculas i minúsculas e os valores de data diferenciam formatos. O formato de data default do Oracle é DD-MON-YY Operadores de Comparação Operador Significado = Igual a
Maior que = Maior ou igual a < Menor que <= Menor ou igual a <> Diferente de Exemplos de utilização: ... Where data_nac = ‘01-JAN-95’ ... Where sal > = 1500
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788
Usando o operador LIKE Usado para executar pesquisas curinga de valores de string de pesquisa válidos. As condições de pesquisa podem conter caracteres literais ou números. % denota zero ou muitos caracteres. _ denota um caractere. Select ename From emp Where ename LIKE ‘S%’ Esta instrução traz nome de todos os empregados cujo o nome comece com a letra S, não importando o restante. Select ename From emp Where ename LIKE ‘_A%’ ENAME
MARTIN JAMES WARD Esta instrução traz todos os nomes quando a letra A estiver na posição de 2º caracter, não importando o que vier pela frente. Usando o operador IS NULL Usado para trazer informações quando algum valor contiver nulo. Select ename, mgr From emp Where mgr is null; ENAME MGR
KING A consulta acima retornou o único empregado que não tem Gerente. Operadores lógicos Operador Significado AND Traz o resultado, quando a pesquisa obedecerem as duas condições. OR Traz o resultado, quando a pesquisa obedecer uma duas condições.
NOT Retorna quando a condição estiver em negação. Usando o operador AND: Select empno, ename, job, sal From emp Where sal >= 1100 AND job=’CLERK’; EMPNO ENAME JOB SAL
7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300 Usando o operador OR: Select empno, ename, job, sal From emp Where sal >= 1100 OR job=’CLERK’; EMPNO ENAME JOB SAL
7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7876 ADAMS CLERK 1100 ... 7934 MILLER CLERK 1300 ... Usando o operador NOT: Select ename, job From emp Where job NOT IN (’CLERK’, ‘MANAGER’,’ANALYST’); ENAME JOB
KING PRESIDENT
7900 JAMES 11400
7876 ADAMS 13200
Classificando por várias colunas Você pode classificar os resultados das consultas por mais de uma coluna. O limite de classificação é o número de colunas de uma determinada tabela. Na cláusula ORDER BY, especifique as colunas e separe seus nomes usando vírgulas. Se deseja inverter a ordem de uma coluna, especifique DESC após seu nome. É possível ordenas por colunas que não estão incluídas na cláusula SELECT. select ename, deptno, sal from emp ORDER BY deptno, sal DESC; ENAME DEPTNO SAL
KING 10 5000 CLARK 10 2450 MILLER 10 1300 FORD 20 3000 ... FUNÇÕES SQL As funções são um recurso avançado de SQL e podem ser usados para realizar o seguinte: Executar cálculos usando dados. Modificar itens de dados individuais. Manipular saída para grupos de linhas. Formatar datas e números para exibição. Converter tipos de dados de coluna. As funções SQL podem aceitar argumentos e sempre retorna um valor. OBS.: A maioria das funções descritas nesta lição são específicas para a versão SQL da Oracle.
Funções de Caracter (Conversão de Maiúsculas e Minúsculas) Função Objetivo LOWER Converte valores de caractere alfabético para letras minúsculas. UPPER Converte valores de caractere alfabético para letras maiúsculas. INITCAP Converte valores de caractere alfabético para letras maiúsculas na primeira letra de cada palavra e todas as outras letras em minúsculas. Funções de Caracter (Manipulação de caractere) Função Objetivo CONCAT Concatena o primeiro valor do caractere ao segundo valor do caractere, equivalente ao operador de concatenação ( || ). SUBSTR ( coluna,m,n) Retorna caracteres específicos a partir do valor de caractere começando na posição m, até n caracteres depois. LENGHT Retorna o número de caracteres do valor INSTR Retorna a posição numérica do caractere nomeado. LPAD / RPAD ( coluna,n,string) Preenche o valor de caractere com a string, justificando à (esquerda ou direita), preenchendo o tamanho de n posições faltantes da qtde da coluna. Funções LOWER, UPPER e INITCAP Função Resultado LOWER (‘SQL Curso’) UPPER (‘SQL Curso’) INITCAP (‘SQL Curso’) sql curso SQL CURSO Sql Curso Funções de Manipulação de Caractere Função Resultado CONCAT (‘Good’, ‘String’) SUBSTR (‘String’, 1, 3) LENGTH (‘String’) INSTR (‘String’, ‘r’) LPAD (sal, 10, ‘*’) TRIM (‘S’, FROM ‘SSMITH’) GoodString Str 6 3
MITH Funções numéricas Função Objetivo ROUND( coluna, n) Arredonda a coluna, expressão ou valor para n casas decimais ou se n for omitido, nenhuma casa decimal (Se n for negativo, os números à esquerda do ponto decimal serão arredondados.) TRUNC (coluna, n) Trunca a coluna, expressão ou valor para n casas decimais ou se n for omitido, nenhuma casa decimal (Se n for negativo, os números à esquerda do ponto decimal serão truncados para zero.)
Data - número DATA Subtrai um número de dias de uma data. Data - data Número de dias Subtrai uma data de outra. Data + número/24 DATA Adiciona um número de horas para uma data. Usando operadores aritméticos com datas select ename, (sysdate – hiredate) / 7 WEEKS from emp where deptno = 10; ENAME WEEKS
KING 830. CLARK 859. MILLER 821. ... O exemplo exibe o nome e o número de semanas empregadas por todos os funcionários do departamento 10. Ele subtrai a data atual (SYSDATE) a partir da data na qual o funcionário foi admitido e divide o resultado por 7 a fim de calcular o número de semanas que o trabalhador está empregado. Funções de Data Função Descrição MONTHS_BETWEEN (data1, data2) Localiza o nr. de meses entre a data1 e a data. ADD_MONTHS (data, n) Adiciona um número n de meses de calendário à data. O valor de n pode ser inteiro ou pode ser negativo. NEXT_DAY( data,’char’) Localiza a data do próximo dias especificado da data seguinte da semana (‘char’). O valor de char pode ser um número representando um dia ou uma string de caractere. LAST_DAY( data) Localiza a data do último dia do mês que contém a data. Usando as funções: MONTHS_BETWEEN (‘01-SEP-95’, ‘11-JAN-94) 19. ADD_MONTHS (‘11-JAN-94’, 6) 11-JUL- NEXT_DAY (‘01-SEP-95’, ‘FRIDAY’)
NUMBER CARACTER DATE
TO_NUMBER TO_DATE
TO_CHAR TO_CHAR
08-SEP-95 /* Próx. Sexta-feira da semana/ LAST_DAY (‘01-SEP-95’) 30-SEP-95 / Último dia do mês em questão */ Funções de Conversão: Existem funções de conversão que são feitas IMPLÍCITAMENTE e conversões que são feitas EXPLICITAMENTE. Conversão IMPLÍCITA de tipo de dados: De Para VARCHAR2 ou CHAR NUMBER VARCHAR2 ou CHAR DATE NUMBER VARCHAR DATE VARCHAR Embora a conversão implícita de tipo de dados esteja disponível, é recomendável que você realize a conversão explícita de tipo de dados a fim de garantir a confiabilidade das instruções SQL. Conversão EXPLÍCITA de tipo de dados: Função Objetivo TO_CHAR (número/data, [fmt]) Converte números ou datas para tipo de dados VARCHAR2. TO_NUMBER (caractere, [fmt]) Converte um string de caractere contendo dígitos para um número no formato especificado pelo modelo de formato opcional fmt. TO_DATE (caractere, [fmt]) Converte uma string de caractere representando uma data para um valor de data de acordo com o fmt especificado. Se o fmt for omitido, o formato é DD-MON-YY. Função TO_CHAR com Datas.
