My notes and Other stuff:: November 2008

Reference:
http://www.comp.nus.edu.sg/~ooibc/courses/sql/plsql.htm

Overview

PL/SQL is the Oracle's extension to SQL with design features of programming languages. The data manipulation and query statements are included in the procedural units of codes. PL/SQL allows the applications to be written in a PL/SQL procedure or a package and stored at Oracle server, where these PL/SQL codes can be used as shared libraries, or applications, thus enhancing the integration and code reuse. Moreover, the Oracle server pre-compiles PL/SQL codes prior to the actual code execution and thus improving the performance.
The basic PL/SQL code structure is :

DECLARE -- optional, which declares and define variables, cursors and user-defined exceptions.
BEGIN -- mandatory

- SQL statements
- PL/SQL statements

EXCEPTION -- optional, which specifies what actions to take when error occurs.
END; -- mandatory

For example, the following PL/SQL code block declares an integer v1, assigns it with value 3 and print out the value.

DECLARE
v1 NUMBER(3);

BEGIN
v1 := 3;
DBMS_OUTPUT.PUT_LINE('v1=' || v1);
END;

Note that DBMS_OUTPUT is an Oracle-supplied PL/SQL package and PUT_LINE is one of the packaged procedures. It displays the values on the SQL*Plus terminal which must be enabled with SET SERVEROUTPUT ON first. To execute this code sample, login into SQL*Plus, and type

SQL> SET SERVEROUTPUT ON
DECLARE
v1 NUMBER(3);

BEGIN
v1 := 3;
DBMS_OUTPUT.PUT_LINE('v1= ' || v1);
END;
/

Note that a PL/SQL block is terminated by a slash / or a line byitself.

Handling Variables

Variables must be declared first before the usage. The PL/SQL variables can be a scalar type such as DATE, NUMBER, VARCHAR(2), DATE, BOOLEAN, LONG and CHAR, or a composite type, such array type VARRAY.
Only TRUE and FALSE can be assigned to BOOLEAN type of variables.
AND, OR, NOT operators can be used to connect BOOLEAN values.
% TYPE attribute can be used to define a variable which is of type the same as a database column's type definition.
Users can customize the variable types by using TYPE ... IS ... statement.

The following code block illustrates the use of TYPE..IS... and VARRAY. In this sample, a type v_arr is defined as an variable array of maximum 25 elements which are of type NUMBER(3). Then a variable v1 is defined as type v_arr . This sample code also demonstrates the use of %TYPE attribute.

DECLARE
TYPE v_arr IS VARRAY(25) of NUMBER(3);
v1 v_arr;
v_empno employee.empno%TYPE;

BEGIN
    v1(2) := 3;
    DBMS_OUTPUT.PUT_LINE('The Value of v1(2) = ' || v1(2));
      v_empno := 4;
END;

Coding Guidelines

Single-line comments are prefixed with two dashes --.
Multiple-line comments can be enclosed with the symbols /* and */.
Variables and function identifiers can contain up to 30 characters, and should not have the same name as a database column name.
Identifiers must begin with an alphanumerical character.
SQL functions can be used in PL/SQL.
Code blocks can be nested and unqualified variables can locally scoped.
It is recommended that variable names are prefixed by v_, and parameter names in procedures/functions are prefixed by _p.

SQL Statements in PL/SQL

The following code block shows how to run DML statements in PL/SQL. Basically they look similar to the SQL. Note that the SELECT statement retrieves the single-row value and store into a variable using INTO clause.
DECLARE
    v_sal employee.sal%TYPE;
BEGIN

    INSERT INTO employee VALUES (6, 'TOM LEE', 10000);

    UPDATE employee SET sal = sal + 5000 WHERE empno = 6;
      SELECT sal INTO v_sal FROM employee WHERE empno = 6;
    DBMS_OUTPUT.PUT_LINE('Salary increased to ' || v_sal);
    DELETE FROM employee WHERE empno = 6;
      COMMIT;
END;

/

Control Structures

Conditions checking

IF THEN
[ELSIF THEN]
[ELSE THEN]
END IF;

Basic loops.

LOOP
...
EXIT WHEN
END LOOP;

FOR loop.

FOR counter IN lower_bound .. upper_bound
...
END LOOP;

WHILE loop.

WHILE LOOP
...
END LOOP;

The code samples making use of the control structures will be given in the following.

SQL Cursor

A SQL cursor is a private Oracle SQL working area. There are two types of SQL cursor: implicit or explicit cursor. The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers.
Using the implicit cursor, we can test the outcome of SQL statements in PL/SQL. For example,

