Oracle_pl/sql (2) Process Control

0. Retrieving single-line data
0.1 using scalar variables to accept data
Example 1:7788
declare
v_ename emp.ename%type;
V_sal Emp.sal%type;
Begin
Select Ename,sal into V_ename,v_sal from EMP where empno=&no;
Dbms_output.put_line (' Clerk number: ' | | &no| | ' staff name: ' | | V_ename);
End;

0.2 Embedded SELECT statement Note:
When using the SELECT INTO statement, you must return a single piece of data and return only one data
No_date_found:
Select INTO does not return data
Too_many_rows:
Select INTO returns multiple Data
Example 2:
declare
v_ename emp.ename%type;
V_sal Emp.sal%type;
Begin
Select Ename,sal into V_ename,v_sal from EMP where Deptno=&deptno;
Dbms_output.put_line (' Department number: ' | | &deptno| | ' staff name: ' | | V_ename);
End;
WHERE clause use CAUTION:
The variable name used cannot be the same as the column name, otherwise the too_many_rows exception is triggered.
Example 3:
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
Empno Emp.empno%type;
Begin
Empno:=&no;
Select Ename,sal into V_ename,v_sal from EMP where empno=empno;
Dbms_output.put_line (' Clerk number: ' | | &no| | ' staff name: ' | | V_ename);
End;

PL/SQL control Structure
1. Execute statements sequentially
declare
v_sal number;
V_avg number;
Begin
Select AVG (SAL) to v_avg from EMP;
Dbms_output.put_line (' average wage: ' | | V_AVG);
Select Sal into V_sal from EMP where empno=7788;
Dbms_output.put_line (' Staff number 7788 Salary: ' | | V_sal);
End;

2. Conditional Branching Statement
2.1 Simple condition judgment
Syntax 1:
If logical expression 1 then
Statement 1;
End If;
Example 1:
Declare
v_sal number;
V_avg number;
Begin
Select AVG (SAL) into the v_avg from EMP;
Select Sal to v_sal from EMP where empno=7788;
If V_sal>v_avg Then
Dbms_output.put_line (' Employee number 7788 Payroll ' | | v_sal| | ' Greater than average wage ' | | V_AVG);
End If;
End;
Example 2: Receive parameters
Declare
v_sal number (6,2);
Begin
Select Sal into V_sal from EMP where lower (ename) =lower (' &name ');
Dbms_output.put_line (' The Employee name entered is: ' | | ' &name ');
If v_sal<2000 then
Update emp set sal=v_sal+200 where lower (ename) =lower (' &name ');
dbms_output.put_l INE (' to employees: ' | | ' &name ' | | ' Wages increased by 200. ');
End If;
End;

2.2 Binary Conditional branching
Syntax 2:
If logical expression 1 then
Statement 1;
else
Statement 2;
End If;
Example 1:
Declare
v_sal number;
V_avg number;
Begin
Select AVG (SAL) into the v_avg from EMP;
Select Sal to v_sal from EMP where empno=7788;
If V_sal>v_avg Then
Dbms_output.put_line (' Employee number 7788 Payroll ' | | V_sal
| | ' Greater than average wage ' | | V_AVG);
Else
Dbms_output.put_line (' Payroll for Staff number 7788 ' | | V_sal
| | ' Less than or equal to average salary ' | | V_AVG);
End If;
End;
Example 2: Receive parameter 7788
Declare
V_comm number (6,2);
Begin
Select Comm into V_comm from EMP where empno=&no;
Dbms_output.put_line (' entered employee number is: ' | | ' &no ');
If V_comm is null then
Update emp set comm=200 where Empno=&no;
Dbms_output.put_line (' Give employee number: ' | | ' &no ' | | ' 200 bonus. ');
Else
Update emp set comm=comm+100 where Empno=&no;
Dbms_output.put_line (' Give employee number: ' | | ' &no ' | | ' Bonuses increased by 100. ');
End If;
End;

