1. IF statement
1.1 IF-THEN statement
Syntax format:
IF condition 1 THEN Execute statement 1; ······ Execute statement n; END IF;
EX:
SQL> declare
2 var_num number;
3 var_name emp.ename% type: = '& ename'; -- & variable to control employee name input
4 begin
5 select count(*) into var_num from emp where ename=var_name;
6 if var_num > = 1 then -- "= 1" instead of "= 1" to avoid duplicate names
7 dbms_output.put_line('there exists :'||var_name);
8 end if;
9 end;
10 /
Enter the value of ename: SMITH -- enter the employee name queried
Original value 3: var_name emp.ename% type: = '& ename';
New value 3: var_name EMP. Ename% type: ='smith ';
there exists :SMITH
PL/SQL procedure completed successfully.
1.2 IF-THEN-ELSE statement
Syntax format:
IF condition 1 THEN Execute statement 1; ······ Execute statement n; ELSE Execute statement 1; ······ Execute statement n END IF;
EX:
Modify directly on the above example
SQL> 8 else dbms_output.put_line('the ' ||var_name|| ' does not exists');; --Notice to add two semicolons, one is the original SQL One of the statements is the end flag of the entire modified statement SQL> 9 end if;; SQL> 10 end;; SQL> RUN 1 declare 2 var_num number; 3 var_name emp.ename%type := '&ename'; 4 begin 5 select count(*) into var_num from emp where ename=var_name; 6 if var_num>=1 then 7 dbms_output.put_line('there exists :'||var_name); 8 else dbms_output.put_line('the ' ||var_name|| ' does not exists'); 9 end if; 10* end; //Enter the value of ename: smith2 //Original value 3: var_name emp.ename%type := '&ename'; //New value 3: var_name emp.ename%type := 'smith2'; the smith2 does not exists PL/SQL The process completed successfully.
1.3 elsif statement
- Statement executed in order
- Each execution condition is mutually exclusive
-
Notice that it's ELSIF, not ELSEIF
I didn't pay attention at the beginning. It was always reported as elseif sales > 35000 thenWarning: the process created has a compilation error. SQL> show errors; Error in PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 7 / 8 pls-00103: the symbol "SALES" appears when one of the following is required: := . ( @ % ;
Syntax format:
IF condition 1 THEN Execute statement 1; ELSIF condition 2 -- note that it is ELSIF instead of ELSEIF Execute statement 2; ELSIF condition 3 -- note that it is ELSIF instead of ELSEIF Execute statement 3; ······ ELSIF condition n -- notice that it is ELSIF instead of ELSEIF Execute statement n; ELSE Execute statement; END IF;
EX:
SQL> run 1 create or replace PROCEDURE p (sales NUMBER) 2 IS 3 bonus NUMBER :=0; 4 BEGIN 5 IF sales > 50000 THEN 6 bonus := 1500; 7 ELSIF sales > 35000 THEN 8 bonus := 500; 9 ELSE 10 bonus :=100; 11 END IF; 12 DBMS_OUTPUT.PUT_LINE ( 13 'Sales = ' || sales || ', bonus = ' || bonus || '.' 14 ); 15* END p; //Procedure created. SQL> DESC p; PROCEDURE p //Parameter name type input/Output default? ------------------------------ ----------------------- ------ -------- SALES NUMBER IN SQL> BEGIN 2 P(50001); 3 P(37000); 4 P(1000); 5 END; 6 / Sales = 50001, bonus = 1500. Sales = 37000, bonus = 500. Sales = 1000, bonus = 100. PL/SQL The process completed successfully.
1.4 nested IF statements
Further conditional restriction is made in the statement of PL/SQL statement block, and IF statement is nested in it
EX:
SQL> run 1 declare 2 var_num number; 3 var_name emp.ename%type := '&ename'; 4 begin 5 select count(*) into var_num from emp where ename=var_name; 6 if var_num>=1 then 7 dbms_output.put_line('there exits :'||var_name); 8 if var_name='SMITH' then 9 dbms_output.put_line('SMITH is found'); 10 else 11 dbms_output.put_line('where is SMITH?'); 12 end if; 13 else 14 dbms_output.put_line('the '||var_name||' does not exits!'); 15 end if; 16* end; //Enter a value for ename: SMITH //Original value 3: var_name emp.ename%type := '&ename'; //New value 3: var_name emp.ename%type := 'SMITH'; there exits :SMITH SMITH is found ··· ··· //Enter the value of ename: CLAVIN //Original value 3: var_name emp.ename%type := '&ename'; //New value 3: var_name emp.ename%type := 'CLAVIN'; the CLAVIN does not exits! PL/SQL The process completed successfully.
2. CASE statement
2.1 simple case statement
Syntax format:
CASE selector The value 1 of the WHEN selector then executes statement 1 WHEN selector value 2 then execute statement 2 ······ The value of the WHEN selector n then executes statement n ESLE Execute statement; END CASE;
EX:
SQL> run 1 declare 2 mark number :=&mark_number;--take mark_number Pass in value as parameter mark 3 begin 4 case mark 5 when 1 then--When mark When the value of is 1 6 dbms_output.put_line('Monday'); 7 when 2 then 8 dbms_output.put_line('Tuesday'); 9 when 3 then 10 dbms_output.put_line('Wednesday'); 11 when 4 then 12 dbms_output.put_line('Thursday'); 13 when 5 then 14 dbms_output.put_line('Friday'); 15 when 6 then 16 dbms_output.put_line('Saturday'); 17 when 7 then 18 dbms_output.put_line('Sunday'); 19 end case; 20* end; //Enter a value for mark Ou number: 4 //Original value 2: mark number :=&mark_number; //New value 2: mark number :=4; Thursday PL/SQL The process completed successfully.
Add exception handling
EX:
SQL > Save D: \ case.sql -- directly save the above to the script file for modification
Created file d:\case.sql
SQL > Edit D: \ case.sql -- modify the document in the editor
SQL > @ d: \ case.sql -- run script file
1 declare
2 mark number :=&mark_number;
3 begin
4 case mark
5 when 1 then
6 dbms_output.put_line('Monday');
7 when 2 then
8 dbms_output.put_line('Tuesday');
9 when 3 then
10 dbms_output.put_line('Wednesday');
11 when 4 then
12 dbms_output.put_line('Thursday');
13 when 5 then
14 dbms_output.put_line('Friday');
15 when 6 then
16 dbms_output.put_line('Saturday');
17 when 7 then
18 dbms_output.put_line('Sunday');
19 else DBMS "output. Put" line ('No such day! '); -- exception handling added
20 end case;
21* end;
Enter the value of mark_number: 666 -- enter a number that does not exist
Original value 2: mark number: = & Mark Ou number;
New value 2: mark number: = 666;
no such day!
PL/SQL procedure completed successfully.
2.2 search case statement
- Search with WHEN clause without selector
EX:SQL> declare 2 v_num number :=&var_num; 3 begin 4 if v_num >=0 and v_num<101 then 5 case 6 when v_num>=90 then 7 dbms_output.put_line('exellent'); 8 when v_num>=70 then 9 dbms_output.put_line('good'); 10 when v_num>=60 then 11 dbms_output.put_line('so bad!'); 12 end case; 13 else 14 dbms_output.put_line('no such mark'); 15 end if; 16 end; 17 / //Enter the value of VaR ﹣ num: 88 //Original value 2: v_num number :=&var_num; //New value 2: v_num number :=88; good PL/SQL The process completed successfully.
3. Loop control statement
3.1 simple loop statement
Syntax format:
LOOP Execute statement; END LOOP;
EX:
SQL> declare 2 var_num number :=0; 3 begin 4 loop 5 dbms_output.put_line('var_num is '||to_char(var_num)); 6 var_num := var_num+2; 7 if var_num>10 then 8 exit;--End of use condition judgment loop loop 9 end if; 10 end loop; 11 dbms_output.put_line('last var_num is '||to_char(var_num)); 12 end; 13 / var_num is 0 var_num is 2 var_num is 4 var_num is 6 var_num is 8 var_num is 10 last var_num is 12 PL/SQL The process completed successfully.
You can also use EXIT WHEN to end the LOOP loop
SQL> 7 exit when var_num>12;; --Overwrite the seventh line of command in the cache SQL>del 8;--Delete lines 8 and 9 SQL>del 9; SQL> list 1 declare 2 var_num number :=0; 3 begin 4 loop 5 dbms_output.put_line('var_num is '||to_char(var_num)); 6 var_num := var_num+2; 7 exit when var_num>12; 8 end loop; 9 dbms_output.put_line('last var_num is '||to_char(var_num)); 10* end;
3.2 WHILE loop statement
3.2.1 simple syntax format:
WHILE cycle condition LOOP Execute statement 1; Execute statement 2; ··· Execute statement n; END LOOP;
- Judge cycle condition and execute again
- If the conditions are met, the loop is executed
- Exit if conditions are not met
- Cycle condition is the end condition
EX:
SQL> 4 while var_num<12 loop SQL> del 7 SQL> list 1 declare 2 var_num number :=0; 3 begin ------- while loop 4 while var_num<12 loop 5 dbms_output.put_line('var_num is '||to_char(var_num)); 6 var_num := var_num+2; 7 end loop; ------- 8 dbms_output.put_line('last var_num is '||to_char(var_num)); 9* end; SQL> run 1 declare 2 var_num number :=0; 3 begin 4 while var_num<12 loop 5 dbms_output.put_line('var_num is '||to_char(var_num)); 6 var_num := var_num+2; 7 end loop; 8 dbms_output.put_line('last var_num is '||to_char(var_num)); 9* end; var_num is 0 var_num is 2 var_num is 4 var_num is 6 var_num is 8 var_num is 10 last var_num is 12 PL/SQL The process completed successfully.
3.2.2 typical syntax format:
WHILE cycle condition LOOP Execute statement 1; Execute statement 2; IF end condition THEN EXIT; END IF; END LOOP
EX:
SQL> run 1 declare 2 var_num number :=0; 3 begin 4 while var_num<12 loop 5 dbms_output.put_line('var_num is '||to_char(var_num)); 6 if var_num = 8 then 7 exit;--var_num Jump out of loop directly at 8 8 end if; 9 var_num := var_num+2; 10 end loop; 11 dbms_output.put_line('last var_num is '||to_char(var_num)); 12* end; var_num is 0 var_num is 2 var_num is 4 var_num is 6 var_num is 8 last var_num is 8 PL/SQL The process completed successfully.
Simplify the modification with EXIT WHEN:
SQL> list 1 declare 2 var_num number :=0; 3 begin 4 while var_num<12 loop 5 dbms_output.put_line('var_num is '||to_char(var_num)); 6 exit when var_num=8;--If var_num=8 Just exit does not mean to continue to the next step 7 var_num := var_num+2 8 end loop;--Loop statement end flag 9 dbms_output.put_line('last var_num is '||to_char(var_num)); 10* end;
3.3 FOR loop statement
3.3.1 scope FOR loop statement
SQL> declare 2 var_counter number; 3 begin 4 for i in 1..1000 loop --Specify cycle range, add 1 for each cycle; index range package boundary 5 insert into student values(i,'name'||to_char(i)); 6 end loop; 7 commit; 8 select count(*) into var_counter from student; 9 dbms_output.put_line('var_counter is : '||var_counter); 10 end; 11 / var_counter is : 1000 PL/SQL The process completed successfully.
3.3.1 cursor FOR loop statement
Syntax format:
FOR cursor index IN (SELECT statement) LOOP Loop statement END LOOP;
EX:
SQL> begin --Vernier for Loop acquisition select A set of records returned by a statement 2 for i in (select ename,sal,deptno from emp where sal>2500) loop --Vernier index I To get the value of a column of a record 3 dbms_output.put_line(i.ename||' salary is : '||i.sal|| ' and in '||i.deptno); 4 end loop; 5 end; 6 / JONES salary is : 2975 and in 20 BLAKE salary is : 2850 and in 30 SCOTT salary is : 3000 and in 20 KING salary is : 5000 and in 10 FORD salary is : 3000 and in 20 PL/SQL The process completed successfully.
4. Sequence control statement
4.1 CONTINUE statement
4.1.1 CONTINUE
Syntax format:
LOOP Execute statement 1 ······ Execute statement n IF Continue condition THEN CONTINUE; END IF; EXIT WHEN end Continue condition; END LOOP;
EX:
SQL> run 1 declare 2 var_num number :=10; 3 begin 4 LOOP 5 var_num := var_num-1; 6 if var_num>5 then 7 dbms_output.put_line('var_num is:'||to_char(var_num)||'in continue...'); 8 CONTINUE;#When var num is greater than 5, continue to execute the statements in the loop: VAR num: = var num-1; 9 end if;#Exit if when var ﹤ num is not greater than 5 10 if var_num=0 then 11 dbms_output.put_line('end...');#Output 'end...' when var? Num is 0 12 exit; #And end all exits 13 end if; 14 dbms_output.put_line('var_num is : '|| to_char(var_num)); 15 END LOOP; 16* end; var_num is:9in continue... var_num is:8in continue... var_num is:7in continue... var_num is:6in continue... var_num is : 5 var_num is : 4 var_num is : 3 var_num is : 2 var_num is : 1 end...
4.1.2 CONTINUE WHEN
Syntax format:
LOOP Execute statement 1 ······ Execute statement n Continue when continue condition; EXIT WHEN end Continue condition; END LOOP;
EX:
SQL> declare 2 var_num number :=10; 3 begin 4 LOOP 5 var_num :=var_num-1; 6 dbms_output.put_line('var_num is:'||to_char(var_num)||'in continue...'); 7 CONTINUE when var_num>5;#Output 'in continue...' and then judge whether the condition > 5 is met, #If satisfied, the loop statement before CONTINUE is executed #Otherwise, execute the LOOP statement after CONTINUE in the LOOP loop 8 if var_num=0 then 9 dbms_output.put_line('end...'); 10 exit; 11 end if; 12 dbms_output.put_line('var_num is :'||to_char(var_num)||' out continue'); 13 END LOOP; 14 end; 15 / var_num is:9in continue... var_num is:8in continue... var_num is:7in continue... var_num is:6in continue... var_num is:5in continue... var_num is :5 out continue var_num is:4in continue... var_num is :4 out continue var_num is:3in continue... var_num is :3 out continue var_num is:2in continue... var_num is :2 out continue var_num is:1in continue... var_num is :1 out continue var_num is:0in continue... end...
4.2 GOTO statement
- To transfer control of a program unconditionally to a mark
- Go to the executable statement or PL/SQL statement block after the tag to continue execution
- GOTO tags can only be used before or after a statement block
EX: search 1-100 for even numbers of large and 45
SQL> run 1 declare 2 var_label varchar2(40); 3 var_num number; 4 begin 5 for i in 1..100 loop 6 if i mod 2 = 0 and i>45 then 7 var_num :=i; #Since the qualified number 46 in 1-100, the search will be stopped after output 8 var_label := 'find the number is :'||to_char(var_num); 9 goto end_search; 10 end if; 11 end loop; 12 var_label :='can not find the number'; 13 <<end_search>> 14 dbms_output.put_line(var_label); 15* end; find the number is :46 PL/SQL The process completed successfully. #Change to an even number greater than 45 in search 1-44 SQL> 5 for i in 1..44 loop SQL> run 1 declare 2 var_label varchar2(40); 3 var_num number; 4 begin 5 for i in 1..44 loop 6 if i mod 2 = 0 and i>45 then 7 var_num :=i; 8 var_label := 'find the number is :'||to_char(var_num); #Since there is no even number greater than 45 in 1-44, jump to < < end [search > > 9 goto end_search; 10 end if; 11 end loop; 12 var_label :='can not find the number'; 13 <<end_search>> 14 dbms_output.put_line(var_label); 15* end; can not find the number PL/SQL The process completed successfully.
4.3 NULL statement
- NULL means none, not any specific
- A NULL statement means doing nothing
EX:
SQL> run 1 declare 2 var_name emp.ename%type; 3 var_number emp.empno%type := &empno;#Enter the value of empno 4 begin 5 select ename into var_name 6 from emp 7 where empno=var_number; 8 if var_name='SMITH' then#Find whether the corresponding empno value in emp is' SMITH ' 9 dbms_output.put_line('find data'); 10 dbms_output.put_line('var_name is '||var_name); 11 else#do nothing if it's not 'SMITH' 12 null; 13 end if; 14* end; //Enter a value for empno: 7369 //Original value 3: var_number emp.empno%type := &empno; //New value 3: var_number emp.empno%type := 7369; find data var_name is SMITH
Modify the null statement to print a line of information, and enter 7499, the 'empno' which does not belong to 'SMITH'
SQL> 12 dbms_output.put_line('find no data');;
SQL> run
····Omission
Enter the value of empno: 7499 × because 7499 corresponds to ALLEN
Original value 3: var_number emp.empno% type: = & empno;
New value 3: var_number emp.empno% type: = 7499;
find no data
PL/SQL procedure completed successfully.
In subsequent tests, fields not included in the empno field in the emp table, no matter whether they are NULL statements or printout statements, all report errors, and no reason can be found temporarily.
It is reasonable to execute a NULL statement, that is, exit the program without doing anything.
···When the statement is NULL 12 NULL; 13 end if; 14* end; Enter the value of empno: 123 Original value 3: var_number emp.empno% type: = & empno; New value 3: var_number emp.empno% type: = 123; declare * Error in line 1: ා you should exit directly, temporarily in doubt ORA-01403: no data found ORA-06512: on line 5
Add an exception handling statement:
SQL> declare 2 var_name emp.ename%type; 3 var_number emp.empno%type := &empno; 4 begin 5 select ename into var_name 6 from emp 7 where empno=var_number; #Exception handling, print out 'NO data found' if no value is found 8 EXCEPTION 9 when no_data_found then 10 dbms_output.put_line('NO data found'); 11 if var_name='SMITH' then 12 dbms_output.put_line('find data'); 13 dbms_output.put_line('var_name is '||var_name); 14 else 15 null; 16 end if; 17 end; 18 / //Enter a value for empno: 45 //Original value 3: var_number emp.empno%type := &empno; //New value 3: var_number emp.empno%type := 45; NO data found PL/SQL The process completed successfully.