SQL%ROWCOUNT, return the number of rows affected;
SQL%FOUND, a BOOLEAN attribute indicating whether the recent SQL statement matches to any row;
SQL%NOTFOUND, a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row;
SQL%ISOPEN, a BOOLEAN attribute and always evaluated as FALSE immediately after the SQL statement is executed.

To write the explicit cursor, please refer to the following example. Note that a cursor definition can array a number of arguments.
For example,
DECLARE

CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, name, sal
FROM employee
WHERE name LIKE '%p_name%';
BEGIN

FOR rec_ac IN csr_ac ('LE')
LOOP
DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal);
END LOOP ;
CLOSE csr_ac;
END;
/

Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following.

DECLARE

CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, name, sal
FROM employee
WHERE name LIKE '%p_name%';
v_a employee.empno%TYPE;
v_b employee.name%TYPE;
v_c employee.sal%TYPE;
BEGIN
    OPEN csr_ac ('LE');
    LOOP
        FETCH csr_ac INTO a, b, c;
        EXIT WHEN csr_ac%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);

    END LOOP;
    CLOSE csr_ac;
END;

Writing PL/SQL Procedures/Functions

PL/SQL functions returns a scalar value and PL/SQL procedures return nothing. Both can take zero or more number of parameters as input or output. The special feature about PL/SQL is that a procedure/function argument can be of input (indicating the argument is read-only), output (indicating the argument is write-only) or both (both readable and writable).
For example, the following is a PL/SQL procedure and a function.

PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2) IS
BEGIN
INSERT INTO employee VALUES (emp_id, name, 1000);
END hire_employee;

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS
   min_sal REAL;
   max_sal REAL;
BEGIN
   SELECT losal, hisal INTO min_sal, max_sal 
      FROM sals
      WHERE job = title;
   RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;

A function is called as part of an expression. For example, the function sal_ok might be called as follows:

IF sal_ok(new_sal, new_title) THEN ...

Writing and Compiling PL/SQL Packages.

A package is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.
Unlike subprograms, packages cannot be called, parameterized, or nested. Still, the format of a package is similar to that of a subprogram:

CREATE PACKAGE name AS  -- specification (visible part)
   -- public type and object declarations
   -- subprogram specifications
END [name];

CREATE PACKAGE BODY name AS  -- body (hidden part)
   -- private type and object declarations
   -- subprogram bodies
[BEGIN
   -- initialization statements]
END [name];

The specification holds public declarations, which are visible to your application. The body holds implementation details and private declarations, which are hidden from your application. As shown in the following figure, you can think of the specification as an operational interface and of the body as a "black box":

You can debug, enhance, or replace a package body without changing the interface (package specification) to the package body.
For example, we want to create a simple package providing three functions: hire_employee, fire_employee and raise_salary.
First we created the package specification.

CREATE OR REPLACE PACKAGE test AS -- package spec
    TYPE list IS VARRAY(25) of NUMBER(3);

    PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2);
    PROCEDURE fire_employee (emp_id INTEGER);
    PROCEDURE raise_salary (emp_id INTEGER, amount REAL);
END test;
/

Then we created the package body.

CREATE OR REPLACE PACKAGE BODY test AS -- package body
    PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2) IS
    BEGIN
        INSERT INTO employee VALUES (emp_id, name, 1000);
    END hire_employee;

    PROCEDURE fire_employee (emp_id INTEGER) IS
    BEGIN
        DELETE FROM employee WHERE empno = emp_id;
    END fire_employee;

    PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Increase Salary :' || to_char(amou
nt));
        UPDATE employee SET sal = sal + amount WHERE empno = emp_id;
    END raise_salary;
END test;

/

To compile the package, we can either type them into SQL*Plus terminal. And Oracle server will compile and store the package, or save them into separate files and compile them from SQL*Plus. Assume the package spec is stored in a file named spec, and the body is stored in another file named body. The following shows how to compile the package and make the procedure call at SQL*Plus.

SQL> SET SERVEROUTPUT ON
SQL> VARIABLE num NUMBER

SQL> @spec

SQL> @body

SQL> exec test.raise_salary(1,1000);

Reference :
http://www.comp.nus.edu.sg/~ooibc/courses/sql/plsql.htm

My notes and Other stuff:

Thursday 27 November 2008

PL/SQL

Overview

Handling Variables

Coding Guidelines

SQL Statements in PL/SQL

Control Structures

SQL Cursor

Writing PL/SQL Procedures/Functions

Writing and Compiling PL/SQL Packages.

Saturday 22 November 2008

How to enable Log4j logging in Eclipse console window?

Thursday 20 November 2008

contactsheet : How to make friends by Telephone

Log4j warning message [log4j:WARN No appenders could be found for logger]