PLSQL 3

From bd_en
Jump to: navigation, search

PLSQL procedures and functions

Registering procedures

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.

Procedures

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).

Functions

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.

Packages

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).

header

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.

Example:

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;

Body

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.

Exercices

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;