Oracle Database PL/SQL cursor

Oracle Database PL/SQL cursor 3) cursor www.2cto.com 1. The basic principle of the cursor is to process the data results one by one. Low execution efficiency and resource occupation. 2. Whether the cursor attribute % FOUND % ISOPEN opens % NOTFOUND % ROWCOUNT the cursor adds 1 to each row to be extracted, and records the number of rows extracted by the cursor. Parameterized cursor www.2cto.com 1) DECLARECURSOR cur_para <id varchar2> ISSELECT books_name FROM books WHERE books_id = id; t_name books. books_name % TYPE; BEGINOPEN cur_para <'000000'>; LOOPFETCH cur_para INTO t_name; exit when cur_para % NOTFOUND; END <t_name>; end loop; CLOSE cur_para; END;/2) ACCEPT my_tid prompt 'Please input the tid: 'Clare CURSOR teacher_cur (CURSOR_id NUMBER) is select tname, TITLE, sex from teachers where tid = CURRSOR_id; begin open partition (my_tid ); loop fetch teacher_cur INTO teacher_name, teacher_title, teacher_sex; exit when teacher_cur % NOTFOUND ;... end loop; CLOSE teacher_cur; END; 3. explicit CURSOR usage: 1) Declare CURSOR <CURSOR Name> is select <Statement>; 2) OPEN <CURSOR Name> 3) extract FETCH <cursor Name> INTO <Variable list>; FETCH <cursor Name> into pl/SQL record; 4) judge whether the cursor is blank, empty transfer to 5th) non-empty transfer 3) 5) CLOSE <cursor Name> 4. implicit cursor 1) SELECT can only return one record begin select tid, TNAME, TITLE, sex into teacher_id, teacher_name, teacher_title, teacher_sex from teachers where tid = 113; END; 2) SELECT can return multiple records BEGINFOR cur IN (SELECT name FROM deptment) LOOPDBMS_OUTPUT.PUT_LINE <cur. name>; end loop; END;/5. cursor variable 1) Name TYPE <TYPE Name> is ref cursorreturn <return TYPE>; 2) OPEN the cursor variable OPEN <cursor variable> FOR <SELECT statement> 3) CLOSE the cursor variable CLOSE 6. use instance 1) DECLARECURSOR mycur ISSELECT * FROM books; myrecord books % ROWTYPE; BEGINOPEN mycur; FETCH mycur INTO myrecord; WHILE mycur % FOUND LOOPDBMS_OUTPUT.PUT_LINE (myrecord. books_id | ',' | myrecord. books_name); FETCH mycur INTO myrecord; end loop; CLOSE mycur; END; 2) modify data DECLARECURSOR cur ISSELECT name FROM deptment for update; text varchar2 <10>; BEGINOPEN cur; FETCH cur INTO text; WHILE cur % found loopupdate deptment SET name = name | '_ t' where current of cur; FETCH cur INTO text; end loop; CLOSE cur; END ;/