plsql

PL/SQL

Pl/SQL woks both in the Oracle Sever(through stored procedures, stored functions, database triggers and packages), and in the Oracle Development Environment(Oracle Forms, Oracle Reports, and Oracle Graphics)

It supports SQL Data types. PL SQL bridges the gap and the need for procedural programming capabilities.

Benefits of PL/SQL :

Improved performance :

It is used to group SQl statements together within a single block and to send the entire block to the server in a single call, thereby reducing the network traffic.

Modularised program Development :

Group logically related statements within blocks.

Nest sub-blocks inside larger blocks to build a powerful program

Break down complex problems into a set of manageable, well-defined , logical modules

Place reusable PL/SQL code in libraries to be shared between Oracle applications or store it in an Oracle server to make it available to any user-application.

Portability :PL/SQL programs can run anywhere the Oracle server is installed, you do not need to tailor them to each new environment.

Identifiers :

It allows you to declare variables, cursors, constants, and exceptions and then use them in SQL and Procedural statements.

It allows to declare variable belonging to scalar, reference, composite and LOB data types

Declare variable based on the columns in the tables.

Procedural language Control Structures :

Execute a sequence of statements conditionally

Execute a sequence of statements iteratively in a loop

Process the individual rows returned by a multi-row query with an explicit cursors.

Error Handling :

Process Oracle-server errors with exception handling routines

Declare user-defined error conditions and process them with exception-handling routines

Structure of a PL/SQL Block

Declare(optional)

Contains all the variables, constants, cursors and user-defined exceptions that will be used in the executable section

Begin(Mandatory)-----Executable section

Contains SQL statements to manipulate data in the database and PL/SQl statements to manipulate data in the data-block

Exception(Optional)

Contains actions to be performed when errors and abnormal conditions arise in the executable section

End;

set serveroutput on - for dbms_output.put_line to give output in SQL

declarevname students.studname%type;vtotal students.total%type;

beginselect studname into vname from students where rollno=1;select total into vtotal from students where rollno=1;dbms_output.put_line('Name of Student is :'||vname);dbms_output.put_line('Total of Student is :'||vtotal);

end;/

PL/SQL Block Types

Anonymous Blocks:

These are unnamed blocks. They are declared in an application where they need to be executed and are passed to the Server for execution at run-time. Example : Triggers in Oracle Developer consists of such blocks

[Declare]

Begin

[Exception]

End;

Sub-Programs :

These are named PL/SQL blocks that can accept parameters and can be invoked as and when required. They are declared as Procedures or Functions. Generally use procedures to perform an action and a function to compute and return a value.

Procedure name is

Begin--statements

[Exception]

End;

Function name return datatypeIsBegin

--statementsreturn value;

[Exception]

End;

Declaring PL/SQl variables

DeclareBirthdate date;Deptno number(2) NOT NULL : =10;Location varchar2(20) : = ‘Mumbai’;Salary constant number : = 10000

PL/SQL variables1) Scalar

These are the data types that correspond to the data types of columns in tables. PL/SQL also supports Boolean datatype

Character, Numeric Date and Boolean types of data types.

The %type attribute. Instead of hardcoding the data type and size of a variable, you can use the %type attribute to declare a variable according to another previously declared variable or a column of a table.

Example : vsalary employees.salary%type

vgrade number(7,2);vvgrade vgrade %type:=10;

Boolean data types(only stores NULL, TRUE , FALSE)vflag Boolean;vvflag Boolean :=TRUE

2) CompositeComposite data types such as records, allows groups of fields to be defined and manipulated in PL/SQL blocks. They have internal components that can be handled and manipulated individually. Also known as Collections. They are of 4 types : TABLE, RECORD, NESTED TABLE and VARRAY.

RECORD:A record is a group of related data items stored as fields, each with it’s own name and data type. Must contain one or more components of any scalar type. Example , you have different kinds of data about an employee like name, salary, hire date. This data is dissimilar in type but logically related.

Type type_name IS RECORD( field declaration, field declaration, ….);

variable type_name;

Type emp_data IS RECORD(first_name varchar2(10),last_name varchar2(10));

emp_record emp_data;

To reference or initialize an individual field :Record_name.field_name;

Emp_record.first_name:=’Ramesh’;

Declaring Records with %rowtype

The %rowtype attribute is used to declare a record based on a collection of columns in a database table or view

Example : Declare

Emp_record employees%rowtype;

The number and data types of the underlying database columns need not be known.The number and the data types of the underlying database column may change at run time.This attribute is useful when retrieving a row into variables with the Select * From tablename.

TABLE :Objects of TABLE type are called INDEX By TABLES. They use a primary key to provide you with array-like access to rows.

An INDEX BY Table :Is similar to an arrayMust contain 2 components

A Primary Key of data type Binary_Integer that indexes the INDEX BY TABLEA Column of a scalar or record data type, which stores the INDEX BY Table elements.

Can increase dynamically because it is unconstrained.

Type type_name is Table of column_type/variable%type/table.column%type/table.%rowtype

INDEX BY BINARY_INTEGER;

identifier type_name;

Type ename_table_type IS TABLE OF employees.first_name%type INDEX BY BINARY_INTEGER;

Ename_table ename_table_type;

Initially such a table is not populated, it contains no keys or no values. An explicit executable statement is required to initialize (populate) the Index By Table.

Type ename_table_type IS TABLE OF employees.first_name%type INDEX BY BINARY_INTEGER;

Type birthdate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;

Ename_table ename_table_type;Birthdate_table birthdate_table_type;

Ename_table(1) :=’Ramesh’;Birthdate_table(1) :=sysdate-10;

Following methods are used by the INDEX BY Tables :Exists(n)-returns TRUE if the nth element existsCount-returns number of elements that a PL/SQL table currently containsFIRST, LAST- returns the first and last index numbersPrior(n)- Returns the index number that preceeds the index n in a tableNext(n)- Returns the index number that succeeds the index n in a tableTRIM-removes oen element from the end of the PL SQL TableTRIM(n)-removes n elements from the end of the PLSQL TableDELETE- removes all elements from PL/SQL tableDELETE(n)-removes the nth element from the PLSQL table.DELETE(m,n)-removes all elements in the range m…n from a PL SQL table.

Example :Set server output on;

DeclareType ename_table_type IS TABLE OF employees%rowtype INDEX BY BINARY_INTEGER;

My_emp_table emp_table_type;V_count number(3):=104;

BeginFor i in 100 .. v_countLoop

Select * into my_emp_table(i) from employees where employee_id=i;

End loop;

For i in my_emp_table.first .. my_emp_table.lastLoop

Dbms_output.put_line(my_emp_table(i).first_name);End loop;

End;/

3) ReferenceThese are the pointers to other program items.(Not a part of the syllabus)

4) LOBsAlso called locators that specify the location of large objects(such as graphic images that are stored out of line.

Conditional Constructs

IF Statement

If-then- end if;

If <condition(s)> thenStatement 1;Statement 2;

End if;

If-then-else-end if;


Else(optional)Statement 3;Statement 4;

End if;

If-then-elsif-elsif-else-end if;


Elsif <condition(s)> thenStatement 3;Statement 4;

Elsif <condition(s)> thenStatement 5;Statement 6;

Else(optional)Statement 7;Statement 8;

End if;

PL/SQL Block to display "Very Good Student", if total of Rollno=1 is more than 275, else display "Average student"


beginselect studname into vname from students where rollno=1;select total into vtotal from students where rollno=1;

if vtotal>=250 thendbms_output.put_line(Vname||' is a very good student');

elsedbms_output.put_line(Vname||' is an average student');

end if;end;/

PL/SQL Block to display "Very Good Student", if total of Rollno=1 is more than 275, "Average student" if total between 250 and <275, and "poor student" if total less than 250


beginselect studname into vname from students where rollno=1;select total into vtotal from students where rollno=1;

if vtotal>=275 thendbms_output.put_line(Vname||' is a very good student');

elsif vtotal>=250 and vtotal<275 thendbms_output.put_line(Vname||' is an average student');

elsedbms_output.put_line(Vname||' is a poor student');

end if;end;/

Nesting of Ifs allowed as per situation.

CASE Expressions

Case selectorWhen expression1 then result1When expression2 then result2When expression3 then result3

When expressionN then resultN

Else resultN+1(optional)End;

Set serveroutput on;

Declarevgrade char(1):=upper(‘&p_grade);vappraisal varchar2(20);

BeginVappraisal :=

CASE vgradeWhen ‘A’ then ‘Excellent’When ‘B’ then ‘Very Good’When ‘C’ then ‘Good’Else ‘No such grade’

End;

Dbms_output.put_line(‘Grade ‘||vgrade||’ Appraisal ‘ || vappraisal);End;/

LOOPS 1) For <variable> in [reverse] lowvalue..highvalue

loopstatement1;statement2;

end loop

Numbers 1 to 10 in orderdeclare

num number(2);begin

