PLSQL 2

From bd_en
Jump to: navigation, search

Control structures

A structured programming language, has to have three types of operations (https://en.wikipedia.org/wiki/Structured_program_theorem):

  • sequence
  • conditions
  • looping

PL/SQL also has those operations. In the first laboratory you met the sequence (although you had an example of conditional branching). Let's discuss the other two:

Conditional control structure

In PLSQL checking some condition is done with the IF - THEN - ELSE - END IF command. After the IF keyword an expression that has a result a boolean value must be present. The format for this command is:


IF boolean_expression THEN 
         some instructions that will be executed if the boolean_expression is true;
   ELSE
         some instructions that will be executed if the boolean_expression is false;
END IF; 

As in other programming languages, the ELSE section can be absent (together with the associated block of instructions). Here is an example:


set serveroutput on;
accept x prompt "Please enter your name: "; -- allows the user to enter a value
DECLARE
   v_number NUMBER(5);
   x NUMBER(5);
BEGIN   
   v_number := &x;
   IF (v_number MOD 2 = 0)
      THEN 
        DBMS_OUTPUT.PUT_LINE('Number is even.');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Number is odd.');
   END IF;
END; 

In the ELSE section you can insert a new condition. For this you can use the ELSIF and the new condition that has to be tested. This section will have a new THEN section and also an ELSE section. However, being part of the initial IF command, it does not have an END IF (except the one from the original IF). Let's see an example where we want to test if a number belongs to an interval:

DECLARE
   v_number NUMBER(5) := 50;
BEGIN
   IF (v_number < 10)
       THEN
           DBMS_OUTPUT.PUT_LINE('The number is less then 10.');
       ELSIF (v_number > 80) THEN
           DBMS_OUTPUT.PUT_LINE('The number is greater then 80.');
       ELSE
           DBMS_OUTPUT.PUT_LINE('The number is in the interval [10,80]');  
   END IF;
END;

You can test a variable against more possible values. In this case you can use the CASE command. If none of the values matches the value of the variable, a DEFAULT section of commands will be executed. For example:

DECLARE
   v_number NUMBER := 5;
BEGIN
   CASE (v_number )
       WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('First natural number bigger then 0.');
       WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('First even natural number.');
       ELSE
           DBMS_OUTPUT.PUT_LINE('Some number that is not 1 or 2');
   END CASE;
END;

The decode function can be emulated with a CASE instruction:

SELECT lname, fname,
CASE scholarship
   WHEN 450 THEN 'more then four hundred'
   WHEN 350 THEN 'more then three hundred'
   WHEN 250 THEN 'more then two hundred'
   ELSE 'No scholarship'
END
FROM students;

CASE command can be used without having an initial operand. In this case, each section will have it's own boolean expression and will be executed only if that expression is evaluated to true:

SELECT lname, fname,
CASE 
   WHEN scholarship>1200 THEN 'RICH'      
   ELSE 'POOR'
END
FROM students;

LOOPING in PLSQL

As in other programming languages, PLSQL Loops can also be with initial conditioning (the first execution of the code will be conditioned by the boolean expression value), with final conditioning (the code will be executed at least once and will repeat if the condition holds true) or with a fixed number of steps (a particular case of initial conditioning loops).

Initial condition loopin can be achieved by using WHILE(boolean_expression) ... END LOOP.

Final condition looping is done by using LOOP ... END LOOP structure.

Looping by a certain number of times is done by using FOR counter IN initial_value..final_value LOOP ... END LOOP;

Let's see some examples:

WHILE
set serveroutput on;
DECLARE
   v_counter INTEGER := 0;
BEGIN
   WHILE (v_counter < 10) LOOP       
       v_counter := v_counter + 1;
       DBMS_OUTPUT.PUT_LINE(v_counter );  
   END LOOP;
END;
LOOP

When using LOOP command, you have to manually exit the loop. This is done by using the EXIT command (actually, in other programming languages this is like a WHILE(true) from where you exit using break; command). The exit command is usually followed by the condition in where you write what condition has to be accomplished to exit the loop (e.g EXIT WHEN (boolean_expression); ). You can also add a label to tell the code where to jump from the loop (when there are more nested loops and you want to exit to a certain loop).


set serveroutput on;
DECLARE
   v_counter INTEGER := 0;
BEGIN
   LOOP
       v_counter := v_counter + 1;
       DBMS_OUTPUT.PUT_LINE(v_counter );  
       EXIT WHEN v_counter = 10;
   END LOOP;
END;
FOR

We can use the FOR command when having an exact number of steps:

set serveroutput on;
DECLARE
   v_counter INTEGER := 0;
BEGIN
   FOR v_counter IN 1..10 LOOP
       DBMS_OUTPUT.PUT_LINE(v_counter );  
   END LOOP;
END;

If you want the FOR command to go in reverse direction, you need to tell this before writing the interval of values: FOR v_counter IN REVERSE 1..10 LOOP .

EXIT can be written in any loop structure to exit it. Let's see a variant where exiting to a certain label is done:

set serveroutput on;
DECLARE
   v_counter1 INTEGER;
   v_counter2 INTEGER;
BEGIN
   <<my_label>>
   FOR v_counter1 IN 1..5 LOOP
       FOR v_counter2 IN 10..20 LOOP
           DBMS_OUTPUT.PUT_LINE(v_counter1 ||'-'||v_counter2 );  
           EXIT my_label WHEN ((v_counter1=3) AND (v_counter2=17));
       END LOOP;
   END LOOP;
END;

The CONTINUE command will skip the remaining lines in the loop and start with the next one. Replace the exit command in the previous code with the following one and test it:

CONTINUE WHEN ((v_counter1=3) AND (v_counter2=13));

How will you do this so you won't display the 3-17 combination ?

A last command that can affect the way a loop executes is GOTO. This has the role to skip to a certain label and is not necessarily intended for loops:

set serveroutput on;
BEGIN
   GOTO my_label;
   DBMS_OUTPUT.PUT_LINE('Won't display.');
   <<my_label>>         
   DBMS_OUTPUT.PUT_LINE('Will display.');
END;

Can you use two GOTO commands to emulate a loop ?

Using cursors

The memory where a query is kept together with the data that represent the result of that query form a cursor. There are two types of cursors: implicit and explicit.

  • The implicit cursor cannot be controlled or manipulated by the user. It is automatically executed by the server and the user can only ask some information about how that was executed.
  • The explicit cursor is defined by the user and he has the control of what it happens to it.


Implicit cursors

Although the programmer don't have direct access to an implicit cursor, he can obtain some information about how it was executed (mostly about the number of rows processed). For this he can use the following attributes of an implicit cursor:

  • SQL%FOUND
  • SQL%NOTFOUND
  • SQL%ROWCOUNT.

Let's consider the following PLSQL code that will modify all the scholarships bigger then 500 (by adding 10):

DECLARE
   v_rows INTEGER;
BEGIN  
   UPDATE students set scholarship = scholarship + 10 WHERE scholarship > 500;
   IF(SQL%FOUND) -- if the command updated at least one row, SQL%FOUND will be true
      THEN
         DBMS_OUTPUT.PUT_LINE('We processed ' || SQL%ROWCOUNT || ' scholarships.');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Nobody got more money.');
    END IF;
END;

Implicit cursors are usually met when updating or deleting rows from the database;

Explicit Cursors

Explicit cursors are declared and used in PLSQL scripts. As we have seen in the previous laboratory, a query can return 0, 1 or more rows. If it returns only one row then selecting it's value to a single variable (or a group of variables if you select more then only one column) is done by using the keyword INTO. We will postpone the discussion about 0 rows returned when we will discuss about exceptions. For now we will proceed to processing multiple rows by the usage of explicit cursors.

There are several steps that have to be followed when you are using an explicit cursor: declaring, opening, fetching lines and closing the cursor.

An explicit cursor is usually defined in the declaration area of the PLSQL block (although that is not a rule). In order to declare an explicit cursor you can use the following format:


DECLARE
   CURSOR   name_of_cursor IS select_command;
   ....

In this case, the select_command is any type of SELECT from SQL language (JOINS, GROUPS, etc.) If the cursor uses any variables, the variables have to be declared before the cursor.

The cursor will be opened in the BEGIN-END section. This is done with OPEN command followed by the name of the cursor to be opened. When the cursor is opened the SELECT command defining the cursor will be executed and the result will be available for the programmer to fetch line by line. A pointer is automatically positioned on the first row in the result and each time you fetch a new row the cursor will skip to the next one.

OPEN name_of_cursor 

Usually after the cursor is opened a LOOP section follows. In this loop each row of the result will be individually fetched and processed. To get the new line you can use the command FETCH followed by the name of the cursor you want to fetch data from (useful when you have two or more cursor opened in the same time). In the end of the fetch command you have to specifiy the variables that will be populated with the values from the fetched row.


FECTH name_of_cursor INTO v_var1, v_var2, v_var3;

To test if you got the last row you have to check the value of the NOTFOUND attribute of the cursor ( name_of_cursor%NOTFOUND ). This will be true when the FETCH operation was not able to get another line from the cursor. This is the moment where you usually exit the loop processing each row:


EXIT WHEN name_of_cursor%NOTFOUND; --Probably immediately after FETCH

In the end, after exiting the loop processing each row you will have to close the cursor. Use CLOSE to do that. By calling again OPEN command, the cursor will be reopened (by re-executing the SELECT command, re-positioning the pointer on the first row of the result, etc.)

Some other attributes an explicit cursor has are:

  •  %ISOPEN,
  •  %FOUND,
  •  %ROWCOUNT.

(tip: display ROWCOUNT inside the loop).

Let's see where is a good place to exit the loop via an example:


DECLARE
   CURSOR list_of_scholarship_owners  IS
       SELECT lname, fname FROM students WHERE scholarship IS NOT NULL;
   v_lname students.lname%type;       
   v_fname students.fname%type;
BEGIN
    OPEN list_of_scholarship_owners  ;
    LOOP
        FETCH list_of_scholarship_owners  INTO v_lname, v_fname ;
        EXIT WHEN list_of_scholarship_owners%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_lname||' '|| v_fname);
    END LOOP;
    CLOSE list_of_scholarship_owners  ;  
END;

Obvious, the list of scholarship owners could be displayed with a simple SELECT. If you want the data to be accessible inside the script then you need to use a cursor (we can for example manipulate each name in the script, not only displaying it).

When you can avoid explicit cursors, do that. FSometimes you don't really need an explicit cursor for taking all the data from a table, change them somehow and then write them to another table. For example:

DROP TABLE people;
CREATE TABLE people(lname Varchar2(10), fname varchar2(10));
BEGIN
   INSERT INTO people SELECT upper(lname), fname FROM students;
   INSERT INTO people SELECT upper(lname), fname FROM instructors;
END;

If the select we used in defining the cursor returns too many columns, they can be all taken in a single variable having the type name_of_cursor%ROWTYPE. In order to access a certain field from a variable of that type, we can add a dot and the name of the field. Here is an example:

DECLARE
   CURSOR student_list IS
       SELECT * FROM students;
   v_std_row student_list%ROWTYPE;       
BEGIN
    OPEN student_list;
    LOOP
        FETCH student_list INTO v_std_row;
        EXIT WHEN student_list%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_std_row.lname||' '|| v_std_row.dob);
    END LOOP;
    CLOSE student_list;  
