Shulou(Shulou.com)05/31 Report--

The purpose of this article is to share with you the content of the sample analysis of Function, a stored procedure for comparing the differences between MySQL and Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Stored procedure & Function

Numbering category ORACLEMYSQL comment 1 creates a stored procedure statement with a different create or replace procedure P_ADD_FAC

Id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) isDROP 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 when creating a stored procedure, the old stored procedure is deleted.

Oracle uses create or replace.

Mysql uses to delete the old stored procedure before creating a new one.

2. Oracle stored procedures can be defined in package or Procedures. If defined in a package, a package can contain multiple stored procedures and methods. If defined in Procedures, you cannot define multiple stored procedures in a stored procedure.

Multiple stored procedures cannot be defined in Mysql stored procedures.

3. String types in oracle can use varchar2.

Varchar is required for Mysql

4. Parameter varchar length is not required in Oracle

The parameter varchar length in Mysql is required, for example, varchar (100) 2 creates a function statement with different CREATE OR REPLACE FUNCTION F_ROLE_FACS_GRP (

Ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE

) RETURN VARCHAR2DROP 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 when creating a function, the old function will be deleted.

Oracle uses create or replace.

Mysql uses to delete the old function before creating a new one.

2. The oracle function can be defined in package or Functions. If defined in a package, a package can contain multiple stored procedures and functions. If defined in Functions, only one function can be defined per function.

Mysql Functions cannot define multiple functions.

3. The return value of oracle is return.

The return value of Mysql is written in a different procedure P_ADD_FAC with returns.3 input parameters (

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.

This method of definition is not supported by Mysql stored procedures. You need to define the actual type and length of the variable.

2. The oracle parameter type in/out/inout is written after the parameter name.

The Mysql parameter type in/out/inout is written before the parameter name.

3. Oracle parameter type in/out/inout must be written.

The Mysql parameter type can be omitted if it is in. If it is out or inout, it cannot be omitted.

Note: the parameter specified in mysql is IN, OUT, or INOUT is legal only for PROCEDURE. (the FUNCTION parameter is always considered to be an IN parameter.) the RETURNS statement can only specify FUNCTION, which is mandatory for functions. 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 4 package declaration create or replace package/package body package name split into multiple stored procedures or functions oracle can create packages, packages can contain multiple stored procedures and methods.

Mysql does not have the concept of packages, you can create stored procedures and methods respectively. Each stored procedure or method needs to be placed in a file.

Example 1: method naming

SD_FACILITY_PKG.F_SEARCH_FAC in oracle

To mysql SD_FACILITY_F_SEARCH_FAC

Example 2: procedure naming

SD_FACILITY_PKG.P_ADD_FAC in oracle

To mysql SD_FACILITY_P_ADD_FAC

5 stored procedure return statements are different return;LEAVE proc; (proc stands for outermost begin end) oracle stored procedures and methods can use return to exit the current procedure and method.

Only leave can be used to exit the current stored procedure in the Mysql stored procedure. You can't use return.

The Mysql method can exit the current method using return. 6 stored procedure exception handling is different from EXCEPTION

WHEN OTHERS THEN

ROLLBACK

Ov_rtn_msg: = c_sp_name | |'('| | li_debug_pos | |'): | |

TO_CHAR (SQLCODE) | |':'| | SUBSTR (SQLERRM,1100); DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK

Set ov_rtn_msg = concat (centering spacename'(', li_debug_pos,'):'

TO_CHAR (SQLCODE),':', SUBSTR (SQLERRM,1100))

END;oracle: internal exceptions do not need to be defined. After EXCEPTION is written at the end of a stored procedure or function, the following part is the exception handling part. Oracle can define custom exceptions. Custom exceptions need to be thrown using the raise keyword before they can be caught in EXCEPTION.

Mysql: mysql internal exception also needs to be defined first, and the exception function needs to be implemented at the same time.

Currently, mysql does not support custom exceptions. 7 procedures and functions declare variables in different locations in begin. Before the end body declares the variable is in the begin...end body, and before anything else after begin, the 8NO_DATA_FOUND exception handles 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 details, please see Note .oracle:

NO_DATA_FOUND is an attribute of a cursor.

When select does not find the data, there will be an exception of no data found, and the program will not be executed downward.

Mysql:

There is no NO_DATA_FOUND attribute. But you can use the FOUND_ROWS () method to get the data queried by the select statement. If the value of FOUND_ROWS () is 0, it enters the exception handling logic. 9 different Procedure_Name (parameters) of the stored procedure way to call the stored procedure in the stored procedure; Call Procedure_Name (parameter); and MYSQL stored procedure calls the stored procedure, you need to use Call pro_name (parameter).

Oracle calls the stored procedure to write the stored procedure name directly. 10 the way to throw an exception is different from the RAISE Exception_Name; see the 2.5 Mysql exception handling section in the remark. Thank you for reading! This is the end of the article on "stored procedures for comparing the differences between MySQL and Oracle & sample Analysis of Function". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.