for num in 1..10loop

dbms_output.put_line('number '|| num);end loop;

end;/Numbers 1 to 10 in reverse orderdeclare

num number(2);begin

for num in reverse 1..10loop

dbms_output.put_line('number '|| num);end loop;

end;/

Print odd and even numbers from 1 to 20 and their countdeclare

a number:=0; b number:=0;begin For x in 1..20 loop if mod(x,2)=0 then dbms_output.put_line('Number '||x||' is Even'); a:=a+1; else dbms_output.put_line('Number '||x||' is Odd'); b:=b+1; end if; end loop; dbms_output.put_line('There are '||a||' even numbers'); dbms_output.put_line('There are '||b||' odd numbers');end;/

While <condition>loop

statement1;statement2;

end loop;

print 10,20,30,...100declare

num number:=10;begin

while num<=100loop

dbms_output.put_line(num);num:=num+10;

end loop;end;/

loopstatement1;statement2;exit when <condition>;

end loop;

print 5.10,15,20,....,50declare

num number:=5;begin

loopdbms_output.put_line(num);num:=num+5;

exit when num>50;end loop;

end;/

CURSORS

Cursors are names given to private SQL work areas created by the Oracle server to execute SQL statements and to store the processing information. There are 2 types of cursors :

IMPLICITThese are declaredc by PLSQL implicitly for all DML and PLSQL Select statements, including queries that return only one row.

EXPLICITFor queries that return more than one row, explicit cursors are declared and named by the programmer, and manipulated in the block’s executable section.

Explicit CursorsUsed to process each row returned by a multiple-row Select statement.Following are the steps followed for handling explicit cursors :1)declareHere we declare the cursor by naming it and define the structure of the query to be performed within it.

Declare Cursor cursor_name is Select ……;

2)OpenThis statement executes the query. The row returned by the query are called the active set and are now available for fetching

Open Cursor_name;

3)Fetch and processHere you actually fetch the rows into variable if required and carry out all the business logic or processing.

LoopFetch cursor_name into variable1, variable 2;

Exit When End loop;

4)closeIt releases the active . It is now possible to reopen the cursor to re-establis a fresh active set.

Close Cursor_name

Do not use the INTO clause in the cursor declaration.

If the row need to be processed in a particular order, you may use the order by clause in the declaration.

to print the name, total of all the studentsdeclare

cursor cstudent is Select Rollno,studname,total from students;vrollno students.rollno%type;vname students.studname%type;vtotal students.total%type;

beginOpen cstudent;loop

fetch cstudent into vrollno, vname, vtotal;exit when cstudent%notfound;dbms_output.put_line('Roll number :'||vrollno);dbms_output.put_line('Name :'||vname);dbms_output.put_line('Total :'||vtotal);

end loop;close cstudent;

end;/

Here. exit when cstudent%notfound should be done immediately after fetch, or else the last record will be displayed twice. "notfound" is a cursor attribute which is used to find whether the cursor currently contains a record or has reached the end of the file or recordset

Explicit Cursor attributes

%isopen – Evaluates to True if the cursor is open%notfound- Evaluates to TRUE if the most recent fetch does not return a row%found- Evaluates to TRUE if the most recent fetch returns a row %rowcount-Evaluates to the number of rows returned so far

Example to retrieve row from employees table and populate them to another table with a similar structure

DeclareCursor emp_cursor is select empid, last_name from employees;Emp_record emp_cursor%rowtype;

BeginOpen emp_cursor;

LoopFetch emp_cursor into emp_record;Exit when emp_cursor%notfound;Insert into temp_emp(empno, name) values(emp_record.empid, emp_record.last_name)

End loop;Commit;Close emp_cursor;End;/

Cursor For Loop

It is a shortcut for processing explicit cursors, because the cursor is opened, rows are fetched once for each iteration in the loop, the loop exits when the last row is processed, and the cursor is closed automatically.

For record_name IN Cursor_nameLoop

Statement1;Statement2;

End loop;

Retrieving the employees one by one and printing them if they belong to department 80.

Set serveroutput on;

DeclareCursor emp_cursor is select first_name , deptno from employees;

BeginFor emp_record IN emp_cursor --implicit open and fetch occursLoop

If emp_record.deptno=80 thenDbms_output.put_line(‘Employee ‘ ||emp_record.first_name);

End if;End loop; --implicit close and implicit loop exit

End;/

Cursors with Parameters

You can pass parameters to the cursors. Thus you can open and close an explicit cursor several times in a block. Each time you open the cursor with a different set of values, returning a different active set. For each execution, the previous cursor is closed and reopnened with a new set of values.

In the cursor declaration, you need to specify the FORMAL parameters, and each of there formal parameters will have to be passed an ACTUAL parameter or value in the Open statement.. You have to specify the Formal parameter data types, but not the size.

Declare cursor emp_cursor(p_deptno number, p_job varchar2) is select employee_id, last_name from employees whre department_id=p_deptno and job_id=p_job;

BeginOpen emp_cursor(80, ‘Sales_Rep’);For emp_record IN emp_cursor Loop

Dbms_output.put_line(‘Employee no. : ‘ || emp_record.employee_id ||’ Employee name :’ || emp_record.last_name);

End loop; Close emp_cursor

Open emp_cursor(60, ‘Programmer’);For emp_record IN emp_cursor Loop

Dbms_output.put_line(‘Employee no. : ‘ || emp_record.employee_id ||’ Employee name :’ || emp_record.last_name);

End loop; Close emp_cursor

End;/

For Update [Of column] clauseSometimes you may want to perform some update or delete based on the values that the cursor has returned to you. Hence it is important that no other user /transaction modifies those values, since the update/delete which you want to perform is based on those values. To prevent any other user/transaction from changing those values, you can lock those row retuned by the cursor through the FOR Update clause.

[It is not necessary that the for update clause refers to a column , but it is recommended by Oracle for better readability and maintenance.]

Also, when querying multiple tables with a join, you can use the For update clause to ensure row locking to particular table of the join. Only that table’s rows will be locked whose column is referred to in the For update [of column] clause.Sometimes it may happen that the rows which you want to lock are already locked by another user/transaction.In such a case, because of the for update

clause your block will keep on waiting till the rows are released by the other user/transaction.. To avoid this type of waiting, you can use the NOWAIT clause with the For update clause, so that instead of waiting indefinitely, the control is immediately passed to your program so that it can do some other work before trying to acquire the lock.

DeclareCursor emp_cursor is Select employee_id, last_name, department_name from employees, departments where employees.department_id=departments.department_id and employees.department_id=60 FOR Update of salary NOWAIT;

Where current Of clauseSometimes you may want to update or delete the row being currently addressed. This is possible through the WHERE CURRENT OF clause, without the need to refer to the ROWID.. But for this you need to lock the rows with the FOR UPDATE clause so that the rows are locked on open.

DeclareCursor sal_cursor is Select e.department_id, employee_id, last_name, salary from employees e, departments d where d.department_id=e.department_id and d.deprtment_id=60 FOR UPDATE OF SALARY NOWAIT;

BeginFor emp_record in sal_cursorLoop

If emp_record.salary<5000 thenUpdate employees set salary=emp_record.salary*1.10 WHERE CURRENT OF sal_cursor;

End if;End loop;

End;/

Cursors with subqueriesDeclare

Cursor my_cursor is Select t1.department_id, t1.department_name, t2.staff from departments t1, (Select department_id, count(*) as staff from employees group by department_id) t2 where t1.department_id=t2.department_id and t2.staff>=3

When this cursor is opened, the active set will contain the department number, department name, and the total number of employees working in that department, such that there are more than 3 employees working in each such department.

EXCEPTIONSAn exception is an identifier in a PL SQL Block that is raised during the execution of a block that terminates it’s main body of actions.. In such cases, you can specify an exception handler to perform some other actions.

Exceptions are raised in 2 ways :An oracle-defined error occurs and the appropriate exception is raised automatically.You explicitly raise an exception by issuing the RAISE statement within the block. The exception being raised may be user-defined or pre-defined.

When an exception is raised, the control is passed to the exception handler in the exception section. If the exception is successfully handled, then the control is not passed back to the enclosing block or environment or point where the exception was raised, but the PL SQl block terminates successfully. But, If an exception is raised and there is no corresponding exception handler, then the PL SQL block terminates with failure and the exception is propogated to the calling environment.

Exception types

Implicitly raised : Predefined Oracle Server : 20 most common errors. Do not declare them and oracle server will raise them implicitly

No_data_foundToo_many_rowsInavlid_cursorZero_divideDup_val_on_index, etc

Non Predefined Oracle Server : Other Oracle server errors. Declare them in the declaration section, and oracle server will raise them implicitly

Explicitly raised :User-defined : A situation which is abnormal from the user point of view. Declare them in the declaration section and raise them explicitly

Declare

Begin

ExceptionWhen Exception1 [or exception2…..] then

Statement1;Statement2;