END;

Explicit cursor are very easy to use in a FOR type of loop. You can automatically open the cursor and in each interation fetch the next row (they are doing automatic fetching). The fields in this line can be uset the same way. The previous code re-writen with a FOR command is:

DECLARE
   CURSOR student_list IS
       SELECT * FROM students;
BEGIN
    FOR v_std_row IN student_list LOOP    
        DBMS_OUTPUT.PUT_LINE(v_std_row.lname||' '|| v_std_row.dob);
    END LOOP;  
END;

Observe that:

  • in a FOR used together with a cursor we don't have the following operations: OPEN, FETCH, CLOSE;
  • we didn't declared a variable of type %ROWTYPE (is automatically declared);
  • the exit is done automatically when there are no more rows to fetch.


You can do even more: you can not declare the cursor at all, just use the select directly into the FOR construction (however you should use round paranthesys):

BEGIN
    FOR v_std_row IN  (SELECT * FROM students) LOOP     
        DBMS_OUTPUT.PUT_LINE(v_std_row.lname||' '|| v_std_row.dob);
    END LOOP;  
END;

An explicit cursor can be declared using parameters (for example because we would want to open the cursor to include only some data - e.g. opening the cursor for the student being first in his year and displaying his grades). For this we can open an explicit cursor having a parameter. The difference is that after the name of the cursor we need to add (in some round parenthesis) the variables that are going to be sued inside the definition of the cursor together with their types. When opening the cursor we will also provide the values for those variables. Here is an example:

