PL/SQL Tutorials ? CONCEPTS
PL/SQL is an acronym to Procedural Language/Structured Query Language.
When the business logic to be attained is not achievable easily using SQL which is 4th Generation Language, Oracle turns to the Procedural Language structures and achieves that business logic and achieves what business is looking for.
In the process of evolution of PL/SQL it has become more robust and multi-faceted.
a. DML to insert, update, delete the data into Oracle Tables
b. DDL to create, alter, drop objects in a schema
c. DCL to create save point and to rollback or commit the work done to a certain desired state. And also enables to take control of the situation in case one or other process within the PLSQL fails and there is a need to rollback to a position where the data integrity maintained and before process state for data is restored.
d. DQL to save data for a given process into variables
e. To call other functions, procedures, packages from within the procedures, functions, packages, triggers.
f. PLSQL owned security mechanism to protect the intrusions
g. To use all the utilities and built in packages as provided by Oracle
h. Triggers, even though are part of PLSQL are of different gene and they are event driven in their nature. They are the backbone of Oracle Forms and they are used in RDBMS to achieve data integrity, data auditing, database auditing, to create a source safe mechanism and many more requirements. But they are the most dangerous creatures that reside within Oracle RDBMS and work behind backs of DBA, users and consumers of data. Some RDBMS have used triggers for data integrity and validation like constraints. In Oracle RDBMS maintaining the data integrity and validation is the job of constraints and using triggers for that purpose leads to performance. There is an issue mutating triggers in Oracle as a result of recursive dependency of one activity on the other recursively. This is an issue with the developers and this, they are to handle carefully.
i. PL/SQL collections, records and object types enable development of Object Oriented Relational Database Management System more comfortably getting out of many procedural issues.
Understanding main features of PLSQL in the words of Oracle:
PLSQL is in Blocks. Block structure look like as under:
The above is an example of an anonymous PLSQL block. This means that this PLSQL has no name and can not be stored in the database as an PLSQL Object.
Between ?declare? and ?begin? key words the user has been given the opportunity to create all types of variables as permitted by PLSQL.
Variables are a type of containers that allow you to identify the container with a PLSQL identifiable datatypes, attributes, constants, cursors, also with assigned values. The values can be assigned to these variables with in the BEGIN and END control blocks.
Here is listing of the data types that are identifiable within PLSQL:
DATATYPE MEMBER SUB-MEMBER SIZE/LENGTH REMARKS
NUMBER BINARY -2**31 to 2**31
BINARY_INTEGER Less storage than NUMBER. Operations are slower than pls_integer
NATURAL non-negative or positive values only
NATURALN prevent the assigning of nulls to an integer variable
POSITIVE non-negative or positive values only
POSITIVEN prevent the assigning of nulls to an integer variable
SIGNTYPE values -1, 0, and 1
NUMBER Its magnitude range is 1E-130 thru 10E1
DOUBLE PRECISION 126 binary digits (roughly 38 digits)
FLOAT 126 binary digits (roughly 38 digits)
REAL 63 binary digits
PLS_INTEGER values range from 2**31 to 2**31 Less storage than NUMBER.
CHARACTER Maximum size up to 32767 bytes. This is fixed length
CHAR maximum width 2000 bytes
LONG and LONG RAW size of a LONG and LONG RAW value is 32760 bytes
RAW Max size up to 32767 bytes
VARCHAR2 Max size up to 32767 bytes
STRING For compatibility with IBM and ANSI
VARCHAR For compatibility with IBM and ANSI
NATIONAL CHARACTER TYPES NCHAR NCHAR maximum size Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the AL16UTF16 encoding, and 32767/3 in the UTF8 encoding.
BFILE The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 - 1 bytes). BFILEs are read-only, so you cannot modify them.
BLOB The size of a BLOB cannot exceed four gigabytes
CLOB The size of a CLOB cannot exceed four gigabytes.
NCLOB The size of an NCLOB cannot exceed four gigabytes.
BOOLEAN TYPE BOOLEAN Values are TRUE, FALSE, and NULL. You cannot insert the values TRUE and FALSE into a database column. Also, you cannot select or fetch column values into a BOOLEAN variable.
DATE and INTERVAL TYPES DATE Valid dates range from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL pre-allocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2 (2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.
ATTRIBUTES for the Variables
Apart from these datatypes, the variables can be declared using ATTRIBUTES
%rowtype which means that an entire row in a table has been identified as a variable
Example emprec scott.emp%rowtype; -- declare record variable
%type which means that the variable?s datatype is to be that of the referenced table.column datatype.
Example var_ename scott.emp.ename%type;
credit_limit CONSTANT REAL := 5000.00;
CURSORS in the declare section
Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:
CURSOR c1 IS
SELECT empno, ename, job FROM emp WHERE deptno = 20;
The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria
Cursor FOR Loops
In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. In the following example, the cursor FOR loop implicitly declares emp_rec as a record:
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
FOR emp_rec IN c1 LOOP
salary_total:= salary_total + emp_rec.sal;
To reference individual fields in the record, you use dot notation, in which a dot (.) serves as the component selector.
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database. This gives you more flexibility and a convenient way to centralize data retrieval.
Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. The following procedure opens the cursor variable generic_cv for the chosen query:
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp, choice NUMBER) IS
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;