2.3 Multi-conditional branching
Syntax 3:
If logical expression 1 then
Statement 1;
elsif logical Expression 2 then
Statement 2
...
Else
Statement 3;
End If;
Example 1:
Declare
v_sal number;
V_avg number;
Begin
Select AVG (SAL) into the v_avg from EMP;
Select Sal to v_sal from EMP where empno=7788;
If V_sal>v_avg Then
Dbms_output.put_line (' Employee number 7788 Payroll ' | | V_sal
| | ' Greater than average wage ' | | V_AVG);
Elsif V_sal<v_avg then
Dbms_output.put_line (' Payroll for Staff number 7788 ' | | V_sal
| | ' Less than average wage ' | | V_AVG);
Else
Dbms_output.put_line (' Payroll for Staff number 7788 ' | | V_sal
| | ' equals the average wage ' | | V_AVG);
End If;
End;

Example 2: Receive parameter 7788
declare
v_empno number;
V_job emp.job%type;
Begin
V_empno:=&no;
Select lower (Job) into the v_job from EMP where empno=v_empno;
Dbms_output.put_line (' Input The employee number is: ' | | ' &no ');
If v_job= ' manager ' then
Update emp set COMM=NVL (comm,0) +200 where empno=v_empno;
Dbms_output.put_line (' by employee Position ' | | v_job| | ' The employee number is: '
| | v_empno| | ' Bonuses increased by 200. ');
Elsif v_job= ' analyst ' then
Update emp set COMM=NVL (comm,0) +100 where empno=v_empno;
Dbms_output.put_line (' by According to the position of employees ' | | v_job| | ' The employee number is: '
| | v_empno| | ' Bonuses increased by 100. ');
Else
Update emp set COMM=NVL (comm,0) +50 where empno=v_empno;
Dbms_output.put_line (' Jobs by Employee ' | | v_job| | ' The employee number is: '
| | v_empno| | ' Bonuses increased by 50. ');
End If;
End;

2.4 Case statement:
Using a single selector for equivalent comparisons in case statements
Syntax 1:
Case expression
When expression 1 then statement 1; --EXECUTE Statement 1 when expression is equal to expression 1
When expression 2 then statement 2; --Ibid.
...
Else
Default statement; --Executes the default statement when the expression is not equal to each of the above expressions
End case;
Example 1:
Declare
V_deptno Emp.deptno%type;
Begin
v_deptno:=&no;
Dbms_output.put_line (' input department number is: ' | | V_DEPTNO);
Case V_deptno
When Ten Then
Update emp set comm=100 where Deptno=v_deptno;
Dbms_output.put_line (' number to Department ' | | v_deptno| | ' The staff is issued 100 Yuan bonus ');
When
Update emp set comm=80 where Deptno=v_deptno;
Dbms_output.put_line (' number to Department ' | | v_deptno| | ' The staff is issued 80 Yuan bonus ');
When
Update emp set comm=50 where Deptno=v_deptno;
Dbms_output.put_line (' number to Department ' | | v_deptno| | ' The staff is issued 50 Yuan bonus ');
Else
Dbms_output.put_line (' Department number does not exist ');
End case;
End

2.5 Using multiple criteria comparisons in case statements
Syntax 2:
Case
When logical expression 1 then statement 1; ---EXECUTE statement 1 when logical expression 1 is true
When logical expression 2 then statement 2; --Ibid.
...
Else
Default statement; --Executes the default statement when none of the above logical expressions are true
End case;

