Deck 11: Stored Procedures and Triggers

Question 1

An important benefit of PL/SQL functions is that they can be used as expressions in SELECT statements.

Answer 1

True

Question 2

When writing a PL/SQL procedure,it is good practice to include length constraints in the data type specifications for parameters.

Answer 2

False
Explanation: You do not provide length in the specification of the data type for a parameter.

Question 3

Because the SQL:1999 trigger specification was defined in response to vendor implementation,most trigger implementations adhere to the SQL:1999 specification.

Answer 3

False

Question 4

To catch a specific error in a stored procedure,you should use a predefined exception or create a user-defined exception.

Question 5

A Database Programming Language is a procedural language with an interface to one or more DBMSs.The interface allows a program to combine procedural statements with nonprocedural database access.

Question 6

A Call-Level Interfaceis a language style for integrating a programming language with a nonprocedural language such as SQL.A statement-level interface involves changes to the syntax of a host programming language to accommodate embedded SQL statements.

Question 7

Portability across host languages is one advantage of using the statement level interface language style.

Question 8

A database programming language allows a program to combine procedural statements with non-procedural database access.

Question 9

Triggers provide reuse of common code,while stored procedures provide rule processing for common tasks.

Question 10

Dynamic binding involves the determination of the access plan at compile time.

Question 11

For procedures and triggers stored in a database,the database connection is explicit.

Question 12

To use the objects in a package,you must use the package name before the object name.

Question 13

An explicit cursor cannot use parameters for non-constant search values in the associated SELECT statement.

Question 14

Due to the growth of online database processing and commercial Web commerce,batch processing is no longer an important method to process database work.

Question 15

Because the optimization process can consume considerable computing resources,it is usually desirable to determine the access plan at run-time using dynamic statement binding.

Question 16

Transitive closure,an important operation for queries involving self-referencing relationships,is supported by most SQL implementations.

Question 17

Procedures and functions are executed by the rule system of the DBMS not by explicit calls as for triggers.

Question 18

Since PL/SQL is a block structured language,all code blocks must have unique names in order to execute in SQL*Plus.

Question 19

All objects in a package interface are public.

Question 20

In the SQL:2003 specification,a statement level interface can support both static and dynamic binding,while a call level interface supports only dynamic binding.

Question 21

The body of a trigger is similar to other PL/SQL blocks,except that triggers have more restrictions on the statements in a block.

Question 22

Which of the following is true of PL/SQL iteration statements?

A) The FOR LOOP iterates until a stopping condition is false.
B) The WHILE LOOP iterates over a range of integer values.
C) The LOOP statement iterates until an EXIT statement ceases termination.
D) All of the above.

Question 23

With regards to conditional decision making in PL/SQL,which statement is true?

A) A condition must evaluate to TRUE or FALSE.
B) Complex conditions are evaluated left to right, and this order cannot be altered.
C) Conditions are evaluated using three-value logic.
D) There is a limit to the number of statements that can be used between the THEN and END-IF keywords.

Question 24

A PL/SQL block contains:

A) A required declaration section, a required executable section, and an optional exception section.
B) An optional declaration section, a required executable section, and an optional exception section.
C) A required declaration section, a required executable section, and a required exception section.
D) An optional declaration section, a required executable section, and a required exception section.

Question 25

A database connection identifies the database used by an application.A database connection can be ________ or ________.

A) implicit/explicit
B) internal/external
C) implicit/dynamic
D) virtual/dynamic

Question 26

Which of the following is the reason the DBMS,instead of the programming environment,manages stored procedures?

A) A DBMS can compile the programming language code along with the SQL statements in a stored procedure.
B) Since they are stored on the server, stored procedures allow flexibility for client-server development.
C) Database administrators can manage stored procedures with the same tools for managing other parts of the database.
D) All of the above.

Question 27

You can encounter mutating table errors in trigger execution,regardless of the DBMS they are executed on.

Question 28

The two language styles provided by SQL:2003 for integrating a procedural language with SQL are:

A) Statement level interface and function level interface.
B) Procedural level interface and trigger level interface.
C) Statement level interface and call level interface.
D) None of the above.

Question 29

In the PL/SQL code block above,aStdSSN is:

A) A return variable.
B) An input parameter.
C) A column in the Student table.
D) None of the above.

Question 30

Based on the PL/SQL code block above,if there is not a SSN in the Student table which matches the value provided:

A) The code will return the name of the Student record that is the closest numeric match.
B) The code will raise an application error.
C) The code will stop executing without any explanation.
D) The code will return NULL.

Question 31

Like procedures,triggers can be tested directly by executing them in SQL*Plus.

Question 32

In PL/SQL,functions should:

A) Always use input parameters.
B) Contain a parameter list.
C) Generate an output value using a RETURN statement.
D) All of the above.

Question 33

For most triggers,you can avoid mutating table errors by using statement triggers with new and old values.

Question 34

Since the number of triggers is a complicating factor in understanding the interaction among triggers,it is always better to create a few large triggers instead of many smaller triggers.

Question 35

What is the primary motivation for using a database programming language?

A) Customization.
B) Batch processing.
C) Complex operations.
D) All of the above. All 3 are the primary motivations for using a database programming language.

Question 36

In PL/SQL,a function is used instead of a procedure when:

A) You want to manipulate output variables.
B) You want to produce a side effect.
C) You are returning a single value.
D) You want to return more than one result.

Question 37

The PL/SQL code block above is an example of:

A) An anonymous block.
B) A PL/SQL package.
C) A PL/SQL function.
D) None of the above.

Question 38

As with other programming languages,in PL/SQL the IF-THEN-ELSE statement construct is:

