SQL (Oracle,SQL Server,PostgreSQL，MySQL)

1. If exist
2. Import Excel
3. Oracle SQL Server
============================================================

1. If exist:
------------
1.1 Oracle:

1. Oracle check a table if exist：
   SQL> select count(*) from all_tables where table_name='ET_ORDER_DETAIL';
   SQL> select count(*) from dba_tables where table_name='ET_ORDER_DETAIL';
   SQL> select count(*) from tab where tname='ET_ORDER_DETAIL'
   SQL> select object_name from user_objects where lower(object_type) = 'table'
   
   //To delete a tablespace If it exists:
   declare
   num number;
   w_name varchar2(20);
   url varchar2(100);
   begin
   w_name := 'TESTFUKU';
   url := 'e:\oracle\product\10.2.0\oradata\orcl\TESTFUKU.DBF';
   SELECT count(1) into num FROM SYS.SM$TS_AVAIL A WHERE A.TABLESPACE_NAME=w_name;
   if num> 0 then
   -- dbms_output.put_line('DROP TABLESPACE '|| w_name ||' INCLUDING CONTENTS');
   execute immediate 'DROP TABLESPACE '|| w_name ||' INCLUDING CONTENTS';
   end if;
   end;
   
   
   //To delete a table If it exists:
   create or replace function getTable
   return boolean is
   r_b boolean;
   v dba_tables.table_name%Type;
   begin
   select table_name into v from SYS.dba_tables where table_name='OFFICE';
   if SQL%FOUND then
   r_b:=true;
   else
   r_b:=false;
   end if;
   return r_b;
   Exception
   when no_data_found then
   r_b:=false;
   return r_b;
   end;

1.2 SQL Server:

if exists

(select * from dbo.sysobjects where id = object_id(N'[db_name].[dbo].[table_name]') )

drop table [db_name].[dbo].[table_name]

1.3 PostgreSQL:

select count(*) from pg_class where relname = ‘tablename’;
select count(*) from information_schema.tables where table_schema=’public’ and table_type=’BASE TABLE’ and table_name=’tablename’;

1.4 MySQL:

Create if not exist
INSERT INTO clients (client_id, client_name, client_type) SELECT 10345, 'IBM', 'advertising' FROM dual WHERE not exists (select * from clients where clients.client_id = 10345);

2. Import Excel:
---------------
2.1 ORACLE:
1.SQL*Loader(sqlldr):
load data
infile ‘c:\text.txt’
append into table test –insert(when table is empty); append, replace
fields terminated by X’09 –for csv fields terminated by ‘,’
(field1,
field2,
field3,
…
fieldn)

sql*plus> sqlldr userid=system/manager control=’c:\control.ctl’

2.PLSQL Developer:

select * from test for update;

then F8
Open the lock, paste, commit
3.import to another sql:由sql导入oracle时将表名改为全部大写（部分大写都不行）
Referencd:http://www-rohan.sdsu.edu/doc/oracle/server803/A54652_01/ch04.htm#793
http://www.80diy.com/home/20050724/19/4164861.html

2.2 PostgreSQL:

postgres>psql realisation

realisation=# COPY realisation_mission (label) from ‘/home/xxd/Documents/syntec_info/import/mission’;

2.3 MySQL:

mysql> LOAD DATA INFILE ‘/Desktop/data.txt’ INTO TABLE test.address_naf700 (code, libelle);
mysql> LOAD DATA INFILE ‘/Desktop/data.txt’ INTO TABLE test.address_country FIELDS TERMINATED BY ” LINES TERMINATED BY ‘\n\n’ (countryname);

3. Oracle SQL Server
---------------------

Oracle：SELECT ‘x’ FROM dual
SQL Server：SELECT ‘x’

Oracle：Select ‘Name’ || ‘Last Name’ From tableName
SQL Server：Select ‘Name’ + ‘Last Name’

Oracle：SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;
SQL Server：SELECT ROUND(15.79, 0) rounded , ROUND(15.79, 0,1) truncated
SELECT FLOOR(ROUND(15.79, 0)), FLOOR(ROUND(15.79, 0,1) )

Oracle：SELECT to_char(123.45 ,99999999999999) from tab
　　 SELECT to_char(EXPIRY_DATE,'DDMONYYYY') from tab
SQL Server：SELECT STR(123.45, 14)
SELECT STR(round(123.455 , 2),12,2)
SELECT CAST(REPLACE((CONVERT(varchar(12) , EXPIRYDATE, 106 )),' ' , '') as varchar(9))

Oracle：SELECT LENGTH('SQLMAG') "Length in characters" FROM DUAL;
SQL Server：SELECT LEN('SQLMAG') "Length in characters"

Oracle：SYSDATE
SQL Server：GETDATE()

Oracle：Select add_months(sysdate,12) from dual
SQL Server：Select dateadd(mm,12,getdate())

Oracle：SELECT sysdate -add_months(sysdate,12) FROM dual
SQL Server：SELECT datediff(dd, GetDate(),dateadd(mm,12,getdate()))