Function Example in PL/SQL Oracle

It is a sample pl/sql oracle example which have a function cube1 and a program. When a function calls accepted number(no) from user is passed as a argument and output of cube1 function is return value to variable(res).

Aim - PL/SQL program to accept a number from user and display its cube(num^3) value using function.

Function:-
First Execute Function PL/SQL block

create or replace function cube1(no in number) return number IS result number(8);
begin
  result:=(no*no*no);
  return result;
exception
  when no_data_found then
  return 'error';
end;

Program: -
Second Execute PL/SQL Program

declare
 no number(5);
 res number(10);
begin
  no:=&no;
  res:=cube1(no);
  dbms_output.put_line('Cube of ' || no || ' is '|| res);
end;

Output: -
Enter value for no: 6
old 5: no:=&no;
new 5: no:=6;
Cube of 6 is 216

Exception Handling Program in PL/SQL Oracle

It is a sample PL/SQL Oracle exception handling program to handle errors. It is used for error tolerance and error avoidance.
In Pl/SQL exception is used to handle run time errors. When a run time error occurs then it moves to exception handler block in PL/SQL program.

Aim -PL/SQL program to perform exception handling.

Program: -

declare
  empn emp.empno%type;
  row_type1 emp%rowtype;
begin
  empn:=&empn;
  select * into row_type1 from emp where empno=empn;
  dbms_output.put_line('Name: '||row_type1.ename);
  exception when NO_DATA_FOUND then
  dbms_output.put_line('No such employee');
end;

Output: -

Enter value for empn: 7566
old 5: empn:=&empn;
new 5: empn:=7566;
Name: JONES

Enter value for empn: 1001 /* Such employee number(empno) is not in emp table */
old 5: empn:=&empn;
new 5: empn:=1001;
No such employee

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