sql server2014切换到oracle12c

安装oracle12c

略

创建用户

--------创建kdbase------------------------------------------
sqlplus / as sysdba;
create pluggable database PDBORCL admin user user1 identified by user1 roles=(connect)  
file_name_convert=  
('C:\app\oracle\oradata\orcl\pdbseed',  
'C:\app\oracle\oradata\orcl\pdborcl');  
alter session set container=PDBORCL;  
alter pluggable database PDBORCL open;  

--1.删除系统原有表空间、数据库用户
alter session set container=PDBORCL;  
drop user user1 cascade;
drop tablespace TS_USER1 including contents and datafiles cascade constraints;

--2.创建表空间
create tablespace TS_USER1 datafile 'C:\app\oracle\oradata\orcl\user1.dbf' size 32M autoextend on next 100M maxsize 15000M extent management local;
--3.重启OracleServiceOrcl服务
exit
lsnrctl stop
sqlplus / as sysdba
alter session set container=PDBORCL; 
shu immediate
startup
exit
lsnrctl start

sqlplus / as sysdba
alter session set container=PDBORCL; 
--4.创建数据库用户
create user user1 identified by user1 default tablespace TS_USER1;
GRANT 
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
　　TO user1;
GRANT connect,resource TO user1;
alter user user1 quota unlimited on TS_USER1;
--解决PLSQL无法连接的问题
grant dba to user1;  

------------------创建kdia-------------------------------
--1.删除系统原有表空间、数据库用户
sqlplus / as sysdba;
alter session set container=PDBORCL;  
drop user user2 cascade;
drop tablespace TS_USER2 including contents and datafiles cascade constraints;

--2.创建表空间
create tablespace TS_USER2 datafile 'C:\app\oracle\oradata\orcl\user2.dbf' size 32M autoextend on next 100M maxsize 15000M extent management local;
--3.重启OracleServiceOrcl服务
exit
lsnrctl stop
sqlplus / as sysdba
alter session set container=PDBORCL; 
shu immediate
startup
exit
lsnrctl start

sqlplus / as sysdba
alter session set container=PDBORCL; 
--4.创建数据库用户
create user user2 identified by user2 default tablespace TS_USER2;
GRANT 
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
　　TO user2;
GRANT connect,resource TO user2;
alter user user2 quota unlimited on TS_USER2;
--解决PLSQL无法连接的问题
grant dba to user2;  

建序列器

use user1;
DROP SEQUENCE SEQ_ASSET_STOCKLOG;
create sequence SEQ_ASSET_STOCKLOG
minvalue 1
maxvalue 999999999999999999
start with 1
increment by 1
CACHE  20;

创建表

create table ASSET_STOCKLOG 
(
   RECORD_NO            INTEGER              default SEQ_ASSET_STOCKLOG.NEXTVAL not null,
   constraint PK_ASSET_STOCKLOG primary key (RECORD_NO)
);

comment on table ASSET_STOCKLOG is
'表注释';
comment on column ASSET_STOCKLOG.RECORD_NO is
'记录号（字段注释）';
...

转换存储过程和函数

