Baixe Comandos Sql em Oracle e outras Notas de estudo em PDF para Automação, somente na Docsity!
APOSTILA DE SQL
O objetivo da Apostila é trazer os principais comandos SQL usados em aplicações ORACLE, com exemplos, comentários e explicações.
FINALIDADE DO CAPITULO:
No final deste capitulo você será capaz de criar scripts permitindo que o usuário entre com valores diversos.
A finalidade dos exemplos abaixo é demostrar como permitir que o usuário entre com os valores que possibilitem a execução de um comando SQL , isso tudo em tempo de execução. Verifique o uso do "&" que permite que o usuário entre com dados. No exemplo abaixo temos um pedido de entrada de um numero, por isso é que o &numero_do_dept não está entre aspas. Podemos notar também que aparece o old valor e o new valor, isso é devido ao cmdo set verify on , se quisermos que não apareça, devemos usar o set verify off.
Vamos ao Exemplo:
SQL> set verify on SQL> select id,last_name,salary 2 from s_emp 3 where dept_id=&numero_do_dept; Enter value for numero_do_dept: 31 old 3: where dept_id=&numero_do_dept new 3: where dept_id=
ID LAST_NAME SALARY
3 Nagayama 1400 11 Magee 1400
Agora não aparecerá o new e old valor porque estamos usando o SET VERIFY OFF.
SQL> set verify off SQL> select id,last_name,salary 2 from s_emp 3 where dept_id=&numero_do_dept; Enter value for numero_do_dept: 31
ID LAST_NAME SALARY
3 Nagayama 1400 11 Magee 1400
Vimos anteriormente como que temos que proceder para que o usuário entre com valores numéricos, para que seja realizada uma pesquisa, agora vamos possibilitar que o usuário ente com valores do tipo caracter, a principal diferença é o uso das aspas, mas temos que ter atenção para o formato na hora de digitarmos, porque tem que ser o mesmo do conteúdo que está na tabela, com maiúsculas e minúsculas.
Vamos ao Exemplo:
SQL> select id,last_name,salary 2 from s_emp 3 where title ='&job_title'; { possibilitando que o usuário entre com o nome do title } Enter value for job_title: Stock Clerk
ID LAST_NAME SALARY
16 Maduro 1400 17 Smith 940 18 Nozaki 1200 19 Patel 795 20 Newman 750 21 Markarian 850 22 Chang 800 23 Patel 795 24 Dancs 860 25 Schwartz 1100
10 rows selected.
6 rows selected.
CONSTRUINDO SCRIPTS USANDO OPÇÕES PARA O USUÁRIO ENTRE COM
DADOS E TAMBÉM PARA SÓ MOSTRAR NA TELA O PROMPT.
No exemplo abaixo estamos usando os seguintes comandos que na da mais é do que a soma de tudo aquilo que vimos neste capitulo mas também algo mais: O cmdo SET ECHO OFF serve para em tempo de execução não se exiba os comandos do SQL e SET ECHO ON serve para retornar a forma anterior. O cmdo ACCEPT serve para que preparemos um PROMPT para receber um valor. Neste exemplo também temos a criação de uma variável chamada V_NAME que recebe valores. Estamos preparando o ambiente para receber valores que serão armazenados dentro de uma variável, para após isto, serem feitas comparações dentro da cláusula WHERE. Estamos usando duas tabelas S_DEPT e S_REGION , o AND é uma função onde complementa a cláusula WHERE , e o UPPER no DPT.NAME está passando o conteúdo do nome do dept para maiúsculo para que seja efetuada a comparação com um nome que será digitado pelo usuário, que por sua vez recebe um UPPER que o transforma em maiúsculo. Essa alternativa é feita porque não sabemos qual é o formato do dado na tabela.
Vamos ao exemplo:
SET ECHO OFF ACCEPT V_NAME PROMPT 'DÊ O NOME DO DEPARTAMENTO:' SELECT DPT.NAME, REG.ID, REG.NAME " NOME DA REGIÃO" FROM S_DEPT DPT, S_REGION REG {veja a criação de apelidos} WHERE DPT.REGION_ID = REG.ID {veja o join} AND UPPER(DPT.NAME) LIKE UPPER('%&V_NAME%') {valor digitado } / SET ECHO ON
Como estamos gerando um SCRIPT, os comandos devem ficar armazenados dentro de um arquivo que possua a extensão SQL e preparado da forma descrita acima quando estivermos no SQL e desejarmos executar o nosso SCRIPT temos que seguir o
procedimento descrito abaixo, usando "@" e o nome do arquivo, ou " START" e o nome do arquivo.
Vamos ao exemplo:
SET ECHO ON SQL> @TEST.SQL SQL> SET ECHO OFF DÊ O NOME DO DEPARTAMENTO:sales old 4: AND UPPER(DPT.NAME) LIKE UPPER('%&V_NAME%') new 4: AND UPPER(DPT.NAME) LIKE UPPER('%sales%')
NAME ID NOME DA REGIÃO
Sales 1 North America Sales 2 South America Sales 3 África / Middle East Sales 4 Ásia Sales 5 Europe
Podemos notar que por ocasião da execução o exemplo anterior mostrou o OLD e o NEW valores da variável, para não mostrar temos que usar o SET VERIFY OFF veja abaixo:
Vamos ao exemplo:
SET VERIFY OFF SET ECHO OFF ACCEPT V_NAME PROMPT 'DÊ O NOME DO DEPARTAMENTO:' SELECT DPT.NAME, REG.ID, REG.NAME " NOME DA REGIÃO" FROM S_DEPT DPT, S_REGION REG WHERE DPT.REGION_ID = REG.ID AND UPPER(DPT.NAME) LIKE UPPER('%&V_NAME%') / SET ECHO ON
Executando o SCRIPT:
SQL> START TEST.SQL SQL> SET VERIFY OFF SQL> SET ECHO OFF DÊ O NOME DO DEPARTAMENTO:SALES
ACCEPT MENOR_DT DATE FORMAT 'MM/DD/YY' -
PROMPT 'ENTRE MENOR DATA (MM/DD/AA) :'
ACCEPT MAIOR_DT DATE FORMAT 'MM/DD/YY' -
PROMPT 'ENTRE MAIOR DATA (MM/DD/AA) :'
COLUMN EMPREGADO FORMAT A
SELECT USERID,FIRST_NAME||' '||LAST_NAME "EMPREGADO",
START_DATE FROM S_EMP
WHERE START_DATE BETWEEN TO_DATE('&MENOR_DT','MM/DD/YY')
AND TO_DATE('&MAIOR_DT','MM/DD/YY')
UNDEFINE MENOR_DT
UNDEFINE MAIOR_DT
COLUMN EMPREGADO CLEAR
SET ECHO ON
SET VERIFY ON
OUTRO EXEMPLO DE SCRIPT:
Estamos montando um script com a finalidade de receber um valor que pesquise registros, no caso estamos querendo pesquisar o ID e o NOME de um CUSTOMER e para isso recebendo um dos nomes do CUSTOMER, por isso estamos usando o LIKE e "%&NOME%" estamos transformando a coluna NAME da tabela a ser pesquisada em maiúsculo para que qualquer nome que seja digitado em maiúsculo seja pesquisado. Está sendo criado a variável NOME pelo ACCEPT e o PROMPT possibilita a recepção de um valor, observe a sintaxe "-" após a variável NOME.
Vamos ao exemplo:
SET ECHO OFF SET VERIFY OFF ACCEPT NOME - PROMPT ' ENTRE COM O NOME DESEJADO :' SELECT ID, NAME FROM S_CUSTOMER WHERE UPPER(NAME) LIKE '%&NOME%' / SET ECHO ON SET VERIFY ON
FINAL DE CAPITULO
FINALIDADE DO CAPITULO:
O COMANDO ALTER TABLE E SUAS VARIAÇÕES:
Adicionado uma coluna em uma tabela:
No exemplo a seguir estamos usando o cmdo ALTER TABLE para adicionar uma coluna em uma tabela, a coluna adicionada a tabela sempre será a última, importante saber que não podemos deletar uma coluna de uma tabela mas somente adicionar, se precisarmos dropar uma coluna a solução é dropar a tabela e recriá – la sem a coluna. Vamos ao exemplo:
SQL> ALTER TABLE S_REGION ADD (comments VARCHAR(255));
Table altered.
O COMD MODIFY:
No exemplo abaixo estamos modificando uma coluna usando o comando modify, no caso estamos aumentando o tamanho da coluna title para 50 , é permitido aumentar o tamanho da coluna mas diminuir não é permitido.
SQL> ALTER TABLE s_emp 2 MODIFY (title VARCHAR(50));
Table altered.
ADCIONANDO UMA CONSTRAINT:
Para adicionarmos uma constraint temos que usar O ALTER TABLE criar um nome para nossa constraint no caso 's_emp_maneger_id_fk', escrevendo de onde ela é colocando a referência com o id de s_emp.
Vamos ao exemplo:
a UK ou FK.
Vamos ao exemplo SQL> ALTER TABLE s_emp 2 ENABLE CONSTRAINT s_emp_id_pk;
Table altered.
FINAL DE CAPITULO
FINALIDADE DO CAPITULO:
O COMANDO SELECT TO_CHAR:
O exemplo abaixo temos um select que está selecionando id,date_ordered da tabela s_ord onde o sales_rep_id é igual a 11, podemos notar que a date_ordered(data) é igual ao formato de data padrão do oracle, ou seja, mês por extenso abreviado e o ano com os dois últimos números do ano.
Vamos ao exemplo:
SQL> SELECT ID,DATE_ORDERED 2 FROM S_ORD 3 WHERE 4 SALES_REP_ID = 11;
ID DATE_ORDE
100 31-AUG- 105 04-SEP- 109 08-SEP- 110 09-SEP- 111 09-SEP-
No exemplo abaixo estamos com o comando " to_char " mudando o formato da data para o modelo que nós estamos querendo mostrar em tela, ou seja, estamos mudando o formato padrão do oracle para um novo formato correspondente ao que queremos, no caso 'mm/yy' que corresponde ao mês e o ano.
Vamos ao exemplo:
SQL> SELECT ID,TO_CHAR(DATE_ORDERED,'MM/YY') ORDERED 2 FROM S_ORD 3 WHERE SALES_REP_ID = 11;
ID ORDERED
100 08/ 105 09/ 109 09/ 110 09/ 111 09/
ID ORDERED
100 MON/AUGUST /NINETEEN NINETY-TWO
105 FRI/SEPTEMBER/NINETEEN NINETY-TWO
109 TUE/SEPTEMBER/NINETEEN NINETY-TWO
110 WED/SEPTEMBER/NINETEEN NINETY-TWO
111 WED/SEPTEMBER/NINETEEN NINETY-TWO
O exemplo abaixo demostra como colocar o dia em formato numérico " FMDD " e o mês e o ano por extenso.
Select LAST_NAME, TO_CHAR(START_DATE,'FMDD "OF" MONTH YYYY') HIREDATE FROM S_EMP WHERE START_DATE LIKE '%91'
LAST_NAME
HIREDATE
Nagayama 17 OF JUNE 1991
Urguhart 18 OF JANUARY 1991
Havel 27 OF FEBRUARY 1991
Sedeghi 18 OF FEBRUARY 1991
Dumas 09 OF OCTOBER 1991
Nozaki 09 OF FEBRUARY 1991
Patel
LAST_NAME
HIREDATE
06 OF AUGUST 1991
Newman 21 OF JULY 1991
Markarian 26 OF MAY 1991
Dancs 17 OF MARCH 1991
Schwartz 09 OF MAY 1991
No exemplo abaixo estamos usando a função to_char novamente, agora para formatarmos um numero e concatenarmos uma coluna numérica com um comentário caracter.
Vamos ao Exemplo:
SQL> SELECT 'O PEDIDO ' ||TO_CHAR(ID)||' TEM UM TOTAL DE: '||TO_CHAR(TOTAL,'FM$9,999,999') FROM S_ORD WHERE DATE_SHIPPED ='21-SEP-92' /
'OPEDIDO'||TO_CHAR(ID)||'TEMUMTOTALDE:'|| TO_CHAR(TOTAL,'FM$9,999,999')
O PEDIDO 107 TEM UM TOTAL DE: $142,
O PEDIDO 110 TEM UM TOTAL DE: $1,
O PEDIDO 111 TEM UM TOTAL DE: $2,
FINAL DE CAPÍTULO.
Finalidade do Capítulo: Demostrar o uso dos comandos COMMIT,ROLLBACK E SAVE POINT.
Os cmdos COMMIT , ROLLBACK e SAVE POINT são usados para controle de execução, de confirmação e de retorno.Veja abaixo que estamos alterando o valor de SALARY na tabela S_EMP usando o UPDATE e SET , após alterá-los nós criamos um SAVEPOINT , que serve simplesmente para o controle de ponto, para se quisermos dar um ROLLBACK nas alterações feitas até então, termos condições de limitar o espaço atingindo pelo ROLLBACK.
Vamos ao Exemplo de Update:
SQL> EDIT Wrote file afiedt.buf 1 UPDATE S_EMP 2 SET SALARY = SALARY * 1. 3* WHERE TITLE ='Stock Clerk' SQL> /
10 rows updated.
Agora vamos criar um SAVEPOINT.
Vamos ao Exemplo:
SQL> savepoint update_ponto;
Savepoint created.
Agora usando o cmdo INSERT INTO estamos inserindo registros na tabela s_region.
SQL> insert into s_region (id,name) 2 values (8,'central');
1 row created.
Estamos selecionando os registros de s_region para confirmamos a inserção ou seja, se realmente foi incluído registros nesta tabela.
SQL> select * from 2 s_region 3 where id = 8;
ID NAME
8 central
Agora após confirmarmos a inclusão dos registros, nós decidirmos que não queremos que seja incluído registros na referida tabela s_region e para voltarmos a nossa ação temos então que dar um ROLLBACK, para desfazer o INSERT, só que somente até o ponto da inserção, ou seja até o SAVE POINT, que foi criado anteriormente ou seja tudo que está antes do SAVE POINT continua com suas alterações preservadas, é bom observar que para o SAVE POINT foi criado um nome no caso UPDATE_PONTO, isso serve para que possamos referenciar de forma correta até que ponto queremos cancelar nossas ações ou seja até que ponto não queremos que sejam salvas nossas ações.
Vamos ao exemplo:
SQL> ROLLBACK TO UPDATE_PONTO;
Rollback complete.
Agora vamos dar novamente um select para confirmarmos se o nosso ROLLBACK realmente fez efeito, observe que as alterações feitas anteriormente não foram concretizadas.
SQL> select * from 2 s_region 3 where id = 8;
no rows selected
O CMDO ROLLBACK serve para que seja desfeitas as alterações que foram efetuadas em uma tabela.
Vamos ao exemplo:
SQL> delete from test; 25,000 rows deleted
para cancelar o DELETE na tabela test.
Finalidade do Capítulo é demostrar como criar comentários a respeito de tabelas.
O comentários criado a respeito de tabelas e colunas são armazenados no dicionário de dados em: ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS
Criando comentários para uma tabela:
SQL> COMMENT ON TABLE s_emp is ' informação sobre funcionário';
Comment created.
Como criar comentários para uma coluna:
SQL> COMMENT ON COLUMN s_emp.last_name IS ' ultimo';
Comment created.COMMENT ON COLUMN s_emp.last_name IS ' último'
Como verificar os comentários existentes dentro de uma tabela ou coluna:
Primeiramente vamos verificar os comentários relativos as colunas pertencentes a uma tabela all_col_comments , para depois realizarmos um select em seu conteúdo pesquisando pelo nome da tabela ou coluna para sabermos qual é o comentário específico a respeito.
SQL> desc all_col_comments Name Null? Type
OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) COMMENTS VARCHAR2(2000)
Vamos realizando um SELECT ALL_COL_COMMENTS para vermos quais os comentários relativos a todas as colunas da tabela s_emp que são armazenados em COMMENTS :
SQL> select COMMENTS 2 from all_col_comments 3 where TABLE_NAME = 'S_EMP';
COMMENTS
ultimo
COMMENTS
Agora queremos saber o comentário a respeito da tabela s_emp veja abaixo:
SQL> SELECT COMMENTS 2 FROM ALL_TAB_COMMENTS 3 WHERE TABLE_NAME ='S_EMP';
COMMENTS
informação sobre funcionário
FIM DE CAPÍTULO
