Introduction to SQL and PL/SQL

FORMATTING RESULTS

For Scripts I can use:
set linesize 132
set pagesize 0
set feedback off
set trimspool on
set heading off

Use the SET command with the following options:

DEFINE = Shows all the saved values and its status.

ECHO ON/OFF = Show/Hide what is doing

FEEDBACK ON/OFF = Show/Hide  rows returned by the query

HEADING OFF/ON = Disable/Enable Column Headers

LINESIZE Num = # of characters per line, 80 char by default, max is 32767

TRIMSPOOL OFF/ON = When we spool to a file, we get several "white spaces" per line, if we want to avoid that, use TRIMSPOOL ON.

MARKUP HTML ON/OFF =  Will let you generate an HTML report. Example
SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL emp1.html
SELECT * FROM emp;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON

NUMWIDTH Num = Can be use to modify the width of all the columns with numbers, by default is 10

NUMFORMAT ...... = Define format for all the numbers.

PAGESIZE
Num = # of rows per page, default value is 66

SCAN ON/OFF = Show/Hide sustitution of variables.

SHOW ALL or SHOW command = Showsen state of a a particular command or all of them.

SQLCASE UPPER = Force to search on the DB with uppercase.

TERMOUT OFF/ON = Show/Hide the output to the screen. Generally you use OFF at the beginning and ON immediatly after spooling to show the results.

RECSEP ON/OFF = Por default el parametro RECSEP esta seteado en WRAP. Esto hace que despues de cada "wrapped record" se insertara el caracter en que se haya seteado RECSEPCHAR, que por defecto es un espacio en blanco. Poniendolo en OFF se eliminara la linea en blanco entre los registros.
The recsep command has basically two functions:
    1.  SQL> set recsep off (returns all rows single-spaced)
    2.  SQL> set recsep each
    (inserts a linefeed between each row/double-spaced)

UNDEFINE variable = Undefine a variable

UNDERLINE = With this variable we define what is going to be shown after the header of each column. Default value is ---

VERIFY ON/OFF = Show/Hide the lines with replacements

NOTE= I recommend to use the following at the top of your scripts to clean all variables:
                CLEAR BREAKS
                CLEAR COMPUTES
 
COLSEP <char> = Generate comma or pipe delimited output for tools such as Excel, Access, and so on while spooling from SQL*Plus. Example:
set pages 0
set feedback off
set colsep ,
select object_name, object_type, status, created
  from all_objects
  where rownum < 10 ;
Output:
ALL_ALL_TABLES                ,SYNONYM           ,VALID  ,14-MAY-03
ALL_APPLY                     ,SYNONYM           ,VALID  ,14-MAY-03
ALL_APPLY_CONFLICT_COLUMNS    ,SYNONYM           ,VALID  ,14-MAY-03
ALL_APPLY_DML_HANDLERS        ,SYNONYM           ,VALID  ,14-MAY-03
ALL_APPLY_ERROR               ,SYNONYM           ,VALID  ,14-MAY-03
ALL_APPLY_KEY_COLUMNS         ,SYNONYM           ,VALID  ,14-MAY-03
ALL_APPLY_PARAMETERS          ,SYNONYM           ,VALID  ,14-MAY-03
ALL_APPLY_PROGRESS            ,SYNONYM           ,VALID  ,14-MAY-03
ALL_ARGUMENTS                 ,SYNONYM           ,VALID  ,14-MAY-03

For TAB delimited output, you can use:
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"

Interaction with SQL
Use PROMPT  to add comments or actions
Use ACCEPT to get values from the user, you could also use & 2 times, which makes Oracle to ask for that value
If I don't want to see comments before ACCEPT use ECHO OFF y VERIFY OFF. Example:
 accept month number prompt 'Month to Calculate: '
 
 

Titles on Reports
Top Title
TTITLE (center, left o right) ‘mi Titulo’
TTITLE center ‘Un titulo’ skip 1 right ‘Otro Título’  skip 2

Bottom Title:
 BTITLE with similar options.

You could also define variables with a title and use it later, example:
  DEFINE LINE1 = 'First Line middle'
  DEFINE LINE2 = 'Second Line Left side'
  DEFINE LINE3 = 'Third line rigth side'
  TTITLE CENTER LINE1 SKIP 1 LEFT LINE2 SKIP1 RIGHT LINE3

If you want to show the Number of Pages, you can use the function SQL.PNO. Example:
  TTITLE LEFT ‘Monthly Report’ RIGHT ‘PAGE:’ FORMAT 999 SQL.PNO SKIP 2

To cancel the titles::
  TTITLE OFF
  BTITLE OFF
 
 

Formatting Results
COLUMN col_name HEADING ‘tittle’ FORMAT a15

The COLUMN command is a way to format the results of a select, if you need a longer tittle you could youse the | (pipe) option. Another option is to wrap the tittle using the following options:
    WORD_WRAPPED =
    WRAPPED =
    TRUNC =

