Oracle Database => Dynamische SQL

Invoering

Met dynamische SQL kunt u een SQL-querycode samenstellen in de runtime. Deze techniek heeft enkele nadelen en moet zeer zorgvuldig worden gebruikt. Tegelijkertijd kunt u complexere logica implementeren. PL / SQL vereist dat alle objecten die in de code worden gebruikt, moeten bestaan en geldig zijn tijdens het compileren. Daarom kunt u DDL-instructies niet rechtstreeks in PL / SQL uitvoeren, maar met dynamische SQL kunt u dat doen.

Opmerkingen

Enkele belangrijke opmerkingen:

Gebruik nooit tekenreeksen om waarden aan de query toe te voegen, gebruik in plaats daarvan parameters. Dit is fout:
```
execute immediate 'select value from my_table where id = ' || 
     id_valiable into result_variable;
```
En dit klopt:
```
execute immediate 'select value from my_table where id = :P '
    using id_valiable into result_variable;
```
Daar zijn twee redenen voor. De eerste is de beveiliging. String samenvoeging maakt het mogelijk om SQL-injectie te maken. Als in de onderstaande query een variabele waarde 1 or 1 = 1 , werkt de instructie UPDATE alle regels in de tabel bij:
```
execute immediate 'update my_table set value = ''I have bad news for you'' where id = ' || id;
```
De tweede reden is prestaties. Oracle parseert query zonder parameters telkens wanneer het wordt uitgevoerd, terwijl query met parameter slechts eenmaal in de sessie wordt ontleed.
Merk op dat wanneer de database-engine een DDL-instructie uitvoert, deze eerder impliciete commit uitvoert.

Selecteer waarde met dynamische SQL

Stel dat een gebruiker gegevens uit verschillende tabellen wil selecteren. Een tabel wordt gespecificeerd door de gebruiker.

 function get_value(p_table_name varchar2, p_id number) return varchar2 is
    value varchar2(100);
  begin
    execute immediate 'select column_value from ' || p_table_name || 
                      ' where id = :P' into value using p_id;
    return value;
  end;

Roep deze functie zoals gewoonlijk aan:

declare
  table_name varchar2(30) := 'my_table';
  id number := 1;
begin
  dbms_output.put_line(get_value(table_name, id));
end;

Tabel om te testen:

create table my_table (id number, column_value varchar2(100));
insert into my_table values (1, 'Hello, world!');

Voeg waarden in dynamische SQL in

In het onderstaande voorbeeld wordt waarde in de tabel uit het vorige voorbeeld ingevoegd:

declare
  query_text varchar2(1000) := 'insert into my_table(id, column_value) values (:P_ID, :P_VAL)';
  id number := 2;
  value varchar2(100) := 'Bonjour!';
begin
  execute immediate query_text using id, value;
end;
/

Update waarden in dynamische SQL

Laten we de tabel van het eerste voorbeeld bijwerken:

declare
  query_text varchar2(1000) := 'update my_table set column_value = :P_VAL where id = :P_ID';
  id number := 2;
  value varchar2(100) := 'Bonjour le monde!';
begin
  execute immediate query_text using value, id;
end;
/

Voer DDL-instructie uit

Deze code maakt de tabel:

begin
  execute immediate 'create table my_table (id number, column_value varchar2(100))';
end;
/

Voer een anoniem blok uit

U kunt een anoniem blok uitvoeren. Dit voorbeeld laat ook zien hoe waarde uit dynamische SQL kan worden geretourneerd:

declare
  query_text varchar2(1000) := 'begin :P_OUT := cos(:P_IN); end;';
  in_value number := 0;
  out_value number;
begin
  execute immediate query_text using out out_value, in in_value;
  dbms_output.put_line('Result of anonymous block: ' || to_char(out_value));
end;
/

Modified text is an extract of the original Stack Overflow Documentation

Licentie onder CC BY-SA 3.0

Niet aangesloten bij Stack Overflow

Oracle Database
Dynamische SQL

Zoeken…