Example 2:
Declare
V_empno Emp.empno%type;
V_sal Emp.sal%type;
Begin
v_empno:=&no;
Dbms_output.put_line (' Entered employee number is: ' | | V_EMPNO);
Select Sal into V_sal from EMP where empno=v_empno;
Case
When v_sal<1000 Then
Update emp set comm=100 where Empno=v_empno;
Dbms_output.put_line (' to pay ' | | v_sal| | ' Less than 1000 of the staff issued 100 Yuan bonus ');
When v_sal<2000 Then
Update emp set comm=80 where Empno=v_empno;
Dbms_output.put_line (' to pay ' | | v_sal| | ' Less than 2000 of the staff issued 80 Yuan bonus ');
When v_sal<3000 Then
Update emp set comm=50 where Empno=v_empno;
Dbms_output.put_line (' to pay ' | | v_sal| | ' Less than 3000 of the staff issued 50 Yuan bonus ');
Else
Dbms_output.put_line (' Pay no bonuses ' to employees with a salary greater than or equal to 3000);
End case;
End

3. Looping statements
Category: Loop loops, while loops, for loops
CREATE TABLE Temp_table (
Num_col Number (8),
Char_col VARCHAR2 (200)
);

3.1 Loop Loop
Grammar:
Loop
Statement
End Loop;
The loop loop does not have an exit condition, and the statement is executed indefinitely.
Be sure to include the Exit statement, define the loop control variable, and change the value of the loop control variable in the loop body.
Loop Exit exit
exit when conditions;
If condition Then
Exit
End If;

Declare
V_counter number:=1;
Begin
Loop
INSERT into temp_table values (V_counter, ' Loop Loop ');
v_counter:=v_counter+1;
If v_counter>=10 Then
Exit
End If;
--exit when v_counter>50;
End Loop;
End

3.2 While loop
Grammar:
While condition loop
Statement
End Loop;

Before each loop, the condition is judged, the condition is true and the statement is executed; otherwise the loop terminates.
Declare
V_counter number:=1;
Begin
While V_counter<=50 loop
INSERT into temp_table values (V_counter, ' while Loop ');
v_counter:=v_counter+1;
End Loop;
End

You can still exit the loop using exit in the while.
Declare
V_counter number:=1;
Begin
While V_counter<=50 loop
INSERT into temp_table values (V_counter, ' while loop exit ');
v_counter:=v_counter+1;
Exit when v_counter>20;
End Loop;
End

3.3 Digital for-loop
When using a For loop, Oracle implicitly defines the loop control variable.
Grammar:
For counter In[reverse] Lower_bound. Upper_bound Loop
Statement1;
Statement2;
.......
End Loop;
Counter is a loop control variable, and the variable is implicitly defined by Oracle and does not require a display definition;
The Lower_bound and Upper_bound correspond to the upper and lower bounds of the cyclic control variable respectively.
By default, for loops, each time a reverse option is specified, each loop control variable is reduced by one
Example 1:
Begin
For V_counter in 1..10 loop
INSERT into temp_table values (V_counter, ' for Loop ');
End Loop;
End
Example 2:reverse from large to small cycle
Begin
For v_counter in reverse 1..10 loop
INSERT into temp_table values (V_counter, ' for loop reverse ');
End Loop;
End

3.4 Implicit cursor for loop
Begin
For RS in (SELECT * from emp) loop
INSERT into temp_table values (rs.empno,rs.ename);
End Loop;
End

Begin
For RS in (SELECT * from emp where deptno=10) loop
If rs.deptno=10 Then
Update emp set sal=sal*1.1 where Empno=rs.empno;
End If;
End Loop;
End

3.5 Nested Loops
Example 1:2-layer for loop
Declare
Result number;
Begin
For I in 1..10 loop
For j in 1..10 Loop
Result:=i*j;
Dbms_output.put_line (i| | ' * ' | | j| | ' = ' | | result);
End Loop;
End Loop;
End

