DBMS LAB ASSIGNMENT 5

LAB 5:

PL/SQL

NOTE: DECLARE AND BEGIN ARE TWO through which SQL knows it is PL/SQL statements to be executed.

1. Write a PL/SQL block. Take the salary of an employee into a variable and check if his or her salary is less than 3000. If ist is less than 3000 then update the EMP table with 3000.

Accept the empno from the user. USE a substitution variable.(&)

2. USE a BIND variable. Write a PL/SQL block to write the deptno, into a table called RESULTS, which has been selected with a condition say of department name is ‘ACCOUNTING’ . if you don’t have a table called RESULTS create one single column as number datatype.

NOTE: BIND variables are declared in SQL*Plus. and are referenced within a PL/SQL block as a HOST variable(with preceding COLON).(:).

3. Write a PL?SQL block. Declare a variable empno and assign it a value of 7788.(Ensure that empno = 7788 exists in your EMP table.). Update the EMP table with sal = 9000 where empno = empno.

Note : See the result the ROLLBACK else all the date will become irrelevant for next practical classes. Why is it happening so?

4. Write a PL/SQL block. Declare a variable v_empno and assign it a value of 7788.(Ensure that empno = 7788 exists in your EMP table.). Update the EMP table with sal = 9000 where empno = v_empno.

Warning : Be careful with the variable names.

IMPLICT CURSOR.

5. Write a PL/SQL block to delete all employees belonging to department 50. And check the number of rows that have been deleted. USE the SQL%ROWROWCOUNT attribute. Insert the value into a table del_history and see the result. Try the same block with department 20.

DO NOT FORGET TO ROLLBACK. if you don’t have a table called del_history create one.

NOTE : SQL%ROWCOUNT returns the number of rows processed by the SQL statement.(an integer value).

6. Write a PL/SQL block to delete all employees belonging to department 50. And check if atleast one row has been processed. USE the SQL %found attribute. Insert the value into a table dels_history and see the result. Try the same block with department 20.

NOTE : SQL%FOUND returns TRUE if atleast one row was processed. Otherwise FALSE.( a BOOLEAN value).

7. Write a PL/SQL block to delete all employees belonging to department 50. And check if any rows have been processed. USE the SQL%NOTFOUND attribute. Insert the value into a table dels_history and see the result. Try the same block with department 20.

NOTE : SQL%FOUND returns TRUE if no rows were processed. Otherwise FALSE. (a BOOLEAN value).

If you have a time : For Q6 and Q7 try another logic of inserting the Boolean values.

8. Write a PL/SQL block to delete all employees whose job = ‘CLERK’ and update the EMP file with job = ‘CAPTAIN’ and sal = 8000. And check if any rows have been processed. USE the SQL%NOTFOUND attribute. If it is TRUE then ROLLBACK if it is FALSE then commit. Insert the value into table dels_history and see the result.

INTO CLAUSE

9. In a PL/SQL block, Declare two variables(specify the datatypes_ and select data from a table into the variables, change the values and through the PL/SQL block insert the values in a table.

%TYPE

10. In a PL/SQL block , Declare two variables , datatypes should be same as that in the table, and select data from a table into variables , change the values and through a PL/SQL block insert the values in a table. USE%TYPE.

SAVE POINT

11. Write a PL/SQL block to insert values into a file. When there is a duplication of values on index rolls back to a SAVE POINT that has been declared earlier in else it writes into a table error message.

NOTE : Insert values into a file that has various values .Make a unique Index file and then Insert a value that is existing in a file. An error araises.

NOTE : 1. INDEX files speed up the retrieval.

2. When there is a where clause then ORACLE uses index file if existing otherwise it uses its own convenient and easy path analysing the EXPLAIN PLAN

Create the index file.

DROP the index file.