关于存储过程的一个不解的问题？

sailer_shi 2004-10-20 09:02:06

我创建了一个存储过程，我用pl/sql test的时候没有问题，但是我用Delphi调用的时候总是报ora-06550错误，但是我既然能编译过去，而且能执行，那么为什么用程序调用的时候会出错？

...全文

292 24 打赏收藏转发到动态举报

写回复

用AI写文章

24 条回复

切换为时间正序

请发表友善的回复…

发表回复

smallfishjun 2004-12-16

打赏
举报

把详细的错误写写看！

fmdsaco 2004-11-13

打赏
举报

学习

sailer_shi 2004-10-21

打赏
举报

不是吧，放到中间也不行？我别的存储过程放到中间了为什么没有问题？这种解释我觉得不可取

David1289 2004-10-21

打赏
举报

CREATE OR REPLACE PROCEDURE pp_insert_total_score /* 这里注释可以吗*/
(
p_result OUT VARCHAR2,
p_msg OUT VARCHAR2,

注释要写在一行的结尾吧，你的注释是夹在句子中间的

sailer_shi 2004-10-21

打赏
举报

IF v_score_to >= v_time /*M_PUNISH_DATE*/
AND v_total_score + v_temp_score < 12
THEN
v_score_to := ADD_MONTHS (v_score_to, 12);
v_over_date := NULL;
v_total_score := m_score;
-- V_DUTY_COUNT:=V_DUTY_COUNT+,0);
-- V_VIOLATION_COUNT:=V_VIOLATION_COUNT+NVL(REC_TOTAL_SCORE.VIOLATION_COUNT,0);

ELSE
v_total_score := v_total_score + m_score;
-- V_DUTY_COUNT:=V_DUTY_COUNT+NVL(REC_TOTAL_SCORE.DUTY_COUNT,0);
-- V_VIOLATION_COUNT:=V_VIOLATION_COUNT+NVL(REC_TOTAL_SCORE.VIOLATION_COUNT,0);
END IF;

IF v_total_score >= 12
THEN
IF v_over_date IS NULL
THEN
v_over_date := v_time;

--V_OVER_DATE:=M_PUNISH_DATE;
/*SELECT MAX(PECCANCYTIME) INTO V_OVER_DATE
FROM PECCANCY T
WHERE casenum=M_CASENUM;--GRANTUNIT=M_GRANT_UNIT
--AND TRIM(LICENSENUM)=TRIM(M_LICENSE_NUM)
--AND SCORE_CANCEL='0'
--AND IS_DEL='0';*/
IF v_over_date < m_punish_date
THEN
v_over_date := m_punish_date;
END IF;
-- ELSE
-- IF M_PUNISH_DATE<V_OVER_DATE THEN
-- FOR REC IN CUR_SCORE(V_OVER_DATE) LOOP
-- V_LAST_SCORE:=V_LAST_SCORE+REC.SCORE;
-- END LOOP;
-- IF M_SCORE>=V_LAST_SCORE THEN
-- V_OVER_DATE:=M_PUNISH_DATE;
-- END IF;
-- END IF;
END IF;
END IF;

IF v_temp_score - m_score > 0
THEN
v_temp_score := v_temp_score - m_score;
ELSE
v_temp_score := 0;
END IF;

UPDATE w_total_score
SET total_score = v_total_score,
temp_score = v_temp_score,
addr = m_addr,
formal_arch = m_formal_arch,
duty_count = v_duty_count,
violation_count = v_violation_count,

