plsql过程语言之uxdb与oracle语法差异
序号 | 场景 | 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 | 标签可以出现在子块前 | 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; / | 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 $$ declare f number; begin if x=0 then f := 1; 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; 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; |