---1 simple if statement
DECLARE
v_no NUMBER := &v;
BEGIN
IF v_no >= 10 THEN
dbms_output.put_line('the number you enterd is >=10');
END IF;
END;
-----------------------------------
---2 simple if statement with else
DECLARE
v_no NUMBER := &v;
BEGIN
IF v_no >=10 THEN
dbms_output.put_line('the number you enterd is >=10');
ELSE
dbms_output.put_line('number is is less than 10');
END IF;
END;
-------------------------------------
--if /elsif
--This logic need enhancement
--Try to enter negative or value >100
DECLARE
v_grade NUMBER := &v;
BEGIN
IF v_grade between 90 and 100 THEN
dbms_output.put_line('the grade is A');
ELSIF v_grade between 80 and 89 THEN
dbms_output.put_line('the grade is B');
ELSIF v_grade between 70 and 79 THEN
dbms_output.put_line('the grade is C');
ELSIF v_grade between 60 and 69 THEN
dbms_output.put_line('the grade is D');
ELSE
dbms_output.put_line('the grade is F');
END IF;
END;
---------------------------------
DECLARE
v_grade NUMBER := &v;
BEGIN
IF v_grade BETWEEN 0 AND 100 THEN
IF v_grade between 90 and 100 THEN
dbms_output.put_line('the grade is A');
ELSIF v_grade between 80 and 89 THEN
dbms_output.put_line('the grade is B');
ELSIF v_grade between 70 and 79 THEN
dbms_output.put_line('the grade is C');
ELSIF v_grade between 60 and 69 THEN
dbms_output.put_line('the grade is D');
ELSE
dbms_output.put_line('the grade is F');
END IF;
ELSE
dbms_output.put_line('the grade should be number between 0 and 100');
END IF;
END;
------------------
/* Write a block the take the employee id as substitute variable
Output message: 'yes, the salary is >=15000' if his salary >=15000
Output message: 'No, the salary is <15000' if his salary <15000
*/
declare
v_sal employees.salary%type;
begin
select salary into v_sal
from employees
where employee_id=&emp_id;
if v_sal>=15000 then
dbms_output.put_line('yes, the salary is >=15000');
else
dbms_output.put_line('No, the salary is <15000');
end if;
end;
-----------------------------------------------------------------------------------------------------------
declare
x number:=5;
y number;
begin
if x<>y then
dbms_output.put_line('welcome');
else
dbms_output.put_line('Operator with null value always =null');
end if;
end;
---solution
declare
x number:=5;
y number;
begin
if nvl(x,0)<>nvl(y,0) then
dbms_output.put_line('welcome');
else
dbms_output.put_line('Operator with null value always =null');
end if;
end;
-------------------------
declare
x number;
y number;
begin
if x=y then
dbms_output.put_line('welcome');
else
dbms_output.put_line('Operator with null value always =null');
end if;
end;
--solution
declare
x number;
y number;
begin
if x is null and y is null then
dbms_output.put_line('welcome');
else
dbms_output.put_line('Operator with null value always =null');
end if;
end;
-------------------------------------------------------------------------------------------------
select employee_id,first_name,salary,department_id, case department_id
when 90 then salary*1.1
when 60 then salary*1.2
when 100 then salary*1.3
else salary
END new_sal
from
employees;
------------------------------------
select employee_id,first_name,salary,department_id, case
when department_id=90 then salary*1.1
when department_id=60 then salary*1.2
when department_id=100 then salary*1.3
else salary
END new_sal
from
employees
--------------------------------------
--here we use the case as experssion
DECLARE
v_sal number;
v_desc varchar2(100);
BEGIN
select salary into v_sal
from employees
where employee_id=&emp_id;
v_desc:=case
when v_sal is null then 'no salay for the employee'
when v_sal between 1000 and 3000 then 'salay is low'
when v_sal between 3001 and 5000 then 'salay is medium'
when v_sal between 5001 and 10000 then 'salay is good'
else 'salay is High'
end ; --here end not end case
dbms_output.put_line(v_desc);
END;
------------------------
--here we use the case as statement
DECLARE
v_sal number;
v_desc varchar2(100);
BEGIN
select salary into v_sal
from employees
where employee_id=&emp_id;
case
when v_sal is null then
dbms_output.put_line('no salay for the employee');
when v_sal between 1000 and 3000 then
dbms_output.put_line('salay is low');
when v_sal between 3001 and 5000 then
dbms_output.put_line('salay is medium');
when v_sal between 5001 and 10000 then
dbms_output.put_line('salay is good');
else
dbms_output.put_line('salay is High');
end case; --here end case
END;
-------------------------------------------------------------------------------------------------------------------------------
--basic loop
--write a basic loop to print welcome 3 times
declare
v_counter number:=0;
begin
loop
v_counter:=v_counter+1;
dbms_output.put_line('welcome ' ||v_counter);
exit when v_counter=3;
end loop;
end;
--------------------------------------
--another method
declare
v_counter number:=0;
begin
loop
v_counter:=v_counter+1;
dbms_output.put_line('welcome ' ||v_counter);
if v_counter=3 then
exit;
end if;
end loop;
end;
-------------------------------------------
--print the employees first name for employee 100,101,102
--using basic loop
declare
v_empno number:=100;
v_first_name employees.first_name%type ;
begin
loop
exit when v_empno>102;
select first_name into v_first_name
from employees
where employee_id = v_empno;
dbms_output.put_line(v_empno ||' '|| v_first_name);
v_empno:=v_empno+1;
end loop;
end;
--------------------------------------------------------------------------------------------------------------
declare
v_counter number:=1;
begin
while v_counter<=3
loop
dbms_output.put_line('welcome');
v_counter:=v_counter+1;
end loop;
end;
--------
--print the employees first name for employee 100,101,102
--using while loop
declare
v_empno number:=100;
v_first_name employees.first_name%type ;
begin
while v_empno<=102
loop
select first_name into v_first_name
from employees
where employee_id = v_empno;
dbms_output.put_line(v_empno ||' '|| v_first_name);
v_empno:=v_empno+1;
end loop;
end;
---------------------------------------------------------------------------------------------
begin
for i in 1..3
loop
dbms_output.put_line('welcome '||i);
end loop;
end;
------------------------
begin
for i in 1..1
loop
dbms_output.put_line('welcome '||i);
end loop;
end;
--------------------
begin
for i in 3..5
loop
dbms_output.put_line('welcome '||i);
end loop;
end;
----------------
begin
for i in reverse 1..3
loop
dbms_output.put_line('welcome '||i);
end loop;
end;
-------------------------
begin
for i in 1..9/2
loop
dbms_output.put_line('welcome '||i);
end loop;
end;
------------------------------------------------
declare
v_name varchar2(200);
begin
for i in 100..102
loop
select first_name||' '||last_name
into v_name
from
employees
where employee_id=i;
dbms_output.put_line(i||':'||v_name);
end loop;
end;
/*
try to do print this
*
**
***
****
*****
*/
---------------------------------------------------------------------------------------------------------------
/*
try to do print this
*
**
***
****
*****
*/
declare
v_star varchar2(100);
begin
for i in 1..5
loop
for j in 1..i
loop
v_star:=v_star||'*';
end loop;
dbms_output.put_line(v_star);
v_star:=null;
end loop;
end;
--------------------------------
declare
v_star varchar2(100);
begin
<<outer_loop>>
for i in 1..5
loop
<<inner_loop>>
for j in 1..i
loop
v_star:=v_star||'*';
end loop inner_loop;
dbms_output.put_line(v_star);
v_star:=null;
end loop outer_loop;
end;
-----------------------------------------
declare
v_star varchar2(100);
begin
<<outer_loop>>
for i in 1..5
loop
<<inner_loop>>
for j in 1..i
loop
v_star:=v_star||'*';
exit;
end loop inner_loop;
dbms_output.put_line(v_star);
v_star:=null;
end loop outer_loop;
end;
-------------------
declare
v_star varchar2(100);
begin
<<outer_loop>>
for i in 1..5
loop
<<inner_loop>>
for j in 1..i
loop
v_star:=v_star||'*';
exit outer_loop when i=3 ;
end loop inner_loop;
dbms_output.put_line(v_star);
v_star:=null;
end loop outer_loop;
end;
---------------------------------------------------------------------------------------------------
begin
for i in 1..10
loop
dbms_output.put_line (i);
end loop;
end;
--i want to print under the number the Symbol :) only for 1,2,3,4,5
--there are many methods
--method 1
declare
v_sym varchar2(100);
begin
for i in 1..10
loop
if i between 1 and 5 then
v_sym:=i||chr(10)||':)';
else
v_sym:=i;
end if;
dbms_output.put_line (v_sym);
end loop;
end;
---------------------------------------
--method 2
begin
for i in 1..10
loop
dbms_output.put_line (i);
continue when i>5; --this mean stop execte next statement(s) when i>5
dbms_output.put_line (':)');
end loop;
end;
No comments:
Post a Comment