Introduction to PL/SQL

PL/SQL (Procedural Language/Structured Query Language):
-         Procedural Language that supports SQL.
-         Provides for Traditional Procedural Language Constructs such as looping and conditional processing.
-         Provides Cursors for processing multi-row queries.
-         Provides Powerful error handling capability.
-         Boost Performance by reducing network traffic by combining many statements in one database transaction.

PL/SQL can be used in many places, including:
-         SQL*Plus
-         Oracle Forms
-         Oracle Reports
-         Oracle Graphics
-         Pre-compilers for most languages (C/C++, Java, Cobol, Fortran, etc.)
-         Oracle Call Interface (OCI)
-         SQL*DBA

Types of PL/SQL Programs:
-         Anonymous Blocks:         Unnamed block issued interactively.
-         Stored Procedures and Functions:         Named blocks that accept parameters and return values.
-         Packages:         Named module that groups procedures and functions.
-         Database Trigers:         Code associated with a table that fires on certain events.
-         Oracle (Developer 2000) Forms Procedures, Functions, and Triggers.

PL/SQL can be called from various places, including:
- SQL*Plus
- Traditional client applications, including those written in Visual Basic, Power builder, and Delphi.
- Web servers running Perl DBI, JSP/JDBC, ASP/ODBC, etc.

Basic Structure:

DECLARE (optional) READABILITY:

- Variable and Cursor declarations. INDENT FOUR SPACES

BEGIN (required)

- Executable PL/SQL and SQL commands. INDENT FOUR SPACES

EXCEPTION (optional)

- Executable commands for error handling. INDENT FOUR SPACES

END; (required)

/ - Causes the PL/SQL program to be executed.

Note the indentation above, failure to indent for readability will result in one full letter grade deduction and your work being returned to you to fix. Also I will not help you with any program that is NOT readable.

DECLARE SECTION:

Declares Variables:

Syntax

- variable_name [CONSTANT] datatype [NOT NULL] [:= VALUE];

- Declare only one variable per line.

- Choose a consistent naming convention for variables.

- Using :=, you can declare and initialize the variable in one step.

Data-Types:
-         BINARY INTEGER (Positive, Natural)
-         NUMBER (Float, Decimal, Real, Integer, Small Integer)
-         CHAR (fixed-length)
-         LONG
-         VARCHAR/VARCHAR2
-         BOOLEAN
-         DATE
-         RAW
-         LONG RAW

Operator    Meaning
+                 Addition.
-                  Subtraction.
*                  Multiplication.
/                   Division.
**                Exponentiation.
<>               Not Equal.
!=                Not Equal.
>                 Greater Than.
<                 Less Than.
>=               Greater Than or Equal To.
<=               Less Than or Equal To.
=                 Test for Equality.
:=                 Assignment operator (Sets the left value equal to the right value).

Inheriting Data-Types from variables or database columns:
A variable can be inherited from another variable or from database tables column.

V_variable v_other_variable%TYPE[:=expr];

V_variable table.column%TYPE[:=expr];

Variable Declaration Examples:

- Declare a variable and initialize it to 0

V_count BINARY_INTEGER := 0;

- Declare a name variable, don?t initialize it.

V_name varchar2(30);

- Declare a constant

V_tax_rate CONSTANT number(4,4) := .0825;

- Declare a variable to store the current date

V_current_dt DATE NOT NULL := SYSDATE;

- Picks up datatype from another variable.

V_amount number (7,2);

V_max_amount v_amount%TYPE;

- Picks up datatype from a table column.

V_slsrep_number sales_rep.slsrep_number%TYPE;

Anytime your variable is storing data from a table, you should declare it this way.

BEGIN SECTION:

- Assigning Variables in Code

V_variable := expression;

Notes:

- := is the assignment operator and = is reserved for logical expressions or a test of equality.

- Assigning Variables Using SELECT

SELECT column1, column2
INTO     v_variable1, v_variable2 -- Required in PL/SQL
FROM      table_name
WHERE     condition;

Notes:

- You must always SELECT INTO variables in PL/SQL.

- The number of columns in the SELECT clause must match the number of variables in the INTO clause and the data-types must be compatible.

- The SELECT statement should return 1 AND ONLY 1 row. For more than one row we will use a cursor, which we shall discuss shortly.

- Using Substitution Variables in SQL*Plus
PL/SQL is not a language designed for User Input/Output, it is more designed run on the database server and interface with a client through a set interface. Because of this it cannot directly accept input from the user, however SQL*Plus can through the accept command. A SQL*Plus variable that is used in a PL/SQL block is called a substitution variable.

- Substitution variables

- Declare or assign the variable using ACCEPT or DEFINE

- Reference the variable using &variable_name

- Global Variables can be defined in SQL*Plus using the VARIABLE command.

- VARIABLE g_global_variable data-type;

- Reference the global variables by preceding them with a ?:?, i.e. :g_variable;

Example:

SQL> @file1.sql -- Unix
SQL> @d:\temp\file1.sql -- Windows, Assuming the file is stored in the d:\temp directory

