MySQL, Oracle stored procedure syntax differences

MySQL, Oracle stored procedure syntax differences

1. Conditional statement: MySQL uses elseif keyword, Oracle is elsif keyword;

Oracle

If expression then

An expression;

elsif

An expression;

endif

Mysql:

If expression then

An expression;

ElseIf

An expression;

endif

2. String connection

Oracle Uses | | ；

MySQL uses the concat function;

3. Date calculation (days of month and year)

Mysql:

The function Timestampdiff () is a function that MySQL itself provides to calculate two time intervals, the syntax is: Timestampdiff (UNIT,DATETIME_EXPR1,DATETIME_EXPR2), where the unit unit has the following types, respectively: SECOND, MINUTE, HOUR, Day,week, MONTH, QUARTER, or year.

Character to date: Str_to_date () The delimiter is the same, the date of the year is consistent; Example:

Select Str_to_date (' 2008-4-2 15:3:28 ', '%y-%m-%d%h:%i:%s ');

SELECT Date_format (Sysdate (), '%y-%m-%d%h:%i:%s ');

Numeric to Character: Concat (num, ")

Oracle

Months_between The date interval month, divided by 12, which is the interval year;

The number of days, only need the date direct subtraction;

Current time: Sysdate

Character Turn date: To_date ()

Date to Character: To_char (Date,format) to_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ')

Numeric to Character: To_char (num)

4. Defining cursors

Oracel:

CURSOR Curplanindex is

SELECT A.index_score

, C.enum_value,c.dn_value,c.up_value,c.score,c.score_desc

From Eval_plan_index A

JOIN eval_index_score c onc.index_id=a.index_id and a.plan_id = c.plan_id

WHERE a.plan_id = v_plan_id and a.index_id= v_index_id

Order BY Dn_value;

Mysql:

DECLARE curplanindex cursor FOR

SELECT A.index_score

, C.enum_value,c.dn_value,c.up_value,c.score,c.score_desc

From Eval_plan_index A

JOIN eval_index_score c onc.index_id=a.index_id and a.plan_id = c.plan_id

WHERE a.plan_id = v_plan_id and a.index_id= v_index_id

Order BY Dn_value;

5, Selectinto assigned value

Oracle has exception error handling

Begin

Select Value_name into Vc_num_unit fromsys_dict

where Dict_code = ' szdw ' and Value_code =v_num_unit and rownum <=1;

exception

When No_data_found Then

Vc_num_unit: = ";

End

MySQL If the select has no data, the into operation is not performed, and the value of the variable remains the last result and needs to be reset manually. It is best to limit 1; return only one piece of data;

MySQL, Oracle stored procedure syntax differences