Oracle问题记录

不允许left join更新

https://www.cnblogs.com/lcword/p/13597090.html 不支持下面的update from语法

update a set a.xxx=b.eee
   from a
   left  join b on a.bid=b.id

https://blog.csdn.net/liangmengbk/article/details/103886792 下面的正确

MERGE INTO DS_OA.CSFR001_FILE D
USING (
    -- 更新这些数据
	SELECT B.AZP01, B.SFB_S4,B.SFB_S5,B.RVBUD02,B.TC_AAI01, B.REMARK,B.CSFR001_ACTI
	FROM   DS_OA.CSFR001_FILE B LEFT JOIN  DS_REPORT.CSFR0018680TGPSERVER A
	ON A.AZP01 = B.AZP01 AND NVL(A.SFB_S4,'-') = B.SFB_S4 AND NVL(A.SFB_S5,'-') = B.SFB_S5 AND NVL(A.RVBUD02,'-') = B.RVBUD02
	AND NVL(A.TC_AAI01,'-') = B.TC_AAI01 AND NVL(A.REMARK,'-') = B.REMARK
	WHERE A.AZP01 IS NULL
) E
ON (D.AZP01 = E.AZP01 AND D.SFB_S4 = E.SFB_S4 AND D.SFB_S5 = E.SFB_S5
AND D.RVBUD02 = E.RVBUD02 AND D.TC_AAI01 = E.TC_AAI01 AND D.REMARK = E.REMARK)
WHEN MATCHED THEN 
UPDATE SET D.CSFR001_ACTI = 'N'
when not matched then 
insert(fname,fmoney)values(t.fname,t.fmoney);

获取所有数据库和表名称

在Oracle里准确的来说并不是库，应该是表属组，查询语句如下

  select DISTINCT(OWNER) from all_tables -- 获取所有数据库名称。挺慢的
  select * from all_tables where OWNER = 'AWDATE' --获取此数据库所有表

ORA-00923

未找到要求的 FROM 关键字，去除这个引号或者改为双引号数字要加双引号，AS的引号是双引号  文字不需要加双引号

oracle监听

更改此文件更方便

数据库名称=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 服务名称)
    )
  )  

不是SELECTed表达式 card_date和over_no不在distinct后