Oracle相关

关联查询时以 id_one 字段去重

select * from a 
left join (select id_one, max(id_two) as idTwo,max(id_three) as idThree from b group by id_one) c 
on a.id_one = c.id_one

表字段新增

ALTER table HI_HPL_MED_REC_LAB add id_field VARCHAR(512);

ALTER table HI_HPL_MED_REC_LAB add id_field DATE;

ALTER table HI_HPL_MED_REC_LAB modify id_field VARCHAR(512);

1if条件

DECODE(expr, [search, result]*, default)

查询当天数据

where AND TO_CHAR (A.dt,'yyyy-mm-dd' ) = TO_CHAR (SYSDATE, 'yyyy-mm-dd')

跨库调用

创建dblink

create database link <link_name> connect to <username> identified by <password> using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = <ip>)(PORT = <port>)))(CONNECT_DATA =(SERVICE_NAME = <service_name>)))';


例子：
create database link dblinktest connect to bbp_test identified by "BSOFT123" using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 10.10.2.65)(PORT = 15211)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

注意：密码要带双引号
dblink 可以跨数据库，也就是说不同的数据库都可以建立link


查dblink
SELECT
	* 
FROM
	dba_db_links;


sql使用方式
SELECT
	* 
FROM
	oauth_refresh_token@dblinkbbp t


添加权限

grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to BBP1230; 


查权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); 

drop database link linkname;
drop public database link linkname;

创建视图

创建包

 --创建包
CREATE 
 OR REPLACE PACKAGE ID_VISMED_PARAM IS FUNCTION SET_ID_VISMED ( VAL NVARCHAR2 ) RETURN NVARCHAR2;
FUNCTION GET_ID_VISMED RETURN NVARCHAR2;

END ID_VISMED_PARAM;

创建包主体

CREATE 
 OR REPLACE PACKAGE BODY ID_VISMED_PARAM IS PARAMVALUE NVARCHAR2 ( 24 );
-- 参数PARAM
FUNCTION SET_ID_VISMED ( VAL NVARCHAR2 ) RETURN NVARCHAR2 IS BEGIN
 PARAMVALUE := VAL;
 RETURN VAL;
 
END;
FUNCTION GET_ID_VISMED RETURN NVARCHAR2 IS BEGIN
 RETURN PARAMVALUE;
 
END;

END ID_VISMED_PARAM;

调用

select * from view where ID_VISMED_PARAM.SET_ID_VISMED('123')='123'