PL/SQL Procedure Sample Example

It is a sample PL/SQL Oracle example using procedure.
When create a procedure IN and OUT keyword used during pass parameters. IN is used to pass values to the procedure and OUT is used to return values to the caller.

Aim - PL/SQL Oracle program to add two numbers using procedure.

Procedure: -
create or replace procedure add_proc(n1 IN number,n2 IN number, tot OUT number)as
begin
tot:=n1+n2;
end;

Sample Program Code: -

declare
 num1 number(3);
 num2 number(3);
 result number(4);
begin
 num1:=&num1;
 num2:=&num2;
 add_proc(num1,num2,result);
 dbms_output.put_line('Addition is: '||result);
end;

First execute only the PL/SQL procedure part then fire program. In this program first execute the add_proc procedure than execute the PL/SQL program.


Output: -
When Execute Procedure then we get output like:
Procedure created.


When Execute Program then:
Enter value for num1:
old 6: num1:=&num1;
new 6: num1:=23;
Enter value for num2: 34
old 7: num2:=&num2;
new 7: num2:=34;
Addition is: 57

Check Number is Armstrong in PL/SQL Programming

It is a sample pl/sql program to take input a number from user and it checks accepted number is Armstrong or not.

Aim - PL/SQL program to check whether number is Armstrong or not.

Program:-

declare
  pnum number(5);
  tot number(5);
  lp number(3);
  tmp number(5);
begin
  pnum:=&pnum;
  tmp:=pnum;
  tot:=0;
  while tmp>0
  loop
    lp:=tmp mod 10;
    tot:= tot + (lp*lp*lp);
    tmp:=floor(tmp/10);
  end loop;
  if(tot like pnum) then
    dbms_output.put_line(pnum||' is armstrong.');
  else
    dbms_output.put_line(pnum||' is not armstrong.');
  end if;
end;

Output: -
Enter value for pnum: 153
old 7: pnum:=&pnum;
new 7: pnum:=153;
153 is armstrong.

PLSQL Example Find Even Odd Number

It is a sample plsql example code to find accepted number is even or odd and print output with friendly message.

Even or Odd Number
Even number is a number which is completely divided by 2 and reminder is 0 like 2, 4, 6, 8, 10, ..
Odd number is a number which is completely not divided by 2 and reminder is not 0 like 1, 3, 5, 7, 9, ..

Aim - PLSQL Example to find accepted number is even or odd

Program Code : -

declare
  num number(5);
begin
  num:=#
  if num mod 2 =0 then
    dbms_output.put_line('Accepted Number '||num||' is even');
  else
    dbms_output.put_line('Accepted Number '||num||' is odd');
  end if;
end;

Output of PLSQL Block:-
Enter value for num: 10
old 5: num:=&num
new 5: num:=10;
Accepted Number 10 is even