A simple example of a Java stored procedure

---create a table
CREATE TABLE TestTable
(
ID1 VARCHAR2 (12),
Name VARCHAR2 (32)
)
Select T.id1,t.name from TestTable t
Insert into TestTable (ID1, NAME)
VALUES (' 1 ', ' Zhangsan ');


Insert into TestTable (ID1, NAME)
VALUES (' 2 ', ' Lisi ');


Insert into TestTable (ID1, NAME)
VALUES (' 3 ', ' Wangwu ');


Insert into TestTable (ID1, NAME)
VALUES (' 4 ', ' Xiaoliu ');


Insert into TestTable (ID1, NAME)
VALUES (' 5 ', ' Laowu ');
---Create a stored procedure
Create or replace procedure Test_count
As
V_total number (1);
Begin
Select COUNT (*) into v_total from TestTable;
Dbms_output.put_line (' Total number of people: ' | | V_total);
End
--Prepare
--Wire to Scott unlock: Alter user Scott account unlock;
--The stored procedure should be under the Scott user. And give Scott a password.
---alter user Scott identified by Tiger;
---Go to the command to execute
EXECUTE Test_count;
----executed in SQL in QL/SPL
Begin
--Call the procedure
Test_count;
End



Create or replace procedure Test_list
As
---is using cursors
CURSOR Test_cursor is a select t.id1,t.name from TestTable t;
Begin
For the Test_record in Test_cursor loop---traverse the cursor and print it out
Dbms_output.put_line (test_record.id1| | Test_record.name);
END LOOP;
test_count;--execution of another stored procedure (test_list contains stored procedure Test_count)
End
-----Execute Stored Procedure test_list
Begin
Test_list;
END;
Parameters for---stored procedure
---in defines an input parameter variable that is used to pass parameters to the stored procedure
--out defines an output parameter variable that is used to fetch data from a stored procedure
---in out defines an input and output parameter variable, both of which function
--These three parameters can only describe the type, do not need to specify the length such as VARCHAR2 (three), Defaul can not write, but as a programmer is best to write.

---Creating a stored procedure with parameters
Create or Replace procedure Test_param (p_id1 in VARCHAR2 default ' 0 ')
As V_name varchar2 (32);
Begin
Select T.name into V_name from TestTable t where T.id1=p_id1;
Dbms_output.put_line (' Name: ' | | V_name);
End
----Executing stored procedures
Begin
Test_param (' 1 ');
End

Default ' 0 '




---Creating a stored procedure with parameters
Create or Replace procedure Test_paramout (V_name out VARCHAR2)
As
Begin
Select name into V_name from testtable where id1= ' 1 ';
Dbms_output.put_line (' Name: ' | | V_name);
End
----Executing stored procedures
DECLARE
V_name VARCHAR2 (32);
BEGIN
Test_paramout (V_name);
Dbms_output. Put_Line (' Name: ' | | V_name);
END;
-------in Out
---Create a stored procedure
Create or Replace procedure Test_paraminout (P_phonenumber in Out varchar2)
As
Begin
p_phonenumber:= ' 0571-' | | P_phonenumber;
End

----
DECLARE
P_phonenumber VARCHAR2 (32);
BEGIN
p_phonenumber:= ' 26731092 ';
Test_paraminout (P_phonenumber);
Dbms_output. Put_Line (' New phone number: ' | | P_phonenumber);
END;
-----The SQL command, query the source code of the current user's stored procedure or function,
-----can be obtained by querying the User_source data dictionary view. The structure of the User_source is as follows:

Sql> DESCRIBE User_source;
Name Type Nullable Default Comments
---- -------------- -------- ------- ------------------------------------------------------------------------------ -------------------------------
Name VARCHAR2 (x) Y name of the object
Type VARCHAR2 (x) Y type of the object: "Type", "Type BODY", "PROCEDURE", "FUNCTION",
' Package ', ' package BODY ' or ' JAVA SOURCE '
Line number Y line number of this line of source
Text VARCHAR2 (4000) Y Source text
Sql>
---query out the definition statement for a stored procedure
Select text from User_source WHERE name= ' Test_count ';
----parameters for querying stored procedure Test_paraminout
Sql> DESCRIBE test_paraminout;
Parameter Type Mode Default?
------------- -------- ------ --------
P_phonenumber VARCHAR2 in Out
Sql>
---See if the status of the current stored procedure is correct,
---valid is correct, invalid indicates that the stored procedure is invalid or needs to be recompiled
SELECT STATUS from user_objects WHERE object_name= ' Test_count ';
-----If you want to check the dependencies of a stored procedure or function, you can determine it by querying the data dictionary user_denpendencies, which is structured as follows:
Sql> DESCRIBE user_dependencies;
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- ----------------------------------------------------------
Name VARCHAR2 (+) name of the object
Type VARCHAR2 (+) Y type of the object
Referenced_owner VARCHAR2 (x) Y owner of referenced object (remote owner if remote object)
Referenced_name VARCHAR2 (+) Y NAME of referenced object
Referenced_type VARCHAR2 (+) Y TYPE of referenced object
Referenced_link_name VARCHAR2 (x) Y NAME of Dblink If this is a remote object
SchemaID number Y
Dependency_type VARCHAR2 (4) Y
Sql>
---Querying stored procedure test_count dependencies
SELECT Referenced_name,referenced_type from user_dependencies WHERE name= ' Test_count ';

A simple example of a Java stored procedure