Chapter 11

1.   What is a database programming language?
Ans: a procedural language with an interface to one or more DBMSs. The interface allows a program to combine procedural statements with non-procedural database access.

2.   Why is customization an important motivation for database programming languages?
Ans: Customization is necessary because no tool provides a complete solution for development of complex database applications. Customization allows an organization to use the built-in power of a tool along with customized code to change the tool’s default actions and to add new actions beyond those supported by the tool.

3.   How do database programming languages support customization?
Ans: Most database application development tools use event driven coding. In this coding style, an event triggers the execution of a procedure. An event model includes events for user actions such as clicking a button as well as internal events such as before a database record is updated. An event procedure may access the values of controls on a forms and reports as well as retrieve and update database records. Event procedures are coded using a database programming language, often a proprietary language provided by the DBMS vendor.

4.   Why is batch processing an important motivation for database programming languages?
Ans: Despite the growth of online database processing, batch processing continues to be an important way to process database work. For example, check processing typically is a batch process in which a clearinghouse bank processes large groups or batches of checks during non peak hours. A database programming language is used to code batch programs.

5.   Why is support for complex operations an important motivation for database programming languages?
Ans: Nonprocedural database access by definition does not support all possible database retrievals. The design of a nonprocedural language involves a tradeoff between amount of code and computational completeness. To allow general purpose computation, a procedural language is necessary. The transitive closure is an important operation not supported by most SQL implementations. For most DBMSs, operations that require the transitive closure must be coded in a database programming language.

6.   Why is efficiency a secondary motivation for database programming languages, not a primary motivation?
Ans: When distrust in optimizing database compilers was high (until the mid 1990s), efficiency was a primary motivation for using a database programming language. To avoid the optimizing compilers, some DBMS vendors supported record-at-a-time access with the programmer determining the access plan for complex queries.  As confidence has grown in optimizing database compilers, the efficiency need has become less important. However with complex Web applications and immature Web development tools, efficiency has become an important issue in some applications. As Web development tools mature, efficiency should become a less important issue.
7.   Why is portability a secondary motivation for database programming languages, not a primary motivation?
Ans: Portability can be important in some environments. Most application development tools and database programming languages are proprietary. If an organization wants to remain vendor neutral, an application can be built using a non-proprietary programming language (such as Java) along with a standard database interface. If just DBMS neutrality is desired (not neutrality from an application development tool), some application development tools allow connection with a variety of DBMSs through database interfaces such as the Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC).

8.   What is a statement level interface?
Ans: A statement level interface is 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. The host language contains additional statements to establish database connections, execute SQL statements, use the results of an SQL statement, associate programming variables with database columns, handle exceptions in SQL statements, and manipulate database descriptors.

9.   What is a call level interface?
Ans: A call level interface (CLI) a language style for integrating a programming language with a nonprocedural language such as SQL. A CLI includes a set of procedures and a set of type definitions for manipulating the results of SQL statements in computer programs. The procedures provide similar functionality to the additional statements in a statement level interface. A call level interface is more difficult to learn and use than a statement level interface. However, the SQL:2003 CLI is portable across host languages, whereas the statement level interface is not portable and not supported for all programming languages.
10. What is binding for a database programming language?
Ans: Binding for a database programming language involves the association of an SQL statement with its access plan.
11. What is the difference between static and dynamic binding?
Ans: Static binding involves determining the access plan at compile time.  Because the optimization process can consume considerable computing resources, it is desirable to determine the access plan at compile time and to reuse the access plan for repetitively executed statements. Dynamic binding involves determining the access plan at run time because the data to retrieve cannot be predetermined until an application executes in some situations.
12. What is the relationship between language style and binding?
Ans: A statement level interface can support both static and dynamic binding. Embedded SQL statements have static binding. Dynamic SQL statements can be supported by the SQL:2003 EXECUTE statement that contains an SQL statement as an input parameter. If a dynamic statement is repetitively executed by an application, the SQL:2003 PREPARE statement supports reuse of the access plan. The SQL:2003 CLI supports only dynamic binding. If a dynamic statement is repetitively executed by an application, the SQL:2003 CLI  provides the Prepare() procedure to reuse the access plan.
13. What SQL:2003 statements and procedures support explicit database connections?
Ans: SQL:2003 specifies the CONNECT statement and other related statements for statement level interfaces and the Connect() procedure and related procedures in the CLI.
14. What differences must be resolved to process the results of an SQL statement in a computer program?
Ans: To process the results of SQL statements, database programming languages must resolve differences in data types and processing orientation.

