A database self-defined exception method based on a variable mechanism, equipment and medium

Abstract

This invention discloses a database custom exception method, device, and medium based on a variable mechanism, belonging to the field of database technology. It addresses the technical problems encountered during the migration of existing PostgreSQL databases, including the massive amount of data modification required for functional migration, the difficulty in achieving complete migration, high kernel intrusion, high maintenance costs, and difficulties in multi-version adaptation. The method includes: assigning values ​​to the variable structure of the PostgreSQL database based on error codes and error messages to construct custom exception type storage variables based on the PostgreSQL database; performing syntax binding on the error codes in the custom exception type storage variables; controlling the exception throwing of the custom exception type storage variables using variable subscripts or variable functions; and capturing and matching exception variables for RAISE exception variables to obtain the currently captured exception variable after a successful match.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a database custom exception method, device, and medium based on a variable mechanism. Background Technology

[0002] The PostgreSQL database has the following limitations: it uses SQLSTATE to represent exceptions; it does not support custom exceptions such as var1exception; it does not support PRAGMA binding (pragma exception_init(var1, -20001); it does not support catching exceptions by name (e.g., when var1 then); it does not support the raise_application_error, SQLCODE / SQLERRM functions; and it does not support raise var1 to throw a user-defined exception. The Oracle database includes the following features: it supports binding exception names with error codes; it supports exception name catching; it supports SQLCODE / SQLERRM; and it supports raise var1 to throw custom exceptions.

[0003] In other words, there are significant semantic differences between the two. Migrating Oracle's exception syntax and functionality to PostgreSQL would require a huge amount of modification and would be difficult to complete. A solution that is fully compatible is needed. In addition, custom exceptions allow the program to throw errors when there are logical errors, which is beneficial for program debugging.

[0004] However, in existing technologies, solutions for achieving PostgreSQL compatibility with Oracle custom exceptions mainly fall into three categories: The first type involves extending the database kernel exception stack structure by adding exception fields or exception lists to achieve exception storage and matching. Specifically, for each exception declaration, a kernel structure is generated and added to the global linked list of the exception stack structure. When encountering the PRAGMA exception_init syntax, the global linked list of exceptions is searched by exception name, the error code is assigned, and SQLSATTE is bound. When an exception is thrown, the global linked list is searched by exception name / error code to find the corresponding error code errcode and error message errormsg and throw it. Throughout the compilation and runtime phases, the global linked list needs to be traversed and matched by name. Exceptions are independent of the original variable structure scope and require additional storage and processing logic, independent of the internal variable namespace.

[0005] The second type is to establish a kernel-level error code mapping table to achieve bidirectional conversion between Oracle error codes and PostgreSQL SQLSTATE. This solution will convert the exception triggering syntax into the original PG SQLSTATE syntax during the compilation stage.

[0006] The third approach involves adding a separate exception domain to isolate exceptions from the native exception stack. This approach requires maintaining an independent exception domain structure, resulting in significant kernel modifications and increased kernel burden.

[0007] In other words, the existing solutions mentioned above all have common defects: they either rely on exception stack extension, independent exception domains or global mapping tables, or they require shared execution context, which is highly intrusive to the kernel, has high maintenance costs, is difficult to adapt to the needs of multiple version iterations of PostgreSQL, and there are problems with the consistency between exception names and ordinary variables. Summary of the Invention

[0008] This application provides a database custom exception method, device, and medium based on a variable mechanism to solve the following technical problems: In the migration of existing database PostgreSQL, the amount of data modification required to achieve functional migration is extremely large and difficult to migrate completely; in addition, it is highly intrusive to the kernel, has high maintenance costs, is difficult to adapt to multiple versions, and generally has poor support for exceptions with the same name in different blocks.

[0009] The embodiments of this application adopt the following technical solutions: On one hand, this application provides a database custom exception method based on a variable mechanism, including: assigning values ​​to the variable structure of a PostgreSQL database based on error codes and error messages according to the exception data type, constructing a custom exception type storage variable based on the PostgreSQL database; wherein the error code is an error code returned by the Oracle database; performing syntax binding on the error codes in the custom exception type storage variable; using a pre-constructed active exception throwing statement, performing exception throwing control on the custom exception type storage variable related to variable subscripts or variable functions to obtain a thrown RAISE exception variable; wherein the RAISE exception variable includes: an exception code and an exception message; performing exception variable capture matching on the RAISE exception variable according to the exception variable address of the RAISE exception variable, obtaining a currently captured exception variable after successful matching; and performing a compatible read on the currently captured exception variable to make the PostgreSQL database compatible with the Oracle database's custom exception mechanism.

[0010] This application's embodiments utilize custom exception handling logic, fully reusing the database's original variable lifecycle management, scope rules, and memory reclamation mechanisms. This eliminates the need for deep modifications to the kernel exception stack and compilation / execution process, significantly reducing the intrusion into the database kernel. Furthermore, it avoids the need for re-adaptation or reconstruction for each major version, effectively solving the version locking problem caused by kernel interface changes in traditional solutions and significantly reducing long-term maintenance costs. It also enables application developers migrating from Oracle to PostgreSQL to use custom exceptions in a more natural and intuitive way, significantly reducing learning costs and migration resistance. Simultaneously, because exception variables are precisely located through variable addresses during both throwing and catching, global linked list traversal matching is avoided, improving exception handling efficiency and ensuring that exception handling logic in Oracle-migrated applications can run completely and correctly on PostgreSQL.

[0011] In one feasible implementation, before assigning values ​​to the variable structure of the PostgreSQL database based on error codes and error information according to the exception data type, the method further includes: creating a new exception data type based on the PostgreSQL database; wherein the exception data type is an exception type used to implement custom exception compatibility with the Oracle database, and the exception data type is implemented using the int4 type of the original system in the PostgreSQL database.

[0012] In one feasible implementation, based on the abnormal data type, the variable structure of the PostgreSQL database is assigned values ​​based on error codes and error messages to construct a custom abnormal type storage variable based on the PostgreSQL database. Specifically, this includes: determining the abnormal data type using the basic data type table of the PostgreSQL database; constructing an abnormal type storage variable based on the abnormal data type; wherein the data structure of the abnormal type storage variable is a PLpgSQL_var structure; storing the abnormal type storage variable in the variable space of the existing variables in the PostgreSQL database; and initializing the error codes and error messages in the abnormal type storage variable to generate the custom abnormal type storage variable; wherein the error messages are used to override the error message description thrown by the server when an abnormality is triggered.

[0013] In one feasible implementation, the error codes in the custom exception type storage variable are syntactically bound, specifically including: finding and determining the custom exception type storage variable through the syntax compilation instructions of the Oracle database; wherein, the syntax compilation instructions of the Oracle database are used to bind the custom exception name with the specified Oracle error code; the syntax binding process of the error code and exception name in the custom exception type storage variable is used to obtain the custom exception type storage variable for capture and processing by exception naming method.

[0014] In one feasible implementation, a pre-constructed proactive exception raising statement is used to control the exception raising of the custom exception type storage variable by adjusting the variable index or variable function, resulting in a raised RAISE exception variable. Specifically, this includes: searching for the custom exception type storage variable stored in the variable space according to the original variable lookup rules of the PostgreSQL database, and constructing the proactive exception raising statement for exception raising; wherein the statement structure of the proactive exception raising statement is a PLpgSQL_stmt_raise structure statement; adding the exception variable index to the proactive exception raising statement; wherein the exception variable index is the storage index of the variable space; searching and determining the custom exception type storage variable in the variable space based on the exception variable index of the proactive exception raising statement, and extracting the error code and error information from the custom exception type storage variable; and using the original error reporting standard macro ereport in the PostgreSQL database to control the exception variable raising by adjusting the error code and error information obtained based on the exception variable index, resulting in a raised RAISE exception variable.

[0015] In one feasible implementation, the custom exception type storage variable is subject to exception throwing control via a variable function. Specifically, this includes: using an Oracle database variable function to call the original report error standard macro ereport, and controlling the exception code and exception message in the RAISE exception variable to throw the exception variable, thereby obtaining the thrown RAISE exception variable.

[0016] In one feasible implementation, based on the exception variable address of the RAISE exception variable, exception variable capture matching is performed on the RAISE exception variable to obtain the current exception capture variable after a successful match. Specifically, this includes: finding and determining the current exception variable based on the exception name of the RAISE exception variable; recording the exception variable index of the current exception variable in the PLpgSQL_condition structure within the matching condition; obtaining the current exception variable address corresponding to the RAISE exception variable based on the exception variable index recorded during the compilation phase; obtaining the real-time exception variable address recorded in the exception structure of the RAISE exception variable after it is thrown in real time; if the found current exception variable... If the address of the current exception variable is the same as the address of the obtained real-time exception variable, then the RAISE exception variable is a successfully captured and matched result. If the address of the current exception variable is different from the address of the obtained real-time exception variable, then the error code associated with the current exception variable address is compared with the error code associated with the real-time exception variable address. If the error code comparison result is consistent, then the RAISE exception variable is a successfully captured and matched result. Based on the successfully captured and matched result, the corresponding RAISE exception variable is determined as the currently captured exception variable after successful matching. The currently captured exception variable includes the current exception number and exception information.

[0017] In one feasible implementation, the current exception capture variable is read in a compatible manner to make the PostgreSQL database compatible with the custom exception mechanism of the Oracle database. Specifically, this includes: adding a global variable to save the current exception number and exception information in the current exception capture variable; wherein, by adding a SQLCODE compatible with the Oracle database, the saved current exception number is read and returned; and by adding a SQLERRM compatible with the Oracle database, the saved exception information is read and returned.

[0018] Secondly, embodiments of this application also provide a database custom exception device based on a variable mechanism, the device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor to enable the at least one processor to execute a database custom exception method based on a variable mechanism as described in any of the above embodiments.

[0019] Thirdly, embodiments of this application also provide a non-volatile computer storage medium, which is a non-volatile computer-readable storage medium storing at least one program. Each program includes instructions, which, when executed by a terminal, cause the terminal to execute a database custom exception method based on a variable mechanism as described in any of the above embodiments.

[0020] This application provides a database custom exception method, device, and medium based on a variable mechanism. Compared with the prior art, the embodiments of this application have the following beneficial technical effects: 1. By utilizing custom exception handling logic, the database's original variable lifecycle management, scope rules, and memory reclamation mechanism can be completely reused without requiring deep modifications to the kernel exception stack or compilation and execution process, greatly reducing the degree of intrusion into the database kernel.

[0021] 2. When the database version iterates, as long as the variable mechanism remains compatible, the implementation logic of this solution can be smoothly migrated without the need for re-adaptation or reconstruction for each major version. This effectively solves the version locking problem caused by kernel interface changes in traditional solutions and significantly reduces long-term maintenance costs.

[0022] 3. By defining custom exceptions as variables of specific data types, the exception name itself exists as a variable identifier in the database namespace, sharing the same scope and visibility rules as ordinary variables, fields, functions, and other objects. This allows application developers migrating from Oracle to PostgreSQL to use custom exceptions in a more natural and intuitive way, significantly reducing the learning cost and migration resistance.

[0023] 4. Since exception variables are precisely located through their addresses during both throwing and catching, the traversal and matching of the global linked list is avoided, which improves the efficiency of exception handling. At the same time, it ensures that the exception handling logic in the Oracle migration application can run completely and correctly on PostgreSQL. Attached Figure Description

[0024] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart of a database custom exception method based on a variable mechanism is provided for embodiments of this application; Figure 2 This application provides an overall architecture diagram for database custom exception handling based on a variable mechanism. Figure 3 This is a schematic diagram of the structure of a database custom exception device based on a variable mechanism, provided in an embodiment of this application. Detailed Implementation

[0025] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0026] This application provides a database custom exception method based on a variable mechanism, such as... Figure 1 As shown, the database custom exception method based on the variable mechanism specifically includes steps S101-S105: S101. Based on the exception data type, perform value assignment processing on the variable structure of the PostgreSQL database according to the error code and error message, and construct a custom exception type storage variable based on the PostgreSQL database. The error code is the error code returned by the Oracle database.

