Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of the basic grammar of the difference between MySQL and Oracle. The article is very detailed and has certain reference value. Interested friends must read it!

Basic grammar

The numbered category ORACLEMYSQL comment 1 variable is declared in a different way li_index NUMBER: = 0DECLARE li_index INTEGER DEFAULT 01. Mysql uses DECLARE to define local variables.

Define the variable syntax as: DECLARE var_name [,...] Type [DEFAULT value] to provide a default value for a variable, you need to include a DEFAULT clause. 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_inputstrSET lv_inputstr = iv_inputstr1. The oracle variable assignment uses: =

Mysql uses the set keyword for assignment. When assigning a value to a variable, use the "=" .3 jump (exit) statement different from EXIT;LEAVE procedure name;1. Oracle: exit the current loop if the exit statement is in the loop. If the exit statement is no longer in the loop, exit the current procedure or method.

Mysql: if the leave statement is followed by a stored procedure name, exits the current stored procedure. If the leave statement is followed by a lable name. Exits the current lable.

While conditional loop

Exit

End loop;label_name:while conditional do

Leave label_name

End while label_name;4 defines cursor TYPE g_grp_cur IS REF CURSOR

DECLARE cursor_name CURSOR FOR SELECT_statement;oracle can define cursors and then assign values to cursors.

Mysql needs to assign values to cursors when defining cursors. The Mysql definition cursor is from the Mysql 5.1 reference manual 20.2.11.1. Declaration cursor .5 defines an array TYPE unpacklist_type IS TABLE OF VARCHAR2 (2000) INDEX BY BINARY_INTEGER; can use temporary tables instead of oracle arrays, or loop split characters instead of oracle arrays. Currently, temporary tables can be used instead of oracle arrays.

For details, see "--message" or "/ * * …. * /" or "/ * …. * /"-- message "or" / *.... * / "or" # "mysql annotations from MySQL 5.1 reference manual 9.5. Comment syntax, the same as oracle, single-line, multi-line / * * / 7 with date-time function format different Oracle time format: yyyy-MM-dd hh:mi:ssMysql time format:% Y-%m-%d% H:%i:%s1. The MYSQL date field is divided into DATE and TIME.

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

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

The system time of the current database in oracle is SYSDATE, accurate to seconds. 8-day plus or minus current time plus N days: sysdate+N

Current time minus N days: sysdate-N date add: date_add (now (), INTERVAL 180 DAY)

Date subtraction: date_sub ('1998-01-01 00 result 0000, interval' 1 1day_second 1day_second) 9 string concatenation different result: = v_int1 | | concat int1 match set result = concat)

1. Oracle uses | | connection string, or you can use the concat function. However, the concat function of Oracle can only concatenate two strings.

Mysql uses the concat method to concatenate strings. The concat function of MySQL can concatenate one or more strings, such as

Mysql > select concat ('10'); the result is: 10.

Mysql > select concat ('11', 22', 22, 33, 33): 112233aa

2. "| |" in Mysql, it is different from the cursor defined by operation 10 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 the 2.2 Mysql cursor processing section 11 transaction rollback ROLLBACK;ROLLBACK;oracle and mysql using the same method 12GOTO statements GOTO check_date;GOTO check_date;oracle and mysql using the same method the above is "an example analysis of the basic syntax for comparing MySQL and Oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.