各位请帮忙看看,在什么情况下才会存储那个STARTDATE??谢谢,我认为该存储过程有问题程序代码是:CREATE OR REPLACE PROCEDURE Hr_Chk_Trans_Date AS
v_empid HR_EMP_TRANS_TEMP.empid%TYPE;
v_trans_no HR_EMP_TRANS_TEMP.trans_no%TYPE;
v_object_content HR_EMP_TRANS_TEMP.object_content%TYPE;
v_available_date HR_EMP_TRANS_TEMP.available_date%TYPE;
v_object_table HR_EMP_TRANS_TEMP.object_table%TYPE;
v_object_column HR_EMP_TRANS_TEMP.object_column%TYPE;
v_trans_method HR_EMP_TRANS_TEMP.trans_method%TYPE;
v_activity HR_EMP_TRANS_TEMP.activity%TYPE;
vermpid PA_EMPID_FLAG.EMPID%TYPE;
CURSOR cur_hr_trans_date IS
SELECT empid,
trans_no,
object_content,
available_date,
object_table,
object_column,
trans_method,
activity
FROM HR_EMP_TRANS_TEMP WHERE activity = 0 ;
BEGIN
OPEN cur_hr_trans_date;
LOOP
FETCH cur_hr_trans_date INTO
v_empid,
v_trans_no,
v_object_content,
v_available_date,
v_object_table,
v_object_column,
v_trans_method,
v_activity; EXIT WHEN cur_hr_trans_date%NOTFOUND; /*this will exit when cursor empty*/ /* it is better that 'sysdate' will be changed to 'current_date' in oracle 9i. */
IF TO_CHAR(v_available_date,'YYYY-MM-DD') <= TO_CHAR(SYSDATE,'YYYY-MM-DD') AND v_activity = 0 THEN
IF v_object_column='DeptID' OR v_object_column='POSTID' OR v_object_column='PositionCode' OR v_object_column='GradeCode' OR v_object_column='DATELEFT' THEN
Hr_Up_Pampid (v_empid , v_object_content , v_available_date ,v_object_column);
END IF ;
IF v_trans_method = 'UPDATE' THEN
IF v_object_column='DATELEFT' THEN
EXECUTE IMMEDIATE 'UPDATE '||v_object_table||' SET '||v_object_column||' = TO_DATE(' || CHR(39) || v_object_content || CHR(39) || ',' || CHR(39) || 'YYYY-MM-DD' || CHR(39) || ') WHERE empid = '''||v_empid||'''';
ELSE if v_object_column='PA_SORT_ID' then
EXECUTE IMMEDIATE 'UPDATE '||v_object_table||' SET '||v_object_column||' = '''||v_object_content||'''WHERE empid = '''||v_empid||''''|| 'AND todate is null';
else
EXECUTE IMMEDIATE 'UPDATE '||v_object_table||' SET '||v_object_column||' = '''||v_object_content||'''WHERE empid = '''||v_empid||'''';
end if;
END IF;
UPDATE HR_EMP_TRANS_TEMP
SET activity = 1
WHERE empid = v_empid; ELSE
EXECUTE IMMEDIATE 'INSERT INTO '||v_object_table||'
(empid, '||v_object_column||', startdate) VALUES ('''||v_empid||''','''||v_object_content||''','''||v_available_date||''')';
UPDATE HR_EMP_TRANS_TEMP
SET activity = 1
WHERE empid = v_empid; END IF; /*ELSE
Update hr_emp_trans_temp
Set activity = 0
Where empid = v_empid;*/ END IF;
END LOOP;
CLOSE cur_hr_trans_date;
COMMIT;
END Hr_Chk_Trans_Date;
/
v_empid HR_EMP_TRANS_TEMP.empid%TYPE;
v_trans_no HR_EMP_TRANS_TEMP.trans_no%TYPE;
v_object_content HR_EMP_TRANS_TEMP.object_content%TYPE;
v_available_date HR_EMP_TRANS_TEMP.available_date%TYPE;
v_object_table HR_EMP_TRANS_TEMP.object_table%TYPE;
v_object_column HR_EMP_TRANS_TEMP.object_column%TYPE;
v_trans_method HR_EMP_TRANS_TEMP.trans_method%TYPE;
v_activity HR_EMP_TRANS_TEMP.activity%TYPE;
vermpid PA_EMPID_FLAG.EMPID%TYPE;
CURSOR cur_hr_trans_date IS
SELECT empid,
trans_no,
object_content,
available_date,
object_table,
object_column,
trans_method,
activity
FROM HR_EMP_TRANS_TEMP WHERE activity = 0 ;
BEGIN
OPEN cur_hr_trans_date;
LOOP
FETCH cur_hr_trans_date INTO
v_empid,
v_trans_no,
v_object_content,
v_available_date,
v_object_table,
v_object_column,
v_trans_method,
v_activity; EXIT WHEN cur_hr_trans_date%NOTFOUND; /*this will exit when cursor empty*/ /* it is better that 'sysdate' will be changed to 'current_date' in oracle 9i. */
IF TO_CHAR(v_available_date,'YYYY-MM-DD') <= TO_CHAR(SYSDATE,'YYYY-MM-DD') AND v_activity = 0 THEN
IF v_object_column='DeptID' OR v_object_column='POSTID' OR v_object_column='PositionCode' OR v_object_column='GradeCode' OR v_object_column='DATELEFT' THEN
Hr_Up_Pampid (v_empid , v_object_content , v_available_date ,v_object_column);
END IF ;
IF v_trans_method = 'UPDATE' THEN
IF v_object_column='DATELEFT' THEN
EXECUTE IMMEDIATE 'UPDATE '||v_object_table||' SET '||v_object_column||' = TO_DATE(' || CHR(39) || v_object_content || CHR(39) || ',' || CHR(39) || 'YYYY-MM-DD' || CHR(39) || ') WHERE empid = '''||v_empid||'''';
ELSE if v_object_column='PA_SORT_ID' then
EXECUTE IMMEDIATE 'UPDATE '||v_object_table||' SET '||v_object_column||' = '''||v_object_content||'''WHERE empid = '''||v_empid||''''|| 'AND todate is null';
else
EXECUTE IMMEDIATE 'UPDATE '||v_object_table||' SET '||v_object_column||' = '''||v_object_content||'''WHERE empid = '''||v_empid||'''';
end if;
END IF;
UPDATE HR_EMP_TRANS_TEMP
SET activity = 1
WHERE empid = v_empid; ELSE
EXECUTE IMMEDIATE 'INSERT INTO '||v_object_table||'
(empid, '||v_object_column||', startdate) VALUES ('''||v_empid||''','''||v_object_content||''','''||v_available_date||''')';
UPDATE HR_EMP_TRANS_TEMP
SET activity = 1
WHERE empid = v_empid; END IF; /*ELSE
Update hr_emp_trans_temp
Set activity = 0
Where empid = v_empid;*/ END IF;
END LOOP;
CLOSE cur_hr_trans_date;
COMMIT;
END Hr_Chk_Trans_Date;
/
'(empid, '||v_object_column||',startdate) -- 这儿有啊,咋的啦?不明白...