[0027] Specifically, a new exception data type was first created based on the PostgreSQL database. This exception data type is designed to be compatible with custom exceptions in the Oracle database, and it is implemented using the original int4 type from the PostgreSQL database.

[0028] In one embodiment, such as Figure 2 As shown, in the custom exception type definition module, the first step is to create a new data type, exception, to represent the custom exception type. The IO function of this type can be implemented using the existing int4 type of the system. This module provides the basic type for the definition of custom exception variables.

[0029] Furthermore, the exception data types are determined using the basic data type tables of the PostgreSQL database. Based on these exception data types, an exception type storage variable is constructed. The data structure for this exception type storage variable is a PLpgSQL_var structure.

[0030] Furthermore, the exception type storage variable needs to be stored in the existing variable space of the PostgreSQL database, and the error code and error message in the exception type storage variable need to be initialized to generate a custom exception type storage variable. The error message is used to override the error message description thrown by the server when the exception is triggered.

[0031] In one embodiment, such as Figure 2 As shown, in the exception variable parsing module, to support compatibility with Oracle's (Relational Database Management System) PRAGMA exception_init binding error number, the PLpgSQL_var structure (an internal C language structure pointer type used by the PL / pgSQL procedural language compiler in the PostgreSQL database kernel to represent ordinary scalar variables such as integer and text, and a concrete implementation of the PLpgSQL_datum base class) is modified to add Oracle database error information and error codes, for example: typedef struct PLpgSQL_var { … / pg attributes / intoraerrcode; / Oracle error codes are used to directly throw or catch Oracle error codes. / charerrormsg; / Error messages, used to override the error message description thrown by the server when an exception is triggered. / } PLpgSQL_var; The syntax for defining exceptions is as follows: my_exec exception.