To check the attributes of a column you can use:
COLUMN col_name

To reset it:
COLUMN col_name CLEAR

The full format is the following:
COLUMN nom_col HEADING ‘mi tittle’ NEW_VALUE variable_to_the_tittle NOPRINT NULL text_to_show_if_null OLD_VALUE variable_to_btittle WORD_WRAPPED
           WRAPPED
     TRUNC

With NOPRINT with NEW_VALUE you could "hide" a variable to print it later in another place, example:
  COLUMN today NEW_VALUE xtoday NOPRINT

This will create a new variable "xtoday" that will have the value of variable "today" but is not going to be printed.
 

Break by Column
This is one of the most used features on reports. The BREAK command will not show repetitive values on a select using order by. Example:
BREAK ON break_column

If I want to have an empty line or empty Page bwfore a new value:
BREAK ON break_column SKIP (N_renglones o PAGE)

Clean all the breaks:
CLEAR BREAKS

 

Calculations
One of the most important commands is COMPUTE which let me calculate totals by a specific field. Its format is:

BREAK ON break_column
COMPUTE math_function OF col_to_apply_function ON variable_collecting_results

Example:
COMPUTE sum OF col1 col2 col3 ON campo o REPORT 

If you want different subtotals use several BREAK, example:
 BREAK ON company ON division ON Departamento SKIP 1

Using:
 BREAK ON REPORT ON col1
  COMPUTE SUM OF ...... ON .......... REPORT
You will calculate not only the sub-totals by group, also a total at the end. 

Options for BREAK ON:
 COL =
 ROW = Performs a BREAK on each row
 PAGE = Performs a BREAK by page
 REPORT = Performs a BREAK at the end of the report

Options for SKIP:
 ‘n rows'= Skip "n" rows
 PAGE = Skip to new page

Options for COMPUTE :
SUM – MIN – MAX – AVG – STD
VAR – COUNT – NUM (like count but for nulls)
 

Single Row Functions

Type

Function

Returns

Number

abs(n)

Absolute value of n. i.e. removes the sign.

 

ceil(n)

Smallest integer >=n

 

floor(n)

Largest integer <=n

 

mod(n,m)

Remainder of n divided by m

 

power(n,m)

n raised to the power m

 

round(n[,m])

n rounded to m digits to the right of the decimal point. m = 0 by default.

 

sign(n)

-1 if n<0
0 if n=0
1 if n>09

 

sqrt(n)

Ö n

 

trunc(n[,m])

as round but there is no rounding up.

Character

chr(n)

the character corresponding to the decimal value e.g. In the ascii character set chr(10) is a new line character.

 

concat(s1,s2)

s1||s2 (used for portability where the ‘|’ character may cause problems)

 

initcap(s)

s with the first letter of each word in upper case

 

lower(s)

s with all letters lower case

 

lpad(s,n[,c])

s left-padded with c’s to a size of n

 

rpad(s,n[,c])

as lpad but to the right


replace(s1,s2[,s3]) s1 with all instances of s2 replaced by s3

 

ltrim(s1[,s2])

s1 with letters in s2 trimmed from left until a letter not in s2 is found.

 

