LINE/COL ERROR
-------- -----------------------------------------------------------------
47/4 PL/SQL: SQL Statement ignored
47/24 PL/SQL: ORA-00907: missing right parenthesis
49/12 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if
CREATE OR REPLACE TRIGGER DML_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON PART
DECLARE
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;
CNT_INSERT NUMBER(10) := 0;
KEY_INSERT NUMBER(10);
CNT_DELETE NUMBER(10) := 0;
KEY_ARRAY ARRAY_TYPE;BEGIN IF INSERTING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_INSERT FROM part;
dbms_output.put_line('the rownumber of insert'||CNT_INSERT);
For i IN 1..CNT_INSERT
LOOP
BEGIN
Select MAX(p_partkey) INTO KEY_INSERT FROM dual;
dbms_output.put_line('random key:'||KEY_INSERT);
KEY_INSERT := KEY_INSERT + 1 ;
INSERT INTO part VALUES (100, '', '','','',38,'', 1000);
END;
END LOOP;ELSIF UPDATING THEN
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;ELSIF DELETING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_DELETE FROM part;
dbms_output.put_line('the number of random_delete'||CNT_DELETE);
FOR i IN 1..CNT_DELETE
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);
IF( NOT KEY_ARRAY.EXISTS(KEY_RANDOM)) THEN
BEGIN
DELETE FROM part WHERE p_partkey = KEY_RANDOM;
KEY_ARRAY(KEY_RANDOM) := KEY_RANDOM;
dbms_output.put_line('the key be deleted==========================:'||KEY_RANDOM);
END;
END IF;
END;
END LOOP;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;
/
-------- -----------------------------------------------------------------
47/4 PL/SQL: SQL Statement ignored
47/24 PL/SQL: ORA-00907: missing right parenthesis
49/12 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if
CREATE OR REPLACE TRIGGER DML_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON PART
DECLARE
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;
CNT_INSERT NUMBER(10) := 0;
KEY_INSERT NUMBER(10);
CNT_DELETE NUMBER(10) := 0;
KEY_ARRAY ARRAY_TYPE;BEGIN IF INSERTING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_INSERT FROM part;
dbms_output.put_line('the rownumber of insert'||CNT_INSERT);
For i IN 1..CNT_INSERT
LOOP
BEGIN
Select MAX(p_partkey) INTO KEY_INSERT FROM dual;
dbms_output.put_line('random key:'||KEY_INSERT);
KEY_INSERT := KEY_INSERT + 1 ;
INSERT INTO part VALUES (100, '', '','','',38,'', 1000);
END;
END LOOP;ELSIF UPDATING THEN
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;ELSIF DELETING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_DELETE FROM part;
dbms_output.put_line('the number of random_delete'||CNT_DELETE);
FOR i IN 1..CNT_DELETE
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);
IF( NOT KEY_ARRAY.EXISTS(KEY_RANDOM)) THEN
BEGIN
DELETE FROM part WHERE p_partkey = KEY_RANDOM;
KEY_ARRAY(KEY_RANDOM) := KEY_RANDOM;
dbms_output.put_line('the key be deleted==========================:'||KEY_RANDOM);
END;
END IF;
END;
END LOOP;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;
/
AFTER DELETE OR INSERT OR UPDATE ON PART
DECLARE
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;
CNT_INSERT NUMBER(10) := 0;
KEY_INSERT NUMBER(10);
CNT_DELETE NUMBER(10) := 0;
KEY_ARRAY ARRAY_TYPE;BEGIN IF INSERTING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_INSERT FROM part;
dbms_output.put_line('the rownumber of insert'||CNT_INSERT);
For i IN 1..CNT_INSERT
LOOP
BEGIN
Select MAX(p_partkey) INTO KEY_INSERT FROM dual;
dbms_output.put_line('random key:'||KEY_INSERT);
KEY_INSERT := KEY_INSERT + 1 ;
INSERT INTO part VALUES (100, '', '','','',38,'', 1000);END;
END LOOP;ELSIF UPDATING THEN
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;ELSIF DELETING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_DELETE FROM part;
dbms_output.put_line('the number of random_delete'||CNT_DELETE);
FOR i IN 1..CNT_DELETE
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);
IF( NOT KEY_ARRAY.EXISTS(KEY_RANDOM)) THEN
BEGIN
DELETE FROM part WHERE p_partkey = KEY_RANDOM;
KEY_ARRAY(KEY_RANDOM) := KEY_RANDOM;
dbms_output.put_line('the key be deleted==========================:'||KEY_RANDOM);
END;
END IF;
END;
END LOOP;
end if;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;
LINE/COL ERROR
-------- -----------------------------------------------------------------
47/4 PL/SQL: SQL Statement ignored
47/24 PL/SQL: ORA-00907: missing right parenthesis
49/12 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if
AFTER DELETE OR INSERT OR UPDATE ON emp
DECLARE
TYPE ARRAY_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
CNT_LOOP NUMBER := 0;
CNT_EXIT NUMBER := 0;
CNT_UPDATE NUMBER := 0;
CNT_RANDOMKEY NUMBER := 0;
PRICE_RANDOM NUMBER;
KEY_RANDOM NUMBER;
KEY_ARRAY ARRAY_TYPE;
CNT_INSERT NUMBER := 0;
KEY_INSERT NUMBER;
CNT_DELETE NUMBER := 0;
KEY_ARRAY ARRAY_TYPE;BEGIN IF INSERTING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_INSERT FROM part;dbms_output.put_line('the rownumber of insert'||CNT_INSERT);
For i IN 1..CNT_INSERT
LOOP
BEGIN
--Select MAX(p_partkey) INTO KEY_INSERT FROM dual;
KEY_INSERT:=CNT_INSERT;
dbms_output.put_line('random key:'||KEY_INSERT);
KEY_INSERT := KEY_INSERT + 1 ;
INSERT INTO part VALUES (100, '', '','','',38,'', 1000);END;
END LOOP;ELSIF UPDATING THEN
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;ELSIF DELETING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_DELETE FROM part;
dbms_output.put_line('the number of random_delete'||CNT_DELETE);
FOR i IN 1..CNT_DELETE
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);
IF( NOT KEY_ARRAY.EXISTS(KEY_RANDOM)) THEN
BEGIN
DELETE FROM part WHERE p_partkey = KEY_RANDOM;
KEY_ARRAY(KEY_RANDOM) := KEY_RANDOM;
dbms_output.put_line('the key be deleted==========================:'||KEY_RANDOM);
END;
END IF;
END;
END LOOP;
END IF;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;
把from part都改成dual
-------- -----------------------------------------------------------------
18/1 PL/SQL: SQL Statement ignored
18/38 PL/SQL: ORA-00904: "P_PARTKEY": invalid identifier
23/3 PL/SQL: SQL Statement ignored
23/15 PL/SQL: ORA-00904: "P_PARTKEY": invalid identifier
26/8 PL/SQL: SQL Statement ignored
26/20 PL/SQL: ORA-00947: not enough values
32/1 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00904: "P_PARTKEY": invalid identifier
37/3 PL/SQL: SQL Statement ignored
37/53 PL/SQL: ORA-00904: "P_PARTKEY": invalid identifier
47/4 PL/SQL: SQL Statement ignored
47/41 PL/SQL: ORA-00904: "P_PARTKEY": invalid identifier
52/8 PL/SQL: SQL Statement ignored
52/58 PL/SQL: ORA-00904: "P_PARTKEY": invalid identifier
56/7 PL/SQL: Statement ignored
56/15 PLS-00371: at most one declaration for 'KEY_ARRAY' is permitted
SQL> CREATE OR REPLACE TRIGGER DML_TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON PART
DECLARE
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;
CNT_INSERT NUMBER(10) := 0;
KEY_INSERT NUMBER(10);
CNT_DELETE NUMBER(10) := 0;
KEY_ARRAY ARRAY_TYPE;BEGIN IF INSERTING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_INSERT FROM DUAL;
dbms_output.put_line('the rownumber of insert'||CNT_INSERT);
For i IN 1..CNT_INSERT
LOOP
BEGIN
Select MAX(p_partkey) INTO KEY_INSERT FROM DUAL;
dbms_output.put_line('random key:'||KEY_INSERT);
KEY_INSERT := KEY_INSERT + 1 ;
INSERT INTO part VALUES (100, '', '','','',38,'', 1000);
END;
END LOOP;ELSIF UPDATING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_UPDATE FROM DUAL;
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 DUAL;
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;ELSIF DELETING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_DELETE FROM DUAL;
dbms_output.put_line('the number of random_delete'||CNT_DELETE);
FOR i IN 1..CNT_DELETE
LOOP
BEGIN
SELECT TRUNC(dbms_random.value(MIN(p_partkey),MAX(p_partkey)))
INTO KEY_RANDOM FROM DUAL;
dbms_output.put_line('random key:'||KEY_RANDOM);
IF( NOT KEY_ARRAY.EXISTS(KEY_RANDOM)) THEN
BEGIN
DELETE FROM part WHERE p_partkey = KEY_RANDOM;
KEY_ARRAY(KEY_RANDOM) := KEY_RANDOM;
dbms_output.put_line('the key be deleted==========================:'||KEY_RANDOM);
END;
END IF;
END;
END LOOP;
END IF;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;
/
--看看这样
CREATE OR REPLACE TRIGGER DML_TRIGGER AFTER DELETE OR INSERT OR UPDATE ON PART for each row
DECLARE
pragma autonomous_transaction;
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) ;
CNT_RANDOMKEY NUMBER(10) := 0;
PRICE_RANDOM NUMBER(10);
KEY_RANDOM NUMBER(10);
KEY_ARRAY ARRAY_TYPE;
CNT_INSERT NUMBER(10);
KEY_INSERT NUMBER(10);
CNT_DELETE NUMBER(10);
KEY_ARRAY ARRAY_TYPE;BEGIN
IF INSERTING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_INSERT FROM part;
dbms_output.put_line('the rownumber of insert'||to_char(CNT_INSERT));
For i IN 1..CNT_INSERT
LOOP
Select MAX(p_partkey) INTO KEY_INSERT FROM dual;
dbms_output.put_line('random key:'||to_char(KEY_INSERT));
KEY_INSERT := KEY_INSERT + 1 ;
INSERT INTO part VALUES (100, '', '','','',38,'', 1000);
END LOOP;ELSIF UPDATING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_UPDATE FROM part;
dbms_output.put_line('the rownumber of random_update'||to_char(CNT_UPDATE));
For i IN 1..CNT_UPDATE
LOOP
SELECT TRUNC(dbms_random.value(MIN(p_partkey),MAX(p_partkey))) INTO KEY_RANDOM FROM part;
dbms_output.put_line('random key:'||to_char(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==========================:'||to_char(KEY_RANDOM));
END LOOP;ELSIF DELETING THEN
SELECT TRUNC(dbms_random.value(1,MAX(p_partkey))) INTO CNT_DELETE FROM part;
dbms_output.put_line('the number of random_delete'||to_char(CNT_DELETE));
FOR i IN 1..CNT_DELETE
LOOP
SELECT TRUNC(dbms_random.value(MIN(p_partkey),MAX(p_partkey))) INTO KEY_RANDOM FROM part;
dbms_output.put_line('random key:'||to_char(KEY_RANDOM));
IF( NOT KEY_ARRAY.EXISTS(KEY_RANDOM)) THEN
DELETE FROM part WHERE p_partkey = KEY_RANDOM;
KEY_ARRAY(KEY_RANDOM) := KEY_RANDOM;
dbms_output.put_line('the key be deleted==========================:'||to_char(KEY_RANDOM));
END IF;
END LOOP;
end if;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);
END;