[0032] PLpgSQL retrieves the exception type from the system table of types, constructs a variable of type PLpgSQL_var, and stores it in the variable space of the original variable. It assigns initial values ​​to the error code (oraerrcode) and the error message (errormsg, short for "error message," which refers to the text information returned by a program or system when an error occurs, describing the cause and status of the failure). Generally, the error code (oraerrcode) is assigned a value of 1, and the error message (errormsg) is assigned the value "User-Defined Exception." In essence, it constructs a variable of a custom exception type to store it.

[0033] S102. Perform syntax binding on the error codes in the custom exception type storage variable.

[0034] Specifically, the custom exception type storage variable is first located and determined using Oracle database syntax compilation directives. These directives are used to bind the custom exception name to a specified Oracle error code.

[0035] Furthermore, it is necessary to bind the error codes in the custom exception type storage variables with the syntax of the exception names to obtain custom exception type storage variables used for capture and processing through exception naming.

[0036] In one embodiment, such as Figure 2 As shown, in the PRAGMA EXCEPTION_INIT exception binding module, for the PRAGMA EXCEPTION_INIT syntax (a compiler directive in Oracle PL / SQL used to bind a custom exception name to a specific Oracle error code, allowing unnamed internal exceptions to be captured and handled by name), for example: my_exc, -20001; the syntax support can be used to find the custom variable storage structure PLpgSQL_var of my_exec and assign its Oracle data error code (oraerrcode) to -20001. During this syntax binding phase, the original PG variable lookup mechanism is executed; the my_exec exception must first go through the exception definition before it can be used.

