Summary of oracle cursor usage

The use of cursor in PL sql is very widespread, and it is often used for the return values of stored procedures and functions, in which the return value for functions solves the problem that pl sql has no table-valued functions in disguise. Generally, when calling stored procedures in java, the returned result sets are also stored with cursors.

Cursors are divided into dynamic cursors and static cursors

1. There are two ways to define dynamic cursors. One is to define custom types such as Type my_cursor is ref cursor. In this way, we can define a cursor type, and then our own variables can be defined with this custom type, such as cursorDemo my_cursor (it is also possible to directly use the sys_refcursor that comes with the system) In addition, the above my_cursor can be divided into strong type and weak type. As the name implies, strong type means to specify which types of values can only be put at the time of definition, such as the strong type definition Type my_cursor is ref cursor return emp%rowtype above. Cursors are called dynamic because you can put different values or values from different tables (weakly typed, strongly typed dynamics refer to values from different tables of the same type), such as open cursorDemo for

Select 1 from dual; can also be open cursorDemo for select a, b from dual

2. Static cursors are relatively simple. Just specify the value directly when defining it, such as cursor my_cursor is select 1 from dual.

Use of cursors.

1. In java, the cursor is called with the type oracleTypes.CURSOR and the result is returned. Some of the code is as follows

CallableStatement cs = ct.prepareCall ("{call findset_emp (?)}")

Cs.setInt (1,10)

Cs.registerOutParameter (2, oracle.jdbc.OracleTypes.CURSOR)

Cs.execute ()

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

While (rs.next ()) {

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

}

2, you can use for loop and fetch in stored procedures or functions to call

The format of the for loop is as follows

Emp my_cursor%rowtype

For emp in my_cursor loop dbms_output.put_line (emp.a)

End loop

The usage of fetch is in the following format

Loop

Fetch my_cursor into emp

Exit when emp%notfound

Dbms_output.put_line (emp.a)

End loop

Compared with the two, the for loop should be simpler, of course, there are other uses, not one by one here, the basic two are enough.