The pit that the Oracle-MySQL storage process encounters ~ (summary)

Oracle's MySQL repository encounters a pit summary

Recently in the project of Oracle to MySQL, the pit is really much, especially the storage process, has not touched the type of knowledge, and recently almost finished the summary. Hope to help some people (including the later self) ~

1> Basic Language method

　Changes in the volume of The Voice:

oracle:v_cnt varchar2 (100): = ";

Mysql:declare v_cnt VARCHAR (+) DEFAULT ';

The stored process is stated as follows:

Oracle:procedure Regnewrecord (I_custno in VARCHAR2 (100))

Msyql:create procedure Regnewrecord(in i_custno Varcahr (100))

Value:

Oracle: v_cnt = ' Test ';

Mysql:set v_cnt = ' Test ';

Function numbers:

Oracle:nvl

Msyql:ifnull

Concatenation of strings:

ORACLE:STR1 | | str2

Mysql:concat (STRA,STR2)

　　

Conversion of the types of the numbers:

ORACLE:VARCHAR2-Msyql:varchar

　

2> Sub-page

　　Using RowNum in Oracle: It is important to note that Oracle's next target starts with 1, and the bottom of MySQL starts at 0.

　Select t.* from (select RowNum rn, a.seqid from test a 　 order by a.seqid ) T

where RN >= 5 and RN <= #表示取結果集中的第5到第10條記錄

　　Use the Limit keyword in MySQL

　　Select seqid from test limit 4,6; #表示從下標4開始, 6 records after enquiries

3>%rowtype %rowtype is the unique key in Oracle

　　Oracle: v_test [email Protected]%rowtype;

　　　　　　　V_test.name = ' Zhangsan ';

Parse: Test is a table name for an existing table, indicating that a variable containing all the fields in the test table is represented, and that the type of the change will be consistent with the type of the form itself and the table number. You only need to use v_test when using the latter. Field name. If the test table has a Name field, then you can use V_test.name to use it.

　　msyql : In MySQL, the%rowtype method is to create a time table from the front table, and define the amount of field response that needs to be used in the time table.

DECLARE t_name varchar (100); --Defining the amount of time in an hour table

　　drop table if exists test_temp;

CREATE temporary TABLE IF not EXISTS test_temp (
SELECT t.* from test limit 1
);

--　 The creation of this table will then automatically save a new time table.

select name into T_name from Test_temp; --the numbers in the time table are put into the time variable, and there are several times when you use the notation to separate, for example : select name, seqid into T_name,t_seqid from Test_temp;

　　After that, you can use the T_name variable to replace the V_test.name directly.

　　If there are other storage processes in the future that need to be used for the time table, you need to re-update the variables to the table.

Eg:update test_temp set name= t_name;

　　

If Oracle uses Tablename%rowtype in the participation of stored processes that are used in the storage process, the MSYQL solution can only refer to the main key of the table in which the schedule is active, and then the changes in the time table to receive the values in the time table.

Oracle: Procedure Autofillsyscol (v_test in Test%rowtype)

　　 MSYQL : (assuming that seqid is the only consciousness in the test table)

Procedure Autofillsyscol (in v_seqid varchar (100))

DECLARE t_name varchar (100);

DECLARE t_seqid varchar (100);

Select Seqid,name into T_seqid,t_name from test_temp where seqid= v_seqid; #注意, this is to take the example above, test_temp the time table has been created after the adjustment.

Here you can directly use T_seqid,t_name to change the volume ... If you have changes to T_seqid, t_name, you need to update the numbers in the Test_temp table

4> Return key

　　Oracle: The end of the storage process can be used directly with the return keyword

　　MySQL: You need to define a tag first and then use the Leave keyword

Use the sample:　　　　

　　　　Oracle:

Begin IF  t_name is null and then return;　　　　　　End If; --Other Operation end

　　　　MSYQL:

Endproc_tag:begin    If t_name is null then    LEAVE endproc_tag;    End If;end

　　

　　　　　　

5> Time Type Conversion

Oracle:to_char (sysdate, ' Yyyymmddhh24mmss ')

Msyql:date_format (now (), '%y%m%d%h%i%s ')

Calculation of 6> Date

Oracle Date addition or subtraction: getdate (' 2018-08-08 ') +365) getdate (' 2018-08-08 ') -365)

MSYQL date addition or subtraction: adddate (' 2018-08-08 ', 365) adddate (' 2018-08-08 ', -365) #後面的值為正數的時候表示相加, minus for negative numbers

7> Use table: Use tables in other repositories during the pre-storage process

Oracle: Can directly use the table name @ the library name, do not need to do what configuration, direct Dblink can be

MySQL: You need to modify the configuration file, change the Library engine: InnoDB--and federated, and then create a response table that requires a reference table to use

　　

8> storage process and storage process

Oracle: Writes the stored process name directly in the storage process, for example: Newregcust ()

MSYQL: You need to add a call key, for example: Calls Newregcust ()

9> Number (P,s)

Oracle: Represents a maximum number of p-digits, and the decimal bit is the p-s. For example: Number (20,6), which indicates a maximum of 20 bits, the decimal bit is 4 bits, and the integer bit is 16 bits.

Msyql:numeric (p,s) and Oracle

10> the definition of participation and participation

Oracle: Need to define the participation and participation, format: (i_opt in Varchar2,o_status out varchar2)

MySQL: The participation can be used without definition, and there will be results set back. Format: (in i_opt varchar (255)) #這裡要注意, to specify the length of varchar, otherwise it will be wrong ~

　　

The pit that the Oracle-MySQL storage process encounters ~ (summary)