PLSQL

admin管理员组

文章数量:1794759

PLSQL

• 说明

Greatest和Least是Oracle的两个内置函数，可以从一众值中计算出最大或最小值，非常方便。但是它们有一个使用起来不太方便的地方：只要列表中含有一个NULL，计算结果就是NULL了。

如果要比较的值比较少，尚可做一些加工来避免NULL值的影响，比如

greatest(coalesce(col1, col2, col3),coalesce(col2, col3, col1),coalesce(col3, col1, col2))

但如果是值很多的情况，使用自定义函数是一个可以考虑的解决办法。

本文程序的基本思路是利用动态SQL，在执行比较函数之前先剔除掉列表中的空值，从而排除影响。另外，PLSQL自定义子过程是不能使用可变参数列表的，本文程序中使用了SYSTEM.DATE_TBL_TYPE等嵌套表数据类型来间接地达到灵活个数入参的形式。

• 程序

create or replace package cux_comparison_utl isfunction mygreatest(pattern system.date_tbl_type) return date;function mygreatest(pattern system.number_tbl_type) return number;function mygreatest(pattern system.varchar_tbl_type) return varchar2;function myleast(pattern system.date_tbl_type) return date;function myleast(pattern system.number_tbl_type) return number;function myleast(pattern system.varchar_tbl_type) return varchar2;end cux_comparison_utl;
/
create or replace package body cux_comparison_utl isg_greatest varchar2(8) := 'greatest';g_least    varchar2(5) := 'least';function do_val_func(callback varchar2, pattern system.date_tbl_type)return date isa system.date_tbl_type := pattern;l varchar2(500);c number;s number;r date;i number;begini := a.first;loopexit when i is null;if a(i) is null thena.delete(i);elsel := l || ',:' || i;end if;i := a.next(i);end loop;if l is null thenreturn null;end if;l := 'select ' || callback || '(' || substr(l, 2) || ') r from dual';c := dbms_sql.open_cursor;dbms_sql.parse(c, l, dbms_sql.native);i := a.first;loopexit when i is null;dbms_sql.bind_variable(c, ':' || i, a(i));i := a.next(i);end loop;dbms_sql.define_column(c, 1, r);s := dbms_sql.execute_and_fetch(c);dbms_sql.column_value(c, 1, r);dbms_sql.close_cursor(c);return r;exceptionwhen others thenreturn null;end do_val_func;function do_val_func(callback varchar2, pattern system.number_tbl_type)return number isa system.number_tbl_type := pattern;l varchar2(500);c number;s number;r number;i number;begini := a.first;loopexit when i is null;if a(i) is null thena.delete(i);elsel := l || ',:' || i;end if;i := a.next(i);end loop;if l is null thenreturn null;end if;l := 'select ' || callback || '(' || substr(l, 2) || ') r from dual';c := dbms_sql.open_cursor;dbms_sql.parse(c, l, dbms_sql.native);i := a.first;loopexit when i is null;dbms_sql.bind_variable(c, ':' || i, a(i));i := a.next(i);end loop;dbms_sql.define_column(c, 1, r);s := dbms_sql.execute_and_fetch(c);dbms_sql.column_value(c, 1, r);dbms_sql.close_cursor(c);return r;exceptionwhen others thenreturn null;end do_val_func;function do_val_func(callback varchar2, pattern system.varchar_tbl_type)return varchar2 isa system.varchar_tbl_type := pattern;l varchar2(500);c number;s number;r varchar2(500);i number;begini := a.first;loopexit when i is null;if a(i) is null thena.delete(i);elsel := l || ',:' || i;end if;i := a.next(i);end loop;if l is null thenreturn null;end if;l := 'select ' || callback || '(' || substr(l, 2) || ') r from dual';c := dbms_sql.open_cursor;dbms_sql.parse(c, l, dbms_sql.native);i := a.first;loopexit when i is null;dbms_sql.bind_variable(c, ':' || i, a(i));i := a.next(i);end loop;dbms_sql.define_column(c, 1, r, 500);s := dbms_sql.execute_and_fetch(c);dbms_sql.column_value(c, 1, r);dbms_sql.close_cursor(c);return r;exceptionwhen others thenreturn null;end do_val_func;function mygreatest(pattern system.date_tbl_type) return date isbeginreturn do_val_func(callback => g_greatest, pattern => pattern);end mygreatest;function mygreatest(pattern system.number_tbl_type) return number isbeginreturn do_val_func(callback => g_greatest, pattern => pattern);end mygreatest;function mygreatest(pattern system.varchar_tbl_type) return varchar2 isbeginreturn do_val_func(callback => g_greatest, pattern => pattern);end mygreatest;function myleast(pattern system.date_tbl_type) return date isbeginreturn do_val_func(callback => g_least, pattern => pattern);end myleast;function myleast(pattern system.number_tbl_type) return number isbeginreturn do_val_func(callback => g_least, pattern => pattern);end myleast;function myleast(pattern system.varchar_tbl_type) return varchar2 isbeginreturn do_val_func(callback => g_least, pattern => pattern);end myleast;end cux_comparison_utl;
/

• 示例

SQL> select cux_comparison_utl.mygreatest(system.date_tbl_type(null,2                                                            sysdate,3                                                            trunc(sysdate))),4         greatest(null, sysdate, trunc(sysdate))5    from dual;CUX_COMPARISON_UTL.MYGREATEST( GREATEST(NULL,SYSDATE,TRUNC(SY
------------------------------ ------------------------------
2022/2/17 16:47:17             SQL> select cux_comparison_utl.mygreatest(system.varchar_tbl_type(null,2                                                               'Oracle SQL Share',3                                                               'Oracle Dev Share')),4         greatest(null, 'Oracle SQL Share', 'Oracle Dev Share')5    from dual;CUX_COMPARISON_UTL.MYGREATEST( GREATEST(NULL,'ORACLESQLSHARE'
------------------------------ ------------------------------
Oracle SQL Share               SQL> select cux_comparison_utl.mygreatest(system.number_tbl_type(null, 1, 0)),2         greatest(null, 1, 0)3    from dual;CUX_COMPARISON_UTL.MYGREATEST( GREATEST(NULL,1,0)
------------------------------ ------------------1

本文标签： plsql