以下三种方法均可实现周岁年龄的计算：
--算法一：
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求当年的第几季度
from dual
实现字符串乘法，如传入：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
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%RDBMSADMINepgstat.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%apexapxchpwd.sql --修改访问权限： begin DBMS_XDB.SETLISTENERLOCALACCESS (FALSE); end; / --查看apex版本 SELECT VERSION_NO FROM APEX_RELEASE; --通过下面链接访问Apex. https://localhost:8080/apex/apex_admin
oracle自带函数