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

Editor to share with you what are the language differences between MySQL and Oracle SQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Data type

The serial number ORACLEMYSQL annotation 1NUMBERint / DECIMALDECIMAL is NUMBER (10Magne2). Such a structure INT is NUMBER (10), which represents an integer.

MYSQL has many types of int, tinyint mediumint bigint, etc., different int widths are different 2Varchar2 (n) varchar (n)

Processing of 3DateDATATIME date field

MYSQL date fields are divided into DATE and TIME. Only DATE is available in the ORACLE date field, which contains the information of year, month, day, hour, minute and second. The system time of the current database is SYSDATE, accurate to seconds. Or use the string to convert the date function TO_DATE ('2001-08-01) year-month-day 24 hours: minute: second format YYYY-MM-DD HH24:MI:SS TO_DATE () there are many date formats, see ORACLE DOC. Convert date field to string function TO_CHAR ('2001-08-01 "grammar" YYYYMMI MmurDD HH24:MI:SS')

The mathematical formulas of the date field are very different. MYSQL finds 7 days from the current time using DATE_FIELD_NAME > SUBDATE (NOW (), INTERVAL 7 DAY) ORACLE to find 7 days from the current time using DATE_FIELD_NAME > SYSDATE-7

Several functions that insert the current time into the MYSQL are: the NOW () function returns the current date and time as `'YYYY-MM-DD HH:MM:SS', which can be directly stored in the DATETIME field. CURDATE () returns today's date in the format 'YYYY-MM-DD', which can be saved directly into the DATE field. CURTIME () returns the current time in the format 'HH:MM:SS', which can be saved directly in the TIME field. Example: insert into tablename (fieldname) values (now ())

The current time in oracle is the equivalent of INTEGER in sysdate4INTEGERint / INTEGERMysql to int5EXCEPTIONSQLEXCEPTION in 2.5 Mysql exception handling 6CONSTANT VARCHAR2 (1) there is no CONSTANT keyword migration from ORACLE to MYSQL in mysql, all CONSTANT constants can only be defined as variables 7TYPE g_grp_cur IS REF CURSOR; cursor: there are alternatives in mysql see 8TYPE unpacklist_type IS TABLE OF VARCHAR2 (2000) INDEX BY BINARY_INTEGER; array in mysql: temporary table processing in mysql

Or write the logic directly into the corresponding code

Direct processing of each value in the collection details see Array processing 9 automatically growing data types MYSQL has automatically growing data types, insert records do not need to operate this field, will automatically obtain data values. ORACLE does not have an auto-growing data type, so you need to create an auto-growing serial number, and when you insert a record, you assign the next value of the serial number to this field. Processing of 10NULLNULL Null characters

MYSQL's non-empty fields also have empty content, and ORACLE defines non-empty fields that do not allow empty content. Define the structure of the ORACLE table according to the NOT NULL of MYSQL, and errors will occur when importing data. Therefore, it is necessary to judge the empty character when importing the data, and if it is a NULL or empty character, you need to change it to a space string.

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 day_second, interval' 1 1 day_second)

9 string concatenation different result: = v_int1 | | villustrint2 alternate set result = concat (vint1mvint2)

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 Mysql cursor processing section 11 transaction rollback using the same method in ROLLBACK;ROLLBACK;oracle and mysql, the same method in 12GOTO statements GOTO check_date;GOTO check_date;oracle and mysql

Function

Numbering Category ORACLEMYSQL Note 1 numeric function round (1.23456pj4) round (1.23456pd4) is the same:

ORACLE:select round (1.23456 and 4) value from dual

MYSQL:select round (1.23456) value2abs (- 1) abs (- 1) function: take the current data as an absolute value

Usage: oracle is the same as mysql

Mysql: select abs (- 1) value

Oracle: select abs (- 1) value from dual

3ceil (- 1.001) ceiling (- 1.001) function: returns the smallest integer not less than X

Usage:

Mysqls: select ceiling (- 1.001) value

Oracle: select ceil (- 1.001) value from dual

4floor (- 1.001) floor (- 1.001) function: returns the maximum integer value not greater than X

Usage:

Mysql: select floor (- 1.001) value

Oracle: select floor (- 1.001) value from dual

5Max (expr) / Min (expr) Max (expr) / Min (expr) function: returns the minimum or maximum value of expr. MIN () and MAX () can accept a string argument; here

In this case, they will return the smallest or largest string.

Usage:

ROACLE: select max (user_int_key) from sd_usr

MYSQL: select max (user_int_key) from sd_usr;6 string function ascii (str) ascii (str) function: returns the ASCII code value of the leftmost character of the string str. If str is an empty string

Then the return value is 0. If str is a NULL, the return value is also NULL.

Usage:

Mysql:select ascii ('a') value

Oracle:select ascii ('a') value from dual

7CHAR (NMagna...) CHAR (NMagol...) Function: CHAR () interprets the parameter as an integer and returns the character given by the ASCII code value represented by the integer

A string made up of. The NULL value will be ignored.

Usage:

Mysql:select char (97) value

Oracle:select chr (97) value from dual

8REPLACE (str,from_str,to_str) REPLACE (str,from_str,to_str) function: all string from_str that appears in the string str is replaced by to_str, and then returns this string.

Usage:

Mysql: SELECT REPLACE ('abcdef',' bcd', 'ijklmn') value

Oracle: SELECT Replace ('abcdef',' bcd', 'ijklmn') value from dual

The number of 9INSTR ('sdsq','s',2) INSTR (' sdsq','s') parameters is different

ORACLE: select INSTR ('sdsq','s',2) value from dual (required to start at location 2)

MYSQL: select INSTR ('sdsq','s') value (starting at default location 1) 10SUBSTR (' abcd',2,2) substring ('abcd',2,2) function name is different:

ORACLE: select substr ('abcd',2,2) value from dual

MYSQL: select substring ('abcd',2,2) value11instr (' abcdefg','ab') locate ('ab','abcdefg') function name is different:

Instr-> locate (Note: the positions of the substring and the total string of locate should be interchanged)

ORACLE: SELECT instr ('abcdefg',' ab') VALUE FROM DUAL

MYSQL: SELECT locate ('ab',' abcdefg') VALUE12length (str) char_length () function name is different:

ORACEL: SELECT length ('AAAASDF') VALUE FROM DUAL

MYSQL: SELECT char_length ('AAAASDF') VALUE13REPLACE (' abcdef', 'bcd',' ijklmn') REPLACE ('abcdef',' bcd', 'ijklmn'):

ORACLE: SELECT REPLACE ('abcdef',' bcd', 'ijklmn') value from dual

MYSQL: SELECT REPLACE ('abcdef',' bcd', 'ijklmn') value14LPAD (' abcd',14,'0') LPAD ('abcd',14,' 0'):

ORACLE: select LPAD ('abcd',14,' 0') value from dual

MYSQL: select LPAD ('abcd',14,' 0') value from dual15UPPER (iv_user_id) UPPER (iv_user_id):

ORACLE: select UPPER (user_id) from sd_usr

MYSQL: like select UPPER (user_id) from sd_usr;16LOWER (iv_user_id) LOWER (iv_user_id):

ORACLE: select LOWER (user_id) from sd_usr

MYSQL: select LOWER (user_id) from sd_usr;17 control stream function nvl (u.email_address, 10) IFNULL (u.email_address, 10)

Or

The name of the ISNULL (u.email_address) function is different (selected according to the role):

ORACLE: select u.email_address, nvl (u.email_address, 10) value from sd_usr u (if u.email_address=NULl, replace its value with 10 in DB)

MYSQL: select u.email_address, IFNULL (u.email_address, 10) value from sd_usr u (if u.email_address=NULl, 10 is shown in the result instead of replacing its value with 10 in DB)

Select u.email_address, ISNULL (u.email_address) value from sd_usr u (1 if u.email_address is NULL, otherwise 0) 18DECODE (iv_sr_status,g_sr_status_com, ld_sys_date, NULL) none, please use IF or CASE statement instead.

IF statement format: (expr1,expr2,expr3) description:

1. Decode (condition, value 1, translation value 1, value 2, translation value 2. Value n, translation value n, default)

This function has the following meaning:

IF condition = value 1 THEN

RETURN (translation value 1)

ELSIF condition = value 2 THEN

RETURN (translation value 2)

.

ELSIF condition = value n THEN

RETURN (translation value n)

ELSE

RETURN (default)

END IF

2. Mysql If syntax description

Function: if expr1 is TRUE (expr1 0 and expr1 NULL), the return value of IF () is expr2

Otherwise, the return value is expr3. The return value of IF () is a numeric value or a string value, depending on where it is located

Depends on the context.

Usage:

Mysql: SELECT IF (1 > 2, 2 and 3)

19 Type conversion function TO_CHAR (SQLCODE) date_format/ time_format function name is different

SQL > select to_char (sysdate,'yyyy-mm-dd') from dual

SQL > select to_char (sysdate,'hh34-mi-ss') from dual

Mysql > select date_format (now (),'% Ymuri% mme% d')

Mysql > select time_format (now (),'% Hmurf% iMel% S'); 20to_date (str,format) STR_TO_DATE (str,format) function name is different:

ORACLE:SELECT to_date ('2009-3-6) VAULE FROM DUAL

MYSQL: the name of the SELECT STR_TO_DATE ('2004-03-01 as SIGNED,'% Ymuri% MMI% d') VAULE21trunc (- 1.002) cast (- 1.002 as SIGNED) function is different:

The date value truncated by the TRUNC function for the specified element.

ORACLE: select trunc (- 1.002) value from dual

MYSQL:select cast (- 1.002 as SIGNED) value

MYSQL:

Character set conversion: CONVERT (xxx USING gb2312)

Type conversion is the same as SQL Server, except that the type parameters are slightly different: CAST (xxx AS type), CONVERT (xxx, type), the type must use the following types:

Available types

Binary, same effect with binary prefix: BINARY

Character type, with parameter: CHAR ()

Date: DATE

Time: TIME

Date and time type: DATETIME

Floating point numbers: DECIMAL

Integer: SIGNED

Unsigned integers: UNSIGNED22TO_NUMBER (str) CAST ("123" AS SIGNED INTEGER) function name is different

ORACLE:SELECT TO_NUMBER ('123') AS VALUE FROM DUAL

MYSQL: SELECT CAST ("123" AS SIGNED INTEGER) as value

SIGNED INTEGER: the signed 23-day shaping function SYSDATEnow () / SYSDATE () is written differently:

ORACLE:select SYSDATE value from dual

MYSQL:select now () value

Select sysdate () value24Next_day (sysdate,7) customizes a function: the F_COMMON_NEXT_DAY (date,int) function has a different name:

ORACLE: SELECT Next_day (sysdate,7) value FROM DUAL

MYSQL: SELECT F_COMMON_NEXT_DAY (SYSDATE (), 3) value from DUAL

(3: refers to the index value of the week) the name of the specified 25ADD_MONTHS (sysdate, 2) DATE_ADD (sysdate (), interval 2 month) function returned next week is different:

ORACLE: SELECT ADD_MONTHS (sysdate, 2) as value from DUAL

MYSQL: SELECT DATE_ADD (sysdate (), interval 2 month) as value from DUAL;262 date subtraction (D1-D2) DATEDIFF (date1,date2) function: returns the number of days between two dates.

Usage:

Mysql: SELECT DATEDIFF ('2008-12-30') AS DiffDate

Oracle: subtract two dates directly (for example, d1-d2=12.3)

There is no corresponding function in the 27SQL function SQLCODEMYSQL, but SQLException in JAVA. The getErrorCode () function can get the error number. The Oracle built-in functions SQLCODE and SQLERRM are specifically used in OTHERS processors to return the error code and error message of Oracle, respectively.

MYSQL: you can get the error code from JAVA. There is no corresponding function in the error status and error message 28SQLERRMMYSQL, but there is SQLException in JAVA. The getMessage () function can get error messages. The Oracle built-in functions SQLCODE and SQLERRM are specifically used in OTHERS processors to return error codes and error messages for Oracle, respectively.

MYSQL: you can get error codes, error status and error messages from JAVA. 29SEQ_BK_DTL_OPT_INT_KEY.NEXTVAL auto-grow columns are auto-grow columns in MYSQL. Get the latest ID by using the following method:

START TRANSACTION

INSERT INTO user (username,password)

VALUES (username,MD5 (password))

SELECT LAST_INSERT_ID () INTO id

COMMIT;30SUM (enable_flag) SUM (enable_flag) is the same:

ORCALE: SELECT SUM (enable_flag) FROM SD_USR

MYSQL: SELECT SUM (enable_flag) FROM SD_USR;31DBMS_OUTPUT.PUT_LINE (SQLCODE) has no corresponding method in MYSQL, its function is to print in the console for testing, and has no effect on migration. Dbms_output.put_line can only display 255characters per line. If you exceed it, an error will be reported.

Loop statement

Numbered category ORACLEMYSQL comment 1IF statement uses a different IF iv_weekly_day = 'MON'THEN

Ii_weekly_day: = 'MON'

ELSIF iv_weekly_day = 'TUE' THEN

Ii_weekly_day: = 'TUE'

END IF;IF iv_weekly_day = 'MON'THEN

Set ii_weekly_day = 'MON'

ELSEIF iv_weekly_day = 'TUE' THEN

Set ii_weekly_day = 'TUE'

END IF;1. Mysql and oracle use exactly the same if statement except that there is one word difference in keyword (ELSEIF/ELSIF).

2. Mysql if statement syntax: extracted from MySQL 5.1 reference manual 20.2.12.1. IF statement

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list]...

[ELSE statement_list]

END IF

IF implements a basic conditional construct. If the search_condition evaluation is true, the corresponding list of SQL statements is executed. If there is no search_condition match, the list of statements in the ELSE clause is executed. Statement_list can include one or more statements. 2FOR statement is different from FOR li_cnt IN 0. (ii_role_cnt-1) LOOP

SELECT COUNT (*) INTO li_role_ik_cnt FROM SD_ROLE

WHERE ROLE_CD = lo_aas_role_upl (li_cnt)

IF li_role_ik_cnt = 0 THEN

RETURN'N'

END IF

Li_role_ik_cnt: =-3

END LOOP;loopLable:LOOP

IF I > (ii_role_cnt-1) THEN

LEAVE looplable

ELSE

SELECT COUNT (*) INTO li_role_ik_cnt FROM SD_ROLE

WHERE ROLE_CD = 'ADMIN_SUPER'; / * lo_aas_role_upl (li_cnt); * /

IF li_role_ik_cnt = 0 THEN

RETURN'N'

END IF

SET li_role_ik_cnt =-3

SET I = iTun1

END IF

END LOOP loopLable

1. Oracle uses the Forth statement to implement the loop.

Mysql uses the Loop statement to implement the loop.

2. Oracle uses For... Loop keyword.

Mysql uses loopLable:LOOP to implement loops. 3 while statements are different from WHILE lv_inputstr IS NOT NULL LOOP

...

END LOOP;WHILE lv_inputstr IS NOT NULL DO

...

The keyword of the whilestatement used in END WHILE;1. Oracle is: while expression loop... End loop

The keyword of using whilestatement in mysql is: while expression do … End while

Stored procedure & Function

Numbering category ORACLEMYSQL comment 1 creates a stored procedure statement with a different create or replace procedure P_ADD_FAC

Id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) isDROP PROCEDURE IF EXISTS `SD_USER_P_ADD_ USR`

Create procedure P_ADD_FAC (

Id_fac_cd varchar (100)

1. If a stored procedure with the same name exists when creating a stored procedure, the old stored procedure is deleted.

Oracle uses create or replace.

Mysql uses to delete the old stored procedure before creating a new one.

2. Oracle stored procedures can be defined in package or Procedures. If defined in a package, a package can contain multiple stored procedures and methods. If defined in Procedures, you cannot define multiple stored procedures in a stored procedure.

Multiple stored procedures cannot be defined in Mysql stored procedures.

3. String types in oracle can use varchar2.

Varchar is required for Mysql

4. Parameter varchar length is not required in Oracle

The parameter varchar length in Mysql is required, for example, varchar (100) 2 creates a function statement with different CREATE OR REPLACEFUNCTION F_ROLE_FACS_GRP (

Ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE

) RETURN VARCHAR2DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_ GRP`

CREATE FUNCTION `SD_ROLE_F_ROLE_FACS_ GRP` (

Ii_role_int_key INTEGER (10)

) RETURNS varchar (1000) 1. If a function with the same name exists when creating a function, the old function will be deleted.

Oracle uses create or replace.

Mysql uses to delete the old function before creating a new one.

2. The oracle function can be defined in package or Functions. If defined in a package, a package can contain multiple stored procedures and functions. If defined in Functions, only one function can be defined per function.

Mysql Functions cannot define multiple functions.

3. The return value of oracle is return.

The return value of Mysql is written in a different procedure P_ADD_FAC with returns.3 input parameters (

Id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) create procedure P_ADD_FAC (

(in) id_fac_cd varchar (100)

1. Oracle stored procedure parameters can be defined as the field type of the table.

This method of definition is not supported by Mysql stored procedures. You need to define the actual type and length of the variable.

2. The oracle parameter type in/out/inout is written after the parameter name.

The Mysql parameter type in/out/inout is written before the parameter name.

3. Oracle parameter type in/out/inout must be written.

The Mysql parameter type can be omitted if it is in. If it is out or inout, it cannot be omitted.

Note: the parameter specified in mysql is IN, OUT, or INOUT is legal only for PROCEDURE. (the FUNCTION parameter is always considered to be an IN parameter.) the RETURNS statement can only specify FUNCTION, which is mandatory for functions. It is used to specify the return type of the function, and the function body must contain a RETURN value statement.

Function func_name (

Gw_id in (out) varchar2) create function func_name (

Gw_id varchar 4 package declaration create or replace package/package body package name split into multiple stored procedures or functions oracle can create packages, packages can contain multiple stored procedures and methods.

Mysql does not have the concept of packages, you can create stored procedures and methods respectively. Each stored procedure or method needs to be placed in a file.

Example 1: method naming

SD_FACILITY_PKG.F_SEARCH_FAC in oracle

To mysql SD_FACILITY_F_SEARCH_FAC

Example 2: procedure naming

SD_FACILITY_PKG.P_ADD_FAC in oracle

To mysql SD_FACILITY_P_ADD_FAC

5 stored procedure return statements are different return;LEAVE proc; (proc stands for outermost begin end) oracle stored procedures and methods can use return to exit the current procedure and method.

Only leave can be used to exit the current stored procedure in the Mysql stored procedure. You can't use return.

The Mysql method can exit the current method using return. 6 stored procedure exception handling is different from EXCEPTION

WHEN OTHERS THEN

ROLLBACK

Ov_rtn_msg: = c_sp_name | |'('| | li_debug_pos | |'): | |

TO_CHAR (SQLCODE) | |':'| | SUBSTR (SQLERRM,1100); DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK

Set ov_rtn_msg = concat (centering spacename'(', li_debug_pos,'):'

TO_CHAR (SQLCODE),':', SUBSTR (SQLERRM,1100))

END;oracle: internal exceptions do not need to be defined. After EXCEPTION is written at the end of a stored procedure or function, the following part is the exception handling part. Oracle can define custom exceptions. Custom exceptions need to be thrown using the raise keyword before they can be caught in EXCEPTION.

Mysql: mysql internal exception also needs to be defined first, and the exception function needs to be implemented at the same time.

Currently, mysql does not support custom exceptions. 7 procedures and functions declare variables in different locations in begin. Before the end body declares the variable is in the begin...end body, and before anything else after begin, the 8NO_DATA_FOUND exception handles EXCEPTION.

WHEN NO_DATA_FOUND THEN

Oi_rtn_cd: = 1

Ov_rtn_msg: = SD_COMMON.P_GET_MSG ('DP-CBM-01100a-016'

Li_sub_rtn_cd

Lv_sub_rtn_msg

); use FOUND_ROWS () instead of NO_DATA_FOUND. For details, please see Note .oracle:

NO_DATA_FOUND is an attribute of a cursor.

When select does not find the data, there will be an exception of no data found, and the program will not be executed downward.

Mysql:

There is no NO_DATA_FOUND attribute. But you can use the FOUND_ROWS () method to get the data queried by the select statement. If the value of FOUND_ROWS () is 0, it enters the exception handling logic. 9 different Procedure_Name (parameters) of the stored procedure way to call the stored procedure in the stored procedure; Call Procedure_Name (parameter); and MYSQL stored procedure calls the stored procedure, you need to use Call pro_name (parameter).

Oracle calls the stored procedure to write the stored procedure name directly. 10 different ways to throw an exception can be found in the exception handling section of 2.5 Mysql in RAISE Exception_Name; for more information.

Trigger

Numbered category ORACLEMYSQL comment 1 create trigger statement different create or replace trigger TG_ES_FAC_UNIT

Before insert or update or delete on ES_FAC_UNIT

For each rowcreate trigger `hs_ esbs`.`TG _ INSERT_ES_FAC_ UNIT`BEFORE INSERT on `hs_ esbs`.`es _ fac_ unit`

For each row1. Oracle uses create or replace trigger syntax to create triggers.

Mysql uses create trigger to create triggers.

2. Oracle can trigger an insert,delete,update event in a trigger.

Mysql supports only one event per trigger. In other words, each trigger needs to be split into 3 mysql trigger.

3. Mysql trigger cannot be displayed or edited on the client side. You need to operate on the machine where the server resides. 2 trigger new and old record rows have different references to get new data:: new.FAC_CD

Get old data:: old.FAC_CD get new data: NEW.FAC_CD

Get the old data: OLD.FAC_CD1. References to new and old record rows:

Mysql is referenced by NEW.col1,OLD.col1.

Oracle is referenced by: NEW.col1,: OLD.col1.

2. NEW and OLD are not case sensitive.

User permissions

Numbered Category ORACLEMYSQL Note 1 create user Create user user_name identified by user_password

Default tablespace starSpace temporary tablespace temp;CREATE USER user_name IDENTIFIED BY user_password;1.oracle create user

There are three default users of Oracle: sys / system / scott. Sys and system are system users with dba authority, and scott user is a demonstration account of Oracle database, which is created during database installation and does not have dba authority.

Create user commands:

Create user user_name identified by user_password

[default tablespace tableSpace]

[temporary tablespace tableSpace}

Description:

Each user has a default table space and a temporary table space. If not specified, oracle sets system to the default table space and temp to the temporary table space.

2.mysql create user

Create user commands:

Mysql > CREATE USER yy IDENTIFIED BY '123'

Yy indicates the user name you want to create, followed by 123 indicates the password

The users established above can log in anywhere.

If you want to restrict login to a fixed address, such as localhost login:

Mysql > CREATE USER yy@localhost IDENTIFIED BY '123 delete 2 delete user Drop user user_name cascade;Drop user user_name;1. Oracle

SQL > drop user user name; / / user has not created any entity

SQL > drop user username CASCADE; / / Delete all users and their created entities

Note: users who are currently connected must not be deleted.

2. Mysql

Since 4.1.1, delete a MYSQL account and use the drop user statement.

However, in versions prior to 5.0.2, the drop user statement could only delete users who did not have any permissions.

From version 5.0.2 onwards, the drop user statement removes any user. Of course, you can't delete yourself. Example: drop user "garfield" @ "localhost". Don't forget to add the @ after it, or you will make a mistake.

To delete a MYSQL account in versions between 4.1.1 and 5.0.2, you need to do the following.

1) use the show grants statement to see what permissions are available for the MYSQL account to be deleted, using methods such as show grants for "garfield" @ "localhost".

2) use the permission statement to reclaim the permissions that the user has in show grants. Executing this statement deletes the relevant records in the ownership limit table except the user table and reclaims the global permissions that the user has in the user table.

3) use the drop user statement to remove the user from the user table. 3 change password alter user user_name identified by new_password

