Answers to Chapter 3

Reviewing It

1: What does PL/SQL stand for?
A1: Procedural Language SQL
2: What exactly is a stored procedure?
A2: A stored procedure is a code module that does some processing and returns a code stating that the procedure was successful or not
3: What is the difference between a Named PL/SQL block and an Un-named PL/SQL block?
A3: Named PL/SQL blocks can be called in the form of procedures or functions, un-named PL/SQL are imbedded in applications
4: What are the three syntax basics of any PL/SQL block?
A4: Begin, Exceptions, End
5: The special datatype %TYPE is useful for what?
A5: %TYPE is useful to guarantee that the datatype and length of the variable matches that of a column in the database
6: PL/SQL uses the DBMS_OUTPUT.PRINT_LINE. What needs to be set in SQL*Plus to see this output?
A6: use of SQL*Plus environment setting SET SERVEROUTPUT ON is set when the PL/SQL feature DBMS_OUTPUT.PRINT_LINE is used
7: What are the three kinds of loops?
A7: Basic, For, and While
8: What are the two variables available to PL/SQL when Oracle errors occur?
A8: SQLCODE and SQLERRM.

Checking It

1: Triggers, Functions, and Procedures can also be coded in JAVA.

True/False

A1: True
2: Cursors are

  1. Temporary storage area for PL/SQL

  2. Work area for SQL returning more than 1 row

  3. Work/storage space for PL/SQL

  4. Executable code modules

A2: b
3: Procedures differ from Functions in that:

  1. Procedures takes no input variables but output variables

  2. Procedures take input variables but Functions cannot

  3. Functions have an output variable where Procedures do not

  4. Functions can have input variables but Procedures cannot

A3: c
4: The PL/SQL variable name must be unique to the PL/SQL block.

True/False

A4: T
5: What is the difference between implicit cursors and explicit cursors?

  1. Explicit processes only 1 row, implicit processes many rows

  2. Explicit cursors are only for DML statements

  3. Implicit cursors are only for DML statements

  4. Implicit cursors can only handle 1 row returned, explicit can handle multiple rows

A5: d
6: An IF statement really doesn't need an ENDIF.

True/False

A6: False
7: If an error condition is not handled by the PL/SQL block:

  1. It is ignored

  2. It is propagated to the calling program

  3. It causes an error inside the PL/SQL

  4. It is handled by PL/SQL default behavior

A7: b.
8: The WHEN_OTHERS error condition can appear where in the EXCEPTIONS area:

  1. Must be the first condition

  2. Must be the last condition

  3. Can appear anywhere, order of conditions does not matter

Applying It

Independent Exercise 1:

  • Create a PL/SQL Procedure that displays the total employee count and the total salary by department.

Independent Exercise 2:

  • Create a PL/SQL Procedure that gives the DEPT 10 people a 10% increase in salary and the DEPT 20 people a 20% increase in salary.

Independent Exercise 3:

  • Create a Function that outputs the total amount of salary only after the last row has been processed.

  • Drop the PEOPLE table.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.17.162.247