这个是生成新存储过程的语句:
CR_PROCE:='CREATE OR REPLACE PROCEDURE '||'RUL'||'_'||PROCE_NAME_STR||'('||'PYDM1'||' IN OUT INTEGER'||')'||' AS '||CHR(13)||'BEGIN'||CHR(13)||
'IF '||'PYDM1'||'='' '''||' THEN'||CHR(13)||
'DBMS_OUTPUT.put_line(''您输入的数据是空值!'');'||CHR(13)||
'END IF;'||CHR(13)|| 'IF (TO_NUMBER('||'PYDM1'||') < '||CS1||' OR TO_NUMBER('||'PYDM1'||') > '||CS2||') '||'THEN'||CHR(13)||
'DBMS_OUTPUT.put_line('''||SJXMC1||'输入的数据必须在'||CS1||'--'||CS2||'之间!!'');'||CHR(13)||
'ELSE '||CHR(13)||
'DBMS_OUTPUT.put_line('''||'输入成功!'||''');'||CHR(13)||
'END IF;'||CHR(13)||'END;'||CHR(13);
EXECUTE IMMEDIATE CR_PROCE;
CR_PROCE:='CREATE OR REPLACE PROCEDURE '||'RUL'||'_'||PROCE_NAME_STR||'('||'PYDM1'||' IN OUT INTEGER'||')'||' AS '||CHR(13)||'BEGIN'||CHR(13)||
'IF '||'PYDM1'||'='' '''||' THEN'||CHR(13)||
'DBMS_OUTPUT.put_line(''您输入的数据是空值!'');'||CHR(13)||
'END IF;'||CHR(13)|| 'IF (TO_NUMBER('||'PYDM1'||') < '||CS1||' OR TO_NUMBER('||'PYDM1'||') > '||CS2||') '||'THEN'||CHR(13)||
'DBMS_OUTPUT.put_line('''||SJXMC1||'输入的数据必须在'||CS1||'--'||CS2||'之间!!'');'||CHR(13)||
'ELSE '||CHR(13)||
'DBMS_OUTPUT.put_line('''||'输入成功!'||''');'||CHR(13)||
'END IF;'||CHR(13)||'END;'||CHR(13);
EXECUTE IMMEDIATE CR_PROCE;
解决方案 »
- oracle向导来创建数据库出错
- Oracle高级复制问题
- 数据库恢复问题:(
- 高手请帮忙!
- 面试题目,尝试答一下,我没有答上来!
- 请问高手,如何知道一个表里的一个字段给那个表的外键,我需要把表名,名段名查出来。
- 我在安装oracle9i时有个名字为ociw32.dll的文件出错,谁能把这个文件考给我!
- iis连接不上别的机器上的ORACLE服务器
- oracle中日期格式转换 现在是23/02/2015 如何转换成2015-02-23
- 并发像同一张ORACLE插入会锁表吧,有向数据库缓存并发插入,=这种方式吗?
- 为什么Oracle书上的SQL都没有schema?
- 调用oracle存储过程错:ora-06502:PL/SQL数字或值错误
CREATE OR REPLACE PROCEDURE RUL_ATSJ02_WZJS(PYDM1 IN OUT INTEGER) AS
BEGIN
IF PYDM1=' ' THEN
DBMS_OUTPUT.put_line('您输入的数据是空值!');
END IF;
IF (TO_NUMBER(PYDM1) < 5 OR TO_NUMBER(PYDM1) > 10000) THEN
DBMS_OUTPUT.put_line('完钻井深输入的数据必须在5--10000之间!!');
ELSE
DBMS_OUTPUT.put_line('输入成功!');
END IF;
END;
2 BEGIN
3 IF PYDM1=' ' THEN
4 DBMS_OUTPUT.put_line('您输入的数据是空值!');
5 END IF;
6 IF (TO_NUMBER(PYDM1) < 5 OR TO_NUMBER(PYDM1) > 10000) THEN
7 DBMS_OUTPUT.put_line('完钻井深输入的数据必须在5--10000之间!!');
8 ELSE
9 DBMS_OUTPUT.put_line('输入成功!');
10 END IF;
11 END;
12 /过程已创建。SQL> 这个生成出来的过程是好的啊,是哪个有问题呢
CREATE OR REPLACE PROCEDURE TEST(SJJMC1 IN VARCHAR2)
AS
--定义变量SJXMC1 VARCHAR2(100);
--PYDM1 INTEGER;
WZ1 integer;
WZ2 INTEGER;
WZ3 integer;
WZ4 INTEGER;
WZ5 INTEGER;
WZ6 INTEGER;
CS1 VARCHAR2(10);
CS2 VARCHAR2(30);
PROCE_NAME_STR VARCHAR2(300);
CR_PROCE VARCHAR2(3000);--定义游标
--CURSOR S_SJJMC IS SELECT DISTINCT SJJMC FROM HT."SYS_Dic03" WHERE SJJMC=SJJMC1;
CURSOR S_PYDM IS SELECT SJJMC,SJXMC,PYDM,ZYS FROM HT."SYS_Dic03" WHERE SJJMC like SJJMC1 AND (ZYS LIKE'%<%<%' OR ZYS LIKE'%≤%≤%' OR ZYS LIKE'%<%≤%' OR ZYS LIKE'%≤%<%' OR ZYS LIKE'%<=%≤%' OR ZYS LIKE'%≤%<=%')
ORDER BY SJJMC,XH;
TEMPROW S_PYDM%ROWTYPE;BEGIN
--给变量赋初值
SJXMC1:='';
PROCE_NAME_STR:='';
CR_PROCE:='';
WZ1:='';
WZ2:='';
WZ3:='';
WZ4:='';
WZ5:='';
WZ6:='';
CS1:='';
CS2:='';--打开游标
OPEN S_PYDM;LOOP FETCH S_PYDM INTO TEMPROW;
--EXIT WHEN S_PYDM%NOTFOUND;
--定义约束
--IF S_PYDM%FOUND THENSJXMC1:=TEMPROW.SJXMC;
PROCE_NAME_STR:=TEMPROW.SJJMC||'_'||TEMPROW.PYDM;
WZ1:=INSTR(TEMPROW.ZYS,'<=',1,1);
WZ2:=INSTR(TEMPROW.ZYS,'<=',1,2);
WZ3:=INSTR(TEMPROW.ZYS,'<',1,1);
WZ4:=INSTR(TEMPROW.ZYS,'<',1,2);
WZ5:=INSTR(TEMPROW.ZYS,'≤',1,1);
WZ6:=INSTR(TEMPROW.ZYS,'≤',1,2);--WZ5:=INSTR(TEMPROW.ZYS,'')
-- <= / <=
IF (WZ1!=0 AND WZ2!=0 AND WZ1<WZ2) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' AA');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ1-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ2+2,LENGTH(RTRIM(TEMPROW.ZYS))-WZ2-1);
ELSIF
-- <= / <
(WZ1!=0 AND WZ3!=0 AND WZ1<WZ3) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' BB');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ1-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ3+1,LENGTH(RTRIM(TEMPROW.ZYS))-WZ3);
ELSIF
-- < / <=
(WZ3!=0 AND WZ1!=0 AND WZ3<WZ1) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' CC');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ3-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ1+2,LENGTH(RTRIM(TEMPROW.ZYS))-WZ1-1);
-- ≤/ ≤
ELSIF
(WZ5!=0 AND WZ6!=0 AND WZ5<WZ6) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' DD');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ5-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ6+1,LENGTH(RTRIM(TEMPROW.ZYS))-WZ6);
-- ≤/ <
ELSIF
(WZ5!=0 AND WZ3!=0 AND WZ5<WZ3) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' EE');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ5-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ3+1,LENGTH(RTRIM(TEMPROW.ZYS))-WZ3);
-- < / ≤
ELSIF
(WZ3!=0 AND WZ5!=0 AND WZ5>WZ3) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' FF');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ3-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ5+1,LENGTH(RTRIM(TEMPROW.ZYS))-WZ5);
-- ≤/ <=
ELSIF
(WZ5!=0 AND WZ1!=0 AND WZ1>WZ5) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' GG');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ5-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ1+2,LENGTH(RTRIM(TEMPROW.ZYS))-WZ1-1);
-- <=/≤
ELSIF
(WZ1!=0 AND WZ5!=0 AND WZ1<WZ5) THEN
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' HH');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ1-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ5+1,LENGTH(RTRIM(TEMPROW.ZYS))-WZ5);
ELSE
-- < / <
DBMS_OUTPUT.put_line(TEMPROW.SJJMC||TEMPROW.SJXMC||TEMPROW.ZYS||' II');
CS1:=SUBSTR(TEMPROW.ZYS,1,WZ3-1);
CS2:=SUBSTR(TEMPROW.ZYS,WZ4+1,LENGTH(RTRIM(TEMPROW.ZYS))-WZ4);
END IF;
CR_PROCE:='CREATE OR REPLACE PROCEDURE '||'RUL'||'_'||PROCE_NAME_STR||'('||'PYDM1'||' IN OUT INTEGER'||')'||' AS '||CHR(13)||'BEGIN'||CHR(13)||
'IF '||'PYDM1'||'='' '''||' THEN'||CHR(13)||
'DBMS_OUTPUT.put_line(''您输入的数据是空值!'');'||CHR(13)||
'END IF;'||CHR(13)|| 'IF (TO_NUMBER('||'PYDM1'||') < '||CS1||' OR TO_NUMBER('||'PYDM1'||') > '||CS2||') '||'THEN'||CHR(13)||
'DBMS_OUTPUT.put_line('''||SJXMC1||'输入的数据必须在'||CS1||'--'||CS2||'之间!!'');'||CHR(13)||
'ELSE '||CHR(13)||
'DBMS_OUTPUT.put_line('''||'输入成功!'||''');'||CHR(13)||
'END IF;'||CHR(13)||'END;'||CHR(13);
EXECUTE IMMEDIATE CR_PROCE;EXIT WHEN S_PYDM%NOTFOUND;SJXMC1:='';
PROCE_NAME_STR:='';
CR_PROCE:='';
WZ1:='';
WZ2:='';
WZ3:='';
WZ4:='';
WZ5:='';
WZ6:='';
CS1:='';
CS2:='';
--EXIT WHEN S_PYDM%NOTFOUND;END LOOP;
CLOSE S_PYDM;
COMMIT;
END;
WZ2:='';
WZ3:='';
WZ4:='';
WZ5:='';
WZ6:='';
先把这些改成=0;再把所有的CR_PROCE的内容输出,注释掉EXECUTE IMMEDIATE CR_PROCE,检查每一条输出的语句是否正确。
估计因为你的变量的值的问题导致有的语句非法了