PL/SQL interview questions

1. What are the various types of database triggers ?

Answer: There are 12 types of triggers they are combination of:

Insert, delete and update triggers.

Before and after triggers .

Row and statement triggers

2.What is advantage of a stored procedure over a database trigger?

3. What is the use of control files ?

Answer: contains pointers to locations of various data files, redo log files, etc.

Answer: we have control over the firing of stored procedure but we have no control over the firing of trigger 

4.What is maximum no. of statements that can be specified in trigger statement?

Answer: one 

5.Can views be specified in trigger statement?

Answer: no

6. --WRITE A PROGRAM TO PRINT EVEN NUMBERS FROM 1 TO 100

 DECLARE

 N NUMBER(3) :=0;

 BEGIN

 WHILE N<=100

 LOOP

 N :=N+2;

 DBMS_OUTPUT.PUT_LINE(N);

 END LOOP;

 END;

---

7. --Write a PL_SQL block to insert numbers into temp table  Using For Loop

Example :-

Insert the numbers 1..10 excludining 6

BEGIN

FOR i in 1..10 LOOP

  IF i = 6  THEN

    null;

  ELSE

    INSERT INTO  temp(results)

    VALUES (i);

 END IF;

END LOOP;

 COMMIT;

END;

Execute a  PLSQL statement to verify that your PLSQL BLock Worked.

Select * from temp;

8. Using If condition write a pl sql block that to find a number wheather it even or odd

declare

  num number(5);

begin

  num:=&num;

  if num mod 2 =0 then

    dbms_output.put_line('Accepted Number '||num||' is even');

  else

    dbms_output.put_line('Accepted Number '||num||' is odd');

  end if;

end;

9. What is the differance between a procedure and Function.

Both Procedures and functions have the capability of accesing parameters but function returns a value
to the PLSQL block where as procedures do not return any value.
You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.