add

About Me

My photo
Oracle Apps - Techno Functional consultant

Monday, October 31

How to Setup Schedule Ship date based on Schedule Arrival date and Transit Time

This is short post , supported with you tube demo about
1.How to Setup Transit Time
2.How system calculate Schedule Ship date based onTransit Time and Schedule Arrival Date if Order Date Type = 'Arrival'.
3.Impact of Change of Shipping Method on calculation of Schedule Ship Date.
4.Impact of Latest Acceptable date.
4.How we set System Parameter for Latest Acceptable date.

Sunday, October 30

Oracle Views

 Database Objects in Oracle :
 TABLE : Used to store information & allows to  manipulate , retrieve & share information.
 ( user_tables, user_tab_columns ,TAB )

 VIEWS & SYNONYMS : Used  to manipulate , retrieve &  share information . They will not hold data .
 ( user_views  , user_synonyms , CAT )

 SEQUENCES :
Used to generate the numbers   automatically ( user_sequences )

  INDEX & CLUSTER : Used to improve performance of   oracle while retrieving or manipulating data.
 ( user_indexes , user_clusters )

 ROLES : Used to hold the collection of permissions to be  shared with other users (user_roles)---------------------------------------------------------------
 Views : It is an stored select statement.
 It is an virtual or Logical component .
 It allows Desc, DML, Select on it.
 It will not hold data.
 It is stored permanantly in "User_views" system  table.
 It can be shared with other users.
 DML on view are reflected in Table and DML on   Table  are reflected in view .
 * It is used to share "Selected Rows and Columns"   with other users. It is used for Reporting Purpose .
 It will Improve performance while manipulating or   retrieving data thru Views.

 Syntax:  create view <view name> as <select                                                            stmt>;

 Ex: Simple View

 create view v1 as select * from emp;
 desc v1
 select * from v1;
 insert into v1 values(.................);
 update v1 set sal = sal + 3000
 where empno =   7788;
 delete from v1 where empno = 7902;
 select * from emp;

 Sharing View :  Scott
 grant all on v1 to user1;

 user1:
 select * from scott.v1;
 insert into scott.v1 values(...............);

 Removing View : scott
 drop view <view name>;
 drop view v1;

 describe user_views
 select * from user_views; -- checking for existing                                            view
---------------------------------------------------------------
 Sharing selected rows and columns thru views  :
 Ex: Complex View
 create view v10 as select * from emp
 where deptno = 10 with check option;

 desc v10
 select * from v10;
 grant all on v10 to user10;

 user10:
 ---------
 desc scott.v10
 select * from scott.v10;
 update scott.v10 set sal = sal + 2000
 where sal < 10000;
 delete from scott.v10 where job = 'CLERK';
 insert into scott.v10 values(....................,10);

 With check option : Clause Used to check for condition while inserting rows   into view.

 Ex: create view stu_oracle as select   roll,sname,fee,phone,mail_id from student
 where course = 'ORACLE' with check option;

 select * from stu_oracle;
 desc stu_oracle
---------------------------------------------------------------
 View based on view: create view v1 as select * from emp;
 create view v2 as select empno,ename,job,deptno
 from v1;
 desc v2
 select * from v2;

 Read only views : * View based on Arithematic Expressions
 create view pay_info as select empno ecode, sal   basic, round(sal * .25) da, round(sal * .35) hra ,
 round(sal * .15) pf,
 round(sal + sal * .25 + sal * .35 - sal * .15 ) gross
 from emp where sal >= 10000;

 desc pay_info
 select * from pay_info;
 grant select on pay_info to user1;

 * View based on Aggregate Functions
 create view dept_analysis as select deptno,
 count(*) ecount, sum(sal) totpay, avg(sal) avgpay,  min(sal) lopay, max(sal) hipay
 from emp group by deptno;

 desc dept_analysis
 select * from dept_analysis; 

 Adv: used for reporting purpose Improves performance while retrieving data thru  view.

 * Views will support constraints automatically create view v11 as select  ename,hiredate,job,deptno from emp;
 desc v11
 select * from v11;
 insert into v11 values('ram',sysdate,'CLERK',85);

 Ex:
 create view v1 as select * from emp;
 create table temp as select * from emp;
 select * from v1;
 select * from temp;
 drop table emp;
  select * from v1;
  select * from temp;
 create table emp as select * from temp;
 select * from v1;

 Note :
 New EMP table must have same structure as Old   EMP table.   Alter stmt will not support on views .
 Alter on Table will not reflect on views (only DML   are reflected )

 Force - option It allows to create a view with out Table
 create Force view eview as select * from Etab;
 -- view created with errors
 desc Etab
 Error : object not exists
 create table Etab as select * from emp;
 desc eview
 select * from eview;
 desc eview

 Join Views: View based on multiple tables
 create view edept as select     empno,ename,sal,job,emp.deptno,dname,loc
 from emp,dept
 where emp.deptno = dept.deptno;

 desc edept
 select * from edept;

 Note:
 7.x - Join views are read only views
 8.0 - Only 1 table of view can be manipulated thru          view   i.e. Key preserved table
 8i -  Both the tables of view are allowed for   manipulation thru view using "Instead of Triggers"  in pl/sql.

 8.0 - Key preserved table Table whose key column is not duplicated in view  result is known as key preserved table .
 Dept --- deptno (pk)
 Emp --- empno (pk)
 Here Emp is key preserved table . DML allowed on  Emp columns thru view.
 Dept is non key preserved table . DML not allowed  on Dept columns thru view.

 select * from edept;
 empno            deptno
 --------            ---------
 7900            10
 7788            20
 7499            10
 7369            20
 7844            10
 7902            30
 7566            30

 update edept set sal = sal + 3000
 where empno =   7900;
 delete from edept where empno = 7902;
 update edept set deptno = 30 where empno =  7369;
 update edept set dname = 'PHARMA' 
 where deptno = 30;

 Materialized view : [ 8i ] It is a static view.
 It holds data in it.
 It will not support DML on it.
 DML on Table will not be reflected in view.
 To create it  " create materialized view "  permission is required.
 It is used to maintain Historic data.
 It is used for Data analysis & Reporting purpose.

 System / Sys : ( DBA ) Grant create materialized view to scott;
 scott :
 create materialized view mv1 as select * from   emp;
 desc mv1
 select * from mv1;

 drop materialized view mv1;
---------------------------------------------------------------

Triggers in Oracle

 Database Triggers:
 - A set of pl/sql statements stored permenantly in    database and "automatically" activated when ever    an event raising statement ( DML ) is executed.
 - They are stored in "User_Triggers" system table.
 - They r used to impose Business rules / user        defined restrictions on Table.
 - They r also activated when Tables are     manipulated by other users or by other application   s/w tools.
 - They provide high security on tables.

 Trigger Parts:  4 Parts
 1. Triggering Event : Indicates when to activate the       trigger .    
Before  -- insert / update / delete
After   --  insert / update / delete 

 2. Trigger Types: 2 Types
 i) Row Triggers : Activates the trigger for every      row manipulated by DML statement.
 ii) Statement Triggers: Activates the trigger for        only once  for 1 DML statement ( Default Type )

 3. Trigger Restriction:
  Used to stop the activation of trigger based on     condition.  If condition is True trigger is active.

 4. Trigger Body: A set of pl/sql statements

 Syntax:
 Create or replace Trigger < Trigger name >
 before/after insert or update or delete 
 [ of <columns> ] on < Table name >
 [ for each row
 when (< condition >) * if condition is True trigger                                         is  executed
 declare                                  
   < variable declaration >; ]
 begin
   < exec stmts >;
 [ exception
   <exec stmts >; ]
 end;
