Database Technology _orcale Technology (0002) _5 minutes using stored procedure _ stored Procedure instance

Basic technology:

Sample business functions:

1. Query the data in the procedure_test_a table,procedure_test_a_sub table According to the incoming type a_type union. and displays the main content.

2. Query the procedure_test_a table,procedure_test_a_sub table According to the incoming type a_type union. and insert the result into the procedure_test_b table.

Here, if the b_email character is blank, the incoming default value is taken.

3. If the procedure_test_b table is inserted. The group statistics asub_number field is updated or inserted into the procedure_test_c table.

This includes most of the operations often used by stored procedures. Including the cycle, conditions, increase the search, the number of input, variable assignment, and so on, not much to say directly on the example:

Full code point I download

1. Create the required tables and initialize the data

Create 4 tables, where 3 tables require initial data, code such as the following:

--Data source Tables 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));--child table of the data source table PROCEDURE_TEST_A_SUBCR Eate TABLE procedure_test_a_sub (asub_id VARCHAR2 (255) not NULL, Asub_name VARCHAR2 (255), Asub_number number (1  8,2), Asub_comment VARCHAR2 (+), a_id VARCHAR2 (255), CONSTRAINT procedure_test_a_sub PRIMARY KEY (asub_id), CONSTRAINT procedure_test FOREIGN KEY (a_id) REFERENCES procedure_test_a (a_id));--Insert Table after data consolidation 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 (18,2), b_comment VARCHAR2 (+), CONSTRAINT procedure_test_b PRIMARY KEY (b_id));--Update table after data integration create TA BLE Procedure_test_c (C_user VARCHAR2 (255) not NULL, C_number number (18,2), CONSTRAINT procedure_test_c PRIMARY KE Y (C_user));--procedure_tEst_a Initialize INSERT into procedure_test_a (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 ', ' [email protected] ', ' T01 '); inse RT into Procedure_test_a (a_id, A_user, A_email, A_type) VALUES (' AID00002 ', ' csdn ', ' [email protected] ', ' T01 '); INS ERT into Procedure_test_a (a_id, A_user, A_email, A_type) VALUES (' AID00003 ', ' pops ', ' ', ' T01 '); INSERT into procedure_tes T_a (a_id, A_user, A_email, A_type) VALUES (' AID00004 ', ' Kid ', ' [email protected] ', ' T01 '); INSERT into procedure_tes T_a (a_id, A_user, A_email, A_type) VALUES (' AID00005 ', ' orc ', ' [email protected] ', ' T01 ');--procedure_test_a_ SUB Initialize INSERT into Procedure_test_a_sub (asub_id, Asub_name, Asub_number, Asub_comment, a_id) VALUES (' ASUBID00001 ', ' ora Lce_blog ', 9.90, ' Oralce blog ', ' AID00001 '); INSERT into Procedure_test_a_sub (asub_id, Asub_name, Asub_number, asub_ COMMENT, a_id) 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_na ME, Asub_number, Asub_comment, a_id) VALUES (' ASUBID00004 ', ' Game_blog ', 6.00, ' Game blog ', ' AID00003 '); INSERT into Procedur  E_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_comme NT, 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 (' ASUBID00008 ', ' Large_blog ', 100000.00, ' Large blog ', ' AID00005 ');-- Procedure_test_c initializing INSERT INTO ProcedurE_test_c (C_user, C_number) VALUES (' Popkidorc ', 9.90); 

After running. Table structure and data results

2. Create a stored procedure

Code such as the following, staring very specific, direct copy can be seen slowly:

CREATE OR REPLACE PROCEDURE procedure_test (I_a_type VARCHAR2, I_default_ma IL VARCHAR2) is--declares the variable start--l_default_mail VARCHAR2 (255): = ' [email protected] '; --Declare the variable. If the second input parameter is not empty, the value is taken as the default value of the Mail field L_test_b_count INTEGER; --b table update affects the number of records L_test_c_count INTEGER; --C table update affects record Count CURSOR A_cursor is SELECT a.a_user, s.asub_number from Procedure_test_a_sub S left JOIN procedu Re_test_a A on a.a_id = s.a_id WHERE a.a_type = I_a_type; --Cursor object. Used to store the result set--declaring variable End--begin--loops through the data in table A 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 is used for the in loop, while loop is also more often used, can go to check--loop shows the data end----Query A, a_sub table, and insert B table start--INSERT INTO Procedure_test_b (b                  _id, B_user, B_email, B_name, B_number, b_comment) SELECT sys_guid (), A.a_user, DECODE (A.a_email,              Null    DECODE (I_default_mail, NULL, L_default_mail, I      _default_mail), A.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.a_id = s.a_id WHERE a.a_type = I_a_type;  L_test_b_count: = Sql%rowcount; Dbms_output.                       Put_Line (' ===insert procedure_test_b rowcount=== ' | | L_test_b_count); --The number of records affected, Sql%rowcount--Query A, a_sub table. and insert B table end----update c table start--if L_test_b_count > 0 Then--first infer that if B table has changed to update C table MERGE into Procedure_test_c c USING (SELECT A.a_user, SUM (S.asub_number) as Sum_number from Procedure_test_a_sub S left JOIN PROCEDURE _test_a A on a.a_id = s.a_id GROUP by A.a_user) A in (A.a_user = C.c_user) when matched the N UPDATE SET c.c_number = A.sum_number when isn't matched then INSERT VALUES (a.a_useR, A.sum_number);    L_test_c_count: = Sql%rowcount; Dbms_output.                         Put_Line (' ===update OR INSERT procedure_test_c rowcount=== ' | | L_test_c_count); -The number of records affected.  Sql%rowcount END IF; --Update the C table end----COMMIT TRANSACTION start--commit;  --use caution here, rather than committing directly to the stored procedure, but using the server-side code to submit it manually. --Commit TRANSACTION End----Exception handling Start--exception--very many exceptions I don't write them all. Common to write two.    Other uses other's when dup_val_on_index then--violates the uniqueness limit. Dbms_output.    Put_Line (' ===dup_val_on_index exception=== ');  RAISE; When No_data_found then--select the data dbms_output was not found.    Put_Line (' ===no_data_found exception=== ');    RAISE; --Terminate the process when the OTHERS then Dbms_output.    Put_Line (' ===others exception=== ');    RAISE; --Exception handling end--end procedure_test;

3. Run the stored procedure

Run directly, code such as the following:

BEGIN  -Runs the stored procedure  procedure_test (' T01 ', ' [email protected] '); END;

Java is called through thin. Code such as the following (key code):

Class.forName ("Database driver package"); Connection conn =  drivermanager.getconnection ("Connection string", "username", "password"); callablestatement proc = Null;proc = Conn.preparecall ("{Call Procedure_test (?
,?
} ");p roc.setstring (1," T01 ");p roc.setstring (2," [email protected] ");p Roc.execute ();

Run the results for example with. The DBMS console prints:

Two updated tables:

Click to enter Ooppookid's blog

Database Technology _orcale Technology (0002) _5 minutes using stored procedure _ stored Procedure instance