Example 2: Multiplication tables
Declare
N_result number;
V_result VARCHAR2 (2);
Begin
For I in 1..9 loop
For j in 1..i Loop
N_result:=j*i;
If n_result<10 and j<>1 then
v_result:=n_result| | ' ‘;
Else
V_result:=n_result;
End If;
Dbms_output.put (j| | ' * ' | | i| | ' = ' | | v_result| | ' ‘);
End Loop;
Dbms_output.put_line (");
End Loop;
End

4. Exit the Loop
4.1 Continue exit this cycle (10G or later)
Begin
For V_counter in 1..50 loop
If v_counter>20 and v_counter<=30 then
Continue
Else
INSERT into temp_table values (V_counter, ' for loop continue ');
End If;
End Loop;
End

4.2 Exit exit this layer loop
Begin
For V_counter in 1..50 loop
If V_counter>10 Then
Exit
Else
INSERT into temp_table values (V_counter, ' for Loop exit ');
End If;
End Loop;
End
Nesting loops and Labels: by using labels in nested loops, you can distinguish between inner loops and outer loops.
and can exit the outer loop directly in the inner loop
Example 3:
Declare
N_result number;
Begin
<<outer>>
For I in 1..10 loop
<<inter>>
For j in 1..10 Loop
N_result:=i*j;
Dbms_output.put_line (' Inner loop output: ' | | N_result);
--Exit inter when j=8;
--exit outer when n_result=30;
End loop Inter;
Dbms_output.put_line (' inner and outer loop output: ' | | N_result);
End loop outer;
Dbms_output.put_line (' outer loop outside output: ' | | N_result);
End

4.3 goto is used to jump to a specific label to execute a statement.
Declare
I int: = 1;
Begin
Loop
INSERT into temp_table values (i, ' Loop loop +goto ');
If i=10 Then
Goto End_loop;
End If;
i:=i+1;
End Loop;
<<end_loop>>
Dbms_output.put_line (' End of cycle ');
End

5. Manipulating data
5.1 Inserting data using the VALUES clause
Declare
V_deptno Dept.deptno%type;
V_dname Dept.dname%type;
Begin
v_deptno:=&no;
v_dname:= ' &name ';
INSERT INTO Dept (deptno,dname) VALUES (v_deptno,v_dname);
End

5.2 Inserting data using subqueries
--duplicate table structure with no data
CREATE TABLE employee as SELECT * from EMP where 1=2;
--
Declare
V_deptno emp.deptno%type:=&no;
Begin
INSERT INTO employee SELECT * from EMP where deptno=v_deptno;
End

5.3 Updating data
Updating column values with an expression
Declare
V_deptno dept.deptno%type:=&no;
V_loc dept.loc%type:= ' &loc ';
Begin
Update Dept set loc=v_loc where Deptno=v_deptno;
End

5.4 Updating column values with subqueries MILLER
Declare
V_ename emp.ename%type:= ' &name ';
Begin
Update employee Set (SAL,COMM) =
(select Sal,comm from emp where ename=v_ename)
where job = (select Job from emp where ename=v_ename)
End

5.5 Deleting data
Using variables to delete data
Declare
V_deptno dept.deptno%type:=&no;
Begin
Delete from dept where Deptno=v_deptno;
End

5.6 Using subqueries to delete data
Declare
V_ename emp.ename%type:= ' &name ';
Begin
Delete from employee where deptno=
(select Deptno from emp where ename=v_ename);
End

6. Transaction control Statements
Transaction control statements include three statements such as Commit,rollback and savepoint
Example 1:
Declare
V_sal emp.sal%type:=&salary;
V_ename emp.ename%type:= ' &name ';
Begin
Update EMP set sal=v_sal where lower (ename) =v_ename;
Select Sal into V_sal from EMP where ename=v_ename;
Dbms_output.put_line (v_sal);
Commit
exception
When others then
--dbms_output.put_line (' A run-time exception occurred ');
--raise_application_error (-20001, ' a run-time exception occurred ');
Rollback
End

Example 2:
Declare
Begin
INSERT into temp_table values (1, ' Transaction control statement ');
SavePoint A1;
INSERT into temp_table values (2, ' transaction control statement ');
SavePoint A2;
INSERT into temp_table values (3, ' transaction control statement ');
SavePoint A3;
Rollback to A2;
Commit
End

Oracle_pl/sql (2) Process Control