现在有一个随机update的pl/sql程序,现在想加进去delete和insertion,要求之间有冲突,要怎么做呢,请求帮助!多谢!
CREATE OR REPLACE PROCEDURE "RANDOM_UPD"
AS
TYPE ARRAY_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
CNT_LOOP NUMBER(10) := 0;
CNT_EXIT NUMBER(10) := 0;
CNT_UPDATE NUMBER(10) := 0;
CNT_RANDOMKEY NUMBER(10) := 0;
CREATE OR REPLACE PROCEDURE "RANDOM_UPD"
AS
TYPE ARRAY_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
CNT_LOOP NUMBER(10) := 0;
CNT_EXIT NUMBER(10) := 0;
CNT_UPDATE NUMBER(10) := 0;
CNT_RANDOMKEY NUMBER(10) := 0;
PRICE_RANDOM NUMBER(10);
KEY_RANDOM NUMBER(10);
KEY_ARRAY ARRAY_TYPE;
BEGIN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_UPDATE FROM part;
dbms_output.put_line('the rownumber of random_update'||CNT_UPDATE);
For i IN 1..CNT_UPDATE
LOOP
BEGIN
SELECT TRUNC(dbms_random.value(MIN(p_partkey),MAX(p_partkey))) INTO KEY_RANDOM FROM part;
dbms_output.put_line('random key:'||KEY_RANDOM);
select TRUNC(dbms_random.value(-100,100) )INTO PRICE_RANDOM FROM DUAL;
UPDATE part SET p_retailprice = p_retailprice + abs(PRICE_RANDOM) WHERE p_partkey = KEY_RANDOM;
dbms_output.put_line('updat key==========================:'||KEY_RANDOM);
END;
END LOOP;
dbms_output.put_line('the number of update'||CNT_EXIT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;/
PRICE_RANDOM NUMBER(10);
KEY_RANDOM NUMBER(10);
KEY_ARRAY ARRAY_TYPE;
BEGIN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_UPDATE FROM part;
dbms_output.put_line('the rownumber of random_update'||CNT_UPDATE);
For i IN 1..CNT_UPDATE
LOOP
BEGIN
SELECT TRUNC(dbms_random.value(MIN(p_partkey),MAX(p_partkey))) INTO KEY_RANDOM FROM part;
dbms_output.put_line('random key:'||KEY_RANDOM);
select TRUNC(dbms_random.value(-100,100) )INTO PRICE_RANDOM FROM DUAL;
UPDATE part SET p_retailprice = p_retailprice + abs(PRICE_RANDOM) WHERE p_partkey = KEY_RANDOM;
dbms_output.put_line('updat key==========================:'||KEY_RANDOM);
END;
END LOOP;
dbms_output.put_line('the number of update'||CNT_EXIT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;/
CREATE OR REPLACE PROCEDURE "RANDOM_UPD"
AS
TYPE ARRAY_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
CNT_LOOP NUMBER(10) := 0;
CNT_EXIT NUMBER(10) := 0;
CNT_UPDATE NUMBER(10) := 0;
CNT_RANDOMKEY NUMBER(10) := 0;
CREATE OR REPLACE PROCEDURE "RANDOM_UPD"
AS
TYPE ARRAY_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
CNT_LOOP NUMBER(10) := 0;
CNT_EXIT NUMBER(10) := 0;
CNT_UPDATE NUMBER(10) := 0;
CNT_RANDOMKEY NUMBER(10) := 0;
PRICE_RANDOM NUMBER(10);
KEY_RANDOM NUMBER(10);
KEY_ARRAY ARRAY_TYPE;
BEGIN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_UPDATE FROM part;
dbms_output.put_line('the rownumber of random_update'||CNT_UPDATE);
For i IN 1..CNT_UPDATE
LOOP
BEGIN
SELECT TRUNC(dbms_random.value(MIN(p_partkey),MAX(p_partkey))) INTO KEY_RANDOM FROM part;
dbms_output.put_line('random key:'||KEY_RANDOM);
select TRUNC(dbms_random.value(-100,100) )INTO PRICE_RANDOM FROM DUAL;
UPDATE part SET p_retailprice = p_retailprice + abs(PRICE_RANDOM) WHERE p_partkey = KEY_RANDOM;
dbms_output.put_line('updat key==========================:'||KEY_RANDOM);
END;
END LOOP;
dbms_output.put_line('the number of update'||CNT_EXIT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;/
PRICE_RANDOM NUMBER(10);
KEY_RANDOM NUMBER(10);
KEY_ARRAY ARRAY_TYPE;
BEGIN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_UPDATE FROM part;
dbms_output.put_line('the rownumber of random_update'||CNT_UPDATE);
For i IN 1..CNT_UPDATE
LOOP
BEGIN
SELECT TRUNC(dbms_random.value(MIN(p_partkey),MAX(p_partkey))) INTO KEY_RANDOM FROM part;
dbms_output.put_line('random key:'||KEY_RANDOM);
select TRUNC(dbms_random.value(-100,100) )INTO PRICE_RANDOM FROM DUAL;
UPDATE part SET p_retailprice = p_retailprice + abs(PRICE_RANDOM) WHERE p_partkey = KEY_RANDOM;
dbms_output.put_line('updat key==========================:'||KEY_RANDOM);
END;
END LOOP;
dbms_output.put_line('the number of update'||CNT_EXIT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货