%TYPE, %ROWTYPE, and User-defined TYPES

%TYPE or %ROWTYPE are derived types from other variables or tables. These types are very convenient to declare when you want to declare the same types of variables as the columns in a table or variables you already declared since it reduces the possible type mismatch.

%TYPE (anchored type declaration)
Example:               first_name customer.fname%TYPE;                 -- anchoring at compile time
                             last_name varchar2(20);
                             another_name last_name%TYPE; 

User-defined subtypes
Syntax:                  SUBTYPE new_type IS original_type;

SUBTYPE num IS NUMBER;                   -- NUMBER(4) not allowed (constraint)

mynum num;                                               -- num is a user-defined type name (number)

SUBTYPE nametype IS customer.fname%TYPE;

Records structure
TYPE student_rec_type IS RECORD (                 -- student_rec_type is a record type name
         studentid NUMBER(5),
         first_name VARCHAR2(20),
         last_name VARCHAR2(20)
);

student_a student_rec_type;
student_b student_rec_type;
student_a.first_name := 'Walker';                            -- reference to first_name in the record

• Record assignment is fine if two record types are matched
   e.g., student_b := student_a;

Using %ROWTYPE for declaring records

Syntax: RecordName TableName%ROWTYPE;

%ROWTYPE is used to declare the same record type as all the column types defined in a table (since the row is basically a record).

e.g., mycust customer%ROWTYPE;     -- mycust is a record name, customer is a table name

Once we defined a row type record variable, we can use all the field names in the table, e.g., customer in this case, mycust.name, mycust.age, etc.

==> %TYPE and %ROWTYPE are really handy when you handle the columns and rows in a database table in PL/SQL program.

Array (Table)

Array in PL/SQL is basically a collection (similar to the collection in other languages like VB, Java or similar to Hash array in Perl). In PL/SQL, unlike other languages, you need to create an array type first before you use it.

Syntax: TYPE tabletype_name IS TABLE OF element_type [NOT NULL]
                                                                        INDEX BY BINARY_INTEGER;

e.g., TYPE mytable IS TABLE OF student.first_name%TYPE INDEX BY BINARY_INTEGER;

names mytable;                       -- two tables in DECLARE section
newNames mytable;                -- note that there is no size specification

names(10) := 'scott';

•  PL/SQL Arrays are not like Arrays in C (it is more similar to the Iterator in C++, the Array in Java, or the Collection in VB).

Key (index) is BINARY_INTEGER, Value (table element), the number of elements is only limited to the size of BINARY_INTEGER, elements are not necessarily in any order

Names(0) := 'aaa';                    -- key is 0, value is 'aaa'
Names(-5) := 'bbb';                  -- key is -5, value is 'bbb'
Names(3) := 'ccc';                    -- key is 3, value is 'ccc'
Names(15.45) := 'ddd'             -- 15.45 is converted to 16 automatically by ORACLE.
NewNames : = names;             -- table assignment (actually this is an object assignment)

Array of structure
TYPE student_table IS TABLE OF students%ROWTYPE INDEX BY BINARY_INTEGER;
mystudents student_table;

mystudents(100).first_name := 'tommy';

Characteristics of PL/SQL Tables
(a) One-dimensional
      A PL/SQL table can have only one column (one-dimensional array).
(b) Unbounded or Unconstrained
      There is no predefined limit to the number of rows in a PL/SQL table. The table grows dynamically as you add more rows        to the table.
(c) Sparse
     A row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially.
(d) Homogeneous elements
     All rows in a table contain values of the same data type.
(e) Indexed by integers (32 bit BINARY_INTEGER)
      You can have that many of rows.

Table Attributes in array (table.attribute)
=> attributes are ORACLE defined methods or properties, e.g., COUNT, DELETE, EXISTS, FIRST, LAST, NEXT,       PRIOR, etc. You can use index (like C/C++) along with these attributes.

(COUNT returns number, DELETE returns n/a but delete, EXISTS returns boolean, FIRST, LAST, NEXT, PRIOR all return index)

tableName.COUNT                    -- number of elements currently contained in the table
tableName.EXISTS(i)                  -- check if ith row exists
tableName.DELETE(i)                 -- deletes the ith row
tableName.DELETE(i,j)               -- deletes all rows between index i and j
tableName.NEXT(i)                     -- next to ith row
tableName.PRIOR(i)                    -- prior of ith row
• Index is better to be start with 1, 2, ….         -- for possible compatibility with other language
• mystudents(100) := NULL;                           -- delete everything in mystudents table but not remove
                                                                       -- element space

                                                                          PL/SQL Execution Environment