DECLARE
   CURSOR scholarship_owners (p_scholarship students.scholarship%type, p_year students.year%type) IS
       SELECT lname, fname FROM students WHERE scholarship > p_scholarship AND year > p_year;
   v_std_row scholarship_owners%ROWTYPE;       
BEGIN
    OPEN scholarship_owners (300,2); -- the values here can be computed by some other PL/SQL code
    LOOP
        FETCH scholarship_owners INTO v_std_row;
        EXIT WHEN scholarship_owners%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_std_row.lname||' '|| v_std_row.fname);
    END LOOP;
    CLOSE scholarship_owners;  
END;

Declaring explicit cursor with FOR UPDATE clause

If while processing the rows we want to modify some data (or even delete the entire row), after declaring the cursor we can use the keywords "FOR UPDATE". This will lock the database, and restrict the access of some other users that want to modify the table selected by the cursor but will allow reading of data for those users. After the "FOR UPDATE" keywords you can specify what are the fields that you want to modify. This is done by using the OF keyword and the names of the columns to be updated (this will only restrict that columns).

If the table is already opened for update by some other cursor, you can specify a number of seconds for the script to pause and retry the reopening of the cursor; if it fails the second time an error will be returned. Adding a number of seconds is done by the use of the keyword WAIT and the number of seconds (or you can use NOWAIT if you want the error to be returned the first time).