[0037] S103. By using a pre-built active exception throwing statement, the custom exception type storage variable is subjected to exception throwing control via variable index or variable function to obtain the thrown RAISE exception variable. The RAISE exception variable includes: exception code and exception message.

[0038] Specifically, based on the original variable lookup rules of the PostgreSQL database, the custom exception type storage variables stored in the variable space are first searched and processed, and an active exception raising statement for exception throwing is constructed. The statement structure of the active exception raising statement is a PLpgSQL_stmt_raise structure statement.

[0039] Furthermore, the exception variable index is added to the active exception throwing statement. Here, the exception variable index is the storage index in the variable space. Then, based on the exception variable index in the active exception throwing statement, the custom exception type storage variable in the variable space is located and determined, and the error code and error information in the custom exception type storage variable are extracted.

[0040] In one embodiment, such as Figure 2 As shown, in the exception throwing module, for exception throwing in method 1, during the compilation phase of the RAISE exception variable: according to the original PG variable lookup rules, after searching for the exception variable in the variable space, the PLpgSQL_stmt_raise (active exception throwing) statement for exception throwing is constructed. This structure reuses the original PG RAISE statement structure, but adds a dno field to record the index of the exception variable (exception variable index). This index is actually the index of the variable stored in the variable space. The address of the variable can be quickly obtained through the exception variable index, thereby retrieving the exception definition structure PLpgSQL_var.

[0041] Furthermore, the raw report error standard macro ereport in the PostgreSQL database is called to control the throwing of exception variables based on the error code and error information obtained from the exception variable index, resulting in the thrown RAISE exception variable.

[0042] In one embodiment, such as Figure 2 As shown, in the exception throwing module, for exception throwing in method 1, during the execution phase of the RAISE exception variable: the PLpgSQL_var structure of the exception variable can be found according to the variable index dno of the PLpgSQL_stmt_raise structure, the error code oraerrcode and the error message errormsg can be retrieved, and the original ereport (reporting error standard macro) can be called to throw the exception code and exception message. In order to accurately match, when ereport is executed, the address of the exception variable is recorded in the excep_datum of the ErrorData structure of the system real-time exception, so that it can be compared by address during matching, and finally the RAISE exception variable after being thrown can be obtained.

