Oracle SQL语句动态模式变量 [英] Oracle SQL statement dynamic schema variable

问题描述

我有几百行代码来创建表，但是架构名称在所有DDL语句中都像SCHEMA_NAME1.TABLE_NAME一样被硬编码。我如何将其作为变量并在所有地方使用，以便我们可以轻松地在一个地方更改SCHEMA_NAME要求的多个位置。请提供您的想法。

I have some hundred lines of code to create tables but schema name is hardcoded like SCHEMA_NAME1.TABLE_NAME in all DDL statements. How can I have this as a variable and use in all the places so that we can easily change in a single place for multiple locations of SCHEMA_NAME requirement.Please give your thoughts.

create table SCHEMA_NAME1.TABLE_NAME1(....);
create table SCHEMA_NAME1.TABLE_NAME2(....);
create table SCHEMA_NAME1.TABLE_NAME3(....);

我想要这样的东西

var SCHEMA_NAME_VALUE ;
create table SCHEMA_NAME_VALUE.TABLE_NAME1(....);
create table SCHEMA_NAME_VALUE.TABLE_NAME2(....);
create table SCHEMA_NAME_VALUE.TABLE_NAME3(....);

推荐答案

使用SQL * Plus或SQLcl运行对象时创建脚本，请使用替换（& ）变量代替硬编码的模式名称，并使用前导 ACCEPT 命令和结尾的 UNDEFINE 命令。当变量没有尾随空格时，请以句点（。）终止变量名称。

When using SQL*Plus or SQLcl to run your object creation script, use substitution (&) variables in place of the hard coded schema names, with a leading ACCEPT command and a trailing UNDEFINE command. When the variable has no trailing white space, terminate the variable name with a period (.).

ACCEPT SCHEMA_NAME_VALUE
create table &SCHEMA_NAME_VALUE..TABLE_NAME1(....);
create table &SCHEMA_NAME_VALUE..TABLE_NAME2(....);
create table &SCHEMA_NAME_VALUE..TABLE_NAME3(....);
UNDEFINE SCHEMA_NAME_VALUE

这篇关于Oracle SQL语句动态模式变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！