Cursors

CEC@asuiin-te-system:~$ sudo bash
[sudo] password FOR CEC:
root@asuiin-te-system:/home/CEC# sqlplus sys AS sysdba

SQL*Plus: RELEASE 11.2.0.2.0 Production ON Tue Jan 3 10:38:46 2023

Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.

Enter password:

Connected TO:
Oracle Database 11g Express Edition RELEASE 11.2.0.2.0 - 64bit Production

SQL> SET serveroutput ON;
SQL> CREATE TABLE cust(Name VARCHAR(10), salary int);

TABLE created.

SQL> DESC cust;
 Name                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 NAME                           VARCHAR2(10)
 SALARY                         NUMBER(38)

SQL> INSERT INTO cust VALUES('Arun',1000);

1 ROW created.

SQL> INSERT INTO cust VALUES('Klop',0);

1 ROW created.

SQL> INSERT INTO cust VALUES('Pep',1250);

1 ROW created.

SQL> INSERT INTO cust VALUES('Zidane',3002);

1 ROW created.

SQL> INSERT INTO cust VALUES('Kante',0001);

1 ROW created.

SQL> INSERT INTO cust VALUES('Rono',777);

1 ROW created.

SQL> SELECT * FROM cust;

NAME           SALARY
---------- ----------
Arun         1000
Klop            0
Pep      1250
Zidane       3002
Kante           1
Rono          777

6 rows selected.

SQL> DECLARE
  2  total_rows NUMBER(2);
  3  BEGIN
  4     UPDATE cust
  5     SET salary=salary+500;
  6     IF sql%notfound THEN
  7         DBMS_OUTPUT.put_line('No customers selected');
  8     elif sql%found THEN
  9         total_rows :=sql%rowcount;
 10         DBMS_OUTPUT.put_line(total_rows || 'Customers Selected');
 11     END IF;
 12  END;
 13  /
    elif sql%found THEN
         *
ERROR AT line 8:
ORA-06550: line 8, column 7:
PLS-00103: Encountered the symbol "SQL" WHEN expecting one OF the following:
:= . ( @ % ;
ORA-06550: line 12, column 4:
PLS-00103: Encountered the symbol ";" WHEN expecting one OF the following:
IF


SQL> DECLARE
  2  total_rows NUMBER(2);
  3  BEGIN
  4     UPDATE cust
  5     SET salary+=500;
  6     IF sql%notfound THEN
  7         DBMS_OUTPUT.put_line('No Customers selected');
  8     ELSIF sql%found THEN
  9         total_rows ;=sql%rowcount;
 10         DBMS_OUTPUT.put_line(total_rows || ' Customers Selected ');
 11     END IF;
 12  END;
 13  /
    SET salary+=500;
              *
ERROR AT line 5:
ORA-06550: line 5, column 12:
PL/SQL: ORA-00927: missing equal SIGN
ORA-06550: line 4, column 2:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 15:
PLS-00103: Encountered the symbol "=" WHEN expecting one OF the following:
( BEGIN CASE DECLARE ELSE ELSIF END EXIT FOR GOTO IF LOOP MOD
NULL PRAGMA RAISE RETURN SELECT UPDATE WHILE WITH
<an identifier> <a double-quoted delimite
ORA-06550: line 10, column 3:
PLS-00103: Encountered the symbol "DBMS_OUTPUT"
ORA-06550: line 10, column 61:
PLS-00103: Encountered the symbol ";" WHEN expecting one OF the following:
. ( , * % & - + / AT MOD remainder rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> AS
FROM INTO || multiset BULK


SQL> DECLARE
  2  total_rows NUMBER(2);
  3  BEGIN
  4  UPDATE cust
  5  SET salary+=500;
  6  ;
  7  /
SET salary+=500;
          *
ERROR AT line 5:
ORA-06550: line 5, column 11:
PL/SQL: ORA-00927: missing equal SIGN
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol ";" WHEN expecting one OF the following:
( BEGIN CASE DECLARE END EXCEPTION EXIT FOR GOTO IF LOOP MOD
NULL PRAGMA RAISE RETURN SELECT UPDATE WHILE WITH
<an identifier> <a double-quoted delimited-


SQL> DECLARE
  2  total_rows NUMBER(2);
  3  BEGIN
  4  UPDATE cust
  5  SET salary = salary + 500;
  6  IF sql%notfound THEN
  7  DBMS_OUTPUT.put_line('No Customers selected');
  8  ELSIF sql%found THEN
  9  total_rows :=sql%rowcount;
 10  DBMS_OUTPUT.put_line(total_rows || 'Customers selscted');
 11  END IF;
 12  END;
 13  /
6Customers selscted

PL/SQL PROCEDURE successfully completed.

SQL> SELECT * FROM cust;

NAME           SALARY
---------- ----------
Arun         1500
Klop          500
Pep      1750
Zidane       3502
Kante         501
Rono         1277

6 rows selected.

SQL> DECLARE
  2  c_name FROM cust.name%TYPE;
  3  c_salary FROM cust.salary%TYPE;
  4  CURSOR c1 IS SELECT name,salary FROM cust;
  5  BEGIN
  6     OPEN c1;
  7     LOOP
  8     FETCH c1 INTO c_name,c_salary;
  9     EXIT WHEN c1%notfound;
 10     DBMS_OUTPUT.put_line(c_name||' has salary of '||c_salary||'Rupees');
 11     END LOOP;
 12     CLOSE c1;
 13  END;
 14  /
c_name FROM cust.name%TYPE;
       *
ERROR AT line 2:
ORA-06550: line 2, column 8:
PLS-00103: Encountered the symbol "FROM" WHEN expecting one OF the following:
CONSTANT EXCEPTION <an identifier>
<a double-quoted delimited-identifier> TABLE LONG double REF
CHAR TIME TIMESTAMP INTERVAL DATE binary national character
nchar


SQL> DECLARE
  2  c_name cust.name%TYPE;
  3  c_salary cust.salary%TYPE;
  4  CURSOR c1 IS SELECT name, salary FROM cust;
  5  BEGIN
  6      OPEN c1;
  7     LOOP
  8     FETCH c1 INTO c_name,c_salary;
  9     EXIT WHEN c1%notfound;
 10     DBMS_OUTPUT.put_line(c_name||' has salary of '||c_salary||'Rupees');
 11     END LOOP;
 12     CLOSE c1;
 13     CLOSE c1;
 14   END;
 15  /
Arun has salary OF 1500Rupees
Klop has salary OF 500Rupees
Pep has salary OF 1750Rupees
Zidane has salary OF 3502Rupees
Kante has salary OF 501Rupees
Rono has salary OF 1277Rupees
DECLARE
*
ERROR AT line 1:
ORA-01001: invalid CURSOR
ORA-06512: AT line 13


SQL> DECLARE
  2   c_name cust.name%TYPE;
  3   c_salary cust.salary%TYPE;
  4   CURSOR c1 IS SELECT name, salary FROM cust;
  5   BEGIN
  6      OPEN c1;
  7     LOOP
  8     FETCH c1 INTO c_name,c_salary;
  9     EXIT WHEN c1%notfound;
 10     DBMS_OUTPUT.put_line(c_name||' has salary of '||c_salary||'Rupees');
 11     END LOOP;
 12     CLOSE c1;
 13   END;
 14  /
Arun has salary OF 1500Rupees
Klop has salary OF 500Rupees
Pep has salary OF 1750Rupees
Zidane has salary OF 3502Rupees
Kante has salary OF 501Rupees
Rono has salary OF 1277Rupees

PL/SQL PROCEDURE successfully completed.

SQL>