SQL DINÂMICO

Somente instruções DML (select, insert, update, delete... ) podem ser utilizadas em blocos PL/SQL.
Quando preciamos criar uma estrutura DDL ou DML em tempo de execução, usamos o SQL Dinâmico.

Para utilizar a SQL Dinâmica na PL/SQL pode-se usar duas técnicas, a BDMS_SQL e a SQL Dinâmica nativa.

EX SQL Dinâmica Nativa: 

declare

  v_sql   varchar2(300);
  v_plsql varchar2(300);
  
begin

    execute immediate 'create table teste_sql_din(coluna varchar2(10))';
  
    for reg in 1..50 loop
      v_sql:= 'insert into teste_sql_din
               values ('||reg||')';
               
      EXECUTE IMMEDIATE v_sql;         
    end loop;
    
    v_plsql:= 'begin
                 for reg in (select * from teste_sql_din) loop
                   dbms_output.put_line(reg.coluna);
                 end loop;
               end;';
    
    EXECUTE IMMEDIATE v_plsql; 
    
    EXECUTE IMMEDIATE 'drop table teste_sql_din';

end;

-----------------------------------------------------------------
Criar uma tabela através de uma procedure:

create or replace procedure prc_create_table
                           (p_nome_tabela varchar2
                           ,p_colunas     varchar2) is
                                            
                                            
begin

  EXECUTE IMMEDIATE 'create table '||p_nome_tabela||' (' ||p_colunas||' )'; 

end;


-- Chamando a procedure
begin

  prc_create_table('tabela_teste','coluna1 number , coluna2 varchar2(10)');

end;
-----------------------------------------------------------------
Deletar linhas de uma tabela:

Create or replace function fnc_deletar_linhas(p_tabela varchar2) return number is

begin
  EXECUTE IMMEDIATE 'delete '||p_tabela;
  
  return sql%rowcount;
end;

-- Chamando a função
begin
  dbms_output.put_line(fnc_deletar_linhas('tabela_teste')||' linhas deletadas');
end;

----------------------------------------------------------------
Inserir linhas na tabela Regions(2 colunas)
create or replace procedure prc_insere_linhas(p_tabela  varchar2
                                             ,p_coluna1 number
                                             ,p_coluna2 varchar2) is
                                             
begin
  EXECUTE IMMEDIATE 'insert into ' ||p_tabela|| ' values (:1, :2)' using p_coluna1, p_coluna2;
end;

-- chamando a procedure
begin
  prc_insere_linhas('regions',5, 'teste');
end;

FUNÇÕES DE TEXTO

LPAD  

select lpad(first_name, 10, 'x')
from   employees;

Preenche com X a esquerda até que a string contenha 10 caracteres










RPAD

select rpad(first_name, 10, 'x')
from   employees;

Preenche com X a direita até que a string contenha 10 caracteres

LOWER

select lower(first_name)

from   employees;

Retorna os dados da string em caixa baixa

UPPER

select upper(first_name)

from   employees;

Retorna os dados da string em caixa alta

INITCAP

Retorna a primeira letra da string em caixa alta

select initcap('marcio')
from  dual;

LENGTH

Retorna a quantidade de caracteres de uma string

select length('marcio')
from  dual;

SUBSTR

Retorna uma substring de uma string

select substr('Oracle',1,3)
from  dual;

INSTR

Retorna de uma substring pode ser encontrada dentro de uma string

select instr('Oracle','ac')
from  dual;

TRIM

Retira valor de uma string (somente no início ou no fim)

select TRIM('   marcio   ') from dual;        
retira os espaços retornando 'marcio'

select TRIM(' '  FROM  '   marcio   ')  from dual;
retira os espaços retornando 'marcio'

select TRIM(LEADING '0' FROM '000111') from dual;
retira os zeros retornando '111'

select TRIM(TRAILING '1' FROM 'Marcio1') from dual;
Retira 1 retornando 'Marcio'

select TRIM(BOTH '1' FROM '123Marcio321111') from dual;
Retira 1 retornando '23Marcio32'

CLÁUSULA WHERE

A cláusula Where permite restringir a consulta SQL, trazendo somente os dados que interessam no momento.

A cláusula where utiliza operadores de comparação:

 x = y        verifica se x é igual a y

select * from employees
where employee_id = 207;

x > y        verifica se x é maior que y

select employee_id
      ,first_name
      ,job_id
      ,salary
from   employees 
where  salary > 9000;

x > =  y      verifica se x é maior ou igual a y

x <  y          verifica se x é menor que y

x <= y         verifica se x é menor ou igual a y

LIKE  usado para buscar uma cadeia de caracteres, mesmo que você não saiba parte dela.

O like pode ser usado com % quando por exemplo você quer trazer todos os registros cujo nome comecem com A:

