Syntax differences between stored procedures in mysql and oracle

Syntax differences between stored procedures in mysql and oracle

Syntax differences between stored procedures in mysql and oracle

1. conditional statement: mysql uses the elseif keyword, and oracle uses the elsif keyword;

Oracle:

If expression then

Expression;

Elsif

Expression;

Endif;

Mysql:

If expression then

Expression;

Elseif

Expression;

Endif;

2. String connection

Oracle |;

Mysql uses the concat function;

3. Date calculation (year, month, and day)

Mysql:

The TimeStampDiff () function is provided by MySQL itself to calculate two time intervals. The syntax is TIMESTAMPDIFF (unit, datetime_expr1, datetime_expr2). The unit is as follows: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Current Time: sysdate ()

Character to date: the str_to_date () delimiter must be the same, and the year, month, and day must be the same. Example:

Select str_to_date ('2014-4-2 ',' % Y-% m-% d % H: % I: % s ');

Date to character: DATE_FORMAT (date, format)

SELECT DATE_FORMAT(sysdate(), '%Y-%m-%d %H:%i:%s');

Numeric to character: concat (num ,'')

Oracle:

Months_between calculates the date interval month. Dividing by 12 is the year of the interval;

Days, which only need to be offset by date;

Current Time: sysdate

Character to 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. Define a cursor

Cancel:

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

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

SELECT a. INDEX_SCORE

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

FROM eval_plan_index

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 assignment

Oracle exception 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 does not perform the into operation if the select statement does not have data. The variable value remains the same as the previous result and needs to be reset manually. It is best to limit 1; only one data record is returned;