---------------------------------------------------------------
* Trigger converts student name to upper case        automatically. create or replace Trigger up_con
 before insert on stu_info
 for each row
 begin
  :new.sname = upper(:new.sname);
 end;

 insert into stu_info    values(101,'ram','ORACLE',2500);
 >select * from stu_info;
   101  RAM  ORACLE  2500
---------------------------------------------------------------  
New & Old (Pseudo Columns):-------------------------------------
 Used to retrieve data from DML stmt temporary   buffer.  * valid with "ROW" triggers only.

    INSERT   UPDATE    DELETE
 NEW    valid         valid        invalid
 OLD    invalid      valid         valid

 * They r refered with : (colon) to indicate as Bind       variables.
    They r accessed from SQL buffer to Pl/sql block.
---------------------------------------------------------------
 Triggering Events: Before insert Row,  Before update Row,
 Before delete Row
 Before insert Stmt,  Before update Stmt,
 Before delete Stmt
 After insert Row,  After update Row,
 After delete Row
 After insert Stmt,  After update Stmt,
 After delete Stmt
 12 Events per Table are allowed.
---------------------------------------------------------------
 * Modifying existing trigger create or replace Trigger up_con
 before insert on stu_info
 for each row
 begin
 if :new.course = 'oracle' and :new.fee_paid = 2500  then
 :new.sname := upper(:new.sname);     * stu_info
 else                                                       ---------- 
 :new.sname := lower(:new.sname);         roll
 end if;                                                    sname
 end;                                                       course
                                                              fee_paid
 insert into stu_info   values(111,'sekhar','oracle',2500);
 insert into stu_info   values(112,'SRINATH','oracle',1000);
 select * from stu_info;
 111 SEKHAR  oracle 2500
 112 srinath oracle 1000
---------------------------------------------------------------
* Trigger Checks for valid increment on employ          salary :>create or replace trigger chk_incr
   before update of sal on emp
   for each row
   begin
   if :new.sal <= :old.sal then
   raise_application_error(-20300,' Increment must      be more  than existing salary ');
   end if;
   end;

 >update emp set sal = sal - 3000
    where empno = 7900;
 >update emp set sal = sal - sal *.35;
---------------------------------------------------------------
 Note: Column Specification [ of sal ] is supported with   UPDATE event only.  Not valid with Insert and    Delete Operations ( Row Level Operations ).

 * Raise_application_error(error no,error message); Built_in sub program stops the DML stmt execution
 and displays the error message.
 Error No Range : -20001 to -20999
 Oracle Reserved Error Nos: -20000 to +20000
---------------------------------------------------------------
 
 * Trigger checks for null value in dept name. >create or replace trigger chk_null
   before insert or update of dname on dept
   for each row
   begin
   if :new.dname is null then
   raise_application_error(-20301,
    ' Department name cannot be blank ');
  end if;
 end;

 >insert into dept values(88,null,'Hyd');
 >update dept set dname = null
   where deptno = 30;
---------------------------------------------------------------
 Note: Constraint Vs Trigger Constraints will check for existing rows also
 Triggers will not check for existing rows

 Constraints provide standard error messages
 Triggers provide user friendly error messages

 Constraints provides pre-defined rules on table   columns.
 Triggers provides user-defined rules on table   columns

* If constraint  & Trigger are defined on table at a      time only Trigger will be activated.---------------------------------------------------------------
 > Create table job_pays
   ( job varchar2(20), losal number(10),
     hisal number(10));

 insert into job_pays values
 ('CLERK' , 3000 , 10000);
 insert into job_pays values
 ('SALESMAN' , 5000 , 15000);
 insert into job_pays values
 ('MANAGER' ,  8000  , 20000);
 insert into job_pays values
 ('ANALYST' , 10000 , 30000);
 insert into job_pays values
 ('EXECUTIVE' , 10000 , 25000);

 * Trigger Checks for valid salary according to the     Job. create or replace trigger chk_sal
 before insert on emp for each row
 -- if condition is True trigger will be executed
 when (new.job <> 'PRESIDENT') 
 declare
 minpay number(10);
 maxpay number(10);
 begin
 select losal,hisal into minpay,maxpay from      job_pays where job = :new.job;
 if :new.sal < minpay or :new.sal > maxpay then
 raise_application_error(-20200,' Salary must be    between '||minpay||' and '||maxpay||' for Job    '||:new.job);
 end if;
 exception
 when no_data_found then
 raise_application_error(-20201,' No such job exists  '||:new.job);
 end;

 INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)
  VALUES(&1,'&2','&3',&4);
--------------------------------------------------------------- 
* Trigger adds the increment amount automatically    to Employ salary. ( Using IMPLICIT Cursor )                   
 create or replace trigger add_incr
 before insert on incr for each row
 begin   
   update emp set sal = sal + :new.amt
   where empno = :new.empno;
     -- Using implicit cursor
   if sql%notfound then
   raise_application_error(-20111,' No such employ     exists with employ code '||:new.empno);
   end if;
   end;

 create table incr(empno number(4), amt    number(10));
 select empno,ename,sal from emp
 where empno = 7900;
 insert into incr values(7900,4000);
 select empno,ename,sal from emp
 where empno = 7900;
 insert into incr values(799,2000);
---------------------------------------------------------------  
Student        Fee_instalments
 ---------        --------------------
 roll - 101                 roll   prev_fee   curr_fee   DOP
 name - RAM        101         0            1000         ---
 course - Oracle9i 101     1000        2000        ---
 fee - 5000           101     3000        2000        ---

 * Trigger adds the fee instalment amount            automatically into fee_instalments table .
 create or replace trigger add_fee
 before insert or update on student
 for each row
 begin
 if inserting then
 insert into fee_instalments values
 (:new.roll, 0, :new.fee, sysdate);
 elsif updating then
 insert into fee_instalments values
 (:old.roll, :old.fee, :new.fee - :old.fee, sysdate);
 end if;
 end;

 insert into student     values(101,'RAM','oracle9i',1000);
 update student set fee = fee + 2000
 where roll = 101;
 update student set fee = fee + 2000
 where roll = 101;
 select * from fee_instalments;

* Keywords:  inserting, updating, deleting 
 -- Represents insert,update and delete operations
 -- Valid in Triggers only
---------------------------------------------------------------
 Transaction -- Table
 --------------
 Accno   tran_type      amt
 101    D     5000
 102    D     7500
 103        D     1000
 104         W     1000
 105         W     9000

 * Statement Level Trigger:
 * Triggers Locks the Transaction table for update        and delete operations. ( Only insert allowed )

 create or replace trigger lock_trans
 before update or delete on transaction
 begin
 raise_application_error(-20555, ' Invalid operation   on Transaction Table ');
 end;

 delete from transaction; update transaction set amt = amt + 1000;
---------------------------------------------------------------  Disabling Triggers:
 >Alter table transaction disable all triggers;
 >Alter table bankmaster disable all triggers;

 Enabling Triggers: >Alter table transaction enable all triggers;
 >Alter table bankmaster enable all triggers;

 Removing Triggers: Drop trigger < Trigger name >;
 >Drop trigger CHK_SAL;

 Checking for Existing Triggers: >desc User_triggers
 >select  trigger_name , triggering_event from
   user_triggers where table_name = 'EMP';
 >select * from user_triggers
   where trigger_name = 'CHK_SAL';
 >select text from USER_SOURCE
    where name = 'CHK_SAL';
 > desc user_source
 
 * User_source -- system table holds the details of       Triggers .---------------------------------------------------------------
 * Statement Level Trigger:
 * Trigger Locks the table for DML operations to         provide security:
 * Trigger checks for i> Valid Timings ii> Weekends
    iii> Public Holidays

 create or replace trigger security_chk
 before insert or update or delete on Bankmaster
 declare
 a number;
 begin
 -- check for valid Timings
 if to_char(sysdate,'hh24') not in     (10,11,12,13,14,15,16,17) then
 raise_application_error(-20111,' No operations     allowed -  Invalid Timings ');
 end if;
 -- check for weekends
 if to_char(sysdate,'dy') in ('sat','sun') then
 raise_application_error(-20112,' No operations     allowed -  Weekends ');
 end if;
 -- check for Public Holidays
 select count(*) into a from holiday
 where to_date(hdate) = to_date(sysdate);
 if a > 0 then
 raise_application_error(-20113,' No operations      allowed - Public Holiday ');
 end if;
 end;

 Holiday
 --------
 hdate --- '15-aug-10'            
 description --- 'Independence Day'  
