1.用异常处理
CREATE OR REPLACE PROCEDURE Auto_Arch_Job_Start_Stop(result OUT VARCHAR2) IS
jobid INTEGER;
job_status VARCHAR2(1);
BEGIN
SELECT job,broken INTO jobid,job_status FROM all_jobs WHERE what='AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;';
IF job_status='N' THEN
dbms_job.broken(jobid,TRUE);
COMMIT;
result:='恭喜你!临时数据自动导入设定已经停止!';
ELSE
dbms_job.broken(jobid,FALSE);
COMMIT;
result:='恭喜你!临时数据自动导入设定已经开始!';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
result:='无此Job!';
END Auto_Arch_Job_Start_Stop;2. 用Decode函数处理
select decode(col1,null,null,'aaaaa') into v_min from tameter;
CREATE OR REPLACE PROCEDURE Auto_Arch_Job_Start_Stop(result OUT VARCHAR2) IS
jobid INTEGER;
job_status VARCHAR2(1);
BEGIN
SELECT job,broken INTO jobid,job_status FROM all_jobs WHERE what='AMETDATA_TO_ARCH;CIRCDATA_TO_ARCH;';
IF job_status='N' THEN
dbms_job.broken(jobid,TRUE);
COMMIT;
result:='恭喜你!临时数据自动导入设定已经停止!';
ELSE
dbms_job.broken(jobid,FALSE);
COMMIT;
result:='恭喜你!临时数据自动导入设定已经开始!';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
result:='无此Job!';
END Auto_Arch_Job_Start_Stop;2. 用Decode函数处理
select decode(col1,null,null,'aaaaa') into v_min from tameter;
SELECT
A
INTO
AZAZ
FROM
AAA
WHERE
条件;
EXCEPTION
WHEN NO_DATA_FOUND THEN
result:='无此Job!';
END ;
BEGIN
SELECT
A
INTO
AZAZ
FROM
AAA
WHERE
条件;
EXCEPTION
WHEN NO_DATA_FOUND THEN
result:='无此Job!';
END ;
BEGIN
SELECT
A
INTO
AZAZ
FROM
AAA
WHERE
条件;
EXCEPTION
WHEN NO_DATA_FOUND THEN
result:='无此Job!';
END ;
iSum number;
select count(*) into iSum from table where ..........
if iSum=0 then
return ;
end if;
...方法二:异常处理
begin
select .. into ... where .....
.........
exception
when no_data_found then
return "没有记录";
when others then
return "其他错误"
end ;
A
INTO
AZAZ
FROM
AAA
WHERE
条件
EXCEPTION
when NO_DATA_FOUND then
raise_application_error( -20903, '查询表出错|| sqlerrm ) ;
END ;
select count(*) into counter from...
如果counter=1就执行,否则跳出。