PLSQL 9

From bd_en
Jump to: navigation, search

DBMS_SQL

When a PLSQL block is run, besides its syntactic verification, the existence of the entities that appear within the block is tested in an attempt to link the existing variables in the code to the actual entities they represent: for example the existence of tables or columns that are interrogated within a cursor is verified in order to associate the cursor with the table or with certain columns in that table. Because of this, we will not be able to build a table inside a script, the verification phase would test the existence of that table and the PLSQL block would generate an error.


The cursors or queries you have encountered so far in PLSQL scripts are static, because they do not change from one script execution to another.


The DBMS_SQL package is used to dynamically generate SQL queries - queries whose text is not known before the actual execution of the code (either because I do not know which table I will make the selection or which column will be used for data filtering - obviously, if it was the value of a column, you could do this by using cursors with parameters that are still static).


A static execution could also be executed dynamically (as if it were not known in advance). However, when queries are statically executed, the server is allowed to verify in advance if the entities used in the script actually exist, if the user has enough rights over these entities, and, because of this, static execution is generally more efficient than the dynamic one - do not use dynamic executions unless you really need it.


For example, in order to export all the tables from database, you first need to find out which tables are (this is quite simple - by querying the "user_tables" table; however, after this step you must take the data from each table and export them one at a time to a file - and you must do this without knowing in advance which tables you need to export - because their names are still in the script). Another example would be to sort a table after a field chosen by the user (you can not do this in static SQL because it involves a column that is not known in advance). In addition to all DML commands you can dynamically execute, you can run DDL commands (CREATE, DROP, ALTER, GRANT, REVOKE) or SCL-session control language commands (ALTER SESSION or SET ROLE).


DDL command execution

Classically, when executing an SQL command from a PLSQL script, a cursor opens, executes, and so on. Often these things are done by default (for example, when you insert an insert in a table you just write the insert ...).


When you want to run a command via DBMS_SQL there are a few steps you must follow, as they are not executed by default. Here's a minimum:


  • Opening a cursor (returns an ID by which the cursor will be used);
  • Parsing the command to verify its accuracy;
  • Associating variables with certain values (if applicable);
  • Execution of the command (returns a success / error code);
  • Closing the cursor.


For example, let's create a table using a PLSQL block, following the steps outlined above:

declare
  v_cursor_id INTEGER;
  v_ok INTEGER;
begin
 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_cursor_id, 'CREATE TABLE TEST(id NUMBER(2,2), val VARCHAR2(30))', DBMS_SQL.NATIVE);
 v_ok := DBMS_SQL.EXECUTE(v_cursor_id);
 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
end; 
 <nowiki>

These steps are useful when executing a command such as create, drop, insert, update, delete, etc.




== SELECT execution ==
When you want to execute a select query, you need to have some other intermediate steps that will allow you to take over the data returned by the command.


The steps we need to take in this case are:


* Opening a cursor (returns an ID by which the cursor will be used);
* Parsing the command to verify its accuracy;
* Associating variables with certain values (if applicable);
* Defining the columns that should be returned (by using the command DEFINE_COLUMN);
* Executing the command (returning a succes / error code);
* Using of the command FETCH_ROWS in order to take over the rows from the cursor;
* Using VARIABLE_VALUE in order to take over the returned value - if there is only one returned value - or COLUMN_VALUE - to take over the columns from the cursor and store them in local variables;
* Closing the cursor.


Let's see, as an example, a sort of Instructors table using a field sent as a parameter.
For now, consider the following procedure (in which only the initial steps are implemented) to see the usefulness of the command parsing function:

 <nowiki>
create or replace procedure display_instructors(field IN varchar2) as
  v_cursor_id INTEGER;
  v_ok INTEGER;
begin
 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_cursor_id, 'SELECT id, lname, fname FROM instructors ORDER BY '||field, DBMS_SQL.NATIVE);
 v_ok := DBMS_SQL.EXECUTE(v_cursor_id);
 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
end;
/
 <nowiki>
Considering the next two anonymous blocks, one of them will generate an error (ie, when the command parade will come). Can you guess which of them?

 <nowiki>
begin
 display_instructors('lname2');
end;
 <nowiki>
 <nowiki>