---------------------------------------------------------------
* Trigger updates Transactions automatically to       Master table.

 Bankmaster        Transaction
 -------------        --------------
 accno            accno
 name                                  tran_type
 acc_type            tran_date
 curr_bal            amt
                                            
 insert into bankmaster   values(101,'RAM','S',20000);   

 create or replace trigger add_deposit
 After insert on transaction for each row
 begin
 if :new.tran_type = 'D' then
 update bankmaster set curr_bal = curr_bal +    :new.amt  where accno = :new.accno;
 end if;
 end;

 insert into transaction   values(101,'D',sysdate,10000);
 insert into transaction   values(101,'D',sysdate,30000);
 select * from bankmaster;
 101    RAM    S   60000
---------------------------------------------------------------
 Instead of Triggers: [ 8i ]
 - Triggers supported only on "views"
 * - Used to perform DML operations on Join views
 *  Instead of  --- insert, update, delete

 Ex: 1
 >create view v1 as select * from emp;

 > create trigger t1 instead of delete on v1
    for each row
 begin
 dbms_output.put_line(' Record Removed ');
 end;

 > delete from v1 where empno = 7900;
 > select * from v1 where empno = 7900;

  create trigger t2 instead of delete on v1
    for each row
 begin
 insert into del_tab    values(:old.empno,:old.ename,sysdate,user);
 end;
---------------------------------------------------------------
 
 ** Manipulating Join views thru Instead of Triggers Ex: 2
 >create view edept as select empno, ename, sal,     job, emp.deptno, dept.deptno dno, dname, loc      from emp, dept
 where emp.deptno = dept.deptno;

 > insert into edept                  values(4411,'VIJAY',20000,'MANAGER',
   99,99,'TECHNICAL','HITECH');   --- error

 >create trigger t2 instead of insert on edept
  for each row
  begin
  insert into dept          values(:new.dno,:new.dname,:new.loc);
  insert into emp(empno,ename,sal,job,deptno)         values (:new.empno,:new.ename,:new.sal,          :new.job, :new.deptno);
  end;
 /

 >insert into edept values (......);   --- repeat step 2
 >select * from edept;
---------------------------------------------------------------
 Ex : 3
 >delete from edept where deptno = 99;
 ( Removes data from emp table only - key                  preserved table )

 * Trigger Removes records from 2 tables create or replace trigger trig11
 instead of delete on edept
 for each row
 begin
 delete from emp where deptno = :old.deptno;
 delete from dept where deptno = :old.deptno;
 end;

 delete from edept where deptno = 99; ( Removes    data from emp ,dept table also )
---------------------------------------------------------------  
Invalid Triggers : 1. >create table temp1(c1 number(3),
        c2 varchar2(10));

 2. >create trigger trig1
     before insert on temp1 for each row
     begin
      insert into temp1 values(102,'SIVA');
     end;
    /

 3. >insert into temp1 values(101,'RAM');
     Error:  It leads to an infinite loop
---------------------------------------------------------------  
>create trigger trig2
   before insert on temp1 for each row
   begin
   update temp1 set c2 = 'HARI';
   end;

 >insert into temp1 values (102,'RAVI');

 It will not perform update in Trigger body becoz 2    DML operations cannot be performed on table at      once.  It leads to a " Trigger Mutuation " error   while working in other s/w tools.
---------------------------------------------------------------  
Note:
 **  TCL commands are not allowed in Triggers.
 Becoz Trigger will be executed while  performing     DML stmt but TCL has to be given after DML stmt    execution.

---------------------------------------------------------------  
Advantage of Database Triggers:  - Automatic execution of code based on event .
  - Used to impose user defined restrictions on             Tables .
  - Provides Security on Tables while Manipulating        data from any where .
---------------------------------------------------------------

Synonym in Oracle

 It is used to hide original name and owner of the  Table.
 It provides security by hiding identity of the  component.
 Desc ,DML and Select are allowed.
 DML on Table are reflected in synonym and DML  on synonym are reflected in Table.
 It will not hold data.
 It is stored permanantly in "user_synonyms"  system table.
 It can be shared with other users.
 2 types
 1. Private synonym : created by user
 2. Public synonym : created by DBA only

 Syntax : private synonym
 create synonym <synonym name> for
 <db object name>;


 Ex: create synonym Esyn for emp;
      desc esyn
      select * from esyn;
   insert into esyn values (....................);
   update esyn set sal = sal + 3000
   where deptno = 30;
   delete from esyn where sal > 30000;
   select * from emp;
   grant all on esyn to user1;

  user1 :
  select * from scott.esyn;
  insert into scott.esyn values (................);

  Scott :
 desc user_synonyms
 select * from user_synonyms;
 drop synonym esyn;

 Public Synonym : ( DBA )
 create public synonym stu_info for student;
 grant all on stu_info to public;

 scott :   select * from stu_info;

 user1 :  select * from stu_info;
         insert into stu_info values(...............);

 user2 : select * from stu_info;

 View Vs Synonym :

 Views will support to share selected rows and   columns  with other users.
 Views support to retrieve arithematic expressions   and multiple table contents .

 Synonym supports to share entire object with other  user.

Oracle in Java programs

 Java is a Built_in software
 Supports to execute Java programs with in oracle server or  client System.
 SQLJ : It is an Transalator.  It will check for SQL stmt syntax errors and translate       the Sql commands into Java equavalent code.
    After Translation it returns .Java file .

 Sql stmts are provided in below format in Sqlj script. #sql { DDL, DML , TCL , DCL };

 Steps:
 Provide Path settings and User details in   "connect.properties" system file. 1. Open Notepad and add the Code
 2. save file as   "filename.sqlj"
 3. C:\>sqlj  filename.sqlj -- check for sql syntax and                                                  translate into .java file
 4. C:\>javac  filename.java -- compiles java program
                                        and gives .class file
 5. C:\>java  filename  -- Execute .class file and gives                                                       final output.
 Iterators: Used to retrieve data from Oracle table to Java program.
 similar to cursors in PL/SQL. 2 Types
 1. Named Iterator - Similar to cursor with For loop
 2. Positional Iterator - Similar to cursor with Operations
--------------------------------------------------------------------------
 Path & Classpath :
 set   path=C:\oracle\ora92\bin;C:\oracle\ora92\jdk\bin;%path%

 set classpath=C:\oracle\ora92\sqlj\lib\translator.jar;
 C:\oracle\ora92\sqlj\lib\runtime12.jar;
 C:\oracle\ora92\jdbc\lib\classes12.jar;%classpath%

Connect.Properties File :

 # Users should uncomment one of the following URLs or      add their own.
 # (If using Thin, edit as appropriate.)
 sqlj.url=jdbc:oracle:thin:@localhost:1521:oracle
 #sqlj.url=jdbc:oracle:oci8:@
 #sqlj.url=jdbc:oracle:oci7:@

 # User name and password here (edit to use different     user/password)
 sqlj.user=sridhar
 sqlj.password=sri123
