DBMS LAB ASSIGNMENT 6

LAB 6:

1. Alter your block produced in Lab Exercise question 11. Redefine the PL/SQL variable as NUMBER(1). What happens if two input values are 4 and 2?

Add an Execption Handler to the block which records an explanatory message message in MESSAGES and ANY kind of exception that may occur. Then run your block again.

2. i. Write aPL/SQL BLOCK; If-Then-Else

For a given account number see the balance and then only update the account ( of the account Number i.e customer)

ii. Write a PL/SQL block to insert a row into the MESSAGES table with the NUMCOLI column holding I if this is the first row to be inserted, 2 if it is the second, and so on.

DO NOT insert a row if the count equals 6 or 8.and exit the loop after the value 10 has been inserted. Commit when the loop ends. (Warning: COUNT is a reserved word!)

iii. Select the ENAME , HIREDATE and SAL columns from EMP where EMPNO equals a number input at run-time. Having selected columns into variables, insert a row into MESSAGES based on the following criteria:

Criteria Message

---------------------------- ----------------------------

Salary greater than 1200 salary more than 1200

ENAME contains as ‘T’ Name contains a ”T”

HIREDATE is month December December

None of the above **None**

Test the block for employee numbers 7654,7369,7900,7876.

3. Write a PL/SQL BLOCK ; If-Then-Elsif-else-end if

For a given account number see the balance is greater than the minimum balance and then onll updates the account (of the account Number i.e customer) or if it is greater than the second nearest minimum balance then update the account otherwise display a message that says Minimum balance is required.

4. Write a PL/SQL BLOCK ; While Loop

Use a While Loop to invert a given Number

5. i. Write a PL/SQL BLOCK ; FOR Loop

Use a FOR Loop to invert a given Number

ii. The following code should be performed in a LOOP, with different values for V on each iteration (range 1 to 10).

UPDATE meassages SET numcol2=100;

WHERE numcoll = V;

If any runs of UPDATE Process in.ore or less than a single row, then exit the loop. (you can test with the SQL%ROWCOUNT attribute)

6. Write a PL/SQL BLOCK ; GOTO Statement

Use a GOTO Statement to increase if any raise in the price of a given product. If no raise display it has the same price to invert a given Number.

7. Write a PL/SQL BLOCK ; SAVE POINT

Use a SAVE POINT to Update the emp table and if the total salary of the emp table exceeds a certain limit then it rolls back to a specific save point.