注释	MSSQL	ORACLE
今天	CONVERT(VARCHAR(8), GETDATE(), 112)	TO_CHAR(SYSDATE,’YYYYMMDD’)
现在时分秒，去掉分号	replace(CONVERT(varchar,getdate(),24),’:’,”)	replace(TO_CHAR(SYSDATE,’hh24:mi:ss’),’:’,”)
字符串位置,字符串@CHAR在@STR中的位置	CHARINDEX(@CHAR,@STR)	INSTR(@STR,@CHAR)
存储过程执行动态SQL	EXEC(sqlstr)	EXECUTE IMMEDIATE sqlstr
打印	PRINT(XXX)	dbms_output.putline(XXX)
从左向右截取字符串3位	left(str,3)	substr(str,0,3)
从右向左截取字符串3位	right(str,3)	substr(str,-3)
存储过程赋值	select @val=123	select 123 into val from dual
存储过程赋值二	select @val=123	val:=123
变量定义和赋值	DECLARE @VAL1 VARCHAR(64),@VAL2 INT 换行SET @VAL2=1	DECLARE VAL1 VARCHAR(64);换行VAL2 INT :=1 ;
数字类型	BIGINT	NUMBER(20)
字符串连接	SELECT ‘A’+’B’	SELECT concat(‘A’,’B’) FROM DUAL 或者使用两条竖线
删除指定长度的字符并在指定的起始点插入另一组字符，获取’1,2,3’	SELECT STUFF(‘,1,2,3’, 1, 1, ”) A	SELECT substr (‘,1,2,3’, 1) A FROM dual;
数据类型	DATETIME	DATE
DATE字段存日期	cast(‘2015-06-24 16:48:00.987’ as DATETIME)	to_timestamp(‘2015-06-24 16:48:00.987’,’yyyy-mm-dd hh24:mi:ss:ff9’)
DATE字段存日期二	cast(‘2015-06-24 16:48:00’ as DATETIME)	TO_DATE(‘2015-06-24 16:48:00.987’,’yyyy-mm-dd hh24:mi:ss’)
DATE字段存时分秒	CONVERT(varchar(12),getdate(),108)	TO_CHAR(SYSDATE,’hh24:mi:ss’)
null替换	ISNULL(COL_NAME,”)	NVL(COL_NAME,”)
连接字符串	+	双竖线
分页查询	select top {pageSize} o.* from (select row_number() over(order by {orderCols}) as rownumber,* from ({sql}) a ) o where rownumber>{firstIndex};	select * from ({sql}) fetch offset {firstIndex} rows fetch next {pageSize} rows only;
查询前几条数据	SELECT top {num} * from ({sql}) 可以使用select top {num} percent * from tb order by score提高效率	selet * from ({sql}) fetch first {pageSize} rows only;
转换为字符串类型	CONVERT(VARCHAR(200),COL_NAME)	TO_CHAR(COL_NAME)
转换为数字类型	CONVERT(NUMBERIC(19,0),col_name)	cast(col_name as number(19,0))
查询后缀是_TASK所有表和字段信息	SELECT TABLE_NAME = d.name , TAB_COMMENTS = ISNULL(CONVERT(VARCHAR(1024),f.value), ”) , COLUMN_NAME = a.name , COL_COMMENTS = ISNULL(CONVERT(VARCHAR(1024),g.[value]), ”) FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = ‘U’ AND d.name != ‘dtproperties’ LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 WHERE 1 = 1 AND d.name LIKE ‘%/_TASK’ ESCAPE ‘/’	select col.TABLE_NAME as TABLE_NAME,utc.comments as TAB_COMMENTS,col.COLUMN_NAME as COLUMN_NAME,com.comments AS COL_COMMENTS from user_tab_columns col inner join user_tab_comments utc on utc.table_name=col.TABLE_NAME inner join user_col_comments com on col.TABLE_NAME=com.TABLE_NAME and col.COLUMN_NAME=com.COLUMN_NAME and col.TABLE_NAME LIKE ‘%/_TASK’ ESCAPE ‘/’
查询所有表的主键字段	SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE	select cu.table_name as TABLE_NAME, cu.column_name as COLUMN_NAME from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = ‘P’
表结构信息是关键字使用[]或”“	select a.[SYSDATE] from YDGH_ZWHY_INFO_TEMP a	select a.”SYSDATE” from YDGH_ZWHY_INFO_TEMP a
截取字符串	SELECT SUBSTRING(‘Hello_World’,0,7)	SELECT SUBSTR(‘Hello_World’,0,6) FROM DUAL
截取字符串二	SELECT SUBSTRING(‘Hello_World’,2,6)	SELECT SUBSTR(‘Hello_World’,2,6) FROM DUAL
判断是否为空	select CASE WHEN ” = ” THEN 0 ELSE 123 END AS SAFERATE	select CASE WHEN ” IS NULL THEN 0 ELSE 123 END AS SAFERATE from dual
判断是否存在	IF EXISTS (SELECT * FROM GATHTASK WHERE GATH_SN=1) BEGIN DELETE FROM GATHTASK WHERE GATH_SN=1; END	declare v_cnt number; begin select count() into v_cnt from dual where exists (SELECT FROM GATHTASK WHERE GATH_SN=1); if v_cnt != 0 then DELETE FROM GATHTASK WHERE GATH_SN=1; end if; end;
EXISTS	IF EXISTS (SELECT …) BEGIN DELETE…; END	declare v_cnt number; begin select count(*) into v_cnt from dual where exists (SELECT…); if v_cnt != 0 then DELETE…; end if; end;

利用Oracle动态游标实现动态SQL循环遍历

create or replace procedure P_TEST_SQL is  
TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标   
tablename varchar2(200) default 'ess_client';  
v_sql varchar2(1000);  
mobile varchar2(15);  
usrs ref_cursor_type;  
begin  
  --使用连接符拼接成一条完整SQL   
  v_sql := 'select usrmsisdn from '||tablename||' where rownum < 11';  
  --打开游标   
  open usrs for v_sql ;  
  loop  
      fetch usrs into mobile;   
      exit when usrs%notfound;  
      insert into tmp(usrmsisdn) values(mobile);  
  end loop;  
  close usrs;  
  commit;  
end P_TEST_SQL;  

Mybatis 定义参数查询表字段(SQLSERVER)

    <select id="queryTableColumnByTableName_Win_MSSQL" parameterType="java.util.HashMap" resultType="java.util.HashMap">
        DECLARE @table_name as varchar(max)
        set @table_name ='${TABLE_NAME}'
        SELECT cast(sys.columns.name as varchar()) as COLUMN_NAME,
        cast((select value FROM sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id
        and sys.extended_properties.minor_id = sys.columns.column_id) as varchar) AS COLUMN_COMMENT
        from sys.columns, sys.tables, sys.types where sys.columns.OBJECT_ID = sys.tables.object_id
        and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name
        ORDER by sys.columns.column_id
    </select>