create or replace procedure GroupPartRefond(groupid in integer,grouppeocount in integer, resout out integer) is
grouppassaccount integer;
mincou integer;
v_pt_id integer;
v_cou integer;
v_sql_str clob;
cursor a_cursor is
select pt.id,count(pi.id) cou from plane_timetable pt right join seats_info si on si.planetimetableid =pt.id
right join passenger_info pi on pi.seatsid =si.id
where pi.gid= groupid and pi.isdelete='0' and si.isdelete='0' and pt.isdelete='0' and pi.name is null group by pt.id;
b_cursor RefCursor.t_Refcursor;
BEGIN
open a_cursor;
loop
fetch a_cursor into v_pt_id,v_cou;
exit when a_cursor%notfound;
v_sql_str :=v_sql_str+ 'union select id, gid, name, gender, age, idnumber, passport, seatsid,phonenum,seatnumber,ptname,rownum from (
select pi.id, pi.gid, pi.name, pi.gender, pi.age, pi.idnumber, pi.passport, pi.seatsid,pi.phonenum,si.seatnumber,pt.ptname
from passenger_info pi left join seats_info si on pi.seatsid =si.id left join plane_timetable pt on si.planetimetableid =pt.id
where pi.gid='+groupid+ ' and pi.isdelete='||0||' and si.isdelete='||0||' and pt.isdelete='||0||' and (pi.name='' or pi.name is null) and pt.id='+v_pt_id+' order by pi.id desc) where rownum <= '+mincou+' '; end loop;
close a_cursor;
v_sql_str:= substr(v_sql_str,1,5);
OPEN b_cursor FOR v_sql_str;
begin
select min(cou) into mincou from (
select pt.id,count(pi.id) cou from plane_timetable pt right join seats_info si on si.planetimetableid =pt.id
right join passenger_info pi on pi.seatsid =si.id
where pi.gid= groupid and pi.isdelete='0' and si.isdelete='0' and pt.isdelete='0' and pi.name is null group by pt.id);
select count(id) into grouppassaccount from group_passenger where gid =groupid; if(grouppassaccount<=(grouppeocount-mincou)) then --update group_info set gpeocounts = v_gpeocounts,where gid = v_gid,
-- resout:=3;
open b_cursor;
loop
fetch b_cursor into v_pt_id,v_cou;
exit when b_cursor%notfound;
resout:=6;
end loop;
close b_cursor;
else
resout:=4;
end if;
end GroupPartRefond;
PROCEDURE LJTEST.GROUPPARTREFOND 编译错误错误:PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
( begin case
declare end exception exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
行:47
文本:end GroupPartRefond;
grouppassaccount integer;
mincou integer;
v_pt_id integer;
v_cou integer;
v_sql_str clob;
cursor a_cursor is
select pt.id,count(pi.id) cou from plane_timetable pt right join seats_info si on si.planetimetableid =pt.id
right join passenger_info pi on pi.seatsid =si.id
where pi.gid= groupid and pi.isdelete='0' and si.isdelete='0' and pt.isdelete='0' and pi.name is null group by pt.id;
b_cursor RefCursor.t_Refcursor;
BEGIN
open a_cursor;
loop
fetch a_cursor into v_pt_id,v_cou;
exit when a_cursor%notfound;
v_sql_str :=v_sql_str+ 'union select id, gid, name, gender, age, idnumber, passport, seatsid,phonenum,seatnumber,ptname,rownum from (
select pi.id, pi.gid, pi.name, pi.gender, pi.age, pi.idnumber, pi.passport, pi.seatsid,pi.phonenum,si.seatnumber,pt.ptname
from passenger_info pi left join seats_info si on pi.seatsid =si.id left join plane_timetable pt on si.planetimetableid =pt.id
where pi.gid='+groupid+ ' and pi.isdelete='||0||' and si.isdelete='||0||' and pt.isdelete='||0||' and (pi.name='' or pi.name is null) and pt.id='+v_pt_id+' order by pi.id desc) where rownum <= '+mincou+' '; end loop;
close a_cursor;
v_sql_str:= substr(v_sql_str,1,5);
OPEN b_cursor FOR v_sql_str;
begin
select min(cou) into mincou from (
select pt.id,count(pi.id) cou from plane_timetable pt right join seats_info si on si.planetimetableid =pt.id
right join passenger_info pi on pi.seatsid =si.id
where pi.gid= groupid and pi.isdelete='0' and si.isdelete='0' and pt.isdelete='0' and pi.name is null group by pt.id);
select count(id) into grouppassaccount from group_passenger where gid =groupid; if(grouppassaccount<=(grouppeocount-mincou)) then --update group_info set gpeocounts = v_gpeocounts,where gid = v_gid,
-- resout:=3;
open b_cursor;
loop
fetch b_cursor into v_pt_id,v_cou;
exit when b_cursor%notfound;
resout:=6;
end loop;
close b_cursor;
else
resout:=4;
end if;
end GroupPartRefond;
PROCEDURE LJTEST.GROUPPARTREFOND 编译错误错误:PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
( begin case
declare end exception exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
行:47
文本:end GroupPartRefond;
解决方案 »
- oracle数据库创建表主外键语法
- 急求!!不能在 本地计算机 启动 OracleDBConsoleorcl。错误提示2
- 请教sqlserver移植到oracle的问题
- 写sql时能否获得明年的昨天?(oracle)
- oracle中伪列的问题??
- 怎样取得结果集的记录条数?
- 新手提问: 初始密码和如何建库
- 请问,为什么我在作业里面的状态都是已失败呢?还有FMT和tx_是什么文件?
- 那里能下载JDEVELOPER ,为什么我载官方下载得没有安装文件亚
- The process termination failed for JRE. %2.?
- oracle startup启动 __streams_pool_size 问题
- oracle 多表关联查询时 添加查询条件,查询速度变得极慢
begin
select min(cou) into mincou from (
多了一个bin,
select min(cou) into mincou from ....或少了一个end
end;
end GroupPartRefond;