To modify the current row, after the command updating the data (or deleting the row) you have to add the keywords "WHERE CURRENT OF" followed by the name of the cursor where the update will be done.

For example, here is an cursor that updates all the students having some problems with passing at some course:

DECLARE 
   CURSOR update_grades IS
      SELECT * FROM grades FOR UPDATE OF value NOWAIT;
BEGIN
   FOR v_row IN update_grades LOOP
       IF (v_row.value < 5) 
            THEN 
                 UPDATE grades SET value=5 WHERE CURRENT OF update_grades;
       END IF;
   END LOOP;
END;


The update in the code will modify the value in the table, not in the cursor.


Exercices

1. (4pt) Build an anonymous PLSQL code that will populate a table containing two fields A and B this way: The value of field A will be a number in 1..10000 interval that have the sum of all digits modulo 9 equal to a value declared as a constant in the declaration section of the script (obviously it will have a value smaller then 9). The field B will be 0 or 1: 0 when the number is not a prime number and 1 when the number is prime.

2. (2pt) Using an explicit cursor opened with the FOR UPDATE clause, build a script that will update the values in field B (from exercice 1) such that instead displaying the primality of the number it will show if the number is or isn't a Fibonacci number. The script will display the number of updated values (the number of values that changed from 0 to 1 plus the number of values that changed from 1 to 0).

3. (4pt) Without using any grouping function or join, cartesian product, display the name, the grades and the courses where those grades are taken of the best student in the faculty that has at least three grades. If there are two best students, display the one that is in the higher year or, if they are both in the same year, display the one that is the first in alphabetical order.


Test the last exercise by using the following query:

select T1.id, T1.fname, T1.lname, T1.year, c.course_title, n.value, T1."avg" from ( select * from (select  s.id, s.fname, s.lname, s.year, 
avg(n.value) as "avg" from students s join grades n on n.id_student=s.id group by s.id, s.fname, s.lname, s.year, s.fname, s.lname
having count(n.value) >= 3 order by avg(n.value) desc, s.year desc, s.fname asc, s.lname asc ) where rownum = 1) T1 join grades n on
n.id_student = T1.id join courses c on c.id = n.id_course;