Source-pl/sql from beginner to proficient-fourth Chapter-pl/sql control statement (base of Base)

Control statements are fundamental to any language, to any programmer.

Learn PL/SQL, only to realize the simplicity of Java grammar, it is not an era of things, of course, their positioning is not the same, not in their own longer than the short of others.

--The 4th chapter begins--code 4.1 The simplest if statement uses example declare v_count number (10): = 0; --Define the counter variable V_empno number (4): = 7888;  --Define employee number begin SELECT COUNT (1)--first query the specified employee number for the presence of into V_count from emp WHERE empno = v_empno; --Use the IF statement to determine if the employee number does not exist, the result is 0 if V_count = 0 THEN--executes the INSERT statement, inserts a new employee record insert INTO EMP (empno, ename, job, hire  Date, Sal, Deptno) VALUES (V_empno, ' Zhang San ', ' manager ', TRUNC (sysdate), 1000, 20); elsif v_count >= 1 then dbms_output.put_line (' the employee already exists.  ');  END IF; --Commit the change to the database; EXCEPTION when OTHERS then Dbms_output.put_line (SQLERRM); --Output exception information End;select * from emp;--code 4.2 if-then-elsedeclare v_count Number (10): = 0; --Define the counter variable V_empno number (4): = 7888;  --Define employee number begin SELECT COUNT (1)--first query the specified employee number for the presence of into V_count from emp WHERE empno = v_empno; --Use the IF statement to determine if the employee number does not exist, the result is 0 if V_count = 0 THEN--executes the INSERT statement, inserts a new employee record insert INTO EMP (empno, ename, job, hire  Date, Sal, Deptno) VALUES (V_empno, ' Zhang San ', ' manager ', TRUNC (sysdate), 1000, 20);  ELSE  --Otherwise, the UPDATE statement is executed updating the employee record updates emp SET ename = ' yellow width ', job = ' Advisor ', HireDate = TRUNC (SYS  DATE), Sal = 100000, Deptno = WHERE empno = v_empno;  END IF; --Commit the change to the database; EXCEPTION when OTHERS then Dbms_output.put_line (SQLERRM);    --Output exception information end;--code 4.3 Nested If statement declare v_sal number (11, 2);       --Salary variable V_deptno number (2);     --Department variable V_job VARCHAR2 (9);  --position variable begin--Query the database for information about the specified employee number SELECT deptno, V_job, Sal into V_deptno, V_job, v_sal from emp WHERE empno =   &empno; --If the department number 20 employee If V_deptno = then--If job title is clerk if V_job = ' Clerk ' then--raise 0.12 V_sa      L: = v_sal * (1 + 0.12);      --If job title is analyst Elsif V_job = ' analyst ' then--pay 0.19 v_sal: = V_sal * (1 + 0.19);   END IF;   -otherwise, an employee who is not 20 will not be allowed a raise or else dbms_output.put_line (' Only employees with a department number of 20 can pay a raise '); END IF; end;--Code 4.4 If-then-elsif Branch Example declare--define substitution variables (here &tmpvar to be placed inInside quotation marks, otherwise run times wrong) V_character CHAR (1): = ' &tmpvar ';  BEGIN If V_character = ' A '--determines whether the character is ' a ', if not, jumps to the next elsif then Dbms_output.put_line (' Current output string: ' | |   V_character); elsif v_character = ' B '--determines if the character is ' B ', if not, jumps to the next elsif then Dbms_output.put_line (' Current output string: ' | | v_charac   ter); elsif v_character = ' C '--determines if the character is ' C ', if not, jumps to the next elsif then Dbms_output.put_line (' Current output string: ' | | v_charac   ter); elsif v_character = ' d '--determines whether the character is ' d ', if not, jumps to the Else statement then Dbms_output.put_line (' Current output string: ' | | | v_characte   R);   ELSE Dbms_output.put_line (' not a character between a-d '); END IF;             end;--Code 4.5 Simple CASE Statement Using example declare V_job VARCHAR2 (30);   --Defines the character type variable that holds the case selector v_empno number (4): = &empno; --Define employee number to query employee begin SELECT Job--Get the value of the selector v_job into V_job from emp WHERE empno = v_   Empno     --When the selector for case is v_job, the type of all when clauses must match to VARCHAR2 type case v_job when ' clerk ' then    UPDATE emp SET sal = sal * (1 + 0.15) WHERE empno = V_empno;      Dbms_output.put_line (' 15% ' for General Staff);         When the ' ANALYST ' then UPDATE emp SET sal = sal * (1 + 0.18) WHERE empno = V_empno;      Dbms_output.put_line (' 18% ' for analysts ' salary increase);         When the ' MANAGER ' then UPDATE emp SET sal = sal * (1 + 0.20) WHERE empno = V_empno;      Dbms_output.put_line (' 20% ' for managerial staff);         When the ' salesman ' then UPDATE emp SET sal = sal * (1 + 0.22) WHERE empno = V_empno;      Dbms_output.put_line (' 22% ' for sales staff); else-Use the Else statement to display the information Dbms_output.put_line (' employee ranks are not in the ranks of the raise!   ');                END case;          --Terminate the case statement block end;--code 4.6 to search for an instance statement using example declare v_sal number (10, 2);     --Define the variable that holds the salary v_empno number: = &empno;--employee numbers used for querying begin SELECT Sal--Get Employee payroll information   Into V_sal from emp WHERE empno = v_empno; --Use Search CASE statement, judging employee salary level case when v_sal between and dbms_output.put_line (' Employee level: Junior staff ');      When v_sal between and dbms_output.put_line (' Employee Level: Intermediate Management ');      When v_sal between and dbms_output.put_line (' Employee Level: Senior manager ');   ELSE Dbms_output.put_line (' not within the level range '); END case;   END;         --code 4.7 loop and Exit Use example declare V_count number (2): = 0;         --Define loop count variable begin loop--Start execution loop V_count: = V_count + 1; --loop counter plus 1--Print character information dbms_output.put_line (' line ' | | | v_count | |      ': Hello pl/sql! ');       --If the count condition is 10, exit the loop if V_count = ten then exit;   --use Exit to exit the loop END IF;   END LOOP; --After the loop exits, the statement is executed Dbms_output.put_line (' The Loop has exited! ');         end;--Code 4.8 Loop and EXIT When using example declare V_count number (2): = 0;         --Define loop count variable begin loop--Start execution loop V_count: = V_count + 1; --loop counter plus 1--Print character information dbms_outPut.put_line (' line ' | | | v_count | |      ': Hello pl/sql! ');   --If the count condition is 10, then exit the loop exit when v_count=10;   END LOOP; --After the loop exits, the statement is executed Dbms_output.put_line (' The Loop has exited! '); end;--code 4.9 uses continue to restart the loop declare x number: = 0; Begin loop-Begins the loop and, when encountered with the continue statement, restarts the execution of the Loop dbms_output.put_line (' Inner loop value: x = ' | |      To_char (x));      X: = x + 1;         If x < 3 then-if the counter is less than 3, start the loop again.             CONTINUE;      --use continue to skip the following code execution and restart the loop END IF; --When the loop count is greater than 3 o'clock executed code dbms_output.put_line (' value after ' continue: x = ' | |      To_char (x));         EXIT when x = 5;   --When the loop count is 5 o'clock, exit the loop END loop; --The end value of the output loop Dbms_output.put_line (' value after the end of the loop body: x = ' | | To_char (x)); end;--Code 4.10 Uses continue when restarting the loop (more concise than if then Conitinue) DECLARE x number: = 0; Begin loop-Begins the loop and, when encountered with the continue statement, restarts the execution of the Loop dbms_output.put_line (' Inner loop value: x = ' | |      To_char (x));      X: = x + 1;      CONTINUE when x<3; --code that executes when the loop count is greater than 3 o'clock     Dbms_output.put_line (the value after ' continue: x = ' | |      To_char (x));         EXIT when x = 5;   --When the loop count is 5 o'clock, exit the loop END loop; --The end value of the output loop Dbms_output.put_line (' value after the end of the loop body: x = ' | | To_char (x));          end;--code 4.11 uses While-loop output counter value declare counter number: = 1; --Define counter variable begin while (counter < 10)--The condition for judging the loop is counter<10 loop dbms_output.put_line (' Counter [' | | | c Ounter | |          '].');   If counter >= 1--If the loop counter is greater than or equal to 1 then counter: = Counter + 1;   --Add the loop counter to the 1 END IF; END LOOP;    end;--Code 4.12 Simple For-loop Example declare v_total INTEGER: = 0; --Cyclic cumulative summary number begin for I in 1. 3--Use the For loop to start the loop count loop v_total: = V_total + 1;   --Aggregate cumulative dbms_output.put_line (' Cyclic counter value: ' | | i);   END LOOP; --Output loop result value Dbms_output.put_line (' Loop total: ' | | v_total);    end;--Code 4.13 uses Reverse's for-loop example declare v_total INTEGER: = 0; --Cyclic cumulative summary number begin for I in REVERSE 1. 3--loop loop v_total with reverse from high to Low: = V_total + 1; --Summary accumulation     Dbms_output.put_line (' Cyclic counter value: ' | | i);   END LOOP; --Output loop result value Dbms_output.put_line (' Loop total: ' | | v_total);  end;--Code 4.14 Dynamically specifies the loop boundary value declare v_counter INTEGER: = &counter; --Dynamic specified upper boundary value variable begin for I in 1.   V_counter--Use variables to define the boundary loop Dbms_output.put_line (' Loop count: ' | | i) in the loop; END LOOP; end;--Code 4.15 Goto statement uses example declare P VARCHAR2 (30); --Define output string variable n pls_integer: = &prime_number; --Define the number to be judged (here the code is enhanced to allow user input, and the loop output) BEGIN <<outer>> for J in 2. ROUND (SQRT (n)) loop--Outer loop if n MOD j = 0 Then--determine if it is a prime number p: = ' not a prime number '; --Initialize the value of P GOTO print_now;  --Jump to Print_now label position END IF;  END LOOP;  P: = ' is a prime number '; --Jump to label location <<print_now>> Dbms_output.  Put_Line (To_char (n) | | p);  N: = n-1;  IF n >= 1 then GOTO OUTER;  ELSE NULL; END IF;   end;--Code 4.16 Goto statement simulates a looping statement declare v_counter INT: = 0;   --Define loop counter variable begin <<outer>>--Define label V_counter: = V_counter + 1; Dbms_output.put_line (' Loop counter:' ||   V_counter);           --judging the counter conditions IF V_counter < 5 then GOTO OUTER; --Jump up to the label position END IF;  end;--code 4.17 NULL statement using example declare v_counter INT: = &counter;  --Allow user to enter variable value BEGIN if V_counter > 5--If the value of the variable is greater than 5 then Dbms_output.put_line (' v_counter>5 ');                      --Output information else--otherwise NULL; --placeholder only, do not do anything END IF;                                    end;--code 4.18 NULL with label Use example declare v_result INT: = 0;                                            --the variable that holds the result value begin V_result: = 16/0; -intentionally by 0 except Dbms_output.put_line (' Now time is: ' | | To_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS '));                                  EXCEPTION--Exception handling statement block when OTHERS and then NULL; --Nothing is done when any exception is triggered. END;

Source-pl/sql from beginner to proficient-fourth Chapter-pl/sql control statement (base of Base)