A) A comparison operator.
B) A conditional statement.
C) A logical operator.
D) None of the above.

Question 39

Which of the following is true of a statement level interface?

A) It is more difficult to learn and use than a CLI.
B) It is available only for proprietary languages.
C) It includes a set of procedures and a set of type definitions for manipulating the results of SQL statements in computer programs.
D) It involves changes to the syntax of a host programming language to accommodate embedded SQL.

Question 40

For statement level interfaces,SQL:2003 provides statements to:

A) Declare cursors.
B) Position cursors.
C) Retrieve values from cursors.
D) All of the above.

Question 41

To control complexity among a collection of triggers,which guideline(s)should be followed?

A) Use data manipulation statements primarily in BEFORE triggers.
B) For triggers that fire on UPDATE statements, do not list the columns to which the trigger applies.
C) Be cautious about creating triggers on tables affected by actions on referenced rows.
D) All of the above.

Question 42

A _____________________ is a procedural language with an interface to one or more DBMSs.

Question 43

In PL/SQL,a(n)_____________ statement is comprised of a variable,the assignment symbol,and an expression.

Question 44

The concept of ______________ for a database programming language involves the association of an SQL statement with its access plan.

Question 45

Mutating table errors:

A) Can occur when one table is cloned from another.
B) Can occur in trigger actions with SQL statements on the target table or related tables affected by DELETE CASCADE actions.
C) Never occur in Oracle databases.
D) None of the above.

Question 46

Which of the following is not a typical use for triggers:

A) Complex integrity constraints.
B) Update propagation.
C) Exception reporting.
D) All of the above are typical uses for triggers.

Question 47

A ______________ can be implicit or explicit.

Question 48

In a PL/SQL IF statement,the keywords AND,OR and NOT are ____________ operators.

Question 49

Open Database Connectivity (ODBC)and Java Database Connectivity (JDBC)are the most widely used _____________ level interfaces.

Question 50

To support customized code,most database application development tools use a coding style known as _________________.

Question 51

For each object defined in a package interface,the package body must define:

A) A private object.
B) An implementation.
C) A cursor.
D) An exception handler.

Question 52

In the case of overlapping triggers,which of the following is true?

A) The firing order is predictable and can be depended on to be the same every time.
B) The firing order has not been specified for SQL:2003.
C) The firing order is the same for all DBMSs.
D) None of the above.

Question 53

In the use of an explicit cursor,which 3 statements replace the FOR statement of an implicit cursor?

A) OPEN, FIND, and CLOSE.
B) OPEN, FETCH, and CLOSE.
C) OPEN, GET, and CLOSE.
D) CONNECT, FIND, and CLOSE.

Question 54

One of the advantages of using a package over procedures and functions is:

A) A package supports a larger unit of modularity.
B) Packages provide easier reuse of code.
C) Packages reduce software maintenance costs.
D) All of the above. All 3 are advantages of using a package.

Question 55

The PL/SQL statement "FOR StudentRec IN SELECT StudentID FROM StudentTable" is an example of:

A) An implicit cursor.
B) An explicit cursor.
C) A dynamic cursor.
D) This statement does not define a cursor.

Question 56

A(n)______________ is a construct in a database programming language that allows for storage and iteration of a set of records returned by a SELECT statement.

Question 57

Which of the following is not a common cursor attribute?

A) %IsOpen.
B) %IsNotOpen.
C) %Found.
D) %NotFound.

Question 58

A(n)_____________ level interface is a language style that involves changes to the syntax of a host programming language to accommodate embedded SQL statements.

Question 59

The PL/SQL code block above:

A) Declares and opens an explicit cursor.
B) Declares and opens an implicit cursor.
C) Does not use a cursor.
D) None of the above.

Question 60

A trigger execution procedure can be affected by which of the following?

A) The DBMS the triggers are executed on.
B) The type of data manipulation statements specified in a trigger.
C) Foreign key constraints on referenced rows.
D) All of the above.

Question 61

In a stored procedure,a(n)_____________ parameter should have a value provided outside the procedure but it can be changed inside the procedure.

Question 62

If you omit the keywords FOR EACH ROW from a trigger specification,the trigger by default becomes a(n)_____________ trigger.

Question 63

Inside a package implementation,each procedure or function must be terminated by a(n)_____________ statement containing the procedure or function name.

Question 64

The ______________________________ specifies the order of execution among the various kinds of triggers,integrity constraints,and database manipulation statements.

Question 65

The _________________ of a trigger involves the keywords BEFORE,AFTER,or INSTEAD OF,along with a triggering event using the keywords INSERT,UPDATE,or DELETE.

Question 66

A package ________________ contains the private details of a package.

Question 67

A package ________________ contains the definitions of procedures and functions along with other objects that can be specified in the DECLARE section of a PL/SQL block.

Question 68

Two triggers with the same timing,granularity,and applicable table ______________ if an SQL statement may cause both triggers to fire.

Question 69

An event-condition-action rule managed by a DBMS is another name for a ____________.

Question 70

When a procedure calls itself,this is known as ______________________.

Question 71

Integrity constraints that compare the values before and after an update to a table occurs are called ___________.

Question 72

The common SQL*Plus command used to list the columns of a table is ___________.

Question 73

The common SQL*Plus command used to display compilation errors is ___________________.

Question 74

Functions should be usable in expressions,i.e.a function call can be replaced by the __________ it returns.

Question 75

An unnamed PL/SQL block of code,which is useful for testing procedures and triggers,is known as a(n)_________ block.

Question 76

In the body of a function,a(n)______________ statement is used to generate the function's output value.