rtrim(s1,n,[s2)

as ltrim but to the right

 

substr(s1,n,m)

the m character long substring of s1 beginning with character n

 

translate(s1,s2,s3)

s1 but with each character in s2 replaced by the corresponding letter from s3.

 

upper(s)

s with all letters upper case.

Character returning number

ascii(c)

returns the decimal value of c in the character set of the database (note that this may not be ascii!!)

 

instr(s1,s2[,n[,m]])

returns the character position of the mth occurrence of the string s2 in s1 beginning with the nth character.

 

length(s1)

the length of s1

Date

add_months(d,n)

the date n days after d

 

last_day(d)

the date of the last day of the month which d is in

 

months_between(d1,d2)

number of months between d1 and d2; this is expressed as a floating point number calculated on the basis if 31 days per month.

 

next_day(d, day)

e.g. if day=Monday then returns the date of the first Monday after d.

 

round(d,f)

returns d rounded to the nearest f (e.g. ‘Day’, ‘Month’, etc)

 

sysdate

the current database date and time

 

trunc(d,f)

as round but truncates rather than rounds.

Conversion

to_char(d,f)
to_char(n,f)

Converts a date or a number to characters.

 

to_date(s1,f)

Converts a character string in format f to a date

 

to_number(c,f)

Converts a character string in format f to a number

Other

greatest(e1,e2&#8230;.)

Whichever of e1, e2, etc is the greatest

 

least(e1,e2,&#8230;.)

Whichever of e1, e2, etc is the least

 

nvl(e1, e2)

e1 unless it is null in which case e2

 

user

the current database username

Decode

decode

decode is not strictly a function but rather a bit of non-standard SQL syntax (for that reason its available in SQL but not in PL/SQL). 

Simulating IF/THEN/ELSE logic
There is no IF command in SQL*Plus. However, you can use some of the techniques shown above along with the DECODE operator to simulate an IF command.
In this example, the user is asked to enter the number of a script to run. The user’s answer is evaluated and the appropriate script is run. If the user entered an incorrect value, the badscript.sql script is run to display an error message.
Prompt ’1 - script1.sql’
prompt ’2 - script2.sql’
prompt ’3 - script3.sql’
accept script_number prompt - ’Enter the number of the script to run: ’
@formatoff
column script_name noprint new_value script_name_var
select decode ( &script_number,
        1, ’script1.sql’,
        2, ’script2.sql’,
        3, ’script3.sql’,
        ’badscript.sql’) script_name
from dual;
@formaton
@&script_name_var

Copy Command

Probably the easiest way to copy data from one table to another is to use
INSERT INTO table1 SELECT * FROM table2;

This method doesn’t work well with large tables because this command is processed as a single unit of work. Since you can’t have ORACLE do a commit in the middle of the insert, you have to have a rollback segment large enough to handle the complete transaction. It also won’t work if the source table has a LONG datatype.

You could write a store procedure that opens a cursor, does the inserts, and does a commit after a certain number of rows. This can get tedious if you have a large number of columns in the table, since you will have to refer to each one in the INSERT command.
The SQL*Plus COPY command allows you continue to use simple SQL to define the data that you are copying. It also allows you to take occasional COMMITS during the copy operation. The commit frequency is controlled by the ARRAYSIZE and COPYCOMMIT settings in your SQL*Plus session. The ARRAYSIZE setting determines how many rows are in a ’batch’. The COPYCOMMIT setting determines how many batches to process before doing a commit.

The COPY command requires that at least one of the tables be a remote table. It expects a SQL*Net connect string to identify the remote database. To copy one local table to another local table, simply specify the connect string for the local database.

The COPY command allows you to CREATE, REPLACE, INSERT, or APPEND the destination table. CREATE will create the table. REPLACE removes any existing rows before inserting new data. INSERT requires that the table exists, but that it is empty. APPEND adds rows and does nothing with any existing rows.

The following example uses the COPY command to create the local table mytable2 as a copy of the local table mytable1. A commit is taken after every 1000 rows. Note that since COPY is a SQL*Plus command, not a SQL command, you must use a ’-’ to continue the command to multiple lines. Also, the COPY command is not stored in your edit buffer, so you’ll have to retype the command if you make mistakes (or save the command in a script file).
set arraysize 10
set copycommit 100
set long 3000
copy from myusername/mypassword@mydb create mytable2 using select * from mytable1 ;

Array fetch/bind size is 10. (arraysize is 10)
Will commit after every 100 array binds. (copycommit is 100)
Maximum long size is 3000. (long is 3000)
Table MYTABLE2 created.
 

Outer Joing
An outer join forces a query to return a row even if the row in one table has no matching row in the other. i.e. it will return at least as many rows as the corresponding equijoin and probably more (since that’s the reason for writing it in the first place). e.g. the following query lists one row for each order pplus one row for each customer who has not placed any orders:
select c.name, o.order_date
from   customers c, orders o
where  o.cust_id = c.id (+);

 
Creating Tables
The following command creates a table VOTER with:

 
Altering Tables and Constraints

  1. Add a column:
    alter table voters add ( post_code varchar2(7));

  2. Add a constraint (column syntax): the only constraint you can add using this syntax is a NOT NULL constraint. Note that this will only work if the affected column is populated for every row.
    alter table voters modify (post_code not null varchar2(7));

  3. Add a constraint (table syntax):
    alter table voters add (constraint name_not_fred check (name != ‘Fred’));

  4. Disable a constraint:
    alter table voters disable constraint name_not_fred;

  5. Enable a constraint:
    alter table voters enable constraint name_not_fred;

  6. Remove a constraint:
    alter table voters drop constraint name_not_fred;

  7. Amend a column:
    alter table voters modify (post_code varchar2(8));

  8. drop a table (the cascade constraints clause is optional; it drops all foreign key constraints which refer to this table):
    drop table voters cascade constraints;

  9. Delete all rows using one of the commands below:
    truncate table voters;
    delete from voters;

  10. Rename a table, view, sequence or private synonym using the RENAME command (note: NOT public synonyms or columns). Grants, indexes, integrity constraints are automatically transferred. Other objects that depend on the renamed object are made invalid e.g. procedures, functions, packages, views, synonyms.
    rename wrinklies to senior_voters;

  11. Add comments on the following objects only: table, view, snapshot, comment:
    COMMENT ON COLUMN voters.sex
    IS ' Trans-sexuals have the sex on their birth certificate';

  12. Comments can be viewed in the data dictionary views: user_tab_comments (tables and views), user_col_comments, all_tab_comments, all_col_comments.

 
Working with Sequences

  1. Sequences are database objects that generate unique integers. They are typically used to provide unique identifiers for rows in a table. If you get a number from a sequence and the number is not used, or the transaction is rolled back, then there will be a gap in your numbers. For this reason, sequences are suitable when there must not be any missing numbers, for example, in generating invoice numbers.

  2. Create a sequence (numbers start at 1000, increment by 5, maximum is 5000 and then cycles back to 1000);
    create sequence cust_seq 
    start with 1000 increment by 5 maxvalue 5000 cycle;
  3. Getting the next value from a sequence:
    select cust_seq.nextval from dual;

  4. Getting the most recently selected value from the sequence
    select cust_seq.currval from dual;

  5. Modify a sequence. The alter sequence command has al of the same options as create sequence except START WITH. To re-start a sequence you must drop it and ten re-create it.:
    alter sequence cust_seq increment 10;

  6. Remove a sequence:
    drop sequence cust_seq;

 
Controlling User Access

  1. Database objects are owned by users whose access is password protected. Users can GRANT other users access to their objects &#8211; this can include granting another user the right to pass on the grant to third parties.

  2. A typical security setup in a simple production system might be is as follows:
    1. A single user (the schema owner) owns the database objects;
    2. The schema owner grants privileges to a number of database roles e.g. clerk, supervisor, manager. Each of these will have different privileges depending on the access needed to carry out the job. The following objects privileges are available:
      • SELECT, INSERT, UPDATE, DELETE (the meaning of these is obvious)
      • ALTER allows you to change the object definition
      • INDEX allows you to create an index on a table object
      • REFERENCES allows you to create a constraint that references the table (can only be granted to a user, not to a role)
      • EXECUTE allows you to execute a package, procedure or function. Note that you do not need privileges on the tables that are accessed by the package, procedure or function. e.g. If you have EXECUTE on a package that inserts into the VOTERS table and the owner of the package has been granted INSERT on VOTERS then by using the package you can insert into the VOTERS table, even if you cannot do so directly from SQL.
      • WITH GRANT OPTION: allows the grantee to pass on the privileges (cannot be granted to a role, oly a user).
    3. Individuals are GRANTed ROLES thereby allowing them the access they need.
  3. A role is created by:
    create role MANAGER_ROLE;
  4. The schema owner grants object privileges to the role:
    grant select, insert, update, delete on VOTERS to MANAGER_ROLE;
    grant select on STATS to MANAGER_ROLE;

  5. A user is created and granted the requisite roles as follows:
    create user fredg identified by xyz123
    grant create session to fredg -- lets fredg connect to the db
    grant MANAGER_ROLE to fredg;

  6. Change a users password with:
    alter user fredg identified by changedpassword;

  7. Privileges are revoked using the REVOKE command:
    revoke select on stats from MANAGER_ROLE;

  8. Create a private synonym (works only for user who creates the synonym):
    create synonym VOTERS for herbieg.voters;

  9. Create a public synonym (works for every user, requires CREATE PUBLIC SYNONYM privilege):
    create public synonym VOTERS for herbieg.voters;

 
Developing a simple PL/SQL block

/*
 * A multi-line comment
 */
declare
  l_counter NUMBER := 12;
  Fixed_value  CONSTANT NUMBER(6) := 999999;
  Var_Value    NUMBER(4,2);
  l_string VARCHAR2(10) := ‘Dummy’;
  isittrue BOOLEAN := FALSE;
begin
  l_counter := length(l_string);
  l_string  := ‘Dummy’||’2’;
  isittrue  := TRUE; -- A simple comment.
end;

 
%ROWTYPE and %TYPE

Used to inherit the data types from the underlying table. Avoids having PL/SQL fail if a column is modified, e.g. CHAR(2) to CHAR(10)
ROWTYPE is whole row and TYPE is for a single column. Here we are going to process one row.

DECLARE
   CURSOR get_customer IS
       SELECT * FROM customer;
   cust_row get_customer%ROWTYPE;
BEGIN
  -- Begin cursor processing.
   OPEN   get_customer;
   LOOP
       -- Retreive one row.
      FETCH get_customer INTO cust_row;
      -- Exit the loop after all rows have been retreived.
      EXIT WHEN
get_customer%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(cust_row.name);
   -- End processing.
   END LOOP;
   CLOSE get_customer;
END;
/

DECLARE
    CURSOR get_customer IS
       SELECT name FROM customer;
    cust_name customer.name%TYPE;
BEGIN
   OPEN   get_customer;
   LOOP
      FETCH get_customer INTO cust_name;
      EXIT WHEN get_customer%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(cust_name);
   END LOOP;
   CLOSE get_customer;
END;
/
 

Implicit cursors (like standard Selects)

  1. Implicit cursors are used to execute simple insert, update, delete statements and select statements of the kind you typically use in SQL*Plus. You don’t declare a cursor, open it, fetch it and close it; Oracle does it all for you behind the scenes. Note that implicit SELECT statements used in pl/sql must include the INTO clause (there must be somewhere to put the answer).
  2. Explicit cursors involve you explicitly declaring, opening, fetching and closing the cursor – see later section.
  3. When you’ve executed an implicit cursor you can test the outcome using the following attributes which need to be tested after the statement is executed:
    1. sql%found: TRUE if statement affected or retrieved more than zero rows; FALSE otherwise
    2. sql%notfound: the opposite of sql%found
    3. sql%rowcount: the number of rows affected or retrieved
    4. sql%isopen: always returns FALSE since the cursor has been automatically closed
/* A multi-line comment*/ 
declare 
  l_voter voters%rowtype; 
begin 
  select * into l_voter 
     from   voters v 
     where  rownum = 1; 
  if SQL%FOUND then  
    update stats s 
      set times_executed = times_executed + 1 
      where year = ‘1999’;

  end if; 
end;
 

Controlling Flow in PL/SQL Blocks

  1. Conditions

  2. Iteration
REM *** Using EXIT WHEN ***
 DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  LOOP
    -- Insert a row into temp_table with the current value of the loop counter.
    INSERT INTO temp_table VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
    -- Exit condition - when the loop counter > 50 we will break out of the loop.
    EXIT WHEN v_Counter > 50;
  END LOOP;
END;
/

REM *** WHILE Loop Example 1 ***
DECLARE
  v_Counter BINARY_INTEGER := 1;
BEGIN
  -- Test the loop counter before each loop iteration to insure that it is still less than 50.
  WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
  END LOOP;
END;
/

REM *** WHILE Loop Example 2 ***
DECLARE
  v_Counter BINARY_INTEGER;
BEGIN
  -- This condition will evaluate to NULL, since v_Counter is initialized to NULL by default.
  WHILE v_Counter <= 50 LOOP
    INSERT INTO temp_table VALUES (v_Counter, 'Loop index');
    v_Counter := v_Counter + 1;
  END LOOP;
END;
/

REM *** Numeric FOR Loop ***
BEGIN
  FOR v_Counter IN 1..50 LOOP
    INSERT INTO temp_table VALUES (v_Counter, 'Loop Index');
  END LOOP;
END;
/


REM ***FOR Loop Ranges ***
DECLARE
  v_LowValue  NUMBER := 10;
  v_HighValue NUMBER := 40;
BEGIN
  FOR v_Counter IN REVERSE v_LowValue .. v_HighValue LOOP
    INSERT INTO temp_table VALUES (v_Counter, 'Dynamically specified loop ranges');
  END LOOP;
END;
/

REM *** Using Labels ***
DECLARE
  v_Counter  BINARY_INTEGER := 1;
BEGIN
  LOOP
    INSERT INTO temp_table VALUES (v_Counter, 'Loop count');
    v_Counter := v_Counter + 1;
    IF v_Counter > 50 THEN
      GOTO l_EndOfLoop;
    END IF;
  END LOOP;
  <<l_EndOfLoop>>
  INSERT INTO temp_table (char_col)
    VALUES ('Done!');
END;
/

Processing Queries Using Cursors

Cursor Loops

Explicitly declared cursor and record
CREATE OR REPLACE PROCEDURE <procedure_name> IS

CURSOR <cursor_name> IS
<SQL statement>

<record_name> <cursor_name>%ROWTYPE;

BEGIN
  OPEN <cursor_name>
  LOOP
    FETCH <cursor_name> INTO <record_name>;
    EXIT WHEN <cursor_name>%NOTFOUND;

    <other code>
  END LOOP;
  CLOSE <cursor_name>;
END <procedure_name>;
/
CREATE OR REPLACE PROCEDURE exp_all IS

CURSOR my_cur IS
SELECT zip_code FROM zip_city;

my_rec my_cur%ROWTYPE;


BEGIN
  OPEN my_cur;
  LOOP
    FETCH my_cur INTO my_rec;
    EXIT WHEN my_cur%NOTFOUND;

    NULL;
  END LOOP;
  CLOSE my_cur;
END exp_all;
/
Explicitly declared cursor and implicit record CREATE OR REPLACE PROCEDURE <procedure_name> IS


CURSOR <cursor_name> IS
<SQL statement>

BEGIN
  FOR <record_name> IN <cursor_name>
  LOOP
    <other code>
  END LOOP;
END <procedure_name>;
/
set serveroutput on

CREATE OR REPLACE PROCEDURE exp_and_imp IS

CURSOR my_cur IS
SELECT zip_code FROM zip_city;


BEGIN
  FOR my_rec IN my_cur
  LOOP
    NULL;
  END LOOP;
END exp_and_imp;
/
Implicitly declared cursor and record CREATE OR REPLACE PROCEDURE <procedure_name> IS

BEGIN
  FOR <record_name> IN (<SQL statement>)
  LOOP
    <other code>
  END LOOP;
END <procedure_name>;
/
CREATE OR REPLACE PROCEDURE imp_all IS

BEGIN
  FOR my_rec IN (SELECT zip_code FROM zip_city)
  LOOP
    NULL;
  END LOOP;
END imp_all;
/

Cursor Loop With
WHERE CURRENT OF Clause
CREATE OR REPLACE PROCEDURE <procedure_name> IS

<cursor definition>

BEGIN
  FOR <record_name> IN (<cursor_name>)
  LOOP
    <other code>
    UPDATE <table_name>
    SET <column_name> = <value>
    WHERE CURRENT OF <cursor_name>
  END LOOP;
END <procedure_name>;
/
CREATE TABLE test (
pid   NUMBER(3),
cash  NUMBER(10,2));

INSERT INTO test VALUES (100, 10000.73);
INSERT INTO test VALUES (200  25000.26);
INSERT INTO test VALUES (300, 30000.11);
INSERT INTO test VALUES (400, 45000.99);
INSERT INTO test VALUES (500, 50000.08);
COMMIT;

CREATE OR REPLACE PROCEDURE wco IS

CURSOR x_cur IS
SELECT pid, cash
FROM test
WHERE cash < 35000
FOR UPDATE;

BEGIN
  FOR x_rec IN x_cur
  LOOP
    UPDATE
test
    SET cash = FLOOR
(cash)
    WHERE CURRENT OF x_cur;
  END LOOP;
  COMMIT;
END wco;
/

exec wco;

SELECT * FROM test;

 


Error Handling

  1. PL/SQL uses exceptions for error handling.

  2. The basic types of exception are:
  3. The following code defines, raises and handles an exception. It also shows the use of the raise statement on its own in an exception handler to re-raise the exception and pass it to the calling routine.

    procedure doubletill_over100 (p_in IN OUT NUMBER)
    -- define an exception
    too_big EXCEPTION;
    begin
      if p_in > 128 then
        raise too_big;
      else
        p_in := p_in * 2;
      end if;
    exception
      when too_big then
        p_in = 1;
        other statements;
      when others then
        DBMS_OUTPUT.PUT_LINE('Unkown error');
        dbms_output.put_line(SQLERRM);
        raise;
    end;

Predefined Exceptions

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. To handle other Oracle errors, you can use the OTHERS handler. The error-reporting functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error numbers.
PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and SQLCODE return values. 

Exception Name

Oracle Error

SQLCODE Value

CURSOR_ALREADY_OPEN

ORA-06511

-6511

DUP_VAL_ON_INDEX

ORA-00001

-1

INVALID_CURSOR

ORA-01001

-1001

INVALID_NUMBER

ORA-01722

-1722

LOGIN_DENIED

ORA-01017

-1017

NO_DATA-FOUND

ORA-01403

+100

NOT_LOGGED_ON

ORA-01012

-1012

PROGRAM_ERROR

ORA-06501

-6501

ROWTYPE_MISMATCH

ORA-06504

-6504

STORAGE_ERROR

ORA-06500

-6500

TIMEOUT_ON_RESOURCE

ORA-00051

-51

TOO_MANY_ROWS

ORA-01422

-1422

VALUE_ERROR

ORA-06502

-6502

ZERO_DIVIDE

ORA-01476

-1476


Brief descriptions of the predefined exceptions follow:


CURSOR_ALREADY_OPEN is raised if you try to open an already open cursor. You must close a cursor before you can reopen it.
DUP_VAL_ON_INDEX is raised if you try to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR is raised if you try an illegal cursor operation. For example, INVALID_CURSOR is raised if you close an unopened cursor.
INVALID_NUMBER is raised in a SQL statement if the conversion of a character string to a number fails because the string does not represent a valid number. For example, the following INSERT statement raises INVALID_NUMBER when Oracle tries to convert 'HALL' to a number:
        INSERT INTO emp (empno, ename, deptno) VALUES ('HALL', 7888, 20);
In procedural statements, VALUE_ERROR is raised instead.

LOGIN_DENIED is raised if you try logging on to Oracle with an invalid username/password.
NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows or if you reference an uninitialized row in a PL/SQL table. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised. SQL group functions such as AVG and SUM always return a value or a null.
NOT_LOGGED_ON is raised if your PL/SQL program issues a database call without being connected to Oracle.
PROGRAM_ERROR is raised if PL/SQL has an internal problem.
ROWTYPE_MISMATCH is raised if the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when you pass an open host cursor variable to a stored subprogram, if the return types of the actual and formal parameters are incompatible, PL/SQL raises ROWTYPE_MISMATCH.
STORAGE_ERROR is raised if PL/SQL runs out of memory or if memory is corrupted.
TIMEOUT_ON_RESOURCE is raised if a timeout occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS is raised if a SELECT INTO statement returns more than one row.
VALUE_ERROR is raised if an arithmetic, conversion, truncation, or size-constraint error occurs. For example, when you select a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR.
In procedural statements, VALUE_ERROR is raised if the conversion of a character string to a number fails. For example, the following assignment statement raises VALUE_ERROR when PL/SQL tries to convert 'HALL' to a number:
DECLARE
   my_empno NUMBER(4);
   my_ename CHAR(10);
BEGIN
   my_empno := 'HALL';  -- raises VALUE_ERROR
In SQL statements, INVALID_NUMBER is raised instead.

ZERO_DIVIDE is raised if you try to divide a number by zero because the result is undefined.

 

Develop PL/SQL Program Units

Developing Stored Procedures and Functions

 

Procedure

Function

Syntax

create or replace procedure myproc (
  p_vat_rate IN NUMBER DEFAULT 17.5,
  p_price IN OUT NUMBER,
  p_vat OUT NUMBER) is
price_invalid EXCEPTION;
begin
  if p_price <= 0.0 then
    raise price_invalid;
  end if;
  p_vat := (p_vat_rate/100) * p_price;
  p_price := p_price + p_vat;
exception
  when price_invalid then
    raise_application_error(-20000, ’Price must be positive’);
  when others then
    raise_application_error(-20999, ’Unexpected error’);
end;
create or replace function vat (
  vat_rate IN NUMBER,
  price IN NUMBER) is
l_vat NUMBER;
begin
  l_vat := (vat_rate/100) * price;
  return l_vat;
end;

sqlplus

variable price number
variable vat number
exec myproc(15,:price,:vat)
 
or
 
exec myproc(p_vat=>:vat, p_price=>:price);
variable price number
variable rate number
exec :price := :price +
vat(:rate,:price);
 
or
 
select vat(15, p.price)
  from prices p
  where p.id=123;

Parameters

  1. An IN parameter may be used in the program unit but not changed. e.g. it may not appear on the left hand side of an assignment. 
  2. An OUT can be assigned to but may not be used/looked at. It may appear on the left hand side of an assignment but not the right hand side. It cannot appear in the condition of an if statement or while/for loop. 
  3. An IN OUT is effectively unrestricted – it can be used wherever a variable declared locally in the program unit can be used.

Notes

  1. The sqlplus execute (or exec) statement is a shorthand for begin … .end; 
  2. Generally, it is best to use the ‘create or replace’ statement as this preserves grants on the program unit. 
  3. Parameters and variables can be defined using the Oracle datatypes (number, varchar2, etc) or by reference to database objects e.g. 
    • sale_price product.price%type; -- defines a simple variable 
    • my_product product%rowtype; -- defines a record 
  4. raise_application_error allows the programmer to pass an error code/message to the calling environment e.g. sqlplus or forms. The error code must be in the range –20000 to –20999. The calling program unit can use pragma exception_init as follows:
    declare
       invalid_price EXCEPTION;
       pragma exception_init(-20000,invalid_price);
       l_price NUMBER := 10;
    begin
      myproc(p_vat=>16, p_price=>l_price)
    exception
      when invalid_price then
        -- handle the error;.
    end;
  5. If a function/procedure fails with an unhandled exception, Oracle rolls back DML statements to the start of the program unit and then passes the exception to the calling program unit, sql*plus, or whatever else called the program unit. 


Developing and using packages

An example package is shown below.

create or replace package pckcar as
  number_of_gets number := 0;
  procedure upd (pId in number, pDesc in varchar2);
  function  getNumberOfUpds return number;
  function  getManufacturer (pId number) return varchar2;
  pragma    restrict_references (getManufacturer, WNDS);
end;
/
create or replace package body pckcar as
  number_of_upds number := 0;
 
  procedure inc_upd is
  begin
    number_of_upds := number_of_upds + 1;
  end;
 
  function getNumberOfUpds return number is
  begin
    return number_of_upds;
  end;
 
  procedure upd (pId in number, pDesc in varchar2) is
  begin
    update car set description = pDesc
    where id = pId;
    inc_upd;
  end;
 
  function getManufacturer (pId number) return varchar2 is
  lName maker.name%type;
  begin
    select m.name into lName
    from maker m, car c
    where m.id = c.fk_id
    and c.id = pId;
    number_of_gets := number_of_gets + 1;
    return lName;
  end;
 
end;
/
  1. Packages group together related procedures, functions and variables. Variables in a package persist for the duration of a session.
  2. You need a package specification (create or replace package) and a package body (create or replace package body). The specification defines what the user of the package can see and use. It therefore follows that…
    1. Variables, functions, procedures declared in the package specification are public. Program units can be executed by anyone who has EXECUTE privilege on the package. Variables can be accessed directly using package_name.variable notation. In the example, number_of_gets, upd(), getManufacturer(), getNumberOfUpds() are public.
    2. Anything which is not declared in the package specification is private and can only be accessed (looked at, amended) by the package code itself. In the example, number_of_upds and inc_upd() are private.
  3. A user who had execute privilege on the package pckcar could issue the following commands:
    1. pckcar.number_of_gets := 100;
    2. pckcar.upd(24);
    3. name := pckcar.getManufacturer(24);
    4. ngets := pckcar.getNumberOfUpds;
    5. select id, pckcar.getManufacturer(id) from car;
  4. .but not these commands:
    1. nupds := pckcar.number_of_upds := 100;
    2. inc_upd;
  5. If a function within a package is to be used in select statements, it must first guarantee to be free of side effects. This is achieved using the pragma restrict_references as shown above for getManufacturer(). WNDS guarantees that the function ‘writes no database state’ i.e. that it does not update the database. This is sufficient for it to be used in the select list. However, as defined getManufacturer() could not be used in an order by statement because it updates a package variable. To allow it to be used in an order by we would have to remove the line of code which updates number_of_gets and change the pragma to:
  6. pragma restrict_references (getManufacturer, WNDS, WNPS);
  7. Compile a package which is already on the database, even if only the package body is marked as invalid, by:
  8. alter package pckcar compile;
  9. Oracle supplied packages include:
    1. dbms_job: to schedule jobs
    2. dbms_lock: lets you request a lock, release a lock etc.
    3. dbms_output: to output to sqlplus
    4. dbms_pipe: lets two database sessions communicate
    5. dbms_sql: to issue dynamic sql statements
    6. dbms_system: lets you, for example, enable trace for a session
    7. dbms_utility.analyze_schema(‘schemaname’, ‘ESTIMATE_ROWS’): to analyze all objects in a schema
    8. dbms_utility.compile_schema(‘schemaname’): to compile all objects in a schema
  10. views
    1. user_tab_privs holds privileges for packages!
    2. user source holds the source of a package.


Running Packaged Procedures

CREATE OR REPLACE
  PROCEDURE RUN_PACKS IS
  BEGIN
     FIN_PACK.UPDATE_SAL (999999, 67500);
      DBMS_OUTPUT.PUT_LINE(FIN_PACK.AVG_SAL);
  END;

In SQL*Plus
set serveroutput on
EXECUTE run_packs.UPDATE_SAL(999999,67500);
 

Developing Database Triggers

Built in Packages Available
- DBMS_OUTPUT displays to the screen
- DBMS_PIPE communicates between sessions
- DBMS_LOCK manages program locks
- DBMS_JOB allows you to schedule jobs
- UTL_FILE reads from and writes tp operating system files
- DBMS_SQL for dynamic SQL and DDL

PL/SQL Tables
Used to perform array processing, must contain Primary Key
DECLARE
     TYPE cust_name IS TABLE OF  customer.name%TYPE
            INDEX BY BINARY_INTEGER;
     STORED_NAME    cust_name;
BEGIN
     SELECT name INTO stored_name(1) FROM customer
      WHERE rownum = 1;
      DBMS_OUTPUT.PUT_LINE(stored_name(1));
END;

Tables using %ROWTYPE
Allows a PL/SQL table to inherit a tables definition

DECLARE
     TYPE cust_rec IS TABLE OF  customer.name%ROWTYPE
            INDEX BY BINARY_INTEGER;
     STORED_CUST    cust_rec;
BEGIN
     SELECT * INTO stored_cust(1) FROM customer
      WHERE rownum = 1;
      DBMS_OUTPUT.PUT_LINE(stored_name(1).name);
END;

 

PL/SQL Table Methods
Assists with table handling
table_name.method(parameters) e.g. stored_cust.EXISTS(1)

BEGIN
     SELECT * INTO stored_cust(1) FROM customer
      WHERE rownum = 1;
      IF stored_cust.EXISTS(1)
         THEN
            DBMS_OUTPUT.PUT_LINE(stored_name(1).name);
      END IF;
END;


PL/SQL Table Methods

Methods available includes

stored_rec.COUNT        -- Number of elements in the table
stored_rec.FIRST           -- Goes to first record
stored_rec.LAST            -- Goes to last record
stored_rec.PRIOR (n)     -- Returns the preceding index number
stored_rec.NEXT (n)      -- Returns the next index number
stored_rec.TRIM            -- Removes the last element in table
stored_rec.TRIM(n)       -- Removes the last n elements in table
stored_rec.DELETE           -- Deletes all elements from table
stored_rec.DELETE(n)      -- Deletes the nth elements from table
stored_rec.DELETE(m, n) -- Deletes elements in range m to n
stored_rec.EXTEND          -- Appends one null row to table
stored_rec.EXTEND(n)      -- Appends n null rows to table
stored_rec.EXTEND(n, I)   -- Appends n copies of the I row to table

Top of page