oracle基本函数

包含关系函数

1. in 等于括号内任一成员 如  select * from dept where deptno in ('10','20','30','40');

2. not in  不等于括号内任一成员 如  select * from dept where deptno not in ('10','20','30','40');

3. exists 和in函数类似；如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

4. between A and b 大于等于A与小于等于B  如 select * from emp where sal beteen 1000 and 1500;

5. not between A and b 不大于等于A与小于等于B  如 select * from emp where sal beteen 1000 and 1500;

6. like '%A%'  包含给定子串A 如 select * from emp where ename like '%A%';

7. >=  大于或等于　　　　<=   小于或等于

nvl函数

1. nvl(n,r)  n为空时返回r

 

替换函数 

1. DECODE函数

decode (expression, search_1, result_1)

　　decode (expression, search_1, result_1, search_2, result_2)

　　decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

　　

　　decode (expression, search_1, result_1, default)

　　decode (expression, search_1, result_1, search_2, result_2, default)

　　decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

　　

　　decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值

 

2. CASE函数

case when 表达式1 then 表达式2 else 表达式3 end

当表达式1为真返回表达式2的值，否则返回表达式3的值

 

数值型常用函数　

 函数    返回值                       样例　　　　　　　　　　　　  显示

ceil(n) 大于或等于数值n的最小整数　　select ceil(10.6) from dual;   11

floor(n)  小于等于数值n的最大整数　  select floor(10.6) from dual;  10

mod(m,n)  m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 

power(m,n)  m的n次方　　　　　　　　 select power(3,2) from dual;   9

round(n,m)  将n四舍五入,保留小数点后m位　　select round(1234.5678,2) from dual;  1234.57

sign(n)    若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1  select sign(12) from dual;  1

sqrt(n)   n的平方根　　　　　　　　　select sqrt(25) from dual ;   5

常用字符函数

initcap(char) 把每个字符串的第一个字符换成大写　　select initicap('mr.ecop') from dual;  Mr.Ecop

lower(char)   整个字符串换成小写　　　　　　　　　select lower('MR.ecop') from dual;    mr.ecop

replace(char,str1,str2)  字符串中所有str1换成str2  select replace('Scott','S','Boy') from dual;  Boycott

substr(char,m,n)  取出从m字符开始的n个字符的子串　　select substr('ABCDEF',2,2) from dual;  BC

length(char)    求字符串的长度　　　　select length('ACD') from dual;  3

|| 并置运算符　　　 select 'ABCD'||'EFGH' from dual;   ABCDEFGH

Instr(char, search_string, startpos, occurrence)

    从字符串中，从位置startpos 开始,寻找search_string 第occurrence次的位置

  select instr('cvmn','v',1,1) from dual;

日期型函数

常用日期型函数

sysdate     当前日期和时间  select sysdate from dual;

last_day  　本月最后一天    select last_day(sysdate) from dual;

add_months(d,n)　当前日期d后推n个月  select add_months(sysdate,2) from dual;

months_between(d,n) 日期d和n相差月数  select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual;

next_day(d,day)  d后第一周指定day的日期 select next_day(sysdate,'Monday') from dual;

day 格式　　有　　'Monday'  星期一　

　　　　　　　　　'Tuesday' 星期二

                  'wednesday' 　星期三

                  'Thursday'  星期四

                  'Friday'  星期五

                  'Saturday' 星期六

　　　　　　　　　'Sunday'  星期日

 

 

特殊格式的日期型函数

  Y或YY或YYY     年的最后一位，两位，三位    select to_char(sysdate,'YYY') from dual;

  Q              季度,1-3月为第一季度　　　　select to_char(sysdate,'Q') from dual;

  MM            　月份数　　　　　　　　　　　select to_char(sysdate,'MM') from dual;

  RM             月份的罗马表示              select to_char(sysdate,'RM') from dual;  IV

  month          用9个字符表示的月份名       select to_char(sysdate,'month') from dual;

  ww             当年第几周　　　　　　　　　select to_char(sysdate,'ww') from dual;

  w              本月第几周　　　　　　　　　select to_char(sysdate,'w') from dual;

  DDD            当年第几天,一月一日为001 ,二月一日032  select to_char(sysdate,'DDD') from dual;

  DD             当月第几天                  select to_char(sysdate,'DD') from dual;

  D              周内第几天                  select to_char(sysdate,'D') from dual;  如　sunday

  DY             周内第几天缩写　　　　　　　select to_char(sysdate,'DY') from dual; 如　sun

  hh12           12小时制小时数　　　　　　　select to_char(sysdate,'hh12') from dual;

  hh24           24小时制小时数　　　　　　　select to_char(sysdate,'hh24') from dual;

  Mi            分钟数　　　　　　　　　　　 select to_char(sysdate,'Mi') from dual;

  ss            秒数　　　　　　　　　　　　 select to_char(sysdate,'ss') from dual;

  select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual;

 

