我刚接触ORACLE存储过程 SQL学的还可以的,希望大家帮帮忙
我只是想见见看存储过程,可是老是编译错误create or replace procedure pro_rs_baobiao(aaa out varchar2)
as
begin
delcare
cursor mycur1 IS
select * from hr_department;
myrecord1 hr_department$ROWTYPE;
BEGIN
OPEN mycur1;
FETCH mycur1 INTO myrecord1;
WHILE mycur1%FOUND LOOP
select id into aaa from hr_department;
FETCH mycur1 INTO myrecord1;
END LOOP;
CLOSE mycur1;
END;commit;
end;
我只是想见见看存储过程,可是老是编译错误create or replace procedure pro_rs_baobiao(aaa out varchar2)
as
begin
delcare
cursor mycur1 IS
select * from hr_department;
myrecord1 hr_department$ROWTYPE;
BEGIN
OPEN mycur1;
FETCH mycur1 INTO myrecord1;
WHILE mycur1%FOUND LOOP
select id into aaa from hr_department;
FETCH mycur1 INTO myrecord1;
END LOOP;
CLOSE mycur1;
END;commit;
end;
你换成下面这句再试试:
DBMS_OUTPUT.PUT_LINE(myrecord1.id);
再报错的话,把出错代码弄到baidu里面去查查
我是在学 select id into aaa from hr_department; 这句话是没用的 也没有错误
我这样写 就对了create or replace Procedure pro_rs_baobiao(i out varchar2) is
dep_id number;
leave_id number;
zhiwei_id number;
n_people number;
sex_man number;
sex_we number;
xl_cz number;
xl_gz number;
xl_dz number;
xl_bk number;
xl_ss number;
xl_bs number;
hj_yuyao number;
hj_feiyy number; cursor mycur1 is select id,N_DLEVEL from hr_department order by N_FNODE asc;
cursor mycur2 is select id from hr_help where c_type='zwgl' order by id asc;
begin
OPEN mycur1;
FETCH mycur1 INTO dep_id,leave_id;
WHILE mycur1%FOUND LOOP
if dep_id>0 then
begin
OPEN mycur2;
FETCH mycur2 INTO zhiwei_id;
WHILE mycur2%FOUND LOOP
n_people:=0;
sex_man:=0;
sex_we:=0;
xl_cz:=0;
xl_gz:=0;
xl_dz:=0;
xl_bk:=0;
xl_ss:=0;
xl_bs:=0;
hj_yuyao:=0;
hj_feiyy:=0;
select count(id) into n_people from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE);
select count(id) into sex_man from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and C_SEX='153';
select count(id) into sex_we from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and C_SEX='154';
select count(id) into xl_cz from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_degree='122';
select count(id) into xl_gz from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_degree='123';
select count(id) into xl_dz from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_degree='124';
select count(id) into xl_bk from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_degree='125';
select count(id) into xl_ss from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_degree='126';
select count(id) into xl_bs from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_degree='127';
select count(id) into hj_yuyao from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_birthplace='1';
select count(id) into hj_feiyy from hr_staff where n_job=zhiwei_id and n_department in (select id from hr_department start with id=dep_id connect by prior id = N_FNODE) and c_birthplace='2';
insert into hr_rs_baobiao(N_DEPART,N_LEAVE,C_TYPE,C_ZHI,N_PEOPLE,SEX_MAN,SEX_WE,XL_CZ,XL_GZ,XL_DZ,XL_BK,XL_SS,XL_BS,HJ_YUYAO,HJ_FEIYY) values (dep_id,leave_id,'ְλ',zhiwei_id,n_people,sex_man,sex_we,xl_cz,xl_gz,xl_dz,xl_bk,xl_ss,xl_bs,hj_yuyao,hj_feiyy);
FETCH mycur2 INTO zhiwei_id;
END LOOP;
CLOSE mycur2;
end;
end if;
FETCH mycur1 INTO dep_id,leave_id;
END LOOP;
CLOSE mycur1;
end;
为什么 怎么会错呢