Today, I need to develop simple function in Oracle about data retrieving. As I mention in previous post, I'm very new to Oracle and don't know much of Oracle command and attributes. That's why I seek some information and develop simple function for it. In some information, I found two way to insert query data into variable as follow:

first one

view plain print about
1DECLARE
2 myvar varchar2(50);
3BEGIN
4 SELECT fieldone into myvar FROM tbl_one WHERE id = 1;
5END;

second one

view plain print about
1DECLARE
2 myvar varchar2(50);
3 CURSOR L1 IS
4 SELECT fieldone FROM tbl_one WHERE id = 1;
5BEGIN
6 OPEN L1;
7 FETCH L1 INTO myvar;
8 CLOSE L1;
9END;

Honestly, I don't know different between above two coding. That's why I asked into stackoverflow.com. Some geek wrote http://stackoverflow.com/questions/3757515/difference-between-implicit-and-explicit-cursors-in-oracle But, I'll run briefly.

First one is called implicit cursor, which will throw error if no record found in this statement. Second one is called explicit cursor, the variable is inserted into NULL value if no record found. And even more than one record found, it will store the first fetched record.

What is more, my senior said :

First one is Oracle act directly insert query value into the variable. Second one is Oracle create new cursor on the memory and insert query value into the variable.

What he mention is second one is more using memory than first one.

This entry was posted on September 21, 2010 at 7:15 AM and has received 1985 views. There are currently 2 comments. Print this entry.