用mysql做了一个项目、现在要转到oracle上、请问各位哥哥姐姐们、两个数据库的存储过程有什么区别、转过去的话要注意什么?还有 DEFINER=`root`@`localhost`是什么意思、换到oracle上能不能把他去掉、下面的是mysql中的其中的一个procedure、各位大侠帮忙看看CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_paper_auto_make2`(
p_plan_id int(10),
p_pool_id int(10),
p_pool_id2 int(10),
p_pool_id3 int(10),
p_pool_id4 int(10),
p_pool_id5 int(10),
p_login_user_id varchar(32)
)
begindeclare v_paper_id int;DECLARE v_done INT DEFAULT 0;DECLARE cur_plan_user cursor FOR
select id from t_test_paper
where plan_id = p_plan_id;DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=1;
set v_done = 0;
open cur_plan_user;
userloop:loop
fetch cur_plan_user into v_paper_id;
IF v_done=1
THEN
LEAVE userloop;
END IF;
call pro_paper_auto_make(p_plan_id,v_paper_id,p_pool_id,p_pool_id2,p_pool_id3,p_pool_id4,p_pool_id5,p_login_user_id);
end loop userloop;
close cur_plan_user;commit;end;
p_plan_id int(10),
p_pool_id int(10),
p_pool_id2 int(10),
p_pool_id3 int(10),
p_pool_id4 int(10),
p_pool_id5 int(10),
p_login_user_id varchar(32)
)
begindeclare v_paper_id int;DECLARE v_done INT DEFAULT 0;DECLARE cur_plan_user cursor FOR
select id from t_test_paper
where plan_id = p_plan_id;DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=1;
set v_done = 0;
open cur_plan_user;
userloop:loop
fetch cur_plan_user into v_paper_id;
IF v_done=1
THEN
LEAVE userloop;
END IF;
call pro_paper_auto_make(p_plan_id,v_paper_id,p_pool_id,p_pool_id2,p_pool_id3,p_pool_id4,p_pool_id5,p_login_user_id);
end loop userloop;
close cur_plan_user;commit;end;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=1;报这样一个错:
错误:PLS-00103: 出现符号 "FOR"在需要下列之一时:
:= . ( @ % ; not null range
default character
行:52
文本:DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_last_row_fetched=1;
p_pool_id number(10),
p_pool_id2 number(10),
p_pool_id3 number(10),
p_pool_id4 number(10),
p_pool_id5 number(10),
p_login_user_id varchar2(32)) is
v_paper_id number;
begin DECLARE
cur_plan_user cursor FOR
select id from t_test_paper where plan_id = p_plan_id;
open cur_plan_user;
loop
fetch cur_plan_user numbero v_paper_id;
exit when cur_plan_user%notfound;
pro_paper_auto_make(p_plan_id,
v_paper_id,
p_pool_id,
p_pool_id2,
p_pool_id3,
p_pool_id4,
p_pool_id5,
p_login_user_id);
end loop;
close cur_plan_user;
commit;
end;
CREATE or replace PROCEDURE pro_paper_auto_make2(p_plan_id number(10),
p_pool_id number(10),
p_pool_id2 number(10),
p_pool_id3 number(10),
p_pool_id4 number(10),
p_pool_id5 number(10),
p_login_user_id varchar2(32)) is
begin for x in (select id from t_test_paper where plan_id = p_plan_id)
loop
pro_paper_auto_make(p_plan_id,
x.id,
p_pool_id,
p_pool_id2,
p_pool_id3,
p_pool_id4,
p_pool_id5,
p_login_user_id);
end loop;
commit;
end;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=1;
是什么意思?应该怎么改啊
declare
cursor cur_plan_detail is
select
c.question_no_b,c.point_per, c.difficult_y,c.difficult_z,c.difficult_n,c.type
from t_test_plan a,t_test_paper_question_b c
where a.id = c.plan_id
and a.id = p_plan_id
order by question_no_b
;
delete from t_test_paper_question_temp where paper_id=p_paper_id;
set v_last_row_fetched = 0;
open cur_plan_detail;
我在游标下面写了个sql语句和set语句、为什么会报错呢?这里不能写吗?应该在哪里写
那这个应该怎么写啊、
PROCEDURE CLL.PRO_PAPER_AUTO_MAKE 编译错误错误:PLS-00103: 出现符号 "."在需要下列之一时:
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
符号 "<an identifier>" 被替换为 "." 后继续。
行:51
文本:dbms_output.put_line('dddddddd'||'ddfdf');错误:PLS-00103: 出现符号 "DELETE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DELETE" 后继续。
行:52
文本:delete from t_test_paper_question_temp where paper_id=p_paper_id;错误:PLS-00049: 错误的赋值变量 'LOOP'
行:56
文本:planloop:loop错误:PLS-00103: 出现符号 ""在需要下列之一时:
:= . ( @ % ;
符号 ";在 "" 继续之前已插入。
行:56
文本:planloop:loop错误:PLS-00103: 出现符号 "I"在需要下列之一时:
transaction <a SQL statement>
行:61
文本:set i=1;错误:PLS-00103: 出现符号 "J"在需要下列之一时:
transaction <a SQL statement>
行:62
文本:set j=1;错误:PLS-00103: 出现符号 "TOTLE"在需要下列之一时:
transaction
<a SQL statement>
行:63
文本:set totle=0;错误:PLS-00049: 错误的赋值变量 'WHILE'
行:67
文本:difficult_y_loop:while i<=v_difficult_y do错误:PLS-00103: 出现符号 ""在需要下列之一时:
:= . ( @ % ;
行:67
文本:difficult_y_loop:while i<=v_difficult_y do
就是这个错误错误:PLS-00103: 出现符号 "DELETE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DELETE" 后继续。
行:52
文本:delete from t_test_paper_question_temp where paper_id=p_paper_id;