Oracle Tutorial de SQL Dinâmico PL/SQL: Executar Imediato e DBMS_SQL

Por: Fiona Brown Fiona Brown
Hours Atualização do

O que é SQL Dinâmico?

Dinâmico SQL é uma metodologia de programação para gerar e executar instruções em tempo de execução. É usado principalmente para escrever programas flexíveis e de uso geral, onde as instruções SQL serão criadas e executadas em tempo de execução com base no requisito.

Maneiras de escrever SQL dinâmico

PL/SQL fornece duas maneiras de escrever SQL dinâmico

  1. NDS – SQL Dinâmico Nativo
  2. SGBD_SQL

NDS (Native Dynamic SQL) – Executar Imediato

Native Dynamic SQL é a maneira mais fácil de escrever SQL dinâmico. Ele usa o comando 'EXECUTE IMMEDIATE' para criar e executar o SQL em tempo de execução. Mas para usar desta forma, o tipo de dados e o número de variáveis ​​​​que serão usadas em tempo de execução precisam ser conhecidos antes. Também oferece melhor desempenho e menos complexidade quando comparado ao DBMS_SQL.

Sintaxe 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • A sintaxe acima mostra o comando EXECUTE IMMEDIATE.
  • A cláusula INTO é opcional e usada somente se o SQL dinâmico contiver uma instrução select que busca valores. O tipo de variável deve corresponder ao tipo de variável da instrução select.
  • A cláusula USING é opcional e usada somente se o SQL dinâmico contiver alguma variável de ligação.

Exemplo 1: Neste exemplo, vamos buscar os dados da tabela emp para emp_no '1001' usando a instrução NDS.

NDS - Executar Imediato

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_manager
USING 1001;
Dbms_output.put_line('Employee Name:‘||lv_emp_name);
Dbms_output.put_line('Employee Number:‘||ln_emp_no);
Dbms_output.put_line(‘Salary:'||ln_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

saída

Employee Name : XXX 
Employee Number: 1001 
Salary: 15000 
Manager ED: 1000

Explicação do código:

  • Linha de código 2-6: Declarando variáveis.
  • Linha de código 8: Enquadrando o SQL em tempo de execução. SQL contém a variável de ligação na condição where ':empno'.
  • Linha de código 9: Executando o texto SQL emoldurado (que é feito na linha de código 8) usando o comando NDS 'EXECUTE IMMEDIATE'
  • As variáveis ​​na cláusula 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) são usadas para armazenar os valores obtidos da consulta SQL (emp_name, emp_no, salário, gerente)
  • A cláusula 'USING' fornece os valores para a variável de ligação na consulta SQL (:emp_no).
  • Linha de código 10-13: Exibindo os valores buscados.

DBMS_SQL para SQL Dinâmico

PL/SQL fornece o pacote DBMS_SQL que permite trabalhar com SQL dinâmico. O processo de criação e execução do SQL dinâmico contém o seguinte processo.

  • ABRIR CURSOR: O SQL dinâmico será executado da mesma maneira que um cursor. Portanto, para executar a instrução SQL, devemos abrir o cursor.
  • ANALISAR SQL: A próxima etapa é analisar o SQL dinâmico. Este processo apenas verificará a sintaxe e manterá a consulta pronta para execução.
  • Valores de VARIÁVEIS BIND: a próxima etapa é atribuir os valores às variáveis ​​de ligação, se houver.
  • DEFINIR COLUNA: A próxima etapa é definir a coluna usando suas posições relativas na instrução select.
  • EXECUTE: a próxima etapa é executar a consulta analisada.
  • BUSCAR VALORES: A próxima etapa é buscar os valores executados.
  • FECHAR CURSOR: Assim que os resultados forem obtidos, o cursor deve ser fechado.

Exemplo 1: Neste exemplo, vamos buscar os dados da tabela emp para emp_no '1001' usando a instrução DBMS_SQL.

DBMS_SQL para SQL Dinâmico

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50);
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
ln_cursor_id NUMBER;
ln_rows_processed;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,ln_emp_no);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,3,ln_salary);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,4,ln_manager);

ln_rows__processed:=DBMS_SQL.EXECUTE(ln_cursor_id);

DBMS_SQL para SQL Dinâmico

LOOP
IF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0
THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,1,lv_emp_name); 
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,2,ln_emp_no);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,3,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||ln_emp_no); 
Dbms_output.put_line(‘Salary:‘||ln_salary); 
Dbms_output.put_line('Manager ID :‘| ln_manager);
END IF;
END LOOP;

DBMS_SQL.CLOSE_ClIRSOR(ln_cursor_id);

END:
/

saída

Employee Name:XXX 
Employee Number:1001 
Salary:15000 
Manager ID:1000

Explicação do código:

  • Linha de código 1-9: Declaração de variável.
  • Linha de código 10: Enquadrando a instrução SQL.
  • Linha de código 11: Abrindo o cursor usando DBMS_SQL.OPEN_CURSOR. Ele retornará o ID do cursor que está aberto.
  • Linha de código 12: Depois que o cursor é aberto, o SQL é analisado.
  • Linha de código 13: A variável de ligação '1001' está sendo atribuída ao ID do cursor em vez de ':empno'.
  • Linha de código 14-17: definir o nome da coluna com base em sua posição relativa na instrução SQL. No nosso caso, a posição relativa é (1) nome_emprego, (2) número_emprego (3) salário (4) gerente. Então, com base nesta posição, estamos definindo a variável alvo.
  • Linha de código 18: Executando a consulta utilizando DBMS_SQL.EXECUTE. Ele retorna o número de registros processados.
  • Linha de código 19-33: Buscando os registros usando um loop e exibindo os mesmos.
  • Linha de código 20: DBMS_SQL.FETCH_ROWS irá buscar um registro das linhas processadas. Pode ser chamado repetidamente para buscar todas as linhas. Se não conseguir buscar linhas, retornará 0, saindo assim do loop.

Resumo

Nesta seção, discutimos SQL dinâmico e as maneiras de executar SQL DINÂMICO. Também vimos as diferentes etapas na execução do SQL dinâmico em ambas as formas. Também vimos exemplos em que o mesmo cenário é tratado nas formas NDS e DBMS_SQL para executar a execução em tempo de execução.

Você pode gostar: