Put_line(‘omg row found

DBMS_OUTPUT.PUT_LINE(‘OMG ROW FOUND!’);
DBMS_OUTPUT.PUT_LINE(EMP_CUR%ROWCOUNT||’-‘||V_ROW.ENAME);
END IF;
END LOOP;
END;
/
--------------------------------------------------------------------------------------------
--------
--CURSOR FOR UDATE attributes
--------------------------------------------------------------------------------------------
--------
DECLARE
Cursor emp_cur is select * from emp for sal nowait;
Begin
For v_row in emp_cur loop
If v_row.sal<3000 then
Update emp set sal=sal*1.25 where current of emp_cur;
End if;
End loop;
End;
/

--------------------------------------------------------------------------------------------
--------
--IMPLICITE CURSOR
--------------------------------------------------------------------------------------------
--------
UPDATE EMP SET SAL=SAL+100 WHERE EMPNO =7000;
---------------------------------------------------------------------------------------------
BEGIN
UPDATE EMP SET SAL=SAL+100 WHERE EMPNO =&EMPNO;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO EMP FOUND’);
ELSE
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||’ ROWS UPDATED’);
END IF;
END;
/

--------------------------------------------------------------------------------------------
--------
--PRE-DEFINED EXCEPTION STATEMENT
--------------------------------------------------------------------------------------------
--------
Declare
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=&EMPNO;
DBMS_OUTPUT.PUT_LINE(‘HELLO ’||V_ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR: EMPNO DOES NOT EXIST !’);
END;
/
--------------------------------------------------------------------------------------------
--------
Declare
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE DEPTNO=&DEPTNO;
DBMS_OUTPUT.PUT_LINE(‘HELLO ’||V_ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR: EMPNO DOES NOT EXIST !’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘ERROR: MULTIPLE EMPLOYEES FOUND ! ’);
END;
/
--------------------------------------------------------------------------------------------
--------
DECLARE
V_NUM1 NUMBER :=&NUM1;
V_NUM2 NUMBER :=&NUM2;
V_NUM3 NUMBER;
BEGIN
V_NUM3 := V_NUM1/V_NUM2;
DBMS_OUTPUT.PUT_LINE(V_NUM3);
EXCEPTION
WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE(‘ERROR: CAN’’T DIVIDE BY ZERO ! ’);
END;
/
--------------------------------------------------------------------------------------------
--------

DECLARE
V_NUM NUMBER;
BEGIN
V_NUM := &NUM;
DBMS_OUTPUT.PUT_LINE(V_NUM);
EXCEPTION
WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(‘ERROR: EXPECTING NUMBER INPUT !
’);
END;
/
--------------------------------------------------------------------------------------------
--------
BEGIN
INSERT INTO DEPT VALUES(&DEPTNO,’&ENAME’,’&LOC’);
EXCEPTION WHEN
DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(‘ERROR: DEPTNO ALREADY EXISTS!’);
END;
/
--------------------------------------------------------------------------------------------
--------
--NON PRE-DEFINED EXCEPTION STATEMENT
--------------------------------------------------------------------------------------------
--------
BEGIN
DELETE FROM DEPT WHERE DEPTNO = &DEPTNO;
END;
/
DECLARE
CHILD_REC_FND EXCEPTION;
PRAGMA EXCEPTION_INIT(CHILD_REC_FND,-2292);
BEGIN
DELETE FROM DEPT WHERE DEPTNO=&DEPTNO;
EXCEPTION
WHEN CHILD_REC_FND THEN DBMS_OUTPUT.PUT_LINE(‘ERROR: FOREIGN KEY FOUND IN ANOTHER
TABLE, CAN’’T DELETE THE DATA!’);
END;
/

--------------------------------------------------------------------------------------------
--------
--USER DEFINED EXCEPTION STATEMENT
--------------------------------------------------------------------------------------------
--------
CREATE TABLE STAFF
(STAFF_ID NUMBER(4) PRIMARY KEY,
SNAME VARCHAR2(30) NOT NULL,
POST VARCHAR2(20) NOT NULL,
SALARY NUMBER(7));
--------------------------------------------------------------------------------------------
--------
DECLARE
V_STAFF_ID STAFF.STAFF_ID%TYPE :=&STAFF_ID;
V_SNAME STAFF.SNAME%TYPE := ‘&SNAME’;
V_POST STAFF.POST %TYPE := ‘&POST’;
V_SALARY STAFF.SALARY%TYPE := &SALARY;
SALARY_OUT_OF_RANGE EXCEPTION;
BEGIN
IF V_SALARY NOT BETWEEN 5000 AND 50000 THEN RAISE SALARY_OUT_OF_RANGE;
ELSE
INSERT INTO STAFF VALUES(V_STAFF_ID,V_SNAME,V_POST,V_SALARY);
END IF;
EXCEPTION
WHEN SALARY_OUT_OF_RANGE THEN DBMS_OUTPUT.PUT_LINE(‘ERROR: SALARY MUST BE IN THE
RANGE OF 5,000 TO 50,000’);
END;
/
--------------------------------------------------------------------------------------------
--------
CREATE TABLE MYEMP AS SELECT EMPNO,ENAME,JOB,SAL FROM EMP;
--------------------------------------------------------------------------------------------
--------

--------------------------------------------------------------------------------------------
--------
DECLARE
V_EMPNO MYEMP.EMPNO%TYPE:=&EMPNO;
V_ENAME MYEMP.ENAME%TYPE:=’&ENAME’;
V_JOB MYEMP.JOB%TYPE:=’&JOB’;
V_SAL MYEMP.SAL%TYPE:=&SAL;
V_MAXSAL MYEMP.SAL%TYPE;
SALARY_EXCEED_MAX EXCEPTION;
BEGIN
SELECT MAX(SAL) INTO V_MAXSAL FROM MYEMP;
IF V_SAL > V_MAXSAL THEN RAISE SALARY_EXCEED_MAX;
ELSE
INSERT INTO STAFF VALUES(V_EMPNO,V_ENAME,V_JOB,V_SAL);
END IF;
EXCEPTION
WHEN SALARY_EXCEED_MAX THEN DBMS_OUTPUT.PUT_LINE(‘ERROR: SALARY MUST BE LESS THAN ’||
V_MAXSAL);
END;
/
--------------------------------------------------------------------------------------------
--------
-- EXCEPTION propagation STATEMENT
--------------------------------------------------------------------------------------------
--------