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

1、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('sm

2、ith' 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 d

3、ual; -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' str

4、ing 希望被替换的字符或变量 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 删除右

5、边出现的字符串 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

6、 就取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.正则表达式替换 se

7、lect 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） 第五个是标识第几个匹配组。（默认为

8、全部都替换掉） 第六个是是取值范围： i：大小写不敏感； c：大小写敏感； n：点号 . 不匹配换行符号； m：多行模式； x：扩展模式，忽略正则表达 放在group by后面 22.ORDER BY用于对查询到的结果进行排序输出 union all /把结果集前后连接起来 union 以上将两个表的结果联合在一起。这两个例子会将两个select语句的结果中的重复值进行压缩，也就是结果的数据并不是两条结果的条数的和 union all 重复的结果显示出来可以使用union all 两个表查询出来的数据显示到一起 但字段必须保持一致 union 和 union all都可以将多个结果集合并，而不

9、仅仅是两个，你可以将多个结果集串起来。 使用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_tr

10、iggers 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 (auth

11、or 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 COLU

12、MN 列名; 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_chec

13、k1 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 co

14、nstraint 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 constr

15、aint 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

16、_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; -创

17、建存储过程 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; -一些简单的int

18、erval参数设置例子 描述 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.每天早上

19、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表示每