When Exception3 [or exception4…..] thenStatement3;Statement4;

[When others thenStatement5;Statement6;]

End;/The exception handling section traps only those exceptions that are specified, others are not trapped, unless you use the OTHERS exception handler, which traps any exception which is not yet handled. Hence, OTHERS is the last exception handler that is defined optionally

Guidelines for Trapping Exceptions :

Begin the Exception-handling section with the EXCEPTION KeywordDefine the required number of exception-handlers, each with it’s own set of actionsWhen an exception occurs PL SQL processes only one Exception-handler before leaving the blockPlace the OTHERS clause after all the other exception-handlersThere can be only one OTHERS clauseExceptions cannot appear anywhere else, besides the Exception section

Trapping Predefined Exceptions :declare

vtotal students.total%type;begin

select total into vtotal from students where rollno=16;dbms_output.put_line('the roll number is not correct');

exceptionwhen no_data_found then

dbms_output.put_line('Check the roll number');end;/

Here, since the data is not found, an error has occurred and the control is transferred to the exception handler, and the line which is after the line that caused the error is not executed, since when the error is encountered, the control is not passed to the immediate next line, but to the exception handler.

declarevtotal students.total%type;

beginselect total into vtotal from students where total>100;

exceptionwhen too_many_rows then

dbms_output.put_line('More than 1 row selected');end;/

Trapping Non-Predefined Exceptions :There are certain non-predefined standard Oracle errors, which always return a standard Oracle server error number, but are not associated with name(exception name/error name). Such errors can be trapped by declaring it first , or by using the OTHERS handler. They are raised implicitly.

The PRAGMA EXCEPTION_INIT utility tells the compiler to associate an exception name with an Oracle error number. After doing this , you may refer to that exception by name and write a specific handler for it. PRAGMA directs the PL SQL compiler to associate all occurrences of that Oracle error number with the given exception name within that block.

Example : Oracle returns error number –2292 when you try to delete a parent record which has got some child records.

Define p_deptno=20

Declaree_emps_existing EXCEPTION;PRAGMA EXCEPTION_INIT(e_emps_existing, -2292);

BEGINDelete from departments where department_id=&p_deptno;Commit;

ExceptionWhen e_emps_existing then

Dbms_output.put_line(‘Cant delete department ‘ ||to_char(&p_deptno) ||’ It has got employees existing ‘);

End;/

Functions for trapping Exceptions :

SQLCODE-returns the numeric value for the error codeSQLERRM-Returns the message associated with the error number

SQLCODE value Description0 No Exception encountered1 User-defined exception+100 No_data_found exceptionnegative number Some standard Oracle server error number

Example : to store all the error-numbers and error-messages in an ERRORS tableDeclare

v_error_code number;v_error_message varchar2(200);

Begin

Exception

When OTHERS thenRollback;v_error_code:=SQLCODE;v_error_message :=SQLERRM;insert into errors values(v_error_code, v_error_message);

End;/

Trapping User-Defined Exceptions :Declare the exception in the declarative sectionRaise the exception in the executable sectionHandle the exception in the Exception-handling section

declarelow_mark exception;venglish students.english%type;

beginselect english into venglish from students where rollno=2;if venglish<35 then

raise low_mark;end if;

exceptionwhen low_mark then

dbms_output.put_line('Failed in English');end;/

if marks in english are less than 35 then the exception is raised and the message is displayed.

Define p_department_desc =’Planning n esearch’Define p_department_number=20

Declaree_invalid_department EXCEPTION;

BeginUpdate departments set department_name=’&p_department_desc’ where department_id=&p_department_number;If SQL%NOTFOUND then

Raise e_invalid_department;End if;Commit;

ExceptionWhen e_invalid_department then

Dbms_output.put_line(‘No such department number’);End;/

Here no_data_found cannot be used , since it is an update statement and not a select statement.

Sub-blocks can handle an exception or pass the exception to the enclosing block

Declare……

e_no_rows exception;e_integrity exception;pragma exception_init(e_integrity, -2292);

BeginFor erec in emp_cursor loopBegin

Select ……..Update…….If SQL%NOTFOUND then

Raise e_no_rows;End if;

End;End loop;

ExceptionWhen e_integrity then…….When e_no_rows then…….

End;/

Raise_Application_Error Procedure :This procedure is used to communicate a predefined exception/error with a non-standard error number and a user-friendly error message from stored sub-programs. It also handles the unhandled exceptions.

Raise_application_error(error_number, error message)

Error_number : must be a user- specified number for exceptions between -20000 to -20999

Error message: user-specified message upto 2048 bytes long.

Raise_application_error can be used in either(or both) the executable section and the exception section. The error number and message are displayed to the user just like a predefined, nonpredefined or userdefined error

ProceduresA procedure is a type of sub-program that performs an action. It can stored in the database as a schema object.. It is a named PL SQL block that can accept parameters(called arguments). It has a Header section, declaration section, executable section and an optional exception-handling section.

Create or Replace Procedure procedure_name [parameter1 [mode] datatype, parameter2 [mode] datatype, …] -- headerIs/AS--this section after is/as and before the begin is the declarative sectionPL SQl block --- executable and an optional exception section

The pl sql block starts with either a BEGIN or the declaration of local variables and ends with END or End procedure_name.

Formal versus Actual Parameters :

Formal parameters are the variables declared in the parameter list of the procedure declaration

Create or Replace procedure hikesal(p_empid number, p_empsal number)…..

End hikesal;

Actual parameters are the variables or expressions passed while calling the procedure

Execute hikesal(v_empid, 2000);Parameter Modes :IN(default) : Value passed to the procedure from the calling environment. The Formal parameter acts as a constant and only takes the values passed by the calling environment. It’s value cannot be changed in the procedure body. But it can take a default value. The actual parameter can be a constant, expression, or an initialised variable.

OUT: Returned to the calling environment. Cannot be assigned a default value. Must be passed as a variable.

In OUT: Passed to the procedure, and then returned to the calling environment. Cannot be assigned a default value. Must be passed as a variable.

Stored Procedure to display employee numbers and their salaries for department 10

create or replace procedure retemp(veno IN number, vename OUT varchar2, vsal OUT number)isbegin

select ename, sal into vename, vsal from emp where empno=veno;end;/

main block

declarecursor c1 is select empno from emp where deptno=10;ve emp.ename%type;vs emp.sal%type;

beginfor erec in c1loop

retemp(erec.empno, ve, vs);dbms_output.put_line('Employee number is '||erec.empno);dbms_output.put_line('Employee name is '||ve);dbms_output.put_line('Employee salary is '||vs);

end loop;end;/

to show increase in salary by 10 percent for all employees and an additional 5 percent for those who have completed more than 5 years of service

create or replace procedure retemp(veno IN number, vsal IN OUT number, year IN number) is begin if year>=60 then vsal:=vsal*1.05; end if; end; /

main plsql block declare cursor c2 is select empno, sal, hiredate from emp; vsal1 emp.sal%type; vdate1 emp.hiredate%type; years number;

begin for rec in c2 loop dbms_output.put_line('Employee number '||rec.empno); dbms_output.put_line('Employee salary '||rec.sal); vsal1:=rec.sal*1.1; dbms_output.put_line('Employee salary '||vsal1); select months_between(sysdate,hiredate) INTO YEARS from emp where empno=rec.empno; retemp(rec.empno,vsal1, years); dbms_output.put_line('Employee salary '||vsal1); end loop; end;/

Viewing OUT Parameters :

Create or replace procedure query_emp(p_id In employees.employee_id%type,p_name OUT employees.first_name%type,p_salary OUT employees.salary%type,p_comm. OUT employees.commission_pct%type)isBEGIN

Select first_name, salary, commission_pct into p_name, p_salary, p_comm from employees where employee_id=p_id;

End emp_query;/

at the calling environment :variable g_name varchar2(25)variable g_sal numbervariable g_comm. Number

execute query_emp(100, :g_name, :g_sal, :g_comm.)

print g_nameprint g_salprint

The colon(:) is required to reference the host variable s in the execute command.

Methods of passing Actual Parameters :Positional : Here the actual parameters are passed in the order in which the Formal parameters are given with the procedure definition

Named Association : Here the actual parameters are passed in ant arbitary order by associating it with the corresponding formal parameter using the => symbol.

Combination : Here the first actual parameter is passed positionally and the remaining are passed using the => symbol as in named association method.

Default Option for parametersCreate or Replace procedure add_dept(p_name IN departments.department_name%type DEFAULT ‘unknown’,P_loc IN departments.location_id%type DEFAULT 100)ISBegin

Insert into departments(department_id, department_name, location_id) values(departments_seq.nextval, p_name, p_loc);

End add_dept;/Default values may be assigned only to In parameters , and not to OUT or IN OUT Parameters.

Executing/Calling the above procedure :Begin

add_dept; -- both defaultadd_dept(‘Welfare’,200); ----positionaladd_dept(p_loc=>300, p_name=>’Medical’);---Named associationadd_dept(p_loc=>400); --- one default

