Intro - Why PL/SQL
SQL Language is useful for making queries on a database.
Usually, an application (written in PHP, Java, C# etc.) needs to make more than one query in a database. Suppose that the data retrieved by the application only need to be processed somehow and when the result is obtain, it will be written back in the database. This kind of processing does not require the user to interact with the application. If the queries have to be executed serially (because for example the second one depend on the answer of the first one), the application may want to make different queries to the database and the time needed for final result to be obtained may increase. Also, if more than one queries are sent to the database server through the network (if the application is not on the same machine as the database server), all the transfer might make the application to become too slow. PL/SQL comes to solve this problem: it is a scripting language that can be executed directly by the Oracle database server (Postgre SQL has a similar language called PL/pgSQL, MS SQL server has Transact-SQL): it allows the programmer to define scripts that will be executed directly by the Oracle SQL server.
In PL/SQL you can run anonymous block codes or define your own functions and procedures. For example, you can define a function called CMMDC and after that run a SQL query like this:
SELECT CMMDC(val1, val2) FROM pairs_of_number;
CMMDC function, obviously, in not defined into SQL.
PL/SQL was defined by Oracle in the end of the '80s especially to make SQL become more complex without forcing the creation of certain functions. For example, CMMDC function should not be present as a possible function in a language designed for retrieving data from a database.
A PL/SQL sequence of instuctions can define:
- an anonymous block,
- a function,
- a procedure,
- a package,
- the content of a package (body),
- a trigger,
- a type of data.
In order to call a function you need to know it's name. An anonymous block is just a sequence of PL/SQL lines that don't have a name and is formed of three parts:
- a declarative section - where you define the variables, constants, cursors or exceptions. It starts with the keyword "DECLARE";
- an executable section - contains PL/SQL instructions. It always starts with the "BEGIN" keyword and ends with "END;" (; also has to be present);
- an exceptions section - when the block generates errors, they will be treated in this section. It starts with the keyword "EXCEPTION".
The only block that is mandatory is the second one (the executable section).
set serveroutput on; BEGIN DBMS_OUTPUT.PUT_LINE('Tommorow:' || (SYSDATE+1)); END;
set serveroutput on; DECLARE v_message VARCHAR2(50) := 'Hello !'; BEGIN DBMS_OUTPUT.PUT_LINE('Message: ' || v_message ); END;
By default the Oracle DB server only serves applications and because of this reason it usually does not show messages to a console. In order to be able to show messages we need to force this option by writing the command
set serveroutput on;.
The package DBMS_OUTPUT is created especially for helping the debug actions (https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm#BABJCAJA). For now, from this package we used the PUT_LINE command which will display the information that it got as a parametter.
The syntax for declaring variables is:
identifier [CONSTANT] data_type [NOT NULL] [:= expression | DEFAULT expression]
identifier - the name of the variable. It has to start with a letter and can be followed by a number or the following three signs: $, #, _ . Variables should start with v_, constants should start with c_, parametters in a function should start with p_ and there is a limit of 30 characters for the name of the variable. You are not allowed to use any keywords of the language (ALL, CREATE, FROM, MODIFY, SELECT, ALTER, DATE, GROUP, NOT, SYNONYM, AND, DEFAULT, HAVING, NULL, SYSDATE, ANY, DELETE, IN, NUMBER, TABLE, AS, DESC, INDEX, OR, THEN, ASC, DISTINCT, INSERT, ORDER, UPDATE, BETWEEN, DROP, INTEGER, RENAME, VALUES, CHAR, ELSE, INTO, ROW, VARCHAR2, COLUMN, EXISTS, IS, ROWID, VIEW, COMMENT, FOR, LIKE, ROWNUM, WHERE).
Atention: name your variable to have a meaning !
CONSTANT - is the modifier for declaring constants. In this case the value will be assigned in the declaration section and nobody will be able to modify it.
data_type - For now we will redirect you to the data types you already know (here: https://profs.info.uaic.ro/~bd/wiki_en/index.php/Lab_2). However, you should know that there are some other data types on the Oracle server (e.g. vectors, references, objects, LOB, etc.) Here you will also include some constraints (if it's the case). For example, when you define a VARCHAR2 variable, you might want to add it's maximum number of characters (e.g. VARCHAR2(25) will define a variable that can store up to 25 characters).
NOT NULL - variables that are not allowed to be null. They also have to be initialized (or they will be null).
:= - to set a value to a variable. The value can also be the result of evaluation of some expression. The value of a variable that is not initialized with := is NULL by default. However, if you want to change this to something else, you can use the "DEFAULT" keyword.
It is recommended (good programming practices) to initialize your variables.
v_#potatoes NUMBER := 2E4; --20000 potatoes
v_age int := &i_age; --get the age from the keyboard when you run the code
v_movie VARCHAR2(30) := 'The Matrix';
c_pi CONSTANT double precision := 3.141592653;
v_hello VARCHAR2(40) DEFAULT 'Welcome!';
v_students_number NUMBER(3) NOT NULL := 1;
v_PSGBD_is_cool BOOLEAN DEFAULT TRUE;
Recomandations: To be easier to read, declare each variable on a different row (good practices). Avoid using columns names as variables
Visibility domain of a variable (Scope)
Variables declared at the beginning of the PL/SQL code are considered to be global variables. Inside the executable section there can be different declarative sections, this time the variables will be local variables and will be accessible only inside the next BEGIN-END block.
For example, execute the following PL/SQL code:
set serveroutput on; DECLARE v_name VARCHAR2(20):='Christian'; v_age INTEGER:= 21; -- INTEGER is an alias for NUMBER(38,0). BEGIN DBMS_OUTPUT.PUT_LINE('Value of variable v_name is: ' || v_name); -- will display 'Christian' DECLARE v_name NUMBER(3) := 5; BEGIN DBMS_OUTPUT.PUT_LINE('Value of variable v_name is: ' || v_name); -- will display 5 DBMS_OUTPUT.PUT_LINE('Value of age is: ' || v_age); -- will display 21 END; DBMS_OUTPUT.PUT_LINE('Value of variable v_name is: ' || v_name); -- will display 'Christian' END;
Global variables can be accessed inside internal blocks even though there are local variables declared with the same name. For that you need to add a label to the global variable you want to access. The label can be defined like this: "<< label >>".
For example we will use a label called "global" in the next block:
<<global>> DECLARE v_name VARCHAR2(20):='Christian'; BEGIN DBMS_OUTPUT.PUT_LINE('Value of variable v_name is: ' || v_name); -- will display 'Christian' DECLARE v_name NUMBER(3) := 5; BEGIN DBMS_OUTPUT.PUT_LINE('Value of variable v_name is: ' || v_name); -- 5 DBMS_OUTPUT.PUT_LINE('Value of global variable v_name is: ' || global.v_name); --'Christian' END; DBMS_OUTPUT.PUT_LINE('Value of variable v_name is: ' || v_name); -- will display 'Christian' END;
Usually, to differentiate between local and global variable we can add a prefix g_ or l_.
To assign a value to a variable you will use := (two points and an equal).
You can write a value of a VARCHAR2 variable on multiple rows:
DECLARE v_text VARCHAR2(200); BEGIN v_text := 'Felis catus is your taxonomic nomenclature. An endothermic quadruped, carnivorous by nature.'; DBMS_OUTPUT.PUT_LINE(v_text); END;
The types of operators are:
- for comparation,
- for strings.
Those types of operators are already known by you, but to remind them you can execute the following anonymous blocks:
arithmetics (+, -, *, /, **):
set serveroutput on; DECLARE a NUMBER := 10; b NUMBER := 4; BEGIN DBMS_OUTPUT.PUT_LINE('Sum: ' || (a+b)); DBMS_OUTPUT.PUT_LINE('Diference: ' || (a-b)); DBMS_OUTPUT.PUT_LINE('Product: ' || (a*b)); DBMS_OUTPUT.PUT_LINE('Divide: ' || (a/b)); DBMS_OUTPUT.PUT_LINE('Exponentiation: ' || (a ** b)); -- not available in SQL END;
For relational operators (!=, <>, ~=, <, >, <=, >=) :
set serveroutput on; DECLARE a NUMBER := 10; b NUMBER := 4; BEGIN IF (a>b) THEN DBMS_OUTPUT.PUT_LINE(a || '>' || b); ELSE DBMS_OUTPUT.PUT_LINE(b || '>=' || a); END IF; IF (a != b) -- you can also use <> or ~= THEN DBMS_OUTPUT.PUT_LINE('Different values.'); ELSE DBMS_OUTPUT.PUT_LINE('Equal!'); END IF; END;
Comparation (LIKE, BETWEEN, IN, IS NULL):
set serveroutput on; DECLARE a NUMBER := TRUNC(DBMS_RANDOM.VALUE(0,100)); -- you can remove the assignment to get a NULL value; BEGIN IF (a IS NOT NULL) THEN IF (a BETWEEN 20 AND 80) THEN DBMS_OUTPUT.PUT_LINE(a || ' is between 20 and 80'); ELSE DBMS_OUTPUT.PUT_LINE(a || ' not between 20 and 80'); END IF; ELSE DBMS_OUTPUT.PUT_LINE('NULL value'); END IF; END;
Logic operators (AND, OR, NOT): You can try replacing the BETWEEN condition using relational and logical operators to get the same result ?
Priority of the operators is the same as in other programming languages.
In all programming languages you have comments: usually as reminders of what the initial programmer wanted to do with that piece of code, or (as in java) to describe what a function will return.
Comments are ignored by the PL/SQL interpereter.
In PL/SQL you can do single line comments if you start that line with "--" (two minus symbols) or if you want to comment multiple lines use " /* ... */ ".
Taking a single value from a table
In order to get data from a table you will use the select statement. You can get:
- more then one row;
- a single row;
- no rows.
Here we will discuss the second type of result (a single row). The other types of response will be discussed in the future.
If only one row is returned, the values contained by in that row can be stored into different variables. To do that you will need to use the keyword "INTO" and to be sure that the variable type is suited for what you want to store in it.
SELECT fname INTO v_name FROM students WHERE ROWNUM=1
In this case we will store the name of the first student in the table in the variable v_name. To be sure that only one row is returned, we used ROWNUM=1.
If you will use an grouping function on the entire table, you can be sure that only one row is returned:
SELECT AVG(value) INTO v_avg FROM grades
If you want to get multiple values from the row, you can separate the fields and the variables by coma as in the following example:
SELECT fname, lname INTO v_name, v_last_name FROM students WHERE ROWNUM=1 --two different values
If the answer is formed from a single row, you should always use SELECT... INTO...
Declaring variables using %TYPE
In order to be sure that the type of the variable is the same as the returned value, we can define it as having the same type as the column of a table (or the same as an existing variable). Here is an example:
To correctly store the maximum value of the grade into a variable v_value, what should be it's type?
SELECT MAX(value) INTO v_value FROM grades;
You can use the following declaration:
here is the entire anoymous block code:
DECLARE v_value grades.value%TYPE; BEGIN SELECT MAX(value) INTO v_value FROM grades; DBMS_OUTPUT.PUT_LINE('Maximum grade: ' || v_value ); END;
Using %TYPE has another advantage: if you alter the initial table and change the type of the field values than the anonymous code will declare the variable as having the same type as the modified type.
Rewrite the code so you display both the minimum and maximum grade.
Using %TYPE is very important. Declare the variables this way each time you want to get a value from a column !
The functions that you have studied in SQL are also available here.
Here is a small reminder (not containing all SQL functions):
Manipulating strings: ASCII, LENGTH, RPAD,CHR, LOWER, RTRIM,CONCAT, LPAD, SUBSTR, INITCAP, LTRIM, TRIM,INSTR, REPLACE, UPPER.
Manipulating numbers: ABS, EXP, ROUND, ACOS, LN, SIGN, ASIN, LOG, SIN, ATAN, MOD, TAN, COS, POWER, TRUNC.
Manipulating DATEs: ADD_MONTHS, MONTHS_BETWEEN, CURRENT_DATE, ROUND, CURRENT_TIMESTAMP, SYSDATE, LAST_DAY, TRUNC.
Converting between data types
It is done automatically between VARCHAR2 and any other data types. Also it is automatic from a variable of type DATE, NUMBER or PLS_INTEGER to LONG (but not the other way around: LONG is bigger and data loss may occur if it is converted into a smaller data type).
DATE cannot be converted to NUMBER or PLS_INTEGER.
Observation: implicit conversion may be slow and might depend on the operating system (e.g. date can be converted differently to varchar2 - depends on how the operating system represent a date).
Explicit conversions can be achieved using one of the following functions: TO_NUMBER(), ROWIDTONCHAR(), TO_CHAR(), HEXTORAW(), TO_CLOB(), RAWTOHEX(), CHARTOROWID(), RAWTONHEX(), ROWIDTOCHAR(), TO_DATE().
BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'Month DD, YYYY')); END;
1. (6pt) Build a PL/SQL script that will ask the user for a last name (family name). If in the table students there are no students having that name, an error message will displayed; otherwise the script will display the following (each on a different row):
- the number of students having that last name;
- the ID and the first name of the student that is the first one in the alphabetic ordered (order by first name);
- the highest and the lowest grade of that student;
- the number A at the power B where A is the highest grade and B is the smallest one;
2. (4pt) Build an anonymous block containing a declaration of a constant of type varchar2(30) that contains your birth date in the following format "ZZ-LL-YYYY" (e.g. 15-04-1993). By executing the code you will get the number of months and days it passed from that date (e.g. 124 months and 13 days) and also what day of week was that date on (Monday, Tuesday...).