Oracle Learning (5)

 

/*
View the definition of the salary field in the structure of the HR. Employees table:
Desc HR. employees;
Press Ctrl + to click
*/

/*
The variables v_salary_num, v_salary_type, and v_salary_subtype are defined by number, % type, and sub-type respectively.
Assign the salary value in the first record of the HR. Employees table to the preceding three variables. And output the preceding three variables on the console.
*/
Declare
V_salary_num number (8, 2 );
V_salary_type HR. Employees. Salary % type;

Subtype salary_subtype is HR. Employees. Salary % type;
V_salary_sub salary_subtype;
Begin
Select salary into v_salary_sub
From HR. Employees
Where rownum <2;

V_salary_num: = v_salary_sub;
V_salary_type: = v_salary_sub;

Dbms_output.put_line ('v _ salary_sub = '| to_char (v_salary_sub, '192. 99 '));
Dbms_output.put_line ('v _ salary_num = '| v_salary_num );
Dbms_output.put_line ('v _ salary_type = '| v_salary_type );
End;

/*
Next to Exercise 1, use rowtype to define a new variable, v_employee, and assign the first record to v_employee.
Use if-else-end if and case to determine the salary values in the database, respectively,
If> 2000 is printed: salary> 2000 of [employee name.
If = 2000, the salary of [employee name] is 2000.
If <2000 is printed: salary of [employee name] <2000
*/
Declare
V_employee HR. Employees % rowtype;
V_salary HR. Employees. Salary % Type: = 4000;

Begin
Select * into v_employee
From HR. Employees
Where salary <v_salary
And rownum <2;


-- Select * from HR. Employees where salary <4000;

If v_employee.salary> v_salary then
Dbms_output.put_line ('[' | v_employee.first_name | v_employee.last_name | '] salary> 2000, =' | to_char (v_employee.salary ));
Elsif v_employee.salary = v_salary then
Dbms_output.put_line ('[' | v_employee.first_name | v_employee.last_name | '] salary = 2000 ');
Else
Dbms_output.put_line ('[' | v_employee.first_name | v_employee.last_name | '] salary <2000 ');
End if;

Case
When v_employee.salary> v_salary then
Dbms_output.put_line ('[' | v_employee.first_name | v_employee.last_name | '] salary> 2000, =' | to_char (v_employee.salary ));
When v_employee.salary = v_salary then
Dbms_output.put_line ('[' | v_employee.first_name | v_employee.last_name | '] salary> 2000, =' | to_char (v_employee.salary ));
Else
Dbms_output.put_line ('[' | v_employee.first_name | v_employee.last_name | '] salary> 2000, =' | to_char (v_employee.salary ));
End case;
End;

/*
Print a string of numbers from 10 to 1 using loop, while, and for respectively.
*/

-- Use loop and exit respectively
Declare
V_loopcount number (2): = 11;
Begin
Loop
V_loopcount: = v_loopcount-1;
If v_loopcount <1 then
Exit;
End if;
Dbms_output.put_line (v_loopcount );
End loop;
End;

-- Use loop and exit when respectively
Declare
V_loopcount number (2): = 11;
Begin
Loop
V_loopcount: = v_loopcount-1;

Exit when (v_loopcount <1 );
Dbms_output.put_line (v_loopcount );
End loop;
End;

-- Use the while
Declare
V_loopcount number (2): = 10;
Begin
While v_loopcount> 0 Loop
Dbms_output.put_line (v_loopcount );
V_loopcount: = v_loopcount-1;
End loop;
End;

-- Use
Declare
V_loopcount number (2): = 10;
Begin
For v_loopcount in reverse 1 .. 10 Loop
Dbms_output.put_line (v_loopcount );
End loop;
End;

-- Use
Declare
V_loopcount number (2): = 10;
Begin
For v_loopcount in 1 .. 10 Loop
Dbms_output.put_line (11-v_loopcount );
End loop;
End;