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

This article mainly explains "what is the performance of SYS_REFCURSOR as a process parameter". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the performance of SYS_REFCURSOR as a process parameter"?

I created the following table and populated the data:

CREATE TABLE plch_tab (item VARCHAR2 (10)) / BEGIN INSERT INTO plch_tab VALUES ('Keyboard'); INSERT INTO plch_tab VALUES (' Mouse'); COMMIT;END;/

Then I created this function to take a row of data from the cursor variable and return:

CREATE OR REPLACE FUNCTION plch_getitem (plch_cur IN SYS_REFCURSOR) RETURN plch_tab.item%TYPEIS lvretval plch_tab.item%TYPE;BEGIN FETCH plch_cur INTO lvretval; RETURN lvretval;END plch_getitem;/

Now I need to write a block to get the rows in the table and display the number of rows and the project name of the last line. Here is the near-finished version:

DECLARE lvitem plch_tab.item%TYPE; test_cur SYS_REFCURSOR;BEGIN OPEN test_cur FOR SELECT * FROM plch_tab ORDER BY item; / * FETCH*/ DBMS_OUTPUT.put_line ('Count =' | | test_cur%ROWCOUNT); DBMS_OUTPUT.put_line ('Item =' | | NVL (lvitem, 'NOT SET')); CLOSE test_cur;END;/

Which of the following options can be used to replace / * FETCH*/ so that after the block is executed, the following output can be seen on the screen?

Count = 2

Item = Mouse

(A)

FETCH test_cur INTO lvitem;lvitem: = plch_getitem (test_cur); SQL > DECLARE 2 lvitem plch_tab.item%TYPE; 3 test_cur SYS_REFCURSOR; 4 BEGIN 5 OPEN test_cur FOR 6 SELECT * FROM plch_tab ORDER BY item; 7 8 FETCH test_cur 9 INTO lvitem; 10 lvitem: = plch_getitem (test_cur); 11 12 DBMS_OUTPUT.put_line ('Count =' | test_cur%ROWCOUNT) 13 DBMS_OUTPUT.put_line ('Item =' | | NVL (lvitem, 'NOT SET')); 14 15 CLOSE test_cur; 16 END; 17 / Count = 2Item = MousePL/SQL procedure successfully completedSQL >

(B)

Lvitem: = plch_getitem (test_cur); lvitem: = plch_getitem (test_cur); SQL > DECLARE 2 lvitem plch_tab.item%TYPE; 3 test_cur SYS_REFCURSOR; 4 BEGIN 5 OPEN test_cur FOR 6 SELECT * FROM plch_tab ORDER BY item; 7 8 lvitem: = plch_getitem (test_cur); 9 lvitem: = plch_getitem (test_cur); 10 11 DBMS_OUTPUT.put_line ('Count =' | test_cur%ROWCOUNT) 12 DBMS_OUTPUT.put_line ('Item =' | | NVL (lvitem, 'NOT SET')); 13 14 CLOSE test_cur; 15 END; 16 / Count = 2Item = MousePL/SQL procedure successfully completedSQL >

(C)

Lvitem: = plch_getitem (test_cur); FETCH test_cur INTO lvitem;SQL > DECLARE 2 lvitem plch_tab.item%TYPE; 3 test_cur SYS_REFCURSOR; 4 BEGIN 5 OPEN test_cur FOR 6 SELECT * FROM plch_tab ORDER BY item; 7 8 lvitem: = plch_getitem (test_cur); 9 FETCH test_cur 10 INTO lvitem; 11 12 DBMS_OUTPUT.put_line ('Count =' | test_cur%ROWCOUNT) 13 DBMS_OUTPUT.put_line ('Item =' | | NVL (lvitem, 'NOT SET')); 14 15 CLOSE test_cur; 16 END; 17 / Count = 2Item = MousePL/SQL procedure successfully completedSQL >

(D)

Lvitem: = plch_getitem (test_cur); FETCH plch_cur INTO lvitem;SQL > DECLARE 2 lvitem plch_tab.item%TYPE; 3 test_cur SYS_REFCURSOR; 4 BEGIN 5 OPEN test_cur FOR 6 SELECT * FROM plch_tab ORDER BY item; 7 8 lvitem: = plch_getitem (test_cur); 9 FETCH plch_cur 10 INTO lvitem; 11 12 DBMS_OUTPUT.put_line ('Count =' | test_cur%ROWCOUNT) 13 DBMS_OUTPUT.put_line ('Item =' | | NVL (lvitem, 'NOT SET')); 14 15 CLOSE test_cur; 16 END; 17 / DECLARE lvitem plch_tab.item%TYPE; test_cur SYS_REFCURSOR;BEGIN OPEN test_cur FOR SELECT * FROM plch_tab ORDER BY item; lvitem: = plch_getitem (test_cur); FETCH plch_cur INTO lvitem; DBMS_OUTPUT.put_line (' Count ='| | test_cur%ROWCOUNT) DBMS_OUTPUT.put_line ('Item =' | | NVL (lvitem, 'NOT SET')); CLOSE test_cur;END;ORA-06550: line 9, column 9: PLS-00201: you must declare the identifier' PLCH_CUR'ORA-06550: line 9, column 3: PL/SQL: SQL Statement ignoredSQL > answer ABC. The answer D is wrong because the name plch_cur is not visible outside the function. REF CURSOR as an IN parameter (does not need to be defined as IN OUT), the cursor will still move forward after FETCH, so FETCH will fetch the next line after you call it. Thank you for reading, the above is the content of "what is the performance of SYS_REFCURSOR as a process parameter". After the study of this article, I believe you have a deeper understanding of what the performance of SYS_REFCURSOR as a process parameter is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.