PL/SQL, JDBC and PHP

1. Introduction:

In a relational database, like Oracle Database Express Edition (XE), we 
can store and retrieve information. A database, as a database server, manages information. 
In a relational database, information are organized into structures called tables. Each 
table contains rows (records) that are composed of columns (fields). The tables are 
stored in the database in logical structures called schemas.

Structured Query Language (SQL) is a nonprocedural programming language that 
enables to access a relational database. Using SQL statements, we can query tables to 
display data, create objects, modify objects, and perform administrative tasks.The SQL 
language compiler generates a procedure to navigate the database and perform the desired 
task.

PL/SQL is an Oracle's procedural language extension to SQL . 
With PL/SQL, we can create procedures, functions, and packages. 
PL/SQL program units are constructed as anonymous blocks, stored functions, 
stored procedures, and packages.

1.1. An anonymous block used in an application is not named or stored in the 
database. It groups the related declarations and statements.

1.2.A procedure or function are stored in the database and can be called by name 
from an application. Functions return a value when executed, whereas procedures are 
void. 
A procedure follows some rules. It starts with BEGIN and finish with END.

1.3. A package is a group of procedures, functions, and variable definitions stored 
in the database. They can be called from other packages, procedures, or functions. 

1.4. A database trigger is a stored procedure associated with a database table, view, 
or event. The trigger can be called after the event, to record it, or take some 
follow-up action. The trigger can also be called before an event to fix new data.

2. Examples

2.1 Example 1:PL/SQL: Oracle database and JDBC

We need to CONNECT to the Oracle database:
2.1.1. Connecting to Oracle 10g Database:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 28 09:37:14 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> connect
Enter user-name: System
Enter password:
Connected.
SQL>

Then CREATE a table:
2.1.2. Creating a table: Physicists:

SQL> CREATE TABLE Physicists(
  2  phys_id NUMBER(3,0) NOT NULL,
  3  first_name VARCHAR2(10) NOT NULL,
  4  last_name VARCHAR2(15),
  5  birth_date NUMBER(4,0),
  6  death_date NUMBER(4,0),
  7  discipline VARCHAR2(20));
Table created.

And INSERT values in the table:
2.1.3. Fill out the table:

SQL> INSERT INTO Physicists VALUES (101,'Joseph','Thomson',1856,1940,'atomic');
1 row created.
SQL> INSERT INTO Physicists VALUES (102,'Ernest','Rutherford',1871,1937,'nuclear');
1 row created.
SQL> INSERT INTO Physicists VALUES (103,'Max','Planck',1858,1947,'radiation');
1 row created.
SQL> INSERT INTO Physicists VALUES (104,'Albert','Einstein',1879,1955,'relativity');
1 row created.
SQL> INSERT INTO Physicists VALUES (105,'Niels','Bohr',1885,1962,'atomic');
1 row created.
SQL> INSERT INTO Physicists VALUES (106,'Louis','DeBroglie',1892 1987,'wave_mechanics');
1 row created.
SQL> INSERT INTO Physicists VALUES (107,'Erwin','Schrodinger',1887,1961,'quantum_mechanics');
1 row created.
SQL> INSERT INTO Physicists VALUES (108,'Maurice','Dirac',1902,1984,'atomic');
1 row created.
SQL> INSERT INTO Physicists VALUES (109,'Ludwig','Boltzmann',1844,1906,'statistical_physics');
1 row created.
SQL>


2.1.4. List the Physicists:

SQL> select * from Physicists;
   PHYS_ID FIRST_NAME LAST_NAME       BIRTH_DATE DEATH_DATE DISCIPLINE
---------- ---------- --------------- ---------- ---------- --------------------
       101 Joseph     Thomson               1856       1940 atomic
       102 Ernest     Rutherford            1871       1937 nuclear
       103 Max        Planck                1858       1947 radiation
       104 Albert     Einstein              1879       1955 relativity
       105 Niels      Bohr                  1885       1962 atomic
       106 Louis      DeBroglie             1892       1987 wave_mechanics
       107 Erwin      Schrodinger           1887       1961 quantum_mechanics
       108 Maurice    Dirac                 1902       1984 atomic
       109 Ludwig     Boltzmann             1844       1906 statistical_physics
9 rows selected.
SQL>


2.1.5. PL/SQL: Create a package:

In this package, we simply set some specifications for the 
cursor and let a variable, in the case we need it:

SQL> CREATE OR REPLACE PACKAGE my_var_pkg AS
  2  TYPE phys_refcur_typ IS REF CURSOR RETURN Physicists%ROWTYPE;
  3  TYPE my_refcur_typ IS REF CURSOR;
  4    my_var_pi                NUMBER := 3.14016;
  5  END my_var_pkg;
  6  /

Package created.
SQL>

2.1.6. PL/SQL:Create a procedure:

This PL/SQL procedure uses the created package my_var_pkg and 
its type my_refcur_typ. It is a function that takes three (1,2,3) 
string parameters the first two (1 and 2) are defined in the table 
Physicists; and will be passed to the procedure as command-line arguments; 
the third (3) will play the role of an output parameter. It is the 
REF CURSOR parameter that it will be returned from the procedure . We also 
set the equivalence between the upper and lower cases and set as one the 
number of characters to input while Oracle will be able to find the entire 
related word.

The procedure function will return physicist info based on his/her first 
and last names.

CREATE OR REPLACE PROCEDURE get_phys_info (?,?,?) 
SQL> CREATE OR REPLACE PROCEDURE get_phys_info (firstname IN VARCHAR2,
2  lastname IN VARCHAR2, phys_cursor IN OUT my_var_pkg.my_refcur_typ) AS
3  BEGIN
4  OPEN phys_cursor FOR SELECT phys_id, first_name, last_name, birth_date, death_date,
5  discipline FROM Physicists
6  WHERE SUBSTR(UPPER(first_name), 1, LENGTH(firstname)) = UPPER(firstname)
7  AND SUBSTR(UPPER(last_name), 1, LENGTH(lastname)) = UPPER(lastname);
8  END get_phys_info;
9  /
Procedure created.
SQL>