This blog is basically serving as my study notes. just posting so that others may prepare also in their last xam day.
PL-- Programming language + SQL
SQL statement selected data put in to variables for further processing/logical implementation.
Programming principles like--> IF else
looping, conditional statements etc can be applied on SQL.
PL/SQL engine::-
1. available on server side/client side
2. help in reducing the network traffic.
3. data security
three sections
1. declaration section
v_fab varchar(34) ;
2. executable section -- mandatory
Begin
3. exception section
exception
end;
four main components
Anonymous block-- server side,
stored procedure
packages
triggers
scope and visibility of variables
Substitution variables
1. & -- it will ask for input n times if same substitution variable used n times
2. && -- It will ask for substitution variable only once and put the first input value in all places where sub is there.
Substitution variables can be switched off with the help of set define on/off
we can also change the character & with some other character by
set define @;
============================
labels
< >
SQL into clause
DML in PL/SQL
TCL in PL/SQL
Control structure
if....... then.. end if
if then else end if
if then elsif then else then end if
case
when condition then
else
end case;
loop end loop;
while condition loop....end loop;
for in
loop.. end loop;
---------------------------
data dictionary views
User_source
user_objects
user_dependencies
procedure_name
function_name
user_error
----------------------
stored functions
return only one value
invoked in expression
contains additional return datatype syntax
------------------------
%type
%rowtype
--------------------
record type
TYPE a IS REECORD
(A1 NUMBER(2)
B1 numebr (2));
-----------------------
PL/SQL table
Type t is table of emp%rowtype
INDEX BY BINARY INTEGER;
we can put record in table one by one and later retrieve without committing;
we can table records by following method
t(i).a1
pl/sql table functions
count
exists(n)
first, last can be used in for loop
next (n), prior(n)
delete(n)
----------------
less trips on network
PL/SQL - Blocks, 1. named, 2. anonymous
Subroutines :- package, function, procedure
declarative section
executable
exception -- runtime error
compilation : syntax checking, binding, p-code generation
For named blocks, p-code is stored in the database, and it is used the next time the
program is executed. As soon as the process of compilation has completed successfully, the
status of a named PL/SQL block is set to VALID, and it is also stored in the database. If the
compilation process was unsuccessful, the status of a named PL/SQL block is set to INVALID.
ASCII
SELECT i.first_name||' '||i.last_name, COUNT(*)
INTO v_name, v_total
FROM instructor i
JOIN section s
ON (i.instructor_id = s.instructor_id)
WHERE i.instructor_id = 102
GROUP BY i.first_name||' '||i.last_name;
=========================
Successful compilation of the named PL/SQL block does not guarantee successful execution of this
block in the future. At the time of execution, if any one of the stored objects referenced by the block
is not present in the database or is inaccessible to the block, execution fails. At such time, the status
of the named PL/SQL block is changed to INVALID.
========================
Substitution variables cannot be used to output
values, because no memory is allocated for them
===============================
When a substitution variable is assigned to the string (text) datatype, it is a good practice to
enclose it in single quotes
=================
As mentioned earlier, substitution variables usually are prefixed by the ampersand (&) or double
ampersand (&&) characters. These are default characters that denote substitution variables. A
special SET command option available in SQL*Plus allows you to change the default character
(&) to any other character or disable the substitution variable feature. This SET command has
the following syntax:
SET DEFINE character
or
SET DEFINE ON
or
SET DEFINE OFF
===========================
SET SERVEROUTPUT ON;
or
SET SERVEROUTPUT ON SIZE 5000;
========================
remember exception scope and propagation rules
====================
case: A runtime error occurs in the declaration section of the block.
If there is no outer block, execution of the program halts, and control is passed to the host environment.
===========================
A runtime error occurs in the exception-handling section of the
block. Just like the previous case, if there is no outer block, execution of the program halts, and
control is passed to the host environment
===========================
However, the RAISE statement can explicitly raise an exception
in the exception-handling section of the block. Consider the following example:
FOR EXAMPLE
--outer block
DECLARE
e_exception1 EXCEPTION;
e_exception2 EXCEPTION;
BEGIN
-- inner block
BEGIN
RAISE e_exception1;
EXCEPTION
WHEN e_exception1 THEN
RAISE e_exception2;
WHEN e_exception2 THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the inner block');
END;
EXCEPTION
WHEN e_exception2 THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred in '||
'the program');
END
========================
RERAISING AN EXCEPTION
On some occasions you may want to be able to stop your program if a certain type of error
occurs. In other words, you may want to handle an exception in the inner block and then pass
it to the outer block. This process is called reraising an exception. The following example helps
illustrate this point:
FOR EXAMPLE
-- outer block
DECLARE
e_exception EXCEPTION;
BEGIN
-- inner block
BEGIN
RAISE e_exception;
Exception Propagation LAB 9.3
201
EXCEPTION
WHEN e_exception THEN
RAISE;
END;
EXCEPTION
WHEN e_exception THEN
DBMS_OUTPUT.PUT_LINE ('An error has occurred');
END;
=======================
Exceptions :- Advanced concepts
Raise application error
Exception_inti pragma
SQLCODE, SQLERRM
RAISE_APPLICATION_ERROR
======================
Works with oracle defined as well as user defined exceptions,
give look and feel of the oracle error message.
associates a error number with error message
error number can be between -20999 to -20000.
syntax
raise_application_error(error_code, error_message, keep errors)
declare
v_num varchar(30);
begin
if v_num < 49
raise_application_error(-20000, 'numer is less than forty');
end;
================
exception init pragma
associates a user defined error message to oracle error which has oracle assigned name but not the error name.
declare
exception e_pragma;
pragma exception_init(e_pragma, -3393
);
===============
To improve the error-handling interface of your program, Oracle gives you two
built-in functions, SQLCODE and SQLERRM, used with the OTHERS exception handler. The
SQLCODE function returns the Oracle error number, and the SQLERRM function returns
the error message. The maximum length of a message returned by the SQLERRM function is
512 bytes.
============
DECLARE
v_zip VARCHAR2(5) := '&sv_zip';
v_city VARCHAR2(15);
v_state CHAR(2);
v_err_code NUMBER;
v_err_msg VARCHAR2(200);
BEGIN
SELECT city, state
INTO v_city, v_state
FROM zipcode
WHERE zip = v_zip;
DBMS_OUTPUT.PUT_LINE (v_city||', '||v_state);
EXCEPTION
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);
END;
===================
recurrence.
Generally, the SQLCODE function returns a negative number for an error number. However,
there are a few exceptions:
. When SQLCODE is referenced outside the exception section, it returns 0 for the error
code. The value of 0 means successful completion.
. When SQLCODE is used with the user-defined exception, it returns +1 for the error code.
. SQLCODE returns a value of 100 when the NO_DATA_FOUND exception is raised.
=======================
------------------