[0043] As a possible implementation, the above PLpgSQL_stmt_raise structure can be as follows: typedef struct PLpgSQL_stmt_raise { PLpgSQL_stmt_type cmd_type cmd_type; … int dno; / Index of custom exception variable / } PLpgSQL_stmt_raise.

[0044] As a possible implementation, the ErrorData structure described above can be as follows: typedef struct ErrorData { / pg attributes / … void excep_datum; / The address of the currently thrown custom exception variable / ErrorData.

[0045] Furthermore, the Oracle database variable function can be used to call the original report error standard macro ereport, which controls the throwing of exception codes and exception messages in the RAISE exception variable to obtain the thrown RAISE exception variable.

[0046] That is, such as Figure 2 As shown, the exception throwing method in Method 2 can also be used: RAISE_APPLICATION_ERROR (a built-in procedure in Oracle database PL / SQL, used to actively throw a custom business exception on the server side and send the error code and information back to the client). Combined with the newly added variable function support, the exception code and exception message can be directly thrown by calling ereport directly in the variable function.

[0047] S104. Based on the address of the RAISE exception variable, perform exception variable capture matching on the RAISE exception variable to obtain the current exception capture variable after successful matching.

[0048] Specifically, it is necessary to first locate and determine the current exception variable based on the exception name of the RAISE exception variable, and then record the exception variable index of the current exception variable in the PLpgSQL_condition structure of the matching condition.

[0049] Furthermore, based on the exception variable index recorded during the compilation phase, the address of the current exception variable corresponding to the RAISE exception variable is obtained. Then, the address of the real-time exception variable recorded in the exception structure of the RAISE exception variable after it is thrown in real time is obtained. If the address of the current exception variable found is the same as the address of the real-time exception variable obtained, then the RAISE exception variable is considered to have been successfully captured.

[0050] If the current exception variable address found is different from the real-time exception variable address obtained, then the error code associated with the current exception variable address is compared with the error code associated with the real-time exception variable address. If the error code comparison result is consistent, then the RAISE exception variable is considered a successful capture match.

[0051] In one embodiment, such as Figure 2 As shown, for the exception handling module, during the compilation phase: the current exception variable needs to be found based on the exception name, and its index dno is recorded in the PLpgSQL_condition structure in the matching condition. During the execution phase: the address of the exception variable is obtained based on the index dno recorded during the compilation phase, and compared with the address excep_datum of the exception variable recorded in the ErrorData structure thrown by the system in real time. If the two are equal, a match is made; otherwise, the error codes are compared, and if they are equal, a match is made. When a match is finally successful, the current exception number and exception information are stored in global variables for use by the sqlcode and sqlerrm built-in functions; and the RAISE exception variable is identified as the successfully matched result.

[0052] Furthermore, based on the successful capture result, the corresponding RAISE exception variable is determined as the current exception capture variable after the successful match. The current exception capture variable includes the current exception number and exception information.

[0053] As a feasible implementation, the exception matching structure PLpgSQL_condition can be modified by adding the index dno attribute, for example: typedef struct PLpgSQL_condition { / pg attributes / … intdno; / Abnormal variable subscript / / pg attributes / struct PLpgSQL_condition next; } PLpgSQL_condition.

[0054] S105. Perform a compatibility read on the current exception capture variables to make the PostgreSQL database compatible with Oracle database's custom exception mechanism.

[0055] Specifically, it is necessary to construct new global variables in the database to store the current exception number and exception information from the current exception capture variables. Specifically, by adding a SQLCODE compatible with Oracle databases, the saved current exception number is read and returned. Similarly, by adding a SQLERRM compatible with Oracle databases, the saved exception information is read and returned.

[0056] In one embodiment, such as Figure 2 As shown, for SQLCODE / SQLERRM compatible functions (which are built-in error reporting mechanisms specific to Oracle PL / SQL, used to obtain the numerical error code and text description information of the current exception, respectively, and are only valid in the EXCEPTION processing block), when an exception is caught each time, a global variable is used to store the exception number and exception information that were triggered. SQLCODE returns the stored exception number, and SQLERRM returns the stored exception information.

[0057] As a possible implementation method, such as Figure 2 As shown, this application is based on a variable mechanism, treating Oracle custom exceptions as special type variables. It mainly consists of six modules: 1. Exception variable type definition module, 2. Exception declaration parsing module, 3. PRAGMA exception binding module, 4. Exception throwing module, 5. Exception catching module, and 6. SQLCODE / SQLERRM compatible function module.

[0058] This document explains the inter-module linkage for the following basic usage of custom exceptions, for example: Declare Var1 exception; / Exception variable definition / PRAGMA exception_init(var1, -1476); / Binding error number / Begin Raise var1; / throw an exception / exception when var1 then / catching exceptions / / Operation 1 / When xxx1 then / Operation 2 / When xxx2 then / Operation 3 / End; / 1) Custom exception declaration or definition statement: Var1 exception This type of custom exception statement can be executed like a normal variable definition statement. It requires adding an exception type to the type system so that it automatically follows the variable definition process, builds the PLpgSQL_var structure, assigns an initial value, and adds it to the variable namespace.