Different PL/SQL Engines
PL/SQL engine on Server side (SQL*PLUS), PL/SQL engine on client side (Oracle's tools like Developer/2000 forms, reports, … communicate with server through remote procedure calls)

What about SQL statements in PL/SQL blocks?
• PL/SQL statements are executed on PL/SQL engine (either on server side or client side)
• SQL statements are sent to SQL statement executor on Server.

Server-side PL/SQL (using SQL*PLUS)
SQL*PLUS
• SQL*PLUS interacts with Server
• In SQL, semicolon is the statement terminator and the statement sent to the server.
• In PL/SQL block, semicolon is a syntactic part of the block, and slash, / is required to execute it.

Example PL/SQL code
DECLARE                        -- anonymous block
/* Declare variables to be used in the block. */
v_custid               NUMBER(3) := 222;
v_first                  VARCHAR2(20) := 'john';
v_last                   VARCHAR2(20) := 'smith';
v_city                   VARCHAR2(20) := 'New York';
BEGIN
/* Add a row to the customer table, using the values of the variables. */

               INSERT INTO customer (customer_number, last, first, city) VALUES (v_custid, v_first, v_last, v_city);

END;

When you executes a PL/SQL program using SQL*PLUS, you need to type / to execute it. The semicolon does not work any more since the semicolon in PL/SQL is just a statement separator.

                                   Using Oracle Variables for I/O (for debugging purpose)

Bind variables
Syntax: VARIABLE variable_name variable_type                     -- VARIABLE is the command
e.g., SQL> VARIABLE my_name VARCHAR2(20);              -- at SQL*Plus prompt

• Binded variables are valid from the SQL prompt
• Inside of PL/SQL block, the variables are delimited by the leading colon, :

Within the PL/SQL block, use it preceded by colon, :

BEGIN
       :my_name := 'Tom';
END;

• To see the content of a binded variable at SQL*PLUS, use the command, PRINT variable_name, e.g., PRINT my_name;
• To see all variables defined -- use the command, VARIABLE

e.g., SQL> variable vcount NUMBER
        SQL> BEGIN
                       SELECT COUNT(*) INTO :vcount
                       FROM students;
                   END;
                   /                                                         -- to execute this PL/SQL block
SQL> PRINT vcount                                           -- to see the content of bind variables

Define variables -- usually used for report (or for environment variables)
• DEFINE define_variable = values -- usually text value
• ACCEPT variable_name variable_type PROMPT 'any prompt text'

ACCEPT my_company PROMPT 'Enter your company'              or
DEFINE my_company = 'ORACLE'

• To see all the defined variables -- use the command, DEFINE
• To remove a defined variable -- use the command, UNDEFINE variable_name
Within the PL/SQL block, you can reference the defined variables preceded by & with ' '

Substitution variables (or Macro variable)

• Define a variable preceded by &              -- used for values (R-value), not for LHS variable (L-value)
SELECT company_name
FROM company
WHERE company_id = '&my_company';

• The following set commands will disable the 'Old and New' prompt

SET VERIFY OFF
SET ECHO OFF

Binded variables, Defined variables only exist within a session!
Substitution variables (no memory allocated)

• PL/SQL does not have capabilities for input or output from/to I/O devices -- so DBMS_OUTPUT (built-in package) and UTL_FILE (for file I/O with operating system) are used. Here DBMS_OUTPUT and UTL_FILE are Oracle built-in packages. There are many other packages including DBMS_SQL package for dynamic PL/SQL which we can execute a SQL statement in a string, like in other programming languages like Visual Basic, Java, etc.

• Input is accomplished through substitution variables (processed by SQL*PLUS like C macros)
&var_name

studentID students.id%TYPE := &student_id;
SELECT &columns FROM classes;

Show ERRORS command
Any compile errors are stored in the user_errors data dictionary view
SHOW ERRORS                   -- to see errorsS

Testing and Debugging

Input from keyboard: use substitution variables
Output to screen: use DBMS_OUTPUT package or bind variables (print command)

DBMS_OUTPUT package                    -- input/output with buffer

PUT routines: PUT (number or date), PUT_LINE(varchar, number, date), NEW_LINE
                       procedures. The difference between PUT and PUT_LINE is that PUT_LINE can handle prints a carriage return                        PUT does not.

GET routines: GET, GET_LINES.

ENABLE and DISABLE control the buffer

The important point to remember is that the dbms_output package is using an in-memory buffer as it's target, it is not actually doing any 'real' output. If you want to display something on the screen, type this setting command first:

SET SERVEROUTPUT ON [SIZE buffer_size]

The SIZE buffer_size command limits the buffer available to your session. The size can be between 2,000 and 1,000,000 bytes (watch out for the side-effect of multi-byte character sets) although SQL*Plus and svrmgr seem to disagree on whether you can actually use the value 1,000,000. The default size is 2,000.

For example, within the PL/SQL block,
begin

               DBMS_OUTPUT.PUT_LINE('Hello, world');                   -- will print the "Hello, world"

end;

If you use a simple set serveroutput on in SQL*Plus, then leading spaces are trimmed, and the newline() command is ignored, so your carefully planned output easily becomes unreadable.

In SQL*Plus, the same command will work, but the presentation rules are different and you are better off using the command:

set serveroutput on format wrapped size 5000

SQL Statement within PL/SQL

Usually DML statements (SELECT, INSERT, UPDATE, DELETE) or Transaction control statements (COMMIT, ROLLBACK, SAVEPOINT) are allowed in PL/SQL program (except for using a package, DBMS_SQL in v2.1 or higher) -- since PL/SQL intentionally designed to use early binding. The DDL commands are possible through the dynamic PL/SQL.

Syntax of SELECT statement:

SELECT field_list INTO PL/SQL record or variable
FROM table_list
WHERE condition;

=>  The SELECT statement should return no more than one row/value, otherwise it returns error.

DECLARE
            Student_record students%ROWTYPE;
            v_department class.department%TYPE
BEGIN
             SELECT *
                  INTO student_record
                  FROM students
                  WHERE id = 1111;
            SELECT department
                INTO v_department
                FROM classes
                WHERE room_id = 999;
 END;

Syntax for the other statements (INSERT INTO … VALUES, UPDATE, DELETE)
are the same, for example, you can use INSERT command as follows:

SQL> CREATE SEQUENCE student_sequence START WITH 100
BEGIN
INSERT INTO students (id, first_name, last_name)
VALUES (student_sequence.NEXTVAL, 'john', 'smith');
END;