select * from employees
where first_name like 'A%';

Para trazer todos os registros cujo nome contenha a string 'xand'

select * from employees
where first_name like '%xand%';

LIKE pode ser também utilizado com o caracter _

Vamos imaginar que alguem possa ter digitado Steven com e ou com i e você queira ter certeza que serão retornados todos os "Steven";

select * from employees
where first_name like 'Stev_n';

BETWEEN      permite verificar se y está entre x e z, pode ser usado com datas, números ou caracteres.

select employee_id
      ,first_name
      ,job_id
      ,salary
from   employees 
where  salary between 12000 and 17000;

IN      permite verificar se o valor de uma coluna está entre um conjunto de valores

select employee_id
      ,first_name
      ,job_id
      ,salary
from   employees 
where  job_id in ('AD_VP', 'MK_MAN', 'FI_MGR');

Caso seja necessário restringir a consulta em mais de uma condição, podem ser acrescentadas as palavras chaves and, or, not e is null

select employee_id
      ,first_name
      ,job_id
      ,salary
from   employees 
where  job_id in ('AD_VP', 'MK_MAN', 'FI_MGR')
and    salary >= 13000;

Vamos utilizar agora o mesmo caso acima, porém onde os cargos NÃO sejam AD_VP, MK_MAN e FI_MGR.  

select employee_id
      ,first_name
      ,job_id
      ,salary
from   employees 
where  job_id not in ('AD_VP', 'MK_MAN', 'FI_MGR')
and    salary >= 13000;

Utilizando o operador IS NULL, vamos buscar os dados onde os nomes dos funcionários comecem entre A e D e não recebam comissão 

select employee_id
      ,first_name
      ,job_id
      ,salary
      ,commission_pct
from   employees
where  first_name between 'A%' and 'D%' 
and    commission_pct is null;

ORDER BY

Utiliza-se a cláusula Order By para retornar os dados em uma ordem específica. Pode-se usar o Order By para ordenar de modo ascendente ou descendente, uma ou mais colunas, numérica ou alfabética.

A cláusula Order By é inserida em último lugar no comando SQL.

select first_name

      ,hire_date

from   employees

order by first_name asc;

select first_name

      ,hire_date

from   employees

order by first_name desc;

Pode-se ordenar também utilizando o número da coluna desejada.

select first_name

      ,hire_date

from   employees

order by 1 asc, 2 desc;

Quando houverem dados null em alguma coluna que o Oracle está tentando ordenar, quando forem de modo Ascendente, os dados nulos aparecerão no final da lista e quando forem ordenados de modo Descendente, os dados nulos serão mostrados no início da lista.

DESCRIBE (DESC)

Descreve a estrutura de uma tabela

desc employees;

DISTINCT

Utilizado para obter valores únicos de uma coluna, ou seja, quando houverem o mesmo valor da coluna em mais de um registro, será trazido uma unica vez.

select distinct(job_id) cargo
from employees;

É possível usar distinct utilizando mais de uma coluna, no caso abaixo, observe que foram trazidos o mesmo cargo mais de uma vez, porém com salários diferentes.

select distinct job_id, salary
from employees;

NVL

Utilizado para substituição de campos NULOS.

select employee_id
      ,first_name
      ,salary
      ,nvl(commission_pct,0) comissao
from employees;

GRANT, REVOKE

Comandos DCL (Data Control Language), utilizado para controle de acesso de usuário a dados.

grant select, insert, update, delete on employees to marcio;

revoke delete on employees to marcio;

COMMIT, ROLLBACK e SAVEPOINT

Dentro da sessão de um usuário, para efetivar uma ou mais transações, utiliza-se o comando "commit" e para desfazer uma ou mais transações utiliza-se o comando "rollback".
Vale ressaltar que caso já tenha sido aplicado o commit não é mais possível utilizar o rollback e vice-versa.

Savepoint é o comando utilizado para marcar pontos de divisão lógicos dentro da transação, ou seja, pode-se estabelecer um ponto de salvamento e, posteriormente, desfazer todos os comandos executados após o seu estabelecimento:

Ex:

create table tabela_teste
(col_teste number)

BEGIN    
  INSERT INTO tabela_teste VALUES (1);
  SAVEPOINT meu_savepoint;
  INSERT INTO tabela_teste VALUES (2);
  ROLLBACK TO SAVEPOINT meu_savepoint;
  INSERT INTO tabela_teste VALUES (3);
  INSERT INTO tabela_teste VALUES (4);
    
  COMMIT; 
END;

SELECT * FROM tabela_teste;

col_teste
------------
1
3
4

CREATE, ALTER, DROP

Usados para operações DDL (Data Manipulation Language ) servindo para criar, alterar e remover objetos do banco de dados, tais como tabelas, views, sequencias, triggers, etc...

