[PLSQL] dynamisch opbouw en uitvoer

[ErikRo]

Even een lekker kort voorbeeldje in nederlands kon ik zo niet vinden over dynamisch sql. Maar het ziet er verdomd handig uit, mits je het werkend krijgt

stel je hebt gedeclareerd:
A := naam
B := adres
C := NAW
D := 1002AD
v_query varchar2

Is dit dan genoeg om hem te laten werken? Of hoort er nog een stap(pen) bij?

DBMS_SQL.PARSE('SELECT ' || A,B || ' FROM ' || C ' WHERE ' || D);
DBMS_SQL.BIND_VARIABLE (v_query, 'SELECT ' || A,B || ' FROM ' || C ' WHERE ' || D);
DBMS_SQL.EXECUTE(v_query);

Dit is wat ik begreep tot nu toe. Parse controleert de string op spelling. Bind de boel in een variabele stopt en execute de variabele_query uitvoert

[ErikRo]

schop

[Woudloper]

ErikRo: Je zit bijna goed, maar als ik het goed had begrepen werkt het als volgt:

SQL:

declare
  dyna_cur  integer;
  A     := naam;
  B     := adres;
  C     := NAW;
  D     := 1002AD;

begin
  dyna_cur  := dbms_sql.open_cursor;

  dbms_sql.parse(dyna_cur, 'SELECT ' || A,B || ' FROM ' || C ' WHERE ' || D);
  dbms_sql.execute(dyna_cur);

end;

Dit moet al voldoende zijn, maar ik zit mij even af te vragen of je in dbms_sql.parse wel een voor from een variabele mag gebruiken. Als dat niet gaat dan moet je dit nog even in een string (varchar2) zetten en die dan parsen, als het goed is werkt dat dan wel...

[Woudloper]

_{ff een stukje uit een boek....}

5.1.3. Het gebruik van DBMS_SQL

In het volgende voorbeeld wordt een procedure NAMES aangemaakt die gebruik maakt van dynamisch SQL. Wanneer de procedure wordt aangeroepen kan de naam van een tabel worden meegegeven waaruit alle rijen uit de eventuele kolom NAME worden geselecteerd. De naam van de tabel is op het moment dat de procedure wordt gecreëerd nog niet bekend.

SQL:

create or replace procedure NAMES(p_table in varchar2) is
  v_name    varchar2(15);
  dyna_cur  integer;
  v_result  integer;

begin
  dyna_cur  := dbms_sql.open_cursor;
  dbms_sql.parse(dyna_cur, 'select name from ' || p_table, 1);
  dbms_sql.define_column (dyna_cur, 1, v_name, 15);
  v_result  := dbms_sql.execute(dyna_cur);

  while dbms_sql.fetch_rows(dyna_cur) = 1 loop
    dbms_sql.column_value(dyna_cur, 1, v_name);
    dbms_output.put_line(v_name);
  end loop;

  dbms_sql.close_cursor(dyna_cur);

exception
   when others then
    dbms_output.put_line(sqlerrm);
    dbms_sql.close_cursor(dyna_cur);
end;

De procedure wordt gecompileerd, het SELECT statement wordt niet gecontroleerd maar ingelezen als een string.

Bij het uitvoeren wordt het volgende gedaan:

1. de cursor dyna_cur wordt geopend, er wordt dus een stukje geheugen gereserveerd voor het uitvoeren van het statement.
2. Het SELECT statement wordt gecontroleerd, bv of de syntax dus correct is. Dit gebeurd pas tijdens het uitvoeren. In dit stukje wordt:
   1. gecontroleerd of de tabel bestaat
   2. gecontroleerd of de kolom bestaat
   3. gecontroleerd of de gebruiker de juiste rechten heeft
3. Met define_column wordt aangegeven dat we een kolom uit het SELECT statement willen gaan gebruiken in de procedure.
4. Met execute wordt het SELECT statement uitgevoerd en de rijen in het geheugen gezet.
5. Daarna worden de rijen door fetch_rows opgehaald uit het geheugen, totdat er geen rijen meer zijn. Als er geen rijen meer kunnen worden opgehaald geeft de functie fetch_rows de waarde 0 terug.
6. De opgehaalde waarden worden met column_value in een variabele gezet, die gebruikt wordt om de waarden op het scherm te zetten (middels dbms_output).
7. Als alle rijen zijn opgehaald wordt de cursor gesloten en het geheugen vrijgegeven.

