Orcal Stored Procedure

3.1 basic oracle Stored Procedure syntax
3.1.1 Basic Structure
CREATE OR REPLACE PROCEDURE
(
Parameter 1 in number,
Parameter 2 IN NUMBER
) IS
Variable 1 INTEGER: = 0;
Variable 2 DATE;
BEGIN
END stored procedure name

3.1.2 SELECT INTO STATEMENT
Save the select query result to a variable. You can store multiple columns in multiple variables at the same time. One record is required; otherwise, an exception is thrown (if no record exists, NO_DATA_FOUND is thrown)
Example:
BEGIN
SELECT col1, col2 into variable 1, variable 2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Xxxx;
END;
...

3.1.3 IF judgment
IF V_TEST = 1 THEN
BEGIN
Do something
END;
End if;

3.1.4 while LOOP
WHILE V_TEST = 1 LOOP
BEGIN
XXXX
END;
End loop;

3.1.5 variable assignment
V_TEST: = 123;

3.1.6 Use cursor with for in
...
IS
CURSOR cur is select * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM: = cur_result. Column name 1 + cur_result. Column name 2
END;
End loop;
END;

3.1.7 cursor with Parameters
CURSOR C_USER (C_ID NUMBER) is select name from user where typeid = C_ID;
OPEN C_USER (variable value );
LOOP
FETCH C_USER INTO V_NAME;
Exit fetch C_USER % NOTFOUND;
Do something
End loop;
CLOSE C_USER;

3.2 process return record set:
Create or replace package pkg_test
AS
TYPE myrctype is ref cursor;
　
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype );
END pkg_test;
/
　　
Create or replace package body pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
Sqlstr VARCHAR2 (500 );
BEGIN
IF p_id = 0 THEN
OPEN p_rc
Select id, NAME, sex, address, postcode, birthday
FROM student;
ELSE
Sqlstr: =
'Select id, name, sex, address, postcode, birthday
From student where id =: w_id ';
OPEN p_rc FOR sqlstr USING p_id;
End if;
END get;
END pkg_test;
/

3.3 function return record set:
Create a package and package body and function defined with ref cursor:
CREATE OR REPLACE
Package pkg_test
/* Define the ref cursor type
The return type is not added. The return type is weak. dynamic SQL queries are allowed,
Otherwise, it is strongly typed and cannot be queried using dynamic SQL;
*/
Type myrctype is ref cursor;
　
-- Function declaration
Function get (intID number) return myrctype;
End pkg_test;
/

CREATE OR REPLACE
Package body pkg_test
-- Function body
Function get (intID number) return myrctype is
Rc myrctype; -- defines the ref cursor variable
Sqlstr varchar2 (500 );
Begin
If intID = 0 then
-- The static test directly returns the result using the select statement.
Open rc for select id, name, sex, address, postcode, birthday from student;
Else
-- Assign a value to a dynamic SQL statement. Use w_id to declare that the variable is obtained from the outside.
Sqlstr: = 'select id, name, sex, address, postcode, birthday from
Student where id =: w_id ';
-- Dynamic Test: return results using sqlstr strings and PASS Parameters using keywords
Open rc for sqlstr using intid;
End if;
　
Return rc;
End get;
　
End pkg_test;

Common syntax for oracle stored procedures: create or replace procedure sp_test
(
Aa in varchar,
Bb in number default 0
) Is
V_pos varchar (50 );
V_num number;
Begin
--- String search and replacement
Select SUBSTR (PARAMETER, 1, INSTR (PARAMETER, branchId)-2) |
SUBSTR (PARAMETER, INSTR (PARAMETER, branchId) + length (branchId) into v_pos from dual;

--- Circular usage
Loop
If bb is null then --- if judgment
Exit; --- exit the loop
End if;
If v_num> = bb then
Exit;
End if;
V_num: = v_num + 1;
End loop;

--- Output information
Dbms_output.put_line ('aaaaa ');

..

Commit;
Exception
When NO_DATA_FOUND then -- no exception is recorded
Dbms_output.put_line ('ddddd ');
When others then
Begin
-- Output error message
Dbms_output.put_line (sqlerrm );
Rollback;

--- Throw an exception
Raise;
End;
End sp_test;
3.4 create a table:
Create table "QJFUND". "TEST1"
("TESTID" VARCHAR2 (80 BYTE ),
"NAME" VARCHAR2 (20 BYTE ),
"PASSWORD" VARCHAR2 (20 BYTE)
)

3.5 create a stored procedure:
(A) stored procedure without return values:
Create or replace procedure testa (PARA1 IN VARCHAR2) AS -- PARA1 is the input parameter
BEGIN
Update Test1 set testid = para1;
End testa;

(B) stored procedures with returned values:
Create or replace PACKAGE pkg_test -- in this example, the name is pkg_test.
AS
TYPE myrctype is ref cursor;
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype); -- the stored PROCEDURE name is get. There is an input parameter NUMBER. One output parameter is myrctype.

END pkg_test;
/-- The diagonal line cannot be omitted; otherwise, it is wrong.
Create or replace package body pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
Sqlstr VARCHAR2 (500 );
BEGIN
IF p_id = 0 THEN
OPEN p_rc
SELECT *
FROM test1;
ELSE
Sqlstr: =
'Select name, password
From test1 ';
OPEN p_rc FOR sqlstr; -- USING p_id
End if;
END get;
END pkg_test;
/-- The diagonal line cannot be omitted; otherwise, it is wrong.
3.6 Java call:

Session oracleSession = HibernateSessionFactory. getSession (); // Hibernate Session
Connection conn = oracleSession. connection (); // obtain the Oracle Connection
Try {
CallableStatement proc = conn. prepareCall ("{call TESTA (?) } "); // Call the Stored Procedure TESTA without a returned value
Proc. setString (1, "ss"); // set the parameters defined in the stored procedure. The parameters start from 1.
OracleSession. beginTransaction ();
Proc.exe cute (); // run the Stored Procedure
OracleSession. getTransaction (). commit ();

Proc = conn. prepareCall ("{call pkg_test.get (?,?) } "); // Call the stored procedure of the returned list
Proc. setInt (1, 1); // set the parameters of the stored procedure, starting from 1
Proc. registerOutParameter (2, oracle. jdbc. OracleTypes. CURSOR );
OracleSession. beginTransaction ();
Proc.exe cute (); // executes the Stored Procedure
OracleSession. getTransaction (). commit ();
ResultSet rs = (ResultSet) proc. getObject (2); // obtain the returned value. The value 2 corresponds to the output parameter location when the stored procedure is defined.
StringBuilder result = new StringBuilder ();
Result. append ("<table> ");
While (rs. next ()){
Result. append ("<tr> <td>" + rs. getString (1) + "</td> <td>"
+ Rs. getString (2) + "</td> </tr> ");
} // Rs. getString (1) corresponds to the first column of the query result
Result. append ("</table> ");
Request. setAttribute ("result", result. toString ());

} Catch (Exception e ){
E. printStackTrace ();
}