plsql过程语言之uxdb与oracle语法差异

该文展示了在Oracle数据库的PL/SQL环境中如何使用GOTO语句进行流程控制,包括在存储过程、函数、匿名块中的应用,以及与IF语句、CASE语句和异常处理的结合使用。示例涵盖了从标签定义、条件跳转到循环控制等不同场景。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

序号

场景

uxdb

oracle

1

在存储过程中使用goto子句

create or replace procedure uxdbc_oracle_extension_plsql_goto_0001_procedure01(t1 int)

language plsql

as $$

begin

if t1%2=0 then

goto even_number;

else

goto odd_number;

end if;

<<even_number>>

raise info 'this is a even number';

goto end_lb;

<<odd_number>>

raise info 'this is a odd number';

<<end_lb>>

null;

end;

$$;

call uxdbc_oracle_extension_plsql_goto_0001_procedure01(10);

create or replace procedure uxdbc_oracle_extension_plsql_goto_0001_procedure01(t1 in int)

is

begin

if t1 mod 2 = 0 then

goto even_number;

else

goto odd_number;

end if;

<<even_number>>

dbms_output.put_line('this is a even number');

goto end_lb;

<<odd_number>>

dbms_output.put_line('this is a odd number');

<<end_lb>>

null;

end;

/

call uxdbc_oracle_extension_plsql_goto_0001_procedure01(10);

序号

场景

uxdb

oracle

2

在函数中使用goto子句

create or replace function uxdbc_oracle_extension_plsql_goto_0002_function01(t1 int) returns text

language plsql

returns null on null input

as $$

declare p varchar(30);

begin

if t1%2=0 then

goto even_number;

else

goto odd_number;

end if;

<<even_number>>

p := cast($1 as text) || ' is a even number';

goto end_lb;

<<odd_number>>

p := cast($1 as text) || ' is a odd number';

<<end_lb>>

return p;

end;

$$;

select uxdbc_oracle_extension_plsql_goto_0002_function01(10);

create or replace function uxdbc_oracle_extension_plsql_goto_0002_function01(t1 in int) return varchar

is p varchar(30);

begin

if t1 mod 2 =0 then

goto even_number;

else

goto odd_number;

end if;

<<even_number>>

p := t1 || ' is a even number';

goto end_lb;

<<odd_number>>

p := t1 || ' is a odd number';

<<end_lb>>

return p;

end;

/

select uxdbc_oracle_extension_plsql_goto_0002_function01(10) from dual;

序号

场景

uxdb

oracle

3

在匿名块中使用goto子句

declare t1 int:=7;

begin

if t1%2=0 then

goto even_number;

else

goto odd_number;

end if;

<<even_number>>

raise info 'this is a even number';

goto end_lb;

<<odd_number>>

raise info 'this is a odd number';

<<end_lb>>

null;

end;

/

do language plsql $$

declare t1 int:=10;

begin

if t1%2=0 then

goto even_number;

else

goto odd_number;

end if;

<<even_number>>

raise info 'this is a even number';

goto end_lb;

<<odd_number>>

raise info 'this is a odd number';

<<end_lb>>

null;

end;

$$;

declare t1 int:=10;

begin

if t1 mod 2 =0 then

goto even_number;

else

goto odd_number;

end if;

<<even_number>>

dbms_output.put_line('this is a even number');

goto end_lb;

<<odd_number>>

dbms_output.put_line('this is a odd number');

<<end_lb>>

null;

end;

/

序号

场景

uxdb

oracle

4

标签可以出现在子块前

do $$

declare

p text;

n int := 39;

begin

for j in 2..round(sqrt(n)) loop

if n % j = 0 then

p := 'is not a prime number';

goto check_odd;

end if;

end loop;

p := ' is a prime number';

<<check_odd>> --here

begin

raise info '% %',n,p;

if n%2=0 then

p := 'is a even number';

else

p := 'is a odd number';

raise info '% %',n,p;

end if;

end;

end;

$$ language plsql;

declare

p varchar(30);

n int := 39;

begin

for j in 2..round(sqrt(n)) loop

if n mod j = 0 then

p := 'is not a prime number';

goto check_odd;

end if;

end loop;

p := ' is a prime number';

<<check_odd>> --here

begin

dbms_output.put_line(n||p);

if n mod 2=0 then

p := 'is a even number';

else

p := 'is a odd number';

dbms_output.put_line(n||p);

end if;

end;

end;

/

序号

场景

uxdb

oracle

5

标签可以出现在if语句之前

declare

p text;

n int := 39;

begin

for j in 2..round(sqrt(n)) loop

if n % j = 0 then

p := 'is not a prime number';

goto check_odd;

end if;

end loop;

p := ' is a prime number';

raise info '% %',n,p;

<<check_odd>> --here

if n%2=0 then