-- The following is in the file file1.sql
ACCEPT p_name PROMPT 'Please enter a name: ';

VARIABLE g_credit_available NUMBER;
DECLARE -- Start of PL/SQL Block
        v_balance customer.balance%TYPE;
        v_credit_limit customer.credit_limit%TYPE;
        v_name customer.lname%TYPE := '&p_name';
BEGIN
        SELECT balance, credit_limit
        INTO v_balance, v_credit_limit
        FROM customer
        WHERE lname = v_name;

        :g_credit_available := v_credit_limit - v_balance;

END;
/

PRINT g_credit_available

-- End file1.sql

Variable Scope and Nested Blocks:
A variable is visible in the block in which it is declared and all nested blocks unless a variable in an enclosing block is re-declared in a sub-block, the sub-block and its subsequent sub-blocks can no longer see the original variable in the enclosing block.

Example:

DECLARE -- Start of PL/SQL Main Block
        v_sal NUMBER (8,2) := 2000;
        v_name VARCHAR2(20) := 'SMITH'; -- v_name is the same in both blocks
BEGIN
        DECLARE -- Start of Sub-Block
                v_comm NUMBER := 0; -- v_comm is local to sub-block
                v_sal NUMBER(8,2) := 1000; -- v_sal is redeclared in sub-block
         BEGIN
                v_sal := v_sal + 500;
                v_comm := v_comm + 100;
                dbms_output.put_line
                        ('Salary for ' || v_name || ' is ' || v_sal || ' Comm is ' || v_comm);

        END; -- End of Sub-Block.

        v_sal := v_sal + 1000; -- v_sal in main-block is 3000
        -- v_comm := v_comm + 500; -- v_comm is an illegal reference
        dbms_output.put_line
                ('Salary for ' || v_name || ' is ' || v_sal); -- || ' Comm is ' || v_comm);

        -- Because scope was only in sub-block.

END; -- End Main Block
/

<<outer_block>>
DECLARE -- Start of PL/SQL Main Block
        v_sal NUMBER (8,2) := 2000;
        v_name VARCHAR2(20) := 'SMITH'; -- v_name is the same in both blocks
BEGIN
        <<inner_block>>
        DECLARE -- Start of Sub-Block
                v_comm NUMBER := 0; -- v_comm is local to sub-block
                v_sal NUMBER(8,2) := 1000; -- v_sal is redeclared in sub-block
         BEGIN
                v_sal := v_sal + 500;
                outer_block.v_sal := inner_block.v_sal;
                v_comm := v_comm + 100;
                dbms_output.put_line
                        ('Salary for ' || v_name || ' is ' || v_sal || ' Comm is ' || v_comm);

        END; -- End of Sub-Block.

        v_sal := v_sal + 1000; -- v_sal in main-block is 3000
        -- v_comm := v_comm + 500; -- v_comm is an illegal reference
        dbms_output.put_line
                ('Salary for ' || v_name || ' is ' || v_sal); -- || ' Comm is ' || v_comm);

        -- Because scope was only in sub-block.

END; -- End Main Block
/

Manipulating Data Using SQL Commands:
The following commands can be used in the BEGIN section to manipulate data

- SELECT

- INSERT

- UPDATE

- DELETE

- COMMIT

- SAVEPOINT

- ROLLBACK

Examples:

CREATE SEQUENCE customer_customer_number_seq
START WITH 650
INCREMENT BY 1;

ACCEPT p_cust_last PROMPT 'Enter Customer Last Name:';
ACCEPT p_cust_first PROMPT 'Enter Customer First Name:';

DECLARE

v_cust_last customer.lname%TYPE := '&p_cust_last';
v_cust_first customer.fname%TYPE := '&p_cust_first';

BEGIN

          INSERT INTO customer (customer_number, lname, fname)
                    VALUES (customer_customer_number_seq.NextVal,
                                    v_cust_last, v_cust_first);

COMMIT;

END;
/

ACCEPT p_customer_nr PROMPT 'Enter Customer # to Delete:';

DECLARE
V_customer_nr customer.customer_number%TYPE := &p_customer_nr;
BEGIN

DELETE FROM customer
WHERE customer_number = v_customer_nr;

COMMIT;

END;
/

ACCEPT p_customer_nr PROMPT 'Enter Customer # to Update:';
ACCEPT p_cust_last PROMPT 'Enter Customer Last Name:';
ACCEPT p_cust_first PROMPT 'Enter Customer First Name:';

DECLARE
          V_customer_nr customer.customer_number%TYPE := &p_customer_nr;
          V_cust_last customer.lname%TYPE := '&p_cust_last';
          V_cust_first customer.fname%TYPE := '&p_cust_first';
BEGIN
          UPDATE customer
          SET lname = v_cust_last,
                    Fname = v_cust_first
          WHERE customer_number = v_customer_nr;

COMMIT;
END;
/

Introduction to PL/SQL

SELECT column1, column2INTO v_variable1, v_variable2 -- Required in PL/SQLFROM table_nameWHERE condition;

SELECT column1, column2
INTO v_variable1, v_variable2 -- Required in PL/SQL
FROM table_name
WHERE condition;