oracle常用函数函数-方法-例子

1.返回字符串的长度 length(name); select length('kiway开维') from dual; --7
2.返回字符串,并将所有的字符小写 lower('AaBbCcDd'); select lower('AaBbCcDd') from dual; --aabbccdd
3.返回字符串,并将所有的字符大写 upper('AaBbCcDd'); select upper('AaBbCcDd') from dual; --AABBCCDD
4.返回字符串并将字符串的第一个字母变为大写 initcap('smith'); select initcap('smith') from dual; --Smith
5.连接两个字符串 concat('010-','88888888')||'测试23'; select concat('010-','88888888')||'测试23' from dual; --010-********测试23

6.取子字符串,从start开始,取count个 SUBSTR(string,start,count); select substr('130********',3,8) from dual; --08888888

8.在一个字符串中搜索指定的字符,返回发现指定的字符的位置 INSTR(C1,C2,I,J)
C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1(第几次出现) select instr('oracle traning','ra',1,2) from dual; --9
--实例：substr(msg,instr(msg,':')+1)) 截取msg中：后面的内容

7.替换字符 REPLACE('string','s1','s2') string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换的字符串
select replace('Welcome to China','Welcome','i') from dual; -- i to China

9.RPAD和LPAD(粘贴字符) RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符 select lpad(rpad('gao',10,'*'),17,'*')from dual; --*******gao*******
10.LTRIM和RTRIM LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串 select ltrim(rtrim(' gao qian jing ',' '),' ') from dual; --gao qian jing

11.空判断 nvl(str,'aa') 如果str为null或者为空字符串 就返回aa select nvl(null,'1234576') ss from dual;-- 1234576

12.DECODE 中的if-then-else逻辑
decode(value,if1,then1,if2,then2,if3,then3,...,else) 如果value=if1 就取then1 如果value=if2 就取then2......否则取else
select decode(2,2,2,1,1,3,3,5) from dual;-- 2

13.case when语句 case 如果列名2为1就显示1 为2就显示2... 否则显示3 end 列的别名
select case when 2= 1 then 1 when 2= 2 then 2 else 3 end 列的别名 from dual ;
select case when 2= 1 then 1 when 2= 2 then 2 else 3 end names from dual;-- 1


14.正则表达式替换 select REGEXP_REPLACE ('asdasf11fad133pp','[0-9]+','QQQ', 1, 1) AS A from dual; 将字段中的数字用QQQ替换 --asdasfQQQfad133pp
select regexp_replace('XX路1001弄12幢102室', '\d+幢', '') from dual；


6个参数
第一个是输入的字符串
第二个是正则表达式
第三个是替换的字符
第四个是标识从第几个字符开始正则表达式匹配。（默认为1）
第五个是标识第几个匹配组。（默认为全部都替换掉）
第六个是是取值范围：
i：大小写不敏感；
c：大小写敏感；
n：点号 . 不匹配换行符号；
m：多行模式；
x：扩展模式，忽略正则表达

式中的空白字符。


11.对给定的数字取整数 FLOOR select floor(2345.67) from dual; -- 2345

12.CEIL 返回大于或等于给出数字的最小整数 select ceil(3.1415927) from dual; --4
Math.floor(i)小于等于i的最大整数 Math.ceil(i)大于等于i的最小整数





替换 select replace('aaaaaa111bbbbb','1','2') abcd from dual; 输出 aaaaaa222bbbbb
替换删除第一个OR select ltrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual; 输出 xxxxWORLDxxxxOR
替换删除最后一个OR select rtrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual; 输出 ORxxxxWORLDxxxx
替换所有X select trim('x' from 'xxxxWORLDxxxx') aaaaa from dual; WORLD
替换所有X select trim(both 'x' from 'xxxxWORLDxxxx') aaaaa from dual; 输出 WORLD
替换最后X select trim(trailing 'x' from 'xxxxWORLDxxxx') aaaaa from dual; 输出 xxxxWORLD
替换开始X select trim(leading 'x' from 'xxxxWORLDxxxx') aaaaa from dual; 输出 WORLDxxxx
leading参数可以去除字符串右端的空格，而trailing参数则可以去除字符串左端的空格 去除左右空格 select trim(' 11 ') aa from dual;


