在oracle sql developer 4.1.3.20中声明并使用一个字符串变量(declare and use a string variable in oracle sql develope

在oracle sql developer 4.1.3.20中声明并使用一个字符串变量(declare and use a string variable in oracle sql developer 4.1.3.20)

所以在工作中，我们最近一直在使用Oracle SQL Developer 4.1.3.20来查询我们的数据，所以我编写了这段代码（实际上它的时间长了8个或更多的表连接）：

select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from xyz.DRUG_KIT k left join xyz.DR_SHIP s on s.ID = k.SHIPMENT_ID left join xyz.USR_PAT u on u.PAT_ID = k.PAT_ID

当我必须切换到另一个数据库中的另一个表时，我一直在复制粘贴到上面哪里看到'xyz'，但肯定有更好的办法吗？ 我使用SQL Server Management Studio 2014，但是一旦我们开始使用Oracle数据库，我们就不得不切换到这一点。 我如何在Oracle SQL Developer中声明一个varchar（？）变量，这样我就可以使用这个变量代替XYZ，并且在查询的顶部，我可以将该变量设置为等于一个地方所需的变量，如果这样做合理的话。 当我尝试将大约10个以上的表连接在一起以检索数据时，我在一行中复制粘贴错误时变得非常麻烦。

So at work we've been using Oracle SQL Developer 4.1.3.20 lately to query our data so I wrote this snippet of code (its actually much longer by 8 or so more table joins):

select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from xyz.DRUG_KIT k left join xyz.DR_SHIP s on s.ID = k.SHIPMENT_ID left join xyz.USR_PAT u on u.PAT_ID = k.PAT_ID

When I have to switch to another table in a different database, I have been copy pasting everywhere above where you see 'xyz' but surely there has to ba better way? I use to use SQL Server Management Studio 2014 but we had to switch to this once we began using oracle databases. How would I declare a varchar(?) variable in Oracle SQL Developer so I can just use that one variable in place of XYZ and at the top of the query I just can set that variable equal whatever necessary in one place if that makes sense. It becomes cumbersome when I copy paste something wrong on one line when I"m trying to join about 10+ tables together to retrieve data.

最满意答案

XYZ是表的所有者，而不是一个不同的数据库。 如果以用户XYZ连接，则不需要将XYZ前缀添加到表名称，只有在您尝试访问由不同“SCHEMA”拥有的对象时才需要此名称。

您可以通过发出alter session命令来更改会话的当前“解析”模式：

ALTER SESSION SET CURRENT_SCHEMA=PDQ;

然后从用户PDQ已授予您选择对象的对象中选择：

select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from DRUG_KIT k left join DR_SHIP s on s.ID = k.SHIPMENT_ID left join USR_PAT u on u.PAT_ID = k.PAT_ID;

然后，您可以将模式更改回XYZ并执行相同的select语句，并根据XYZ拥有的表内容获取不同的结果：

ALTER SESSION SET CURRENT_SCHEMA=XYZ; select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from DRUG_KIT k left join DR_SHIP s on s.ID = k.SHIPMENT_ID left join USR_PAT u on u.PAT_ID = k.PAT_ID;

要使由PDQ拥有的DR_SHIP表可以从XYZ模式访问，可以创建PDQ.DR_SIP的私有同义词来代替XYZ所指的表：

CREATE OR REPLACE SYNONYM DR_SHIP FOR PDQ.DR_SHIP;

另一方面，如果您希望可以从数据库中的所有SCHEMA访问DRUG_KIT表的PDQ版本，则可以创建公共同义词：

CREATE OR REPLACE PUBLIC SYNONYM DRUG_KIT FOR PDQ.DRUG_KIT;

如果以上都不符合您的需求，则可以使用SQL * Plus样式替换变量：

ACCEPT user; select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from &user..DRUG_KIT k left join &user..DR_SHIP s on s.ID = k.SHIPMENT_ID left join &user..USR_PAT u on u.PAT_ID = k.PAT_ID;

但是你需要明白，这些不一定在SQL * Plus，SQL Developer，SQLcl以及其他几个SQL开发环境之外工作。

XYZ is the owner of the table, not a different database. If you are connected as user XYZ, then you don't need to prefix XYZ to the table name, it's only required if you are attempting to access objects owned by a different "SCHEMA".

You can change the current "parsing" schema of your session by issuing an alter session command:

ALTER SESSION SET CURRENT_SCHEMA=PDQ;

and then select from objects the user PDQ has granted you select privs on:

select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from DRUG_KIT k left join DR_SHIP s on s.ID = k.SHIPMENT_ID left join USR_PAT u on u.PAT_ID = k.PAT_ID;

You can then alter your schema back to XYZ and perform the same select statement and get different results based on the contents of the tables as owned by XYZ:

ALTER SESSION SET CURRENT_SCHEMA=XYZ; select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from DRUG_KIT k left join DR_SHIP s on s.ID = k.SHIPMENT_ID left join USR_PAT u on u.PAT_ID = k.PAT_ID;

To make the DR_SHIP table owned by PDQ accessable from the XYZ schema, a private synonym to PDQ.DR_SIP can be created in place of the table onwed by XYZ:

CREATE OR REPLACE SYNONYM DR_SHIP FOR PDQ.DR_SHIP;

On the other hand if you want the PDQ version of the DRUG_KIT table accessible from all SCHEMAs in the DB, you can create a public synonym:

CREATE OR REPLACE PUBLIC SYNONYM DRUG_KIT FOR PDQ.DRUG_KIT;

If none of the above meet your needs, you can use SQL*Plus style substitution variables:

ACCEPT user; select s.NAME as "Shipment ID" ,k.STATUS_ID as "Status" ,u.SCR_NO as "SPID" from &user..DRUG_KIT k left join &user..DR_SHIP s on s.ID = k.SHIPMENT_ID left join &user..USR_PAT u on u.PAT_ID = k.PAT_ID;

But you need to understand that these won't necessarily work outside of SQL*Plus, SQL Developer, SQLcl, and possibly a couple of other SQL development environments.