Shulou(Shulou.com)05/31 Report--

This article introduces you how to use Oracle cursors, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Extract data from cursors

Use the FETCH command to get a row of data from the cursor. Each time the data is extracted, the cursor points to the next row of the result set. The syntax is as follows:

FETCH cursor_name INTO variable [, variable,...]

For each column of a cursor defined by SELECT, the list of FETCH variables should have a variable corresponding to it, and the variable should be of the same type.

Example:

SET SERVERIUTPUT ON

DECLARE

V_ename EMP.ENAME%TYPE

V_salary EMP.SALARY%TYPE

CURSOR c_emp IS SELECT ename,salary FROM emp

BEGIN

OPEN c_emp

FETCH c_emp INTO v_ename,v_salary

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

FETCH c_emp INTO v_ename,v_salary

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

FETCH c_emp INTO v_ename,v_salary

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

CLOSE c_emp

END

This code is undoubtedly very troublesome. If there are multiple lines that return results, you can use a loop and use cursor attributes as the condition to end the loop. Extracting data in this way will greatly improve the readability and simplicity of the program. Let's rewrite the above program using a loop:

SET SERVERIUTPUT ON

DECLARE

V_ename EMP.ENAME%TYPE

V_salary EMP.SALARY%TYPE

CURSOR c_emp IS SELECT ename,salary FROM emp

BEGIN

OPEN c_emp

LOOP

FETCH c_emp INTO v_ename,v_salary

EXIT WHEN c_emp%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

END

Record variable

Define a record variable using the TYPE command and% ROWTYPE. For more information about% ROWsTYPE, see the related resources.

Record variables are used to extract rows of data from cursors, and when the cursor selects many columns, it is much more convenient to use records than to declare a variable for each column.

When using% ROWTYPE on the table and putting the values taken from the cursor into the record, if you want to select all the columns in the table, using * in the SELECT clause is much better than listing all the column names.

Example:

SET SERVERIUTPUT ON

DECLARE

R_emp EMP%ROWTYPE

CURSOR c_emp IS SELECT * FROM emp

BEGIN

OPEN c_emp

LOOP

FETCH c_emp INTO r_emp

EXIT WHEN c_emp%NOTFOUND

DBMS_OUT.PUT.PUT_LINE ('Salary of Employee' | | r_emp.ename | |' is' | | r_emp.salary)

END LOOP

CLOSE c_emp

END

% ROWTYPE can also be defined by a cursor name, so the cursor must be declared first:

SET SERVERIUTPUT ON

DECLARE

CURSOR c_emp IS SELECT ename,salary FROM emp

R_emp c_emp%ROWTYPE

BEGIN

OPEN c_emp

LOOP

FETCH c_emp INTO r_emp

EXIT WHEN c_emp%NOTFOUND

DBMS_OUT.PUT.PUT_LINE ('Salary of Employee' | | r_emp.ename | |' is' | | r_emp.salary)

END LOOP

CLOSE c_emp

END

Cursor with parameters

Similar to stored procedures and functions, parameters can be passed to cursors and used in queries. This is useful for dealing with situations where cursors are opened under certain conditions. Its syntax is as follows:

CURSOR cursor_name [(parameter [, parameter],...)] IS select_statement

The syntax for defining parameters is as follows:

Parameter_name [IN] data_type [{: = | DEFAULT} value]

Unlike stored procedures, cursors can only accept values passed, not return values. Parameters define only the data type, not size.

In addition, you can set a default value for the parameter, which is used when no parameter value is passed to the cursor. The parameter defined in the cursor is just a placeholder, and it may not be reliable to reference the parameter elsewhere.

Assign a value to the parameter when the cursor is opened, the syntax is as follows:

OPEN cursor_name [value [, value]....]

Parameter values can be text or variables.

Example:

DECALRE

CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno

CURSOR c_emp (p_dept VARACHAR2) IS

SELECT ename,salary

FROM emp

WHERE deptno=p_dept

ORDER BY ename

R_dept DEPT%ROWTYPE

V_ename EMP.ENAME%TYPE

V_salary EMP.SALARY%TYPE

V_tot_salary EMP.SALARY%TYPE

BEGIN

OPEN c_dept

LOOP

FETCH c_dept INTO r_dept

EXIT WHEN c_dept%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname)

V_tot_salary:=0

OPEN c_emp (r_dept.deptno)

LOOP

FETCH c_emp INTO v_ename,v_salary

EXIT WHEN c_emp%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary)

V_tot_salary:=v_tot_salary+v_salary

END LOOP

CLOSE c_emp

DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary)

END LOOP

CLOSE c_dept

END

Vernier FOR loop

Most of the time we follow these steps when designing programs:

1. Open the cursor

2. Start the cycle

3. Take a value from the cursor

That line is returned

5. Deal with

6. Close the loop

7. Close the cursor

You can simply refer to this type of code as cursors for loops. But there is another loop that is different from this type, and this is the FOR loop. The cursor used for the FOR loop is declared in a normal way. Its advantage is that it does not need to explicitly open, close, fetch data, test the existence of data, define variables to store data, and so on. The syntax of the cursor FOR loop is as follows:

FOR record_name IN

(corsor_name [(parameter [, parameter]...)]

| (query_difinition)

LOOP

Statements

END LOOP

Let's rewrite the above example with a for loop:

DECALRE

CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno

CURSOR c_emp (p_dept VARACHAR2) IS

SELECT ename,salary

FROM emp

WHERE deptno=p_dept

ORDER BY ename

V_tot_salary EMP.SALARY%TYPE

BEGIN

FOR r_dept IN c_dept LOOP

DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname)

V_tot_salary:=0

FOR r_emp IN c_emp (r_dept.deptno) LOOP

DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary)

V_tot_salary:=v_tot_salary+v_salary

END LOOP

DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary)

END LOOP

END

Using queries in cursor FOR loops

The query can be defined in the cursor FOR loop, the cursor has no name because it is not explicitly declared, and the record name is defined by the cursor query.

DECALRE

V_tot_salary EMP.SALARY%TYPE

BEGIN

FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP

DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname)

V_tot_salary:=0

FOR r_emp IN (SELECT ename,salary

FROM emp

WHERE deptno=p_dept

ORDER BY ename) LOOP

DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary)

V_tot_salary:=v_tot_salary+v_salary

END LOOP

DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary)

END LOOP

END

Subqueries in cursors

The syntax is as follows:

CURSOR C1 IS SELECT * FROM emp

WHERE deptno NOT IN (SELECT deptno

FROM dept

WHERE dnameplate accounts

You can see that it is no different from a subquery in SQL.

On how to use Oracle cursors to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.