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'
字数:26
发布于 1 年前