Oracle stored procedures and Java calls

Let me talk about my requirements so that you can better understand my code. My requirements are: the stored procedure receives two parameters, userid and topicid, from the Java end ), in

Let me talk about my requirements so that you can better understand my code. My requirements are: the stored procedure receives two parameters, userid and topicid, from the Java end ), in

I found many bugs for the first time, so I paid a lot of attention. It took me three days to get rid of all the bugs. I will list all the points of attention, and there may be drops, please correct and discuss with each other.

First, the stored procedure code is successfully tested: (the code in it may not be useful to you. For reference, I will post it all for future reference)

The jar package uses ojdbc14.jar. You can use either class12.jar or ojdbc6.jar.

Let me talk about my requirements so that you can better understand my code. My requirements are: the stored procedure receives two parameters, userid and topicid, from the Java end ), in the stored procedure, the system cyclically queries the likes of the current user on the current topic. If a record exists, the record is set to 1. If no record exists, it is set to 0. Finally, a result set is returned, is the relational table corresponding to user => topic => record.

Now, let's get started with code and learning, so it's inevitable that your mood is fluctuating and nagging, and you want to describe it in the most straightforward way.

------------ Create a type in the database, corresponding to the object structure to be passed in on JAVA:
Create or replace type tp_arr3 as Object
(
Userid nvarchar2 (40), -- change from varchar2 to nvarchar2 to match the String type of Java.
Topicid nvarchar2 (40 ),
Record nvarchar2 (4)
)

--- Multiple test creation may result in the error "ORA-02303: cannot use type or table relevance to delete or replace a type", then you only need to create a new type name.
----------
Create or replace type tp_arr_tbl3 as table of tp_arr3
-------------- Create a package and create a cursor type to put the output parameters
Create or replace package testpkg
Type testcur is ref cursor;
End testpkg;
------ Create a stored procedure and define two parameters. One input parameter is an array of object types (this type should meet most of the complex requirements). The other output parameter is used to store the query value with a cursor.
Create or replace procedure findRecord (type_obj IN tp_arr_tbl3, result out testpkg. testcur)
As
T tp_arr3;
Sql2 varchar2 (500 );
Sql3 varchar2 (500 );
V_count varchar2 (4); -- temporary intermediate variable used to store the corresponding record
BEGIN
Sql2: = 'drop table tb_temp ';
Sql3: = 'create TABLE tb_temp (userid varchar2 (40), topicid varchar2 (40) primary key, record varchar2 (4 ))';
Execute immediate sql2;
Execute immediate sql3;

FOR I IN type_obj.first () .. type_obj.last ()
LOOP
T: = type_obj (I );
Select count (*) into v_count from scott. tb_praise_rel where userid = t. userid and topicid = t. topicid;
Dbms_output.put_line (t. userid | '=>' | t. topicid | '=>' | v_count );
Insert into tb_temp values (t. userid, t. topicid, v_count );
End loop;
COMMIT;

Open result for select * from tb_temp;
END;
-------------- Execute the Stored Procedure

Declare
Ta tp_arr_tbl3: = tp_arr_tbl3 (); -- Object Declaration
T tp_arr3: = tp_arr3 ('0', '0', '0'); -- Declaration and Initial Value assignment (required steps)
Begin
For I in 1 .. 12 loop
Ta. extend;
T. userid: = '1 ';
T. topicid: = I;
T. record: = '0 ';
Ta (I): = t;
FindRecord (ta );
End loop;
End findRecord;

---------------------- Test part of Table query
Select * from tb_temp;

Select * from scott. tb_praise_rel;

Select userid from scott. tb_praise_rel where userid = '1' and topicid = '1 ';
----------------------- Cursor test. It is useless later and can be skipped.
Cursor testcur is select userid, topicid from scott. tb_praise_rel;
Cur testcur % rowtype;

Open testcur;
Loop
Fetch testcur into cur;
Exit when testcur % notfound;
Dbms_output.put_line ('userid: '| cur. userid |', topicid: '| cur. topicid );
Update tb_temp set record = '1' where userid = cur. userid and topicid = cur. topicid;
End loop;
Dbms_output.put_line ('----------------------');
Close testcur;
COMMIT;

Note: 1. the test user initially used Scott and found that the execution permission was not available, but the dba permission was still not granted to the user, and then the system was used; 2. multiple test creation may result in the "ORA-02303: cannot use type or table relevance to delete or replace a type" error, as long as you create another type name; 3. because my tb_temp table has unique field constraints, each time a stored procedure comes in, it deletes the table, creates the table, and inserts data. 4. remember not to miss the place where the semicolon should be typed, not to miss more places; 5. when executing a stored procedure, you must first declare and assign the initial value. Otherwise, an error will be reported. 6. note that when the stored procedure test value is assigned in pl/SQL. extend cannot be less; 6. there are many precautions for custom types, such as nvarchar2 and JavaString; 7. in short, the tp_arr3 type refers to one record, and the tp_arr_tbl3 type refers to multiple records;

-- Like relational table
Create table tb_praise_rel (
Id varchar2 (40) primary key,
Userid varchar2 (40), -- User ID
Topicid varchar2 (40), -- Topic id
Remarks1 varchar2 (3000), -- backup Field
Remarks2 varchar2 (3000 ),
Remarks3 varchar2 (3000)
);

Then use the Java call code:

Package com. lofter. svntesr;