Mysqladmin-u root-p 123456 password "your password"; 1.mysql changes password

The first way:

1) change the situation where root does not have a password before

C:\ mysql\ bin > mysqladmin-u root password "your password"

2) if the root has a password before the change, if it is 123456

C:\ mysql\ bin > mysqladmin-u root-p123456 password "your password"

Note: the changed password cannot use single quotation marks, double quotation marks can be used or no quotation marks can be used

The second way:

1) c:\ mysql\ bin > mysql-uroot-p password to log in as root

2) mysql > use mysql Select Database

3) mysql > update user set password=password ('your password') where User='root'

4) mysqlflush privileges; Reload permissions Table 4 set user permissions Grant connect to star-the star role allows users to connect to the database and create database objects

The Grant resource to star-star role allows users to use storage space in the database.

Grant dba to star-- DBA permission GRANT ALL ON picture.* TO test IDENTIFIED BY "test"; 1. For more information, please see 2.1 Oracle permission Settings.

two。 For more information, please see 1.4 user Rights Settings 5 Recycling permissions Revoke select, update on product from user02;REVOKE privileges (columns) ON what FROM user1. Oracle

The basic format of the Revoke statement is as follows:

REVOKE permission type [(field list)] [, permission type [(field list)] …] ON {database name. Table name} FROM user name @ domain name or IP address

