表结构如下
create table TBL_REST_WAITING_QUEUE
(
USER_CODE VARCHAR2(12),
USER_NAME VARCHAR2(12),
AGENT_CODE VARCHAR2(12),
BRAND VARCHAR2(30),
REQ_MESSAGEID VARCHAR2(20),
BEGIN_WAITING DATE default SYSDATE,
QUEUE_LIST VARCHAR2(50),
REQ_TYPE INTEGER,
REQ_REST_TYPE VARCHAR2(20),
REQ_REST_TXT VARCHAR2(100),
ISENABLEREST INTEGER default 0,
ISENABLERESTTIME DATE,
ISENABLEAGENTCODE VARCHAR2(20),
ISENABLETXT VARCHAR2(100),
ID INTEGER,
SENDREMIND INTEGER default 0,
SENDTIME DATE
)create table TBL_REST_WAITING_HISTORY
(
RecordID INTEGER,
USER_CODE VARCHAR2(12),
AGENT_CODE VARCHAR2(12),
BEGIN_WAITING DATE,
END_WAITING DATE,
ENDSTATE VARCHAR2(12),
RECORDCREATE DATE default sysdate not null,
ENDSUBSTATE VARCHAR2(12),
BRAND VARCHAR2(20)
)存储过程如下create or replace procedure MonitorWaitingQueue(CURRENT_STATUS IN VARCHAR2,
SUB_STATUS IN VARCHAR2,
AGENT_CODE IN VARCHAR2) is L_AGENT_CODE VARCHAR2(12) := AGENT_CODE;
L_END_WAITING DATE := sysdate;
L_ENDSTATE VARCHAR2(12) := CURRENT_STATUS;
L_ENDSUBSTATE VARCHAR2(12):= SUB_STATUS;
cur_restdata tbl_rest_waiting_queue%rowtype;
begin
select * into cur_restdata from tbl_rest_waiting_queue t
where t.agent_code = L_AGENT_CODE;
INSERT INTO tbl_rest_waiting_history
(USER_CODE,AGENT_CODE,BEGIN_WAITING,END_WAITING,ENDSTATE,ENDSUBSTATE,BRAND)
values
(cur_restdata.user_code,cur_restdata.agent_code,cur_restdata.begin_waiting,L_END_WAITING,L_ENDSTATE,L_ENDSUBSTATE,cur_restdata.brand);
end MonitorWaitingQueue;在DELPHI中调用存储过程不成功,在PL/SQL Developer中点右键 test,输入参数执行不了, 在Select 附近就出错了,运行不了,不懂 Oracle的存储过程,谢谢大家!
create table TBL_REST_WAITING_QUEUE
(
USER_CODE VARCHAR2(12),
USER_NAME VARCHAR2(12),
AGENT_CODE VARCHAR2(12),
BRAND VARCHAR2(30),
REQ_MESSAGEID VARCHAR2(20),
BEGIN_WAITING DATE default SYSDATE,
QUEUE_LIST VARCHAR2(50),
REQ_TYPE INTEGER,
REQ_REST_TYPE VARCHAR2(20),
REQ_REST_TXT VARCHAR2(100),
ISENABLEREST INTEGER default 0,
ISENABLERESTTIME DATE,
ISENABLEAGENTCODE VARCHAR2(20),
ISENABLETXT VARCHAR2(100),
ID INTEGER,
SENDREMIND INTEGER default 0,
SENDTIME DATE
)create table TBL_REST_WAITING_HISTORY
(
RecordID INTEGER,
USER_CODE VARCHAR2(12),
AGENT_CODE VARCHAR2(12),
BEGIN_WAITING DATE,
END_WAITING DATE,
ENDSTATE VARCHAR2(12),
RECORDCREATE DATE default sysdate not null,
ENDSUBSTATE VARCHAR2(12),
BRAND VARCHAR2(20)
)存储过程如下create or replace procedure MonitorWaitingQueue(CURRENT_STATUS IN VARCHAR2,
SUB_STATUS IN VARCHAR2,
AGENT_CODE IN VARCHAR2) is L_AGENT_CODE VARCHAR2(12) := AGENT_CODE;
L_END_WAITING DATE := sysdate;
L_ENDSTATE VARCHAR2(12) := CURRENT_STATUS;
L_ENDSUBSTATE VARCHAR2(12):= SUB_STATUS;
cur_restdata tbl_rest_waiting_queue%rowtype;
begin
select * into cur_restdata from tbl_rest_waiting_queue t
where t.agent_code = L_AGENT_CODE;
INSERT INTO tbl_rest_waiting_history
(USER_CODE,AGENT_CODE,BEGIN_WAITING,END_WAITING,ENDSTATE,ENDSUBSTATE,BRAND)
values
(cur_restdata.user_code,cur_restdata.agent_code,cur_restdata.begin_waiting,L_END_WAITING,L_ENDSTATE,L_ENDSUBSTATE,cur_restdata.brand);
end MonitorWaitingQueue;在DELPHI中调用存储过程不成功,在PL/SQL Developer中点右键 test,输入参数执行不了, 在Select 附近就出错了,运行不了,不懂 Oracle的存储过程,谢谢大家!
SUB_STATUS IN VARCHAR2,
AGENT_CODE IN VARCHAR2) is
DECLARE
L_AGENT_CODE VARCHAR2(12);
L_END_WAITING DATE;
L_ENDSTATE VARCHAR2(12);
L_ENDSUBSTATE VARCHAR2(12);
cur_restdata tbl_rest_waiting_queue%rowtype;
BEGIN
L_AGENT_CODE:= AGENT_CODE;
L_END_WAITING:= sysdate;
L_ENDSTATE:= CURRENT_STATUS;
L_ENDSUBSTATE:= SUB_STATUS;
SELECT * INTO cur_restdata FROM tbl_rest_waiting_queue t
WHERE t.agent_code = L_AGENT_CODE;
INSERT INTO tbl_rest_waiting_history(USER_CODE,AGENT_CODE,BEGIN_WAITING,END_WAITING,ENDSTATE,ENDSUBSTATE,BRAND)
VALUES
(cur_restdata.user_code,cur_restdata.agent_code,cur_restdata.begin_waiting,L_END_WAITING,L_ENDSTATE,L_ENDSUBSTATE,cur_restdata.brand);
end MonitorWaitingQueue;
where t.agent_code = L_AGENT_CODE;
INSERT INTO tbl_rest_waiting_history
(USER_CODE,AGENT_CODE,BEGIN_WAITING,END_WAITING,ENDSTATE,ENDSUBSTATE,BRAND)
values
(cur_restdata.user_code,cur_restdata.agent_code,cur_restdata.begin_waiting,L_END_WAITING,L_ENDSTATE,L_ENDSUBSTATE,cur_restdata.brand);
改成
INSERT INTO tbl_rest_waiting_history
(select * into cur_restdata from tbl_rest_waiting_queue t
where t.agent_code = L_AGENT_CODE;)试下
自行查询一下。
没有数据无法判断。
WHERE t.agent_code = L_AGENT_CODE;
在这个地方你输入数据的时候是不是包ora-01403 未找到数据,这个地方的输入L_AGENT_CODE值一定要能在数据库中找到,不然会报错的。
create or replace procedure MonitorWaitingQueue(CURRENT_STATUS IN VARCHAR2,
SUB_STATUS IN VARCHAR2,
AGENT_CODE IN VARCHAR2)
is
L_AGENT_CODE VARCHAR2(12);
L_END_WAITING DATE;
L_ENDSTATE VARCHAR2(12);
L_ENDSUBSTATE VARCHAR2(12);
cursor cur_restdata is SELECT * FROM tbl_rest_waiting_queue t WHERE t.agent_code = L_AGENT_CODE;
BEGIN
L_AGENT_CODE:= AGENT_CODE;
L_END_WAITING:= sysdate;
L_ENDSTATE:= CURRENT_STATUS;
L_ENDSUBSTATE:= SUB_STATUS;for v_cur_res in cur_restdata loop INSERT INTO tbl_rest_waiting_history(USER_CODE,AGENT_CODE,BEGIN_WAITING,END_WAITING,ENDSTATE,ENDSUBSTATE,BRAND)
VALUES
(cur_restdata.user_code,cur_restdata.agent_code,cur_restdata.begin_waiting,L_END_WAITING,L_ENDSTATE,L_ENDSUBSTATE,cur_restdata.brand);
end loop;
commit;end MonitorWaitingQueue;
(select * into cur_restdata from tbl_rest_waiting_queue t
where t.agent_code = L_AGENT_CODE;)试下都失去本来题目的意义,主要是要用到ROWTYPE!
--你的存储过程,本身没有太大的错误,如果你输入的参数能够select出值,则过程正常运行。
--如果输入的参数导致select 查不出值,就会报 no_data_found 的错误,你只需要修改一下你得存储
--捕捉 no_data_found 异常特殊处理下就行了。CREATE OR REPLACE PROCEDURE MONITORWAITINGQUEUE(CURRENT_STATUS IN VARCHAR2,
SUB_STATUS IN VARCHAR2,
AGENT_CODE IN VARCHAR2) IS L_AGENT_CODE VARCHAR2(12) := AGENT_CODE;
L_END_WAITING DATE := SYSDATE;
L_ENDSTATE VARCHAR2(12) := CURRENT_STATUS;
L_ENDSUBSTATE VARCHAR2(12) := SUB_STATUS;
CUR_RESTDATA TBL_REST_WAITING_QUEUE%ROWTYPE;
BEGIN
SELECT *
INTO CUR_RESTDATA
FROM TBL_REST_WAITING_QUEUE T
WHERE T.AGENT_CODE = L_AGENT_CODE; INSERT INTO TBL_REST_WAITING_HISTORY
(USER_CODE,
AGENT_CODE,
BEGIN_WAITING,
END_WAITING,
ENDSTATE,
ENDSUBSTATE,
BRAND)
VALUES
(CUR_RESTDATA.USER_CODE,
CUR_RESTDATA.AGENT_CODE,
CUR_RESTDATA.BEGIN_WAITING,
L_END_WAITING,
L_ENDSTATE,
L_ENDSUBSTATE,
CUR_RESTDATA.BRAND);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('输入参数错误,导致查询异常,异常信息:【' || SQLERRM ||'】');
END MONITORWAITINGQUEUE;
--为了防止insert 产生异常,按照下面这么写吧。
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('输入参数错误,导致查询异常,异常信息:【' || SQLERRM ||'】');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('其他异常情况,异常信息:【' || SQLERRM ||'】');