PLSQL procedures and functions
Procedures in PLSQL are recorded in the database. Because of this they are also called "stored procedures". (take care: if you work on a public computer for this laboratory, you need to delete your own procedures due to the fact that they will persist even though you deleted all the files you created).
For example, let's write the following PLSQL procedure:
CREATE OR REPLACE PROCEDURE display AS my_name varchar2(20):='George'; BEGIN DBMS_OUTPUT.PUT_LINE('My name is ' || my_name); END display;
Select the code in SQL developer and "run" the procedure. This will be automatically checked to see if the syntax is correct. If there are no problems then the entire script is stored in the database. If you execute the code later, DB knows that it's already correct and will not check it again. In SQL developer, in the left sidebar you can see that there is a section called "Procedures". You can see your newly compiled procedure in that section (both the name and the sourcecode).
Procedures can be executed only by calling them from an anonymous PL/SQL code.
set serveroutput on; BEGIN display(); END;
To erase a procedure you can drop it:
DROP PROCEDURE display;
The source code of the compiled procedures can be found in the table "USER_SOURCE". Find out the structure of this table and try to find the procedure you just created:
SELECT text FROM user_source WHERE LOWER(name) LIKE 'display';
Beside the USER_SOURCE table you can access two other interesting tables: USER_OBJECTS and USER_PROCEDURES. For now you can discover by yourself what are those tables for (we will come back to the subject later this semester).
Remember: DELETE THE PROCEDURES FROM THE COMPUTERS IN THE LABORATORY.
There are two types of sub-programs that you can create: procedure and functions. Let's get into detail for each of them.
The PL/SQL blocks that you executed up to this point are called anonymous blocks - just because they don't have any name to be called upon. Anonymous blocks are not stored into USER_SOURCE because they don't have a name and that is why nobody could reference/call them anyway.
In anonymous blocks there is a "DECLARE" section, where you can define the variables used in the anonymous block. This section was optional - if there was no variable needed in the block, you can not add a declare section
to declare a stored procedure, we use the following syntax:
CREATE [OR REPLACE] PROCEDURE name_of_procedure [(param1 [behaviour] data_type1, param2 [behaviour] data_type2,... paramN [behaviour] data_typeN)] AS|IS [var1 type1; etc.] BEGIN ..... -- code with instructions END [nume_procedura];
After CREATE there are two optional words (that have to be added together): "OR REPLACE". Without those, the procedure can't be re-written - you need to drop it in order to create a procedure having the same name.
The word PROCEDURE indicates the type of sub-program you want to create. For functions we will use the word FUNCTION. "name_of_procedure" is the identifier for the programmer to be able to call this procedure.
A stored procedure can have more input parameters, output parameters or parameters that can be both input and output. The parameters are given in the round parenthesis following the name of the procedure; they are separated by commas. The behaviour section define the way that parameter is (input, output or both): "IN", "OUT", "IN OUT". If the behaviour is missing it is default considered that the parameter is for input (IN). When you call the procedure, you need to have variables in the same position where the OUT parameters are (not constants or hardcoded values). Also, those variables have to be of the same type as the ones that are define in the header of the procedure. After the procedure is executed, those variables will be populated with the values from the procedure. You can defin the types usint %TYPE and don't forget to add the prefix p_ to the variables inside the procedure - just to have a reminder that those are parameters.
The word AS or IS is telling the compiler that there is the point the procedure starts. After one of these the declaration section follows (we don't need to add the DECLARE keyword this time) and the BEGIN-END section.
A procedure can be called from an anonymous block, from some other procedure or from some application that can interact with the SQL server (e.g from PHP/Java).
Here is a procedure that increments a value that is received as a parameter.
CREATE OR REPLACE PROCEDURE inc (p_val IN OUT NUMBER) AS BEGIN p_val := p_val + 1; END;
set serveroutput on; DECLARE v_number NUMBER := 7; BEGIN inc(v_number); DBMS_OUTPUT.PUT_LINE( v_number ); END;
Explain the error that is given by the following PLSQL anonymous block:
BEGIN inc(7); END;
What about this one ? will this function ?
set serveroutput on; DECLARE v_number varchar2(10) := '7'; BEGIN inc(v_number); DBMS_OUTPUT.PUT_LINE( v_number ); END;
Try to do something similar for a variable having the type date.
Can you build a procedure that has two input parameters and one output parameter that sums the input parameters in the output one? Call this procedure by having numbers instead of variables in the input parameters positions.
As you could see, you can send a varchar2 to the increment procedures and is still going to work. That is because PLSQL knows to automatically convert some data. However, from the point of view of performance, it is better to have a perfect match between the parameters in the call and the ones in the header of the procedure. If you don;t know the type of the parameters, you can use describe followed by the name of the procedure to find out.
Some programming languages allow having a predefined value for input parameters. PLSQL also do this. Let's see an example where we will build a procedure that displays in the console the exponentiation of two numbers:
CREATE OR REPLACE PROCEDURE pow (p_base IN Integer := 3, p_exponent IN INTEGER DEFAULT 5) AS v_result INTEGER; BEGIN v_result := p_base ** p_exponent; DBMS_OUTPUT.PUT_LINE(v_result); END;
In this case the input parameters are initialized. If the procedure is called with two parameters then the values send to the procedure override the ones defined in the header. You can also send only one parameter (not both of them) and in this case, you need to do an call that also specify what parameter do you want to override (use the form key=>value). For example, the following three scripts will have the same effect:
set serveroutput on; BEGIN pow(2, 3); END;
set serveroutput on; BEGIN pow(p_base=>2, p_exponent=>3); END;
set serveroutput on; BEGIN pow(p_exponent=>3, p_base=>2); END;
Can you figure out what are the outputs for the following anonymous blocks ?
set serveroutput on; BEGIN pow(2, p_exponent=>3); END;
set serveroutput on; BEGIN pow(p_base=>2); END;
set serveroutput on; BEGIN pow(p_exponent=>3); END;
Let's see how we call this when we want the output to be sent back to the anonymous block:
CREATE OR REPLACE PROCEDURE pow (p_base IN Integer := 3, p_exponent IN INTEGER DEFAULT 5, p_out OUT Integer) AS BEGIN p_out := p_base ** p_exponent; END;
set serveroutput on; DECLARE v_out INTEGER; BEGIN pow(p_base=>3, p_out => v_out); DBMS_OUTPUT.PUT_LINE(v_out); END;
Fact: A procedure can have a RETURN comand (that will force the exit from that procedure).
Procedures are useful when the PLSQL code return more than only one value. When a single value is returned, you should use functions.
The advantage of creating functions is that they can also be used inside a SQL command (e.g. upper function). There are several types of data that can prevent the usage of a function inside a SQL command (e.g BOOLEAN).
DML (data manipulation language - delete/update/insert) should be avoided in functions. Also don't modify the structure or delete tables. COMMIT/ROLLBACK keywords should also be avoided. Basically a function should only compute something and return something (you can select data though).
The syntax is similar to the procedure (you use FUNCTION instead of PROCEDURE and before IS/AS add the returned type). IN a function it is mandatory to have the RETURN command followed by the value of the same type as the returned type declared in the header of the function (you can still use IN/OUT but if you use OUT you cannot use the function in a SQL query).
Here is a function having a varchar2 as an input parameter and that returns some manipulation of the input string.
CREATE OR REPLACE FUNCTION make_waves(p_input_string varchar2) RETURN varchar2 AS v_index INTEGER; v_result varchar2(1000) := ''; BEGIN FOR v_index IN 1..length(p_input_string) LOOP IF(v_index MOD 2 = 1) THEN v_result := v_result || UPPER(SUBSTR(p_input_string,v_index,1)); ELSE v_result := v_result || LOWER(SUBSTR(p_input_string,v_index,1)); END IF; END LOOP; return v_result ; END;
You can call the function from a query:
select make_waves('Computer Science') from dual;
But also from an anonymous block:
DECLARE v_string VARCHAR2(1000) :='Computer Science'; BEGIN v_string := make_waves(v_string); DBMS_OUTPUT.PUT_LINE(v_string); END;
Not all functions need parameters (e.g. SYSDATE).
You cannot use the syntax key=>value like in procedures.
Functions called in a SELECT command should not contain any DML commands.
Functions called in a UPDATE or DELETE should not use SELECT or DML.
Functions called in a SQL command should not COMMIT nor ROLLBACK.
Functions called in a SQL command cannot contain DDL commands (e.g. CREATE TABLE) or DCL(e.g. ALTER SESSION) because those do an automatic COMMIT.
As in Java (and not only) packages are used for better organizing the source code. They group together functions or procedures that either depend one upon the other or have the same domain of activity (e.g. management of students in a faculty). Packages in PLSQL can group together procedures, functions, variables, cursors and exceptions.
It is build from two entities:
- header specifications - is almost like an interface that tells the user what are the functions in that packages and their parameters. This should be declared at the begin (or before) because you cannot compile the second part without having this header section.
- body - it contains the code for each procedure and function described in the header section. Can contain some other functions and procedures but those will not be available in public domain. You can recompile the body without recompiling the header (if the headers of the functions don't change).
You can use the following syntax for defining the header:
CREATE [OR REPLACE] PACKAGE package_name IS|AS public types public variables (will be NULL if not specified) public specs for procedures and functions END [package_name]
Everything that is here is available to any user that has the EXECUTE right on that package.
CREATE OR REPLACE PACKAGE manager_faculty IS g_today_date DATE:= SYSDATE; CURSOR student_list IS SELECT REGISTRATION_NUMBER, LNAME, FNAME, GROUPNO, YEAR FROM students ORDER BY lname; PROCEDURE add_student (lname students.lname%type, fname students.fname%type); PROCEDURE remove_student (nr students.REGISTRATION_NUMBER%type); END manager_faculty;
You can use the following syntax to build the body:
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS private variables and data types public or private functions and procedures; [BEGIN initialization area] END package_name;
If you want to use a procedure inside some other procedure, you have to define it first. Each procedure defined in the header has to have an implementation in the body section. Here is an example for the previous header:
CREATE OR REPLACE PACKAGE BODY manager_faculty IS faculty_name VARCHAR2(100) := 'Computer Science from IASI'; FUNCTION compute_age (dob DATE) RETURN INT AS BEGIN RETURN FLOOR((g_today_date - dob)/365); END compute_age; PROCEDURE add_student (lname students.lname%type, fname students.fname%type) IS BEGIN DBMS_OUTPUT.PUT_LINE('Call a private function: '|| compute_age (to_date('01/01/1990','DD/MM/YYYY'))); DBMS_OUTPUT.PUT_LINE('Implementation should be done by you:'); END add_student; PROCEDURE remove_student (nr students.REGISTRATION_NUMBER%type) IS BEGIN null; -- it does nothing (yet) END remove_student; END manager_faculty;
In this example, the compute_age function was not exposed to the public. Therefore only functions and procedures inside the package can call it.
Describe on the name of the package will allow you to see what;s in there.
To call a function or a procedure inside the package you can only write it's name. If you want to do it from outside the package (public domain) you need to write the name of the package, a dot and the name of the procedure as in the following example:
set serveroutput on; BEGIN manager_faculty.add_student('Becali', 'Gigi'); END;
To delete a package you can execute:
DROP PACKAGE package_name;
To delete the body do this:
DROP PACKAGE BODY package_name
you cannot delete the header without deleting the body.
Ex. 1 (8 pt):
Build a PLSQL package to manage a Faculty:
It has to have the following:
- (2pt) - a private procedure that will return the age of the student (in years, months and days - you can reuse code from homework 1) of the student;
- (2pt) - procedure that will add a student into the database and emulate some grades and friendships for him.
- (2pt) - a procedure that will remove a student together with his constraints.
- (2pt) - a procedure that will display statistics about a student: matricol number, average of his grades, his grades and the courses, his age (use the private procedure at part 1), his position in a top (for example in his group or in his year), how many friends he has, etc..
Ex. 2 (5 pt - all or nothing):
- (5pt) Build a procedure that runs under 2 seconds and display (via IDs) which are the frinds that has the same trunc(avg(grades)). (e.g. student having ID 1 and an average of 7.25 has as friends the students 2 (8.33), 3 (7.99) and 4 (7.25), you will display the following couples: 1-3 and 1-4). Create a SQL query to prove that also shows the average for a student. There is also a contest in the romanian version of the site... if you want to participate, conditions will be explained at the laboratory.
Hint: use indexes;