SQL连接符与使用字符串


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博客 ” ,链接:http://blog.itpub.net/30820196/viewspace-2126456/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/30820196/viewspace-2126456/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值