15. What is a cursor?
Ans: To process the results of SQL statements that return more than one row, a cursor must be used. A cursor allows storage and iteration of a set of records returned by a SELECT statement. A cursor is similar to a dynamic array in which the array size is determined by the size of the query result.
16. What statements and procedures does SQL:2003 provide for cursor processing?
Ans: For statement level interfaces, SQL:2003 provides statements to declare cursors, open and close cursors, position cursors, and retrieve values from cursors. The SQL:2003 CLI provides procedures with similar functionality to the statement level interface.
17. Why study PL/SQL?
Ans: There are many database programming languages that could be studied for reading and writing stored procedures. PL/SQL is a widely used language among Oracle developers, and Oracle is a widely used enterprise DBMS. In addition, PL/SQL has the features of a modern programming language.
18. Explain case sensitivity in PL/SQL. Why are most parts of PL/SQL case insensitive?
Ans: User identifiers and reserved words are not case sensitive. String constants are case sensitive. Case sensitivity can lead to subtle errors in code. Thus, many languages are case insensitive for user identifiers and reserved words.
19. What is an anchored variable declaration?
Ans: An anchored variable declaration uses the data type associated with another variable. Anchored declarations relieve the programmer from knowing the data types of database columns. An anchored declaration includes a fully qualified column name followed by the keyword %TYPE.
20. What is a logical expression?
Ans: A condition that evaluates to TRUE, FALSE, or NULL. Conditions include comparison expressions using the comparison operators connected using the logical operators AND, OR, and NOT. Conditions are evaluated using the three-valued logic.
21. What conditional statements are provided by PL/SQL?
Ans: PL/SQL provides the IF-THEN statement to test a single condition, the IF-THEN-ELSE statement with a set of alternative statements if the condition is false, and the IF-THEN-ELSEIF statement to test a condition with each ELSEIF clause. The CASE statement uses a selector instead of condition. A selector is an expression whose value is used to determine a decision.

22. What iteration statements are provided by PL/SQL?
Ans: The FOR LOOP statement iterates over a range of integer values. The WHILE LOOP statement iterates until a stopping condition is false. The LOOP statement iterates until an EXIT statement ceases termination. Note that the EXIT statement can also be used in the FOR LOOP and the WHILE LOOP statements to cause early termination of a loop.
23. Why use an anonymous block?
Ans: A PL/SQL block contains an optional declaration section (DECLARE keyword), an executable section (BEGIN keyword), and an optional exception section (EXCEPTION keyword). Anonymous blocks do not have names. Anonymous blocks are useful to test PL/SQL statements and develop test cases for stored procedures and triggers.
24. Why should a DBMS manage procedures rather than a programming language environment?
Ans: A DBMS can compile the programming language code along with the SQL statements in a stored procedure. A DBMS can detect when the SQL statements in a procedure need to be recompiled due to changes in database definitions. A DBMS can store procedures on a server rather than replicating procedures on every client. A DBMS provides security for stored procedures. A DBMS allows stored procedures to extend the functionality of standard SQL functions.
25. What are the three usages of a parameter?
Ans: An input parameter (IN) should not be changed inside a procedure. An output parameter (OUT) is given a value inside a procedure. An input-output parameter (IN OUT) should have a value provided outside a procedure but can be changed inside a procedure.
26. What is the restriction on the data type in a parameter specification?
Ans: You cannot provide a length or any other data type constraint for a parameter.
27. Why use predefined exceptions and user-defined exceptions?
Ans: To catch a specific error, you should use a predefined exception or create a user-defined exception.
28. Why use the OTHERS exception?
Ans: The OTHERS exception is a catchall. You should use this exception to catch a variety of errors when not needing specialized code for each kind of exception.

