如何在Oracle中重复查询 [英] How to repeat a query in Oracle

问题描述

我需要将此查询重复100次.我想我应该使用循环功能，但没有找到解决方法.

I need to repeat this query 100 times. I think I should use loop function but didn't find any solutions on how to do it.

select case when DBMS_RANDOM.value >= 0 and DBMS_RANDOM.value<=0.053 then 1
            when DBMS_RANDOM.value > 0.053 and DBMS_RANDOM.value <= 0.097 then 2
            when DBMS_RANDOM.value > 0.097 and DBMS_RANDOM.value <= 0.142 then 3
end random_groups
from temp_trt;

这是temp_trt的样本数据 在此处输入图片描述

here is the sample data of temp_trt enter image description here

推荐答案

每次调用DBMS_RANDOM.value()都会返回一个不同的值.因此，不能保证，任何通话都将落在您的任何范围内.实际上，从统计学上讲，这是不可能的.因此，在大多数情况下，由于未定义ELSE分支，因此您将返回NULL.

Each call of DBMS_RANDOM.value() returns a different value. Consequently there is no guarantee that any call will fall between any of your bounds. In fact it's statistically unlikely. Consequently most of the time you'll get a NULL returned, because you have defined no ELSE branch.

这里是生成一百个随机值的替代解决方案.

Here is an alternate solution which generates one hundred random values.

with dr as (
    select DBMS_RANDOM.value val
    from dual
    connect by level <= 100 
)
select dr.val
      , case when dr.val >= 0 and dr.val<=0.053 then 1
            when dr.val > 0.053 and dr.val <= 0.097 then 2
            when dr.val > 0.097 and dr.val <= 0.142 then 3
            else 4 
end random_groups
from dr
;

鉴于您的代码定义分支范围的方式，大多数random_groups将是4.

Given the way your code defines the bounds of the branches, most of the random_groups will be 4.

您发布的(玩具?)代码尚不清楚TEMP_TRT的作用，因此我决定忽略它.如果您不满意，请编辑您的问题以添加更多详细信息

It's not clear from your posted (toy?) code what the role of TEMP_TRT is, so I decided to ignore it. Please edit your question to add more detail if this makes you unhappy

这篇关于如何在Oracle中重复查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！