Oracle常用函数及工具

844 0 0

作者:求知若渴,虚心若愚.

一，作者简述

首先介绍一下自己，作者在传统工作上班，做到第一年基本上用的都是oracle,技术非常落后，但是没有办法当时自己的实力确实很弱，需要磨练，也急需生活费自供自己，多的就不说了。

我相信很多的小伙伴跟我一样。懂的自然懂，要好好努力增强自己才是最关键。

二，工具推荐

接下来就给大家推荐一些Oracle的常用函数以及工具。

1.常用函数

select greatest('11','5') from dual;--取字符串最小值

select least(4,1) from dual;--取数字最小值

select wm_concat(列名) from dual;--将一列变成一行

rank() over (partition by 分组列名 order by 排序列名 DESC) --根据某列分组并排序并排名

select listagg(o.serialno, ',') within group(order by o.serialno)from REPORT_FORMS o --列换行可加逗号

SELECT A.COL FROM A WHERE TRANSLATE(A.COL,'*0123456789','*') IS NULL;--筛选一列纯数字（列中包含有别的字符串和数字）：

select * from user_tab_comments a where a.table_name='表名字' ; --查看表解释

COMMENT ON TABLE b_so IS '表解释';--创建表的时候对表做解释

substr('字符串',-8) --对字符串截取函数

replace('V_ENDRERATIOWAYQUO','*',''); --对字符串替换函数

select substr('AAA-BBB',1,instr('AAA-BBB','-',-1)-1) 值 from dual; --截取某字符串之前的所有值
--单列主键
alter table 表名字 add constraint 主键名字 primary key (要设置的主键字段);
--联合主键
alter table ECS_STORE.TF_B_AIR_CONFIG add constraint TF_B_AIR_CONFIG_PK primary key (TYPE_ID , PROVINCE_CODE);

2.创建主键自增

--创建主键自增--

/*第一步：创建表格*/
create table t_user(
id int primary key, --主键，自增长
username varchar(20),
password varchar(20),
type varchar(20)
);

/*第二步：建立自定义的sequence*/
CREATE SEQUENCE user_sequence
increment by 1 -- 每次加几个
start with 1 -- 从1开始计数
nomaxvalue -- 不设置最大值
nocycle -- 一直累加，不循环
nocache -- 不建缓冲区

/*第三步：建立触发器*/
create trigger mem_trig before
insert on t_user for each row when (new.id is null)
begin

select user_sequence.nextval into:new.id from dual;

end;

3.储存过程的创建

CREATE OR REPLACE PACKAGE PKG_RIS_RF IS
/* 不带参数的存储过程 */
-- PROCEDURE WATCH_MONKEY;
/* 带参数的存储过程 */
PROCEDURE PROC_CAL_RF101(start_date IN VARCHAR2, end_date IN VARCHAR2);
END PKG_RIS_RF;

/*带参数的存储过程*/
CREATE OR REPLACE PACKAGE BODY PKG_RIS_RF IS
/*
*带参数的存储过程
*/
PROCEDURE PROC_CAL_RF101(start_date IN VARCHAR2, end_date IN VARCHAR2) IS
/* 参数声明 */
-- name VARCHAR2(12);
BEGIN
-- 处理体
name := 'Hello Oracle!';
-- 异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('CATCH EXCEPTIOIN');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('异常代码:' + sqlcode); --sqlcode代表异常代码
DBMS_OUTPUT.PUT_LINE('异常信息:' + sqlerrm); --sqlerrm代表异常信息
END PROC_CAL_RF101;
END PKG_RIS_RF;

4.dblinke创建

create public database link dblinkname connect to username identified by password
　　 using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =servicename)
)
)';
其中username 是用户名， password是数据库的密码，database_ip是数据库ip地址，servicename是服务名。

5.Oracle快捷键设置

（1首先在本地创建一个txt文本：

（2. 等号前面的是你在plsql中要使用的快捷键，输入那个快捷键就会输出你等号后面的语句了，这里我的快捷键语句也给大家贴出来

iiv=insert into table values()
ii=insert into
up=update
se=select
fr=from
wh=where
de=delete
df=delete from
sf=select * from
scf=select count(1) from
pr=procedure
fu=function
us=update new_tablet set
dt=drop table new_table
ssf=select sum() from
saf=select AVG() from
to=to_date('','YYYY-MM-DD')
co=count(1)
--在两个数据之间
bet=select * from table a where a.time between timemin and timemax