29. How does a function differ from a procedure?
Ans: Functions should return values instead of manipulating output variables and having side effects such as inserting a row. You should always use a procedure if you want to have more than one result and/or have a side effect. Functions should be usable in expressions meaning that a function call can be replaced by the value it returns. A function should always use input parameters. After the parameter list, the return data type is defined. In the function body, the sequence of statements should include a RETURN statement to generate the function’s output value.
30. What are the two kinds of cursor declaration in PL/SQL?
Ans: An implicit cursor is declared as part of a FOR statement. An implicit cursor can be used only inside the FOR statement. An explicit cursor is declared using the CURSOR statement.
31. What is the difference between a static and a dynamic cursor in PL/SQL?
Ans: PL/SQL supports static cursors in which the SQL statement is known at compile-time as well as dynamic cursors in which the SQL statement is not determined until run-time.
32. What is a cursor attribute?
Ans: Cursor attributes indicate the status of a cursor. Commonly used cursor attributes are Found, NotFound, IsOpen, and RowCount.
33. How are cursor attributes referenced?
Ans: When used with an explicit cursor, the cursor name precedes the cursor attribute. When used with an implicit cursor, the SQL keyword precedes the cursor attribute. For example, SQL%RowCount denotes the number of rows in an implicit cursor. The implicit cursor name is not used.
34. What is the purpose of a PL/SQL package?
Ans: Packages support a larger unit of modularity than procedures or functions. A package may contain procedures, functions, exceptions, variables, constants, types, and cursors. By grouping related objects together, a package provides easier reuse than individual procedures and functions.
35. Why separate the interface from the implementation in a PL/SQL package?
Ans: A package separates a public interface from a private implementation to support reduced software maintenance efforts. Changes to a private implementation do not affect the usage of a package through its interface.
36. What does a package interface contain?
Ans: A package interface contains the definitions of procedures and functions along with other objects that can be specified in the DECLARE section of a PL/SQL block. All objects in a package interface are public.

37. What does a package implementation contain?
Ans: For each object in the package interface, the package body must define an implementation. In addition, private objects can be defined in a package body. Private objects can be used only by referenced in the package body. External users of a package cannot access private objects.
38. What is an alternative name for a trigger?
Ans: Because a trigger involves an event, a condition, and a sequence of actions, it also is known as event-condition-action rule.
39. What are typical uses for triggers?
Ans: Triggers are used for complex integrity constraints, transition constraints, update propagation, exception reporting, and audit trails.
40. How does SQL:2003 classify triggers?
Ans: SQL:2003 classifies triggers by granularity, timing, and applicable event. For granularity, a trigger can involve each row of an SQL statement or the entire SQL statement. Row triggers are more common than statement triggers. For timing, a trigger can fire before or after an event. Typically, triggers for constraint checking fire before an event, while triggers updating related tables and performing other actions fire after an event. For applicable event, a trigger can apply to INSERT, UPDATE, and DELETE statements. Update triggers can specify a list of applicable columns.
41. Why do most trigger implementations differ from the SQL:2003 specification?
Ans: Because the SQL:2003 trigger specification was defined in response to vendor implementations, most trigger implementations vary from the SQL:2003 specification. Most DBMSs support the spirit of the SQL:2003 trigger specification in trigger granularity, timing, and applicable events but do not adhere strictly to the SQL:2003 trigger syntax.
42. How are compound events specified in a trigger?
Ans: You use the OR keyword to define a compound event.
43. How are triggers tested?
Ans: Triggers unlike procedures cannot be tested directly. Instead, you use SQL statements that cause the triggers to fire.
44. Is it preferable to write many smaller triggers or fewer larger triggers?
Ans: There is no clear preference for many smaller triggers or fewer larger triggers. Although smaller triggers are easier to understand than larger triggers, the number of triggers is a complicating factor to understand interactions among triggers.

