Oracle programming code_Part2

//Oracle tutorial_part2
//Cursor programming
==========================================
//Explicit cursor
/* using cursor display the details of all those emp from emp whose sum of sal and comm is>3000; */
declare
 vempno emp.empno%type;
 vename emp.ename%type;
 vsal emp.sal%type;
 vdeptno emp.deptno%type;
 cursor c1 is 
   select empno,ename,sal,deptno from emp where sal+nvl(comm,0)>3000;
 begin
   open c1;
   loop
     fetch c1 into vempno,vename,vsal,vdeptno;
     if c1%FOUND then
       dbms_output.put_line(vempno||'    '||vename||'    '||vsal||'       '||vdeptno);
     else
       exit;
     end if;
   end loop;
  close c1;
end;
-----------------------------
declare
 name emp.ename%type;
 no  emp.empno%type;
 cursor empc is select ename,empno from emp;
begin
 open empc;
 dbms_output.put_line('rowcount '||empc%ROWCOUNT);
 loop
  fetch empc into name,no;
  dbms_output.put_line('name '||name||' no'||no);
  exit when empc%NOTFOUND;
 end loop;
 dbms_output.put_line('rowcount'||empc%ROWCOUNT);
 close empc;
end;
-----------------------------
//Implicit cursor (here cursor keyword is not used):
begin
 delete from dept where deptno=100;
 if sql%notfound then
  raise_application_error(-20303,'No such department in the dept table'); 
 end if;
end;
/
//Other commands used in implicit cursor==> sql%FOUND
==========================================

Table creation with constraints:
create table Doctors(
doctor_id number(6) primary key,
doctor_name varchar2(20) not null,
doct_address varchar2(35),
doct_ph_no number(10)
);

create table Test (
test_id number(4) primary key,
test_name varchar2(20),
doctor_id number(6),
constraint fk_doctor_id foreign key(doctor_id) references Doctors
);
==========================================

//Defining values
DEFINE P_SALARY=50000;
DEFINE P_BONUS=10;
DECLARE
RESULT NUMBER:=0;
BEGIN
RESULT:=(P_SALARY*P_BONUS/100)+P_SALARY;
DBMS_OUTPUT.PUT_LINE(RESULT);
END;
/
==========================================
//Stored Procedures:
CREATE OR REPLACE PROCEDURE Prof_Chng_Merchant_To_Silver(INPUT_MOBILENUMBER VARCHAR2)
AS
  Src_memberid   NUMBER(24);
  Src_account_id NUMBER(24);
  Src_programnameref VARCHAR(20);
  Check_programnameref VARCHAR(20);
BEGIN
  SELECT mac.memberid,acc.id INTO Src_memberid,Src_account_id FROM account acc, memberaccountrole mac
  WHERE acc.id = mac.accountid AND acc.devicenumber LIKE INPUT_MOBILENUMBER;

  SELECT Value INTO Check_programnameref FROM account_dtl WHERE code='OP_FR_PROG' AND account_id IN
  (SELECT id FROM account WHERE devicenumber=INPUT_MOBILENUMBER);

  IF Check_programnameref='MERCHANTSVA' then

    SELECT programnameref INTO Src_programnameref FROM account WHERE devicenumber LIKE INPUT_MOBILENUMBER;
    UPDATE ACCOUNT SET PROGRAMNAMEREF = Decode(Src_programnameref,'MBILL','MBILL','SILVER') 
 WHERE DEVICENUMBER LIKE INPUT_MOBILENUMBER;

    IF SQL%ROWCOUNT = 1 THEN     
      UPDATE account_dtl SET Value = 'SILVER' WHERE code LIKE'OP_FR_PROG' AND ACCOUNT_ID = Src_account_id;
      UPDATE bank_temp_upload_customer  SET PRODUCT = 'SILVER' WHERE ACCOUNT_ID = Src_account_id
        AND customer_id = (SELECT Max(customer_id) FROM bank_temp_upload_customer WHERE account_id = Src_account_id);
      Insert into ACCOUNT_LOCK_LOG (id, account_id, reason_code, lock_reason, createdt,
        createdby, modifydt, modifiedby, auth_user_id, locked_by_user_type, action_type)
      Values
        ((SELECT Max(id)+1 FROM ACCOUNT_LOCK_LOG), Src_account_id, 'NOTE', 
  'Coverted the Product type from MERCHANT to SILVER', sysdate, NULL, sysdate, 'PROCEDURE', NULL, NULL, 'NOTE');
    END IF;
  END IF;

  dbms_output.put_line ('No MerchantSVA customer found with this mobile number');

  EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO AUDIT_LOG (
      AUDIT_LOG_ID, TRANSACTION_ID, TRANSACTION_DATE,
      OBJECT_NAME, EVENT_CATEGORY, EVENT_TYPE, OBJECT_PK_VALUE,
      COLUMN_NAME, OLD_VALUE, NEW_VALUE,ADMIN_USER, EVENT_DATA)
    VALUES ( seq_audit_log_id.nextval, null, SYSDATE
      ,'Prof_Chng_Merchant_To_Silver','PLEXCEPTION', 'PROFILE',NULL
      ,'OTHERS' ,NULL, NULL, 'PROCEDURE', INPUT_MOBILENUMBER);

