Oracle总结

Oracle总结

replace()函数：

replace(source,search_str,replace_str)
// 查询t_student表格中name字段，显示结果的时候，将名字中的‘小张’替换成‘张三丰’
select replace(name,'小张','张三丰') from t_student; 

// 把表中name字段中的‘小张’，都替换成‘张三丰’
update t_student set name = replace(name,'小张','张三丰') where ....;

wmsys.wm_concat()函数

oracle数据库中的wmsys.wm_concat()用来连接字符串；
把同组的多行数据连接在一行显示，默认用逗号隔开；

pg库想用的替换方案：
	string_agg(a, ’,’);

instr()函数（俗称：字符串查找函数）

// 格式一：
// instr(源字符串，目标字符串)
instr(string1,string2);

// 格式二：
// instr(源字符串，目标字符串，源字符串的起始位置，出现的第几次)
instr(string1,string2[,start_position[,nth_appearance]]);
// 格式二解析：string2的值要在string1中查找，从start_position给出的位置开始在string1检索，检索第几次出现string2。

// 返回值是检索到的位置

// pgsql中没有该函数，可以自定义
CREATE FUNCTION instr(string varchar,
string_to_search varchar,
beg_index integer) RETURNS integer AS

$$ DECLARE pos integer NOT NULL DEFAULT 0;

temp_str varchar;

beg integer;

length integer;

ss_length integer;

BEGIN

IF beg_index > 0 THEN

temp_str := substring(string FROM beg_index);

pos := position(string_to_search IN temp_str);

IF pos = 0 THEN

RETURN 0;

ELSE

RETURN pos + beg_index - 1;

END IF;

ELSIF beg_index < 0 THEN

ss_length := char_length(string_to_search);

length := char_length(string);

beg := length + beg_index - ss_length + 2;

WHILE beg > 0 LOOP

temp_str := substring(string FROM beg FOR ss_length);

pos := position(string_to_search IN temp_str);

IF pos > 0 THEN

RETURN beg;

END IF;

beg := beg - 1;

END LOOP;

RETURN 0;

ELSE

RETURN 0;

END IF;

END;

$$ LANGUAGE plpgsql STRICT IMMUTABLE;

purge用法介绍

清除oracle 回收站(recyclebin)中的表和索引并释放与其相关的空间，还可清空回收站，或者清除表空间中记录的已删除的部分表空间。 	
注意：purge后不能回滚和恢复。

     在oracle10G之后；oracle提供一种回收站的机制；即闪回技术。闪回技术通常用于快速简单恢复数据库中出现的认为误操作等逻辑错误。发展到11G之后；回收站更加完善；对在可闪回时间内；数据库级别的恢复。
 
      postgresql本身未支持闪回技术的；目前没有像oracle闪回技术的功能；目前postgresql实现这种功能原理是：在drop table时；创建一个名为Trash的schema, 然后将被删除的表移动到这个schema下面.

rename函数介绍(修改表名)

// 方式一：
rename table1 to table2;

// 方式二：
alter table old_table_name to new table_name;

// pgsql库中只能用方式二修改表名

user_indexes和user_ind_columns

查看已经存在的索引
user_indexes:     系统视图存放的是索引的名称以及该索引是否是唯一索引等信息。

user_ind_column:  系统视图存放的是索引名称，对应的表和列等。

对于pg库可以用select * from pg_indexes;

sysdate系统时间

Oracle中获取系统时间用sysdate;
pgsql中获取时间用current_date;

trunc 时间截取

// oracle
select trunc(sysdate, 'yy') "当年第一天" from dual;
select trunc(sysdate, 'yyyy') "当年第一天" from dual;
select trunc(sysdate, 'year') "当年第一天" from dual;
select trunc(sysdate-1) from dual;


select trunc(sysdate, 'mm') "当月第一天" from dual;
select trunc(sysdate, 'month') "当月第一天" from dual;
select trunc(last_day(sysdate)+1) "下个月第一天的0点" from dual;

// pgsql可用date_trunc()代替
date_trunc(text,timestamp);

regexp_like()正则表达式函数

// oracle中自带的函数

// pg库的实现正则表达式的方式：
create or replace function regexp_like(str character varying,reg character varying) 
returns boolean 
language plpgsql 
as $function$ 
declare v_match text;
begin
	
	select
		regexp_matches(str, reg)
	into
		v_match;
	
	if v_match is not null then 
		return true;
	else 
		return false;
	end if;

end;
$function$;


--true
select regexp_like ('123','12');
--false
select regexp_like ('13','24');

rownum

// oracle中的rownum是一个虚拟列，每输出一行，递增1
// pgsql库中没有rownum虚拟列，那么从Oracle迁移到pgsql应该怎么办呢?

// 对于Oracle来说，rownum是系统顺序分配为从查询返回的的行的编号，返回的第1行就是1，第二行就是2；这个伪字段可以用于限制查询返回的总行数，通常可以用与分页，因为Oracle中没有limit；也可用于生成序列值。


1、pgsql输出行号，使用临时序列
create temp sequence if not exists tmp_seq;
alter sequence tmp_seq restart with 1;
select nextval(‘tmp_seq’) as rownum, * from test limit 10;

2、oracel输出行号
ALTER TABLE jobhist ADD seqno NUMBER(3);
UPDATE jobhist SET seqno = ROWNUM;
SELECT seqno, empno, TO_CHAR(startdate,’DD-MON-YY’) AS start, job FROM jobhist;

// 分页
// Oracle
select * from table where rownum < 3;

// pgsql
select * from table limite ; // 等同于 select * from table limit 3 offset 0;
    

查看自定义的函数

—oracle查看所有函数信息

select *From user_source;
select * from user_procedures where object_type = 'FUNCTION';

—pgsql查看所有函数信息

SELECT
pg_proc.proname AS "函数名称",
pg_type.typname AS "返回值数据类型",
pg_proc.pronargs AS "参数个数"
FROM
pg_proc

JOIN pg_type
ON (pg_proc.prorettype = pg_type.oid)
WHERE
pg_type.typname != 'void'
AND pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public');

创建序列号sequence

MySQL有主键自增，auto_increment
但是Qarcle没有主键自增，所以需要创建sequence来支持主键自增。sequence对于Oracle来说就是个序号，和表没有关系；
	1.创建sequence
	create sequence seqTest		--sequence名称
	INCREMENT BY 1				--每次加几个
	START WITH 1				--从1开始计数
	NOMAXvalue					--不设置最大值
	NOCYCLE						--一直累加，不循环
	CACHE 10;					--设置缓存cache个序列，如果系统down掉了，或者其他情况将会导致序列不连续，也可以设置为-----NOCACHE

update的多条件修改

update table_name set column_name =
case
when column_name = 'case_type' then 'goal_value' eles 'eles_value' 
end;

insert into value 和 insert into values

oracle中只有insert into values,没有value；
mysql中有values也有value，插入一条数据，values效率高，插入多条数据时，value效率更高；

e的多条件修改

update table_name set column_name =
case
when column_name = 'case_type' then 'goal_value' eles 'eles_value' 
end;

insert into value 和 insert into values

oracle中只有insert into values,没有value；
mysql中有values也有value，插入一条数据，values效率高，插入多条数据时，value效率更高；