oracle存储过程,提示创建的过程有编译错误,应该怎么修改?CREATE OR REPLACE PROCEDURE proc_favor_insert(proc_info out VARCHAR2) IS
house_favorID2 VARCHAR2(10)
favor_name2 VARCHAR2(20);
favor_re2 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_favorID,4,4) as int)+1 as VARCHAR2(20));
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END ;
/
house_favorID2 VARCHAR2(10)
favor_name2 VARCHAR2(20);
favor_re2 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_favorID,4,4) as int)+1 as VARCHAR2(20));
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END ;
/
CREATE OR REPLACE PROCEDURE proc_favor_insert(proc_info out VARCHAR2) IS
house_favorID2 VARCHAR2(10);--少了分号
favor_name2 VARCHAR2(20);
favor_re2 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 --oracle连接字符用||
house_favorID2:='fav'||cast(cast(substr(house_favorID,4,4) as int)+1 as VARCHAR2(20));
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0 then--少了then
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END ;
CREATE OR REPLACE PROCEDURE proc_favor_insert
(favor_name2 VARCHAR2,--你的这两个变量应该是从外面传进去的吧
favor_re2 VARCHAR2,
proc_info out VARCHAR2)
IS
house_favorID2 VARCHAR2(10);--少了分号
cou number;
BEGIN
SELECT MAX(house_favorID) INTO house_favorID2 from tb_favor;
IF house_favorID2 IS NULL
THEN
house_favorID2:='fav1001';
ELSE --oracle连接字符用||
house_favorID2:='fav'||cast(cast(substr(house_favorID,4,4) as int)+1 as VARCHAR2(20));
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0 then--少了then
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END ;
错误是PLS-00103:出现符号"END"在需要下列之一时:begin function package pragma procedure subtype type use<an identifier> <a double-quoted delimited-identifier> form current cursor
CREATE OR REPLACE PROCEDURE proc_favor_insert(proc_info out VARCHAR2) IS
house_favorID2 VARCHAR2(10)
favor_name2 VARCHAR2(20);
favor_re2 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_re2);
proc_info:='OK';
END IF;
END ;
CREATE OR REPLACE PROCEDURE XIAO.proc_favor_insert(proc_info out VARCHAR2) IS
house_favorID2 VARCHAR2(10):=null;
house_name2 VARCHAR2(20):='';
favor_re2 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);
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0 THEN
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END ;
最后修改为上面的代码,还是有个错误:"PLS-00103:出现符号"FAVOR_REMARK2"在需要下列之一时: := ) @% default character"
赐教~~~
house_favorID2 VARCHAR2(10); ---分号
favor_name2 VARCHAR2(20);
favor_re2 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_re2);
proc_info:='OK';
END IF;
END ;
favor_name2是待用户插入记录,从外部传入
---猜的
CREATE OR REPLACE PROCEDURE proc_favor_insert(proc_info out VARCHAR2,favor_name2 VARCHAR2,favor_re2 VARCHAR2,house_favorID varchar2) IS
house_favorID2 VARCHAR2(10); ---分号
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_favorID,4,4) as int)+1 as VARCHAR2(20));
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0 then ---少了then
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END ;
'fav'+ 改成 'fav'||
house_favorID2 VARCHAR2(10);
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);
END IF;
SELECT COUNT(*) INTO cou, FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0 THEN
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END ;
提示的错误是
Compilation errors for PROCEDURE XIAO.PROC_FAVOR_INSERTError: PLS-00103: 出现符号 ";"在需要下列之一时:
;
符号 ";在 ";" 继续之前已插入。
Line: 11
Text: END IF;
--多了个,
--然后再检查看看你的标点符号是不是英文的
CREATE OR REPLACE PROCEDURE XIAO.proc_favor_insert
(house_name2 VARCHAR2, favor_re2 VARCHAR2 ,proc_info out VARCHAR2)
IS
house_favorID2 VARCHAR2(10);
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);
END IF;
SELECT COUNT(*) INTO cou FROM tb_favor WHERE favor_name=favor_name2;
IF cou>0 THEN
proc_info:='isHave';
ELSE
INSERT INTO tb_favor VALUES(house_favorID2,favor_name2,favor_re2);
proc_info:='OK';
END IF;
END;