[0059] 2) Custom exception binding statement: PRAGMA exception_init(var1, -1476) The main function is to associate the custom exception represented by var1 (variable) with -1476. The default exception number is 1. This syntax modifies the default exception number to facilitate matching with raise and when. While supporting the syntax, it also finds the variable structure PLpgSQL_var of var1 and modifies its error code oraerrcode (the error code returned by the Oracle database).

[0060] 3) Triggering an exception: Raise (actively throws an exception) var1 During the compilation phase: The variable var1 is located, and its index dno is recorded in the PLpgSQL_stmt_raise structure. PLpgSQL_stmt_raise is the statement structure constructed by the original system for each raise. We add the dno attribute to record the index of the exception variable. During the execution phase: During execution, the PLpgSQL_var structure is obtained based on the index dno. ereport is called to throw the corresponding error code and error message, and the address of the PLpgSQL_var variable of var1 is recorded in the real-time error message structure ErrorData. This facilitates address comparison during subsequent exception matching. The real-time error message structure ErrorData is the original system's structure for recording the database exceptions currently triggered.

[0061] 4) Exception handling: when var1 then / Operation 1 / During the compilation phase: The variable `var1` is located, and its index `dno` is recorded in the `PLpgSQL_condition` structure. The `PLpgSQL_condition` structure is a statement structure constructed by the original system for each `when xxx then` capture operation. We add the `dno` attribute to record the index of the exception variable. During the execution phase: The `PLpgSQL_var` structure is obtained based on the index `dno`. First, the address of the exception variable recorded in the `ErrorData` structure is compared with the address of the `PLpgSQL_var` to be matched. If they are equal, the match is successful. Otherwise, the error code `oraerrcode` recorded in `PLpgSQL_var` is compared with the error code recorded in the `ErrorData` structure. If they are equal, the match is successful. That is, address matching is prioritized, followed by error code comparison.

[0062] After a successful match, the current exception code and exception information are saved to a global variable for direct use by SQLCODE and SQLERRM.

[0063] 5) SQLCODE and SQLERRM compatible functions A new built-in function has been added. This function processes and returns the current exception code and exception information stored in a global variable.

[0064] 6) raise_application_error It is relatively independent; a built-in function can be constructed, and the ereport function can be directly called to throw the error code and error message.

[0065] 7) Exception variable scope module (optional additional module) This module can reuse the existing variable scope mechanism to achieve unified storage, retrieval, and destruction of custom exception variables and ordinary variables. Specifically, the scope management for exception variables includes: This solution requires leveraging PostgreSQL's native namespaces and PL / pgSQL variable management mechanisms to implement scope isolation and name resolution for custom exception variables. It avoids adding independent lifecycle management logic; the creation, access, and destruction of exception variables reuse existing variable management processes, ensuring Oracle PL / SQL exception semantic compatibility while minimizing intrusion into the PostgreSQL kernel.

[0066] During the custom exception definition phase, for each custom exception declared, the system creates a corresponding PLpgSQL_var structure, adds it to the variable array of the current compilation unit, and registers the exception name in the current PL / pgSQL namespace. Subsequently, in RAISE, exception handler, or exception reference scenarios, the existing variable name lookup mechanism can be directly reused to complete exception resolution.

[0067] Each statement block establishes an independent namespace hierarchy through a label. When resolving exception names, the search is first performed in the namespace corresponding to the current block; if not found, the search proceeds outwards along the namespace chain layer by layer until a match is found or the outermost scope is reached.

