create or replace procedure QUESTIONS_Select4(
questionSort in varchar2 default null,
questionType in varchar2 default null,
isuser in varchar2 default null,
questionCode in varchar2 default null,
questionDescrb in varchar2 default null,
ischeckTable in varchar2 default null ,
O_Cursor OUT SYS_REFCURSOR
)
AS
STRSQL VARCHAR(4000);
BEGIN STRSQL := '
SELECT
a.QuestionID as 题目代码
, a.QuestionTypeCode
, a.UseChk
, a.Re
, a.time as 答题时限
, a.AnswerChk
, a.QuestionName as 题目描述
, a.QuestionClassCode
, b.QuestionTypeName as 类型
,c.QuestionClassName as 题目分类
FROM Questions a,QuestionTypes b,QuestionClasses c
WHERE c.questionclasscode(+)=a.questionclasscode and b.questiontypecode(+)=a.questiontypecode and (a.delchk=0 or a.delchk is null)
';
IF questionType IS NOT NULL THEN STRSQL := STRSQL || ' AND a.QuestionTypeCode=''' || NVL(questionType,'') || ''''; END IF;
IF questionSort IS NOT NULL THEN STRSQL := STRSQL || ' AND c.QuestionClassName=''' || NVL(questionSort,'') || ''''; END IF;
IF isuser IS NOT NULL THEN STRSQL := STRSQL || ' AND a.UseChk ='''|| NVL(isuser,'')||''''; END IF;
IF questionCode IS NOT NULL THEN STRSQL := STRSQL || ' AND b.QuestionTypeName like''%' || NVL(questionCode,'') || '%'''; END IF;
IF questionDescrb IS NOT NULL THEN STRSQL:=STRSQL || ' AND a.QuestionName=''' || NVL(questionDescrb,'')||'''';END IF;
IF ischeckTable IS NOT NULL THEN STRSQL:=STRSQL || ' AND a.AnswerChk=''' || NVL(ischeckTable,'')||'''';END IF;
STRSQL := STRSQL ||' ORDER BY QuestionID ';
OPEN O_Cursor FOR STRSQL;
END;
--------------------------------------------------------------------------------------------------------------执行这段语句时,报错 "ORA-01722: 无效数字\n"。
questionSort in varchar2 default null,
questionType in varchar2 default null,
isuser in varchar2 default null,
questionCode in varchar2 default null,
questionDescrb in varchar2 default null,
ischeckTable in varchar2 default null ,
O_Cursor OUT SYS_REFCURSOR
)
AS
STRSQL VARCHAR(4000);
BEGIN STRSQL := '
SELECT
a.QuestionID as 题目代码
, a.QuestionTypeCode
, a.UseChk
, a.Re
, a.time as 答题时限
, a.AnswerChk
, a.QuestionName as 题目描述
, a.QuestionClassCode
, b.QuestionTypeName as 类型
,c.QuestionClassName as 题目分类
FROM Questions a,QuestionTypes b,QuestionClasses c
WHERE c.questionclasscode(+)=a.questionclasscode and b.questiontypecode(+)=a.questiontypecode and (a.delchk=0 or a.delchk is null)
';
IF questionType IS NOT NULL THEN STRSQL := STRSQL || ' AND a.QuestionTypeCode=''' || NVL(questionType,'') || ''''; END IF;
IF questionSort IS NOT NULL THEN STRSQL := STRSQL || ' AND c.QuestionClassName=''' || NVL(questionSort,'') || ''''; END IF;
IF isuser IS NOT NULL THEN STRSQL := STRSQL || ' AND a.UseChk ='''|| NVL(isuser,'')||''''; END IF;
IF questionCode IS NOT NULL THEN STRSQL := STRSQL || ' AND b.QuestionTypeName like''%' || NVL(questionCode,'') || '%'''; END IF;
IF questionDescrb IS NOT NULL THEN STRSQL:=STRSQL || ' AND a.QuestionName=''' || NVL(questionDescrb,'')||'''';END IF;
IF ischeckTable IS NOT NULL THEN STRSQL:=STRSQL || ' AND a.AnswerChk=''' || NVL(ischeckTable,'')||'''';END IF;
STRSQL := STRSQL ||' ORDER BY QuestionID ';
OPEN O_Cursor FOR STRSQL;
END;
--------------------------------------------------------------------------------------------------------------执行这段语句时,报错 "ORA-01722: 无效数字\n"。
-- 注意:如果你将一个数值字段(number等类型的字段)去连接一个字符串时
-- 必须先将数值字段转换为字符串类型-- 例如:假设表t 有col1字段,其类型为number(18,2)select col1||' $' from t;-- 上面这样是不对的
select to_char(col1)||' $' from t;-- 必须先用to_char()函数将数值转换为字符串类型!(至于你要转换的特定格式(精确度),你自己看着办)
我猜是你给一个number行的字段传了一个字符型的参数吧