Oracle (5) control statements

Oracle (5) control statements all programming languages are inseparable from control statements, oracle also provides basic control statements for PL/SQL programming, conditional statements, cyclic statements, sequential control statements ------------------------------- condition statements --------------------------- control statements pl/SQL three condition branch statements if -- then, if -- then --- else, if -- then --- elsif --- then --- the above three statements should be in java if () {} www.2cto.com if () {} else {} if () {} else if {} else {}
Case 1 (if -- then) -- write a process, you can enter an employee name, if the employee's salary is lower than -- 2000, add 10% create or replace procedure sp_pro6 (spName varchar2) is -- Define v_sal emp. sal % type; begin -- execute select sal into v_sal from emp where ename = spName; -- judge if v_sal <2000 thenupdate emp set sal = sal + sal * 10% where ename = spName; end if; end; exec sp_pro6 ('Scott '); Case 2 (if -- then --- else) -- write a process and enter an employee name, if the employee's subsidy is not 0, an additional 100 will be added on the original base; if the subsidy is 0, the subsidy will be set to 200 create or replace procedure sp_pro6 (spName varchar2) is -- defines www.2cto.com v_comm emp. comm % type; begin -- execute select comm into v_comm from emp where ename = spName; -- judge if v_comm <> 0 thenupdate emp set comm = comm + 100 where ename = spName; elseupdate emp set comm = comm + 200 where ename = spName; end if; end; exec sp_pro6 ('Scott ');
Case 3 (if -- then --- elsif --- then --- else) -- write a process and enter an employee number. if the employee is president, his salary is increased by 1000, if the employee is a manager, his salary will be increased by 500. The employee's salary in other positions will be increased by 200 create or replace procedure sp_pro6 (spNo number) is -- defines www.2cto.com v_job emp. job % type; begin -- execute select job into v_job from emp where empno = spNo; if v_job = 'President 'thenupdate emp set sal = sal + 1000 where empno = spNo; elsif v_job = 'manager' thenupdate emp set sal = sal + 500 where empno = spNo; else update emp set sal = sal + 200 where empno = spNo; end if; end;
------------------------------- Loop statement ----------------------------------- the loop statement begins with a loop and ends with an end loop. This loop will be executed at least once for a while loop, the body loop statement is executed only when the condition is true... loop starts to end with end loop. for loop statement case (loop) -- please write a process, enter the user name, and add 10 users to the -- users table cyclically, create or replace procedure sp_pro6 (spName varchar2) is added from 1 -- Definition: = indicates that the value is assigned to www.2cto.com v_num number: = 1; beginloop insert into users value (v_num, spName ); -- determine whether to exit the loop exit when v_num = 10; -- auto-incrementing pl/SQL does not have the syntax v_num ++ in java. v_num: = v_num + 1; end loop; end;
Case (while LOOP) -- please write a process, enter the user name, and add 10 users to the -- users table cyclically, create or replace procedure sp_pro6 (spName varchar2) is added to the user number starting from 11 -- Definition: = indicates the value of v_num number: = 11; beginwhile v_num <= 20 loop -- execute insert into users value (v_num, spName); v_num: = num + 1; end loop; www.2cto.com end; the basic structure of for loop statements (the first two for loop statements are not recommended to meet the requirements) is as follows: begin for I in reverse 1 .. 10 loop insert into users values (I, 'kyle '); end loop; end; Description: Controls variable I, which is constantly added in implicit mode ----- -------------------------- Ordered control statement ----------------------------------- goto and nullgoto statements jump to a specific label to execute the statement. Note that the use of the goto statement increases the complexity of the program and the readability of applications, therefore, during general application development, we recommend that you do not use the basic syntax of the goto statement as follows: goto lable, where lable is the defined label name declarei int: = 1; begin loop dbms_output.put.line ('output I = '| I); if I = 10 then goto end_loop; end if; I: = I + 1; end loop; <end_loop> dbms_output.put_line ('loop termination'); end; the null statement www.2cto.com does not perform any operation, and the control is directly passed to the next statement.. The main advantage of using a null statement is that it can improve the readability of pl/SQL. declare v_sal emp. sal % type; v_ename emp. ename % type; begin select ename, sal into v_ename, v_sal from emp where empno = & no; if v_sal <3000 then update emp set comm = sal * 0.1 where ename = v_ename; else null; end if; end; Author: kyle8525_nsn