时间戳

首页 > 解决方案 > PL/SQL - Iterating over cursor and assigning value

sql - PL/SQL - Iterating over cursor and assigning value

问题描述

This may be a basic question, but I'm relatively new to PL/SQL and I'm stuck.

Let's say I have a cursor, for example:

cursor pr_cur(pr_id varchar2) 
is
  select priority
  from some_table
  where id = pr_id;

The priority column can have only 3 possible values: 'Low', 'High', 'Very high'

I want to iterate through the cursor and assign the highest priority found to a variable: max_pr.

for pr_rec in pr_cur(some_value)
loop
  max_pr := pr_rec.priority;
  exit when pr_rec.priority = 'Very high';
end loop;

I hope this is not a duplicate question. How should I go about doing this?

标签： sqloracleplsql

解决方案

您不需要Cursor 和不需要的循环if else。您可以使用单个 sql 查询来获得最大优先级，使用适当ORDER BY的 withrow_number或FETCH FIRST

在Oracle 11g中 

SELECT
    priority
INTO max_pr
FROM
    (
        SELECT
            t.*,
            ROW_NUMBER() OVER(
                ORDER BY
                    CASE priority
                        WHEN 'Very high'   THEN 1
                        WHEN 'High'        THEN 2
                        WHEN 'Low'         THEN 3
                        ELSE 4
                    END
            ) AS rn
        FROM
            some_table t
    )
WHERE
    rn = 1;

Oracle 12c及更高版本

SELECT
    priority
INTO max_pr
FROM
    some_table t
ORDER BY
    CASE priority
        WHEN 'Very high'   THEN 1
        WHEN 'High'        THEN 2
        WHEN 'Low'         THEN 3
        ELSE 4
    END
FETCH FIRST 1 ROWS ONLY

推荐阅读