PL_SQL模块学习之九、程序流程

最新推荐文章于 2021-04-03 14:07:18 发布

w.ang.jie

最新推荐文章于 2021-04-03 14:07:18 发布

阅读量307

点赞数

分类专栏： oracle PL/SQL 文章标签：数据库 sql pl/sql 程序流程

本文链接：https://blog.youkuaiyun.com/qq_32392597/article/details/104689245

版权

oracle 同时被 2 个专栏收录

97 篇文章

订阅专栏

PL/SQL

19 篇文章

订阅专栏

1. IF语句

1.1 IF-THEN语句

语法格式：

IF 条件1 THEN
	执行语句1;
	······
	执行语句n;
END IF;

EX:

SQL>  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 --“>=1”而不是“=1”为了避免重复名称
  7    		dbms_output.put_line('there exists :'||var_name);
  8     end if;
  9   end;
 10  /
输入 ename 的值:  SMITH --输入查询的员工姓名
原值    3:  var_name emp.ename%type := '&ename';
新值    3:  var_name emp.ename%type := 'SMITH';
there exists :SMITH

PL/SQL 过程已成功完成。

1.2 IF-THEN-ELSE语句

语法格式：

IF 条件1 THEN
	执行语句1;
	······
	执行语句n;
	ELSE
	执行语句1;
	······
	执行语句n
END IF;

EX:
直接在上面的示例上修改

SQL> 8 else  dbms_output.put_line('the ' ||var_name|| ' does not exists');;  
--注意添加两个分号，一个是原SQL语句中的，一个是整个修改语句的结束标志
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;
输入 ename 的值:  smith2
原值    3:  var_name emp.ename%type := '&ename';
新值    3:  var_name emp.ename%type := 'smith2';
the smith2 does not exists

PL/SQL 过程已成功完成。

1.3 ELSIF语句

语句按顺序执行
各执行条件互斥

注意是ELSIF 而不是ELSEIF
一开始没注意，写成ELSEIF sales > 35000 THEN总是报错

警告: 创建的过程带有编译错误。