create table veiculos
(cod_veiculo            number(6)
,des_veiculo            varchar2(60)
,ano_veiculo            varchar2(60)

,valor_veiculo          date);

alter table veiculos add tipo_veiculo varchar2(10);
Inserida a coluna tipo_veiculos na tabela veiculos.

drop table veiculos;
Removida a tabela veiculos e todos os seus registros.

INSERT, UPDATE, DELETE

Usados para operaçõs DML (Data Manipulation Language), incluir novos registros, modificar registros existentes e remover registros, respectivamente.

insert into employees(EMPLOYEE_ID    
                     ,FIRST_NAME     
                     ,LAST_NAME      
                     ,EMAIL          
                     ,PHONE_NUMBER   
                     ,HIRE_DATE      
                     ,JOB_ID         
                     ,SALARY         
                     ,COMMISSION_PCT 
                     ,MANAGER_ID     
                     ,DEPARTMENT_ID)
                     values
                     ((select max(employee_id + 1)
                       from employees)   
                     ,'Marcio'     
                     ,'Correia'      
                     ,'marcio.correia@xxxx.com'
                     ,11999990000   
                     ,sysdate
                     ,'IT_PROG'         
                     ,'1000'         
                     ,null 
                     ,103     
                     ,60);

Obs: Para a coluna employee_id está sendo utilizada a função max e buscando na própria tabela o maior código existente e somando o valor 1, criando assim o novo código de funcionário.

Para a coluna hire_date (data de contratação) está sendo informado o valor sysdate, que é o valor da data corrente do banco de dados.
----------------------------------------------------------------------------------------------------------------------------------

insert into employees values
                     ((select max(employee_id + 1)
                       from employees)   
                     ,'Joao'     
                     ,'Silva'      
                     ,'joao.silva@xxxx.com'
                     ,11999990000   
                     ,sysdate
                     ,'IT_PROG'         
                     ,'1000'         
                     ,null 
                     ,103     

                     ,60);

É possivel também realizar o insert sem informar o nome das colunas, ou seja, simplesmente informando os valores que serão inseridos, porém severão ser informados os valores de todas as colunas existentes na tabela, caso contrário será retornado o erro:
Erro de SQL: ORA-00947: não há valores suficientes

00947. 00000 -  "not enough values"
----------------------------------------------------------------------------------------------------------------------------------

update employees
set    department_id = 90
where  employee_id   = 207;

Neste comando o funcionário de código 207 está tendo seu departamento alterado para 90.
----------------------------------------------------------------------------------------------------------------------------------

delete employees
where  employee_id = 208;

Neste comando o funcionário de código 208 está sendo removido da base de dados.
----------------------------------------------------------------------------------------------------------------------------------

Obs: Nos comandos update e delete deve-se tomar muito cuidado com a cláusula where, informando corretamente o que deverá ser alterado/removido, caso não seja informada a cláusula where todas as linhas da tabela sofrerão a ação solicitada.

SELECT

Usado para obtenção de dados e acesso a colunas.
A sintaxe para um comando select consiste se "select... from...;".

select * from employees;

CONCATENAÇÃO DE COLUNAS

É possível combinar colunas fazendo com que se tornem "uma só coluna", utilizando a concatenação, através do operador ||. 

select 'O salário do funcionário '||first_name||' é '||salary
from    employees;

Pode-se usar também a função CONCAT, que trará o mesmo resultado acima.

SELECT CONCAT(CONCAT(CONCAT
('O salário do funcionário ',first_name ), ' é '),salary) "Salario" 

FROM employees;

CÁLCULOS ARITMÉTICOS

O Oracle permite que você realize operações aritméticas em dados numéricos de tabelas,  pode-se usar os mesmos operadores que usamos no dia a dia (+, -, /, *).


Vamos aplicar 15% de aumento no salário dos funcionários:

select employee_id
       ,first_name
       ,salary
       ,salary * 1.15 "Salario Alterado"
from employees;

Porém vamos imaginar que você queira fazer uma operação aritmética sem a utilização de dados do sistema, você pode utilizar a tabela reservada do Oracle para essa operação, a tabela Dual.
A tabela Dual pertence ao usuário SYS e é uma tabela com uma coluna e uma linha, usada para preencher os requisitos de sintaxe dos comandos SQL.

 select 1 + 1 from dual;

Muito importante observar que é necessário informar através do parênteses quais cálculos serão realizados primeiro.

select (1000 * 1.10) * (10 - 5) from dual;
Resultado: 5500

select (1000 * 1.10) * 10 - 5 from dual;
Resultado: 10995

Tá baratinho...

O Commit deve ser mais caro !

Felicidade !

Quanto mais melhor...