p := 'is a even number';

else

p := 'is a odd number';

end if;

raise info '% %',n,p;

end;

/

declare

p varchar(30);

n int := 39;

begin

for j in 2..round(sqrt(n)) loop

if n mod j = 0 then

p := 'is not a prime number';

goto check_odd;

end if;

end loop;

p := ' is a prime number';

dbms_output.put_line(n||p);

<<check_odd>> --here

if n mod 2=0 then

p := 'is a even number';

else

p := 'is a odd number';

end if;

dbms_output.put_line(n||p);

end;

/

序号

场景

uxdb

oracle

6

goto语句可以从一个子if语句跳到父if语句中

declare i int :=7;

begin

if i != 0 then

if i > 0 then

raise info 'is zhengshu.';

goto lb;

else

raise info 'is fushu.';

end if;

<<lb>>

raise info 'outer';

else

raise info 'is 0.';

end if;

end;

/

declare i int :=7;

begin

if i != 0 then

if i > 0 then

dbms_output.put_line('is zhengshu.');

goto lb;

else

dbms_output.put_line('is fushu.');

end if;

<<lb>>

dbms_output.put_line('outer');

else

dbms_output.put_line('is 0.');

end if;

end;

/

序号

场景

uxdb

oracle

7

goto语句可以将控制转移出if判断语句

create table uxdbc_oracle_extension_plsql_goto_0031_table01(id int, name varchar(10),job varchar(10),hiredate date);

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (120, 'Weiss','shouyin','2020-09-01');

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (121, null,'daogou','2021-05-05');

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (122, 'Weiss2',null,'2019-01-10');

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (123, 'Weiss3','qingjie',null);

create or replace procedure uxdbc_oracle_extension_plsql_goto_0031_procedure01 (v_id int)

language plsql

as $$

declare v_name varchar(10);

v_job varchar(10);

v_hiredate varchar(10);

begin

select name, job, hiredate into v_name, v_job, v_hiredate from uxdbc_oracle_extension_plsql_goto_0031_table01 where id = v_id;

if v_name is null then

goto invalid_emp;

end if;

if v_job is null then

goto invalid_emp;

end if;

if v_hiredate is null then

goto invalid_emp;

end if;

raise info 'this is a validated without errors.';

<<invalid_emp>>

raise info 'this is not a valid employee.';

end;

$$;

call uxdbc_oracle_extension_plsql_goto_0031_procedure01(120);

call uxdbc_oracle_extension_plsql_goto_0031_procedure01(121);

create table uxdbc_oracle_extension_plsql_goto_0031

_table01(id int, name varchar(10),job varchar(10),hiredate date);

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (120, 'Weiss','shouyin',to_date('2020-09-01','YYYY-MM-DD'));

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (121, null,'daogou',to_date('2021-05-05','YYYY-MM-DD'));

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (122, 'Weiss2',null,to_date('2019-01-10','YYYY-MM-DD'));

insert into uxdbc_oracle_extension_plsql_goto_0031_table01 values (123, 'Weiss3','qingjie',null);

create or replace procedure uxdbc_oracle_extension_plsql_goto_0031_procedure01 (v_id int)

is v_name varchar(10);

v_job varchar(10);

v_hiredate varchar(10);

begin

select name, job, hiredate into v_name, v_job, v_hiredate from uxdbc_oracle_extension_plsql_goto_0031_table01 where id = v_id;

if v_name is null then

goto invalid_emp;

end if;

if v_job is null then

goto invalid_emp;

end if;

if v_hiredate is null then

goto invalid_emp;

end if;

dbms_output.put_line('this is a validated without errors.');

<<invalid_emp>>

dbms_output.put_line('this is not a valid employee.');

end;

/

call uxdbc_oracle_extension_plsql_goto_0031_procedure01(120);

call uxdbc_oracle_extension_plsql_goto_0031_procedure01(121);

序号

场景

uxdb

oracle

8

case语句

declare

season int;

temperature int;

begin

season:=20;

temperature:=38;

case season

when 10 then

raise info 'spring';

when 20 then

raise info 'summer';

goto temp_start;

when 30 then

raise info 'autumn';

when 40 then

raise info 'winter';

else

raise info 'is invalid season';

end case;

<<temp_start>>

case

when temperature>30 then

raise info 'so hot';

when temperature>15 then

raise info 'so comfortable';

else

raise info 'so cold';

end case;

end;

/

declare

season int;

temperature int;

begin

season:=20;

temperature:=38;

case season

when 10 then

dbms_output.put_line('spring');

when 20 then

dbms_output.put_line('summer');

goto temp_start;

when 30 then

dbms_output.put_line('autumn');

when 40 then

dbms_output.put_line('winter');

else

dbms_output.put_line('is invalid season');

end case;

<<temp_start>>

case