End;/

In Combinational method all the positional parameters must be before the named parameters, or else there will be an error.

Example : Execute add_dept(p_name=>’Legal’, 500) -- will give an error as “a positional parameter may not follow a named parameter”Declaring Local Sub-procedures :The local procedures are not stored as Schema objects. Because they are defined in the declaration section of another program/PL SQL Block/procedure, their scope is limited to the parent(enclosing) block in which they are defined. When you are declaring a sub-program in the declaration section of the main program, it must me the last item declared. Any variable declared after the local subprogram will cause a compilation error.

declareprocedure p1 isbegin

dbms_output.put_line('P1 is called');end;

beginp1;dbms_output.put_line('In the main block');

end;/

declareprocedure p1;procedure p2 isbegin

dbms_output.put_line('p2 called');p1;

end;

procedure p1 isbegin

dbms_output.put_line('p1 called');p2;

end;begin

p1;dbms_output.put_line('In the main block');p2;

end;/

it will show error : p1 must be declared(if procedure p1; is not types as line 1)The result is an infinite loop, and dbms.... is never executed.

Procedure to delete an employees record and to log the entry of the user who deleted the record

Create or Replace procedure del_emp(p_id IN employees.employee_id%type) IS

Procedure log_empISBegin

Insert into log_table(user_id, log)date) values(USER, SYSDATE);End log_emp;

BeginDelete from employees where employee_id=p_id;log_exec;

End del_emp;/

Invoking a stored procedure from an anonymous block :Declare

V_id number:=145;Begin

Raise_salary(v_id);Commit;……

End;/

Invoking a stored procedure from another Stored procedure :Create or replace procedure emp_procIS

Cursor emp_cursor is select employee_id from employees;Begin

For erec in emp_cursorLoop

Raise_salary(erec.employee_id);End loop;Commit;

End emp_proc;/

Handled Exceptions :When an exception is raised in a called procedure, the control goes immediately to it’s exception section. If the exception is handled, then the block terminates, and the control goes to the calling program/environment. Any DML statements issued before the exception was raised remain as part of the transaction.

Consider the following tablesLOCATIONSLocation_ID City

1 Mumbai

DEPARTMENTSDepartment_ID Department_name Manager_ID Location_ID

5 Welfare 200 1

Create procedure Ins_dept(p_locid number) ISv_did number(4);

BeginDbms_output.put_line(‘Procedure INS_dept started’);Insert into departments values(5, ‘Mumbai’, 200, p_locid);Select department_id into v_did from employees where employee_id=999;

End;/

Create procedure Ins_loc(p_lid number, p_city varchar2) ISv_city varchar2(30);v_dname varchar2(30);

BeginDbms_output.put_line(‘MAIN Procedure INS_LOC started’);Insert into locations(location_id, city) values(p_lid, p_city);Select city into v_city from locations where location_id=p_lid;Dbms_output.put_line(‘Inserted City ’|| v_city);Dbms_output.put_line(‘Invoking Procedure INS_dept’);Ins_dept(p_lid);

ExceptionWhen no_data_found thenDbms_output.put_line(‘No such department/location for any employee’);

End;/

The procedure INS_loc inserts a new location into the locations table. The procedure ins_dept is supoosed to insert a new department with department id=5, department name=Welfare, Manager ID=200 at the new location inserted through INS_loc.

Procedure ins_dept selects the department id for an employee whose employee id=999. Suppose there is no such employee, then it raises no_data_found, which is not handled in procedure ins_dept. So, the control is passed to the calling procedure ins_loc where the exception no_data_found is handled. Thus, the DML in ins_dept is not rolled back, and becomes a part of the transaction of procedure ins_loc procedure

Execute ins_loc(1, ‘Mumbai’)

If the exception is handled in the calling procedure, all DML statements in the calling and called procedure remain as part of the transaction.If the exception is not handled by the calling procedure, the calling procedure terminates and the exception propagates to the calling environment. ALL DML statements in the calling and called procedure are rolled back along with any changes to any host variables.

If in the above 2 procedures, if the exception section from the ins_loc is removed, then the DMLs in both the procedures are rolled back if no employee with employee_id=999 is found.

FunctionsFunction is a named PL SQL block that can accept parameters and be invoked. A function must return a value to the calling environment, whereas a procedure returns zero or more values. Like a procedure, a function has a header, declarative section, executable part and an optional exception section. In addition, a function must have a RETURN clause in the header and at least one RETURN statement in the executable section.Function is called as part of a SQL expression or as a part of a PL SQL expression.

Create or Replace Function function_name [parameter1 [mode] datatype, parameter2 [mode] datatype, …] -- headerRETURN datatypeIs/AS--this section after is/as and before the begin is the declarative sectionPL SQl block --- executable and an optional exception section

End function_name;/The type of parameter mode should be only of type IN.

Local functions :Not stored in databaseDefined and called within the same programFunctions do not have declare. If it is necessary to declare a variable it should be done in the declare part of the enclosing programdeclare

x number;function f1 return number isbegin

dbms_output.put_line('Inside the function');return 10;

end;

begindbms_output.put_line('Inside the Main');x:=f1;dbms_output.put_line('value of x is'||x);

end;/

Global or stored function :Stored in the databaseDefined and saved separately, can be called thru another pl/sql block

Create or Replace Function getsal(p_id IN employees.employee_id%type)RETURN numberIs

v_salary employee.salary%type :=0;Begin

Select salary into v_salary from employees where employee_id=p_id;RETURN v_salary;

End getsal;/

Executing functions :Declare a variable to hold the return value, which is a good programming practice, as follows :

variable gsalary numberexecute :gsalary :=getsal(130)print gsalary

try the following on emp in scott :ed f1;

create or replace function chkemp(veno number)return boolean isvsal emp.sal%type;

beginselect sal into vsal from emp where empno=veno;dbms_output.put_line('salary is '|| vsal);return true;

exceptionwhen no_data_found thendbms_output.put_line('salary is not found');

return false;

end;/at the sql prompt :>show_error;

to show the errors in the function coding;

to execute>@f1;

main blockdeclare y number :=&eno; flag boolean;begin flag:=chkemp(y); if flag=True then dbms_output.put_line('Employee Existing'); else dbms_output.put_line('Employee Not Existing'); end if;end;/

stored function to update the salaries of employees getting less than 3000 with increment of certain % percent

create or replace function upsal(veno number, inc number)return number is

vsal number;begin

update emp set sal=sal+(sal*inc/100) where empno=veno;commit;select sal into vsal from emp where empno=veno;return vsal;

end;/

select text from user_source where type ='FUNCTION' and name='UPSAL' order by line

main pl/sql blockdeclare

cursor c1 is select empno, sal from emp where sal<3000;incr number :=&incr;incsal number;

beginfor erec in c1loop

incsal:=upsal(erec.empno, incr);dbms_output.put_line('Increased salary is '||incsal||' for employee '||

erec.empno);end loop;

end;/

Although 3 parameter modes IN, OUT and IN OUT are available with functions, do not use OUT and IN OUT with functions, since the purpose of a function is to accept 0 or more parameters and return a single value. Having a function returning multiple values is a poor programming practice, and it involves a lot of complications and side effects. There can be a RETURN statement in the exception section also.

Advantages of Functions :To perform calculations that are complex, awkward or unavailable with SQLIncrease data independence by processing complex data at the Oracle server level(since the stored functions are stored at the server), rather than retrieving data into an application.Increase efficiency of queries by performing functions in the query rather than in the application

Create or Replace Function tax(p_value IN NUMBER)Return NUMBER ISBegin

Return(p_value * 0.08);End Tax;/

INVOKING a function from a SQL ExpressionSuppose you have a table employees with salary column then you may use the above function as follows to calculate their tax

Select empno, salary, tax(salary) from employees;

Locations from where you may call a User-defined function :Select list of a SELECT commandCondition of the WHERE and HAVING clauseConnect by , start with, order by and group by clausesValues clause of the insert commandSET clause of the update command

Select empno, tax(salary) from employees where tax(salary) >(select max(tax(salary)) from employees where departmentid=30) order by tax(salary) desc

Restrictions on calling function :The function must be a Stored FunctionIt must accept only IN parametersAccept the parameters whose data type must be standard SQL data type and NOT a PL SQL data type

The return data types must be a standard SQL data type and NOT a PL SQL data type

Restrictions from where a stored function can be called :Functions called from an Update/Delete statements on a Table XYZ cannot contain a DML statement on the table XYZFunctions called from an Update/Delete statements on a Table XYZ cannot query the table XYZWhen called from an INSERT/UPDATE/DELETE/SELECT statement, the function cannot contain commands that end the transaction, such a COMMIT(transaction control statement), ALTER SYSTEM(system control statement), SET ROLE(session control statement), or DDL commandsFunctions called from a SQL Expression(as shown above) cannot contain a DML Statement.If a Function internally calls sub subprogram(that is another function or procedure) then that sub program even muts not violate any of the above mentioned restrictions.

