Shulou(Shulou.com)06/01 Report--

Stored procedure

1. Create

Create procedure procedure name (variable name in variable type... Variable name out variable type...) is

/ / define variables Note: you do not need to specify a size after the variable type

Begin

/ / statement executed

End

Example: what is used in the project:

CREATE OR REPLACE PROCEDURE PROC_CBBS_FILES

-stored procedure description

-- / *

-- / * Procedure: PROC_CBBS_FILES-stored procedure

-/ * Discription: insert the data from the mv_f_xinxg_files view into the mv_f_cbbs_files table in turn-stored procedure description

-/ * Version: 1.0 Murray-initial version

-/ * Author: Hao Xiaoli

-/ * Create Date: 2014-08-26

-- / *

AS

Insert_str long;-insert table statement

BEGIN

FOR x IN (select * frommv_f_xinxg_files)

LOOP

-cycle

Insert_str: = 'INSERT INTOmv_f_cbbs_files (MO_ID,CAPTION,TIME,FILES,PROV_ID,PROV_NAME,TACHE_NAME,BUSI_TYPE)

VALUES (''| | x.MO_ID | |'',''| | x.CAPTION | |'',''| | x.TIME | |'',''| |

X.FILES | |'',''| | x.PROV_code | |'',''| | x.PROV_NAME | |'',''| | x.TACHE_NAME | |'',''| | x.busi_type | |',')'

-execute insert statement

EXECUTE IMMEDIATE insert_str

COMMIT;-submit

END LOOP;-end cycle

END;-BEGIN END

Example: stored procedure with no return value for ①:

Please write a procedure that can add a book to the book table and require that the procedure be called through the java program.

-- in: indicates that this is an input parameter. Default is in.

-- out: represents an output parameter

Sql code

1. Create or replace procedure sp_pro7 (spBookId in number,spbookNa

Me in varchar2,sppublishHouse in varchar2) is

2. Begin

3. Insert into book values (spBookId,spbookName,sppublishHouse)

4. End

5. /

-- call in java

Java code

1. / / call a procedure with no return value

2. Import java.sql.*