转换函数

  to_number()  将合法的数字字符串   select to_number('88877') from dual;   88877

  to_char()    将数字转换为字符串　 select to_char(88877)  from dual;    '88877'

  

 

  set serveroupt on;

  dbms_output.put_line('hello world')  

  ---输出hello world

 

特殊函数

 set heading off   由于正在创建数据文件，不需要表头

set pagesize 0  不需要分页

set linesize 80  设置行的最大尺寸

set echo off 告诉sql plus 在执行语句时，不要回显语句

set feedback off 禁止sql plus 显示有多少满足查询的行被检索到

 

 col sales format 999,999,999

 append 添加文本到当前行尾

 change/old/new/      在当前行用新的文本代替旧的文本

　change/text      从当前行删除ｗｅｎｂ

　del          删除当前行

　input text 　在当前行之后添加一行

　list  显示缓冲区中的所有行

　list n 显示缓冲区中的第n行

  list m n  显示m到n

 

rpad(c_start,n,c_repeat)  用户重复的字符串

BitAnd(n,m) 按位与

BitOr(n,m) 按位或

STDDEV 计算标准偏差

 

查询表的大小：

select segment_name, bytes 

from user_segments 

where segment_type = 'TABLE'

and segment_name='T_QL_REPORT';

查询表空间的大小：

Select Tablespace_Name, Sum(bytes) / 1024 / 1024

  From Dba_Segments

 Where Dba_Segments.tablespace_name='GXMES' --表空间名为‘GXMES’

 Group By Tablespace_Name;

查询表的列名：

select column_name from user_tab_columns where table_name ='MT_RT_USERINFO';

 

select cname from col where tname ='MT_RT_USERINFO';

 

删除函数

 

oracle 删除一个表中全部的数据，速度很快 TRUNCATE

关键字: oracle 删除 表 全部 数据 速度 快 

TRUNCATE TABLE tableName 

 

tableName是要删除全部行的表的表名 

 

TRUNCATE TABLE 虽然与不带 WHERE 的 DELETE 语句相同 

但 TRUNCATE TABLE 速度更快而且占资源少速度快 

 

TRUNCATE TABLE 不记录日志所以不能激活触发器 

 

TRUNCATE TABLE 不能用于参与了索引视图的表 

判断是否为数字型

SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;

 

 

表解锁

select a.sid, b.owner, object_name, object_type, a.LMODE,a.REQUEST from v$lock a, all_objects b where type='TM' and a.ID1 = b.object_id; alter system kill session ' sid, s.serial#,';select * from v$session t where t.SID = '119'

 

数据库闪滚

flashback table MT_RT_SYSTEMMENU to timestamp to_timestamp('2010-05-17 18:00:00’,'yyyy-MM-dd hh24:mi:ss');

 

拼接聚合函数

strcat() 值拼接 注：公司自己写的

 

查询表关联

select *

  from user_constraints t

 where t.r_constraint_name in

       (select q.constraint_name

          from user_constraints q

         where q.table_name = 'T_QL_DATA_COLLECTION_CFG')

 

 

 

C#在后台报无效字符。

 

ORA-00911: invalid character

 

1.可能是你的 SQl最后面加了分号

 

2.可能是你安装Oracle问题

 

客户端  服务端都看下

 

打开注册表编辑下

 

Regedit.exe ---》 HKEY_LOCAL_MACHINE ---》SOFTWARE ---》 ORACLE-HOME

 

将这个节点NLS_LANG设置为

 

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

 

逐行累加函数

select item,sum(qty)over(partition by item order by order_no) as h from b

 

select Item, 

? ?? ?? ?sum(Qty) over(order by Item) as "累计"

from

(

??select Item, Item from 表名

??order by Item

)