oracle存储过程里要判断一个变量值是不是在存在于数据库中,怎么实现?
一个变量
question_sum number;
我要判断这个变量是不是已经存在与数据库中:
if question_sum not in (select question_sum from table_1)这在存储过程里应该怎么写?
谢谢大家!
一个变量
question_sum number;
我要判断这个变量是不是已经存在与数据库中:
if question_sum not in (select question_sum from table_1)这在存储过程里应该怎么写?
谢谢大家!
if后面只能跟逻辑表达式 不支持sql查询 这就是我碰到的问题继续等待
declare
cnt number;
begin
select count(*) into cnt
from table_1
where question_sum is not null;if cnt<>0 then ...
是看这个变量在不在表的一列中。下面的例子,判断变量question_sum在不在表table_1的列question_sum中。
if question_sum not in (select question_sum from table_1)。
写在存储过程里应该怎么写?
in_var number;
cnt number;
begin
select count(*) into cnt
from table_1
where question_sum=in_var;if cnt=0 then ...
CREATE OR REPLACE PROCEDURE STORE_WIKI AS
DECLARE
question varchar(100);
answer varchar(1000);
url varchar(1000);
question_sum number;
cursor cur is SELECT WIKI_EXPLANATION.TITLE,WIKI_EXPLANATION.EXPLANATION,WIKI_EXPLANATION.URL
FROM WIKI_EXPLANATION
WHERE WIKI_EXPLANATION.URL NOT IN (SELECT URL FROM HAOBAI);
begin
open cur;
fetch cur into question,answer,url;
while cur %found loop
select count(*) into question_sum from HAOBAI where HAOBAI.QUESTION = question;
if question_sum = 0
then
INSERT
INTO HAOBAI
VALUES(question,answer,url);
end if;
commit;
fetch cur into question,answer,url;
end loop;
close cur;
end;
[code]
大家看看我的这个存储过程。表HAOBAI是个空表,为什么执行这个存储过程后,表HAOBAI里只插入了一条数据呢?
大家看看语法对不对,有没有死循环或者别的语法错误?
大家看看语法对不对,有没有死循环或者别的语法错误?
[code=SQL]
CREATE OR REPLACE PROCEDURE STORE_WIKI AS
DECLARE
question varchar(100);
answer varchar(1000);
url varchar(1000);
question_sum number;
cursor cur is SELECT WIKI_EXPLANATION.TITLE,WIKI_EXPLANATION.EXPLANATION,WIKI_EXPLANATION.URL
FROM WIKI_EXPLANATION
WHERE WIKI_EXPLANATION.URL NOT IN (SELECT URL FROM HAOBAI);
begin
open cur;
fetch cur into question,answer,url;
while cur %found loop
select count(*) into question_sum from HAOBAI where HAOBAI.QUESTION = question;
if question_sum = 0
then
INSERT
INTO HAOBAI
VALUES(question,answer,url);
end if;
commit;
fetch cur into question,answer,url;
end loop;
close cur;
end;
[code]
SELECT WIKI_EXPLANATION.TITLE,WIKI_EXPLANATION.EXPLANATION,WIKI_EXPLANATION.URL
FROM WIKI_EXPLANATION
WHERE WIKI_EXPLANATION.URL NOT IN (SELECT URL FROM HAOBAI);
我也觉得很奇怪 没有加上那个if then判断的话,是没有问题的。加上了以后,就出现了这样的问题。好像是当出入第一条元组以后,question_sum 的值就一直为1了。
我刚学这个不久,也搞不清楚到底哪里不对!
http://topic.csdn.net/u/20090504/13/7a1c102d-ba05-49db-9298-169081e00485.html