If the select or statement does not set @i_last=null return any row, select top 1 @i_first = _idx_ @i_first will not from @emps order by _idx_ asc change its value, keeping the previously stored value. So, first initialize this variable as null. LAST select @i_last = max(_idx_) from I_last := method @emps emp.LAST; or set @i_last=null select top 1 @i_last = _idx_ from @emps order by _idx_ desc NEXT select @i_last = min(_idx_) from I_next := method @emps where _idx_ > @i emp.NEXT(j); PRIOR select @i_last = max(_idx_) from I_prior := method @emps where _idx_ < @i emp.PRIOR(j); DELETE DELETE FROM @emps WHERE _idx_ = emps.delete(i); method @i emps.delete; DELETE FROM @emps emps.trim TRIM is declare @_idx_ int emps.trim; method equivalent to select top(@n) @_idx_= _idx_ emps.trim(n); emps.trim(1). from @emps order by _idx_ desc delete @emps where _idx_ >= @_idx_ EXISTS exists(select * from @emps t.exists(i) method _idx_ = @i) COUNT select @t_count = FROM i = t.COUNT; method @emps Bulk The INSERT INTO @emps empno) select empno row_number() collect into SELECT row_number() over(order bulk collect function depends by empno) as _idx_, empno into emps @emps on table from emp from emp declaration. For or declaration with _idx_ INSERT INTO @emps (empno) identity column do not use SELECT empno from emp row_number(). EXTEND t.extend; SELECT @t_next_value = method FROM t.extend(n); @emps t.extend(n, i);
or set @i_last=null select top 1 @i_first = _idx_ from @emps order by _idx_ asc If the select statement does not return any row, @i_first will not change its value, keeping the previously stored value. So, first initialize this variable as null. LAST method I_last := emp.LAST; select @i_last = max(_idx_) from @emps or set @i_last=null select top 1 @i_last = _idx_ from @emps order by _idx_ desc NEXT method I_next := emp.NEXT(j); select @i_last = min(_idx_) from @emps where _idx_ > @i PRIOR method I_prior := emp.PRIOR(j); select @i_last = max(_idx_) from @emps where _idx_ < @i DELETE method emps.delete(i); emps.delete; DELETE FROM @emps WHERE _idx_ = @i DELETE FROM @emps TRIM method emps.trim; emps.trim(n); declare @_idx_ int select top(@n) @_idx_= _idx_ from @emps order by _idx_ desc delete @emps where _idx_ >= @_idx_ emps.trim is equivalent to emps.trim(1). EXISTS method t.exists(i) exists(select * from @emps _idx_ = @i) COUNT method i = t.COUNT; select @t_count = FROM @emps Bulk collect into select empno bulk collect into emps from emp INSERT INTO @emps empno) SELECT row_number() over(order by empno) as _idx_, empno from emp or INSERT INTO @emps (empno) SELECT empno from emp The row_number() function depends on @emps table declaration. For declaration with identity _idx_ column do not use row_number(). EXTEND method t.extend; t.extend(n); t.extend(n, i); SELECT @t_next_value = FROM @emps
SSMAOracle.docx