Oracle常用语句

剔重语句

select * from( select *, row_number() over 
(partition by 要剔重的字段 order by * desc) rank
from 表) a where a.rank =1

将多行数据进行合并

select province, listagg(to_char(city_name),',') 
within group (order by city_name) as city from 
(select city_code, city_name, province from ncaowii.i_city order by city_code) 
group by province

将合并行字段分开（正则表达式）

select regexp_substr(a.LPBAKNAM, '[^-]+', 1, Level,'i') bankname,LPISSDTE
from (select LPISSDTE,LPBAKNAM from ncaowii.i_cladlps) a
connect by Level <= LENGTH(a.LPBAKNAM) - LENGTH(REGEXP_REPLACE(a.LPBAKNAM, '-', '')) + 1

数学函数:

绝对值：abs()

select abs(-2) value from dual;   ##输出 --(2) 

取整函数（大）：ceil（）

select ceil(-2.001) value from dual;    ##输出 --(-2)

取整函数（小）：floor（）

select floor(-2.001) value from dual;    ##输出--(-3)

取整函数（截取）：trunc（）

select trunc(-2.001) value from dual;  ##输出(-2)

四舍五入：round（）

select round(1.234564,4) value from dual;   ##输出(1.2346)

取平方：Power（m,n）

select power(4,2) value from dual;  ##输出(16)

取平方根:SQRT()

select sqrt(16) value from dual;  ##输出(4)

取随机数:dbms_random(minvalue,maxvalue)

select dbms_random.value() from dual;  (默认是0到1之间)
select dbms_random.value(2,4) value from dual;  （2-4之间随机数）

取符号:Sign()

select sign(-3) value from dual; ##输出(-1)
select sign(3) value from dual; ##输出(1)

取集合的最大值:greatest(value)

select greatest(-1,3,5,7,9) value from dual; ##输出(9)

取集合的最小值:least(value)

select least(-1,3,5,7,9) value from dual;  ##输出(-1)

select  nvl(null,10) value from dual;  ###输出(10)
select nvl(score,10) score from student

PL/SQL

Test Window举例：

DECLARE
  v_co ncaowii.i_posintf.co%TYPE; ##使用原数据类型
  v_polno ncaowii.i_posintf.polno%TYPE; ##使用原数据类型
BEGIN
  select co,polno into v_co, v_polno
  from ncaowii.i_posintf where polno = 'A100060885';
  DBMS_OUTPUT.PUT_LINE(v_co||'的polno编号是：'||v_polno);  ##输出类型
END;

DECLARE
  v_co ncaowii.i_posintf%ROWTYPE; ##使用原数据列名
BEGIN
  select * into v_co
  from ncaowii.i_posintf where polno = 'A100060885';
  DBMS_OUTPUT.PUT_LINE(v_co.co||'的polno编号是：'||v_co.polno);
END;

可执行四则运算：加（+）减（-）乘（）除（/）乘方（**）

dual的使用：

with temp as(
select ‘数据1’ 列名1,‘数据1’ 列名1,‘90’ 平均分 from dual union all