兄弟试写的一个procedure
首先判断是否存在记录,存在记录的话update表格
create or replace procedure transfer_terminal (
mer_id in terminal_east.merchantid% type,
ter_id in terminal_east.terminalid% type)
cursor cur_terminal is select * from (select * from terminal_east union select * from terminal_west@west)
as
if ( exists(select * from (select * from merchant_east union select * from merchant_west@west) where merchantid = mer_id))
begin
update cur_terminal
set merchantid = mer_id
where terminalid = ter_id
end;但是报错Error(5,1): PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following: ; is with authid as cluster order using external deterministic parallel_enable pipelined
不知道怎么回事
首先判断是否存在记录,存在记录的话update表格
create or replace procedure transfer_terminal (
mer_id in terminal_east.merchantid% type,
ter_id in terminal_east.terminalid% type)
cursor cur_terminal is select * from (select * from terminal_east union select * from terminal_west@west)
as
if ( exists(select * from (select * from merchant_east union select * from merchant_west@west) where merchantid = mer_id))
begin
update cur_terminal
set merchantid = mer_id
where terminalid = ter_id
end;但是报错Error(5,1): PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following: ; is with authid as cluster order using external deterministic parallel_enable pipelined
不知道怎么回事
2.不建议采用游标处理,直接采用sql判断。
给你排个版,但是你update游标。这不叫程序阿。。create or replace procedure transfer_terminal (
mer_id in terminal_east.merchantid% type,
ter_id in terminal_east.terminalid% type)
cursor cur_terminal is select * from (select * from terminal_east union select * from terminal_west@west)
as
if ( exists(select * from (select * from merchant_east union select * from merchant_west@west) where merchantid = mer_id))
begin
update cur_terminal
set merchantid = mer_id
where terminalid = ter_id
end;
ter_id in terminal_east.terminalid% type) is
cursor cur_terminal is
select *
from (select *
from terminal_east
union
select * from terminal_west@west);
begin
if exists (select *
from (select *
from merchant_east
union
select * from merchant_west@west)
where merchantid = mer_id)) then
update cur_terminal set merchantid = mer_id where terminalid = ter_id;
end if;
end;
假设有一分布式数据库(东,西区)各有2个表格terminal(terminalid, merchantid), 及merchant(merchantid)
现需移动terminal, 例如将terminalid = 1, merchantid = 1 的数据,更新为 terminalid = 1, merchantid = 2
首先需要判断merchantid=2这条记录是否存在,如存在,则更新。
这样的procedure应该怎么写啊?或者有其他的实现方法?