END Prof_Chng_Merchant_To_Silver;
/

//Way to execute
EXEC Prof_Chng_Merchant_To_Silver('919902029064');
COMMIT;
==========================================
create or replace procedure area(length in number,breadth in number)
is
v_area number;
begin
v_area:=length*breadth;
dbms_output.put_line('The area is '||v_area);
end area;
/
-----------------------------
CREATE or replace procedure proc1(p_id in number)
is
emp_record emp%rowtype;
begin
select * into emp_record from emp where empno =p_id;
dbms_output.put_line(emp_record.ename||'   '||emp_record.job);
end proc1;
/
-----------------------------
//Way to execute
begin
  area(6,6);
  proc1(80);
end;
/
OR
Execute area(4,5);
-----------------------------
//Calling another stored procedure
create or replace procedure up_fees(id number)
is
begin
update stud set fees=fees+500
where sl=id;
end up_fees;
/
-----------------------------
create or replace procedure proc7(pid number)
is
begin
up_fees(pid);
exception
when no_data_found then
dbms_output.put_line('No Such data in student table');
end proc7;
/
==========================================

//Function
CREATE or replace function func3(psal number)
return number
is
begin 
insert into emp (empno,ename,job,sal,deptno) values(3455,'Deepak','SSE',4500,20);
return (psal+500);
end func3;
/
-----------------------------
create or replace function day(num IN number)
return varchar2 
is
begin
  case num
           when 1 then return  'SUNDAY'
           when 2 then return  'MONDAY'
           when 3 then return 'TUESDAY'
           when 4 then return 'WENESDAY'
           when 5 then return 'THURSDAY'
           when 6 then return 'FRIDAY'
           when 7 then return 'SATURDAY'
         else return 'INVALID NUMBER'
  END;
 END day;
/
-----------------------------
create or replace function wish(name varchar2)
return varchar2
is
begin
if name='deepak' or name='kumar' or name='modi' then
 return 'Welcome to the creation of function in oracle';
else
return 'Not a valid user of scott';
end if;
end wish;
/
==========================================
declare
 pName bank_temp_upload_customer.product%type;
 PPID  bank_temp_upload_customer.PROGRAM_PROFILE_ID%type;
 custId  bank_temp_upload_customer.CUSTOMER_ID%type;
 custIdMax NUMBER(24);
 mobNumber bank_temp_upload_customer.mobile_number%TYPE; 
 cursor tempCursor is 
    select product,program_profile_id,mobile_number,customer_id from bank_temp_upload_customer 
    WHERE mobile_number IN ('919686680837','919686680836') AND
    pi_master_id=(SELECT pi_master_id FROM PI_MASTER WHERE pi_code='YBL') 
    ORDER BY mobile_number desc;
begin
 open tempCursor; 
 loop
  fetch tempCursor into pName,PPID,mobNumber,custId;
  SELECT Max(customer_id) INTO custIdMax FROM bank_temp_upload_customer WHERE mobile_number=mobNumber;
  IF custId=custIdMax then
     dbms_output.put_line('ProductName ==>'||pName||', Profile_ID==>'||PPID||', Max_Customer_Id==>'||custId||',
  Mobile_Number==>'||mobNumber);     
  UPDATE bank_temp_upload_customer SET PROGRAM_PROFILE_ID=(SELECT ID FROM program_profile WHERE 
  partner_code='YBL' AND PROGRAM_NAME =pName) 
      WHERE MOBILE_NUMBER=mobNumber AND customer_id=custIdMax; 
  END IF;
  exit when tempCursor%NOTFOUND;
 end loop;
 dbms_output.put_line('Task completed');
 close tempCursor;
end;

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

//Triggers in Oracle
Can be seen here too: http://deepakmodi2006.blogspot.com/2011/01/database-triggers-in-oracle.html
CREATE OR REPLACE TRIGGER SECURE_EMP
 BEFORE INSERT ON EMPLOYEES
 BEGIN
 IF
 (TO_CHAR(SYSDATE,'DY') IN  ('THU','FRI')) OR
 (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '17:00')
 THEN RAISE_APPLICATION_ERROR(-20500,'YOU MAY INSERT INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
 END IF;
 END;
/
------------------------------
CREATE OR REPLACE TRIGGER SECURE_EMP1
 BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES
 BEGIN
  IF
   (TO_CHAR(SYSDATE,'DY') IN  ('WED','SUN')) OR
   (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
  THEN 
IF DELETING THEN 
RAISE_APPLICATION_ERROR(-20501,'YOU MAY DELETE INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSIF INSERTING THEN 
RAISE_APPLICATION_ERROR(-20502,'YOU MAY INSERT INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSIF UPDATING ('DATEOFJOINING') THEN
RAISE_APPLICATION_ERROR(-20503,'YOU MAY UPDATE DOJ INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSE
RAISE_APPLICATION_ERROR(-20504,'YOU MAY DO TRANSACTION INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
 END IF;
END IF;
END;
/
------------------------------
create or replace trigger tri_emp
after insert or update or delete on employee
for each row
begin
  insert into audit_table values(user,sysdate,:old.empno,:old.ename,:new.ename,
  :old.job,:new.job,:old.sal,:new.sal);
end;
/
==========================================

//Package and Package body
//Package is like Interface in java, only declaration. Package body is having basically details of those.
//One full example to start with:
create or replace package summul as
  procedure pr1(a number,b number);
  function fr1(a number,b number)return number;
  c number;
end summul;

create or replace package body summul
 as
procedure pr1 (a number,b number) 
 is
 sumValue number(4);
 begin
  sumValue:=a+b;
  dbms_output.put_line(sumValue);
end pr1;

function fr1(a number,b number) return number is
mul number;
begin
 mul:=a*b;
 return (mul);
end fr1;
end summul;

//execute summul.pr1(10,20);
//select summul.fr1(10,20) from dual;
SQL> execute summul.pr1(10,20);
     PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> execute summul.pr1(10,20);
30
PL/SQL procedure successfully completed.
SQL> select summul.fr1(12,34) from dual;
SUMMUL.FR1(12,34)
-----------------
408
==========================================

create or replace package var_pack
is
function g_del return number;
procedure set_g_del (p_val in number);
end var_pack;
/
-------------------
create or replace package body var_pack is
gv_del number :=0;

function g_del return number is 
begin
 return gv_del;
end;

procedure set_g_del (p_val in number) is 
begin
if p_val=0 then
  gv_del := p_val;
else 
  gv_del :=gv_del +1;
end if;
end set_g_del;
end var_pack;
/
------------------------------
//Another package and package body
create or replace package dept_pack
 is 
 v_dept number;
 procedure dept_proc;
 end dept_pack;
/
-------------------
create or replace package body dept_pack
 is
 procedure dept_proc
  is
  v_name varchar2(20);
  v_loc varchar2(30);
 begin
  select dname,loc into v_name,v_loc from dept where deptno=v_dept;
  dbms_output.put_line(v_name||'  '||v_loc);
 end dept_proc;
 begin
  select deptno into v_dept from emp where ename='FORD';end dept_pack;
/
==================END=======================