[0068] This scope management mechanism has the following characteristics: 1. Supports block-level scope isolation Exceptions defined in different statement blocks are independent of each other, and inner exceptions do not affect the definition of outer exceptions.

[0069] 2. Supports matching based on the nearest possible match for duplicate names. Exceptions with the same name can be defined in inner and outer scopes. Name resolution follows the principle of nearest scope priority, giving priority to exception definitions in the current block or the nearest outer block.

[0070] 3. Unified management of abnormal and normal variables Custom exceptions share the same namespace and name resolution mechanism as regular PL / pgSQL variables, which can detect conflicts between variable names and exception names in the same scope during the definition phase and avoid name ambiguity.

[0071] 4. Reuse native lifecycle management mechanisms Exception variables are created and released uniformly along with the variable space of the function or statement block to which they belong, without the need to maintain the generation cycle and destruction process of exception objects separately.

[0072] The above design enables custom exception definitions, scope isolation, name masking, and name resolution capabilities that conform to Oracle PL / SQL semantics, while minimizing kernel intrusion.

[0073] In addition, embodiments of this application also provide a database-customized exception device based on a variable mechanism, such as... Figure 3 As shown, the database custom exception device 300 based on the variable mechanism specifically includes: At least one processor 301; and a memory 302 communicatively connected to the at least one processor 301; wherein the memory 302 stores instructions executable by the at least one processor 301 to enable the at least one processor 301 to execute: Based on the exception data type, the variable structure of the PostgreSQL database is assigned values ​​based on error codes and error messages to construct a custom exception type storage variable based on the PostgreSQL database; where the error codes are the error codes returned by the Oracle database. Perform syntax binding on error codes in custom exception type storage variables; By using pre-built active exception throwing statements, the custom exception type storage variable is subjected to exception throwing control related to variable subscripts or variable functions to obtain the thrown RAISE exception variable; where RAISE exception variable includes: exception code and exception message; Based on the address of the RAISE exception variable, perform exception variable capture matching on the RAISE exception variable to obtain the current exception capture variable after a successful match; Perform a compatibility read on the current exception handling variables to make the PostgreSQL database compatible with Oracle database's custom exception mechanism.

[0074] This application utilizes custom exception handling logic to fully reuse the database's existing variable lifecycle management, scope rules, and memory reclamation mechanisms. It eliminates the need for deep modifications to the kernel exception stack and compilation / execution process, significantly reducing the intrusion into the database kernel. Furthermore, it avoids the need for re-adaptation or refactoring for each major version, effectively resolving version locking issues caused by kernel interface changes in traditional solutions and significantly reducing long-term maintenance costs. It also enables application developers migrating from Oracle to PostgreSQL to use custom exceptions in a more natural and intuitive way, significantly reducing learning costs and migration resistance. Simultaneously, because exception variables are precisely located through variable addresses during both throwing and catching, global linked list traversal matching is avoided, improving exception handling efficiency and ensuring that exception handling logic in Oracle-migrated applications can run completely and correctly on PostgreSQL.

[0075] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0076] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0077] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0078] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0079] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0080] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0081] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0082] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0083] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0084] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0085] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of this specification.

Claims

1. A database custom exception method based on a variable mechanism, characterized in that, The method includes: Based on the exception data type, the variable structure of the PostgreSQL database is assigned values ​​based on error codes and error messages to construct a custom exception type storage variable based on the PostgreSQL database; wherein, the error code is the error code returned by the Oracle database; Perform syntax binding on the error codes in the custom exception type storage variable; By using a pre-built active exception throwing statement, the custom exception type storage variable is subjected to exception throwing control related to variable index or variable function, resulting in the thrown RAISE exception variable; wherein, the RAISE exception variable includes: exception code and exception message; Based on the address of the RAISE exception variable, perform exception variable capture matching on the RAISE exception variable to obtain the current exception capture variable after successful matching; Perform a compatibility read on the current exception capture variable to make the PostgreSQL database compatible with Oracle database's custom exception mechanism.

2. The database custom exception method based on a variable mechanism according to claim 1, characterized in that, Before assigning values ​​to the variable structure of the PostgreSQL database based on error codes and error messages according to the exception data type, the method further includes: A new exception data type is created based on the PostgreSQL database; wherein the exception data type is an exception type used to achieve custom exception compatibility with the Oracle database, and the exception data type is implemented using the int4 type of the original system in the PostgreSQL database.

