(Parte 1 de 13)

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=31

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.

Nos exemplos anteriores o usuário entrou com a condição de pesquisa quer seja numérica ou caracter, cada qual com suas características, agora vamos ver como que possibilitamos ao usuário entrar com o nome da coluna e com a própria condição de pesquisa que será estabelecida na cláusula WHERE. Neste caso temos um exemplo onde é requerida a entrada de um dado numérico.

Vamos ao exemplo:

SQL> select id,&nome_coluna

2 from s_emp

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>30000

ID DATE_ORDE

--------- ---------

100 31-AUG-92

104 03-SEP-92

107 07-SEP-92

108 07-SEP-92

109 08-SEP-92

97 28-AUG-92

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

NAME ID NOME DA REGIÃO

------------------------- --------- ---------------------------------------------

Sales 1 North America

Sales 2 South America

Sales 3 Africa / Middle East

Sales 4 Ásia

Sales 5 Europe

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 A30

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:

SQL>

ALTER TABLE s_emp

ADD CONSTRAINT s_emp_manager_id_fk

FOREIGN KEY (MANAGER_ID)

REFERENCES s_emp(id)

DROPANDO UMA CONSTRAINT:

Este exemplo mostra como dropar uma CONSTRAINT, Neste caso o nome da constraint é s_emp_maneger_id_fk, verifique a forma como foi escrito o exemplo abaixo:

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:

(Parte 1 de 13)

Comentários