Create Table table10_prc (
Family VARCHAR2(200),
Name VARCHAR2(200) ,
ID INTEGER NOT NULL PRIMARY KEY ,
NAME_ID INTEGER ;
) ;
CREATE SEQUENCE ID_seq3
MINVALUE 1
START WITH 1
INCREMENT BY 1 ;
Create or Replace trigger trg3
BEFORE insert on table10_prc
for each row
BEGIN
select ID_seq3.nextval INTO :new.ID from dual ;
END ;
CREATE OR REPLACE PROCEDURE addnewmem4 (str IN VARCHAR2)
FOREIGN KEY INTEGER REFERENCES table10_prc(ID);
AS
BEGIN
INSERT INTO table10_prc (Name, Family,NAME_ID)
WITH
temp
AS
( SELECT REGEXP_SUBSTR (str,
'[^,]+',
1,
LEVEL) val
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT (str, ',') + 1)
SELECT NAME_ID,TRIM(SUBSTR(val, 1, INSTR (val, ';') - 1)),
TRIM(SUBSTR(val, INSTR (val, ';') + 1))
FROM temp;
COMMIT;
END;
CREATE OR REPLACE PROCEDURE delete_member (par_id IN VARCHAR2)
IS
BEGIN
DELETE FROM table10_prc
WHERE id = par_id;
END;
BEGIN
addnewmem4 ('faezeh;Ghanbarian,pari;izadi');
END;
BEGIN
addnewmem4 ('Saeed;Izadi,Saman; Rostami');
END;
BEGIN
delete_member (1);
END;
BEGIN
delete_member (2) ;
END;
CALL delete_member(5);
CALL delete_member(6);
select *
from table10_prc ;
1条答案
按热度按时间jtjikinw1#
当我为prodecure定义forigen密钥时,收到错误
这一点:
无效。外键属于表,而不是过程。