Oracle常用SQL函数整理

--返回ASCII码
select ASCII('A') "A的ASCII码" ,ASCII('a') "a的ASSCII码" from dual ;
--反向
select   CHR(65) from dual;

--连接两个字符串,如果一个为空则返回有值的

select concat('Oracle','11g') "合并后" from dual;

--返回字符串中每个单词首字母大写

select initcap('oracle universal installer') "首字母大写后" from dual;

--返回一个字符在字符串'j'中从第6个开始第3次出现的位置

select INSTR ('jjjjjireutoilkjsdflkdsjfoiuewrlkdsjflkoieuroiu','j',-6,3) from dual ;
--返回字符串长度
select length('oituoireutoilkjsdflkdsjfoiuewrlkdsj') from dual; --6为正数时,j的位置
select length('jjjjjireutoilkj') from dual;                     --6为负数时,j的位置,从左往右数,但是返回值是正数位置

--去掉一个字符串从左边开始,包含'iuyu'的字符
select LTRIM('iuuuuyyuyuyuyuyuidsfdysytu','iuyu') from dual;

--替代字符串中指定的字符为指定的字符
select replace ('opopiiuopoijiuu','o','A') from Dual;

-------------------------------------------数字类函数
--返回大于或等于n的最小整数

select ceil(9.02) from dual; --向上取整

--返回小于或等于n 的最大整数

select floor(8.6345) from dual;--向下取整

--sign 若正返回1 若负返回-1

select sign(9) from dual; --可以根据状态,比较大小

FLOOR——对给定的数字取整数位--向下取整
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
CEIL-- 返回大于或等于给出数字的最小整数--向上取整
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
              4
ROUND——按照指定的精度进行四舍五入
SQL> select round(3.1415926,4) from dual;
ROUND(3.1415926,4)
------------------
            3.1416
TRUNC——按照指定的精度进行截取一个数
SQL> select trunc(3.1415926,4) from dual;
ROUND(3.1415926,4) 3.1415
------------------

------------------------------------------------------------------
select sysdate from   dual;

--取结果集前51条
select *
from (select a.factorvalue,'1','105A'
          from productdata.femriskfactorlistb a
         where 1 = 1
           and a.riskcode = '106001003'
           and a.factortype = 'TextAge'
         order by a.factorvalueorder)
where rownum <= 51;

----正则替换
select regexp_replace('87Y','[^0-9]') from dual;----87

--查询重复记录并删除
DELETE        SDBANKMAPPINGTEST WHERE ID IN(
SELECT (comcode) from SDBANKMAPPINGTEST
group by (comcode) having count(*)>1 );
-- Add/modify columns


alter table test_daoru rename column NEW_FIELD_NAME to name;

--删除一个字段

alter table test_daoru
drop column ;

---快读插入N条数据-----------------------------------------------------------------------------
    declare
    i integer; --定义变量
    begin
    i := 18;
    loop
    /* 插入数据 feeyear缴费期限   period保险期间 */
    insert into sh_1044_nv(textage,sex,feeyear,period,amnt,cod1)
    values(
    TO_CHAR(i||'Y'),'1','1000Y','10Y','','10000'
    );
    /* 参数递增 */
    i := i + 1;
    /* 停止条件 */
    exit when i > 60;
    end loop;
    commit;
    end;

---Oracle自动序列号--------------------------------------------
　　create sequence <序列名称>
　　start with <起始数>
　　increment by <增长量>
　　[maxvalue 值]
　　[minvalue 值]
　　[cycle 当到达最大值的时候，将继续从头开始]
　　[Nocycle -- 一直累加，不循环]
　　[Cache ]

----------------------------------------------------------

--创建一个方法通过生日得到年龄
create or replace function GetAge(brithday varchar2) return number as
v_Age number(3);
begin
select to_char(sysdate, 'yyyy') -
         to_char(to_date(brithday, 'yyyy-mm-dd'), 'yyyy') +
         decode(sign(to_char(sysdate, 'mmdd') -
                     to_char(to_date(brithday, 'yyyy-mm-dd'), 'mmdd')),
                -1,
                -1,
                0,
                0,
                1,
                0)
    into v_Age
    from dual;
    return v_Age;
end;

----------------------------------------------------------
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s,v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
----------------------------------------------------------

--锁表查询
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid;
--==================
--释放SESSION SQL:
--alter system kill session 'sid, serial#';
ALTER system kill session '23, 1647';
----------------------------------------------------------
---查看数据库空间大小
select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;
----------------------------------------------------------
--修改字段长度
alter table test_feilv_01 modify ( textage varchar2(10))
----------------------------------------------------------
--汇总
select decode(grouping(a.scene_id) + grouping(a.enter_id),
                           1,'小计',
                           2,'总计',
                           a.enter_id) "公司编码",
           decode(grouping(a.scene_id), 1, count(*) || '条', a.scene_id) "报文编号"
--sum(f_pagesnumber) sum_pagesnumbers
   from tft_xsl_template a
group by rollup(a.enter_id, a.scene_id);
----------------------------------------------------------
--oracle 中的替换
regexp_replace(1,2,3,4,5,6)

语法说明：1：字段   2：替换的字段 3：替换成什么 4：起始位置（默认从1开始） 5：替换的次数（0是无限次） 6：不区分大小写

----------------------------------------------------------
/*1. 利用游标循环更新、删除MemberAccount表中的数据*/
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT * FROM dbo.MemberAccount) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor ; --读取第一行数据
WHILE @@FETCH_STATUS = 0
　　BEGIN
　　　　--UPDATE dbo.MemberAccount SET UserName = UserName + 'A' WHERE CURRENT OF My_Cursor; --更新
　　　　--DELETE FROM dbo.MemberAccount WHERE CURRENT OF My_Cursor; --删除
　　　　　　FETCH NEXT FROM My_Cursor; --读取下一行数据
　　END
　　CLOSE My_Cursor; --关闭游标
　　DEALLOCATE My_Cursor; --释放游标
    GO

/*2. 利用游标循环更新MemberService表中的数据（更新每个用户所购买服务的时间）*/
　
DECLARE @UserId varchar(50)
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT UserId FROM dbo.MemberAccount) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @UserId; --读取第一行数据(将MemberAccount表中的UserId放到@UserId变量中)
WHILE @@FETCH_STATUS = 0     BEGIN         PRINT @UserId; --打印数据(打印MemberAccount表中的UserId)
UPDATE dbo.MemberService SET ServiceTime = DATEADD(Month, 6, getdate()) WHERE UserId = @UserId; --更新数据
FETCH NEXT FROM My_Cursor INTO @UserId; --读取下一行数据(将MemberAccount表中的UserId放到@UserId变量中)
END CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO
----------------------------------------------------------

巴特西

Oracle常用SQL函数整理

最新文章

热门文章