Oracle (vi) PL/SQL, loops, and cursors

-- view: view (virtual table), not occupying physical space!
--  if there is no permission --
create or replace   view  teacher_view
as
select tname,sal from teacher

--   Query view
select * from teacher_view

=======pl/sql=======*************

--PL/SQL (procedural Language), procedural-language! Added logical judgment on the original SQL statement
--Cycle and other operations! When you execute a PL/SQL program on a database, the PL/SQL statements are parsed and executed separately!

--Syntax structure: = is an assignment statement
--01.declare Optional Partial declaration: variable, exception, cursor ...

--02.begin necessary part writing: SQL statements and PL/SQL statements

--03.exception optional section for handling when an exception occurs

--04.end Necessary Parts


--Query salary According to the assigned teacher's number output a result
Declare
V_name Teacher.tname%Type
V_sal Teacher.sal%Type
V_resultVARCHAR2 (30);
Begin
Select T.tname,t.salInto V_name,v_salFrom teacher Twhere TNO=1002;
--Get different return results based on salary
If V_sal>=2000and V_sal<=10000Then
V_result:=‘You alright‘;
elsif v_sal>10000and V_sal<=40000Then
V_result:=‘Really can‘;
elsif v_sal>40000Then
V_result:=‘Beef Cattle and beef cattle‘;
Else
V_result:=‘Baa Baa‘;
EndIf;
Dbms_output.put_line (v_name||‘:‘|| V_sal||‘=====‘||V_result);

--Output different results based on the value of V_result
CaseV_result
When‘You alright‘Then
Dbms_output.put_line (v_name||‘Salary at 2k to 10k‘);
When‘Really can‘Then
Dbms_output.put_line (v_name||‘Salary at 10k to 40k‘);
When‘Beef Cattle and beef cattle‘Then
         Dbms_output.put_line (v_name< Span style= "color: #808080;" >|| ' wages above 40k ");
        else
             Dbms_output.put_line (v_name|| ' salary 2k below ");
      end case; 
    
end;

======= Cycle =======*************

--Loop structure
--01.loop syntax
Loop
Execution of the statement;
ExitWhenConditions
EndLoop


Declare
INumber;
Begin
I=1;
Loop
Dbms_output.put_line (i);
I=i+1;
ExitWhen I>50;
EndLoop
End;

--02.while syntax
WhileConditional loop
Execution of the statement;
EndLoop

Declare
INumber;
Begin
I=1;
While I<=50Loop
Dbms_output.put_line (i);
I=i+1;
EndLoop
End;


--03.for syntax

for variable in  range (upper, lower) loop
  executed statement;
end loop;

declare
I begin
for i in 1. 50
   Loop
    Dbms_output.put_line (i);
   end loop;
end;

======= Cursor =======*************

--Cursors: Is the system to open a data buffer for our users! The results of the SQL statement execution are stored!
--Each cursor area has a name, and the user gets the record one line at a cursor!

--01. Implicit cursors are created automatically when executing SQL statements in our PL/SQL statement! ====>sql
--02. Display cursors can return multiple rows of queries!
--03.REF cursors are used to process dynamic SQL query results that run-time can determine

--Implicitly-cursor Properties
--01.sql%found affects one or more rows return True
--02.sql%notfound does not affect the number of rows returned true
--03.sql%rowcount the number of rows affected by the SQL statement
--04.sql%isopen whether the cursor is open, always false
Declare
Tname Teacher.tname%type--Declaring a variable
Cursor Teacher_cursorIsSelect Tnamefrom teacher;--declaring cursors
Begin
Open teacher_cursor;--Open cursor
Fetch Teacher_cursorIntoTname;
--Cyclic output Tname
While teacher_cursor%found
Loop
Dbms_output.put_line (' Teacher's name = =' || tname);
fetch teacher_cursor into tname;
end Loop;
Close teacher_cursor; -- close cursor
end;

Oracle (vi) PL/SQL, loops, and cursors