begin
 display_instructors('lname');
end;
 <nowiki>

Let's redefine the display procedure so it will take the values in the selected columns (id, lname, fname) and display them. For this we need to declare variables that are of the same type as the ones in the instructors table, and after we have parsed the command to associate each variable with the column it represents. After executing the command (with EXECUTE) we have to take the information we obtained row by row and display:

 <nowiki>
create or replace procedure display_instructors(field IN varchar2) as
  v_cursor_id INTEGER;
  v_ok INTEGER;
  
  v_id_instructor int;
  v_lname_instructor varchar2(15);
  v_fname_instructor varchar2(30);
begin
 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_cursor_id, 'SELECT id, lname, fname FROM instructors ORDER BY '||field, DBMS_SQL.NATIVE);
 DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, v_id_instructor); 
 DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 2, v_lname_instructor,15); 
 DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 3, v_fname_instructor,30);   
 v_ok := DBMS_SQL.EXECUTE(v_cursor_id);
 
 LOOP 
    IF DBMS_SQL.FETCH_ROWS(v_cursor_id)>0 THEN 
        


DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, v_id_instructor);      
DBMS_SQL.COLUMN_VALUE(v_cursor_id, 2, v_lname_instructor); 
         DBMS_SQL.COLUMN_VALUE(v_cursor_id, 3, v_fname_instructor); 

        DBMS_OUTPUT.PUT_LINE(v_id_instructor || '   ' || v_lname_instructor || '    ' || v_fname_instructor);
     ELSE 
       EXIT; 
     END IF; 
 END LOOP;   
 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
end;
/
 <nowiki>



Run the anonymous block that did not generate an error (of the two above) again to see a sort by the name field of the instructors table.
Here's how we get from the cursor the properties of each column. Using the next anonymous block we will only list column names:

 <nowiki>
DECLARE
 v_cursor_id NUMBER;
 v_ok        NUMBER;
 v_rec_tab     DBMS_SQL.DESC_TAB;
 v_nr_col     NUMBER;
 v_total_columns    NUMBER; 
BEGIN
 v_cursor_id  := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE(v_cursor_id , 'SELECT * FROM students', DBMS_SQL.NATIVE);
 v_ok := DBMS_SQL.EXECUTE(v_cursor_id );
 DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_total_columns, v_rec_tab);

 v_nr_col := v_rec_tab.first;
 IF (v_nr_col IS NOT NULL) THEN
   LOOP
     DBMS_OUTPUT.PUT_LINE(v_rec_tab(v_nr_col).col_name);
     v_nr_col := v_rec_tab.next(v_nr_col);
     EXIT WHEN (v_nr_col IS NULL);
  


    END LOOP; 
  END IF;
 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
END;
/
 <nowiki>



As we learned the name using the col_name property, we can find out more about the columns. Try the following attributes yourself: col_type, col_max_len, col_name_len, col_schema_name, col_schema_name_len, col_precision, col_scale, col_null_ok (the last one can be true or false and can not be displayed but only queried).


You can find more information in the DESC_REC sections on the page
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#CHDCIIBF .




== Exercise ==
1. (5pt) Build a PLSQL procedure with the name of a table as a parameter. The procedure should export all the lines of that table to an SQL file.
* At the beginning of the file there will also be a drop table command, respectively a table creation table. The data types will be the same as those in the original table;
* In addition, the initial constraints (uniqueness, primary keys, not null) will be added;
* There will be written the insert commands that will populate the created table.
Bonus (2pt): Export the tables to which the exported table depends. The export will be done in the same file, and for these tables you must generate drop and create commands and in the file the popup commands of these tables will be placed before the export of the table given as a parameter of the procedure (from above). In this way, if you export for example the grades table, the courses and students tables will be exported in the same file (the way you find the links between the tables represent the individual research work - yours: D).
2. (3pt) Build the catalog of each subject separately. Script must run correctly after adding a new item or removing an existing one. The catalog will include the grade, the date of the notary, the name, the first name and the id of the student who took the grade.


3. (2pt) Fix the last PHP issue: create a web form with a PHP code that DOES NOT allow SQL injection.


Warning: Using the EXECUTE IMMEDIATE command in any of the problems results in a 3-point penalty.