skandlo
30 de Diciembre del 2005

Hola, estoy realizando un procedimiento almacenado que realiza varias inserciones a la base de datos Oracle segun los argumentos que le llegan.

¿Sería bueno utilizar SQL Dinámico?

Es que he leido que el SQL Dinámico es util utilizarlo en este tipo de casos

Gracias de antemano

a
30 de Diciembre del 2005

y como lo haces ahora?

andrewsreid
30 de Diciembre del 2005

El SQL dinámico es muy útil.
Se puede hacerlo en Oracle 7 con DBMS_SQL y en Oracle 8 con EXECUTE IMMEDIATE.
Te permite a emplear DDL (DROP TABLE, CREATE TABLE etc) en tu PL/SQL.
También te permite a hacer SELECT, UPDATE etc en una tabla cuyo nombre se desconoce cuando empieza el PL/SQL.
Pero es bastante complicado.
Si puedes hacer lo que quieres hacer sin utilizarlo éste sería mejor.
Aquí están unos ejemplos de SQL dinámico que escribí hace mucho tiempo.
Tengo más pero no hay sitio aquí y mis descripciones son en inglés y éste es un sitio español.

SQL> @dynamic1.sql
SQL> --
SQL> -- Examples of dynamic SQL using DBMS_SQL:
SQL> --
SQL> DECLARE
2 CID INTEGER;
3 ROWS_PROCESSED INTEGER;
4 DISPLAY_DATE DATE;
5 DYNAMIC_SQL_STATEMENT VARCHAR2(200);
6 --
7 /* Parse and execute dynamic SQL statement to list the contents of a table. */
8 PROCEDURE DISPLAY_TABLE(LIST_NO NUMBER) IS
9 BEGIN
10 DYNAMIC_SQL_STATEMENT := 'SELECT MY_DATE FROM DYNAMIC_SQL_TABLE';
11 DBMS_SQL.PARSE(CID, DYNAMIC_SQL_STATEMENT, DBMS_SQL.V7);
12 DBMS_SQL.DEFINE_COLUMN(CID,1,DISPLAY_DATE);
13 ROWS_PROCESSED := DBMS_SQL.EXECUTE(CID);
14 WHILE DBMS_SQL.FETCH_ROWS(CID) > 0
15 LOOP
16 DBMS_SQL.COLUMN_VALUE(CID,1,DISPLAY_DATE);
17 DBMS_OUTPUT.PUT_LINE('('||LIST_NO||') '||DISPLAY_DATE);
18 END LOOP;
19 END;
20 BEGIN
21 --
22 /* Open a new cursor and return its ID. */
23 CID:= DBMS_SQL.OPEN_CURSOR;
24 --
25 /* Parse and execute a dynamic SQL statement to create a table. */
26 DYNAMIC_SQL_STATEMENT := 'CREATE TABLE DYNAMIC_SQL_TABLE'||
27 ' AS SELECT SYSDATE MY_DATE FROM DUAL';
28 DBMS_SQL.PARSE(CID, DYNAMIC_SQL_STATEMENT, DBMS_SQL.V7);
29 DISPLAY_TABLE(1);
30 --
31 /* Insert an extra date in the table. */
32 DYNAMIC_SQL_STATEMENT := 'INSERT INTO DYNAMIC_SQL_TABLE VALUES (''25-DEC-04'')';
33 DBMS_SQL.PARSE(CID, DYNAMIC_SQL_STATEMENT, DBMS_SQL.V7);
34 ROWS_PROCESSED := DBMS_SQL.EXECUTE(CID);
35 DISPLAY_TABLE(2);
36 --
37 /* Update one of the rows in the table. */
38 DYNAMIC_SQL_STATEMENT := 'UPDATE DYNAMIC_SQL_TABLE '||
39 'SET MY_DATE =''26-DEC-04'' WHERE MY_DATE = ''25-DEC-04''';
40 DBMS_SQL.PARSE(CID, DYNAMIC_SQL_STATEMENT, DBMS_SQL.V7);
41 ROWS_PROCESSED := DBMS_SQL.EXECUTE(CID);
42 DISPLAY_TABLE(3);
43 --
44 /* Delete a row from the table. */
45 DYNAMIC_SQL_STATEMENT := 'DELETE FROM DYNAMIC_SQL_TABLE '||
46 'WHERE MY_DATE = ''26-DEC-04''';
47 DBMS_SQL.PARSE(CID, DYNAMIC_SQL_STATEMENT, DBMS_SQL.V7);
48 ROWS_PROCESSED := DBMS_SQL.EXECUTE(CID);
49 DISPLAY_TABLE(4);
50 --
51 /* Parse and execute a dynamic SQL statement to drop a table. */
52 DYNAMIC_SQL_STATEMENT := 'DROP TABLE DYNAMIC_SQL_TABLE';
53 DBMS_SQL.PARSE(CID, DYNAMIC_SQL_STATEMENT, DBMS_SQL.V7);
54 /* Close the cursor */
55 DBMS_SQL.CLOSE_CURSOR(CID);
56 END;
57 /
(1) 06-MAY-05
(2) 06-MAY-05
(2) 25-DEC-04
(3) 06-MAY-05
(3) 26-DEC-04
(4) 06-MAY-05