Dit was het! Ik zou zeggen succes ermee Maar als ik jou was zal ik nog ff een cursus gaan volgen wat je allemaal met PL/SQL kan

[leonardo1504]

[quote]
DBMS_SQL.PARSE('SELECT ' || A,B || ' FROM ' || C ' WHERE ' || D);
[quote]

moet dan op zijn minst er zo uit zien:

code:

DBMS_SQL.PARSE('SELECT ' || A,B || ' FROM ' || C || '  WHERE ' || D);

Ik weet niet welke versie van Oracle je gebruikt, maar vanaf 8i kan je meestal beter Native Dynamic SQL gebruiken (NDS). Dat is een stuk overzichtelijker en vaak ook efficienter. Kijk op http://technet.oracle.com vooor meer info. Daar kan je alle tutorials en voorbeelden vinden die je nodig hebt.

[ErikRo]

Woudloper, bedankt voor je uitvoerige antwoord, ik hoop dat je OCR in huis had
Die cursus zou ik graag willen volgen, maar het bedrijf waar ik werk heeft even geen geld en eigenlijk vinden ze dat ik mijn werk maar met gewoon sql moet doen Je mocht eens te slim worden.
Dus ben ik maar met zelfstudie bezig als ik even wat tijd over heb. Maar dat is inderdaad best wel pittig vooral als je even vast komt te zitten, maar daarom ben ik ook erg blij met dit formum.

Leonardo1504
Wij hebben 8i dus nds zou kunnen. Alleen die voorbeeldjes van technet zijn die altijd zo groot? Ik heb net even een voorbeeldje (8 blz) van Technet gehaald. Kost me alleen al een uur om dat door te lezen en te begrijpen
ps technet bestaat zeker niet in het nederlands?

[leonardo1504]

nope bestaat niet in het Nederlands, probeer deze link eens. Misschien dat er wel budget is voor dit boek van O'Reilly?
http://www.oreilly.com/ca...eatures/chapter/ch04.html

en deze van technet is ook wel aardig:

http://download-uk.oracle...v.901/a89856/11_dynam.htm

[Woudloper]

Dit boek is ook wel handig (en niet duur):

Leerboek Oracle PL/SQL (Academic Service) door Gilbert Rattink
ISBN: 90 395 0945 x
NUGI: 854

[ErikRo]

Die heb ik al

[JaQ]

als je wat wilt doen met de return waarden kan ik je zeker native aanraden. Indien je geen return waarden hoeft te hebben (bijvoorbeeld alleen insert, updates of deletes), kan je vanaf oracle 8 ook gewoon zeggen

execute immediate <<statement>>


Klein voorbeeldje van native:
v_sql := ' MAAK HIER JE QUERY OF STUK DYNAMISCH PL/SQL ';

v_cursor := dbms_sql.open_cursor;
dbms_sql.parse (v_cursor
, v_sql
, dbms_sql.native);
dbms_sql.define_column (v_cursor, 1, v_col_1);
dbms_sql.define_column (v_cursor, 3, v_col_2, 80);
dbms_sql.define_column (v_cursor, 5, v_col_3);
dbms_sql.define_column (v_cursor, 6, v_col_4, 80);
v_numrows := dbms_sql.execute (v_cursor);

LOOP
IF dbms_sql.fetch_rows (v_cursor) > 0 THEN
dbms_sql.column_value (v_cursor, 1, v_col_1);
dbms_sql.column_value (v_cursor, 3, v_col_2);
dbms_sql.column_value (v_cursor, 5, v_col_3);
dbms_sql.column_value (v_cursor, 6, v_col_4);
/* DOE NU IETS MET DIE COLUMNS */

END IF;
END LOOP;
dbms_sql.close_cursor (v_cursor);

Ik hoop dat dit wat helpt.