SQL连接符
·把列与列，列与字符连接在一起。
·用“||” 表示。
·可以用来“合成”列。
VAST@orcl> select last_name||job_id as "Employees" from employees;
Employees
-----------------------------------
OConnellSH_CLERK
GrantSH_CLERK
WhalenAD_ASST
HartsteinMK_MAN
FayMK_REP
SQL使用字符串
·字符串可以是select列表中的一个字符，数字，日期。
·日期和字符只能在单引号中出现。
·每当返回一行时，字符串被输出一次。
VAST@orcl> select last_name||' is '||job_id as "Employees" from employees;
Employees
---------------------------------------
OConnell is SH_CLERK
Grant is SH_CLERK
Whalen is AD_ASST
Hartstein is MK_MAN
Fay is MK_REP
根据连接符和字符串，我们可以做一些脚本的生成，简化工作。
例1：生成创建表
VAST@orcl> select 'create table t'||level||' as select * from t1;' from dual connect by level between 2 and 5;
'CREATETABLET'||LEVEL||'ASSELECT*FROMT1;'
---------------------------------------------------------------------------
create table t1 as select * from t1;
create table t2 as select * from t1;
create table t3 as select * from t1;
create table t4 as select * from t1;
create table t5 as select * from t1;
例2：在当前用户下创建hr所包含的所有表
VAST@orcl> select 'create table '|| table_name||' as select * from hr.'||table_name||';'from dba_tables where owner='HR';
'CREATETABLE'||TABLE_NAME||'ASSELECT*FROMHR.'||TABLE_NAME||';'
-----------------------------------------------------------------------------------------------
create table REGIONS as select * from hr.REGIONS;
create table COUNTRIES as select * from hr.COUNTRIES;
create table EMPLOYEES as select * from hr.EMPLOYEES;
create table LOCATIONS as select * from hr.LOCATIONS;
create table JOBS as select * from hr.JOBS;
create table DEPARTMENTS as select * from hr.DEPARTMENTS;
create table JOB_HISTORY as select * from hr.JOB_HISTORY;
例3：生成删除当前用户所有表
VAST@orcl> select 'drop table '||tname||' purge;' from tab;
'DROPTABLE'||TNAME||'PURGE;'
------------------------------------------------
drop table T1 purge;
drop table T2 purge;
drop table T3 purge;
drop table T4 purge;
drop table T5 purge;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30820196/viewspace-2126456/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
