存储过程如下 CREATE OR REPLACE PROCEDURE VSMATCHCREATE(
p_MATCHID IN NUMBER ,
p_MSGNR IN NUMBER ,
p_MATCHINIT IN NUMBER ,
p_MATCHACTIVE IN NUMBER ,
p_MATCHSTATUS IN NUMBER ,
p_BETSTATUS IN NUMBER ,
p_MATCHSEASONNO IN NUMBER ,
p_MATCHSEASONNAME IN VARCHAR2 ,
p_ROUNDNUMBER IN NUMBER ,
p_TOURNAMENTNAME IN VARCHAR2 ,
p_SPORTID IN NUMBER ,
p_CATEGORYID IN NUMBER ,
p_HOMEID IN NUMBER ,
p_HOMELANGID IN NUMBER ,
p_AWAYID IN NUMBER ,
p_AWAYLANGID IN NUMBER ,
p_MATCHDATE IN DATE ,
p_MATCHSCORE IN VARCHAR2 ,
p_ResultOutPut IN OUT NUMBER )
AS
p_Now TIMESTAMP;
p_WorkingDate NUMBER;
p_Count NUMBER;
BEGIN
SELECT COUNT(*)
INTO p_Count
FROM DUAL
WHERE EXISTS
(SELECT "MatchID" FROM MATCHES WHERE "MatchID"=p_MATCHID
);
IF p_Count=0 THEN
SELECT SYSTIMESTAMP INTO p_Now FROM DUAL;
select FN_WORKINGDATE(p_Now) into p_WorkingDate from DUAL;
INSERT
INTO MATCHES
(
"MatchID" ,
"MSGNR" ,
"MatchInit" ,
"MatchActive" ,
"MatchStatus" ,
"BetStatus" ,
"MatchSeasonNo" ,
"MatchSeasonName" ,
"RoundNumber" ,
"TournamentName" ,
"SportID" ,
"CategoryID" ,
"HomeID" ,
"HomeLangID" ,
"AwayID" ,
"AwayLangID" ,
"MatchDate" ,
"MatchScore" ,
"WorkingDate" ,
"BetClear" ,
"BetClearTS" ,
"CreateTS" ,
"UpdateTS"
)
VALUES
(
p_MATCHID ,
p_MSGNR ,
p_MatchInit ,
p_MatchActive ,
p_MatchStatus ,
p_BetStatus ,
p_MatchSeasonNo ,
p_MatchSeasonName ,
p_RoundNumber ,
p_TournamentName ,
p_SportID ,
p_CategoryID ,
p_HomeID ,
p_HomeLangID ,
p_AwayID ,
p_AwayLangID ,
p_MatchDate ,
p_MatchScore ,
p_WorkingDate ,
0 ,
p_Now ,
p_Now ,
p_Now
);
p_ResultOutPut:=0;
ELSE
p_ResultOutPut:=0;
END IF;
END VSMATCHCREATE;
调用的过程如下:SET SERVEROUTPUT ON
declare
ret NUMBER;
nn TIMESTAMP;
BEGIN
SELECT SYSTIMESTAMP INTO nn FROM dual;
VSMATCHCREATE
(
3, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
1, --IN NUMBER ,
'123', --IN VARCHAR2 ,
0, --IN NUMBER ,
'123', --IN VARCHAR2 ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
nn, --IN DATE ,
'-', --IN VARCHAR2 ,
ret --IN OUT NUMBER
);
DBMS_OUTPUT.PUT_LINE(ret);
end;
我表示很疑惑, 求解.
p_MATCHID IN NUMBER ,
p_MSGNR IN NUMBER ,
p_MATCHINIT IN NUMBER ,
p_MATCHACTIVE IN NUMBER ,
p_MATCHSTATUS IN NUMBER ,
p_BETSTATUS IN NUMBER ,
p_MATCHSEASONNO IN NUMBER ,
p_MATCHSEASONNAME IN VARCHAR2 ,
p_ROUNDNUMBER IN NUMBER ,
p_TOURNAMENTNAME IN VARCHAR2 ,
p_SPORTID IN NUMBER ,
p_CATEGORYID IN NUMBER ,
p_HOMEID IN NUMBER ,
p_HOMELANGID IN NUMBER ,
p_AWAYID IN NUMBER ,
p_AWAYLANGID IN NUMBER ,
p_MATCHDATE IN DATE ,
p_MATCHSCORE IN VARCHAR2 ,
p_ResultOutPut IN OUT NUMBER )
AS
p_Now TIMESTAMP;
p_WorkingDate NUMBER;
p_Count NUMBER;
BEGIN
SELECT COUNT(*)
INTO p_Count
FROM DUAL
WHERE EXISTS
(SELECT "MatchID" FROM MATCHES WHERE "MatchID"=p_MATCHID
);
IF p_Count=0 THEN
SELECT SYSTIMESTAMP INTO p_Now FROM DUAL;
select FN_WORKINGDATE(p_Now) into p_WorkingDate from DUAL;
INSERT
INTO MATCHES
(
"MatchID" ,
"MSGNR" ,
"MatchInit" ,
"MatchActive" ,
"MatchStatus" ,
"BetStatus" ,
"MatchSeasonNo" ,
"MatchSeasonName" ,
"RoundNumber" ,
"TournamentName" ,
"SportID" ,
"CategoryID" ,
"HomeID" ,
"HomeLangID" ,
"AwayID" ,
"AwayLangID" ,
"MatchDate" ,
"MatchScore" ,
"WorkingDate" ,
"BetClear" ,
"BetClearTS" ,
"CreateTS" ,
"UpdateTS"
)
VALUES
(
p_MATCHID ,
p_MSGNR ,
p_MatchInit ,
p_MatchActive ,
p_MatchStatus ,
p_BetStatus ,
p_MatchSeasonNo ,
p_MatchSeasonName ,
p_RoundNumber ,
p_TournamentName ,
p_SportID ,
p_CategoryID ,
p_HomeID ,
p_HomeLangID ,
p_AwayID ,
p_AwayLangID ,
p_MatchDate ,
p_MatchScore ,
p_WorkingDate ,
0 ,
p_Now ,
p_Now ,
p_Now
);
p_ResultOutPut:=0;
ELSE
p_ResultOutPut:=0;
END IF;
END VSMATCHCREATE;
调用的过程如下:SET SERVEROUTPUT ON
declare
ret NUMBER;
nn TIMESTAMP;
BEGIN
SELECT SYSTIMESTAMP INTO nn FROM dual;
VSMATCHCREATE
(
3, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
1, --IN NUMBER ,
'123', --IN VARCHAR2 ,
0, --IN NUMBER ,
'123', --IN VARCHAR2 ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
nn, --IN DATE ,
'-', --IN VARCHAR2 ,
ret --IN OUT NUMBER
);
DBMS_OUTPUT.PUT_LINE(ret);
end;
我表示很疑惑, 求解.
解决方案 »
- 我想向DBA方向发展不知道需要读oracle方面的什么书籍
- 查询每日收款情况的sql语句
- oracle sql 相同数据合并
- 用Oracle存储过程怎么实现这个功能(急)
- linux 安装oracle10g 写入目录/tmp 时出错
- 请问有谁知道Oracle数据库的登录用户名和密码分别是什么啊?以后能否不用登录信息啊?
- 为什么我设置varchar2(4000),插入700多个汉字时就说超过长度!
- 关于 1 对 多 的显示
- ★★★★★救命呀!!运行时出现:The Network Adapter could not establish the connection错误★★★★★
- 送分题来啦! 一个与概率有关的问题。
- 大神们求一sql
- Oracle 安装 INS-32025
declare
ret NUMBER;
nn TIMESTAMP;
BEGIN
SELECT SYSTIMESTAMP INTO nn FROM dual;
VSMATCHCREATE
(
3, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
0, --IN NUMBER ,
1, --IN NUMBER ,
'123', --IN VARCHAR2 ,
0, --IN NUMBER ,
'123', --IN VARCHAR2 ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
1, --IN NUMBER ,
nn, --IN DATE ,
'-', --IN VARCHAR2 ,
ret --IN OUT NUMBER
);
DBMS_OUTPUT.PUT_LINE(ret);
end;
错误报告 -
ORA-01722: 无效数字
ORA-06512: 在 "TEST01.VSMATCHCREATE", line 26
ORA-06512: 在 line 6
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.
我表示淡淡的忧伤啊