ExamplesCreate or Replace function abc(p_sal number) Return number isBegin

Insert into employees(employee_id, last_name, appoint_date, salary) valurs(1001,’Pereira’, sysdate-10, 10000);Return(p_sal +100);

End;/ The function fails with the folliwng :Update employees set salary=abc(2000) where emplyee_id=90;

The function contains a DML statement on table employees. We are calling the function thru the update command on the same table employees. The update statement returns an error saying that the table is mutating

AsloCreate or replace function xyz( a number) retutn number iss number;Begin

Select salary into s from employees where employee_id=95;Return (s+a);

End;/Update employees set salary=xyz(100) where employee_id=95;

Here also the mutating table error is returnedWhen the code in a function is querying or reading the data in a table which is being updated, such a table is called MUTATING table

To drop a function :Drop function function_name;

Procedure V/s. FunctionExecutes as a PL SQL BlockExec Proc_name

Invoked as part of an expressionm

Does not contain a RETURN clause in the Header

Must contain a RETURN clause in the Header

May or may not return values Must return a single valueUsed to perform a series of actions Used to compute some value, which is

returned to the calling environment

Managing Subprograms :

System privileges are required to Create, Alter, Drop or Execute subprograms. The keyword PROCEDURE is used to assign such rights on Stored Procedures, Functions and Packages. System privileges are granted by the user SYSTEM or SYS

Grant Create (any) PROCEDURE to user_name;Grant Alter any PROCEDURE to user_name;Grant Drop any PROCEDURE to user_name;Grant Execute any PROCEDURE to user_name;

The keyword ANY means that you ca create, alter, drop, or excute your own subprograms and those in another schema. The keyword ANY is optional for CREATE.

If a PL SQL subprogram refers to any objects that are not in the same schema, then you must be granted access to those objects explicitly, not through a role

If you are not the owner of a subprogram, then you must have EXECUTE privilege on that subprogram.

Providing INDIRECT ACCESS to DataSuppose EMPLOYEES table in Scheme(user) HEMANT. There is another user MANOJ and a third USER SALONI. Suppose we need to ensure that SALONI can access the table EMPLOYEES in the schema HEMANT, but only through a procedure manoj__proc which is created by the user MANOJ.

DIRECT ACCESSFrom Hemant schema grant object –level privilge on the table employees to user ManojManoj has created the procedure manoj_proc that queries the Employees table in the schema Hemant

INDIRECT ACCESSManoj grants execute privilge to Saloni on his procedure manoj_procThis Saloni can access

By default subprograms execute under security domain of the owner, which is called DEFINER’s-RIGHTS.

User_Objects To obtain names of all PL/SQL stored objects within a schema, query the user_objects

USER_OBJECTSObject_name -Name of ObjectObject_Id -Internal identifier for the objectObject_type -Whether TABLE, PROCEDURE, PACKAGE, PACKAGE

BODY, FUNCTION, TRIGGERCreate- -Date of object creationLast_DDL_Time -Date when object was last modifiedTimestamp -Date and time when object was last recompiledStatus -VALID or INVALID

You can also examine the ALL_OBJECTS and DBA_OBJECTS, both of which contain an additional OWNER column.

USER_SOURCEName -Name of the objectType -Type of object : Procedure, Function, Package, Package

BodyLINE -Line number of the source codeTEXT -Text of the source code line

This table contains one record for each line of code. ALL_SOURCE and DBA_SOURCE contain the additional column Owner

Select text from user_source where name=’PROCEDURE_name’ order by line;

USER_ERRORSName -Name of the objectType -Type of object : Procedure, Function, Package, Package

BodyLINE -Line number of the source code where error occurredPosition -Position in the line where the error occurredText -Text of the error message

SHOW ERRORS [Function/Procedure/Package/Package body/Trigger/ViewThis will show use the errors that occurred while trying to compile the articular program unit.

SHOW ERRORS without any [parameter will show the compilation errors

The following query will also show you an output which is similar to that of show errors

Select line || ‘/’||position, text from user_errors where name=’proc_name’ order by line;

TRIGGERS

Definition : Trigger is basically a PL/SQL block or PL/SQL procedure associated with a Table/View/Schema/Database and which executes implicitly whenever a particular event takes place

Types of Triggers :Application Triggers which execute implicitly when a particular DML event occurs within an application such as Oracle Forms DeveloperDatabase Triggers which could be 1) Data Event or 2) System EventData Event trigger fires implicitly whenever a DML operation occurs on table or a DDL event such as CREATE/ALTER are issued. It could also be an INSTEAD OF Trigger on a View. They are fired irrespective of the user connected and irrespective of the application used.System Event Triggers are fired when a system event occurs such as user logs on, user shutsdown, etc

Guidelines for Designing TriggersDosDesign Triggers to guarantee that when a specific operation is performed, related actions are performed.Use Triggers only for Centralised, Global Operations that occur for the triggering event, regardless of the user or application that issues the triggering statement.

DontsDo not use triggers when the functionality is already built into Oracle Server.Do not use triggers to implement integrity constraints. Use the built-in constraints

Excessive use of triggers can result into complex interdependencies. If the logic is very lengthy, write a stored procedure for the same and invoke the stored procedure in the trigger bodyIf multiple triggers of the same type are defined for a table, their order of execution is arbitary. To ensure that multiple triggers of the same type are executed in a particular order, write stored procedures for the different actions and have a single consolidated trigger in which you call those stored procedures in the required order.

The Trigger Statement contains :Trigger Timing :BEFORE, AFTER for tables INSTEAD OF for viewsTriggering Event :INSERT, UPDATE or DELETETable name :On table or viewTrigger Type :Statement level or Row levelTrigger Body :PL SQL block

BEFORE :Execute the trigger body before the triggering DML event on the tableTo determine whether the triggering statement should be allowed to completeTo calculate some values before completing the triggering Insert or Update statementTo validate or set some complex business logicTo initialize some global variables or flags

BEFORE :Execute the trigger body before the triggering DML event on the tableTo complete the triggering statement before executing the trigger body

INSTEAD OFThis type of trigger is used to modify data through a view that was otherwise not modifiable because of the inherently non-modifiable nature of the view. Such trigger works in the background by performing the action directly on the underlying base tables involved in the view. You may write INSERT, UPDATE or DELETE statements on a view.

The Triggering Event can be INSERT, UPDATE or DELETE statement on a table. In case of UPDATE statement, you can specify the column, which if changed, causes the trigger to fire. The triggering event can contain one or two or all the three DML operations.

The trigger type can be Statement Trigger or Row Trigger. A Statement Trigger is fired once for the triggering event. It may affect zero or more rows, but is fired only once. They are used when the trigger-action to be performed is independent of the data affected by the triggering event. A Row Trigger is fired once for each affected row. If no rows are effected, then the trigger is not fired. They are used when the trigger-action depends on the data or rows affected by the triggering event.

The Trigger body defines the action that needs to be performed when the triggering action is issued. It is a PL SQL block containing SQL and PL SQL statements defining PL SQL types, item, variables, constants, cursors, exceptions, etc. You can call other PL SQL procedures or Java Procedures. Trigger size cannot be more than 32K.

The Firing Sequence is as follows :BEFORE Statement Trigger -------- once

BEFORE Row Trigger -------- once for each affected rowAFTER Row Trigger -------- once for each affected row

AFTER Statement Trigger --------- once

Syntax for Statement Level TriggerCreate or Replace Trigger Trigger_nameTimingEvent1 or Event2 or Event3On table_view_nameTrigger_body

The trigger name must be unique with respect to other triggers in the same schema. Trigger name need not be unique with respect to other schema objects like table, view, or procedure.

A trigger to prevent insertion of records into the dept table on Saturdays or Sundays or on non-working hours on other dayscreate or replace trigger check_deptnobefore insert on deptbeginif (to_char(sysdate,'DY') in ('SAT','SUN')) or (to_char(sysdate,'HH24:MI') not between '09:00' and '17:00' ) thenraise_application_error(-20500,'Insert Not allowed');end if;end;/

Raise_application_error is a server-side built-in procedure that returns an error to the user and causes the PL SQL block to fail. When the database trigger fails, the triggering statement is automatically rolledback by Oracle Server.

Table : DEPTDEPTNO varchar2DEPTNAME vrachar2

A trigger to prevent insert or update or delete operations on table deptcreate or replace trigger check_deptnobefore insert or update or delete on deptbeginif deleting then raise_application_error(-20500,'Delete Not allowed');elsif Inserting then raise_application_error(-20501,'Insert Not allowed');elsif Updating('deptno') then raise_application_error(-20502,'Updating of deptno Not allowed');else raise_application_error(-20503,'Updating of Table Not allowed');end if;end;/

Special Conditional Predicates INSERTING, UPDATING, DELETING, UPDATING(‘column’) can be used in a single trigger for combining several triggering events into one single trigger