13.得到当前系统时间：sysdate 转换时间为字符 to_char(date,'yyyy-mm-dd hh24:mi:ss') 转换字符为时间 to_date(str,'yyyy-mm-dd hh24:mi:ss')
明天的日期 to_char((sysdate)+1,'yyyy.mm.dd') 返回日期的最后一天 select last_day(sysdate) from dual
增加或减去月份
select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; 添加2个月
select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual; 减少2个月

获得两个时间相差的天数（整数）
select trunc(to_date('2011-6-13','yyyy-mm-dd') - to_date('2011-6-12','yyyy-mm-dd')) from dual;

select sysdate+(10/24/60) as cc from dual 获取10分钟前得时间
select sysdate-1/24 as cc from dual 获得一个小时前的时间
select trunc(sysdate,'DD') AA,trunc(sysdate,'MM') BB,trunc(sysdate,'yyyy') CC,trunc(sysdate,'day') DDfrom dual;
AA列得到当天，bb列得到当月第一天，CC列得到当年第一天， DD列得到本周第一天

修改数据：select*from table_name for update; select t.*,t.rowid from t ;


14.将给出的字符转换为数字 select to_number('1999');


15:AVG(DISTINCT|ALL) all表示对所有的值求平均值,distinct只对不同的值求平均值

16:MAX(DISTINCT|ALL) 求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

17:MIN(DISTINCT|ALL) 求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

18:STDDEV(distinct|all) 求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差

19.VARIANCE(DISTINCT|ALL) 求协方差

20.GROUP BY 主要用来对一组数进行统计

21.HAVING 对分组统计再加限制条件 HAVING可以放在group by前面 也可以

放在group by后面

22.ORDER BY用于对查询到的结果进行排序输出






union all //把结果集前后连接起来
union 以上将两个表的结果联合在一起。这两个例子会将两个select语句的结果中的重复值进行压缩，也就是结果的数据并不是两条结果的条数的和
union all 重复的结果显示出来可以使用union all 两个表查询出来的数据显示到一起 但字段必须保持一致
union 和 union all都可以将多个结果集合并，而不仅仅是两个，你可以将多个结果集串起来。
使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。但列名则不一定需要相同，oracle会将第一个结果的列名作为结果集的列名。


ctrl+e就能将执行过的历史记录给调出来 查看历史的sql查询记录 或者 查询 SELECT * FROM V$SQL


自定义排序 ORDER BY DECODE(ppmc, '海尔',1,'联想',2,'索尼',3)


select * from dba_users; --所有用户
select * from user_triggers where table_owner = 'EMIS' and table_name = upper('message_out_x'); --所有触发器 --Oracle中如何查看一个表上都有哪些触发器
select * from user_procedures; --所有存储过程
select * from dba_tables; --所有表
select * from dba_tab_cols; -- 所有列




--更改表的结构
1.ALTER TABLE 表名 MODIFY(列名,数据类型);
alter table skate_test modify (author number(10,0) );　在修改列的长度时候,只能编辑比现有字段实际存的长度还要大 在修改列的数据类型的时候,所修改的列必须为空
2.增加一个列 ALTER TABLE 表名 ADD(列名 数据类型); ALTER TABLE skate_test ADD(author NUMBER(38,0) not null);
3.给列改名：ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名; ALTER TABLE skate_test RENAME COLUMN author TO authorer_new
4.删除一个列　ALTER TABLE 表名 DROP COLUMN 列名; alter table skate_test drop column author
5.将一个表改名 ALTER TABLE 当前表名 RENAME TO 新表名; alter table skate_test rename to test_sakte
6.给表加注释　comment column on 表名.列名 is '注释内容'; //修改表的列的注释 COMMENT ON TABLE MOVO_NEW.TEST_SAKTE IS '注释内容'; //修改表的注释


