Shulou(Shulou.com)06/01 Report--

This article mainly introduces Mysql migration to Oracle analysis, the content of the article is carefully selected and edited by the author, Mysql migration to Oracle analysis has a certain pertinence, the reference significance for everyone is still relatively large, the following with the author to understand the subject content bar.

1. Selection of data types.

Value Type:

Mysql has two types of numbers: whole numbers and real numbers.

Storage integer can be selected: TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT , corresponding to 8, 16, 24, 32, 64 bit storage space respectively. They can be stored from-2 to the N-1 power to 2 to the N-1 power-1, where N is the number of bits in storage space.

Integer types have the optional UNSIGNED attribute, which indicates that negative values are not allowed, which roughly doubles the upper bound for positive numbers. For example, TINYINT UNSIGNED can store the range 0 - 255, while TINYINT can store the range-128-127.

Real numbers are numbers with fractional parts. The DECIMAL type is used to store exact decimals. Because of the extra computation and overhead, try to use it only for precise calculations of decimals. When the amount of data is relatively large, you can consider using bigint instead of DECIMAL.

Oracle, by contrast, has number,int,float,binary_float,binary_double,numeric types.

The number type occupies 0~22 bytes of storage space in oracle. It is a variable length data type. It adopts oracle internal algorithm. It is a soft data type. Therefore, it has good data accuracy, versatility and portability.

The following types are mapped from the number type.

NUMERIC(p,s): maps exactly to NUMBER(p,s). If p is not specified, it defaults to 38.

DECIMAL(p,s) or DEC(p,s): maps exactly to NUMBER(p,s). If p is specified, the default is 38

INTEGER or INT: Fully mapped to type NUMBER(38).

SMALLINT: Fully mapped to type NUMBER(38)

BINARY_FLOAT and BINARY_DOUBLE can store a larger range of data than number due to different storage structures, but their accuracy is not as good as number. If storing financial data, it is recommended to use number.

For scientific calculations, BINARY_FLOAT and BINARY_DOUBLE are recommended, because floating-point data uses hardware calculations, and its computational efficiency is very high.

For all int types in Mysql, it is recommended that they correspond to Oracle number types uniformly. (number(p,s) is also mapped exactly to number type, so there is no need to set precision)

Character Type:

Mysql uses VARCHAR and CHAR.

VARCHAR is a variable length type

CHAR is a fixed length type

Fixed-length types like CHAR remove all trailing spaces, making certain behaviors difficult to understand when storing and comparing data. Therefore, VARCHAR is a good choice.

Char in Oracle is a fixed-length type and will be filled with spaces.

Varchar2 stores data in a variable-length manner, which saves space. In terms of storage efficiency, it is comparable to char.

Char types also have a problem with trailing spaces.

For Oracle types, varchar2 should be chosen whenever possible due to work habits and storage requirements.

2. Role of delimiter in Mysql

This keyword tells the interpreter whether the command has ended and whether mysql can execute subsequent scripts.

DELIMITER ;DROP PROCEDURE IF EXISTS p_contract; --This statement can execute DELIMITER $$immediately --does not end with a semicolon, and subsequent statements wait to be executed when $$is encountered. CREATE PROCEDURE p_contract() BEGIN。。。。。。。。。。。 END $$ --Executes the middle statement. DELIMITER ;

3.DECLARE CONTINUE HANDLER FOR NOT FOUND

If no data is returned, the program continues and sets the variable IS_FOUND to 0, which occurs when select XX into XXX from tablename.

4. UNIX timestamp and date conversion

Mysql date and time function, recommend you can take a look at this article: www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html

Oracle gets UTC time:

select to_char(sys_extract_utc(systimestamp),'yyyy-mm-dd hh34:mi:ss') from dual; --UTC time

Conversion between UNIX timestamp and date in mysql

UNIX timestamp to date conversion function: FROM_UNIXTIME()

select FROM_UNIXTIME(1410318106);

Date conversion to UNIX timestamp function: UNIX_TIMESTAMP()

select UNIX_TIMESTAMP('2014-09-10 11:01:46');

where DATE_FORMAT(FROM_UNIXTIME('1410318106','%Y-%m-%d %h:%m:%s'),'%Y-%m-%d %h:%m:%s')

Oracle does not have such conversion functions, you need to write them yourself (the following writing also comes from the network)

--Oracle Date type convert to Unix timestamp create or replace function bill_query.oracle_to_unix(in_date DATE) return number isBEGIN return( (in_date -TO_DATE('19700101','yymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600);END oracle_to_unix;/--Unix timestamp converted to Oracle time create or replace function bill_query.unix_to_oracle(in_number NUMBER) return date isBEGIN return(TO_DATE('19700101','yyyymmdd') + in_number/86400 +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24);END unix_to_oracle;/

5. Indexes and constraints in Mysql correspond to Oracle

primary key ---> primary key

index ---> index

unique key -> unique index

key ---> index

7. AUTO_INCREMENT attribute

AUTO_INCREMENT attribute exists in Mysql field, and sequence is needed instead in Oracle.

When this field is used, an explicit call is required, inserting sequence_name.nextval

8. Mysql When creating a table, you can query whether the table exists and delete it by statement (drop database if exists table_name)

Oracle can be implemented through custom functions. (When there is a DDL statement in the function, it cannot be called by select, only by assignment.) Consider that it would be easier to implement and invoke it in a stored procedure.)

create or replace function hytpdtnmdb.fun_obj_ifexists(v_obj_name in varchar2) return number is num_tab number; num_seq number;begin select count(1) into num_tab from all_objects where OWNER||'. '||OBJECT_NAME = upper(v_obj_name) and OBJECT_TYPE='TABLE'; if num_tab = 1 then execute immediate 'drop table '||v_obj_name; return(num_tab); end if; select count(1) into num_seq from all_objects where OWNER||'. '||OBJECT_NAME = upper(v_obj_name) and OBJECT_TYPE='SEQUENCE'; IF num_seq = 1 THEN EXECUTE IMMEDIATE 'drop SEQUENCE '||v_obj_name; RETURN num_seq; END IF; return -1;end fun_obj_ifexists;

9. About Oracle Stored Procedure Execution Permission

In the case of user A, it is necessary to update, delete, drop table, or create table B user objects.

Even if you grant DBA authority to the user, it can be executed normally in anonymous stored procedures or in the command window, but there will be insufficient permissions in stored procedures.

By searching online, by adding AUTHID CURRENT_USER. Creating a stored procedure as CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID CURRENT_USER as solves this problem.

Article link: blog.csdn.net/gavinloo/article/details/6869234

But occasionally there are still insufficient permissions, which can be solved by grant explicit authorization.

10. Difference of partial functions

Mysql concat can concatenate multiple strings. Only two characters can be concatenated in Oracle. If you need more than one string concatenation, still use '||'

NUMTOYMINTERVAL ( n , 'char_expr' )

char_expr: Date description, can be YEAR and MONTH;

NUMTODSINTERVAL( n , 'char_expr' )

char_expr: time description, which can be day, hour, minute, second;

Interval can only be followed by numbers

Interval can be used under normal circumstances, but only when the value to be increased or decreased is a variable.

numtodsinterval and NUMTOYMINTERVAL alternatives.

After reading the above about Mysql migration to Oracle analysis, many readers must have some understanding, if you need to obtain more industry knowledge information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.