1. PlSql programming procedure language

In the sql statement of database, the common definition of variable condition judgment cycle structure is added to the statement of process
1) . basic grammatical structure
declare
– declaration part
– similar to defining variables
begin
– process logic code
end;
2) . attribute declaration method
%Type: declare the same type of variable and field in the table
%rowtype: declare that the variable is consistent with a row of data type in the table

1,PlSql programming procedure language Process language in database sql Statement
    //Common definition of variable condition judgment loop structure for statements added to process
  1.Basic grammatical structure
    declare
       --Declaration part
       --Part similar to defining variables
    begin
       --Processing logic code
    end;
  2.Property declaration method  
    %type : Declare variables of the same type as fields in the table

    %rowtype :  Declaration variable is consistent with a row of data type in the table
*/

declare
    v1 number(2) :=1; --1)Declare variables and assign values
    v2 number(10);    --2)Declare variables

    v3  emp.ename%type ; --3)Declare variable, type is field type in the table

    v4_row empCrowtype;  --4)Store a record in a table


begin

    dbms_output.put_line('v1=='||v1);  --1)Printing v1 Sentence
    select sal into v2 from emp where empno = 7369; --2)Query and assign 
    dbms_output.put_line('v2=='||v2); --Printing v2 Sentence

    select ename into v3 from emp where empno =7369; --3)Query and assign
    dbms_output.put_line('v3=='||v3); 


    select * into v4_row  from emp where empno = 7369;    --4)Query a record and assign a value
    dbms_output.put_line('v4_row---ename == '||v4_row.ename||'----eno=='||v4_row.empno);



end;

select * from emp

2. Condition judgment of oracle

if condition then
Processing statement
elsif condition 2 then
Processing statement
else
Default handling
end if; – end of if expression

/*
  2,oracle Condition judgment of
  if  condition then
     Processing statement
  elsif condition2 then   
     Processing statement
  else
     Default handling
  end if;  --ifEnd of expression
*/

--Requirement: query7499Output Chinese character prompt for salary judgment size of employees


declare 
sals number(6,2);

begin
  select sal into sals from emp where empno = 7499; 

  if sals>3000 then                        
    dbms_output.put_line('Pay more than 3000---'||sals);
  elsif sals < 1000 then
    dbms_output.put_line('Salary less than 1000---'||sals);
  else
      dbms_output.put_line('Wages range from 1000 to 3000---'||sals); 

  end if;
end;

3. The cyclic structure of oracle

a---—
loop
exit when condition
end loop;
b---—
while condition loop
end loop;
c---—
for variable in range loop
end loop;
.----

/*
  3,oracle Cycle structure of
---------------------
     loop
       exit when condition
     end loop;
---------------------
     while condition loop

     end loop;
---------------------
     for variable in Range loop 

     end loop;
------------------------
*/
--Use cyclic structure output1---10Figures

--1)loop  exit when condition  end loop;

declare
 v1 number(2) := 1;
begin

--Start cycle 
loop  
  dbms_output.put_line(v1);

exit when v1 >= 10;
v1 := v1+1;

end loop;
--End cycle

end;
2) while condition loop  end loop;


declare

v1 number(2) :=1;
begin

while v1<=10 loop
  dbms_output.put_line(v1);
  v1 := v1+1;
  end loop;


end;


3) for variable in Range loop end loop 

declare

begin

for v1 in 1..10 loop
  dbms_output.put_line(v1);
  end loop;

end;

4. Cursors are equivalent to collections

1) concept.
The database uses cursors to implement cursors to receive the collection of record results from queries

2) . cursor usage steps
① Declare cursor: cursor cursor name is select statement
② Open cursor: open game name
③ Fetch cursor: fetch cursor name into record variable
④ Close cursor: close nickname
3) . properties of cursor
%found
%notfound

/*
  4,Cursors are equivalent to collections
  1).concept
  The database uses cursors to implement cursors to receive the collection of record results from queries

  2).Cursor usage steps
  ①Declaration cursor: cursor is select Sentence
  ②Open cursor: open game name
  ③Fetch cursor: fetch game name into Record variables
  ④Close cursor: close nickname
  3).Properties of cursor
     %found
     %notfound

*/

--loop exit when ... end loop;

declare

cursor myCursor is select * from emp;  --1)Declare cursor and assign
rowdata emp%rowtype;
begin

   open myCursor; --2Open cursor

   loop 
     fetch myCursor into rowdata;  --3Extract line information
     exit when myCursor%notfound;   --4No information to terminate extraction
     dbms_output.put_line('number--'||rowdata.empno||'---Full name--'||rowdata.ename);

   end loop;  


   close myCursor;  --5Close cursor     


end;

--while ... loop  end loop  Cursor is in header by default, no data


declare 
cursor mycursor is select * from emp;  --1)Declare cursor and assign
rowdata emp%rowtype;

begin

  --open mycursor; --open
     fetch mycursor into rowdata; --extract 
       loop  fetch mycursor into rowdata; --extract 
       dbms_output.put_line('number--'||rowdata.empno||'---Full name--'||rowdata.ename);
    end loop;

    close mycursor;

end;

--for .. in a...b loop end loop;

declare 
cursor mycursor1 is select * from emp;  --1)Declare cursor and assign

begin

  open mycursor1; --open
       for rowdata in mycursor1

       loop
         dbms_output.put_line('number--'||rowdata.empno||'---Full name--'||rowdata.ename);  
       end loop;   

  close mycursor1; --Close
end;