PL/SQL procedure successfully completed.

SQL> @dynamic2.sql
SQL> --
SQL> -- Examples of dynamic SQL using EXECUTE IMMEDIATE:
SQL> --
SQL> DECLARE
2 DISPLAY_DATE DATE;
3 DYNAMIC_SQL_STATEMENT VARCHAR2(200);
4 --
5 /* Parse and execute dynamic SQL statement to list the contents of a table. */
6 PROCEDURE DISPLAY_TABLE(LIST_NO NUMBER) IS
7 TYPE MY_CURSOR_TYPE IS REF CURSOR;
8 MY_CURSOR MY_CURSOR_TYPE;
9 BEGIN
10 DYNAMIC_SQL_STATEMENT := 'SELECT MY_DATE FROM DYNAMIC_SQL_TABLE';
11 OPEN MY_CURSOR FOR DYNAMIC_SQL_STATEMENT;
12 LOOP
13 FETCH MY_CURSOR INTO DISPLAY_DATE;
14 EXIT WHEN MY_CURSOR%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE('('||LIST_NO||') '||DISPLAY_DATE);
16 END LOOP;
17 END;
18 BEGIN
19 --
20 /* Parse and execute a dynamic SQL statement to create a table. */
21 DYNAMIC_SQL_STATEMENT := 'CREATE TABLE DYNAMIC_SQL_TABLE'||
22 ' AS SELECT SYSDATE MY_DATE FROM DUAL';
23 EXECUTE IMMEDIATE DYNAMIC_SQL_STATEMENT;
24 DISPLAY_TABLE(1);
25 --
26 /* Insert an extra date in the table. */
27 DYNAMIC_SQL_STATEMENT := 'INSERT INTO DYNAMIC_SQL_TABLE VALUES (''25-DEC-04'')';
28 EXECUTE IMMEDIATE DYNAMIC_SQL_STATEMENT;
29 DISPLAY_TABLE(2);
30 --
31 /* Update one of the rows in the table. */
32 DYNAMIC_SQL_STATEMENT := 'UPDATE DYNAMIC_SQL_TABLE '||
33 'SET MY_DATE =''26-DEC-04'' WHERE MY_DATE = ''25-DEC-04''';
34 EXECUTE IMMEDIATE DYNAMIC_SQL_STATEMENT;
35 DISPLAY_TABLE(3);
36 --
37 /* Delete a row from the table. */
38 DYNAMIC_SQL_STATEMENT := 'DELETE FROM DYNAMIC_SQL_TABLE '||
39 'WHERE MY_DATE = ''26-DEC-04''';
40 EXECUTE IMMEDIATE DYNAMIC_SQL_STATEMENT;
41 DISPLAY_TABLE(4);
42 --
43 /* Parse and execute a dynamic SQL statement to drop a table. */
44 DYNAMIC_SQL_STATEMENT := 'DROP TABLE DYNAMIC_SQL_TABLE';
45 EXECUTE IMMEDIATE DYNAMIC_SQL_STATEMENT;
46 END;
47 /
(1) 06-MAY-05
(2) 06-MAY-05
(2) 25-DEC-04
(3) 06-MAY-05
(3) 26-DEC-04
(4) 06-MAY-05

PL/SQL procedure successfully completed.