SQL> show errors;
PROCEDURE P 出现错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/8      PLS-00103: 出现符号 "SALES"在需要下列之一时：
         := . ( @ % ;

语法格式：

IF 条件1 
THEN
	执行语句1;
ELSIF 条件2  -- 注意是ELSIF 而不是ELSEIF
	执行语句2;
ELSIF 条件3 -- 注意是ELSIF 而不是ELSEIF
	执行语句3;
	······
ELSIF 条件n -- 注意是ELSIF 而不是ELSEIF
	执行语句n;
ELSE
	执行语句;
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;

过程已创建。

SQL> DESC p;
PROCEDURE p
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 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 过程已成功完成。

1.4 嵌套IF语句

在PL/SQL语句块的语句中进行进一步的条件限制，将IF语句嵌套进去
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;
输入 ename 的值:  SMITH
原值    3:      var_name emp.ename%type := '&ename';
新值    3:      var_name emp.ename%type := 'SMITH';
there exits :SMITH
SMITH is found
···
···
输入 ename 的值:  CLAVIN
原值    3:      var_name emp.ename%type := '&ename';
新值    3:      var_name emp.ename%type := 'CLAVIN';
the CLAVIN does not exits!

PL/SQL 过程已成功完成。

2. CASE语句

2.1 简单的case语句

语法格式：

CASE 选择器
	WHEN 选择器的值 1   THEN 执行语句 1;
	WHEN 选择器的值 2   THEN 执行语句 2;
	······
	WHEN 选择器的值 n   THEN 执行语句 n;
	ESLE
		执行语句；
END CASE;

EX:

SQL> run
  1  declare
  2  mark number :=&mark_number;--将mark_number作为参数将值传入mark
  3  begin
  4     case mark 
  5     when 1 then--当mark的值为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;
输入 mark_number 的值:  4
原值    2: mark number :=&mark_number;
新值    2: mark number :=4;
Thursday

PL/SQL 过程已成功完成。

添加异常处理
EX:

SQL> save d:\case.sql  --直接将上面的保存到脚本文件中修改
已创建 file d:\case.sql
SQL> edit d:\case.sql --在 编辑器中修改文档
SQL> @d:\case.sql--运行脚本文件
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!');--添加了异常处理
 20     end case;
 21* end;
输入 mark_number 的值:  666 --输入没有的数字
原值    2: mark number :=&mark_number;
新值    2: mark number :=666;
no such day!

PL/SQL 过程已成功完成。

2.2 搜索式case语句

不使用选择器，用WHEN子句进行搜索
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  /
输入 var_num 的值:  88
原值    2: v_num number :=&var_num;
新值    2: v_num number :=88;
good

PL/SQL 过程已成功完成。

3. 循环控制语句

3.1 简单循环语句(loop)

语法格式：

	LOOP
		执行语句;
		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;--使用条件判断结束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 过程已成功完成。

也可使用EXIT WHEN结束LOOP循环，输出结果同上

SQL> 7          exit when var_num>12;; --改写缓存中第七行命令
SQL>del 8;--删除第八和第九行命令
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循环语句

3.2.1 简单的语法格式：

WHILE 循环条件 LOOP
	执行语句;
	执行语句2;
	···
	执行语句n;
	END LOOP;

判断循环条件再执行
如果条件满足，则执行循环
如果条件不满足，则退出
循环条件即结束条件

EX:

SQL> 4 while var_num<12 loop
SQL> del 7
SQL> list
  1  declare
  2     var_num number :=0;
  3  begin
  ------- while循环
  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
······省略执行语句
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 过程已成功完成。

3.2.2 典型的语法格式：

WHILE 循环条件 LOOP
	执行语句1；
	执行语句2；
	IF 结束条件 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为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 过程已成功完成。

用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;--如果var_num=8就退出，不等于就继续执行下一步
  7  	var_num := var_num+2
  8     end loop;--循环语句结束标志
  9  dbms_output.put_line('last var_num is '||to_char(var_num));
 10* end;

3.3 FOR循环语句

3.3.1 范围FOR循环语句

SQL>  declare
 2      var_counter number;
 3      begin
 4         for i in 1..1000 loop --指定循环范围，每循环一次加1；索引范围包边界
 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 过程已成功完成。

3.3.1 游标FOR循环语句

语法格式：

FOR 游标索引 IN (SELECT 语句） LOOP
	循环执行语句
END LOOP;

EX:

SQL> begin
--用游标for循环获取select语句返回的一组记录
  2  for i in (select ename,sal,deptno from emp where sal>2500) loop
  --游标索引I来获取一个记录的某个列的值
  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 过程已成功完成。

4. 顺序控制语句

4.1 CONTINUE语句 *难

4.1.1 CONTINUE

语法格式：

LOOP
执行语句1
······
执行语句n
IF Continue 条件 THEN
	CONTINUE;
END IF;

EXIT WHEN 结束 Continue 条件；
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;#当var_num大于5时继续执行CONTINUE前的循环语句： var_num := var_num-1;
  9     end if;#当var_num不大于5时退出if
 10     if var_num=0 then
 11             dbms_output.put_line('end...');#当var_num为0时输出'end...'
 12             exit;						    #并结束所有退出
 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

语法格式：

LOOP
执行语句1
······
执行语句n
	CONTINUE  WHEN Continue 条件;
EXIT WHEN 结束 Continue 条件；
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;#输出'in continue...'后再判断是否满足>5的条件，
  								#如果满足，则执行CONTINUE之前的循环语句
  								#否则执行LOOP循环中CONTINUE之后的循环语句
  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语句

将程序的控制权无条件强制转移到某个标记处
转到标记后的可执行语句或PL/SQL语句块继续执行
GOTO标记只能在语句块之前或之后使用

EX:搜索1~100中大与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;
  						#由于1~100中由符合条件的数字46，所以输出后停止搜索
  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 过程已成功完成。
#修改为搜索1~44中大于45的偶数
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);
  				#由于1~44中没有大于45的偶数，所以直接跳转到 <<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 过程已成功完成。

4.3 NULL语句

NULL意味着无，不对应任何具体
NULL语句表示什么也不做
EX:

SQL> run
  1  declare
  2     var_name emp.ename%type;
  3     var_number emp.empno%type := &empno;#输入empno的值
  4  begin
  5     select ename into var_name
  6     from emp
  7             where empno=var_number;
  8      if var_name='SMITH' then#找到emp中对应empno值是否是'SMITH'
  9             dbms_output.put_line('find data');
 10             dbms_output.put_line('var_name is  '||var_name);
 11     else#不是'SMITH'则do nothing
 12             null;
 13     end if;
 14* end;
输入 empno 的值:  7369
原值    3:      var_number emp.empno%type := &empno;
新值    3:      var_number emp.empno%type := 7369;
find data
var_name is  SMITH

将null语句修改为打印一行信息，并输入7499这个不属于‘SMITH’的‘empno’

SQL>  12             dbms_output.put_line('find no data');;
SQL> run
 ····省略
输入 empno 的值:  7499#因为7499对应的是ALLEN
原值    3:      var_number emp.empno%type := &empno;
新值    3:      var_number emp.empno%type := 7499;
find no data

PL/SQL 过程已成功完成。

后续测试emp表中empno字段不包含的字段，不管是NULL语句还是打印输出语句，都报错，暂时找不到原因。
按理说应该执行NULL语句，即什么都不做就退出程序。

···语句为NULL时
 12              NULL;
 13      end if;
 14* end;
输入 empno 的值:  123
原值    3:     var_number emp.empno%type := &empno;
新值    3:     var_number emp.empno%type := 123;
declare
*
第 1 行出现错误: #应该直接退出才对，暂时存疑
ORA-01403: 未找到任何数据
ORA-06512: 在 line 5

自己加入一个异常处理语句：

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;
  			#异常处理，未发现数值即打印输出'NO data 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  /
输入 empno 的值:  45
原值    3:     var_number emp.empno%type := &empno;
新值    3:     var_number emp.empno%type := 45;
NO data found

PL/SQL 过程已成功完成。