For example, the administrator revokes the permissions that user admin@localhost has on database xsxk to create, create databases and tables, and revokes the permissions that the user can grant to other users, using the following command.

Mysql > revoke create,drop on xsxk.* from admin@localhost

Mysql > revoke grant option on xsxk.* from admin@localhost

The "user name @ domain name or IP address" section of the revoke statement must match the "user name @ domain name or IP address" section of the original grant statement, and the "permission type" section can be part of the granted permissions. Moreover, revoke can only revoke permissions, cannot delete user accounts, and still keep the user's record in the authorization table user; users can still connect to the database server. If you want to delete the user completely, delete the user record from the user table using the delete statement mentioned earlier.

2. Mysql

To revoke a user's privileges, use the revoke statement. The syntax of REVOKE is very similar to the GRANT statement, except that TO is replaced by FROM and there are no INDETIFED BY and WITH GRANT OPTION clauses:

REVOKE privileges (columns) ON what FROM user

The user part must match the user part of the original GRANT statement for the user you want to revoke. The privileges part does not need to match, you can use the Grant statement to authorize, and then use the revoke statement to revoke only part of the permission.

The REVOKE statement removes only permissions, not users. Even if you revoke all permissions, the user record in the user table remains, which means that the user can still connect to the server. To delete a user completely, you must explicitly delete the user record from the user table with a Delete statement

