Tutorial do Oracle PL / SQL Dynamic SQL: Execute Immediate & DBMS_SQL

Índice:

Anonim

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
  • NDS (Native Dynamic SQL) - Executar Imediato
  • DBMS_SQL para SQL Dinâmico

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

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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;/

Resultado

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

Explicação do código:

  • Linha de código 2-6 : Declarando variáveis.
  • Linha de código 8 : Enquadramento do 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 : Execução do 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 maneira 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 irá apenas verificar a sintaxe e manter a consulta pronta para execução.
  • Valores de VARIÁVEL BIND : A próxima etapa é atribuir os valores para as variáveis ​​de vinculação, se houver.
  • DEFINE COLUMN : 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.
  • FETCH VALUES : O próximo passo é buscar os valores executados.
  • FECHAR CURSOR : Uma vez que os resultados são buscados, 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.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:/

Resultado

Employee Name:XXXEmployee Number:1001Salary:15000Manager 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 Bind '1001' está atribuindo 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.