A structured programming language, has to have three types of operations (https://en.wikipedia.org/wiki/Structured_program_theorem):
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:
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;
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;
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 ?
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.
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:
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 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.
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:
(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;
- 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.
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;