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