(RMNO IN VARCHAR2, ARRIVAL IN VARCHAR2
, OPENO IN NUMBER) AS
OLDDATE ROOM.ARRDATE%TYPE;
HMSG HISTORY.MESSAGE%TYPE;
BEGIN
SELECT ARRDATE INTO OLDDATE FROM ROOM
WHERE ROOMNO = RMNO
FOR UPDATE;
UPDATE ROOM SET ARRDATE = RTRIM(ARRIVAL), ARRDATEDATE = SYSDATE
, ARRDATEOPENO = OPENO
WHERE ROOMNO = RMNO;
HMSG := '到着日更新';
HMSG := HMSG || ' ROOM=' || LTRIM(RMNO, '0');
HMSG := HMSG || ' 旧到着日=' || OLDDATE || ' 新到着日=' || ARRIVAL;
BEGIN
INSERT INTO HISTORY (OPERATION, OPENO, ROOMNO, GPINO, MESSAGE)
VALUES (24, OPENO, RMNO, NULL, HMSG);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
我这段存储过程那里又问题啊,拜托了执行结果如下:
SQL> exec ARR_SET('2035','0802151015',30);
BEGIN ARR_SET('2035','0802151015',30); END;*
行1でエラーが発生しました。:
ORA-01403: データが見つかりません。
ORA-06512: "HSVUSER.ARR_SET", 行28
ORA-06512: 行1
, OPENO IN NUMBER) AS
OLDDATE ROOM.ARRDATE%TYPE;
HMSG HISTORY.MESSAGE%TYPE;
BEGIN
SELECT ARRDATE INTO OLDDATE FROM ROOM
WHERE ROOMNO = RMNO
FOR UPDATE;
UPDATE ROOM SET ARRDATE = RTRIM(ARRIVAL), ARRDATEDATE = SYSDATE
, ARRDATEOPENO = OPENO
WHERE ROOMNO = RMNO;
HMSG := '到着日更新';
HMSG := HMSG || ' ROOM=' || LTRIM(RMNO, '0');
HMSG := HMSG || ' 旧到着日=' || OLDDATE || ' 新到着日=' || ARRIVAL;
BEGIN
INSERT INTO HISTORY (OPERATION, OPENO, ROOMNO, GPINO, MESSAGE)
VALUES (24, OPENO, RMNO, NULL, HMSG);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
我这段存储过程那里又问题啊,拜托了执行结果如下:
SQL> exec ARR_SET('2035','0802151015',30);
BEGIN ARR_SET('2035','0802151015',30); END;*
行1でエラーが発生しました。:
ORA-01403: データが見つかりません。
ORA-06512: "HSVUSER.ARR_SET", 行28
ORA-06512: 行1
====================
NO DATA FOUND,没有数据。没有 ROOMNO=RMNO 的记录。
ARRIVAL IN VARCHAR2,
OPENO IN NUMBER) AS
OLDDATE ROOM.ARRDATE%TYPE;
HMSG HISTORY.MESSAGE%TYPE;
BEGIN
BEGIN
SELECT ARRDATE INTO OLDDATE FROM ROOM WHERE ROOMNO = RMNO FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ROOM 表中ROOMNO=''' || RMNO || '''的数据不存在');
END;
UPDATE ROOM
SET ARRDATE = RTRIM(ARRIVAL),
ARRDATEDATE = SYSDATE,
ARRDATEOPENO = OPENO
WHERE ROOMNO = RMNO; HMSG := '到着日更新';
HMSG := HMSG ¦ ¦ ' ROOM=' ¦ ¦ LTRIM(RMNO, '0');
HMSG := HMSG ¦ ¦ ' 旧到着日=' ¦ ¦ OLDDATE ¦ ¦ ' 新到着日=' ¦ ¦ ARRIVAL;
BEGIN
INSERT INTO HISTORY
(OPERATION, OPENO, ROOMNO, GPINO, MESSAGE)
VALUES
(24, OPENO, RMNO, NULL, HMSG);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
你自己拿这个来试验一下就知道是什么问题了.
CREATE OR REPLACE PROCEDURE ARR_SET(RMNO IN VARCHAR2,
ARRIVAL IN VARCHAR2,
OPENO IN NUMBER) AS
OLDDATE ROOM.ARRDATE%TYPE;
HMSG HISTORY.MESSAGE%TYPE;
BEGIN
BEGIN
SELECT ARRDATE INTO OLDDATE FROM ROOM WHERE ROOMNO = RMNO FOR UPDATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ROOM 表中ROOMNO=''' || RMNO || '''的数据不存在');
END;
UPDATE ROOM
SET ARRDATE = RTRIM(ARRIVAL),
ARRDATEDATE = SYSDATE,
ARRDATEOPENO = OPENO
WHERE ROOMNO = RMNO; HMSG := '到着日更新';
HMSG := HMSG ¦ ¦ ' ROOM=' ¦ ¦ LTRIM(RMNO, '0');
HMSG := HMSG ¦ ¦ ' 旧到着日=' ¦ ¦ OLDDATE ¦ ¦ ' 新到着日=' ¦ ¦ ARRIVAL;
BEGIN
INSERT INTO HISTORY
(OPERATION, OPENO, ROOMNO, GPINO, MESSAGE)
VALUES
(24, OPENO, RMNO, NULL, HMSG);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
你自己拿这个来试验一下就知道是什么问题了.