Lab 2.1 Exercises


2.1.1 Use PL/SQL Anonymous Blocks

a)

Why it is more efficient to combine SQL statements into PL/SQL blocks?

b)

What are the differences between named and anonymous PL/SQL blocks?


For the next two questions, consider the following code:

 
 DECLARE    v_name VARCHAR2(50);    v_total NUMBER; BEGIN    SELECT i.first_name' 'i.last_name, COUNT(*)      INTO v_name, v_total      FROM instructor i, section s     WHERE i.instructor_id = s.instructor_id       AND i.instructor_id = 102    GROUP BY i.first_name' 'i.last_name;    DBMS_OUTPUT.PUT_LINE       ('Instructor 'v_name' teaches 'v_total          ' courses'); EXCEPTION    WHEN NO_DATA_FOUND THEN       DBMS_OUTPUT.PUT_LINE ('There is no such instructor'); END; 
graphics/trick_icon.gif

The SELECT statement in the preceding example is supported by multiple versions of Oracle. However, Oracle 9i also supports the new ANSI 1999 SQL standard, and the SELECT statement can be modified as follows according to this new standard:

 
  SELECT i.first_name' 'i.last_name, COUNT(*)   INTO v_name, v_total   FROM instructor i   JOIN section s   ON (i.instructor_id = s.instructor_id)   WHERE i.instructor_id = 102   GROUP BY i.first_name' 'i.last_name;  

Notice, the FROM clause contains only one table, INSTRUCTOR. Following the FROM clause is the JOIN clause that lists the second table, SECTION. Next, the ON clause lists the join condition between the two tables

 
  i.instructor_id = s.instructor_id  

which has been moved from the WHERE clause.


graphics/trick_icon.gif

You will find detailed explanations and examples of the statements using new ANSI 1999 SQL standard in Appendix E and in the Oracle help. Throughout this book we will try to provide you with examples illustrating both standards; however, our main focus will remain on PL/SQL features rather than SQL.


c)

Based on the example just provided, describe the structure of a PL/SQL block.

d)

What happens when runtime error NO_DATA_FOUND occurs in the PL/SQL block just shown?


2.1.2 Understand How PL/SQL Gets Executed

a)

What happens when an anonymous PL/SQL block is executed?

b)

What steps are included in the compilation process of a PL/SQL block?

c)

What is a syntax error?

d)

How does a syntax error differ from a runtime error?




Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net