create or replace procedure test1(ywtypeid in varchar2)
as
asknum number;
answerNum number;
beginselect count(问题编号) a into asknum from 问题表 where 问题类型=ywtypeid;
CASE asknum
WHEN 1 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
WHEN 2 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
WHEN 3 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'3','yesno');
END CASE;end test1;
执行结果,我真不知道怎么好了??
SQL> execute test1('北京');begin test1('北京'); end;ORA-06592: 执行 CASE 语句时未找到 CASE
ORA-06512: 在"CCDBUSER.TEST1", line 10
ORA-06512: 在line 1
as
asknum number;
answerNum number;
beginselect count(问题编号) a into asknum from 问题表 where 问题类型=ywtypeid;
CASE asknum
WHEN 1 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
WHEN 2 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
WHEN 3 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'3','yesno');
END CASE;end test1;
执行结果,我真不知道怎么好了??
SQL> execute test1('北京');begin test1('北京'); end;ORA-06592: 执行 CASE 语句时未找到 CASE
ORA-06512: 在"CCDBUSER.TEST1", line 10
ORA-06512: 在line 1
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
.....
asknum number;
answerNum number;
begin
select count(问题编号) a
into asknum
from 问题表
where 问题类型 = ywtypeid;
if asknum = 1 then
insert into 问题表1
(问题类型, 问题编号, 问题内容)
values
(ywtypeid, '1', 'ok');
else
if asknum = 2 then
insert into 问题表1
(问题类型, 问题编号, 问题内容)
values
(ywtypeid, '1', 'ok');
insert into 问题表1
(问题类型, 问题编号, 问题内容)
values
(ywtypeid, '2', 'no');
else
if asknum = 2 then
insert into 问题表1
(问题类型, 问题编号, 问题内容)
values
(ywtypeid, '1', 'ok');
insert into 问题表1
(问题类型, 问题编号, 问题内容)
values
(ywtypeid, '2', 'no');
insert into 问题表1
(问题类型, 问题编号, 问题内容)
values
(ywtypeid, '3', 'yesno');
end if;
commit;
end;
create or replace procedure test3(ywtypeid in varchar2)
as
asknum number;
answerNum number;
begin
select count(问题编号) a into asknum from 问题表 where 问题类型=ywtypeid;
select COUNT(*) b into answerNum from 问题表1 where 问题类型=ywtypeid;if answerNum>0 then
delete from 问题表1 where 问题类型=ywtypeid;else
case when asknum=1 then
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
when asknum=2 then
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
when asknum=3 then
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'3','yesno');
end case ;
end if ;
end test3;
as
asknum number;
answerNum number;
begin
select count(问题编号) a into asknum from 问题表 where 问题类型=ywtypeid;
select COUNT(*) b into answerNum from 问题表1 where 问题类型=ywtypeid; if answerNum>0 then
delete from 问题表1 where 问题类型=ywtypeid; else
case when asknum=1 then
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
when asknum=2 then
BEGIN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
END;
when asknum=3 then
BEGIN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'3','yesno');
END;
ELSE BEGIN insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
END;
end case ;
end if ;
end test3;问题出在,你要给asknum不在(‘1’,‘2’,‘3’)之外的值给一个出路,
按上面的试试吧。
CASE asknum
WHEN 1 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
WHEN 2 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
WHEN 3 THEN
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'3','yesno');
else
statements here;
or
null;
END CASE;
看看我2楼的代码.
create or replace procedure test3(ywtypeid in varchar2)
as
asknum number;
answerNum number;
begin
select count(问题编号) a into asknum from 问题表 where 问题类型=ywtypeid;
select COUNT(*) b into answerNum from 问题表1 where 问题类型=ywtypeid; if answerNum>0 then
BEGIN
delete from 问题表1 where 问题类型=ywtypeid;
END;
else
case asknum
when 1 then
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
when 2 then
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
when 3 then
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'1','ok');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'2','no');
insert into 问题表1 (问题类型,问题编号,问题内容) values (ywtypeid,'3','yesno');
ELSE
select * from 问题表1;
end case ;
end if ;
end test3;
不管怎么着都是这个错误,那个过程都不行???
SQL> execute test4('北京');begin test4('北京'); end;ORA-06550: 第 1 行, 第 7 列:
PLS-00905: 对象 CCDBUSER.TEST4 无效
ORA-06550: 第 1 行, 第 7 列: create or replace procedure test4(ywtypeid in varchar2) 这个就是第一行啊
自己多调试,有些基本的东西别人是帮不了你的.干脆帮你写好了:
SQL> create table tb (lx varchar2(10),bh varchar2(10),nr varchar2(100));Table createdSQL> create table tb1 (lx varchar2(10),bh varchar2(10),nr varchar2(100));Table createdSQL> insert into tb values('1','jzyf','dfaf');1 row insertedSQL> commit;Commit complete--下面一段是你需要代码:
create or replace procedure test1(ywtypeid in varchar2) as
asknum number;
answerNum number;
begin
select count(bh) a into asknum from tb where lx = ywtypeid;
if asknum = 1 then
insert into tb1 (lx, bh, nr) values (ywtypeid, '1', 'ok');
else
if asknum = 2 then
insert into tb1 (lx, bh, nr) values (ywtypeid, '1', 'ok');
insert into tb1 (lx, bh, nr) values (ywtypeid, '2', 'no');
else
if asknum = 3 then
insert into tb1 (lx, bh, nr) values (ywtypeid, '1', 'ok');
insert into tb1 (lx, bh, nr) values (ywtypeid, '2', 'no');
insert into tb1 (lx, bh, nr) values (ywtypeid, '3', 'yesno');
end if;
end if;
end if;
commit;
end;SQL> execute test1('1');PL/SQL procedure successfully completedSQL> select * from tb1;LX BH NR
---------- ---------- -------------------
1 1 ok
CREATE OR REPLACE procedure CCDBUSER.test2(ywtypeid in varchar2)
as
asknum number;
answerNum number;
groupNum long;
askid number;
askadd number;
beginselect count(问题编号) a into asknum from 问题表 where 问题类型=ywtypeid;select count(问题编号) b into answerNum from 问题表1 where 问题类型=ywtypeid;select POWER('3',asknum) c into groupNum from dual; askadd:=1 ;
askid:=1 ;
while askid<=groupNum loop
askid:= askid + askadd ;
insert into 答案组合表(id) VALUES (askid) ;
END loop;end test2;
直接用if 语句就好控制了。
isbegin
case province
when 1 then
dbms_output.put_line('3333333');
when 2 then
dbms_output.put_line('2222');
else
dbms_output.put_line('4444444');
end case;end;这个是我写的 运行正常
你的procedure 自己检查一下,,
一步步测测
ASKNUM NUMBER;
ANSWERNUM NUMBER;
log_error EXCEPTION;
BEGIN
SELECT COUNT(问题编号) A
INTO ASKNUM
FROM 问题表
WHERE 问题类型 = YWTYPEID;
SELECT COUNT(*) B INTO ANSWERNUM FROM 问题表1 WHERE 问题类型 = YWTYPEID; IF ANSWERNUM > 0 THEN
DELETE FROM 问题表1 WHERE 问题类型 = YWTYPEID;
END IF;
IF ASKNUM = 1 THEN INSERT INTO 问题表1
(问题类型, 问题编号, 问题内容)
VALUES
(YWTYPEID, '1', 'ok');
ELSIF ASKNUM = 2 THEN INSERT INTO 问题表1
(问题类型, 问题编号, 问题内容)
VALUES
(YWTYPEID, '1', 'ok');
INSERT INTO 问题表1
(问题类型, 问题编号, 问题内容)
VALUES
(YWTYPEID, '2', 'no'); ELSIF ASKNUM = 3 THEN
BEGIN
INSERT INTO 问题表1
(问题类型, 问题编号, 问题内容)
VALUES
(YWTYPEID, '1', 'ok');
INSERT INTO 问题表1
(问题类型, 问题编号, 问题内容)
VALUES
(YWTYPEID, '2', 'no');
INSERT INTO 问题表1
(问题类型, 问题编号, 问题内容)
VALUES
(YWTYPEID, '3', 'yesno');
END;
END IF;
END TEST3;