Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Detailed explanation of cursors and functions in Oracle

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Detailed explanation of cursors and functions in Oracle

1. Vernier

Cursor is a PL/SQL control structure; it can display and control the processing of SQL statements, which is convenient for the row data of the table.

Deal with it one by one. A cursor is not a database object, it just stays in memory.

Procedure:

Declare a cursor

Open the cursor

Take out the result, and the result at this time takes out a row of data

Which type of cursor can be closed to load all the data in a row?

In this case, the ROWTYPE type is used, which means that you can load all the data in a row. For example: query the information with employee number 7369 (definitely one line of information).

Example: query the information with employee number 7369 (definitely one line of information).

DECLARE eno emp.empno%TYPE; empInfo emp%ROWTYPE; BEGIN eno: = & en; SELECT * INTO empInfo FROM emp WHERE empno=eno; DBMS_OUTPUT.put_line ('employee number:' | | empInfo.empno); DBMS_OUTPUT.put_line ('employee name:' | | empInfo.ename); END

Use for loops to manipulate cursors (more common)

DECLARE-declare cursor CURSOR mycur IS SELECT * FROM emp where empno=-1; empInfo emp%ROWTYPE; cou NUMBER; BEGIN-cursor operation uses a loop, but cursor must be opened before operation FOR empInfo IN mycur LOOP-- ROWCOUNT records the number of rows operated by the cursor cou: = mycur%ROWCOUNT; DBMS_OUTPUT.put_line (cou | | 'employee number:' | | empInfo.empno) DBMS_OUTPUT.put_line (cou | | 'employee name:' | | empInfo.ename); END LOOP; END

We can see that the cursor FOR loop really simplifies the development of cursors, we no longer need open, fetch and close statements, we no longer need to use the% FOUND attribute to check whether the last record is reached, and Oracle implicitly does it for us.

Write the first cursor and output all the information.

DECLARE-- declare the cursor CURSOR mycur IS SELECT * FROM emp;-- equivalent to a List (EmpPo) empInfo emp%ROWTYPE; BEGIN-- the cursor operation uses a loop, but the cursor must be opened OPEN mycur before the operation;-- FETCH mycur INTO empInfo the cursor to the next line. -- determine whether any data in this row is found WHILE (mycur%FOUND) LOOP DBMS_OUTPUT.put_line ('employee number:' | | empInfo.empno); DBMS_OUTPUT.put_line ('employee name:' | | empInfo.ename);-- modify the cursor to continue down FETCH mycur INTO empInfo; END LOOP; END

You can also loop cursors in another way: LOOP. END LOOP

DECLARE-declare cursors CURSOR mycur IS SELECT * FROM emp; empInfo emp%ROWTYPE; BEGIN-cursors use loops, but cursors must be opened OPEN mycur before operation; LOOP-FETCH mycur INTO empInfo; EXIT WHEN mycur%NOTFOUND; DBMS_OUTPUT.put_line ('employee number:' | | empInfo.empno) DBMS_OUTPUT.put_line ('employee name:' | | empInfo.ename); END LOOP; END

Note 1: it is best to determine whether the cursor is already open before opening the cursor.

Judge by ISOPEN

Format:

Cursor ISOPEN IF mycur%ISOPEN THEN null; ELSE OPEN mycur; END IF

Note 2: you can use ROWCOUNT to record the number of rows operated on by cursors.

DECLARE-declare cursors CURSOR mycur IS SELECT * FROM emp; empInfo emp%ROWTYPE; cou NUMBER; BEGIN-cursor operations use loops, but cursors must be opened before operation: IF mycur%ISOPEN THEN null; ELSE OPEN mycur; END IF; LOOP-FETCH mycur INTO empInfo; EXIT WHEN mycur%NOTFOUND; cou: = mycur%ROWCOUNT DBMS_OUTPUT.put_line (cou | | 'employee number:' | | empInfo.empno); DBMS_OUTPUT.put_line (cou | | 'employee name:' | | empInfo.ename); END LOOP; END

two。 Function

A function is a process with a return value.

Define a function: this function can query the employee's annual salary according to the employee's number.

CREATE OR REPLACE FUNCTION myfun (eno emp.empno%TYPE) RETURN NUMBER AS rsal NUMBER; BEGIN SELECT (sal+nvl (comm,0)) * 12 INTO rsal FROM emp WHERE empno=eno; RETURN rsal; END

Write the SQL statement directly and call this function:

SELECT myfun (7369) FROM dual

Write a function to enter an employee name to determine whether the name exists in the employee table. If there is a return 1, there is no return 0.

Create or replace function empfun (en emp.ename%type) return number as is_exist number; begin select count (*) into is_exist from emp where ename=upper (en); return is_exist; end

Thank you for reading, hope to help you, thank you for your support to this site!

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.

Views: 214

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report