PL/SQL

Question 1

What is the basic syntax for a block?

Answer 1

DECLARE

BEGIN

EXCEPTION

END

Question 2

What are ‘Anonymous Blocks’?

Answer 2

When you click the run button you get ‘compile runs’

Question 3

What are ‘Named Blocks’

Answer 3

When you click the run button you get ‘compiles’

P code is generated

Question 4

Declare variable v_foo and assign it the value 7

Answer 4

v_foo NUMBER := 7

Question 5

Assign the number of teachers in the COSC department into the v_cosc variable from the teachers table

Answer 5

SELECT COUNT(*)
INTO v_cosc
FROM teachers
WHERE dept = ‘COSC’;

Question 6

What are the 2 errors you may get when using a SELECT … INTO statement?

Answer 6

• NO_DATA_FOUND
  Get when 0 rows are returned
• TOO_MANY_ROWS
  Get when more than 1 row is returned

Question 7

Write this as PL/SQL

If(grade > 50) {
   System.out.println("Pass");
} else if (grade > 0) {
   System.out.println("Fail");
} else {
   System.out.printlnt("W");
}

Answer 7

IF grade > 50 THEN
dbms_output.put_line(‘Pass’);

ELSIF grade > 0 THEN
dbms_output.put_line(‘Fail’);
ELSE
dbms_output.put_line(‘W’);

END IF;

Question 8

Write this as PL/SQL

while (i < 5) {
System.out.println(i);
i++;
}

Answer 8

WHILE i < 5 LOOP
System.out.println(i)
i := i + 1;
END LOOP;

Question 9

Declare a varaible v_name to be the same data type as name from the Employees table

Answer 9

v_name Employees.name%TYPE;

Question 10

Write an exception when theres SELECT … INTO returns no rows to print out Oops!

Answer 10

EXCEPTION
WHEN NO_DATA_FOUND THEN dbms_output.put_line(‘Oops!’);
END;

Question 11

What are bind variables?

Answer 11

Variables created in SQL*Plus and that can be referenced in PL/SQL

Question 12

Declare a bind foo variable to equal 7

Answer 12

:foo := 7;

Question 13

Print out ‘Hello World’ in PL/SQL. Include line that enables printing

Answer 13

SET SERVEROUTPUT ON

dbms_output.put_line(‘Hello World’);