DECLARE
CURSOR EMP_cursor
IS
SELECT * FROM EMP;
CURSOR EMP_CURSOR1 (no IN NUMBER)
IS
SELECT FIRST_NAME FROM EMP WHERE EMPLOYEE_ID=no;
CURSOR EMP_cursor2 (no IN NUMBER )
RETURN EMP%ROWTYPE
IS
SELECT * FROM EMP;
BEGIN
dbms_output.put_line( 'Cursor Created' ) ;
END;
/
DECLARE
new_sal NUMBER(10);
REC_VAL EMP%ROWTYPE;
CURSOR c1
IS
SELECT * FROM emp WHERE employee_id =103;
BEGIN
OPEN c1;
FETCH c1 INTO rec_val;
new_sal :=rec_val.salary * 1000;
dbms_output.put_line( 'Year New Salary : '||new_sal ) ;
CLOSE c1;
END;
/
CREATE TABLE ASSOCIATE
(
USER_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(80),
LAST_NAME VARCHAR2(50),
EMAIL VARCHAR2(100),
SALARY NUMBER,
HIRE_DATE DATE,
CREATION_DATE DATE
) ;
INSERT
INTO ASSOCIATE VALUES
(
1,
'SLOBA',
'RAY',
'slobaexpert@gmail.com',
5000,
'26-FEB-2006',
'26-FEB-2006'
) ;
COMMIT;
BEGIN
UPDATE ASSOCIATE SET SALARY =7000 WHERE USER_ID =2 ;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No row found for update Associate Table' );
END IF ;
END;
DECLARE
TYPE CURVAR_TYPE
IS
REF
CURSOR;
CUR_VARIABLE CURVAR_TYPE;
GET_RESULT VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('##############################################################');
DBMS_OUTPUT.PUT_LINE('Using the ref cursor for selecting Email from associate table');
DBMS_OUTPUT.PUT_LINE('**************************************************************');
OPEN CUR_VARIABLE FOR SELECT EMAIL FROM ASSOCIATE;
FETCH CUR_VARIABLE INTO GET_RESULT;
DBMS_OUTPUT.PUT_LINE('Ref Cursor points to Email as = '||GET_RESULT);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('##############################################################');
DBMS_OUTPUT.PUT_LINE('Using the same ref cursor for selecting First Name from associate table');
DBMS_OUTPUT.PUT_LINE('**************************************************************');
OPEN CUR_VARIABLE FOR SELECT FIRST_NAME FROM ASSOCIATE;
FETCH CUR_VARIABLE INTO GET_RESULT;
DBMS_OUTPUT.PUT_LINE('Ref Cursor points to First name as = '||GET_RESULT);
DBMS_OUTPUT.PUT_LINE('**************************************************************');
END;
DECLARE
CURSOR C1
IS
SELECT * FROM EMP;
REC EMP%ROWTYPE;
LOT INTEGER;
BEGIN
OPEN C1;
IF C1%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is opened');
FETCH C1 INTO REC;
IF C1%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Cursor is having records ... ');
END IF;
LOT:=C1%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('Cursor Row count : '||LOT);
END IF;
END;
[u]OUTPUT:[/u]
Cursor is opened
Cursor is having records ...
Cursor Row count : 1
DECLARE
CURSOR C1
IS
SELECT FIRST_NAME FROM EMP;
NAME VARCHAR2(20) ;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO NAME;
EXIT
WHEN C1%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE(NAME);
END LOOP;
CLOSE C1;
END;
OUTPUT:
Steven
Neena
Lex
Alexander
Bruce
David
Valli
Diana
Nancy
Daniel
DECLARE
CURSOR C1
IS
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMP;
BEGIN
FOR IREC IN C1
LOOP
IREC.SALARY := IREC.SALARY+10;
DBMS_OUTPUT.PUT_LINE('Calculation = '||IREC.SALARY);
DBMS_OUTPUT.PUT_LINE(IREC.EMPLOYEE_ID||','||IREC.FIRST_NAME);
END LOOP;
END;
OUTPUT:
Calculation = 24010
100,Steven
Calculation = 17010
101,Neena
Calculation = 17010
102,Lex
Calculation = 9010
103,Alexander
Calculation = 6010
104,Bruce
Calculation = 4810
105,David
When we are using for loop there is no need to open and fetch a cursor. We can see the same in the above mentioned program. Here in this program for loop will go to all the record in the cursor and add a values 10 with all the salary and display these rows.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)