использование динамического sql для создания столбца для оператора выбора

Я пишу хранимую процедуру для результатов с разбивкой на страницы, и этот результат можно упорядочить по определенным значениям. У меня был случай переключения в операторе select, но поскольку он пытался выполнить orderby на rownum, он был очень медленным.

Теперь я пытаюсь использовать dyanmic sql для создания запроса за пределами select, но я не знаю, возможно ли то, что я делаю.

Вот мой SQL в Oracle SQL Developer:

create or replace PROCEDURE Sp_tsa_trainees_pagination (
schemeid IN INT,
searchval IN VARCHAR2,
pagesize IN INT DEFAULT 20,
currentpage IN INT DEFAULT 1,
--orderby IN VARCHAR2,
cursor_  OUT SYS_REFCURSOR)

AS
-- LOCAL VARIABLES
totalcount INT;
numberofpages INT;
startposition NUMBER;
endposition NUMBER;
orderby VARCHAR2(100) := 'surname asc' ;
dynamic_query VARCHAR(255) := 'row_number() over (order by t.SURNAME DESC, t.FORENAMES DESC) AS rnum';
BEGIN

-- Get total number of trainees in scheme
select COUNT(t.ORG_REGISTRATION_ID) 
into totalcount FROM v_trainee t 
where t.ORG_REGISTRATION_ID = schemeid 
AND t.status = 'A' and LOWER(t.trainee_name) like '%' || LOWER(searchval) || '%';

  -- calculate number of pages in the pagination by dividing total number of records by how many to display for each page
  numberofpages := totalcount / pagesize;

  -- get start position by multiplying number of records to display for each page by current page
  startposition := pagesize *( currentpage-1);

  -- add calculated start position by number of records to display to get end position
  endposition := startposition + pagesize;

  CASE orderby 
    WHEN 'surname desc' THEN dynamic_query := 'row_number() over (order by t.SURNAME DESC, t.FORENAMES DESC) AS rnum';
    WHEN 'surname asc' THEN dynamic_query := 'row_number() over (order by t.SURNAME ASC, t.FORENAMES ASC) AS rnum';
END CASE;


    OPEN cursor_ FOR
    Select * from 
(
SELECT 
-- order by based on selection

dynamic_query rnum,

t.ORG_REGISTRATION_ID SearchId,
    t.FORENAMES Forenames,
    t.FORENAME Forename,
    t.SURNAME Surname,
    t.person_id PersonId,
    t.trainee_name TraineeName,
    t.STATUS Status,
    t.IPD_ANNUAL_REVIEW_DATE AnnualReviewDate,
    t.ANNUAL_REVIEW_STATUS AnnualReviewStatus,
    t.payment_received PaymentRecieved,
    t.TRAINEE_ID TraineeId,
    t.IPD_SIGNUP_DATE IpdSignupDate,
    t.START_DATE StartDate,
    t.END_DATE EndDate,
    t.LENGTH_ON_SCHEME LengthOnScheme,
    t.EMPLOYEE_NUMBER EmploymentNumber,
    t.SELECTED_LEVEL SelectedLevel,
    t.SELECTED_LEVEL_DESCRIPTION SelectedLevelDescription,
    t.ELIGIBLE_LEVEL EligibleLevel,
    t.ELIGIBLE_LEVEL_DESCRIPTION EligibleLevelDescription,
    sce.FORENAMES SceForenames,
    sce.FORENAME SceForename,
    sce.SURNAME SceSurname,
    sce.mentor_name SceName,
    sce.EMPLOYEE_NUMBER SceEmployeeNumber,
    de.FORENAMES DeForenames,
    de.FORENAME DeForename,
    de.SURNAME DeSurname,
    de.mentor_name DeName,
    de.EMPLOYEE_NUMBER DeEmployeeNumber,
    t.COMPLETED_ATTRIBUTE_LEVELS CompletedAttributeLevels,
    t.ATTRIBUTE_LEVEL_COUNT AttributeLevelCount,

    -- get percentage
    CASE t.ATTRIBUTE_LEVEL_COUNT
    WHEN 0 THEN 0
    ELSE
    COMPLETED_ATTRIBUTE_LEVELS / t.ATTRIBUTE_LEVEL_COUNT * 100
    END percentage,

    DECODE(F_ISTRAINEEGROUPMEMBER(t.ORG_REGISTRATION_ID, 'S', t.person_id),'Y','N','Y') WithoutTsaGroup,
    orr.status SchemeStatus,
    (select count(*) from TRAINING_GROUP_TRAINEE tgt where tgt.trainee_id = t.TRAINEE_ID) NUMBER_OF_GROUPS,
    TotalCount
  FROM v_trainee t
  INNER JOIN org_registration orr ON t.ORG_REGISTRATION_ID = orr.id
  LEFT OUTER JOIN v_mentor sce    ON t.sce_id = sce.MENTOR_ID
  LEFT OUTER JOIN v_mentor de     ON t.de_id = de.MENTOR_ID
  where t.ORG_REGISTRATION_ID = schemeid  AND t.status = 'A' 
  and LOWER(t.trainee_name) like '%' || LOWER(searchval) || '%'
  )
  where rnum >= startposition and rnum <= endposition;

END;

Я хочу использовать эту переменную с назначенным sql:

dynamic_query rnum,

Но когда я выполняю хранимую процедуру, я получаю эту ошибку:

ORA-01722: недопустимый номер ORA-06512: в "db.SP_TSA_TRAINEES_PAGINATION", строка 46 ORA-06512: в строке 13

Итак, в основном мой вопрос: могу ли я назначить SQL для VARCHAR2, а затем динамически использовать его в операторе выбора.


sql oracle dynamic-sql plsql
person nick gowdy    schedule 12.02.2018    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Для этого вам может понадобиться динамический SQL. Например:

create or replace procedure testDyn(n in number, C OUT SYS_REFCURSOR) is
    vDynamicPart varchar2(1000);
    vSQl         varchar2(1000);
begin
    --
    if  (n = 1) then
        vDynamicPart := 'count(1)';
    else
        vDynamicPart := 'count(null)';
    end if;
    --
    vSQl := 'select ' || vDynamicPart || ' from dual';
    open C for vSQl;
end;

Если вы назовете это

declare
    n1 number;
    n2 number;
    C1 SYS_REFCURSOR;
    C2 SYS_REFCURSOR;
begin
    testDyn(1, C1);
    testDyn(2, C2);

    fetch C1 into n1;
    fetch C2 into n2;

    dbms_output.put_line('n1: ' || n1);
    dbms_output.put_line('n2: ' || n2);
end;

ты получаешь:

n1: 1
n2: 0
person Aleksej    schedule 12.02.2018