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:
VERIFY ON/OFF = Show/Hide the lines with replacements
FEEDBACK ON/OFF = Show/Hide rows returned by the query
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.
HEADING OFF/ON = Disable/Enable Column HeadersLINESIZE Num = # of characters per line, 80 char by
default, max is 32767
DEFINE = Shows all the saved values and its status.
ECHO ON/OFF = Show/Hide what is doing
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 immediately 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 ---
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)
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 |
|
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) |
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….) |
Whichever of e1, e2, etc is the greatest |
|
least(e1,e2,….) |
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:
a primary key (pk_id)
a unique key (unq_name)
a foreign key (fk_area)
a column check constraint (m_or_f)
a table check constraint (dor_after_dob)
create table VOTER
id number(6) constraint pk_id primary key,
name varchar2(30) not null constraint unq_name unique,
sex varchar2(1) not null constraint m_or_f check (sex in (‘M’,’F’)),
dob date not null,
area varchar2(5) not null constraint fk_area references areas(code),
dor date,
constraint dor_after_dob check (dor > dob)
);
Altering
Tables
and Constraints
Add a column:
alter table voters add ( post_code
varchar2(7));
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));
Add a constraint (table syntax):
alter
table
voters add (constraint name_not_fred check (name !=
‘Fred’));
Disable a constraint:
alter table voters disable
constraint name_not_fred;
Enable a constraint:
alter table voters enable constraint
name_not_fred;
Remove a constraint:
alter table voters drop constraint
name_not_fred;
Amend a column:
alter table voters modify (post_code
varchar2(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;
Delete all rows using one of the commands
below:
truncate table voters;
delete from voters;
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;
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';
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
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.
Getting the next value from a sequence:
select cust_seq.nextval from dual;
Getting the most recently selected value
from the sequence
select cust_seq.currval from dual;
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;
Remove a sequence:
drop sequence cust_seq;
Controlling
User
Access
Database objects are owned by users whose
access is password protected. Users can GRANT other users
access to their objects – this can include
granting another user the right to pass on the grant to
third parties.
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;
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;
Change a users password with:
alter user fredg identified by
changedpassword;
Privileges are revoked using the REVOKE
command:
revoke select on stats from
MANAGER_ROLE;
Create a private synonym (works only for
user who creates the synonym):
create synonym VOTERS for
herbieg.voters;
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
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 WHEN get_customer%NOTFOUND;
-- Exit the loop after all
rows have been retreived.
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)
/* 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
Conditions
IF counter = 1 THEN
DBMS_OUTPUT.PUT_LINE('counter is 1') ;
ELSIF counter = 2 THEN
DBMS_OUTPUT.PUT_LINE('counter is 2') ;
ELSIF counter = 3 THEN
DBMS_OUTPUT.PUT_LINE('counter is 3') ;
ELSE
DBMS_OUTPUT.PUT_LINE('counter is something else') ;
END IF;
An IF statement must have one IF, it can have zero or more ELSIFs and a maximum of 1 ELSE.
IF statements can be nested to any depth.
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
PL/SQL uses exceptions for error handling.
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;
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 ( |
create or replace function vat ( |
sqlplus |
variable price number |
variable price number |
Parameters |
|
|
Notes |
|
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;
/
pragma restrict_references (getManufacturer, WNDS, WNPS);
alter package pckcar compile;
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
CREATE OR REPLACE TRIGGER AUDCAR
BEFORE INSERT OR DELETE OR UPDATE ON CAR
DECLARE
action varchar2(1);
BEGIN
IF INSERTING THEN
action=’I’;
ELSIF UPDATING THEN
action=’I’;
ELSIF DELETING THEN
action=’I’;
END IF;
insert into action_audits values (‘CAR’,action,user);
END;
A row level trigger is created with the
following syntax:
CREATE
OR
REPLACE TRIGGER TRGCAR
BEFORE
INSERT
OR DELETE OR UPDATE OF ID, DESCRIPTION
ON
CAR
FOR
EACH
ROW
WHEN
(nvl(new.description,’X’)
!= ‘Focus’)
DECLARE
BEGIN
IF
INSERTING THEN
update
stats set number_of_cars = number_of_cars + 1;
ELSIF
UPDATING THEN
IF
:new.description != :old.description THEN
update
stats set number_of_changes = number_of_changes + 1;
END
IF;
ELSIF
DELETING THEN
update
stats set number_of_cars = number_of_cars - 1;
END
IF;
END;
Enable a trigger with:
alter trigger trgcar enable;
Disable all triggers on a table:
alter table car disable all triggers;
Enable all triggers on a table:
alter table car enable all 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