sql server2014切换到oracle12c

来源：互联网发布：java多线程之间的通信编辑：程序博客网时间：2024/06/11 19:24

安装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服务exitlsnrctl stopsqlplus / as sysdbaalter session set container=PDBORCL; shu immediatestartupexitlsnrctl startsqlplus / as sysdbaalter 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服务exitlsnrctl stopsqlplus / as sysdbaalter session set container=PDBORCL; shu immediatestartupexitlsnrctl startsqlplus / as sysdbaalter 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_STOCKLOGminvalue 1maxvalue 999999999999999999start with 1increment by 1CACHE  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>

0 0