Oracle case, loop, while, for simple instances

Select * from employees EMP where EMP. Salary = 3000
If statement
Begin
If (10> 50) then
Dbms_output.put_line ('da Yu ');
Else
Dbms_output.put_line ('Bu Da Yu ');

End if;
End;
Select * from employees EMP where EMP. employee_id = 119
Where
EMP. department_id = 30 and
Salary <1, 250

Declare
V_sal number (10 );
V_empid number (4 );
Begin
V_empid: = & nid;
Select EMP. Salary into v_sal
From employees EMP
Where EMP. employee_id = v_empid;
If v_sal <= 2500 then
Update employees set salary = salary + 200 where employee_id = v_empid;
Elsif v_sal> 2500 and v_sal <3000 then
Update employees set salary = salary + 100 Where employee_id = v_empid;
Else
Dbms_output.put_line ('The salary raise range not exists ');
End if;
Exception
When no_data_found then
Dbms_output.put_line ('employee cannot be found! ');

End;

------------ Case comparison of Single-value equivalence ----------------------

Declare
STR number;
Begin
STR: = & STR;
Case Str
When 60 then
Dbms_output.put_line ('failed ');
When 70 then
Dbms_output.put_line ('excellent ');
When 80 then
Dbms_output.put_line ('excellent ');
Else
Dbms_output.put_line ('others ');
End case;
End;
------------ Comparison of case range conditions ----------------------
Declare
Num number (6, 2 );
Begin
Num: = & num;
Case
When num <60 then
Dbms_output.put_line ('failed ');
When num <80 then
Dbms_output.put_line ('excellent ');
When num <100 then
Dbms_output.put_line ('excellent ');
End case;
Exception
When case_not_found then
Dbms_output.put_line ('no case statement that meets the requirements: '| sqlerrm );
End;
-------------- Case expression ---------------------------
-- Used in the value assignment statement
Declare
Num number (5 );
Val varchar2 (50 );
Begin
Num: = & num;
VAL: = case num
When 1 then 'first group'
When 2 then 'second group'
When 3 then 'group 3'
End | 'is good ';
Dbms_output.put_line (VAL );
End;

-- Used in select statements
select * from employees where employee_id = 109
declare
STR varchar2 (200 );
begin
select case
when salary between 2000 and 3000 then
'General white-collars '
when salary between 6000 and 10000 then
' company gold collar '
else
'employee'
end KKK into STR from employees where employee_id = 109;
dbms_output.put_line (STR);
end;

select EMP. first_name, EMP. phone_number, Case
when EMP. salary between 2000 and 3000 then
'General white-collars '
when EMP. salary between 6000 and 10000 then
'Company credentials'
else
'employees'
end as employee type
from employees EMP
-------------------- goto null ---------------------------

Declare
Num number (5): = 10;
Begin
If num> 5 then
Goto label1;
Else
-- Dbms_output.put_line ('Nothing ');
NULL; -- do not do anything, its main purpose is to ensureProgramStructure Integrity.
End if;
Dbms_output.put_line ('Welcome to you! ');
<Label1>
Dbms_output.put_line ('greater than 5 ');

End;
-------------------- Loop ------------------------------------

/*
Features: loop operation at least once
What loop statements have in common is loop end loop.
*/
Create Table TMP
(
TID number (6) primary key,
Tname varchar2 (10)
)

Declare
I number (6): = 1;
Begin
Loop
Insert into TMP values (I, 'value' | I );
Exit when I! = 0; -- cyclic termination statement
I: = I + 1;
End loop;
Dbms_output.put_line ('data warehouse receiving completed ');
End;

Select * from TMP
-------------------- While ---------------------------------

Create Table TMP
(
TID number (6) primary key,
Tname varchar2 (10)
)
Delete from TMP;

Declare
I number (6): = 1;
Begin
While I <= 10
Loop
Insert into TMP values (I, 'value' | I );
I: = I + 1;
End loop;
Commit;
End;
--------------------- ----------------------------------

/*
The number of cycles is determined.
*/
Declare
I number (5 );
J number (5 );
Begin
For I in reverse 1 .. 10
Loop
For J in 1. I
Loop
Dbms_output.put ('*');
End loop;
Dbms_output.put_line ('');
End loop;
End;
---------------------- Pre-defined exception -------------------------------

No need to define, no need to manually throw
Select *
From employees EMP
Where EMP. employee_id = 1
Declare
Sal char (1 );
E_integrity exception;
Pragma prediction_init (e_integrity,-01422 );
Begin
Select EMP. Salary into Sal
From employees EMP
Where EMP. employee_id <10000;
Exception
When e_integrity then
Dbms_output.put_line ('Too many values: '| sqlerrm );
When no_data_found then
Dbms_output.put_line ('no value: '| sqlerrm );
-- When others then
-- Dbms_output.put_line ('value assignment error '| sqlerrm );
End;
---------------------- Non-predefined ------------------------
Declare
E_integrity exception;
Pragma prediction_init (e_integrity,-2291 );
Begin
Update employees set employees. department_id = 10
Where employees. employee_id = 101;
Exception
When e_integrity then
Dbms_output.put_line ('this department does not exist! '| Sqlerrm );
When others then
Dbms_output.put_line ('this department does not exist --! '| Sqlcode | ''| sqlerrm );
End;

Select * from orders ments

-------------------- Custom ---------

-- Manually define and throw
Select * from employees where employees. employee_id = 1111
Declare
Ex exception;
Begin
Update employees set employees. Salary = 10000
Where employees. employee_id = 1111;
If SQL % notfound then
Raise ex; -- Note: a custom exception must be thrown manually.
End if;
Exception
When ex then

Dbms_output.put_line ('the data is not found: '| sqlerrm );
-- Throw a custom exception
-- Raise_application_error (-20001, 'this employee does not exist! ');
When others then
Dbms_output.put_line ('value assignment error ');
End;


Begin
Raise_application_error (-20001, 'this employee does not exist! ');
End;

Create or replace procedure pro_test_exep
As
Ex exception;
Begin
Update employees set employees. Salary = 10000
Where employees. employee_id = 1111;
If SQL % notfound then
Raise ex;
End if;
Exception
When ex then

Dbms_output.put_line ('the data is not found: '| sqlerrm );
-- Throw a custom exception
Raise_application_error (-20001, 'this employee does not exist! ');
When others then
Dbms_output.put_line ('value assignment error ');
End;


Declare
E_integrity exception;
Pragma prediction_init (e_integrity,-20001 );
Begin
Pro_test_exep;
Exception
When e_integrity then
Dbms_output.put_line ('this employee does not exist ');
End;