when temperature>30 then

dbms_output.put_line('so hot');

when temperature>15 then

dbms_output.put_line('so comfortable');

else

dbms_output.put_line('so cold');

end case;

end;

/

序号

场景

uxdb

oracle

9

goto语句可以从exception中跳转到父块中

declare i int :=0;

begin

<<LB1>>

i := i + 1;

raise info '%',i;

begin

<<LB2>>

if i =1 then

raise exception numeric_value_out_of_range;

else

raise exception division_by_zero;

end if;

exception

when numeric_value_out_of_range then

goto LB1;

when division_by_zero then

raise info 'division_by_zero';

when others then

raise info 'error';

end;

end;

/

declare i int :=0;

numeric_value_out_of_range exception;

division_by_zero exception;

begin

<<LB1>>

i := i + 1;

dbms_output.put_line(i);

begin

<<LB2>>

if i =1 then

raise numeric_value_out_of_range;

else

raise division_by_zero;

end if;

exception

when numeric_value_out_of_range then

goto LB1;

when division_by_zero then

dbms_output.put_line('division_by_zero');

when others then

dbms_output.put_line('error');

end;

end;

/

序号

场景

uxdb

oracle

10

goto语句在内外循环之间跳转,最后跳出嵌套循环

declare

s int := 0;

i int := 0;

j int;

begin

<<outer_loop>>

loop

i := i + 1;

j := 0;

<<inner_loop>>

loop

j := j + 1;

s := s + i * j;

if j<=5 then

goto inner_loop;

elsif (i * j) <= 15 then

goto outer_loop;

else

goto end_loop;

end if;

end loop inner_loop;

end loop outer_loop;

<<end_loop>>

raise info 'end_loop';

raise info 'The sum of products equals:% ',s;

end;

/

declare

s int := 0;

i int := 0;

j int;

begin

<<outer_loop>>

loop

i := i + 1;

j := 0;

<<inner_loop>>

loop

j := j + 1;

s := s + i * j;

if j<=5 then

goto inner_loop;

elsif (i * j) <= 15 then

goto outer_loop;

else

goto end_loop;

end if;

end loop inner_loop;

end loop outer_loop;

<<end_loop>>

dbms_output.put_line('end_loop');

dbms_output.put_line('The sum of products equals: '||s);

end;

/

序号

场景

uxdb

oracle

11

如果goto语句过早地退出游标for loop语句,游标将关闭

create table uxdbc_oracle_extension_plsql_goto_0047_table01(id int, name varchar(10),job varchar(10));

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (120, 'Weiss','shouyin');

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (121, 'Weiss1','daogou');

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (122, 'Weiss2','kuaiji');

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (123, 'Weiss3','qingjie');

declare

p varchar(10);

c cursor for select id,name,job from uxdbc_oracle_extension_plsql_goto_0047_table01;

begin

for v in c loop

raise info '%---%---%', v.id,v.name,v.job;

if v.id=120 then

goto end_loop;

end if;

end loop;

<<end_loop>>

raise info 'end_loop,cursor close';

end;

/

create table uxdbc_oracle_extension_plsql_goto_0047_table01(id int, name varchar(10),job varchar(10));

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (120, 'Weiss','shouyin');

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (121, 'Weiss1','daogou');

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (122, 'Weiss2','kuaiji');

insert into uxdbc_oracle_extension_plsql_goto_0047_table01 values (123, 'Weiss3','qingjie');

declare

p varchar(10);

v_id int;

v_name varchar(10);

v_job varchar(10);

cursor c is select id,name,job from uxdbc_oracle_extension_plsql_goto_0047_table01;

begin

open c;

fetch c into v_id,v_name,v_job;

dbms_output.put_line(v_id||'---'||v_name||'---'||v_job);

if v_id=120 then

goto end_loop;

end if;

<<end_loop>>

close c;

dbms_output.put_line('end_loop,cursor close');

end;

/

序号

场景

uxdb

oracle

12

函数递归调用

create or replace function uxdbc_oracle_extension_plsql_goto_0053_function01(x number) returns number

language plsql

as $$

f number;

begin

if x=0 then

f := 1;

return f;

else

goto lb_digui;

end if;

<<lb_digui>>

f := x * uxdbc_oracle_extension_plsql_goto_0053_function01(x-1);

return f;

end;

$$;

select uxdbc_oracle_extension_plsql_goto_0053_function01(5);

create or replace function uxdbc_oracle_extension_plsql_goto_0053_function01(x number) return number

is f number;

begin

if x=0 then

f := 1;

return f;

else

goto lb_digui;

end if;

<<lb_digui>>

f := x * uxdbc_oracle_extension_plsql_goto_0053_function01(x-1);

return f;

end;

/

select uxdbc_oracle_extension_plsql_goto_0053_function01(5) from dual;

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值