SQL - Apostilas - Informática Part1, Notas de estudo de Informática

Baixe SQL - Apostilas - Informática Part1 e outras Notas de estudo em PDF para Informática, 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

3 where &condição; Enter value for nome_coluna: LAST_NAME Enter value for condição: SALARY > 100

ID LAST_NAME

---

1 Velasquez 2 Ngao 3 Nagayama 4 Quick-To-See 5 Ropeburn 6 Urguhart 7 Menchu 8 Biri Temos um outro exemplo em que o usuário entra com o valor da coluna e da condição da WHERE.

SQL> select id,&nome_coluna 2 from s_ord 3 where &condição; Enter value for nome_coluna: date_ordered Enter value for condição: total>

ID DATE_ORDE

---

100 31-AUG- 104 03-SEP- 107 07-SEP- 108 07-SEP- 109 08-SEP- 97 28-AUG-

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

Input truncated to 11 characters

OUTRO EXEMPLO DE GERAÇÃO DE SCRIPT:

Mais uma vez vamos usar alguns comandos já vistos em exemplos anteriores, mas também alguns comandos novos e precisamos Mostrar a descrição de tais comandos: O comando SET ECHO OFF tem a finalidade de não deixar aparecer os cmdos feitos para a execução. O cmdo VERIFY OFF tem a finalidade de não mostrar os valores recebidos pelas as variáveis que no exemplo em questão são MENOR_DT e MAIOR_DT. O ACCEPT é usado para criar variáveis e o PROMPT para receber valores para as variáveis. Criadas as variáveis, observe a sintaxe e o "-" entre a definição do formato de data o uso de DATE, o FORMAT e a especificação 'MM/DD/YY'. Estamos usando também o COLUMN FORMAT A30 para formatar o tamanho da coluna EMPREGADO e logo depois estamos concatenando as colunas FIRST_NAME e LAST_NAME. Estamos também usando o BETWEEN para pesquisarmos valores que estão entre uma data e outra. Observe o uso do TO_DATE antes da colocação da variável com o "&" para receber valores que o usuário digita, e observe também a forma que foi colocado o formato da data 'MM/DD/YY' e o uso do AND que faz parte da WHERE. O UNDEFINE serve para que as variáveis percam os valores após a execução.

Vamos ao Exemplo:

SET ECHO OFF SET VERIFY OFF

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:

SQL> ALTER TABLE S_EMP

2 DROP CONSTRAINT s_emp_manager_id_fk;

Table altered.

DROPANDO REGISTROS EMCACATA: Para dropar uma chave primaria em cascata ou seja, deletá

• la de forma que seja deletada em todas as tabelas com quem tenha relação, temos prosseguir da seguinte forma:

Vamos ao exemplo:

SQL> ALTER TABLE s_dept 2 DROP primary key cascade; {observe o cmdo cascade}

Table altered.

Desabilitando CONSTRAINTS de uma tabela:

Verifique o cmdo CACADE sendo usado no ALTER TABLE, a finalidade de seu uso é permitir que todos os objetos que fazem referência ao campo ID de S_EMP aceitem a desabilitação da constraint.

Vamos ao exemplo:

SQL> ALTER TABLE s_emp 2 DISABLE CONSTRAINT s_emp_id_pk CASCADE;

Table altered.

Habilitando uma constraint:

Para habilitarmos uma constraint não precisamos usar o cmdo cascade, porque é criada de forma automática a UK ou FK.

Vamos ao exemplo SQL> ALTER TABLE s_emp 2 ENABLE CONSTRAINT s_emp_id_pk;

Table altered.

FINAL DE CAPITULO

Agora vamos converter a data colocando o dia por extenso ou seja usando, "day"(extenso), para o dia e colocando o mês por extenso usando, "month" para mês e colocando o ano por extenso usando o "yyyy" para ano.

Vamos ao Exemplo:

SELECT ID,TO_CHAR(DATE_ORDERED,'DAY/MONTH/YYYY') ORDERED FROM S_ORD WHERE SALES_REP_ID = 11

ID ORDERED

100 MONDAY /AUGUST /

105 FRIDAY /SEPTEMBER/

109 TUESDAY /SEPTEMBER/

110 WEDNESDAY/SEPTEMBER/

111 WEDNESDAY/SEPTEMBER/

Agora estamos colocando o nome da ano escrito por extenso usando a FUNÇÃO "YEAR".

SELECT ID,TO_CHAR(DATE_ORDERED,'DAY/MONTH/YEAR') ORDERED

FROM S_ORD

WHERE SALES_REP_ID = 11;

ID ORDERED

100 MONDAY /AUGUST /NINETEEN NINETY-TWO

105 FRIDAY /SEPTEMBER/NINETEEN NINETY-TWO

109 TUESDAY /SEPTEMBER/NINETEEN NINETY-TWO

110 WEDNESDAY/SEPTEMBER/NINETEEN NINETY-TWO

111 WEDNESDAY/SEPTEMBER/NINETEEN NINETY-TWO

Agora estamos usando o cmdo " dy " para mudar o dia da semana, passando da escrita por extenso para abreviado em três dígitos, observe o exemplo.

Vamos ao exemplo:

SQL> SELECT ID,TO_CHAR(DATE_ORDERED,'DY/MONTH/YEAR') ORDERED 2 FROM S_ORD 3 WHERE SALES_REP_ID = 11;

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

Finalidade do Capitulo: Demostrar o uso do Comando TO_DATE.

Verifique que no exemplo abaixo estamos querendo fazer uma pesquisa usando um campo data onde escrevemos a data, o mês e o ano e o cmdo to_date transforma o que digitamos em data para a pesquisa, só que deve ser obedecida uma seqüência lógica nas posições por isso o exemplo abaixo está incorreto.

Vamos ao exemplo:

SQL> SELECT DATE_ORDERED 2 FROM S_ORD 3 WHERE DATE_ORDERED = 4 TO_DATE('31 1992, AUGUST','DD MONTH, YYYY'); ERROR: ORA-01843: not a valid month

Vamos tentar corrigir o exemplo acima. A função do cmdo to_date é converter valores digitados, numéricos e caracters para data, a ordem da data procurada no exemplo abaixo tem que ser a mesma do formato de data colocado da seguinte forma:

SQL> SELECT DATE_ORDERED

2 FROM S_ORD

3 WHERE DATE_ORDERED =

4 TO_DATE('31 1992, AUGUST','DD, YYY MONTH');

DATE_ORDE

31-AUG-

31-AUG-

31-AUG-

31-AUG-

31-AUG-

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;

O CMDO COMMIT é o contrário do ROLLBACK ou seja serve para confirmar as alterações que por ventura sejam feitas. Uma vez dado um COMMIT não podemos retornar mais atrás.

SQL> delete from test; 25,000 rows deleted

Vamos confirmar o DELETE com o COMMIT.

SQL> COMMIT; Commit complete.

FINAL DE CAPÍTULO

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)