Simple Example of DB2 Stored Procedure

 

When applying for the short message service, the customer needs to fill in some basic information and determine whether the user already exists in the Business System Based on the information. Because online services and business systems are physically isolated, and the customer information stored in online databases is incomplete, it is necessary to exchange data to the business system and determine in the business system.

 

The solution is to use stored procedures. I have learned about stored procedures before, but they are not used in projects. However, after some efforts, we finally completed the process and encountered some difficulties. This article will avoid unnecessary errors for children who are not familiar with the DB2 stored procedure.

 

 

Drop procedure "PLName" @ create procedure "PLName" (-- stored PROCEDURE name IN IN_ID BIGINT, -- the following are all input parameters IN IN_ENTNAME VARCHAR (200), IN IN_REGNO VARCHAR (50 ), IN IN_PASSWORD VARCHAR (20), IN IN_LEREP VARCHAR (300), IN IN_CERTYPE CHARACTER (1), IN IN_CERNO VARCHAR (50), IN IN_LINKMAN VARCHAR (50 ), IN IN_SEX CHARACTER (1), IN IN_MOBTEL VARCHAR (30), IN IN_REQDATE TIMESTAMP, IN IN_REMITEM VARCHAR (300), IN IN_STATE CHARACTER (1), IN IN_TIMESTAMP TIMESTAMP) BEGIN declare V_RESULT BIGINT; -- declare the variable delete from TableNameA where id = IN_ID; SET V_RESULT = NULL; -- assign a value to the variable -- check whether the user input information is valid select B. id INTO V_RESULT from TableNameB, TableNameC c where normal judgment condition if (V_RESULT is not null) then --- if valid, execute the following insert statement insert into TableNameA (ID, ENTNAME, REGNO, PASSWORD, LEREP, CERTYPE, CERNO, LINKMAN, SEX, MOBTEL, REQDATE, REMITEM, STATE, TIMESTAMP) VALUES (IN_ID, IN_ENTNAME, IN_REGNO, IN_PASSWORD, IN_LEREP, IN_CERTYPE, role, IN_LINKMAN, IN_SEX, IN_MOBTEL, IN_REQDATE, IN_REMITEM, IN_STATE, IN_TIMESTAMP); end if; commit; END @ function description:

 

When a stored procedure is called, some values (IN input parameters) are input, and then the database (select statement) is queried based on the input values. Operations (add, delete, and update) are performed based on the query results)

 

There are two ways to execute a written stored procedure:

 

1. Copy it to the DB2 client and run it directly.

 

Note:

 

 

 

Change to @. Many previous errors are related to it, such as: "This command is treated as an SQL

Statement, because it is not a valid command for the command line processor "it takes a long time to solve this problem, seriously affecting the mood

 

2. save the preceding statement as test. put the db2 file in any directory (such as the root directory of the D disk), input db2cmd in cmd, and then enter db2-td @-vf D: \ test. db2.

 

After execution, you can test whether the stored procedure is correctly written.

 

Write SQL directly:

 

Call PLName (stored procedure name) (IN_ID, IN_ENTNAME, IN_REGNO, IN_PASSWORD, IN_LEREP, IN_CERTYPE, IN_CERNO, IN_LINKMAN, IN_SEX, IN_MOBTEL, success, success, IN_STATE, and timeout values) the above is the stored procedure that I used today. It has very simple functions and complicated operations. If you have any questions, please feel free to contact us.

 

From those who want to stand up, who want to reach the Talents