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

O que é SQL dinâmico?

SQL dinâmico é 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.

Neste tutorial, você aprenderá-

Maneiras de escrever SQL dinâmico

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

  1. NDS - SQL dinâmico nativo
  2. DBMS_SQL

NDS (Native Dynamic SQL) - Executar Imediato

SQL dinâmico nativo é 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 dessa forma, o tipo de dados e o número de variáveis ​​que serão usados ​​em um tempo de execução precisam ser conhecidos antes. Ele também oferece melhor desempenho e menos complexidade quando comparado ao DBMS_SQL.

Sintaxe

EXECUTE IMMEDIATE() [INTO] [USING ]
  • A sintaxe acima mostra o comando EXECUTE IMMEDIATE.
  • A cláusula INTO é opcional e usada apenas 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 apenas se o SQL dinâmico contiver qualquer 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.

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 vinculação em que condição ': empno'.
  • Linha de código 9 : Executando o texto SQL com moldura (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 manter os valores buscados na 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

A 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.

  • CURSOR ABERTO : O SQL dinâmico será executado da mesma forma que um cursor. Portanto, para executar a instrução SQL, devemos abrir o cursor.
  • PARSE 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 BIND VARIABLE : A próxima etapa é atribuir os valores para as variáveis ​​de vinculação, se houver.
  • DEFINIR COLUNA : A próxima etapa é definir a coluna usando suas posições relativas na instrução select.
  • EXECUTAR : A próxima etapa é executar a consulta analisada.
  • OBTER 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.

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);

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 : Enquadramento da 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 vinculação '1001' é atribuída ao id do cursor em vez de ': empno'.
  • Linha de código 14-17 : Definindo o nome da coluna com base em sua posição relativa na instrução SQL. Em nosso caso, a posição relativa é (1) emp_name, (2) emp_no (3) salary (4) manager. Portanto, com base nesta posição, estamos definindo a variável de destino.
  • Linha de código 18 : Executando a consulta usando 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 o mesmo.
  • Linha de código 20: DBMS_SQL.FETCH_ROWS buscará um registro das linhas processadas. Ele pode ser chamado repetidamente para buscar todas as linhas. Se não puder buscar linhas, ele retornará 0, saindo do loop.

Resumo

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