SQL連線符與使用字串

dbLjy2015發表於2016-10-15
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章