3. The database custom exception method based on a variable mechanism according to claim 1, characterized in that, Based on the exception data type, the variable structure of the PostgreSQL database is assigned values ​​based on error codes and error messages to construct a custom exception type storage variable based on the PostgreSQL database, specifically including: The abnormal data type is determined by searching the basic data type table of the PostgreSQL database; and an abnormal type storage variable is constructed based on the abnormal data type; wherein the data structure of the abnormal type storage variable is a PLpgSQL_var structure; The exception type storage variable is stored in the variable space of the original variable in the PostgreSQL database, and the error code and error information in the exception type storage variable are initialized and assigned values ​​to generate the custom exception type storage variable; wherein, the error information is used to override the error information description thrown by the server when the exception is triggered.

4. The database custom exception method based on a variable mechanism according to claim 1, characterized in that, Syntax binding is performed on the error codes in the custom exception type storage variable, specifically including: The custom exception type storage variable is located and determined by using the syntax compilation instructions of the Oracle database; wherein, the syntax compilation instructions of the Oracle database are used to bind the custom exception name with the specified Oracle error code. The error codes in the custom exception type storage variable are syntactically bound to the exception names to obtain custom exception type storage variables used for exception capture and processing via exception naming.

5. The database custom exception method based on a variable mechanism according to claim 1, characterized in that, By using pre-built active exception throwing statements, the custom exception type storage variable is subjected to exception throwing control related to variable subscripts or variable functions to obtain the thrown RAISE exception variable, specifically including: According to the original variable lookup rules of the PostgreSQL database, the custom exception type storage variable stored in the variable space is searched and processed, and the active exception raising statement for exception raising is constructed; wherein, the statement structure of the active exception raising statement is a PLpgSQL_stmt_raise structure statement. Add the exception variable index to the active exception throwing statement; wherein, the exception variable index is the storage index of the variable space; Based on the exception variable index of the active exception throwing statement, locate and determine the custom exception type storage variable in the variable space, and extract the error code and error information from the custom exception type storage variable. By calling the raw report error standard macro ereport in the PostgreSQL database, the error code obtained based on the error variable index and the error information are used to control the throwing of related exception variables, resulting in the thrown RAISE exception variable.

6. The database custom exception method based on a variable mechanism according to claim 5, characterized in that, The custom exception type storage variable is subject to exception throwing control for relevant variable functions, specifically including: By using Oracle database variable functions, the original report error standard macro ereport is called to control the throwing of exception codes and exception messages in the RAISE exception variable, so as to obtain the thrown RAISE exception variable.

7. The database custom exception method based on a variable mechanism according to claim 1, characterized in that, Based on the address of the RAISE exception variable, perform exception variable capture matching on the RAISE exception variable to obtain the current exception capture variable after a successful match, specifically including: Based on the exception name of the RAISE exception variable, find and determine the current exception variable; and record the exception variable index of the current exception variable in the PLpgSQL_condition structure in the matching condition; Based on the index of the exception variable recorded during the compilation phase, obtain the address of the current exception variable corresponding to the RAISE exception variable; Get the address of the real-time exception variable recorded in the exception structure of the RAISE exception variable after it is thrown in real time; If the current abnormal variable address found is the same as the real-time abnormal variable address obtained, then the RAISE abnormal variable is a successful capture match result. If the current abnormal variable address found is different from the real-time abnormal variable address obtained, then the error code associated with the current abnormal variable address is compared with the error code associated with the real-time abnormal variable address. If the error code comparison result is a match, then the RAISE exception variable is a captured match result; Based on the successful capture result, the corresponding RAISE exception variable is determined as the current exception capture variable after successful matching; wherein, the current exception capture variable includes the current exception number and exception information.

8. A database custom exception method based on a variable mechanism according to claim 1, characterized in that, Perform compatible readings of the currently captured exception variables to make the PostgreSQL database compatible with Oracle database's custom exception mechanism, specifically including: By adding a new global variable, the current exception number and exception information in the current exception capture variable can be saved; Specifically, the system adds a new SQLCODE function compatible with Oracle databases to read and return the saved current exception number; and adds a new SQLERRM function compatible with Oracle databases to read and return the saved exception information.

9. A database-defined exception device based on a variable mechanism, characterized in that, The device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor to enable the at least one processor to execute a database custom exception method based on a variable mechanism according to any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The storage medium is a non-volatile computer-readable storage medium that stores at least one program, each program including instructions that, when executed by a terminal, cause the terminal to perform a database custom exception method based on a variable mechanism according to any one of claims 1-8.

Images