当前位置: 代码迷 >> Oracle管理 >> 执行提示:无效的SQL语句解决方法
  详细解决方案

执行提示:无效的SQL语句解决方法

热度:22   发布时间:2016-04-24 05:55:13.0
执行提示:无效的SQL语句
SQL语句如下:


 if exists ((SELECT ID,
  name,
  phone,
  mobile,
  address,
  buyMachineType,
  buyDate,
  buyAddress,
  buyPrice,
  customerType,
  vipCard,
  content,
  email,
  area_id,
  area_code,
  area_name,
  importDirection
  FROM tbl_customer_amt_temp A
  WHERE A.IMPORTDIRECTION=1
)A
ON (B.ID=A.ID))
begin update tbl_customer_temp B set
  B.name=A.name,
  B.phone=A.phone,
  B.mobile=A.mobile,
  B.address=A.address,
  B.buyMachineType=A.buyMachineType,
  B.buyDate=A.buyDate,
  B.buyAddress=A.buyAddress,
  B.buyPrice=A.buyPrice,
  B.customerType=A.customerType,
  B.vipCard=A.vipCard,
  B.content=A.content,
  B.email=A.email,
  B.area_id=A.area_id,
  B.area_code=A.area_code,
  B.area_name=A.area_name,
  B.importDirection=A.importDirection
 end
 else if ((SELECT ID,
  name,
  phone,
  mobile,
  address,
  buyMachineType,
  buyDate,
  buyAddress,
  buyPrice,
  customerType,
  vipCard,
  content,
  email,
  area_id,
  area_code,
  area_name,
  importDirection
  FROM tbl_customer_amt_temp A
  WHERE A.IMPORTDIRECTION=1
)A
ON (B.ID <> A.ID))
  begin
  insert tbl_customer_temp B (ID,name,
  phone,
  mobile,
  address,
  buyMachineType,
  buyDate,
  buyAddress,
  buyPrice,
  customerType,
  vipCard,
  content,
  email,
  area_id,
  area_code,
  area_name,
  importDirection)
  values (A.ID,A.name,A.phone,A.mobile,A.address,A.buyMachineType,
A.buyDate,A.buyAddress,A.buyPrice,A.customerType,A.vipCard,A.content,A.email,A.area_id,A.area_code,
  A.area_name,
  A.importDirection)
  end


麻烦帮忙看下是哪里错了

------解决方案--------------------
MERGE 语句
提供了对表根据条件进行插入或者更新的能力。
如果行存在则执行UPDATE,如果不存在则执行INSERT。
避免了单独的修改。
提高了性能,更便于使用。
对于数据库应用很有益。
语法:别名AL
MERGE INTO table_name(目的表) table_alias USING (table/view/sub_query)//数据来源
(可以用子查询) alias(别名)
on(连接条件)
WHEN MATCHED THEN UPDATE SET(关键字) (修改目的表)
目的表AL.col1=原表AL.col_var1
目的表AL.col2=原表AL.col2_var2
WHEN NOT MATCHEN THEN(关键字)
INSERT (目的表AL.COL_LIST)
VALUES(原表AL.COL_VARS);(插入原表)
create table newtable_name(新表) as select * from oldtable_name(原表) where 1=0;
将原表中的结构复制到新表中,但具体的数据项不进行复制。
------解决方案--------------------
CREATE PROCEDURE TEST AS 
v_count int :=0;
begin
  
SELECT count(1)
into v_count
FROM IMPORTDIRECTION 
WHERE IMPORTDIRECTION=1;

if v_count>0 then 
  
update tbl_customer_temp B set( 
name, phone, mobile, address, buyMachineType, buyDate, 
buyAddress, buyPrice, customerType, vipCard, content, 
email, area_id, area_code, area_name, importDirection )
= (select A.name,A.phone,A.mobileA.address,A.buyPrice,A.buyMachineType,A.buyDate,A.buyAddress,
A.customerType,A.vipCard,A.content,A.email,A.area_id,A.area_code,
A.area_name,A.importDirection
from IMPORTDIRECTION A where B.ID=A.ID)
where exists (select 1 from IMPORTDIRECTION where B.ID=A.ID);
  相关解决方案