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

This article mainly introduces "how to use PostgreSQL cursors". In daily operation, I believe many people have doubts about how to use PostgreSQL cursors. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use PostgreSQL cursors". Next, please follow the editor to study!

I. explanation

A cursor literally means a swimming cursor.

Describe it in database language: a cursor is a location entity mapped on a row of data in the result set. With the cursor, users can access any row of data in the result set. After placing the cursor on a certain row, the data can be manipulated, such as extracting the data of the current row, and so on.

II. Classification

Explicit cursor

A cursor defined with the CURSOR...IS command that can process multiple records returned by a query statement (SELECT).

Implicit cursor

PL/SQL is automatically defined when you execute insert (INSERT), delete (DELETE), modify (UPDATE), and query (SELECT) statements that return a single record.

III. Attributes

The Oracle cursor has four attributes:% ISOPEN,% FOUND,% NOTFOUND,% ROWCOUNT

% ISOPEN determines whether the cursor is opened. If% ISOPEN equals true, otherwise it equals false.

% FOUND% NOTFOUND determines whether the row of the cursor is valid, and if so,% FOUNDD equals true, otherwise equals false.

% ROWCOUNT returns the number of record rows read by the cursor up to the current position.

IV. Use

Declare a cursor

CURSOR emp_info (vartype number) is select * from emp

Open the cursor

Open emp_info

Read cursor

Fetch mycur into varno,varprice

Close the cursor

Close emp_info

Fifth, display cursor traversal

Method one

DECLARE V_EMP_INFO EMP%ROWTYPE; CURSOR EMP_INFO IS SELECT * FROM EMP;-- 1, declare cursor BEGIN OPEN EMP_INFO;-- 2, open cursor, pass parameter value LOOP FETCH EMP_INFO INTO V_EMP_INFO -- 3. Extract cursor fetch into IF EMP_INFO%FOUND THEN DBMS_OUTPUT.PUT_LINE (V_EMP_INFO.ENAME | | 'date of employment' | | V_EMP_INFO.HIREDATE); ELSE DBMS_OUTPUT.PUT_LINE ('finished processing result set'); EXIT; END IF; END LOOP; CLOSE EMP_INFO 4. Close the cursor END

Method two

DECLARE V_EMP_INFO EMP%ROWTYPE; CURSOR EMP_INFO IS SELECT * FROM EMP;-- 1, declare the cursor BEGIN OPEN EMP_INFO;-2, open the cursor, pass the parameter value LOOP FETCH EMP_INFO INTO Variety EMPTOINFO3, extract the cursor fetch into EXIT WHEN EMP_INFO%NOTFOUND DBMS_OUTPUT.PUT_LINE (V_EMP_INFO.ENAME | | 'date of employment is' | | V_EMP_INFO.HIREDATE); END LOOP; DBMS_OUTPUT.PUT_LINE ('finished processing result set'); CLOSE EMP_INFO;-- 4. Close cursor END;/

Method three

DECLARE V_EMP_INFO EMP%ROWTYPE; CURSOR EMP_INFO IS SELECT * FROM EMP;-- 1. Declare cursor BEGIN FOR V_EMP_INFO IN EMP_INFO LOOP DBMS_OUTPUT.PUT_LINE (V_EMP_INFO.ENAME | | 'date of employment is' | | V_EMP_INFO.HIREDATE); END LOOP; DBMS_OUTPUT.PUT_LINE ('finished processing result set'); END / VI. Implicit cursor processing DECLARE V_EMP_INFO EMP%ROWTYPE; BEGIN SELECT * INTO V_EMP_INFO FROM EMP WHERE ename='SMITH'; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE (V_EMP_INFO.ENAME | | 'date of employment' | | V_EMP_INFO.HIREDATE); END IF; DBMS_OUTPUT.PUT_LINE ('finished processing result set'); END At this point, the study on "how to use PostgreSQL cursors" is over. I hope you can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.