Thursday, July 29, 2010

pl/sql introduction

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.

=======================























------------------

No comments:

Post a Comment