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

Oracle SQL Programming Basics

Lets learn the basic of Oracle Programming in this blog

Select statement to query a table
Select * from emp;
Select * From Dept ;
Here * denotes that every column of the table has been selected.
emp -> table_name

Select statement to query a table with a filter condition.
Select * From EMP Where Employee_name = 'Joe' ;
Here
1) We are selecting all the columns of table EMP
2) The Query has been filtered for Employee name 'Joe'

Describe columns name present in the table "Dept"
Desc Dept ;

Insert statement
Now , Let try to insert a new row with values like dept no = 10 , dept name = eee , job = e

Insert into Dept values(10,'eee','e');

The above Query will insert a row into the table Dept and it will be displayed by using the Select Statement.

Now , Let try to insert a new row with values like Emp no = 100 , Emp Name = "John", Designation = "Manager",
Manager id = 200 , Hired Date = 26-Aug-2010 , Salary = 25000 , Tax = 2000 , Dept no = 10


Insert into Emp values(100,'John','Manager',200,'26-Aug-2010',25000,2000,10) ;

Different table Join

Inner join

Select  A.Emp_name , B.Dept_name From Emp AS A 
Inner Join Dept AS B

On A.Dept_id = B.Dept_id;

Check out : Oracle Programming Advanced

Vlookup in Excel

Here is how we do Vlookup in Excel

Lets Say this is the Lookup table from where we want to get the Information

Name	Salary	Age
Robert	150000	25
Kevin	200000	30
John	100000	35
Smith	50000	26
Rose	75000	65
Shiela	250000	55

We want to get the Salary for following People

Name

Robert

Kevin

Leslie

The Vlookup formula will look like this

=VLOOKUP(G2,A1:C7,2,0)

Here Vlookup is the Function name

G2 - the column (Name) we are looking info about

A1:C7 - this is the range in excel where our lookup data is present

2 - to retrieve the second column information from Lookup table

0 - returns false if name doesn't exist

Result is

Name	Salary
Robert	150000
Kevin	200000
Leslie	#N/A