Oracle technology _ 5 minutes will use stored procedure _ simple example of stored procedure (including loop, condition, add and modify query, parameter input, variable assignment, java call, etc.), oracle_5

Oracle technology _ 5 minutes will use stored procedure _ simple example of stored procedure (including loop, condition, add and modify query, parameter input, variable assignment, java call, etc.), oracle_5

Example business functions:

1. query the data in PROCEDURE_TEST_A table and PROCEDURE_TEST_A_SUB table based on the input type A_TYPE and display the main content.

2. query the PROCEDURE_TEST_A table and PROCEDURE_TEST_A_SUB table based on the input type A_TYPE, and insert the result into the PROCEDURE_TEST_ B table.

If the B _EMAIL field is null, the default value is used.

3. If the PROCEDURE_TEST_ B table is inserted, the ASUB_NUMBER field in the group statistics is updated or the PROCEDURE_TEST_C table is inserted.

Download the complete code

It contains most of the operations commonly used in the stored procedure, including loops, conditions, addition, modification, query, parameter input, and variable assignment. If not, let's just look at the example:

1. create the required table and create four tables for initialization data. The Code is as follows:

-- Data source TABLE PROCEDURE_TEST_ACREATE TABLE PROCEDURE_TEST_A (A_ID VARCHAR2 (255) not null, A_USER VARCHAR2 (255), A_EMAIL VARCHAR2 (255), A_TYPE VARCHAR2 (5 ), CONSTRAINT PROCEDURE_TEST_A primary key (A_ID); -- Partition TABLE PROCEDURE_TEST_A_SUB (ASUB_ID VARCHAR2 (255) not null, ASUB_NAME VARCHAR2 (255), ASUB_NUMBER NUMBER ), ASUB_COMMENT VARCHAR2 (2000), A_ID VARCHAR2 (255), CONSTRAINT encrypt primary key (ASUB_ID), CONSTRAINT PROCEDURE_TEST foreign key (A_ID) REFERENCES PROCEDURE_TEST_A (A_ID )); -- Insert the create table PROCEDURE_TEST_ B (B _ID VARCHAR2 (255) not null, B _USER VARCHAR2 (255), B _EMAIL VARCHAR2 (255), B _NAME VARCHAR2 (255 ), B _NUMBER NUMBER (2000), B _COMMENT VARCHAR2 (255), CONSTRAINT PROCEDURE_TEST_ B primary key (B _ID); -- Update the CREATE TABLE PROCEDURE_TEST_C (C_USER VARCHAR2 () NOT NULL after data integration, c_NUMBER NUMBER (), CONSTRAINT PROCEDURE_TEST_C primary key (C_USER); -- PROCEDURE_TEST_A initialize insert into values (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('aid00006 ', 'system ', '', 't00'); insert into PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('aid00001 ', 'popkidorc', 'popkidorc @ mail.com ', 't01'); insert into PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('aid00002 ', 'csdn', 'csdn @ mail.com ', 't01 '); insert into PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('aid00003 ', 'Pop', '', 't01 '); insert into PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, a_TYPE) VALUES ('aid00004 ', 'kid', 'Kid @ mail.com', 't01'); insert into PROCEDURE_TEST_A (A_ID, A_USER, A_EMAIL, A_TYPE) VALUES ('aid00005 ', 'orc', 'orc @ mail.com', 't01 '); -- initialize insert into PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('asubid0000001 ', 'oralce _ blog', 9.90, 'oralce blog', 'aid00000000001'); insert into PROCEDURE_TEST_A_SUB) VALUES ('asubid00002 ',' SQL _ blog', 1.50, 'SQL blog', 'aid00001'); INSERT INTO PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('asubid00003 ', 'SWIFT _ blog', 1.00, 'SWIFT blog', 'aid00001'); insert into PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('asubid00004 ', 'game _ blog', 6.00, 'game blog', 'aid00003'); insert into PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('asubid00005 ', 'Sport _ blog', 5.55, 'Sport blog', 'aid00003'); insert into PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('asubid00006 ', 'Kid _ blog', 99.00, 'Kid blog ', 'aid00004'); insert into PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('asubid00007 ', 'zero _ blog', 0.00, 'zero blog', 'aid00005'); insert into PROCEDURE_TEST_A_SUB (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID) VALUES ('asubid1_8', 'large _ blog', 100000.00, 'large blog', 'aid00005 '); -- PROCEDURE_TEST_C initialize insert into PROCEDURE_TEST_C (C_USER, C_NUMBER) VALUES ('popkidorc', 9.90 );

After execution, the table structure and data results

2. The code for creating a stored procedure is as follows:

Create or replace procedure PROCEDURE_TEST (I _A_TYPE VARCHAR2, I _DEFAULT_MAIL VARCHAR2) IS -- declare the variable start -- L_DEFAULT_MAIL VARCHAR2 (255): = 'default @ mail.com '; -- declare the variable and assign values; if the second input parameter is not blank, this value is taken as the default value of the MAIL field L_TEST_ B _COUNT INTEGER; -- the number of affected records after update in Table B L_TEST_C_COUNT INTEGER; -- after the C table IS updated, the number of affected records CURSOR A_CURSOR is select. A _ USER, S. ASUB_NUMBER FROM PROCEDURE_TEST_A_SUB s left join PROCEDURE_TEST_A a on. A _ ID = S. A _ ID WHER E. A _ TYPE = I _A_TYPE; -- cursor object, used to store the result set -- declare the variable end -- BEGIN -- cyclically display the data start -- FOR A_C IN A_CURSOR LOOP DBMS_OUTPUT.PUT_LINE ('= LOOP PROCEDURE_TEST_A =' | A_C.A_USER | '=' | A_C.ASUB_NUMBER ); end loop; -- here we use the for in loop, while loop is also more commonly used, you can check-LOOP display of the data IN Table A end -- Query A, A_SUB table, INSERT the start -- insert into PROCEDURE_TEST_ B (B _ID, B _USER, B _EMAIL, B _NAME, B _NUMBER, B _COMMENT) SELECT SYS_GUID (),. A _ USER, DE CODE (. A _ EMAIL, NULL, DECODE (I _DEFAULT_MAIL, NULL, L_DEFAULT_MAIL, I _DEFAULT_MAIL),. A _ EMAIL), S. ASUB_NAME, S. ASUB_NUMBER, S. ASUB_COMMENT FROM PROCEDURE_TEST_A_SUB s left join PROCEDURE_TEST_A a on. A _ ID = S. A _ id where. A _ TYPE = I _A_TYPE; L_TEST_ B _COUNT: = SQL % ROWCOUNT; DBMS_OUTPUT.PUT_LINE ('= INSERT PROCEDURE_TEST_ B ROWCOUNT =' | L_TEST_ B _COUNT); -- number of affected records, SQL % ROWCOUNT -- query tables A and A_SUB and insert Table B end -- Update the C table start -- IF L_TEST_ B _COUNT> 0 THEN -- Update the C table MERGE INTO PROCEDURE_TEST_C USING (SELECT. A _ USER, SUM (S. ASUB_NUMBER) AS SUM_NUMBER FROM PROCEDURE_TEST_A_SUB s left join PROCEDURE_TEST_A a on. A _ ID = S. A _ id group by. A _ USER) a on (. A _ USER = C. C _ USER) when matched then update set c. C _ NUMBER =. SUM_NUMBER when not matched then insert values (. A _ USER,. SUM_NUMBER); L_TEST_C_CO UNT: = SQL % ROWCOUNT; DBMS_OUTPUT.PUT_LINE ('= update or insert PROCEDURE_TEST_C ROWCOUNT =' | L_TEST_C_COUNT); -- number of affected records, SQL % ROWCOUNT END IF; -- Update the end of table C -- submit the transaction start -- COMMIT; -- this is used with caution. It is best not to submit the transaction directly in the stored procedure, but to manually submit it using the server code. -- Commit transaction end -- EXCEPTION Processing start -- EXCEPTION -- I won't write many exceptions one by one. I usually write two. Others use OTHER's WHEN DUP_VAL_ON_INDEX THEN -- violation of the uniqueness restriction. Explain ('= DUP_VAL_ON_INDEX EXCEPTION ='); RAISE; WHEN NO_DATA_FOUND THEN -- data not found in SELECT DBMS_OUTPUT.PUT_LINE ('= NO_DATA_FOUND EXCEPTION = '); RAISE; -- terminate the process when others then DBMS_OUTPUT.PUT_LINE ('= others exception ='); RAISE; -- EXCEPTION Handling end -- END PROCEDURE_TEST;

3. Execute the stored procedure directly. The Code is as follows:

BEGIN -- execute the Stored Procedure PROCEDURE_TEST ('t01', 'test @ mail.com '); END;

The code for calling java through thin is as follows (key code ):

Class. forName ("database driver package"); Connection conn = DriverManager. getConnection ("connection string", "User Name", "password"); CallableStatement proc = null; proc = conn. prepareCall ("{call PROCEDURE_TEST (?,?) } "); Proc. setString (1," T01 "); proc. setString (2," test@mail.com "using proc.exe cute ();

The execution result is as follows:

Two updated tables:

Click to enter the ooppookid blog