total_fine = total_fine + m_fine,
-- NOTIFY_DATE=V_NOTIFY_DATE,
over_date = v_over_date,
license_level = m_license_level,
--DECODE(M_LICENSE_LEVEL,NULL,LICENSE_LEVEL,M_LICENSE_LEVEL,M_LICENSE_LEVEL hangto_date = v_hagn_to
WHERE grant_unit = m_grant_unit
AND TRIM (license_num) = TRIM (m_license_num);

-- IF SQL%NOTFOUND
EXCEPTION
WHEN no_data_found THEN
INSERT INTO w_total_score
(grant_unit, formal_arch, license_num,
NAME, sex, addr, total_score, temp_score,
violation_count, duty_count, total_fine, status,
notify_date, score_to, over_date, license_level,
hangto_date
)
VALUES (m_grant_unit, m_formal_arch, TRIM (m_license_num),
m_name, m_sex, m_addr, m_score, 0,
v_violation_count, v_duty_count, m_fine,
-- M_FINE,
v_status,
NULL, -- V_NOTIFY_DATE,
v_score_to, NULL, m_license_level,
v_hagn_to
);
END;

p_result := '0';
p_msg := '更新成功！';
GOTO label_end;
<<label_end>>
IF p_result ='0' THEN COMMIT;
ELSE ROLLBACK;
END IF;
NULL;
EXCEPTION
WHEN OTHERS
THEN
p_result := '255';
p_msg := '失败！'||substr(SQLERRM,1,200);
NULL;
/* IF CUR_TOTAL_SCORE%ISOPEN THEN
CLOSE CUR_TOTAL_SCORE;
END IF; */
END pp_insert_total_score;

sailer_shi 2004-10-21

打赏
举报

我的存储过程的全部代码

CREATE OR REPLACE PROCEDURE pp_insert_total_score
/*
写入总表

*/
(
p_result OUT VARCHAR2,
p_msg OUT VARCHAR2,
m_casenum IN VARCHAR2,
m_grant_unit IN VARCHAR2,
m_formal_arch IN VARCHAR2,
m_license_num IN VARCHAR2,
m_name IN VARCHAR2,
m_sex IN VARCHAR2,
m_addr IN VARCHAR2,
m_punish_date IN DATE,
m_score IN NUMBER,
m_fine IN NUMBER,
m_hang_months IN NUMBER,
m_is_notify IN VARCHAR2,
m_license_level IN VARCHAR2,
m_type IN VARCHAR2,
m_istemp IN VARCHAR2
)
IS
v_total_score NUMBER := 0;
v_temp_score NUMBER := 0;
v_over_date DATE;
temp_days NUMBER := 0;
v_status CHAR (1);
v_notify_date DATE;
v_score_to DATE;
v_score_start DATE;
v_hagn_to DATE;
v_duty_count NUMBER (3, 0) := 0;
v_violation_count NUMBER (3, 0) := 0;
v_last_score NUMBER (3, 0) := 0;
v_time DATE;
--M_ADDR VARCHAR2(50);
/*CURSOR CUR_SCORE(V_PUNISH_DATE DATE )
IS SELECT SCORE
FROM PECCANCY
WHERE SCORE_CANCEL='0'
and IS_DEL='0'
AND CASENUM = M_CASENUM;*/
BEGIN
SELECT TO_DATE (info_value, 'YYYY.MM.DD')
INTO v_score_start
FROM ws_sys_info
WHERE info_name = 'SCORE_START_DATE';

SELECT ADD_MONTHS (v_score_start,
12
* ( FLOOR (MONTHS_BETWEEN (SYSDATE, v_score_start) / 12)
+ 1
)
)
INTO v_score_to
FROM DUAL;

IF m_istemp ='1'
THEN
BEGIN
UPDATE w_total_score
SET temp_score = temp_score + m_score
WHERE TRIM (formal_arch) = TRIM (m_formal_arch);

IF SQL%NOTFOUND
THEN
INSERT INTO w_total_score
(grant_unit, formal_arch, license_num,
NAME, sex, addr, total_score, temp_score,
violation_count, duty_count, status, notify_date,
score_to, over_date, license_level, hangto_date
)
VALUES (m_grant_unit, m_formal_arch, TRIM (m_license_num),
m_name, m_sex, m_addr, 0, m_score,
0, 0, 'A', NULL,
v_score_to, NULL, m_license_level, NULL
);
END IF;

p_result := '0';
p_msg := '更新成功！';

GOTO label_end;
EXCEPTION
WHEN OTHERS
THEN
p_result := '255';
p_msg := '更新出错！';
GOTO label_end;
END;
END IF;

v_hagn_to := NULL;
v_time := SYSDATE;

-- SELECT NVL(SUM(SCORE),0), NVL(SUM(FINE),0) INTO TEMP_SCORE, M_FINE FROM W_DETAIL WHERE CASENUM = M_CASENUM;
-- select score,fine into temp_score,m_fine from peccancy where casenum = m_casenum;
--TEMP_SCORE:=M_SCORE;
-- SELECT NVL(MAX(HANGMONTHS), 0) INTO TEMP_MONTHS FROM W_DETAIL WHERE CASENUM = M_CASENUM;
-- SELECT HANG_TO INTO V_HAGN_TO FROM PECCANCY WHERE CASENUM = M_CASENUM;
--TEMP_MONTHS:=M_HANG_MONTHS;
IF m_hang_months > 0
THEN
v_status := 'F';
ELSE
BEGIN
SELECT NVL (hang_to, SYSDATE) - SYSDATE
INTO temp_days
FROM peccancy
WHERE casenum = m_casenum; --GRANT_UNIT=M_GRANT_UNIT
EXCEPTION
WHEN no_data_found THEN
p_result := '255';
p_msg := '无此记录！';
GOTO label_end;
END;
-- AND LICENSE_NUM=M_LICENSE_NUM;
IF temp_days > 0
THEN
v_status := 'F';
ELSE
v_status := 'A';
END IF;
END IF;

IF m_type = '1'
THEN
v_duty_count := 0;
v_violation_count := 1;
ELSE
v_duty_count := 1;
v_violation_count := 0;
END IF;

/*
OPEN CUR_TOTAL_SCORE;
FETCH CUR_TOTAL_SCORE INTO REC_TOTAL_SCORE;
IF CUR_TOTAL_SCORE%NOTFOUND THEN
SELECT TO_DATE(INFO_VALUE,'YYYY.MM.DD') INTO V_SCORE_START
FROM WS_SYS_INFO
WHERE INFO_NAME='SCORE_START_DATE';
SELECT ADD_MONTHS(V_SCORE_START,12*(FLOOR(MONTHS_BETWEEN(SYSDATE,V_SCORE_START)/12)+1))
INTO V_SCORE_TO
FROM DUAL;*/-- v_total_score := m_score;
BEGIN
SELECT NVL (total_score, 0), NVL (temp_score, 0), over_date, score_to,
NVL (duty_count, 0) + v_duty_count,
NVL (violation_count, 0) + v_violation_count
INTO v_total_score, v_temp_score, v_over_date, v_score_to,
v_duty_count,
v_violation_count
FROM w_total_score
WHERE TRIM (license_num) = TRIM (m_license_num);

-- v_zsscore := m_score + v_zsscore;
IF (v_total_score<12) AND (v_total_score + m_score >= 12)
THEN
v_over_date := m_punish_date;
ELSIF v_total_score+m_score <12 THEN
v_over_date := NULL;
END IF;