这个过程将空值插入到我的自定义表中-为什么

Question

CREATE OR REPLACE PROCEDURE SRM_H_PROC(
EMPLID_OUT OUT VARCHAR2,
ACAD_CAREER_OUT OUT VARCHAR2,
ACAD_PROG_INOUT IN OUT VARCHAR2,
PROG_STATUS_OUT OUT VARCHAR2,
ADMIT_TERM_INOUT IN OUT VARCHAR2)
AS
CURSOR PROC2
IS
SELECT
A.EMPLID,
A.ACAD_CAREER,
A.ACAD_PROG,
A.PROG_STATUS,
A.ADMIT_TERM
FROM
PS_ACAD_PROG A
WHERE
A.ADMIT_TERM  = ADMIT_TERM_INOUT
AND A.ACAD_PROG =ACAD_PROG_INOUT
AND A.EFFDT     =
(
SELECT MAX(EFFDT) FROM PS_ACAD_PROG WHERE EMPLID=A.EMPLID
)
AND A.EFFSEQ=
(
SELECT
MAX(EFFSEQ)
FROM
PS_ACAD_PROG
WHERE
EMPLID =A.EMPLID
AND EFFDT=A.EFFDT
)
AND A.PROG_STATUS='AC';
BEGIN
FOR I IN PROC2
LOOP
IF ADMIT_TERM_INOUT='1701' THEN
INSERT
INTO
PS_SRM_H_PROC_TB VALUES
(
EMPLID_OUT,
ACAD_CAREER_OUT,
ACAD_PROG_INOUT,
PROG_STATUS_OUT,
ADMIT_TERM_INOUT
);
ELSE
DBMS_OUTPUT.PUT_LINE('HI DEAR, SAY HELLO TO ME.');
END IF;
END LOOP;
COMMIT;
END SRM_H_PROC;

当我将acad_prog"BTRCS"和admit_term"1701"作为过程中的输入时，表输出如下：

(NULL)  (NULL)  BTRCS   (NULL)  1701
(NULL)  (NULL)  BTRCS   (NULL)  1701
(NULL)  (NULL)  BTRCS   (NULL)  1701
(NULL)  (NULL)  BTRCS   (NULL)  1701
(NULL)  (NULL)  BTRCS   (NULL)  1701
(NULL)  (NULL)  BTRCS   (NULL)  1701

Answer 1

您需要在if语句和insert语句中使用cursor值，如下所示：

FOR I IN PROC2
LOOP
IF I.ADMIT_TERM ='1701' THEN
INSERT
INTO
PS_SRM_H_PROC_TB VALUES
(
I.EMPLID,
I.ACAD_CAREER,
I.ACAD_PROG,
I.PROG_STATUS,
I.ADMIT_TERM
);
ELSE
DBMS_OUTPUT.PUT_LINE('HI DEAR, SAY HELLO TO ME.');
END IF;
END LOOP;

这也可以使用insert into .. select ..(不带cursor(完成，如下所示：

CREATE OR REPLACE PROCEDURE SRM_H_PROC(
EMPLID_OUT OUT VARCHAR2,
ACAD_CAREER_OUT OUT VARCHAR2,
ACAD_PROG_INOUT IN OUT VARCHAR2,
PROG_STATUS_OUT OUT VARCHAR2,
ADMIT_TERM_INOUT IN OUT VARCHAR2)
AS
BEGIN 
INSERT
INTO
PS_SRM_H_PROC_TB 
SELECT
A.EMPLID,
A.ACAD_CAREER,
A.ACAD_PROG,
A.PROG_STATUS,
A.ADMIT_TERM
FROM
PS_ACAD_PROG A
WHERE
A.ADMIT_TERM  = ADMIT_TERM_INOUT
AND A.ACAD_PROG =ACAD_PROG_INOUT
AND A.EFFDT     =
(
SELECT MAX(EFFDT) FROM PS_ACAD_PROG WHERE EMPLID=A.EMPLID
)
AND A.EFFSEQ=
(
SELECT
MAX(EFFSEQ)
FROM
PS_ACAD_PROG
WHERE
EMPLID =A.EMPLID
AND EFFDT=A.EFFDT
)
AND A.PROG_STATUS='AC'
AND A.ADMIT_TERM ='1701';
COMMIT;
END SRM_H_PROC;

干杯！！