What is the difference between explicit and implic

Question

I am a bit rusty on my cursor lingo in PL/SQL. Anyone know this?

Answer 1

An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from

• inefficiency (the ANSI standard specifies that it must fetch twice to check if there is more than one record)
• vulnerability to data errors (if you ever get two rows, it raises a TOO_MANY_ROWS exception)

Example

SELECT col INTO var FROM table WHERE something;

An explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others.

Example

DECLARE   
  CURSOR cur IS SELECT col FROM table WHERE something; 
BEGIN
  OPEN cur;
  FETCH cur INTO var;
  CLOSE cur;
END;

Answer 2

1.CURSOR: When PLSQL issues sql statements it creates private work area to parse & execute the sql statement is called cursor.

2.IMPLICIT: When any PL/SQLexecutable block issues sql statement. PL/SQL creates implicit cursor and manages automatically means implcit open & close takes place. It used when sql statement return only one row.It has 4 attributes SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.

3.EXPLICIT: It is created & managed by the programmer. It needs every time explicit open,fetch & close. It is used when sql statement returns more than one row. It has also 4 attributes CUR_NAME%ROWCOUNT, CUR_NAME%FOUND, CUR_NAME%NOTFOUND, CUR_NAME%ISOPEN. It process several rows by using loop. The programmer can pass the parameter too to explicit cursor.

• Example: Explicit Cursor

 

declare 
   cursor emp_cursor 
   is 
   select id,name,salary,dept_id 
   from employees; 
   v_id employees.id%type; 
   v_name employees.name%type; 
   v_salary employees.salary%type; 
   v_dept_id employees.dept_id%type; 
   begin 
   open emp_cursor; 
   loop 
   fetch emp_cursor into v_id,v_name,v_salary,v_dept_id; 
   exit when emp_cursor%notfound;
   dbms_output.put_line(v_id||', '||v_name||', '||v_salary||','||v_dept_id); 
   end loop;                    
   close emp_cursor; 
   end;

Answer 3

A cursor is a SELECTed window on an Oracle table, this means a group of records present in an Oracle table, and satisfying certain conditions. A cursor can SELECT all the content of a table, too. With a cursor you can manipulate Oracle columns, aliasing them in the result. An example of implicit cursor is the following:

BEGIN
   DECLARE
      CURSOR C1
      IS
         SELECT DROPPED_CALLS FROM ALARM_UMTS;

      C1_REC   C1%ROWTYPE;
   BEGIN
      FOR C1_REC IN C1
      LOOP
         DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);
      END LOOP;
   END;
END;
/

With FOR ... LOOP... END LOOP you open and close the cursor authomatically, when the records of the cursor have been all analyzed.

An example of explicit cursor is the following:

BEGIN
   DECLARE
      CURSOR C1
      IS
         SELECT DROPPED_CALLS FROM ALARM_UMTS;

      C1_REC   C1%ROWTYPE;
   BEGIN
      OPEN c1;

      LOOP
         FETCH c1 INTO c1_rec;

         EXIT WHEN c1%NOTFOUND;

         DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);
      END LOOP;

      CLOSE c1;
   END;
END;
/

In the explicit cursor you open and close the cursor in an explicit way, checking the presence of records and stating an exit condition.

Answer 4

Google is your friend: http://docstore.mik.ua/orelly/oracle/prog2/ch06_03.htm

PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an "implicit" cursor because you, the developer, do not explicitly declare a cursor for the SQL statement.

An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.