Explain Oracle Database PL/SQL statement blocks with one instance

PL/SQL block to display detailed information of the employee with the given employee number

1 SQL> Declare

2 v_empno emp. empno % Type;/* declare the variable v_empno, % type: Make the Type of the variable the same as the empno type in the emp table */

3 v_emprecord emp % Rowtype;/* declare the variable v_emprecord, % rowtype: Make the variable of the same type as the entire row in the emp table */

4 Begin

5 Select * Into v_emprecord From emp Where empno = & v_empno;

6 dbms_output.put_line (employee ID | v_emprecord.empno );

7 dbms_output.put_line (employee name | v_emprecord.ename );

8 dbms_output.put_line (onboarding date | v_emprecord.hiredate );

9 dbms_output.put_line (position | v_emprecord.job );

10 dbms_output.put_line (administrator ID | v_emprecord.mgr );

11 dbms_output.put_line (salary | v_emprecord.sal );

12 dbms_output.put_line (bonus | v_emprecord.comm );

13 dbms_output.put_line (Department ID | v_emprecord.deptno );

14 End;

If condition control statement

Syntax:

1 if condition then statement;

2 elsif condition then statement;

3 else statement;

4 end if;

5/* condition is a Boolean variable (values: true, false, or null. If the expression is true, then is executed); statement is a PL/SQL or SQL statement ;*/

Use if-then-elsif to perform the following salary increase ratio: (the percentage of salary increases is based on their existing salary)

Deptno Raise (% age)

10 5%

20 10%

30 15%

40 20%

1 SQL> Declare

2 v_empno emp. empno % Type;

3 v_emprecord emp % Rowtype;

4 v_sal emp. sal % Type;

5 Begin

6 Select * Into v_emprecord From emp Where empno = & v_empno;

7 If v_emprecord.empno = 10 Then v_sal: = v_emprecord.sal * 1.05;

8 Elsif v_emprecord.empno = 20 Then v_sal: = v_emprecord.sal * 1.10;

9 Elsif v_emprecord.empno = 30 Then v_sal: = v_emprecord.sal * 1.15;

10 Elsif v_emprecord.empno = 40 Then v_sal: = v_emprecord.sal * 1.20;

11 End If;

12 End;

For loop control statement Syntax:

 

1for counter in [reverse] lower_bower... upper_bound loop

2statement1;

3statement2;

4

5end loop;

6/* counter is an implicitly declared integer. Each cycle increases by 1 or decreases by 1. reverse reduces counter from the maximum value to the minimum value; lower_bound specifies the next upper_bound of the counter variable change range to specify the previous session of the counter variable change range */

Use the for loop control statement to add 10 new employee numbers to the "emp" table;

1 SQL> Declare

2 v_empno emp. empno % Type;

3 Begin

4 Select Max (empno) Into v_empno From emp;

5 For I In 1 .. 10 Loop

6 v_empno: = v_empno + 1;

7 Insert Into emp (empno) Values (v_empno );

8 End Loop;

9 End;