--添加约束
alter table sc add constraint chk_check1 check(grade>=1 and grade<=100)
建立表时创建默认约束：
create table t1 (tname varchar2(20) default 'yang');
为表添加默认约束：
alter table t1 modify (tname varchar2(20) default 'yang');
/*对应约束*/
alter table '表名'
add
--主键
constraint PK_字段/*此为命名规范要求*/ primary key(字段),
alter table MESSAGE_OUT add constraint PK_MESSAGE_OUT primary key (AUTOID)
--唯一约束
constraint UK_字段 unique key(字段),
--默认约束
constrint DF_字段 default('默认值') for/*到*/ 字段,
--检查约束
constraint CK_字段 check(约束。如

：len(字段)>1),
--主外键关系
constraint FK_主表_从表 foreign(外键字段) references 主表(主表主键字段)
go--后续 一般约束是写在建表之后的
--限制时间的约束
alter table 表
add constraint CK_Date
check(列 between '1900-1-1 ' and '2990-1-1')


--实现表自动增长
-- 建立序列
create sequence SEQ_s_msg_Temporary
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
-- oracle建立触发器
create or replace trigger TRIG_INSERT_s_msg_Temporary
before insert on s_msg_Temporary for each row
begin
if :NEW.AUTOID is null then
select SEQ_s_msg_Temporary.nextval into :NEW.AUTOID from dual;
end if;
END TRIG_INSERT_s_msg_Temporary;






Oracle定时调用存储过程实例：
select * from user_jobs;--查询Oracle定时任务
--删除Oracle定时任务
begin
dbms_job.remove(26); -- --26是某定时任务在user_jobs中的job
commit;
end;

--创建存储过程
create table testtest(cur_time date);
create or replace procedure proc_testtest as
begin
insert into testtest(cur_time) values(sysdate);
end;
--添加定时任务
declare jobtesttest number;
begin
dbms_job.submit(jobtesttest,'proc_testtest;',sysdate,null);
commit;
end;

select * from testtest;
--一些简单的interval参数设置例子 描述 Interval参数值(执行数时间)
1.每天运行一次 'SYSDATE + 1'
2.每小时运行一次 'SYSDATE + 1/24'
3.每10分钟运行一次 'SYSDATE + 10/（60*24）'
4.每30秒运行一次 'SYSDATE + 30/(60*24*60)'
5.每隔一星期运行一次 'SYSDATE + 7'
6.不再运行该任务并删除它 NULL
--定时到特定日期或时间的任务例子
1.每天午夜12点 'TRUNC(SYSDATE + 1)'
2.每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
3.每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
4.每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
5.每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
6.每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'
--tjob是系统会自动分配一个任务号jobno；其中next_date 表示第一次执行定时任务的时间，interval表示执行的间隔时间，sysdate+1/1440表示每隔1分钟执行一次，5/1440表示每5分钟执行一次



--orcle 打印至控制台： 要在测试窗口中执行 在dbms输出中显示
begin dbms_output.put_line('hey look, ma!'); end; --控制台直接输出

declare a varchar(12); begin a:='测试输出'; dbms_output.put_line(a); end; -- 有参数传递

declare a varchar(12); begin sms_stat_bydate('2012-10-18',a); end; --调用有返回值的存储过程



create or replace procedure testpre ( statdate varchar2 ,pv_finish out varchar2 )
as

begindate date;
begin
if (statdate='') then
begindate:=sysdate;
end if;

if(statdate<>'') then
begindate:=sysdate;
end if ;

pv_finish := 'y';
end testpre;


declare a varchar(12);
begin
testpre('2012-10-18',a);
dbms_output.put_line(a); --输出到控制台
end;