如何将下面的语句在储存过程中执行通过========?

Question

select * into ....
from (
        select row_number() over(order by gxsj) no,
               WJFTESTTABLE.* from WJFTESTTABLE ) t1,
        (select no from (select row_number() over(order by gxsj) no,
        WJFTESTTABLE.* from WJFTESTTABLE ) t where id='0604') t2
where abs(t1.no-t2.no)<2;存储过程中不支持select * from tb_;
要返回记录集，请使用游标。

Answer 1

select * from (
        select row_number() over(order by gxsj) no,
               WJFTESTTABLE.* from WJFTESTTABLE ) t1,
        (select no from (select row_number() over(order by gxsj) no,
        WJFTESTTABLE.* from WJFTESTTABLE ) t where id='0604') t2
into out_t1,out_no,out_t2
where abs(t1.no-t2.no)<2;

Answer 2

create or replace package myPackage
as
    type myRefCursor is ref cursor;
end;CREATE OR REPLACE PROCEDURE MBP.PROC_WJFTEST(cur out myPackage.myRefCursor)
is
BEGIN
      open cur for '
                    select * 
                      from 
                        (
                          select row_number() over(order by gxsj) no
                                 ,WJFTESTTABLE.* 
                          from WJFTESTTABLE 
                        ) t1,
                        (
                          select no 
                          from 
                            (
                               select row_number() over(order by gxsj) no,
                                      WJFTESTTABLE.* 
                               from WJFTESTTABLE 
                            ) t 
                          where id=''0604''
                        ) t2
                      where abs(t1.no-t2.no)<2
                    ';
end;

Answer 3

.net中调用：     OracleConnection conn = new OracleConnection("Data Source=test;User Id=system;Password=111111");
        try
        {
            
            conn.Open();
            OracleCommand cmd = new OracleCommand("MBP.PROC_WJFTEST", conn);            cmd.CommandType = CommandType.StoredProcedure;
                       OracleParameter parm = new OracleParameter("cur",OracleType.Cursor);
            parm.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(parm);
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            this.GridView1.DataSource = ds.Tables[0].DefaultView;
            this.GridView1.DataBind();
        }
        catch
        {
            throw;
        }
        finally
        {
            conn.Close();
        }

Answer 4

各位支持用游标的＂游侠＂，为什么不把code写出来呢,看看在执行时是否能顺利的通过呢？

Answer 5

  FOR REC IN (SELECT T1.*
                FROM (SELECT ROW_NUMBER() OVER(ORDER BY GXSJ) NO,
                             WJFTESTTABLE.*
                        FROM WJFTESTTABLE) T1,
                     (SELECT NO
                        FROM (SELECT ROW_NUMBER() OVER(ORDER BY GXSJ) NO,
                                     WJFTESTTABLE.*
                                FROM WJFTESTTABLE) T
                       WHERE ID = '0604') T2
               WHERE ABS(T1.NO - T2.NO) < 2) LOOP
               
       --可直接引查询结果 REC.*    数据 
               
  
  END LOOP;