Oracle Programming Advanced

Lets see a Simple procedure in this post.

Below is the code for a simple procedure which Queries the database and display the record when it is called.


Create or Replace Procedure P_GetEmpName 
As
V_Emp_Name varchar2 ;      -- Declaration Section
Begin                                     -- Execution Section

Select EmpName Into V_Emp_Name From Emp Where Emp_No = 12345;
Dbms_Output.Put_Line("The Employee Name is : " V_Emp_Name)
End;

The Above Procedure when called will give the following result.

Exec P_GetEmpName ;

Output :
The Employee Name is :John


Below is the code for a simple procedure which Queries the database and display the record when it is called.

Passing a Parameter to Procedure to Insert into a table

Create or Replace Procedure P_InsertRecord(P_Name varchar2)
As 
V_name varchar2(8) := P_Name;   -- Declaration Section
Begin                                              -- Execution Section
Insert into dept values(20,V_name,'f');
End;

The Above Procedure when called will give the following result.

Exec P_InsertRecord('John');

Select * From Dept ;

Output :
Dept No    Dept Name   Job Type
20                 John             f


Let us see about Dynamic Sql and execution of it in this post.

Create or Replace Procedure P_DynamicSql(P_Table_Name Varchar2) 
As

V_Table_Name Varchar2(10) := P_Table_Name ;          -- Declaration Section

V_Dynaic_Query Varchar2(50) ;
Begin  -- Execution Section

V_Dynaic_Query := 'Create Table ' || V_Table_Name || ' (Name Varchar2(50))'  ;

Execute Immediate  V_Dynaic_Query ;

Exception   -- Exception Section

When Others Then

Dbms_Output.Put_Line('Error Creating Table') ;

End;


When the above Procedure is called , Table with the Provided name will be create with a column called Name.


Exec P_DynamicSql('Emp') ;


The Table Gets Created