英文:
Optional limit for oracle queries
问题
是不是可以根据参数使 Oracle 查询中的 limit 子句变成可选的？
我有一些执行时间很长的重型查询，所以在测试期间，我想只获取10行（几乎瞬间完成），但我不想为每种情况使用单独的查询。
英文:
Is it possible to make the limit clause in an Oracle query optional based on a parameter?
I've got some heavy queries that thake lots of time to execute so I'd like to fetch like only 10 rows during testing (which is nearly instant), but I don't want to use separate queries for each case.
答案1
得分: 1
这取决于您的上下文，但您可以在FETCH FIRST和X ROWS ONLY中定义参数，并被要求输入该值，或者您可以将其存储在表中，根据需要进行更改。
WITH
tbl AS
( SELECT LEVEL "ID", TRUNC(SYSDATE) - (LEVEL - 1) "DT" FROM Dual CONNECT BY LEVEL <= 10 )
SELECT *
FROM tbl
FETCH FIRST &X ROWS ONLY -- for X = 3
--
-- ID DT
-- ---------- ---------
-- 1 05-JUN-23
-- 2 04-JUN-23
-- 3 03-JUN-23
--
SELECT *
FROM tbl
FETCH FIRST (SELECT 5 FROM Dual) ROWS ONLY
-- ID DT
-- ---------- ---------
-- 1 05-JUN-23
-- 2 04-JUN-23
-- 3 03-JUN-23
-- 4 02-JUN-23
-- 5 01-JUN-23
英文:
It depends on your context but you coould either define the parameter within FETCH FIRST &X ROWS ONLY and will be asked to enter the value or you could have it stored in a table and change it as you need.
WITH
tbl AS
( Select LEVEL "ID", TRUNC(SYSDATE) - (LEVEL - 1) "DT" From Dual Connect By LEVEL <= 10 )
Select *
From tbl
Fetch FIRST &X ROWS ONLY -- for X = 3
--
-- ID DT
-- ---------- ---------
-- 1 05-JUN-23
-- 2 04-JUN-23
-- 3 03-JUN-23
--
Select *
From tbl
Fetch FIRST (Select 5 From Dual) ROWS ONLY
-- ID DT
-- ---------- ---------
-- 1 05-JUN-23
-- 2 04-JUN-23
-- 3 03-JUN-23
-- 4 02-JUN-23
-- 5 01-JUN-23
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论