--------------------------------------------------------------------
 Example 1:    Demo1.sqlj

 /* Example to Create Table, Insert,
     Update and Commit */

 import java.sql.*;
 import java.sql.SQLException;
 import oracle.sqlj.runtime.Oracle;

 /* User Defined Class Holding User defined Funtion */
 class Myclass
 {
 public void myfun() throws SQLException
 {
 #sql { create table EMPLOY111(eno number(4),name    varchar2(20), basic number(10)) };
 System.out.println(" Table created ....! ");
 #sql { insert into EMPLOY111 values(101,'sridhar',8000) };
 #sql { update EMPLOY111 set basic = basic + 4000 where           eno = 101};
 #sql { commit };
 System.out.println(" Table data saved ....... ");
 }
 }

 /* Main Class Holding main Function */
  public class Demo1
  {
  public static void main(String args[])
  {
  try
  {
   Oracle.connect(Demo1.class,"connect.properties");
   Myclass obj1 = new Myclass();
   obj1.myfun();
   }
  catch(SQLException e)
  {
   System.out.println(" The error is " +e);
 }
 }
 }

 C:\>sqlj Demo1.sqlj
 C:\>javac Demo1.java
 C:\>java Demo1



Function:
Create or replace function f1 (veno number) return number is
vsal number(12,2);
Begin
Select sal into vsal from emp where empno = veno;
Return(round(vsal * 12));
End;

Example  2 :  Demo2.sqlj

/* Example to Call a Function from Database */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

/* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
int eno = 7900;
float asal;
#sql { asal = { VALUES f1(:eno) };
 /* VALUES - built_in to call a function in SQLJ */
System.out.println("Annual Salary of employ is:  " +asal);
}
}

/* Main Class Holding main Function */
public class Demo2
{
public static void main(string args())
{
try
{
Oracle.connect(Demo2.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is " +e);
}
}
}


Procedure:
Create or replace procedure p1 (veno number, incr number) is
Begin
Update emp set sal = sal + incr where empno = veno;
End ;

Example 3 :  Demo3.sqlj

/* Example to Call a Procedure from Database */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

/* User Defined Class Holding User defined Function */
class Myclass
{
public void myfun() throws SQLException
{
int e = 7788;
float s = 5000;
#sql { CALL p1(:e,:s) };  
/* CALL - built_in to call a procedure in SQLJ */
#sql { commit };
System.out.println(" Table data saved ....... ");
}
}

/* Main Class Holding main Function */
public class Demo3
{
public static void main(string args())
{
try
{
Oracle.connect(Demo3.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is " +e);
}
}
}

Example 4 :  Demo4.sqlj

/* Example for Creating Nested Tables  */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