Other

Changes to connections in numbered category ORACLEMYSQL comment 1

1 、

Select a., B., C., D. *

From a, b, c, d

Where a.id = b.id

And a.name is not null

And a.id = c.id (+)

And a.id = d.id (+)

The other side of the position of "(+)" is the direction of the connection.

So example 1 above is a left connection.

Example 2 below is both a right connection.

2 、

Select a., B., C., D. *

From a, b, c, d

Where a.id = b.id

And a.name is not null

And a.id (+) = c.id

Method one

Select a. Floor, c. Stories, d.*

From a

Left join (c, d)

On (a.id = c.id and a.id = d.id), b

Where a.id = b.id

And a.name is not null

Method two

Select a. Floor, c. Stories, d.*

From a

Left join c on a.id = c.id

Left join d on a.id = d.id, b

Where a.id = b.id

There are some differences between and a.name is not nulloracle sql statement and mysql sql statement.

1. Oracle left connection, right connection can be achieved using (+).

Mysql can only use the keywords left join, right join, etc. 2 obtained by the sql statement executed in the last sentence or

The number of affected SQL%ROWCOUNT is followed by: FOUND_ROWS () after executing the select statement

After executing the update delete insert statement, use:

In ROW_COUNT (). Oracle:

Sql indicates the SQL Statement executed in the last sentence, and rowcount indicates the number of entries obtained or affected by the SQL.