Syntax for Row Level TriggerCreate or Replace Trigger Trigger_nameTimingEvent1 or Event2 or Event3On table_view_nameFor Each Row[When (condition)]Trigger_body

create or replace trigger check_dept before insert on dept for each row when(new.deptno>50) begin raise_application_error(-20555,'Hello'); end;/

with the WHEN clause you can specify a condition in the brackets. The condition is evaluated for each row to determine whether or not the trigger body is executed. If the condition is satisfied then only we enter into the trigger body. The WHEN clause is optional. Thus, the WHEN clause is to restrict the Trigger action to those rows that satisfy a certain condition

With ROW Level trigger only, we can use the OLD and NEW Qualifiers to reference the value of a column before and after the data change by prefixing the column name with the OLD and NEW Qualifiers. You need to use the colon(:) as a prefix in every SQL and PL SQL statement. No colon(:) required if the Qualifiers are used in the WHEN clause.

Operation Old Value New ValueFor INSERT NULL Newly Inserted valueFor UPDATE Value before update Value after updateFor DELETE Value before update NULL

Table : AUDT_DEPT create table audt_dept ( User_name varchar2(15), Time timestamp, Old_deptno varchar2(15), New_dept varchar2(15), Old_deptname varchar2(15), New_deptname varchar2(15) )

create or replace trigger audt_deptafter insert or update or delete on dept for each rowbegininsert into audt_dept values(user, sysdate, :old.deptno, :new.deptno, :old.deptname, :new.deptname);end;

INSTEAD OF TriggerIt is used to modify the data where a DML statement has been issued against an inherently non-updatable view(that is views which are non-updatable because the underlying query may contain set operator, group function, group by clause, connect by, start, distinct operator or a join of 2 or more tables. Unlike other triggers, this trigger is fired by Oracle sever instead of executing the triggering statement. Such triggers are Row Level Triggers. The WITH CHECK option for views is not enforced when insertion/updation are performed by using the INSTEAD OF Trigger. The INSTEAD OF trigger must enforce the check.

Create or Replace Trigger Trigger_nameINSTEAD OFEvent 1 or event2 or event3On View_nameFor each rowTrigger_body

Even if FOR EACH ROW is omitted, INSTEAD OF Triggers is still defined as a Row Trigger. BEFORE and AFTER options are not valid.

Consider the following tables :

Table : EmployeeEmpid EmpnameSalaryDeptno

Table : Dept_statDeptnoDeptnameTotalsal

Following trigger updates the total salary in Dept_stat table whenever rows are inserted/update/deleted from the Employee table.

Create view empl_view as Select e.empid, e.empname, e.salary, e.deptno, d.deptname from employee e, dept_stat d where e.deptno=d.deptno;

Create or Replace Trigger New_emp_checkINSTEAD OFINSERT OR DELETE OR UPDATEFOR EACH ROWBeginIf INSERTING THEN

Insert into employee values(:new.empid, :new.empname, :new.salary, :new.deptno);

Update dept_stat set totalsal=totalsal+:new.salary where deptno=:new.deptno;

Elsif DELETING ThenDelete from employee where empid=:old.empid;Update dept_stat set totalsal=totalsal-:old.salary where

deptno=:old.deptno;

Elsif UPDATING(‘SALARY’) thenUpdate employee set salary=:new.salary where empid=:old.empid;Update dept_stat set totalsal=totalsal +(:new.salary - :old.salary where

deptno=:old.deptno;

Elsif Updating(‘Deptno’) thenUpdate employee set deptno=:new.deptno where empid=:old.empid;Update dept_stat set totalsal=totalsal - :old.salary where

deptno=:old.deptno;Update dept_stat set totalsal=totalsal + :new.salary where

deptno=:new.deptno;End if;

End;/To Enable/Disable a TriggerAlter Trigger Trigger_name DISABLE/ENABLE

To Enable/Disable all Triggers on a tableAlter Table Table_name DISABLE/ENABLE ALL TRIGGERS

To Recomplie a TriggerAlter Trigger Trigger_name COMPILE

This command is used to explicitly recompile a trigger, regardless of whether it is valid or invalid.

To Drop a TriggerDrop Trigger Trigger_name;All the triggers on a table are dropped when the table is dropped.

SYSTEM LEVEL TRIGGERSTriggers on system events can be defined at the Database Level or Schema Level. Triggers A Database Level Trigger fires for all users. A Schema or Table Level Trigger fires only when that schema or table is involved.

Events that can cause a System Level Trigger to fire are :A DDL statement on a object in the database or schema (the trigger can be Database Level or Schema Level)Specific User or any other User logs on or off (the trigger can be Database Level or Schema Level)Database Shutdown or Startup (the trigger can be Database Level only

Triggers on DDL Statements :Create OR Replace Trigger Trigger_nameTimingDDL_Event1 Or DDL_Event2 or….On Database/SchemaTrigger_body

Timing : BEFORE or AFTERDDL_Event : CREATE OR ALTER OR DROP. Causes the Oracle Server to fire the trigger whenever a Create/Alter/Drop Statement modifies a Database Object in the Data Dictionary.

On Database/Schema : You can create triggers on DDL Statements at the DATABASE or SCHEMA Level.

Trigger_body : It is a complete PL SQL Block.

The DDL Triggers fire when the Object is a Cluster, Function, Index, Package, Procedure, Role, Sequence, Synonym, Table, Tablespace, Trigger, Type , View or a User

Triggers on System Events :Create OR Replace Trigger Trigger_nameTimingDatabase_Event1 Or Database_Event2 or….On Database/SchemaTrigger_body

Database_Events : AFTER SERVERERROR : Whenever a Server Error Message is logged.AFTER LOGON : Whenever a User Logs on to the DatabaseBEFORE LOGOFF : Whenever a User Logs off the DatabaseAFTER STARTUP : Whenever a Database is openedBEFORE SHUTDOWN: Whenever a Database is shut down

These triggers can be created at the DATABASE or SCHEMA LEVEL, but SHUTDOWN and STARTUP apply only to the Database Level.

CREATE OR REPLACE TRIGGER logon_trigAFTER LOGON ON SCHEMABEGINInsert into log_trig_table(userid, date, action) values(USER, SYSDATE, ‘Logged On’);END;/

CREATE OR REPLACE TRIGGER logoff_trigAFTER LOGOFF ON SCHEMABEGINInsert into log_trig_table(userid, date, action) values(USER, SYSDATE, ‘Logged Off’);END;/CALL Statement :It is recommended to create stored procedures and call them in the Trigger body rather than coding the PL SQL body in the trigger itself. The procedures so called can be implemented in PL SQL, C, Java. The CALL statement enables you to call a stored procedure. There is no semicolon at the end of the CALL Statement.

Create or Replace Trigger salary_checkBefore Update of Salary , job_id ON EmployeesFor Each RowWhen(new.job_id<>’YYZ’)CALL check_sal(:new.job_id, :new.salary)

MUTATING TABLEWhen the code in a trigger is querying or reading the data in a table which is being updated, such a table is called MUTATING table. Reading and Writing data using triggers follows certain rules, which are applicable to only row level triggers or a statement level trigger that is fired because of the referential integrity constraint ON DELETE CASCADE. A mutating table is the one that is currently being modified by an UPDATE, INSERT OR DELETE statement and a ROW

level trigger on any such event is trying to read/write data into the same table. (A table is not mutating for STATEMENT level triggers.) A triggered table itself is mutating, and any other table referencing it with the FOREIGN KEY constraint is also mutating.

Consider a trigger which checks that the salary for a new employee or an existing employee is always between the minimum and maximum salaries for that job code.

Create or Replace Trigger Check_SalBefore INSERT Or UPDATE OF salary, job_idOn EmployeesWhen (new.job_id <>’PROG’)Declare

V_minsal employees.salary%type;V_maxsal employees.salary%type;

BEGINSelect Min(salary), max(salary) into v_minsal, v_maxsal from employees

where job_id=:new.job_idIF :New.salary<v_minsal or :New.salary>v_maxsal then

Raise_application_error(-20505,’Salary Out Of Range’);END IF:

END;/

Viewing Trigger Information

User_objects :Contains name and status of the trigger and the date and time when the trigger was created.

select object_name, subobject_name, status, created from user_objects where object_type='TRIGGER'

User_Triggers :Contains name, type, triggering event, trigger body, table on which table was created.

select trigger_name, trigger_type, triggering_event, table_name, referencing_names, status, trigger_body from user_triggers

User_errors :Contains details of the compilation errors that occurred while the trigger was compiling

PACKAGES

It is used to group together related PL SQL Types, items, subprograms(procedures and functions) into one single container. A Package, generally, has two parts : Package Specification and Package Body, which are stored separately in the database. This allows to change the definition of a program construct in the package body without causing the Oracle server to invalidate the other schema objects that call or reference the program construct.

Package Specification is the INTERFACE to your application. In the Specification we declare the variables, constants, types, exceptions, cursors and sub programs available for use

Package Body fully defines the cursors and subprograms, and thus implements the specification.

The package cannot be itself parameterized, called or nested. When you call a packaged construct for the first time, the entire package gets loaded into memory. Further calls to other constructs of the same package do not need any disk I/O.

Types of Package Constructs :Public or Global : Declared within the package specification and may be defined in the package body. Can be referenced and changed(in case of variables) outside the package and are visible to the external users.

Private or Local : Declared and Defined within the package body. Can only be referenced by other constructs which are part of the same package.

Creating the Package Specification

Create or Replace Package package_nameIs/as

Public type and item declarationsSubprogram specifications

End package_name;

The Replace option drops and recreates the package specification.Public type and item declarations : Declares variables, constants, cursors, exceptions or types. Variables declared in the package specification are initialized to NULL by default.Subprogram specification : Declares the PL SQL subprograms. The public procedures or functions can be invoked repeatedly by other constructs in the same package or from outside the package.

Creating the Package Body

Create or Replace Package Body package_nameIs/As

Private type and item declarationsSubprogram Bodies(Private and Public)

End package_name;

The Replace option drops and recreates the package body.Private type and item declarations : Declares variables, constants, cursors, exceptions or types. All private constructs must be declared before they are used in the public constructs.Subprogram Bodies : Defines the PL SQL subprograms, public and private.

Subprograms and Cursors are declared(without their respective bodies) in the package specification, and they have their underlying implementation(or bodies) in the package body. Thus, if a package specification does not declare subprograms and cursors and declares only types, constants, variables, exceptions, then the package body is not necessary. But the package body can still be used to initialize items declared in the package specification.

Write a package that contains a function that validates the commission so that the commission may not be greater than the highest commission amongst all the employees. The function should be called through a procedure in the same package, and this procedure should display the appropriate messages and reset and validate the prevailing commission.

Create or Replace Package comm_packageIs

g_comm Number :=0.10; -- declared and initializedprocedure reset_comm(p_comm IN Number);

END comm_package;/

Create or Replace Package Body comm_packageIs

Function validate_comm(p_comm IN Number) Return BooleanIs

v_max_comm Number;Begin

Select max(commission_pct) into v_max_comm from employees;If p_comm>v_max_comm then return(FALSE);Else return(TRUE);End if;

End validate_comm;

Procedure reset_comm(p_comm IN NUMBER)IsBegin

If validate_comm(p_comm) theng_comm:=p_comm;

ElseRaise_application_error(-20210,’Invalid Commission’);

End if;End reset_comm;

End comm_package;/

When you invoke a package or function from within the same package, you need not qualify it’s name with the package name. The above function is a private function(declared and defined within the package body), hence it can be referenced by only other procedures/functions of the same package.To invoke a package procedure or function from outside the package, you need to qualify it’s name with the name of the packageComm_package.reset_comm(0.25)

To invoke a procedure from a package through iSQL *PlusExecute comm_package.reset_comm(0.15)

To invoke a procedure from a package through a different schemaExecute scott.comm_package.reset_comm(0.15)

To invoke a procedure from a package through a remote databaseExecute comm_package.reset_comm@ny(0.15)

Declaring Bodiless Package

Create Or Replace Package global_constsISmile_2_kilo constant number :=1.6093;kilo_2_mile constant number :=0.6214;yard_2_meter constant number :=0.9144;meter_2_yard constant number :=1.936;end global_consts;/

Execute dbms_output.put_line(’20 miles = ‘ ||20*global_consts.mile_2_kilo|| ‘ kms.’);

This package specification declares public(global) variables that exist for the duration of the user session.

Referencing a Public variable from a stand-alone procedureCreate or Replace procedure meter_to_yard(p_meter IN number, p_yard Out number)IsBegin

p_yard:=p_meter * global_consts.meter_2_yard;End meter_to_yard;/

at the sql prompt>variable yard number>execute meter_to_yard(1,:yard)> print yard

To Drop the package Specification :Drop Package package_name;

To Drop the package body :Drop Package body package_name;

Advantages :Modularity since it encapsulates related constructsEasier Application Design, since specification and body are coded and compiled separately.Hiding Information :

Only package declarations made in the specification are visible and accessible to applicationsPrivate package constructs are hidden and inaccessibleAll the coding is hidden in the package body.

Added Functionality since it allows persistency of variables and cursorsOverloading allowed, since multiple subprograms of the same name are allowedBetter Performance :

Entire package loaded into memory when a package is first referencedOnly one copy of the package in memory for all the usersDependency hierarchy is simplified

Overloading support in Packages

Packaged subprograms(procedures or functions) can be overloaded. Overloading not possible for stand-alone subprograms.

Overloading is a feature which enables you to have different packaged subprograms with the same name, but differing in the number, order or data-type of the formal parameters of the subprograms.

RESTRICTIONS

1) Standalone subprograms cannot be overloaded2) Subprograms which differ only in the datatypes of their parameters, but

the different datatypes belong to the same family. Example : NUMBER and DECIMAL datatypes belong to the same family

3) Subprograms which differ only in the subtypes of their parameters, but the different subtypes belong to the same family. Example : VARCHAR and STRING are PL/SQL subtypes of VARCHAR2.

