Question

oracle 创建存储过程提示编译错误,错误在哪里?

CREATE OR REPLACE PROCEDURE delDic(id1 number)
AS
childCount number DEFAULT 0;
parentID number DEFAULT 0;
BEGIN

select parent_id into parentID from sys_dic where dic_id = id1;
if (parentID > 0) then
begin
select count(dic_id) into childCount from sys_dic where parent_id = parentID and dic_status = 'active';
update sys_dic set child_count = childCount where dic_id = parentID;
end;
end if;
commit;
END;

Answer 1

CREATE OR REPLACE PROCEDURE proc_favor_insert(proc_info out VARCHAR2) IS
house_favorID2 VARCHAR2(10)
favor_name2 VARCHAR2(20);
favor_remark2 VARCHAR2(50);
cou number;
BEGIN
SELECT MAX(house_favorID) INTO house_favorID2 from tb_favor;
IF house_favorID2 IS NULL
THEN
house_favorID2:='fav1001';
ELSE
house_favorID2:='fav'+cast(cast(substr(house_favorID2,4,4) as int)+1 as VARCHAR2(20));

---cast(cast(substr(house_favorID2,4,4) as int)+ 是house_favorID2 不是house_favorID
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
---favor_name2的值呢 你没赋值 怎么来
IF cou>0
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_remark2);
proc_info:='OK';
END IF;
END ;

追问

能不能看我的创建语句看看问题所在,而不是拷贝别的地方答案,谢谢了

Answer 2

* 具体错误信息放上来
* commit 语句，之前没有begin transaction啊

追问

就提示 [Err] ORA-24344: success with compilation error 可是我去掉commit语句也不可以

追答

SELECT xx INTO 变量 FROM ...，这个语法有问题（INTO的是表，而非变量）。

改成如下试试：
SELECT 变量 = xx FROM ...

追问

我把if语句去掉就不报错了,我换衣是if语句格式哪里错误了?您能把我看下吗