 /* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
#sql{ create type book_type as object(bid number(3),title varchar2(15),doi date, dor date) };
#sql { create type books as table of book_type };
System.out.println("Nested table created .....");
#sql { create table student (roll number(4) primary key, name varchar2(20), binfo books) nested table binfo store as book_table };
System.out.println("Table created.....");
#sql { insert into student values( 1001 , 'Sridhar',
books (book_type(11,'sql',sysdate,sysdate + 5),
book_type(12,'pl/sql',sysdate,sysdate + 15),
book_type(13,'dba',sysdate,sysdate + 10))) } ;
System.out.println("Record Inserted .....");
}
}

/* Main Class Holding main Function */
public class Demo4
{
public static void main(string args())
{
try
{
Oracle.connect(Demo4.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}

Example 5: Demo5.sqlj

/* Example for Creating Varrying Arrays */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

/* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
#sql{ CREATE OR REPLACE TYPE CATY AS VARRAY(3) OF VARCHAR2(30) };
System.out.println(" Type created ...... ");
#sql { create table TV (tcode number(3),brand caty) };
#sql { insert into TV values(101, caty('LG','ONIDA','BPL')) };
#sql{commit};
System.out.println("Data Saved ........!");
}
}

/* Main Class Holding main Function */
public class Demo5
{
public static void main(string args())
{
try
{
Oracle.connect(Demo5.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}

Example 6: Demo6.sqlj

/* Example Using Named Iterator to retrieve data  */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

/* Declaring a Named iterator */
#sql iterator ss (String ename,float sal,float comm);

/* User Defined Class Holding User defined Funtion */
class Myclass
{
public void myfun() throws SQLException
{
ss myiter;
#sql myiter = { select ename,sal,nvl(comm,0) as comm from emp };
while (myiter.next())
{
System.out.print("Name is :"+myiter.ename());
System.out.print("Salary is :"+myiter.sal());
System.out.println("Commision is :"+myiter.comm());
}
}
}

/* Main Class Holding main Function */
public class Demo6
{
public static void main(string args())
{
try
{
Oracle.connect(Demo6.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}

Example 7:  Demo7.sqlj

/* Example Using Positional Iterator to retrieve data  */

import java.sql.*;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import java.lang.String;

/* Declaring a Positional Iterator */
#sql iterator ss (String ,float ,String);

/* User Defined Class Holding User defined Function */
class Myclass
{
public void myfun() throws SQLException
{
ss myiter = null;
String n = null;
String j = null;
float s = 0;
#sql myiter = { select ename,sal,job from emp };
while (true)
{
#sql {fetch :myiter into :n,:s,:j};
if (myiter.endFetch())
{ break };
System.out.print("Name is :"+n);
System.out.print("Salary is :"+s);
System.out.println("Designation is :"+j);
}
myiter.close();
}
}

/* Main Class Holding main Function */
public class Demo7
{
public static void main(string args())
{
try
{
Oracle.connect(Demo7.class,"connect.properties"};
Myclass obj1 = new Myclass();
obj1.myfun();
}
catch(SQLException e)
{
System.out.println(" The error is : " +e);
}
}
}

SQL Environment Settings

 SQL Environment Settings: set - used to provide sql environment settings
      - valid only per session
 >set null novalue/space
 >set feedback off/on -- supports with all sql stmts
 >set heading off/on
 >set pause on/off  -- page by page display
 >select * from emp;
 >set linesize 200/80 -- no of character's per line
 >set pagesize 120/14/24(10g) -- no of lines                                                             per page
 >select * from emp;
 >set autocommit on/off  -- valid for DML stmts                                           only
 --------------------------------------------------------------  
spool:  Used to store the sql screen content to an
            OS file (.Lst file)
 >spool <filename>
 >spool off - stops the transfering of sql screen                    content into OS file.
 >spool <filename> append
 Adds the sql screen content to existing spool file
 ( 10g )
--------------------------------------------------------------- 
SQL Editor Commands : i - insert mode ( new line )
 a - append mode ( same line )
 ed -- opens editor ( notepad )
 / -- run the buffer content
 L -- list the buffer content
 c/old/new -- change the column/word in sql stmt
 save <filename>-- stores the buffer content
 into .SQL file
 ed <filename> -- opens file in editor ( Notepad )
 get <filename> -- displays the file
 @<filename> or start <filename> -- execute the                                                         file
--------------------------------------------------------------- 
Substitution Operator: &  [ &character ] used to accept the values from key board.
 valid for that particular stmt only.
 >select * from &tname where &condition;
 >insert into STUDENT                 values(&roll,'&sname','&course',&fee);
 >insert into stu_info values(&1,'&2','&3',&4);

 Substitution Operator: && valid for compleate session.
 >select * from &&tname where &condition;
 >insert into stu_info        values(&roll,'&sname','&&course',&fee);
---------------------------------------------------------------
 SQL Reporting Commands: - Used to arrange the select stmt output in          readable format
 - Automatically applied to any "select" stmt output
 - valid only per session
 - Non sql commands valid in sql Environment of        Oracle
 - They are not related to any Table
 5 commands
 1. Ttitle - Top Title
 2. Btitle - Bottom Title
 3. Column Headings
 4. Formats
 5. Control break reports

 ttitle 'Employ Report'
 btitle 'End of Report'
 select * from emp;
 select * from dept;
 ttitle off / on
 btitle off / on
 ttitle left 'Employ Report'  -- left / right / center   (default)
 btitle left 'End of Report'

 >ttitle left  'Student Report of Oracle9i' skip
           left  'For the month of Dec 2009'
 >select * from student where course = 'oracle9i';

   Skip - splits the title into 2 lines---------------------------------------------------------------
 Column Headings & Formats:
 column empno heading 'Employ|Number'
 column ename heading 'Emp Name'
 column sal heading 'Salary' format $9,99,999
 column comm heading 'Commision' format   $9,99,999
 column pf heading 'Provident|Fund' format    $9,9999.99

 | - pipe ( Splits column heading into 2 lines)
 >Select empno,ename,sal,comm from emp;
 >Select ename, sal , sal * .15 pf from emp;

 To clear Headings:
 column empno clear
 column ename clear
 column sal clear
 column comm clear

 >Select empno "Employ Number", ename "Emp        Name", sal  Salary, comm  Commision from emp;

 Note: Alias name is valid in that select stmt output only
 Alias name will not support Formats
 * Column headings are valid till the end of session
    and support formats.

 >select empno,ename,'Rs.'||to_char(sal) as salary
  from emp;  -- Displaying salary with " Rs. "
---------------------------------------------------------------
 Control Break Reports --------------------------
 break on deptno compute sum of sal on deptno
 select deptno,ename,sal from emp order by   deptno;

 break on job compute sum of sal on Job
 select Job,ename,sal from emp order by Job;

 break on course compute sum of fee_paid on course
 select course, sname, fee_paid from stu_info
 order by course;

 To clear Break:
 >clear breaks
---------------------------------------------------------------
 Non-SQL commands: -- supported in sql * plus environment only
 Sql Reporting commands
 Describe , set , spool , & , &&
 SQL editor commands

Scalar Query in Oracle

 Scalar Query : [ 8.0 ] Select statement provided in place of column name  is  known as Scalar query.
 It is an Independent Query.
 Advantage : Used to retrieve Data Analysis reports

 * List the department details along with no.of             Employees and Total Salary .

 select deptno, dname, loc ,
 ( select count(*) from emp where deptno =      d.deptno ) ecount,
 ( select sum(sal) from emp where deptno =     d.deptno ) totsal  from dept d ;
    deptno   dname    loc   ecount    totsal

 Using 2 Queries :

 select job,ename,sal from emp order by job;

 select job ,min(sal) lopay , max(sal) hipay from     emp group by job order by job ;

 Using Scalar Query :

 select job,ename,sal,
 ( select min(sal) from emp where job = e.job )        lopay ,
 ( select max(sal) from emp where job = e.job )        hipay
   from emp e order by job ;
       job    ename   sal   lopay   hipay

  Select deptno,ename,sal,
  ( select min(sal) from emp
     where deptno = e.deptno) lopay ,
  ( select max(sal) from emp where deptno =            e.deptno ) hipay from emp e order by deptno ;
      deptno    ename   sal   lopay   hipay

 SELECT :

 SELECT - clauses
              ( where ,group by, having, order by)
               Joins
               Set operators
               Sub Queries
               Co-related sub query
               Scalar query  , Inline View
               Arithematic Expressions
               Built-in functions

Pseudo Columns in Oracle

 Pseudo columns:
   Automatically filled by oracle.
 Ex: sysdate, nextval, currval, rowid, rownum,level,         sqlcode, sqlerrm, new, old .

 Rowid: It is an unique value
 It is automatically assigned with every row inserted  into table.
 It is stored permanantly in database
 It is an 18 Bit Hexa decimal value.
 It comprises of Object id, Data file id, Block id &
 Record id.

 select rowid,dname,loc from dept;
 select rowid,empno,ename,sal from emp;
 ........AAA    101
 ........AAB    102           
 ........AAC    103
 ........AAD    104
 ........AAE    105

 ** Removing Duplicate records : > Delete from emp where rowid not in
   ( select min(rowid) from emp group by empno);

 * Retrive all rows except Last Record   > select empno,ename,sal,deptno from emp
      where rowid not in (select max(rowid) from              emp);

 * Update all rows except First row in Table. >Update emp set sal = sal + 2500 where rowid         not in (select min(rowid) from emp);
---------------------------------------------------------------
 
 Rownum :  It is an unique value
  It is an dynamic value automatically retrieved     along with Select statement output.
  It is only for display purpose.
  It is not stored in database.

 >select rownum,ename,sal from emp;
 >select rownum,dname,loc from dept;

 * Retrieving Top 5 Highly paid Employees  > select rownum,empno,ename,job,sal from
     ( select rownum,empno,ename,job,sal from               emp order by sal desc )
       where rownum <= 5;
  
 * Retrieving Nth maximum salaried employ details
   (2 max,..)
  > select rownum,empno,ename,job,sal from
     ( select rownum,empno,ename,job,sal from            emp order by sal desc )
     group by rownum,empno,ename,job,sal
     having rownum = &N;

 * Retrieving Alternate rows   select rownum,empno,ename,job,sal from emp
   group by rownum,empno,ename,job,sal
   having mod(rownum,2) = 0; -- EVEN Rows
   [ having mod(rownum,2) != 0; ] -- ODD Rows

 * Inline view : Select statement provided in place     of table name is known as Inline view.
---------------------------------------------------------------
  Level :

 It will arrange the select statement output in  Inverted tree structure ( Hierarichal Tree ) and   gives the position of  row in Tree. ( Returns   Number )

 * Retrieving the Hierarichy of employees based on     their Superior.  >Select Level,empno,ename,sal,job,mgr from emp
   connect by prior empno = mgr start with
   mgr is null order by Level;

 * Retrieving Nth maximum salary using Level.    ( * Duplicates are eliminated )
  >Select Level,max(Sal) from emp where Level =       &N  connect by prior sal > sal group by Level;
---------------------------------------------------------------
 
 Locks : Used to preserve the rows for manipulation     purpose to prevent the other users to access the    same data at the same time.
 They prevent Dead Locks .
 They improve Data concurrency
 ( sharing with multiple users ).

 2 Types  1. Implicit Locks   2. Explicit Locks

 Implicit Locks : Automatically imposed by oracle      whenever  " DML" operations are performed by   user.

 Explicit Locks : They are imposed by user before      manipulating data.  2 Types
 i> Row Level Locks : used to lock the selected   rows of table. It is imposed with " For Update "   clause in select.

 Ex:1
 > select * from emp where empno = 7900
    for update;
   > update emp set sal = sal + 3000
          where empno =  7900;
      > commit;
 Ex:2
  > select * from emp where deptno = 10
     for update;
  > update emp set sal = sal + 1000
     where deptno  = 10;
  > commit;

 ii> Table Level Locks : Used to lock entire table
      > Lock table emp in Exclusive mode;
      > update emp set sal = sal + sal * .25;
      > commit;
 
 Note :
 DML are not allowed by other users when table is     locked by user.  Commit / rollback will release any   type of lock applied.
---------------------------------------------------------------
 
 E.F. Codd's Rules: Every DBMS package has to support min 6 / 12    rules to be declared as RDBMS.

 1. Information Representation
 2. Guaranteed Access
 3. Systematic treatment of Null values
 4. View Updation
 5. Comprehensive Data sub language
 6. High level Insert,Update,Delete
 7. Data discription rule
 8. Data distribution rule
 9. Physical Data Independence
 10. Logical Data Independence
 11. Data Integrity rules
 12. Non sub version rule
---------------------------------------------------------------
 Sub Programs: ( Named Pl/sql Blocks )
 A set of pl/sql statements stored permenantly in
 database and used to perform a task.
 They can accept input from user as arguments      dynamically.
 They are stored in "user_source" system table.
 They are re-usable components.
 They can be shared with other users.
 They can be used in other application s/w tools.
 They are faster in execution - stored in compiled     format.
 They support modularity.
 Modularity - A huge task divided into N no.of sub     tasks.   Easy to manage the Logic.
            Easy to debug errors.
 2 Types .  i> Procedures    ii> Functions

 Procedure: A sub program type performs a task     and will not return the values.
    --  Generally procedures are used to perform            DML operations on database.
   --  They cannot be called in SELECT stmt.

 Function: A sub program type performs a task and   will return the value. ( Returns only one value )
     -- Generally functions are used for Reporting                purpose or for calculating results.
            --  They can be called in SELECT stmt.
            -- DML operations are not allowed

 Syntax : Procedure
 Create or replace procedure <procedure name>
 [ (arguments  MODE  <data type>) ]  is
 [ <variable declaration>; ]
 begin
 <exec stmts>;
 [ exception
 <exec stmts>; ]
 end;

 Procedure can be used in SQL , Other S/W tools, * PL/SQL, Triggers, Procedures, Functions,             Packages,Developer 6i.

 * Syntax: ( Using Procedure )
  begin
  Procedure_name(arguments);
  end;

 Syntax : Function
 Create or replace Function <Function name>
 [ (arguments  MODE  <data type>) ] 
 Return <data type> is
 [ <variable declaration> ];
 begin
 <exec stmts>;
 Return( variable );
 [ exception
 <exec stmts>; ]
 end;

 Function can be used in SQL , Select ,Other Appln    S/W  tools
 * PL/SQL, Triggers, Procedures, Functions,            Packages, Developer 6i

 * Syntax: ( Using Function )
  begin
  variable := Function_name(arguments);
  end;
---------------------------------------------------------------
 
 * Procedure calculates Intrest: create or replace procedure cal_intr
 ( p number, n number, r number )
 is
 si number(12);
 ci number(14);
 begin
 si := ( p * n * r )/100;   --   pnr/100
 ci := P * power((1 + r/100),n);  -- p*(1+r/100)^n
 dbms_output.put_line(' Simple Intrest is : '||si);
 dbms_output.put_line(' Compound Intrest is :'||ci);
 end;

 Using Procedure: At SQL:
 > desc cal_intr
 > exec cal_intr(12000,12,8.5);   *exec/execute
 > exec cal_intr(100000,25,11.5);

 In PL/SQL: >begin
  cal_intr(120000,12,8.5);
  end;
--------------------------------------------------------------- 
* Function calculates Intrest: create or replace Function intr_cal
 ( p number, n number, r number )
 return number is
 ci number(14);
 begin
  ci := P * power((1 + r/100),n);
 return(round(ci));
 end;

 Using Function: At SQL:
 > desc intr_cal
 i)> select intr_cal(120000,12,8.5),                intr_cal(100000,25,11.5)  from dual;
 ii)> variable result number  -- declaring variable at                                                            sql
    > exec :result := intr_cal(75000,10,9.5);
    > print result                      * variable/var

 In PL/SQL: declare
 result number(14);
 begin
 result := intr_cal(120000,12,8.5);
 dbms_output.put_line(' Compound Intrest is :'     ||result);
 end;

 >create table loan_master
 (cust_id number(4), cname varchar2(20), amount    number(12), duration number(3),
 irate number(5,2));
 >insert into loan_master values   (&1,'&2',&3,&4,&5);

 >select cust_id, upper(cname),    intr_cal(amount,duration,irate)  as "Intrest"  from    loan_master;
---------------------------------------------------------------
 
 * Function checks for Leap year   create or replace function chk_year( y number )
   return varchar2 is
   begin
   if mod(y,4) = 0 then
   return(' Leap Year ');
   else
   return(' Not Leap Year ');
   end if;
   end;
         
 Using Function:   At SQL: > desc chk_year
 > select chk_year(2008), chk_year(2010) from         dual;
 > var result varchar2(30)
 > exec :result := chk_year(2012);
 > print result
 > select ename, hiredate,
   chk_year(to_char(hiredate,'yyyy'))  "year" from       emp;

 set autoprint on --- Automatically prints the      variables . No need to use print stmt.
---------------------------------------------------------------
 * Procedure calculates the new commision for all        employees of a particular department . create or replace procedure cal_comm
 (vdept  number) is
  cursor c1 is select empno,ename,comm from emp
  where deptno = vdept;
  i c1%rowtype;
 begin
 dbms_output.put_line
 (' Employ commision Report ');
 for i in c1 loop
 if i.comm is null then
 i.comm := 3000;
 elsif i.comm = 0 then
 i.comm := 2500;
 else
 i.comm := i.comm + i.comm * .25;
 end if;
 update emp set comm = i.comm where empno =    i.empno;
 dbms_output.put_line(i.empno||'  '||i.ename
 ||'    '||i.comm);
 end loop;
 commit;
 end;

 Using Procedure at SQL:  >desc cal_comm
 >exec cal_comm(30);
--------------------------------------------------------------- 
* Checking the Existing Subprograms >desc user_source
 >select name from user_source;
 >select text from user_source where name =          'CHK_YEAR';
 * User_source -- System Table holds source code      of Triggers, Procedures, Functions and Packages

 To Remove Sub Programs: drop procedure <proc name>;
 drop function <func name>;
 >drop procedure cal_intr;
 >drop function chk_year;

 Sharing Sub programs:
 scott :
 >Grant execute on chk_year to user1;
 user1:
 > desc scott.chk_year
 > select scott.chk_year(2020),             scott.chk_year(2015),
    scott.chk_year(2016) from dual;
 >Revoke execute on chk_year from user1;
---------------------------------------------------------------
 
 * Procedure Prints the Students Fee Due Report :  >create table stu_info(roll number(3), sname           varchar2(20), course varchar2(20),
  fee_paid number(5));
  insert into stu_info values(&1,'&2','&&3',&4);

 create or replace procedure
 stu_rep(vcourse varchar2,vfee number) is
 cursor c1 is select roll,sname,fee_paid from    stu_info
 where course = vcourse and fee_paid < vfee;
 due number(5); 
 k c1%rowtype;
 totdue number(10) := 0;
 begin
 dbms_output.put_line(' Student fee due Report ');
 for k in c1 loop
 due := vfee - k.fee_paid;
 dbms_output.put_line(k.roll||'  '||k.sname
 ||'   '||k.fee_paid||'  '||due);
  totdue := totdue + due;
 end loop;
 dbms_output.put_line( ' Total Due Amount is : '     ||totdue);
 end;

 At SQL:
 >desc stu_rep
 >exec stu_rep('ORACLE',2500);
 >Exec stu_rep('JAVA',3000);
 >Exec stu_rep('Dev6i',2000);
 >Exec stu_rep('UNIX',500);
---------------------------------------------------------------
 Ex : Create sequence s1 increment by 1
        start with 11;

 * Procedure adds a department into DEPT table
 >create or replace procedure add_dept
 (vname varchar2 default 'unknown',
 vloc varchar2 default 'Hyderabad') is
 begin
 insert into dept values(s1.nextval,vname,vloc);
 commit;
  end;

 Using at SQL:
 >desc add_dept
 >exec add_dept;
 >exec add_dept('SALES','MUMBAI');   
 >exec add_dept('EXPORT');
 >exec add_dept(vloc => 'CHENNAI');
 >exec add_dept(vloc => 'PUNE', vname =>              'SOFTWARE');

 Note:
 Passing arguments to sub programs is supported in  2  methods.
 i. Positional Notation: (default notation)
   Arguments are passed based on their position.
 ii. Named Notation:( => )
    Arguments are passed based on their name.

 Sharing Sub programs: >Grant execute on add_dept to user1;
 User1:
 >desc scott.add_dept
 >exec scott.add_dept;
 >exec scott.add_dept('Testing');
 >insert into scott.dept values(22,'HR','Secbad'); --                                                                      error
 * User can manipulate the Table only thru               Procedure but not directly thru insert stmt.  
 * Whenever subprograms are shared with other         users ,automatically all the components used in        subprogram are shared with other users.
---------------------------------------------------------------
 Parameter Modes in Sub Programs: ------------------------------------------
 "Mode" indicates the behaviour of argument in
  sub program
 3 Modes
 1. IN - default  mode   IN parameter is used to carry the input into sub     program. It cannot be assigned with a value inside   sub program.   

 2. OUT    OUT parameter is used to return the results from    sub program. It can be assigned with a value         inside the sub program.

 3. IN OUT    IN OUT parameter is used to carry the input and    return the results from sub program . It can be     assigned with a value inside sub program .

 Ex:
 Create or replace procedure p1
 ( a IN number, b OUT number ) is
 begin
 b := a * a;
 end;

 Using Procedure:  At SQL > desc p1
 > var x number        *variable/var
 > exec p1(5,:x);
 > print  x

 Create or replace procedure p2
 ( a number, b out number, c out number ) is
 begin
 b := a * a;
 c := a * a * a;
 end;
          
  Using Procedure:  At SQL > desc p2
 > var p number
 > var q number
 > exec p2(5,:p,:q);
 > print  p  q

 Create or replace procedure p3
 ( a in out nocopy  number)  is
 begin
 a := a * a * a;
 end;

 * Nocopy : Applied with arguments in subprograms
 It assigns the value to argument but it will not     copy into it permenantly. It will save resources.
 It will be specified while transfering Huge loads of
 data into parameters.

 Using at SQL: > desc p3
 > var n number
 > exec :n  := 5;  -- initializing variable at SQL
 > exec p3(:n);
 > print  n

 * Procedure Returns Total Salary, No.of Employees    in a department.
 create or replace procedure get_dept_info
 (dno number,dtot out number, dcnt out number) is
 begin
 select sum(sal),count(*) into dtot, dcnt from emp
 where deptno = dno;
 end;

 At Sql:
 var a number
 var b number
 exec Get_dept_info(30,:a,:b);
 print a b
---------------------------------------------------------------
 
* Write a procedure to get name,course,fee_paid        using rollno thru OUT parameters.---------------------------------------------------------------
* Function calculates Factorial of given number and
  checks for odd/even numbers:
 create or replace function cal_fact( n in number,
 f out  number) return boolean is
  fac number(12) := 1;
 begin
 -- calculating factorial
 -- 5! = 5 * 4 * 3 * 2 * 1 = 120
 for i in 1 .. n loop
 fac := fac * i;
 end loop;
 -- Storing result in OUT Parameter
 f := fac;
 -- check for odd/even
 if mod(n,2) = 0 then
 return(true);
 else
 return(false);
 end if;
 end;
 /

 Using Function:
 declare
    a number(3) := &num;
   fact number(12);          -- x boolean;
 begin
 -- calling function           --  x := cal_fact(a,fact);
 if cal_fact(a,fact) then      --  if x = true then
 dbms_output.put_line(' Given number is Even ');
 else
 dbms_output.put_line(' Given number is Odd ');
 end if;
 dbms_output.put_line(' Factorial of given number    is  : '||fact);
 end;

 Note:
 Function returning "Boolean" value or with OUT      parameter cannot be used in SQL or Select stmt.
 * DML are Not valid in Functions becoz they can be  called in " SELECT " statement.
------------------------------------------------------------  
Trigger Vs Sub Programs:
 Triggers are automatically activated by " DML "      statements.
 Procedures & Functions has to be Explicitly   invoked by user.
------------------------------------------------------------   Advantage of Sub programs:
  - Code Reusabilty
  - Faster Execution of code
  - Easy to manage code
  - Executed in the Oracle Server memory even if it       is activated from other Application s/w tools.
 - These r called as Stored Procedures & Stored         Functions ---------------------------------------------------------------
 Database Objects in ORACLE :
 Tables , Views , Synonyms , Sequences , Indexes ,  Clusters ,Roles , Triggers , Procedures , Functions ,  Packages , Objects
---------------------------------------------------------------

Programming Language(PL) of SQL Examples

 PL/SQL :  Programming Language of SQL
 ---------
 Features :
 - Supports to execute a Block of statements as a       unit
 - Supports variables n Constants
 - Supports conditional constructs
 - Supports Iteration control statements
 - Supports Error handling using Exceptions
 - Supports  to define Composite Datatypes
 - Support to execute a block of statements            automatically based on the event using Database     Triggers
 - Suports to store and share the code using        subprograms


 Pl/sql blocks:
 It is an collection of SQL and prog language stmts.
 2 Types  1. Anonymous Block  - Block with out                                                                 name
              2. Named Block - Block with fixed name
                  Ex:  Subprograms,Triggers

 Block Structure :(Anonymous)
 declare                                   Simple Block:
  [< variable decln>; ]          Begin
  begin                           < exec stmts >;       
  < exec stmts >;              end;
 [ exception                              /
  < exec stmts >; ]
 end;
 
/

 Variable  Declaration :
 
* All sql data types are supported
 * Boolean is also supported
 Declare
 veno number(4) := 7900;      
                                :=  -- Assignment operator   
                                =  -- comparision operator
 vname varchar2(20);
 vjob varchar2(20) not null := 'CLERK';
 doj date default sysdate;
 flag boolean := TRUE;
 pin constant number(6) := 500038;

 Executable Statements :
 
* DML ,TCL are supported
 * DDL , DCL are not supported
 * select .. into stmt ---> Used to retrieve the table      column contents into Pl/sql block.
    select <column list> into <variable list> from
    <table name> where <condition>;
   * dbms_output.put_line(' message text  '||                                                        variables );
      used to print messages / variables on to screen.
   * Comments
   -- single line comment
   /* multi line
       comment */
---------------------------------------------------------------
 
PL/SQL Examples : >set serveroutput on/off
 -- Activates the dbms stmt output buffer
 -- valid per session only

 Ex: PL/SQL Program retrieve the employ details        and calculates the Net salary and Prints.
 declare
   veno number(4) := &employ;  
   vname  varchar2(10);        
   vsal  number(7,2);             
   vcomm number(7,2); 
   net number(7,2);
 begin
   -- Retrieving data from table
 select ename,sal,comm into vname,vsal,vcomm       from emp where empno = veno;
 -- calculating net salary
 net := vsal + nvl(vcomm,0);
 dbms_output.put_line('Employ details are :');
 dbms_output.put_line(veno||'  '||vname||'  '||vsal
 ||'  '||vcomm||'  '||net);
 end;
 /
--------------------------------------------------------------
 save <filename> - save to local OS (.SQL file)
 get <filename> - display the file
 ed <filename> - opens file in editor
 start <filename> / @<filename> - execute the file
---------------------------------------------------------------
 
 Attribute Declaration: Used to define the pl/sql variables dynamically     according to  the Table structure.
 i) %Type - Column Type Declaration Used to define the variables according to the     specific column structure.
 Syntax : 
 Variable  <tablename>.<columnname>%type;

 >declare
    -- Using %type declaration
   veno emp.empno%type := &employ;
   vname emp.ename%type;
   vsal emp.sal%type;  
   vcomm emp.comm%type;
   net emp.sal%type;
 begin
   ---  same as above example  ---
 end;
---------------------------------------------------------------
 
 ii) %Rowtype - Record Type Declaration Used to define the variable according to the
 compleate table structure.
 Syntax :   Variable  <Tablename>%rowtype;

 Using %rowtype declaration : >declare                         -- veno    --->  i.empno
    i emp%rowtype ;       -- vname   --->  i.ename
   net number(12,2);       -- vsal   --->  i.sal
 begin                 -- vcomm  --->  i.comm
  i.empno := &employ;
 select ename,sal,comm into i.ename,i.sal,i.comm     from emp where empno = i.empno;
 net := i.sal + nvl(i.comm,0);
 dbms_output.put_line('Employ details are :');
 dbms_output.put_line(i.empno||'  '||i.ename
 ||'     '||net);
 end;
---------------------------------------------------------------  
create table student (roll number(3), name    varchar2(20), class number(2), m1 number(3),
 m2  number(3), m3 number(3), m4 number(3),  m5  number(3), m6 number(3));

 Ex:2
 * Pl/sql block calculates total marks and average       marks of a student.
  declare
   i student%rowtype ;
   tot_marks number(6);
   avg_marks number(6,2);
 begin
 i.roll := &rollno;  
 select name,class,m1,m2,m3,m4,m5,m6 into
 i.name, i.class, i.m1, i.m2, i.m3, i.m4, i.m5, i.m6    from student where roll = i.roll;
 tot_marks :=  i.m1 + i.m2 + i.m3 + i.m4 + i.m5 +                       i.m6;
 avg_marks :=  tot_marks / 6;
 dbms_output.put_line(' Student details are: ');
 dbms_output.put_line(i.roll||'  '||i.name
 ||'      '||i.class||'  '||tot_marks||'  '||avg_marks);
 end ;
---------------------------------------------------------------
 
 Conditional Constructs: Used to check for multiple conditions while
 manipulating data in pl/sql.
  i> IF       ii> CASE (8i)

 Syntax: Simple If
 if <condition> then
  <exec stmts>;
 end if;
 Syntax: Complex If
  if <condition1> then
   <exec stmts>;
  [ elsif <condition2> then  //  ELSIF -- else if 
   <exec stmts>;
  elsif <condition3> then   
   <exec stmts>;
    .......
    .......
  else
   <exec stmts>; ]
  end if;
---------------------------------------------------------------
 
 Using IF statement : declare
   i student%rowtype;
   tot_marks number(6);
   avg_marks number(6,2);
   result varchar2(50);
 begin
 i.roll := &rollno;
 select name,class,m1,m2,m3,m4,m5,m6 into
 i.name,i.class,i.m1,i.m2,i.m3,i.m4,i.m5,i.m6 from    student where roll = i.roll;
 tot_marks := i.m1 + i.m2 + i.m3 + i.m4 + i.m5 +                       i.m6;
 avg_marks := round(tot_marks / 6);
 IF i.m1 < 40 or i.m2 < 40 or i.m3 < 40 or
 i.m4 < 40 or i.m5 < 40 or i.m6 < 40 THEN
 result := 'FAIL';
 ELSIF avg_marks >= 70 THEN
 result := 'DISTINCTION';
 ELSIF avg_marks >= 60 THEN
 result := 'FIRST CLASS';
 ELSIF avg_marks >= 50 THEN
 result := 'SECOND CLASS';
 ELSE
 result := 'THIRD CLASS'; 
 END IF;
 dbms_output.put_line(' Student details are: ');
 dbms_output.put_line(i.roll||'  '||i.name||'  '||   i.class ||'  '||tot_marks||'  '||avg_marks
 ||'    '||result);
 end ;
---------------------------------------------------------------
 
 * PL/SQL Block checks for existing commission and   assign new commission . declare
   veno emp.empno%type  := &employ; 
   vname emp.ename%type;
   vcomm emp.comm%type; 
   x emp.comm%type; 
  begin
   select ename,comm into vname,vcomm from       emp where empno = veno;
  x := vcomm;  -- Storing old commission
  if vcomm is null then
  vcomm := 3000;
  elsif vcomm = 0 then
  vcomm := 2500;
  else
  vcomm := vcomm + vcomm * .25;
  end if;
  update emp set comm = vcomm
  where empno = veno;
  dbms_output.put_line(veno||'  '||vname||'  '||x
  ||'    '||vcomm);
  commit;
  end;
---------------------------------------------------------------  
Case [ 8i ]:  Used to check for multiple conditions easily.
  It will check for "equality" condition in pl/sql.
  * It can be used in "Select" stmt also.
  * Case in select is used to generate the reports

  Syntax: ( Select )
  case
  when <cond1> then  <value1>
  [ when <cond2> then  <value2>
  when <cond3> then  <value3>
  else <value4> ]
  end case

  Syntax: ( Pl/Sql )
  case <variable>
  when <value1> then
   <exe stmts>;
  [ when <value2> then
   <exe stmts>;
    .
    .
  else
   <exe stmts>; ]
  end case;

 Using Case Construct :
 declare
 grade char(1) := '&grade';
 begin
 case grade
 when 'A' then
  dbms_output.put_line(' Grade is A ');
 when 'B' then
  dbms_output.put_line(' Grade is B ');
 when 'C' then
  dbms_output.put_line(' Grade is C ');
 else
  dbms_output.put_line(' Grade is D '); 
 end case;
 end;

 Case in Select : select empno,ename,sal,job,
 case
 when job = 'CLERK' then 'C'
 when job = 'SALESMAN' then 'B'
 when job in ('MANAGER','ANALYST') then 'B+'
 when job = 'PRESIDENT' then 'A+'
 else 'D'
 end "GRADE" from emp;

 select empno,ename,job,sal,
 case
 when sal <= 3000 then 'LOW'
 when sal > 3000 and sal <= 6000 then
 'BELOW  AVG'
 when sal > 6000 and sal < 10000 then 'AVERAGE'
 when sal between 10000 and 15000 then    'NORMAL'
 when sal > 15000 then 'HIGH'
 end "RANGE" from emp;
---------------------------------------------------------------
 
 Iteration Control statements:( LOOPS ) Supports to execute a block of statements   repeatedly until   conditions are True.
 4 Types
 1. Simple loop             2. While loop
 3. Numeric For loop     4. Cursor For loop

 Simple Loop:
 It is an infinite loop task
 Syntax:
 Loop
 <exec stmts>;
 end loop;

 To break the simple loop :
 i> exit when (condition);
 ii> if ( condition )  then
     exit;
     end if;

  Using Loops:  * Pl/sql block prints first 10 numbers on to screen

  Using Simple Loop  declare
  a number(3) := 1;
  begin
  dbms_output.put_line(' The Numbers are : ');
  loop
  dbms_output.put_line(a);
  a := a + 1;
  exit when (a > 10);
  end loop;
  dbms_output.put_line(' End of numbers ');
  end;

 While Loop: It is an pre-tested loop
 Syntax:
 while ( condition ) loop
  <exec stmts>;
 end loop;

 Using While loop  declare
  a number(3) := 1;
  begin
  dbms_output.put_line(' The Numbers are : ');
  while (a <= 10) loop
  dbms_output.put_line(a);
  a := a + 1;
  end loop;
  dbms_output.put_line(' End of numbers ');
  end;

 Numeric For loop: Syntax:
 For <variable> in [reverse] <value1> .. <value2>   loop
 <exec stmts>;
 end loop;

 <variable> --- Automatically defined by for loop
 reverse (optional) --- Accepts values in reverse   order
 ..  --- Range operator
 It increments the variable by 1 always
 variable cannot be assigned with a value in For    loop

 Using Numeric For loop  Begin
  dbms_output.put_line(' The Numbers are : ');
  for n in 1 .. 20 loop
  dbms_output.put_line(n);
  end loop;
  dbms_output.put_line(' End of numbers ');
  end;

  Using Reverse option:

  Begin
  dbms_output.put_line(' The Numbers are :');
  for n in REVERSE 1 .. 10 loop
  dbms_output.put(n||'  ');
  end loop;
  dbms_output.put_line('   ');
  dbms_output.put_line(' End of numbers ');
  end;

 * dbms_output.put  --- Prints the results in same                                       line
 * dbms_output.put_line  --- Prints the results in                                               new line
 * "Put_line" must be followed with "put" stmt to         activate output buffer.
---------------------------------------------------------------