MySql and Oracle Data Type Mappings

the default settings used by SQL Developer to convert data types from MySQL to Oracle. SQL Developer enables you to 

change the default setting for certain data types by specifying an alternative type. For information about changing the default data type mappings, see the SQL Developer online help.

Table 2-4 Default Data Type Mappings Used by Oracle SQL Developer

MySQL Data Type	Oracle Data Type
BIGINT	NUMBER(19, 0)
BIT	RAW
BLOB	BLOB, RAW
CHAR	CHAR
DATE	DATE
DATETIME	DATE
DECIMAL	FLOAT (24)
DOUBLE	FLOAT (24)
DOUBLE PRECISION	FLOAT (24)
ENUM	VARCHAR2
FLOAT	FLOAT
INT	NUMBER(10, 0)
INTEGER	NUMBER(10, 0)
LONGBLOB	BLOB, RAW
LONGTEXT	CLOB, RAW
MEDIUMBLOB	BLOB, RAW
MEDIUMINT	NUMBER(7, 0)
MEDIUMTEXT	CLOB, RAW
NUMERIC	NUMBER
REAL	FLOAT (24)
SET	VARCHAR2
SMALLINT	NUMBER(5, 0)
TEXT	VARCHAR2, CLOB
TIME	DATE
TIMESTAMP	DATE
TINYBLOB	RAW
TINYINT	NUMBER(3, 0)
TINYTEXT	VARCHAR2
VARCHAR	VARCHAR2, CLOB
YEAR	NUMBER

Exemple:

MySql:

#Create Report_megax_daily table and index;
CREATE TABLE REPORT_MEGAX_DAILY
(
SETTLED_TIME DATETIME,
ACCOUNT_ID INT(11),
AD_SPACE_ID INT(11),
CITY_ID INT(11),
IMPRESSIONS DECIMAL(10,0),
CLICKS DECIMAL(10,0),
COSTS DECIMAL(10,2)
);
CREATE INDEX REPORT_MEGAX_DAILY_IDX1 ON REPORT_MEGAX_DAILY
(ACCOUNT_ID);
CREATE INDEX REPORT_MEGAX_DAILY_IDX2 ON REPORT_MEGAX_DAILY
(AD_SPACE_ID);
#Create REPORT_MEGAX_HOURLY_NEW table and index;

CREATE TABLE REPORT_MEGAX_HOURLY_NEW
(
SETTLED_TIME DATETIME,
DSP_ACCOUNT_ID INT(11),
AD_SPACE_ID INT(11),
MEDIA_BUY_ID INT(11),
BID_REQUEST_COUNT DECIMAL(10,0),
BID_RESPONSE_COUNT DECIMAL(10,0),
BID_WIN_COUNT DECIMAL(10,0),
CLICKS DECIMAL(10,0),
BID_LOSE_COUNT DECIMAL(10,0),
BID_TIMEOUT_COUNT DECIMAL(10,0),
BID_PRICE DECIMAL(10,2),
BID_ALL_PRICE DECIMAL(10,2)
);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX1 ON REPORT_MEGAX_HOURLY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX2 ON REPORT_MEGAX_HOURLY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);

#Create REPORT_MEGAX_DAILY_NEW table and index;
CREATE TABLE REPORT_MEGAX_DAILY_NEW
(
SETTLED_TIME DATETIME,
DSP_ACCOUNT_ID INT(11),
AD_SPACE_ID INT(11),
MEDIA_BUY_ID INT(11),
BID_REQUEST_COUNT DECIMAL(10,0),
BID_RESPONSE_COUNT DECIMAL(10,0),
BID_WIN_COUNT DECIMAL(10,0),
CLICKS DECIMAL(10,0),
BID_LOSE_COUNT DECIMAL(10,0),
BID_TIMEOUT_COUNT DECIMAL(10,0),
BID_PRICE DECIMAL(10,2),
BID_ALL_PRICE DECIMAL(10,2)
);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX1 ON REPORT_MEGAX_DAILY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX2 ON REPORT_MEGAX_DAILY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);

 

Oracle:

--Create Report_megax_daily table and index;
CREATE TABLE REPORT_MEGAX_DAILY
(
SETTLED_TIME DATE,
ACCOUNT_ID NUMBER,
AD_SPACE_ID NUMBER,
CITY_ID NUMBER,
IMPRESSIONS NUMBER,
CLICKS NUMBER,
COSTS NUMBER
);
CREATE INDEX REPORT_MEGAX_DAILY_IDX1 ON REPORT_MEGAX_DAILY
(ACCOUNT_ID);
CREATE INDEX REPORT_MEGAX_DAILY_IDX2 ON REPORT_MEGAX_DAILY
(AD_SPACE_ID);
--Create REPORT_MEGAX_HOURLY_NEW table and index;

CREATE TABLE REPORT_MEGAX_HOURLY_NEW
(
SETTLED_TIME DATE,
DSP_ACCOUNT_ID NUMBER,
AD_SPACE_ID NUMBER,
MEDIA_BUY_ID NUMBER,
BID_REQUEST_COUNT NUMBER,
BID_RESPONSE_COUNT NUMBER,
BID_WIN_COUNT NUMBER,
CLICKS NUMBER,
BID_LOSE_COUNT NUMBER,
BID_TIMEOUT_COUNT NUMBER,
BID_PRICE NUMBER,
BID_ALL_PRICE NUMBER
);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX1 ON REPORT_MEGAX_HOURLY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_HOURLY_NEW_IDX2 ON REPORT_MEGAX_HOURLY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);

--Create REPORT_MEGAX_DAILY_NEW table and index;
CREATE TABLE REPORT_MEGAX_DAILY_NEW
(
SETTLED_TIME DATE,
DSP_ACCOUNT_ID NUMBER,
AD_SPACE_ID NUMBER,
MEDIA_BUY_ID NUMBER,
BID_REQUEST_COUNT NUMBER,
BID_RESPONSE_COUNT NUMBER,
BID_WIN_COUNT NUMBER,
CLICKS NUMBER,
BID_LOSE_COUNT NUMBER,
BID_TIMEOUT_COUNT NUMBER,
BID_PRICE NUMBER,
BID_ALL_PRICE NUMBER
);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX1 ON REPORT_MEGAX_DAILY_NEW
(SETTLED_TIME);
CREATE INDEX RPT_MEGAX_DAILY_NEW_IDX2 ON REPORT_MEGAX_DAILY_NEW
(DSP_ACCOUNT_ID, AD_SPACE_ID, MEDIA_BUY_ID);

转载于:https://www.cnblogs.com/jobs2/p/3424972.html