PUT_LINE(\u2018OMG ROW FOUND DBMS_OUTPUT.PUT_LINE(EMP_CUR%ROWCOUNT||\u2019-\u2018||V_ROW.ENAME END IF END LOOP END-CURSOR FOR UDATE attributes-DECLARECursor

Put_line(‘omg row found

This preview shows page 14 - 21 out of 38 pages.

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; /
Image of page 14
-------------------------------------------------------------------------------------------- -------- --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; /
Image of page 15
-------------------------------------------------------------------------------------------- -------- --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; / -------------------------------------------------------------------------------------------- --------
Image of page 16
Image of page 17
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; /
Image of page 18
-------------------------------------------------------------------------------------------- -------- --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; -------------------------------------------------------------------------------------------- --------
Image of page 19
-------------------------------------------------------------------------------------------- -------- 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 -------------------------------------------------------------------------------------------- --------
Image of page 20
Image of page 21

You've reached the end of your free preview.

Want to read all 38 pages?

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture