Oracle if .. else usage

OracleCondition branch usage

A. If... then

B. If... then... Else

C. If... then... elsif .... Else

Instance1

Question: When writing a process, you can enter an employee name if the employee's salary is lower2000To increase the employee's salary.10 % .
Create or replace procedureSp_pro6(SpnameVarchar2) is V_sal Emp_copy.sal % Type ;

Begin

SelectSalIntoV_salFromEmp_copyWhereEname=Spname;

IfV_sal<2000 then

UpdateEmp_copySetSal=Sal* 1.1 whereEname=Spname;

End if;

End;

Instance2

Question: Write a process, you can enter an employee name, if the employee'sNo subsidy0On the basis of the original100If the subsidy is0Set the subsidy200;

Create or replace procedureSp_pro6(SpnameVarchar2) is

V_comm emp_copy.comm% Type;

Begin

SelectCommIntoV_commFromEmp_copyWhereEname=Spname;

IfV_comm<> 0 then

UpdateEmp_copySetComm=Comm+ 100 WhereEname=Spname;

Else

UpdateEmp_copySetComm=Comm+ 200 WhereEname=Spname;

End if;

End;

Instance3

Multi-condition Branch
If -
Then -
Elsif -Then .

Question: When writing a process, you can enter an employee ID if the employee's position isPresidentJust

Increase his salary1000If the employee's position isManagerTo increase his salary.500, Others

Increase in employee salaries200.

Create or replace procedureSp_pro6(SpnoNumber) is

V_job emp_copy.job% Type;

Begin

SelectJobIntoV_jobFromEmp_copyWhereEmpno=Spno;

IfV_job= 'President'
Then

UpdateEmp_copySetSal=Sal+ 1000 whereEmpno=Spno;

ElsifV_job= 'Manager'
Then

UpdateEmp_copySetSal=Sal+ 500 whereEmpno=Spno;

Else

UpdateEmp_copySetSal=Sal+ 200 whereEmpno=Spno;

End if;

End;