oracle如何调用MySQL的函数_ORACLE 数据库函数使用

select usr.userid userid, decode(count(ba.account_number), '0', ' ', LISTAGG(ba.account_number,',') within group(order by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status)) accountnumber,

NVL(usr.firstname, ' ') firstname, NVL(usr.lastname, ' ') lastname, ar.name userrole,

NVL(sp.lock_reason, decode(sp.status,1,'global.label.lockoutReason.accountExpired','')) reason, 'global.label.reactivate' action

from edx_bsl_umf_user usr, edx_bsl_auth_secprofile sp, edx_bsl_sec_prof_roles_link prl, edx_bsl_auth_secrole ar,

edx_umf_user_acct_link ual, edx_omf_account ba

where usr.userid = sp.userid and sp.profileid = prl.profile_id and prl.role_id = ar.id

and (sp.islocked = 1 or (sp.status=1 and to_date('$reportContext.getCurrentDateTime()','mm/dd/yyyy hh24:mi:ss') > sp.DATE_PASSWD_EXP)) and usr.isactive = 1 and ar.type = 'B2C'

and usr.userid = ual.userid and ual.account_key = ba.account_key and ba.deletedat is null

group by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status

union

select usr.userid userid, decode(count(osa.account_number), '0', ' ', LISTAGG(osa.account_number,',') within group(order by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status)) accountnumber,

NVL(usr.firstname, ' ') firstname, NVL(usr.lastname, ' ') lastname, ar.name userrole,

NVL(sp.lock_reason, decode(sp.status,1,'global.label.lockoutReason.accountExpired','')) reason, 'global.label.reactivate' action

from edx_bsl_umf_user usr, edx_bsl_auth_secprofile sp, edx_bsl_sec_prof_roles_link prl, edx_bsl_auth_secrole ar,

user_service_agreement usa, edx_omf_serviceagreement osa

where usr.userid = sp.userid and sp.profileid = prl.profile_id and prl.role_id = ar.id

and (sp.islocked = 1 or (sp.status=1 and to_date('$reportContext.getCurrentDateTime()','mm/dd/yyyy hh24:mi:ss') > sp.DATE_PASSWD_EXP)) and usr.isactive = 1 and ar.type = 'B2B'

and usr.userid = usa.userid(+) and usa.service_agreement_id = osa.service_key(+)

and osa.delete_date is null

group by usr.userid, usr.firstname, usr.lastname, ar.name, sp.lock_reason, sp.status

]]>

decode：相当于 if else

nvl(args1,args2):当args=null返回，args2

listagg():

测试数据

SQL>SQL> select empno,ename,deptno from scott.emp;

EMPNO ENAME DEPTNO

----- ---------- ------

7369 SMITH 20

7499 ALLEN 30

7521 WARD 30

7566 JONES 20

7654 MARTIN 30

7698 BLAKE 30

7782 CLARK 10

7788 SCOTT 20

7839 KING 10

7844 TURNER 30

7876 ADAMS 20

7900 JAMES 30

7902 FORD 20

7934 MILLER 10

SQL> SELECT deptno,

LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees

FROM scott.emp

GROUP BY deptno;

DEPTNO EMPLOYEES

------ --------------------------------------------------------------------------------

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD