MySQL and Oracle Difference Comparison 2 Basic Syntax

MySQL and Oracle Difference Comparison 2 Basic Syntax

Basic Syntax

## Number                                                                                        MySQL Variables are declared in different ways li_index NUMBER := 0 DECLARE li_index INTEGER DEFAULT 0 1. mysql uses DECLARE to define local variables.

The syntax for defining variables is: DECLARE var_name[,...] type [DEFAULT value] to be given Variables provide a default value and need to contain a DEFAULT clause. The value can be specified as an expression and does not need to be a constant. If there is no DEFAULT clause, the initial value is NULL.

2 Variables are assigned in different ways lv_inputstr := iv_inputstr SET lv_inputstr = iv_inputstr 1. Oracle variable assignment uses:=

mysql uses the set keyword when using assignment. When assigning a value to a variable Use "=".

3 The jump out (exit) statement is different EXIT; LEAVE procedure name; 1. oracle: If the exit statement is in the loop, exit the current loop. If the exit statement is no longer in the loop, exit the current loop. Procedure or method.

Mysql: If the leave statement is followed by the stored procedure name, exit the current stored procedure. If the leave statement is followed by the lable name, exit the current lable.

while condition loop

exit;

end loop; label_name:while condition do

leave label_name;

end while label_name;

4 Definition of cursor TYPE g_grp_cur IS REF CURSOR;

DECLARE cursor_name CURSOR FOR SELECT_statement; Oracle can define the cursor first and then assign a value to the cursor.

Needed when mysql defines a cursor Assign a value to the cursor. Mysql definition of cursor comes from Mysql 5.1 Reference Manual 20.2.11.1. Declare the cursor.

5 Define the array TYPE unpacklist_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; You can use a temporary table instead of the oracle array,
You can also loop and split characters to replace the oracle array. Currently, you can use a temporary table to replace the oracle array.

For details, see 2.4 in <<2009002-OTMPPS-Difficult Questions-0001.doc>> Mysql array processing part

6 Different comment methods "-- message" or "/**….*/"
or "/*&*/" "-- message" or " /*&*/"

or "#" mysql comments come from MySQL 5.1 Reference Manual 9.5. Comment syntax is recommended to be the same as Oracle, single line use --, multi-line /* */

7 The built-in date and time function formats are different Oracle time format: yyyy-MM-dd hh:mi:ss Mysql time format: %Y-%m-%d %H:%i:%s 1. MYSQL date fields are divided into DATE and TIME Two types.

ORACLE date field only has DATE, which contains year, month, day, hour, minute and second information.

2. In mysql, take the current system time as the now() function, accurate to seconds.

Get the system time of the current database in oracle as SYSDATE, accurate to seconds.

8 Date addition and subtraction Add N days to the current time: sysdate+N

Subtract N days from the current time: sysdate -N Date addition: date_add(now(), INTERVAL 180 DAY)

Date subtraction: date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second)

9 Different string concatenation characters result := v_int1||v_int2; set result = concat(v_int1,v_int2); 1. Oracle uses || to connect strings, and you can also use the concat function. But Oracle The concat function can only connect two strings.

Mysql uses the concat method to connect strings. MySQL's concat function can connect one or more strings, such as

mysql> select concat( '10'); The result is: 10.

mysql> select concat('11','22','33','aa'); The result is: 112233aa

2. "||" is the AND operation in Mysql

10 Define different cursors CURSOR l_bk_cur IS

SELECT B.BK_HDR_INT_KEY, B.BK_NUM

FROM ES_SR_DTL_VRB A, ES_BK_HDR B

WHERE A.BK_HDR_INT_KEY = B.BK_HDR_INT_KEY

AND b.BK_STATUS != ES_BK_PKG.g_status_can

AND A.SR_HDR_INT_KEY = ii_sr_hdr_int_key; DECLARE l_bk_cur CURSOR

FOR SELECT B.BK_HDR_INT_KEY, B.BK_NUM

FROM ES_SR_DTL_VRB A, ES_BK_HDR B

WHERE A.BK_HDR_INT_KEY = B.BK_HDR_INT_KEY
##AND b.BK_STATUS != ES_BK_PKG.g_status_can
##AND A.SR_HDR_INT_KEY = ii_sr_hdr_int_key;

For details, see 2.2 Mysql Cursor Processing Section in <<2009002-OTMPPS-Difficult Questions-0001.doc>>

11 Transaction Rollback ROLLBACK; ROLLBACK; The same method is used in oracle and mysql

12 GOTO statement GOTO check_date; GOTO check_date; The usage method is the same in oracle and mysql

The above is the content of the basic syntax of MySQL and Oracle Difference Comparison 2. For more related content, please pay attention to PHP Chinese Net (www.php.cn)!