Resolving a Subprogram callWhen a call is made to a subprogram, the compiler starts searching for a subprogram with that name in the current scope, and if it does not find one in the current scope, it starts searching in the enclosing scopes. As soon as the compiler finds one or more subprograms with the required name, the compiler stops the search. It then starts matching the number, order and datatypes of the actual parameters passed with the called subprogram with formal parameters of the one or more subprograms searched in a scope.

Create or replace package overloadIs

Procedure add_employee(p_empno IN employees.empno%type, p_empname IN employees.empname%type default ‘unknown’, p_age IN employees.empage%type default 21);

Procedure add_employee(p_empname IN employees.empname%type default ‘unknown’, p_age IN employees.empage%type default 21);

END overload;

Create or replace package body overloadIs

Procedure add_employee(p_empno IN employees.empno%type, p_empname IN employees.empname%type default ‘unknown’, p_age IN employees.empage%type default 21)IsBegin

Insert into employees(empno, empname, empage) values(p_empno, p_empname, p_empage);

End add_employee;

Procedure add_employee(p_empname IN employees.empname%type default ‘unknown’, p_age IN employees.empage%type default 21)IsBegin

Insert into employees(empno, empname, empage) values(empnoseq.nextval, p_empname, p_empage);

End add_employeeEND overload;

At the SQL PROMPT>Execute overload.add_employee(101,’Rahul’, 32)>Execute overload.add_employee(’Rahul’, 23)

If you redeclare a built-in subprogram in another PL SQL Program unit, your local declaration overrides the built-in or standard subprogram. To access the built-in subprogram, you need to qualify it with the package name. Thus, if you have redeclared the to_char function, then to use the built-in to_char function , you call it as :STANDARD.TO_CHAR, because the function to_char belongs to the built-in package STANDARD.

If you redeclare a built-in subprogram as a stand-alone subprogram then to access your sub-program you need to qualify it with your schema, example scott.to_char

Using Forward Declarations

PL/SQL does not allow forward referencing, which means that a subprogram cannot be referenced or called before it has been declared. To solve this, PL/SQL allows a special subprogram declaration called Forward declaration, whereby you give the subprogram specification terminated by a semicolon. The subprogram can be then defined after another subprogram that calls it. Forward declarations are used when you need to :

Define subprograms in a logical or alphabetical orderDefine mutually recursive subprograms(which call each other directly/indirectly)Group subprograms in a package

Use Forward Declarations in the cases of private constructs or private subprograms of the package, since public package constructs are already declared in the package specification.

The formal parameter list must appear in both the forward declaration and the subprogram body. The subprogram body can appear anywhere after the forward declaration , but both must appear in the same program unit.

Thus packages support forward declarations, since the subprogram specifications go in the package specification, and the subprogram bodies go in the package body, where they are invisible to the applications.

Create or Replace Package Body forwardpackIsProcedure calc_rating(..); --forward declaration

Procedure award_bonus(….)Is begin

Calc_rating(…)End;

Procedure calc_rating(…)IsBegin

End;End forwardpack;/

One-time-Only Procedure

When the derivation of the values is too complex it is difficult to initialize public or private variables to that value in the variable declaration. In such cases, we use one-time-only procedures, since such procedures are executed only once when the package is invoked within a user session. For such type of variables do not initialize them in the declaration, because the value is reset by the one-time-only procedure.

The keyword END is not used at the end of the One-Time-Only procedures.

Create or Replace Package otopIs

Hra number;----declare all other public procedures/functions/variables

END otop;/

Create or Replace Package Body otopIs

----declare all private procedures/functions/variables----define private/public procedures/functions

BeginSelect rate into hra from rate_mast where rate_name=’HRA’;

----- No End for this one-time-only procedureEND otop;/

