oracle SQL -用变量替换常量

hgncfbus 于 2023-05-28 发布在 Oracle

关注(0)|答案(2)|浏览(176)

如何将常量'constantHere'替换为变量？我使用普通SQL，我知道变量的概念在普通SQL中不存在，但也许有一个解决方案？
重要的是，我在那里留下了'%'符号，因为我永远不知道常数如何结束。
我有多个联合和相同的'constantHere'作为条件。每次运行脚本时，我都需要更改它。我想只有1个地方的代码来改变它，而不是向上和向下滚动，并改变所有的人。

select tuse.user_id             user_id,
        tuse.user_name          user_name,
        tuse.login_name         login,
        tuse.is_active          user_active,
        userint.role_code       role_code,
        userint.is_active       role_active,
        null     special_priv,
        null      special_priv_active
        
from object.t_user                                                        tuse
    left join object.user_special_privilege                                 usespec
        on tuse.user_id = usespec.user_id
    left join object.special_privilege                                      specpriv
        on usespec.special_privilege_id = specpriv.special_privilege_id
    left join object.user_role_int                                          userint
        on tuse.user_id = userint.user_id

where upper(tuse.user_name) like upper('constantHere%')

union

select tuse.user_id             user_id,
        tuse.user_name          user_name,
        tuse.login_name         login,
        tuse.is_active          user_active,
        null      role_code,
        null       role_active,
        specpriv.description    special_priv,
        specpriv.is_active      special_priv_active
        
from object.t_user                                                        tuse
    left join object.user_special_privilege                                 usespec
        on tuse.user_id = usespec.user_id
    left join object.special_privilege                                      specpriv
        on usespec.special_privilege_id = specpriv.special_privilege_id
    left join object.user_role_int                                          userint
        on tuse.user_id = userint.user_id

where upper(tuse.user_name) like upper('constantHere%')

order by 2, 5, 7

oracle

来源：https://stackoverflow.com/questions/76307417/sql-replace-constant-with-variable

2条答案

按热度按时间

jvlzgdj91#

引用：“* 我希望代码中只有一个地方可以更改它，而不是上下滚动并更改所有代码 *。
如果这是你需要的，那么你可以把它放在CTE：

WITH var_def AS (Select 'something' "constantHere" From Dual)   -- define it here
select tuse.user_id             user_id,
        tuse.user_name          user_name,
        tuse.login_name         login,
        tuse.is_active          user_active,
        userint.role_code       role_code,
        userint.is_active       role_active,
        null     special_priv,
        null      special_priv_active
        
from object.t_user
    Inner Join var_def ON(1 = 1)       --  here you attach var_def.constantHere to every row                                                        tuse
    left join object.user_special_privilege                                 usespec
        on tuse.user_id = usespec.user_id
    left join object.special_privilege                                      specpriv
        on usespec.special_privilege_id = specpriv.special_privilege_id
    left join object.user_role_int                                          userint
        on tuse.user_id = userint.user_id

where upper(tuse.user_name) like upper(var_def.constantHere||'%')   -- here you use it

union

select tuse.user_id             user_id,
        tuse.user_name          user_name,
        tuse.login_name         login,
        tuse.is_active          user_active,
        null      role_code,
        null       role_active,
        specpriv.description    special_priv,
        specpriv.is_active      special_priv_active
        
from object.t_user   
Inner Join var_def ON(1 = 1)       --  here you attach var_def.constantHere to every row                                              tuse
    left join object.user_special_privilege                                 usespec
        on tuse.user_id = usespec.user_id
    left join object.special_privilege                                      specpriv
        on usespec.special_privilege_id = specpriv.special_privilege_id
    left join object.user_role_int                                          userint
        on tuse.user_id = userint.user_id

where upper(tuse.user_name) like upper(var_def.constantHere||'%')   -- here you use it

order by 2, 5, 7

现在你只有一个地方可以改变它。。。相应地用大写字母命名你的常数。我把constant这个名字写在这里是因为你在问题中使用了它。

赞(0）回复(0）举报 2023-05-28

ifsvaxew2#

使用替换变量。
use define constantHere='SomeValue'
替换为&& constant

赞(0）回复(0）举报 2023-05-28