In Mysql:

The number of entries affected by the query after the execution of the select statement uses: FOUND_ROWS ()

The number of entries affected by the query after the execution of the update delete insert statement: ROW_COUNT () 3 query paging SELECT t1.*

FROM

(SELECT MSG_INT_KEY

MSG_TY

MSG_CD

ROWNUM ROW_NUM

FROM SD_SYS_MSG

WHERE (ii_msg_int_key IS NULL

OR msg_int_key = ii_msg_int_key)

ORDER BY MSG_CD

) T1

WHERE (in_page_no IS NULL)

OR (t1.ROW_NUM >

((in_page_no-1) * li_per_page_amt)

AND t1.ROW_NUM

< (in_page_no*li_per_page_amt + 1) );方法:使用循环变量替换oracle中ROWNUM set @mycnt = 0; SELECT (@mycnt := @mycnt + 1) as ROW_NUM,t1.* FROM (SELECT MSG_INT_KEY, MSG_TY, MSG_CD, ROWNUM ROW_NUM FROM SD_SYS_MSG WHERE (ii_msg_int_key IS NULL OR msg_int_key = ii_msg_int_key ) ORDER BY MSG_CD ) t1 WHERE (in_page_no IS NULL) OR (t1.ROW_NUM>

((in_page_no-1) * li_per_page_amt)

AND t1.ROW_NUM

< (in_page_no * li_per_page_amt + 1) ); 4java null值""作为参数传入后,在oracle中将识别为null""作为参数据传mysql还是""现在java代码需要修改: inPara.add(MSG_TY.equals("") ? null : MSG_TY);5执行动态sqllv_sql := 'SELECT ' ||' distinct ' || iv_cd_field_name || ' FIELD1 '|| ' FROM ' || iv_table_name || ' WHERE ' || NVL(iv_where_cause,' 1=1 '); OPEN l_sys_cur FOR lv_sql;set @a = iv_cd_field_name; set @b = iv_table_name; set @c = IFNULL(iv_where_cause,' 1=1 '); SET @s = concat('SELECT distinct ', @a , ' FIELD1 FROM ' , @b , ' WHERE ' , IFNULL(@c,' 1=1 ')); PREPARE stmt3 FROM @s; EXECUTE stmt3; DEALLOCATE PREPARE stmt3;1. oracle可以将动态sql放在游标中执行. mysql游标声明有一定的局限性: mysql游标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。Mysql采用Prepared Statements实现动态sql. 例子如下: INT Emp_id_var = 56 PREPARE SQLSA FROM "DELETE FROM employee WHERE emp_id=?" ; EXECUTE SQLSA USING :Emp_id_var ; 6存储过程相互调用时传递数组 Oracle使用数组步骤: 1. 将传入的字符串通过P_UNPACK_LIST方法转换为数组.(lo_holiday_jan_upl即为数组) P_UNPACK_LIST(iv_jan__str, lv_delimiter, lo_holiday_jan_upl); 2. 传数组到另一个存储过程. P_MOD_MONTH(iv_year, 1, lo_holiday_jan_upl, iv_user_cd); 3. P_MOD_MONTH中使用数组: (将数组中的各个元素取出来插入到SD_HOLIDAY表) FOR li_cnt IN 0 .. 9 LOOP IF iv_daystr(li_cnt) IS NOT NULL THEN INSERT INTO SD_HOLIDAY (HOLIDAY_INT_KEY, YEAR, MONTH, DAY, ENABLE_FLAG, CREATE_BY, CREATE_DATE, LAST_UPD_BY, LAST_UPD_DATE) VALUES (SEQ_HOLIDAY_INT_KEY.NEXTVAL, iv_year, iv_month, iv_daystr(li_cnt), 1, iv_user_cd, ld_sys_date, iv_user_cd, ld_sys_date); END IF; END LOOP; mysql中数用数组步骤: 1. 将需要处理的字符串交给执行业务逻辑 的存储过程处理. CALL SD_HOLIDAY_P_MOD_MONTH(iv_year, 1, iv_jan__str, iv_user_cd); 2. SD_HOLIDAY_P_MOD_MONTH中处理字符串. (将字符串按自定格式分隔出来,在对每个小字符串进行业务逻辑处理.) SET lv_inputstr = iv_inputstr; loopLable:LOOP IF li_cnt >

