oracle pl/sql块 |

4月 10, 2018 | Nix.Huang

很少写pl/sql, 笔记之，防止下次编写时语法错误，哈哈，囧

declare
CURSOR cur_1 is
select * from .....
cur_product cur_1%rowtype;

--变量定义 
v_Day varchar2(100);
v_night varchar2(100);
v_pos number := 0;
i number :=0;
total_count number :=0;

begin
for cur_product in cur_1 loop
  begin
    select regexp_substr(t.product_name, '[[:digit:]]+日') into v_Day from lvmama_ver.prod_product t where t.product_id=cur_product.product_id;
    update lvmama_ver.prod_product set product_name=regexp_replace(product_name, '[[:digit:]]+晚[[:digit:]]+日', v_Day||v_night)  
	where product_id=cur_product.product_id;
    i:=i+1;
    total_count := total_count + 1;
    if i>=100 then                
        i:=0;
        commit;
    end if;
    EXCEPTION
        when others then
        commit;
        DBMS_OUTPUT.put_line(cur_product.product_id ||'处理失败');
  end;
end loop;
commit;
DBMS_OUTPUT.put_line('prod_product处理行数'|| total_count);
end;

declare

CURSOR cur_1 is

select * from .....

cur_product cur_1%rowtype;

--变量定义

v_Day varchar2(100);

v_night varchar2(100);

v_pos number := 0;

i number :=0;

total_count number :=0;

begin

for cur_product in cur_1 loop

begin

select regexp_substr(t.product_name, '[[:digit:]]+日') into v_Day from lvmama_ver.prod_product t where t.product_id=cur_product.product_id;

update lvmama_ver.prod_product set product_name=regexp_replace(product_name, '[[:digit:]]+晚[[:digit:]]+日', v_Day||v_night)

where product_id=cur_product.product_id;

i:=i+1;

total_count := total_count + 1;

if i>=100 then

i:=0;

commit;

end if;

EXCEPTION

when others then

commit;

DBMS_OUTPUT.put_line(cur_product.product_id ||'处理失败');

end;

end loop;

commit;

DBMS_OUTPUT.put_line('prod_product处理行数'|| total_count);

end;

Posted in: Oracle

知行合一，止于至善

oracle pl/sql块

站内搜索