45. What is a trigger execution procedure?
Ans: A trigger execution procedure specifies the order of execution among the various kinds of triggers, integrity constraints, and database manipulation statements. Trigger execution procedures can be complex because the actions of a trigger may fire other triggers.
46. What is the order of execution for various kinds of triggers?
Ans: Oracle and SQL:2003 execute triggers in the order of BEFORE STATEMENT, BEFORE ROW, AFTER ROW, and AFTER STATEMENT. An applicable trigger does not execute if its WHEN condition is not true. For overlapping triggers, the execution order is arbitrary.
47. What is an overlapping trigger? What is the execution order of overlapping triggers?
Ans: Two triggers with the same timing, granularity, and applicable table overlap if an SQL statement may cause both triggers to fire. For overlapping triggers, Oracle specifies that the execution order is arbitrary. For SQL:2003, the execution order depends on the time in which the trigger is defined. Overlapping triggers are executed in the order in which the triggers were created. You should not depend on a specific firing order for overlapping triggers.
48. What situations lead to recursive execution of the trigger execution procedure?
Ans: Data manipulation statements in a trigger and actions on referenced rows can lead to recursive execution.
49. List at least two ways to reduce the complexity of a collection of triggers.
Ans: Four ways to control complexity are listed below:

• Do not use data manipulation statements in BEFORE triggers.
• Limit data manipulation statements in AFTER triggers to statements that are likely to succeed.
• For triggers that fire on UPDATE statements, always list the columns to which the trigger applies.
• Ensure that overlapping triggers do not depend on a specific order to fire.
• Be cautious about triggers on tables affected by actions on referenced rows. These triggers will fire as a result of actions on the parent tables.

50. What is a mutating table error in an Oracle trigger?
Ans: In trigger actions, Oracle prohibits SQL statements on the table in which the trigger is defined or on related tables affected by DELETE CASCADE actions. The underlying trigger table and the related tables are known as mutating tables. If a trigger executes an SQL statement on a mutating table, a run-time error occurs.
51. How are mutating table errors avoided?
Ans: On most triggers, you should access the new and old data rather than use an SQL statement to access a mutating table. In specialized situations, you must redesign a trigger to avoid a mutating table error. One solution involves a package and a collection of triggers that use procedures in the package. The package maintains a private array that contains the old and new values of the mutating table. Typically, you will need a BEFORE STATEMENT trigger to initialize the private array, an AFTER ROW trigger to insert into the private array, and an AFTER STATEMENT trigger to enforce the integrity constraint using the private array. Another solution involves a view and an INSTEAD trigger.
52. What are typical uses of BEFORE ROW triggers?
Ans: BEFORE ROW triggers are typically used for complex integrity constraints and transition constraints. BEFORE ROW triggers also can be used to standardize data entry practices such as converting values to upper case.
53. What are typical uses of AFTER ROW triggers?
Ans: AFTER ROW triggers are typically used for update propagation, exception reporting, and audit trails.
54. What is the difference between a hard constraint and a soft constraint?
Ans: In a hard constraint, a violation of the constraint causes failure of the associated SQL statement. In a soft constraint, a violation of the constraint does not cause failure of the associated SQL statement. For example, a hard constraint for a price change of more than 10% causes the update to fail if price is changed more than 10%. In contrast, a soft constraint results in a row written to an exception table instead of statement failure.
55. What kind of trigger can be written to implement a soft constraint?
Ans: An AFTER ROW constraint is normally used to implement a soft constraint. The AFTER ROW constraint can insert a row into an exception table and alert a database administrator or other user about the exception.
56. How does the Oracle trigger execution procedure differ from the SQL:2003 execution procedure for recursive execution?
Ans: In the Oracle execution procedure, steps 2.1 and 2.4 may involve recursive execution of the procedure. In the SQL:2003 execution procedure, only step 2.4 may involve recursive execution because SQL:2003 prohibits data manipulation statements in BEFORE triggers.