3. Public class Test2 {

4. Public static void main (String [] args) {

5.

6. Try {

7. / / 1. Load driver

8. Class.forName ("oracle.jdbc.driver.OracleDriver")

9. / / 2. Get a connection

10. Connection ct = DriverManager.getConnection ("jdbc:o

Racle:thin@127.0.0.1:1521:MYORA1 "," scott "," M123 ")

11.

12. / / 3. Create CallableStatement

13. CallableStatement cs = ct.prepareCall ("{callsp_pro7 (?)}")

14. / / 4. Here you are? Assignment

15. Cs.setInt (1, 010)

16. Cs.setString (2, "The Smiling、Proud Wanderer")

17. Cs.setString (3, people's Publishing House)

18. / / 5. Execution

19. Cs.execute ()

20.} catch (Exception e) {

21. E.printStackTrace ()

twenty-two。 } finally {

23. / / 6. Close each open resource

24. Cs.close ()

25. Ct.close ()

twenty-six。 }

twenty-seven。 }

28.}

② stored procedures with return values (non-list)

Example: write a procedure that can enter the employee's number and return the employee's name.

Sql code

1.-- stored procedures with input and output

Create or replace procedure sp_pro8 (spno in number, spName out varchar2) is

Begin

Select ename into spName from empwhere empno=spno

End

/

Java code

Import java.sql.*

Public class Test2 {

Public static void main (String [] args) {

Try {

/ / 1. Load driver

Class.forName ("oracle.jdbc.driver.OracleDriver")

/ / 2. Get a connection

Connection ct = DriverManager.getConnection ("jdbc:oracle:thin@127.0.0.1:1521:MYORA1", "scott", "M123")

/ / 3. Create CallableStatement

/ * CallableStatement cs = ct.prepareCall ("{callsp_pro7 (?)}")

/ / 4. Here you are? Assignment

Cs.setInt (1 dint 10)

Cs.setString (2, "The Smiling、Proud Wanderer")

Cs.setString (3, people's Publishing House); * /

/ / see how to call a procedure with a return value

/ / create a CallableStatement

/ * CallableStatement cs = ct.prepareCall ("{call sp_pro8 (?)}"); / / to the first one? Assignment

Cs.setInt (1 no. 7788)

/ / for the second one? Assignment

Cs.registerOutParameter (2century oracle.jdbc.OracleTypes.VARCHAR)

/ / 5. Execution

Cs.execute ()

/ / take out the return value, should you pay attention to it? The order of

String name=cs.getString (2)

System.out.println ("name of 7788" + name)

} catch (Exception e) {

E.printStackTrace ()

} finally {

/ / 6. Close each open resource

Cs.close ()

Ct.close ()

}

}

Note: 1. For the input value of the process, use setXXX, for the output value, use registerOutParameter, the order of question marks should be corresponding, and the type should be taken into account.

2. The way to retrieve the return value of the process is the getXXX provided by CallableStatement (the location of the output parameter) and consider the type of the output parameter.

Case expansion: write a process that enters the employee's number and returns the employee's name, salary, and position.

Sql code

1.-- stored procedures with input and output

2. Create or replace procedure sp_pro8

3. (spno in number, spName outvarchar2, spSal out number,spJob outvarchar2) is

4. Begin

5. Select ename,sal,job into spName,spSal,spJob from emp where empno=spno

6. End

7. /

Java code

1. Import java.sql.*

2. Public class Test2 {

3. Public static void main (String [] args) {

5. Try {

6. / / 1. Load driver

7. Class.forName ("oracle.jdbc.driver.OracleDriver")

8. / / 2. Get a connection

9. Connection ct = DriverManager.getConnection ("jdbc:oracle:thin@127.0.0.1:1521:MYORA1", "scott", "M123")

11. / / 3. Create CallableStatement

12. / * CallableStatement cs = ct.prepareCall ("{callsp_pro7 (,?)}")

13. / / 4. Here you are? Assignment

14. Cs.setInt (1, 010)

15. Cs.setString (2, "The Smiling、Proud Wanderer")

16. Cs.setString (3, people's Publishing House); * /

18. / / see how to call a procedure with a return value

19. / / create a CallableStatement

20. / * CallableStatement cs = ct.prepareCall ("{callsp_pro8 (?)}")

twenty-two。 / / for the first one? Assignment

23. Cs.setInt (1 no. 7788)

24. / / for the second one? Assignment

25. Cs.registerOutParameter (2century oracle.jdbc.OracleTypes.VARCHAR)

twenty-six。 / / for the third one? Assignment

twenty-seven。 Cs.registerOutParameter (3authored oracle.jdbc.OracleTypes.double)

twenty-eight。 / / to the fourth? Assignment

twenty-nine。 Cs.registerOutParameter (4 recordoracle.jdbc.OracleTypes.VARCHAR)

thirty-one。 / / 5. Execution

thirty-two。 Cs.execute ()

thirty-three。 / / take out the return value, should you pay attention to it? The order of

thirty-four。 String name=cs.getString (2)

thirty-five。 String job=cs.getString (4)

thirty-six。 System.out.println ("name of 7788" + name+ "work:" + job)

thirty-seven。 } catch (Exception e) {

thirty-eight。 E.printStackTrace ()

thirty-nine。 } finally {

forty。 / / 6. Close each open resource

forty-one。 Cs.close ()

forty-two。 Ct.close ()

forty-three。 }

forty-four。 }

45.}

③ stored procedures with return values (list [result set])

Case study: write a process that enters the department number and returns information about all employees in that department.

Because the oracle stored procedure does not return a value, all its return values are replaced by the out parameter, and the list is no exception, but because it is a collection, you can't use normal parameters, you have to use pagkage. So it should be divided into two parts:

The process of returning a result set

1. Create a package in which you define the type test_cursor, which is a cursor. As follows:

Sql code

Create or replace package testpackage as

TYPE test_cursor is ref cursor

End testpackage

two。 Establish a stored procedure. As follows:

Sql code

1. Create or replace procedure sp_pro9 (spNo in number,p_cursor outtestpackage.test_cursor) is

2. Begin

3. Open p_cursor for select * from emp where deptno = spNo

5. End sp_pro9

3. How to call this procedure in a java program

Java code

1. Import java.sql.*

2. Public class Test2 {

3. Public static void main (String [] args) {

5. Try {

6. / / 1. Load driver

7. Class.forName ("oracle.jdbc.driver.OracleDriver")

8. / / 2. Get a connection

9. Connection ct = DriverManager.getConnection ("jdbc:oracle:thin@127.0.0.1:1521:MYORA1", "scott", "M123")

11. / / see how to call a procedure with a return value

12. / / 3. Create CallableStatement

13. / * CallableStatement cs = ct.prepareCall ("{callsp_pro9 (?)}")

15. / / 4. To the first? Assignment

16. Cs.setInt (1, 010)

17. / / for the second one? Assignment

18. Cs.registerOutParameter (2monooracle.jdbc.OracleTypes.CURSOR)

20. / / 5. Execution

21. Cs.execute ()

twenty-two。 / / object is strongly converted to result set

23. ResultSet rs= (ResultSet) cs.getObject (2)

24. While (rs.next ()) {

25. System.out.println (rs.getInt (1) + "+ rs.getString (2))

twenty-six。 }

twenty-seven。 } catch (Exception e) {

twenty-eight。 E.printStackTrace ()

twenty-nine。 } finally {

thirty。 / / 6. Close each open resource

thirty-one。 Cs.close ()

thirty-two。 Ct.close ()

thirty-three。 }

thirty-four。 }

35.}

Run and successfully conclude that the department number is 10 for all users

④ writing paging process

Example: write a stored procedure that requires you to enter a table name, the number of records per page, and the current

Page. Returns the total number of records, total pages, and the returned result set.

Sql code

1. Select T1 whererownum, rownum rn from (select * from emp) T1 whererownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.