带%行类型参数的PL \ SQL插入过程
问题描述:
我有两个表。我需要做一个将记录或记录插入表的过程,具体取决于表是否已经存在。 1.过程仅如果与脚的人已经在人表 2.变型,当我需要用新的人,在stay_person表中的相应条目添加记录工作,什么都不会发生 3.当在评论% rowtype参数并相应地更改主体,第一种情况下的过程停止工作,对于第二种情况,它工作正常。带%行类型参数的PL SQL插入过程
--CREATE TABLE PERSON
CREATE TABLE PERSON
("P_ID" INTEGER CONSTRAINT PK_PERSON PRIMARY KEY,
"P_NAME" VARCHAR2(20) NOT NULL,
"P_NAME2" VARCHAR2(20) NOT NULL,
"P_PIN" VARCHAR(11) NOT NULL,
CONSTRAINT "LENGTH_PIN" CHECK (LENGTH(P_PIN) = 11),
CONSTRAINT "IS_DIGIT_PIN" CHECK (REGEXP_LIKE(P_PIN, '^[0-9]*$'))
);
--CREATE INDEX
CREATE UNIQUE INDEX I_PERSON_PIN ON PERSON(P_PIN);
--CREATING SEQUENCE
CREATE SEQUENCE SEQ_PERSON START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE NOCYCLE NOCACHE;
COMMIT;
/
--CREATE TABLE STAY_PERSON
CREATE TABLE STAY_PERSON
("PS_ID" INTEGER CONSTRAINT PK_STAY_PERSON PRIMARY KEY,
"PS_P_ID" INTEGER NOT NULL,
"PS_DATE_START" DATE DEFAULT SYSDATE,
"PS_DATE_STOP" DATE DEFAULT SYSDATE,
CONSTRAINT "FK_PERSON" FOREIGN KEY(PS_P_ID) REFERENCES PERSON(P_ID),
CONSTRAINT CK_DATE_START_STOP CHECK (PS_DATE_START <= PS_DATE_STOP)
);
--CREATE INDEX
CREATE INDEX I_STAY_PERSON_DATAE_START ON STAY_PERSON(PS_DATE_START);
CREATE INDEX I_STAY_PERSON_DATAE_STOP ON STAY_PERSON(PS_DATE_STOP);
--CREATE SEQUENCE
CREATE SEQUENCE SEQ_STAY_PERSON START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE NOCYCLE NOCACHE;
-- //
CREATE OR REPLACE PROCEDURE ADD_TO_STAY_PERSON(
NAME2 IN VARCHAR2, NAME IN VARCHAR2, PIN IN VARCHAR2, DATA_START IN DATE, DATA_SOP IN DATE
) AS
V_PERSON INTEGER;
V_P_ID PERSON%ROWTYPE;
V_CURRVAL_P_ID INTEGER;
BEGIN
SELECT COUNT(*)
INTO V_PERSON
FROM PERSON
WHERE P_PIN = PIN;
SELECT *
INTO V_P_ID
FROM PERSON
WHERE P_PIN = PIN;
IF V_Person = 0 THEN
INSERT INTO Person (P_ID, P_NAME, P_NAME2, P_Pin)
VALUES(SEQ_PERSON.NEXTVAL, NAME, NAME2, Pin);
V_CURRVAL_P_ID :=SEQ_PERSON.CURRVAL;
INSERT INTO STAY_PERSON (PS_ID, PS_P_ID, PS_DATE_START, PS_DATE_STOP)
VALUES(SEQ_STAY_PERSON.NEXTVAL, V_CURRVAL_P_ID, Data_Start, Data_Sop);
COMMIT;
END IF;
IF V_PERSON = 1 THEN
INSERT INTO STAY_PERSON (PS_ID, PS_P_ID, PS_DATE_START, PS_DATE_STOP)
VALUES(SEQ_STAY_PERSON.NEXTVAL, V_P_ID.P_ID, Data_Start, Data_Sop);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END ADD_TO_STAY_PERSON;
--//
--INSERT FIRET SAMPLE DATA
INSERT INTO Person (P_ID, P_NAME, P_NAME2, P_Pin)
VALUES(SEQ_PERSON.NEXTVAL, 'DOE', 'JOHN', '00000000001');
INSERT INTO STAY_PERSON (PS_ID, PS_P_ID, PS_DATE_START, PS_DATE_STOP)
VALUES(SEQ_STAY_PERSON.NEXTVAL, 1, SYSDATE-100, SYSDATE-90);
COMMIT;
-- TRY ADD NEW RECORD TO TABLE STAY_PERSON WITH NEW PERSON - FAILED
CALL ADD_TO_STAY_PERSON('DOE', 'JANE', '00000000002', SYSDATE-100, SYSDATE-90);
-- ADD NEW RECORD TO TABLE STAY_PERSON WITH OLD PERSON - OK
CALL ADD_TO_STAY_PERSON('DOE', 'JANE', '00000000001', SYSDATE-85, SYSDATE-70);
答
您需要在该人不在场时发现异常。
declare
V_P_ID PERSON%ROWTYPE;
begin
SELECT *
INTO V_P_ID
FROM PERSON
WHERE P_PIN = PIN;
-- Do something because person is in table
exception
when no_data_found then
-- Do something because person is not present in table.
end;
你的代码对我来说看起来像一个MERGE,你不需要PLSQL。 –