Restrictions on Package Functions1) A function called from a query or DML statement cannot end the current

transaction, create or rollback to a savepoint or alter the system session2) A function called from a query statement or a DML statement cannot

execute a DML statement or otherwise modify the database3) A function called from a DML statement cannot read or modify the table on

which the DML was fired.

Create or Replace Package tax_calc_packIs

Function tax_calc(p_value in number) return number;End tax_calc_pack;/

Create or Replace Package Body tax_calc_packIs

Function tax_calc(p_value in number) return numberIs

v_rate number :=0.08;Begin

return(p_value*v_rate);End tax_calc;

End tax_calc_pack;/

To use the packaged function :Select tax_calc_pack.tax_calc(salary), salary from emp;

PERSISTENT State of a PackageCreate or Replace Package comm_packageIs

g_comm number :=10;procedure reset_comm(p_comm in number);

end comm_package;/

Create or Replace Package Body comm_packageIs

Function validate_comm(p_comm in number) return BooleanIs

v_max_comm number;Begin

Select max(commission_pct) into v_max_comm from employees;If v_max_comm >=p_comm then return(TRUE);Else return(FALSE);End if;

End validate_comm;

Procedure reset_comm(p_comm IN number)IsBegin

If validate_comm(p_comm) then g_comm:=p_comm;Else raise_application_error(-20210,’Invalid Commission’);End if;

End reset_comm;End comm_package;/

Time User X User Y0900 Execute

comm_package.reset_comm(0.25)Assume maximum is 0.4.Max_comm=0.4>0.25Hence g_comm=0.25

0930 Insert into employees(empno, commission_pct) values(‘Ram’,0.9)

0935 Execute comm_package.reset_comm(0.5)Now maximum is 0.9.Max_comm=0.9>0.5Hence g_comm=0.5

1000 Execute comm_package.reset_comm(0.6)For this user, maximum is still 0.4, since user Y did not complete his transaction.Max_comm=0.4>0.5 is InvalidHence, Invalid Commission

1100 Rollback ;Exit

1145 Logged In againSince the earlier transaction was rolled back, max_comm is 0.4Execute comm_package.reset_comm(0.3)Max_comm=0.4>0.3Hence g_comm=0.3

Persistent State of a Packaged Cursor

Create or Replace Package pack_curIs

Cursor c1 is select empno from emp order by empno desc;Procedure p1;Procedure p2;

End pack_cur;/

Create or Replace Package Body pack_curIs

v_empno number;Procedure p1 isBegin

Open c1;Loop

Fetch c1 into v_empno;Dbms_output.put_line(‘Employee Number :’ || (v_empno));Exit when c1%rowcount>=3;

End loop;End p1;

Procedure p2 isBegin

LoopFetch c1 into v_empno;Dbms_output.put_line(‘Employee Number :’ || (v_empno));Exit when c1%rowcount>=6;

End loop;Close c1;

End p2;End pack_cur;/

The cursor is opened in P1, three rows are fetched and displayed, and the cursor is not closed. When we continue to fetch in P2, the next three rows are fetched and displayed. Thus packages maintain a persistent state of the package cursors.

The persistent state of a cursor/variable is maintained in a session of a user. The state does not persist across several sessions of the same user or acroos different users.

LOB

LOB is a data type to store large, unstructured data such as text, graphic images, video clippings, etc. There are 4 large object data types

BLOB for Binary large objects such as video clipCLOB for character large objectsNCLOB for multibyte chracter large objectsBFILE for a binary file stored at the OS level outside the database. The BFILE column stores a file locator that points to the external file

LOBs are characterized in two ways :Internal LOBs(CLOB, NCLOB, BLOB) stored in the databaseExternal files(BFILE) stored outside the database. BFILEs can be accessed only in the read-only mode from an Oracle server

Components of a LOB :The 2 distinct components of an LOB are :

LOB value : It is the data that constitutes the real object being storedLOB locator : It is a pointer to the location of the LOB value stored inside

the database

The LOB value may be stored anywhere in the database, but the LOB locator is stored in the row.

Internal LOBs :They are stored inside the Oracle server in the LOB segment. A BLOB< CLOB or NCLOB can be used in one of the following :

Column of a tableAttribute of a user-defined typePL SQL variable, parameter or return value(result)

BLOB is interpreted by the Oracle server as a bit-stream, similar to LONG RAW.CLOB is interpreted by the Oracle server as a single-byte character streamNCLOB is interpreted by the Oracle server as a multiple-byte character stream, based on the byte-length of the national character set

To interact with LOB, the following interfaces are used :PL/SQL package DBMS_LOBOracle Call Interface(OCI)Oracle Objects for Object Linking & Embediing(OLE)Pro*C/C++/Pro*COBOL precompilersJDBC

Suppose you have the following table :Create table employee(emp_id number(5), fname varchar2(25), lname varchar2(25), resume CLOB, photo BLOB);

Before you start writing data to CLOB/BLOB columns using DBMS_LOB package or OCI(oracle call interface) , such columns must be made non-null, that is, it must contain a locator that points to an empty or populated LOB value. You may initialize CLOB and BLOB columns by using the functions EMPTY_CLOB() and EMPTY_CLOB() functions respectively. This can be done through the insert or update statements as follows :

Insert into employee values(10, ‘Hemant’, ‘Pangam’, empty_clob(), empty_blob());

Now the columns have been initialized, but not yet populated by data.You could have done the initialization work at the time of table definition as follows :

Create table employee(emp_id number(5), fname varchar2(25),lname varchar2(25),resume CLOB default empty_clob(), photo BLOB default empty_blob());

Now we can put values as follows :Insert into employee values(10, ‘Hemant’,’ Pangam’, ‘MCA, MCP, MCSD,MCDBA, OCP’, NULL);Insert into employee values(20, ‘Manoj’,’ Pangam’, ‘M.Tech, MCP, MCSD,MCDBA, OCP Ph.D’, NULL);

If the record is already there, then you could have used UPDATE command;

Select resume from employee;

Updating CLOB using DBMS_LOB in PL/SQL :Declare lobloc clob; --serves as the LOB locator text varchar2(30000):='Corporate Trainer'; amount number; -- amount of data to be written offset number; -- where to start writingBegin Select resume into lobloc from employee where emp_id=10 FOR Update;

---For update locks the row and returns the LOB locator for the ---resume lob column

offset :=DBMS_LOB.getlength(lobloc)+2; amount :=length(text); dbms_lob.write(lobloc, amount, offset, text); -- write at lob location, amount to be written, from where and text to write

text:='Corporate Consultant cum System Analyst'; Select resume into lobloc from employee where emp_id=20 FOR Update; Amount:=length(text) ; dbms_lob.writeappend(lobloc, amount, text); -- append at lob location, amount to be written and text to write

commit;End;/

Selecting CLOB values using DBMS_LOBselect dbms_lob.substr(resume, 5, 5), dbms_lob.instr(resume, 'Cor') from employee

Selecting CLOB values in PL/SQLDeclare

Text varchar2(4000);Begin

Select resume into text from employee where emp_id=10;Dbms_output.put_line(‘Resume is : ‘ || text);

End;/

Removing LOBsTo delete row containing LOBs : Delete from employee where emp_id=30;To disassociate a LOB value from a row: Update employee set resume =empty_clob() where emp_id=40

To Add LOB columnsAlter table table_name add(col1 CLOB, Col2 BLOB);

Managing BFILESCreate a directory ‘saloni’ as a oracle user and give the path of the directory which contains your files as follows :Create or replace directory SALONI as ‘d:\saloni\saloni’

Give read privileges to users on that directory as follows :grant read on directory SALONI to public;

create the following table :create table x(col1 varchar2(10), col2 bfile);

Use the BFILENAME function to inset values as follows, ensuring tha the files c1.bmp and c2.bmp are existing in the folder :insert into x values(‘c1’, bfilename('saloni','c1.bmp'));insert into x values(‘c2’, bfilename('saloni','c2.bmp'))

Once physical files are associated with the records using SQL, you may now use subsequent read operations on BFILE using the PL/SQL dbms_lob package and OCI.

Then the following procedure is used to load a BFILE pointer to an image of each employee into the table ‘X’ using DBMS_LOB packageCreate or Replace procedure load_emp(p_file_loc IN varchar2)IS v_file BFILE; v_filename varchar2(20); cursor emp_cursor is select col1 from x for update;Begin For erec IN emp_cursor Loop v_filename:=erec.col1||'.bmp'; v_file:=BFILENAME(p_file_loc, v_filename); dbms_lob.fileopen(v_file); update x set col2=v_file where current of emp_cursor;

dbms_output.put_line('Loaded File :' || v_filename ||' size : '||dbms_lob.getlength(v_file));dbms_lob.fileclose(v_file);

End loop;End load_emp;/

plsql

Documents

type type

sql data types

boolean types of data

data types of columns

plsql statements

plsql blocks

lob data types

variable type