1. WHILE

Oracle: WHILE cond  LOOP statement ...  END LOOP;

MySQL: WHILE cond  DO  statement ...END WHILE; 

2. IF

Oracle: IF cond THEN statement ELSIF statement c statement END IF

MySQL: IF cond  THEN statement ELSEIF  cond THEN statement ELSE statement END IF;
 

3. Exception

Oracle:  EXCEPTION WHEN cond THEN statement

MySQL: DECLARE handler_type HANDLER
FOR condition_value , condition_value ... Statement

handler_type:

  • CONTINUE
  • EXIT
  • UNDO

condition_value:

  • SQLSTATE VALUE

sqlstate_value:

  • SQLWARNING
  • NOT FOUND
  • SQLEXCEPTION
  • mysql_error_code

Comments: MySQL Sample

CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO test.t VALUES (1);
SET @x = 2; INSERT INTO test.t VALUES (1); SET @x = 3;
END;

Details: http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

4. DECODE

Oracle:

decode(supplier_i, 10000, 'IBM', 10001,'Microsoft', 10002, 'Hewlett Packard',Gateway')

MySQL:

CASE variable WHEN 10000 THEN  'val1' WHEN 10001  THEN 'val2' ELSE  'val3' end
 

CASE in MYSQL:

SELECT  CASE job_lvl WHEN 10000 THEN  'val1' WHEN 10001  THEN 'val2' ELSE  'val3' end from employees;

5. NVL

ORACLE: The NVL function lets you substitute a value when a null value is encountered. Syntax for the NVL function is:NVL ( string1, replace_with ) string1 is the string to test for a null value.

MySQL: IFNULL(expr1,expr2)

6. Right Outer Join

Oracle: T2.C1(plus) = T1.C1   Example -select last_name, d.department_id from employees e, departments d where e.department_id (plus) = d.department_id;

MySQL :   T1 RIGHT JOIN T2 ON T1.A=T2.A

7.  LEFT  Outer Join

Oracle: T2.C1 = T1.C1(plus)   Example -select last_name, d.department_id from employees e, departments d where e.department_id  = d.department_id(plus) ;

MySQL :   T1 LEFT JOIN T2 ON T1.A=T2.A

8. SYSDATE

Oracle: SYSDATE

MySQL: NOW()

9. CURRENT_USER

Oracle: CURRENT_USER

MySQL: CURRENT_USER()

10. FUNCTION

Oracle:  FUNCTION <name > (param1 IN STRING, param2   IN STRING) RETURN STRING 

MySQL :  CREATE FUNCTION <name > ( IN param1 STRING,  IN parma2 STRING) RETURNS STRING 

11. PROCEDURE

Oracle:  PROCEDURE <name > (param1 IN STRING, param2   IN STRING)

MySQL :  CREATE PROCEDURE <name > ( IN param1 STRING,  IN parma2 STRING)

12. CURSOR:

Oracle:  CURSOR cursor_name IS select-statement;   OPEN cursor_name

MySQL:   BEGIN  DECLARE my_cursor CURSOR FOR select-statement << can't be prepared statement>>; OPEN my_cursor; END

  • No labels