create or replace procedure F_USERToT_USER is
begin
declare
varid f_userinfo.userid%type;
var_id t_userinfo.userid%type;
varname f_userinfo.username%type;///这行有错
varage f_userinfo.age%type;
varsex f_userinfo.sex%type;
vardeptno f_userinfo.deptno%type;
CURSOR mycur is
select userid,username,age,sex,deptno from f_Userinfo where userId is not null;
begin open mycur;
loop
fetch mycur into varid,varname,varage,varsex,vardeptno;
exit when mycur%notfound; select userid into var_id from t_userinfo where userid=varid;
if var_id= null then
insert into t_userinfo values(varid,varname,varage,varsex,vardeptno);
else
update t_userinfo set userName=varname,age=varage,sex=varsex,deptno=vardeptno
where userid=var_id;
end loop;
close mycur
exception when others then
dbms_output.put_line("exception occurs!");
end;
end F_USERToT_USER;
这是源码,不知道错在哪?我把begin和declare去掉,把is改成as后,还是出错??
请高手帮忙一下!!谢谢!!!
begin
declare
varid f_userinfo.userid%type;
var_id t_userinfo.userid%type;
varname f_userinfo.username%type;///这行有错
varage f_userinfo.age%type;
varsex f_userinfo.sex%type;
vardeptno f_userinfo.deptno%type;
CURSOR mycur is
select userid,username,age,sex,deptno from f_Userinfo where userId is not null;
begin open mycur;
loop
fetch mycur into varid,varname,varage,varsex,vardeptno;
exit when mycur%notfound; select userid into var_id from t_userinfo where userid=varid;
if var_id= null then
insert into t_userinfo values(varid,varname,varage,varsex,vardeptno);
else
update t_userinfo set userName=varname,age=varage,sex=varsex,deptno=vardeptno
where userid=var_id;
end loop;
close mycur
exception when others then
dbms_output.put_line("exception occurs!");
end;
end F_USERToT_USER;
这是源码,不知道错在哪?我把begin和declare去掉,把is改成as后,还是出错??
请高手帮忙一下!!谢谢!!!
,用SQL>Show error;贴出出错问题看看
create or replace procedure F_USERToT_USER
is
varid f_userinfo.userid%type;
var_id t_userinfo.userid%type;
¡¡varname f_userinfo.username%type;///ÕâÐÐÓдí
varage f_userinfo.age%type;
varsex f_userinfo.sex%type;
vardeptno f_userinfo.deptno%type;
¡¡CURSOR mycur is
¡¡select userid,username,age,sex,deptno from f_Userinfo where userId is not null;
begin
open mycur;
loop
¡¡¡¡fetch mycur into varid,varname,varage,varsex,vardeptno;
exit when mycur%notfound; select userid into var_id from t_userinfo where userid=varid;
if var_id is null then ¡¡
¡¡ ¡¡ insert into t_userinfo values(varid,varname,varage,varsex,vardeptno);
else
update t_userinfo set userName=varname,age=varage,sex=varsex,deptno=vardeptno
where userid=var_id;
end loop;
close mycur
exception when others then
dbms_output.put_line("exception occurs!");
end;
end F_USERToT_USER;
/
错误挺多,修改后如下:
CREATE OR REPLACE PROCEDURE F_USERTOT_USER IS
BEGIN
DECLARE
VARID F_USERINFO.USERID%TYPE;
VAR_ID T_USERINFO.USERID%TYPE;
VARNAME F_USERINFO.USERNAME%TYPE;
VARAGE F_USERINFO.AGE%TYPE;
VARSEX F_USERINFO.SEX%TYPE;
VARDEPTNO F_USERINFO.DEPTNO%TYPE;
CURSOR MYCUR IS
SELECT USERID, USERNAME, AGE, SEX, DEPTNO
FROM F_USERINFO
WHERE USERID IS NOT NULL;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR
INTO VARID, VARNAME, VARAGE, VARSEX, VARDEPTNO;
EXIT WHEN MYCUR%NOTFOUND;
SELECT USERID INTO VAR_ID FROM T_USERINFO WHERE USERID = VARID;
IF VAR_ID IS NULL THEN
INSERT INTO T_USERINFO
VALUES
(VARID, VARNAME, VARAGE, VARSEX, VARDEPTNO);
ELSE
UPDATE T_USERINFO
SET USERNAME = VARNAME,
AGE = VARAGE,
SEX = VARSEX,
DEPTNO = VARDEPTNO
WHERE USERID = VAR_ID;
END IF;
END LOOP;
CLOSE MYCUR;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE("exception occurs!");
END;
END F_USERTOT_USER;
/
var_id t_userinfo.userid%type;
varname f_userinfo.username%type;
varage f_userinfo.age%type;
varsex f_userinfo.sex%type;
vardeptno f_userinfo.deptno%type;
cou number;
CURSOR mycur is
select userid, username, age, sex, deptno
from f_Userinfo
where userId is not null;
begin
open mycur;
loop
fetch mycur
into varid, varname, varage, varsex, vardeptno;
exit when mycur%notfound;
select count(userid) into cou from t_userinfo where userid = varid;
if cou > 0 then
insert into t_userinfo
values
(varid, varname, varage, varsex, vardeptno);
else
update t_userinfo
set userName = varname,
age = varage,
sex = varsex,
deptno = vardeptno
where userid = var_id;
end if;
end loop;
close mycur;
exception
when others then
dbms_output.put_line("exception occurs!");
end;