oracle 函数
以下三种方法均可实现周岁年龄的计算：
--算法一：
SELECT TRUNC(months_between(sysdate, csrq)/12) AS "Age" FROM wrxxb;
--算法二：
SELECT trunc(to_number(to_char(sysdate,'yyyymmdd'))/10000 - to_number(to_char(to_date('1982-8-28','yyyy-mm-dd'),'yyyymmdd'))/10000) FROM dual;
--算法三：
SELECT to_char(sysdate,'yyyy')-to_char(csrq,'yyyy')+ decode(sign(to_char(sysdate,'mmdd')-to_char(csrq,'mmdd')),-1,-1,0,0,1,0) FROM wrxxb
计算日期年月日周季度等
select
to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyww') as week2, --oracle求当年的第几周， yyyyiw 显示201152
to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyy') as year, --oracle求第几年
to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyymm') as month, --oracle求当年的第几月
to_char(TO_DATE('20110101', 'YYYYMMDD'), 'yyyyddd') as day, --oracle求当年的第几天
to_char(TO_DATE('20110401', 'YYYYMMDD'), 'yyyyq') as quarter, -- oracle求当年的第几季度
to_char(sysdate,'w'), --本月第几周，1.1-1.7第一周
to_char(date'2020-01-07','ww'), --本年第几周，字符串，1.1-1.7第一周
to_char(date'2020-01-07','FMWW') --本年第几周，数值，1.1-1.7第一周
from dual
缺失值填充
--自左向右，返回第一个非空值 select coalesce(null, '1', 'a', null) from dual
日期处理函数
create or replace function my_to_date(p_string in varchar2) return date
as
type fmtArray is table or varchar2(25);
l_fmts fmtArray := fmtArray('dd-mon-yyyy','dd-month-yyyy',
'dd/mm/yyyy','dd/mm/yyyy hh24:mi:ss');
l_return date;
begin
for i in 1 .. l_fmts.count loop
begin
l_return := to_date(p_string, l_fmts(i))
exception when others then null;
end;
EXIT when l_return is not null;
end loop;
if(l_return is null) then
l_return := new_time(to_date('01011970','ddmmyyyy') + 1/24/60/60*p_string, 'GMT', 'EST')
end if;
return l_return;
end;
自定义函数1
CREATE OR REPLACE PACKAGE BODY LIUR IS
function stringToDate(vstr VARCHAR2) return date is
begin
return to_date(vstr,'yyyymmdd');
exception when others then
begin
return to_date(vstr,'yyyy-mm-dd');
exception when others then
return to_date('1901-01-01','yyyy-mm-dd');
end;
end stringToDate;
function stringToNumber(vstr VARCHAR2) return number is
begin
return to_number(vstr);
exception when others then
return null;
end stringToNumber;
PROCEDURE GETLOCK_DATA IS
BEGIN
DELETE er_log WHERE ti<=TRUNC(SYSDATE,'dd')-3;
COMMIT;
DELETE log_LOCK WHERE ti<=TRUNC(SYSDATE,'dd')-3;
COMMIT;
INSERT INTO log_LOCK
select SYSTIMESTAMP TI,object_name,machine,s.sid,s.inst_id,s.serial#,s.program,S.EVENT,
(SELECT SQL_TEXT FROM V$SQLAREA WHERE SQL_ID=S.SQL_ID) sq, s.sql_exec_start
from gv$locked_object l,dba_objects o,gv$session s
where l.object_id=o.object_id and l.session_id=s.sid AND L.INST_ID=S.INST_ID;
COMMIT;
END;
END;
自定义函数2
create table ptab (mydata varchar(20), myid number);
create or replace function myfunc(d_p in varchar2, i_p in number) return number as
begin
insert into ptab (mydata, myid) values (d_p, i_p);
return (i_p * 2);
end;
/
自定义函数实例
实现字符串乘法，如传入：str='2,3,1', times=2, 输出：‘4,6,2’
create or replace function str_mul(str in varchar2,
times in number)
return varchar2 authid current_user is
cursor str_split is
select regexp_substr(str,'[^,]+',1, level) num
from dual
connect by level <= length(str) - length(REGEXP_REPLACE(str, ',', ''))+1;
res varchar2(2000);
--num_str str_split%rowtype;
begin
for num_str in str_split loop
if num_str.num is not null then
res := res || num_str.num*times || ',';
--insert into SalesList select v_kh.keHu, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
--stmt := 'select count(*) from ' || owner || '.' || table_name || ' where ' || col_name || ' is null';
--execute immediate stmt into counts;
end if;
end loop;
--commit;
return substr(res, 1, length(res)-1);
end;
--select str_mul('3,,2,1', 3) from dual
listagg溢出截断，12c以上版本
select listagg(x, ';' on overflow truncate) within group (order by x) from dual
json解析 12c以上版本，如果11g版本可使用apex_json包（5.0以上）的xml_table/to_xmltype()方法，但是效率会低很多；还可尝试pl/json(用PL / SQL编写的通用JSON对象)
--从JSONArray中取数据
SELECT jt.*
FROM JSON_TABLE('[
{
"device_type_id": 1,
"amount": 120,
"remarks": ""
},
{
"device_type_id": 2,
"amount": 122,
"remarks": ""
},
{
"device_type_id": 3,
"amount": 123,
"remarks": "11111111111"
}
]',
'$'COLUMNS(
NESTED PATH '$[*]' COLUMNS (
device_type_id VARCHAR2(32) PATH '$.device_type_id',
amount VARCHAR2(32) PATH '$.amount',
remarks VARCHAR2(32) PATH '$.remarks')
)
) AS jt;
--从JSONObject对象中取数据
SELECT jt.*
FROM JSON_TABLE('{
"detailed": [
{
"device_type_id": 1,
"amount": 120,
"remarks": ""
},
{
"device_type_id": 2,
"amount": 122,
"remarks": ""
}
]
}',
'$'COLUMNS(
NESTED PATH '$.detailed[*]' COLUMNS (
device_type_id VARCHAR2(32) PATH '$.device_type_id',
amount VARCHAR2(32) PATH '$.amount',
remarks VARCHAR2(32) PATH '$.remarks')
)
)
AS jt;
--从三层嵌套的JSONObject对象中取数据
SELECT jt.*
FROM JSON_TABLE('{
"certificate": "14531209693428a799591c0248bb95c3",
"rows": [
{
"odo_id": "0",
"odo_no": "ZC-FY-20170217001",
"stamp": "2017-02-24",
"order_no": "ZC-DD-20170210001",
"partners_id": "213",
"shipping_address": "深圳市福田区科技园南区T2-B栋601",
"contacts": "李魁",
"tel": "13510141822",
"self_mention": "0",
"detailed": [
{
"device_type_id": "1",
"amount": "121",
"remarks": ""
},{
"device_type_id": "2",
"amount": "122",
"remarks": ""
}
]
},{
"odo_id": "0",
"odo_no": "ZC-FY-20170217002",
"stamp": "2017-02-24",
"order_no": "ZC-DD-20170210001",
"partners_id": "213",
"shipping_address": "深圳市福田区科技园南区T2-B栋601",
"contacts": "李魁",
"tel": "13510141822",
"self_mention": "0",
"detailed": [
{
"device_type_id": "3",
"amount": "123",
"remarks": ""
},{
"device_type_id": "4",
"amount": "124",
"remarks": ""
}
]
}
]}',
'$'COLUMNS(
requestor VARCHAR2(32) PATH '$.certificate',
NESTED PATH '$.rows[*]' COLUMNS (
odo_no VARCHAR2(32) PATH '$.odo_no',
NESTED PATH '$.detailed[*]' COLUMNS (
phone_type VARCHAR2(32) PATH '$.device_type_id',
phone_num VARCHAR2(20) PATH '$.amount'
)
)
)
) AS jt;
json_table \ xml_table 对比，示例中json_table用时1.5秒，而xmltable用时27秒
-- json_table
with eqdata as (
select e.id,
e.title,
e.mag
from earthquake_json j, json_table(
document,
'$.features[*]'
columns(
id varchar2(20) path '$.id',
mag number path '$.properties.mag',
title varchar2(200) path '$.properties.title' ) ) e
), minmax as (
select min(e.mag) minmag, max(e.mag) maxmag
from eqdata e
)
select e.id,
e.title,
e.mag
from eqdata e, minmax m
where e.mag in ( m.minmag, m.maxmag )
-- xmltable
with eqdata as (
select e.id,
e.title,
e.mag
from earthquake_json j, xmltable(
'/json/features/row'
passing apex_json.to_xmltype( j.document )
columns
id varchar2(20) path 'id/text()',
mag number path 'properties/mag/text()',
title varchar2(200) path 'properties/title/text()' ) e
), minmax as (
select min(e.mag) minmag, max(e.mag) maxmag
from eqdata e
)
select e.id,
e.title,
e.mag
from eqdata e, minmax m
where e.mag in ( m.minmag, m.maxmag )
xmltable示例
SELECT * FROM XMLTABLE(
'$B/DEAL_BASIC/USER_DEAL_INFO'
PASSING
XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>
<DEAL_BASIC>
<USER_DEAL_INFO>
<USER_DEAL_ID>1000100001</USER_DEAL_ID>
<DEAL_INURE_TIME>20081130</DEAL_INURE_TIME>
<DEAL_EXPIRE_TIME>30000101</DEAL_EXPIRE_TIME>
<DEAL_CREATE_TIME>20081130</DEAL_CREATE_TIME>
</USER_DEAL_INFO>
<USER_DEAL_INFO>
<USER_DEAL_ID>1000100002</USER_DEAL_ID>
<DEAL_INURE_TIME>20081131</DEAL_INURE_TIME>
<DEAL_EXPIRE_TIME>30000102</DEAL_EXPIRE_TIME>
<DEAL_CREATE_TIME>20081131</DEAL_CREATE_TIME>
</USER_DEAL_INFO>
</DEAL_BASIC>') AS B
COLUMNS
USER_DEAL_ID VARCHAR2(50) PATH
'/USER_DEAL_INFO/USER_DEAL_ID/text()',
DEAL_INURE_TIME VARCHAR2(50) PATH
'/USER_DEAL_INFO/DEAL_INURE_TIME',
DEAL_EXPIRE_TIME VARCHAR2(50) PATH
'/USER_DEAL_INFO/DEAL_EXPIRE_TIME',
DEAL_CREATE_TIME VARCHAR2(50) PATH
'/USER_DEAL_INFO/DEAL_CREATE_TIME')
apex启用：Apex在oracle 11g Release 2版本中是默认安装的。但安装后没有启用。可以使用下面方法启用它。
--显示用户 show user spool apex --查看安装的Apex详情。 select * from dba_registry where comp_id = 'APEX'; -- @%oracle_home%\RDBMS\ADMIN\epgstat.sql --查看HTTP所使用的port，其中0表示没有开启。 select dbms_xdb.getHTTPPort from dual; --设置port。 begin dbms_xdb.setHTTPPort(8080); commit; end; / --启用匿名帐号。 ALTER USER ANONYMOUS ACCOUNT UNLOCK; --修改密码：admin/admin @%Oracle_home%\apex\apxchpwd.sql --修改访问权限： begin DBMS_XDB.SETLISTENERLOCALACCESS (FALSE); end; / --查看apex版本 SELECT VERSION_NO FROM APEX_RELEASE; --通过下面链接访问Apex. https://localhost:8080/apex/apex_admin
oracle自带函数
