Simple PL/SQL Procedure

create table emp (Acolumn number(8,2),Bcolumn varchar2(50))
create table emp_bkp as select * from emp
insert into emp (acolumn) values (1);
select * from emp_bkp
truncate table emp_bkp


There are two types of cursors
1. Implicit Cursor
2. Explicit Cursor

Implicit Cursor is used by Oracle Server to pass and execute SQL Statements.
SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.

Explicit Cursors are explicitly declared by programmer.

Boolean data type means the o/p will be TRUE or FALSE.

IF and Case statements
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSEIF-END IF

IF Syntax:
IF Condition THEN
Statements;
ELSEIF Condition THEN
Statements;
ELSE
Statements;
END IF;

Case Syntax:
CASE Column name or CASE
When ‘A’ then ‘AJ’
WHEN ‘B’ then ‘BD’
ELSE ‘NO SUCH VALUE’
END;

:variable_name is called as bind variable
/* Procedure 1 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE := 10;
begin
select Acolumn into v_evnt_ky from emp where Acolumn=1;
insert into emp_bkp (Acolumn) values (v_evnt_ky);
commit;
end;

/* Procedure 2 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
--select Acolumn into v_evnt_ky from emp where Acolumn=1;
insert into emp_bkp (Acolumn) values (v_bonus);
commit;
END IF;
end;

/* Procedure 3 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
DBMS_OUTPUT.PUT_LINE('This is my first PL/SQL Procedure');
commit;
END IF;
end;

/*Procedure for Informatica*/
create or replace procedure dummy_emp (
v_evnt_ky IN number,
v_bonus IN emp.acolumn%TYPE )
IS
begin
IF v_bonus = 10 THEN
insert into emp_bkp (Acolumn) values (v_evnt_ky);
commit;
END IF;
end dummy_emp;

/*Display mailing address*/
declare
v_name varchar2(50) :='Aj';
v_address varchar2(30) :='Veedhi';
v_state varchar2(50) :='SJC';
v_m_address varchar2(50);
begin
v_m_address:=v_name||chr(10)||v_state||chr(10)||v_address;
DBMS_OUTPUT.PUT_LINE(v_m_address);
END;
o/p:Aj
SJC
Veedhi

/*Qualify an Identifier: By using Block label Prefix*/

< < OUTER > >
declare
birthdate date;
begin
    declare
    birthdate date;
    Begin
    OUTER.birthdate:=to_date('03/01/2011','mm/dd/yyyy');
    END;
    DBMS_OUTPUT.PUT_LINE(birthdate);
    END;
   -- O/P=01-MAR-11

/* Raise the error Using raise application error message*/

declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
select Acolumn into v_evnt_ky from emp where Acolumn=2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('HISTORY');
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-'||SQLERRM);
end;

O/P:

ORA-20001: An error was encountered - 100 -ERROR- ORA-01403: no data found

ORA-06512: at line 14


/* Using Define in PL/SQL */
DEFINE P_NUM1=9;
DEFINE P_NUM2=3;
DECLARE
    V_NUM1 number :=&P_NUM1;
    V_NUM2 number :=&P_NUM2;
     V_RESULT number;
BEGIN
    V_RESULT := (V_NUM1/V_NUM2)+V_NUM2;
    /*Printing PL/SQL Variable*/
    DBMS_OUTPUT.PUT_LINE(V_RESULT);
    END;

 /*Using Merge function*/

DECLARE
   v_bonus   emp.acolumn%TYPE := 1;
BEGIN
MERGE INTO EMP_BKP BKP
USING EMP E
ON
(BKP.ACOLUMN=v_bonus)
WHEN MATCHED THEN
UPDATE
SET
--BKP.ACOLUMN = E.ACOLUMN,
BKP.BCOLUMN =E.BCOLUMN
WHEN NOT MATCHED THEN
INSERT VALUES(E.ACOLUMN,E.BCOLUMN);
   COMMIT;
END;