Comparison between MySQL and Oracle five stored procedures & functions, mysqloracle

Comparison between MySQL and Oracle five stored procedures & functions, mysqloracle

Stored Procedure & Function

No.	Category	ORACLE	MYSQL	Note
1	The statement for creating a stored procedure is different.	Create or replace procedure P_ADD_FAC ( Id_fac_cd IN ES_FAC_UNIT.FAC_CD % TYPE) is	Drop 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 during the creation of the stored procedure, the old stored procedure will be deleted. Oracle uses create or replace. Mysql deletes the old stored procedure before creating a new stored procedure. 2. oracle stored Procedures can be defined in a package or Procedures. if it is defined in a package, a package can contain multiple stored procedures and methods. if it is defined in Procedures, multiple stored Procedures cannot be defined in the stored procedure. Multiple stored procedures cannot be defined in the Mysql stored procedure. 3. varchar2. Mysql needs to use varchar 4. The length of the varchar parameter in Oracle is not required, The length of the varchar parameter in Mysql is required, for example, varchar (100)
2	Different function creation statements	Create or replace function F_ROLE_FACS_GRP ( Ii_role_int_key IN SD_ROLE.ROLE_INT_KEY % TYPE ) RETURN VARCHAR2	Drop function if exists 'sd _ ROLE_F_ROLE_FACS_GRP '; Create function 'sd _ ROLE_F_ROLE_FACS_GRP '( Ii_role_int_key INTEGER (10) ) RETURNSvarchar (1000)	1. If a function with the same name exists during function creation, the old function will be deleted. Oracle uses create or replace. Mysql deletes the old function first, and then creates a new function. 2. oracle Functions can be defined in a package or in Functions. if defined in a package, a package can contain multiple stored procedures and functions. if it is defined in Functions, each function can only define one function. Mysql Functions cannot define multiple Functions. 3. return the oracle return value using return. The Mysql return value is returns.
3	The input parameters are written differently.	Procedure P_ADD_FAC ( 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. Mysql stored procedures do not support this definition method. You need to define the actual type and length of the variable. 2. The oracle parameter types in/out/inout are written after the parameter name. Mysql parameter types in/out/inout are written before the parameter name. 3. oracle parameter types in/out/inout must be written. Mysql parameter type can be omitted if it is in. If it is out or inout, it cannot be omitted. Note: IN, OUT, or INOUT is valid only for PROCEDURE. (FUNCTION parameters are always considered as IN parameters.) The RETURNS clause can only be used to specify the FUNCTION. This is mandatory for the FUNCTION. 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 (100 ))
4	Package declaration method	Create or replace package/package body package name	Split into multiple stored procedures or functions	Oracle can create packages that contain multiple stored procedures and methods. Mysql does not have the package concept. You can create stored procedures and methods separately. Each stored procedure or method must be stored in a file. Example 1: Method naming SD_FACILITY_PKG.F_SEARCH_FAC in oracle To mysql SD_FACILITY_F_SEARCH_FAC Example 2: Process naming SD_FACILITY_PKG.P_ADD_FAC in oracle To mysql SD_FACILITY_P_ADD_FAC
5	Different Stored Procedure return statements	Return;	LEAVE proc; (proc represents the in end of the outermost layer)	Oracle stored procedures and methods can use return to exit the current process and method. Mysql stored procedures can only use leave to exit the current stored procedures. return cannot be used. Mysql method can exit the current method using return.
6	Exception Handling in stored procedures is different	EXCEPTION WHEN OTHERS THEN ROLLBACK; Ov_rtn_msg: = c_sp_name | '(' | li_debug_pos | '):' | TO_CHAR (SQLCODE) | ':' | SUBSTR (SQLERRM, 1,100 );	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; Set ov_rtn_msg = concat (c_sp_name, '(', li_debug_pos ,'):', TO_CHAR (SQLCODE), ':', SUBSTR (SQLERRM, 1,100 )); END;	Oracle: Internal exceptions do not need to be defined. After an EXCEPTION is written at the end of a stored procedure or function, the subsequent part is the EXCEPTION handling part. oracle can define custom exceptions. Custom exceptions can be captured only when an EXCEPTION is thrown using the raise keyword. Mysql: mysql internal exceptions also need to be defined first. At the same time, exceptions must be implemented. Currently, mysql does not support custom exceptions.
7	The positions of declared variables for procedures and functions are different.	Declare variables in begin... Before end	Declare variables in the begin... end body, before any other content after begin
8	NO_DATA_FOUND Exception Handling	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 more information, see note.	Oracle: NO_DATA_FOUND is an attribute of the cursor. If no data is found in the select statement, no data found exception will occur, and the program will not run down. Mysql: There is no NO_DATA_FOUND attribute, but the data queried by the select statement is obtained using the FOUND_ROWS () method. If the value obtained by FOUND_ROWS () is 0, the exception handling logic is entered.
9	Different Methods for calling stored procedures in Stored Procedures	Procedure_Name (parameter );	Call Procedure_Name (parameter );	For MYSQL stored procedures to Call stored procedures, Call pro_name (parameter) is required ). Oracle calls the stored procedure and directly writes the stored procedure name.
10	The exception throwing method is different.	RAISE prediction_name;	See Remarks	See section 2.5 Mysql Exception Handling in <2009002-OTMPPS-Difficult Questions-0001.doc>