9 THEN

LEAVE looplable

ELSE

SET li_pos = INSTR (lv_inputstr, iv_delimiter)

IF li_pos = 0 THEN

Leave looplable

ELSE

Set temp_str = SUBSTR (lv_inputstr, 1, li_pos-1)

/ * insert temp_str into SD_HOLIDAY table * /

INSERT INTO SD_HOLIDAY (...)

SET lv_inputstr = SUBSTRING (lv_inputstr, li_pos + LENGTH (iv_delimiter))

END IF

SET li_cnt = li_cnt+1

END IF

Pass an array solution when END LOOP loopLable; stored procedures call each other:

12 strings are passed into the stored procedure in oracle, then the 12 strings are converted into 12 arrays, and then other stored procedures are called and the 12 arrays are passed to the stored procedure respectively, which facilitates the business logic processing of each array.

Mysql solution: remove the array in the stored procedure, transfer the string directly when the two stored procedures are called, and then decompose the string where you need to deal with the business logic for business logic processing.

You can refer to 2.4.2 in which the string is decomposed layer by layer. 7Java cannot be used to pick up intselect fac_unit_key FILED1 in String. In oracle, select fac_unit_key FILED1 can be changed in mysql.

Select CAST (fac_unit_key AS CHAR) FILED1CAST (intvalue AS CHAR) are all the contents of this article entitled "what are the language differences between MySQL and Oracle SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.