|
自定义函数实现
CREATE OR REPLACE FUNCTION public.new_time(ts timestamp without time zone, tz1 text, tz2 text) RETURNS timestamp without time LANGUAGE plpgsql STRICT AS $function$ declare res timestamp; begin execute 'set local timezone to '''|| tz2||''''; -- raise notice '%', current_setting('timezone'); select (timestamptz(ts || tz1))::timestamp into res; return res; |
编码解码函数 | 类型转换函数 | to_multi_byte |
自定义函数实现
1、全角转换为半角函数 create or replace function CnFullToHalf(s character varying) returns character varying declare retval character varying; c character varying; l integer; begin l=length(s); retval=''; while l>0 loop c=left(s,1); if c=' ' then -- 全角空格 retval=retval||' '; elsif c>='！' and c<='～' then -- 全角!到~的范围 retval=retval || chr(ascii('!')+ascii(c)-ascii('！')); retval=retval||c; end if; s=substring(s,2,l-1); l=l-1; end loop; return retval; $$language plpgsql strict immutable; |
to_single_byte |
自定义函数实现
2、半角转全角 create or replace function CnHalfToFull(s character varying) returns character varying declare retval character varying; c integer; l integer; begin l=length(s); retval=''; while l>0 loop c=ascii(left(s,1)); if c=32 then -- 空格 retval=retval||' '; elsif c>=33 and c<=126 then retval=retval || chr(ascii('！')+c-ascii('!')); retval=retval||chr(c); end if; s=substring(s,2,l-1); l=l-1; end loop; return retval; $$language plpgsql strict immutable; |
to_timestamp_tz | date_format(date timestamp with time zone,format text)、 | FIRST | FIRST_VALUE(value | LAST_VALUE(value any) | median获取中位数 |
自定义函数
select id,company,salary from ( select e.*, count(*) over(partition by company) x,row_number() over(partition by company order by salary) r_num from employee e) tmp where r_num in (ceil(x/2),x/2+1); |
dbms_assert | dbms_pipe | SQL语法 | dual表查询 | select 1 from dual; |
select
1 from dual;
select 1； |
select
1 from dual;
select 1； |
rownum或者row_number() | rownum或者row_number() limit m,n; | rownum或者row_number() limit m,n; | with as materialize |
with t1 as
select /*+ materialize*/ id from
t_a),
t2 as select id from t_b) select * from t1 ,t2 where t1.id=t2.id; t1 as MATERIALIZED ( select id fromt_a), t2 as select id from t_b) select * from t1 ,t2 where t1.id=t2.id; t1 as MATERIALIZED ( select id fromt_a), t2 as select id from t_b) select * from t1 ,t2 where t1.id=t2.id; |
Oracle返回为相差的天数； | VastbaseG100返回为interval类型 | select extract('epoch' from now()) - extract('epoch' from timestamp '2018-10-01'); | purge |
purge
table temp;
drop table table_name purge; |
DDL回滚 | package、package body | schema、procedure/function | dblink | ROWID |
|
|