Database Management Systems
Download
Report
Transcript Database Management Systems
Database Management Systems
Chapter 6
Forms and Reports
Jerry Post
Copyright © 2003
1
D
A
T
A
B
A
S
E
Uses of Forms
Collect Data
Display Query Results
Display Analysis and Computations
Switchboard for other Forms and
Reports
Direct Manipulation of Objects
Graphics
Drag and Drop
2
D
A
T
A
B
A
S
E
Human Factors Design
User Control
Match user tasks.
Application responds to user
control & events.
User customization
Consistency
Layout, Design & colors
Actions
Clarity
Organization
Purpose
Terminology
Aesthetics
Art to enhance, graphics
Sound
Feedback
Methods
Visual
Text
Audio
Uses
Acceptance of input
Changes to data
Completion of tasks
Events / Activation
Forgiveness
Anticipation and correction
of errors
Confirmation on delete and
updates
Backup and recovery
3
D
A
T
A
B
A
S
E
Windows Interface Standards
The Windows Interface: An
Application Design Guide
(Microsoft)
Navigation and Choices
Mouse, Icons
Keyboard, Short-cuts
Menus
Selections from a list
Single
Contiguous Multiple
Disjoint Multiple
Manipulation
Activation
Drag and Drop
Feedback
Progress indicators and
status gauges
Flashing
Tool tips
Status bar
3-D controls
Message boxes
Focus
Outline box
Cursor
4
D
A
T
A
B
A
S
E
Windows Interface
Window components
Frame (sizing)
Title bar
Control-menu box
Buttons
Minimize
Maximize
Close
Scroll box (thumb)
Scroll bar
5
D
A
T
A
B
A
S
E
Windows Menus
Menus
Drop-down
Short Cut Keys
Mnemonic character
Pop-up (as needed)
6
D
A
T
A
B
A
S
E
Message Box (A Simple Form)
Message Boxes
Title
Message
Simple buttons
Icons
Modal (required)
7
D
A
T
A
B
A
S
E
Interface / Accessibility
Multiple Input Methods
Keyboard
Mouse
Voice
Multiple Output
Visual
Sound
Color
Some Suggestions:
Beware of Red/Green.
Avoid requiring rapid
user responses.
Avoid rapid flashing on
the screen.
Give users customization
options.
Volume
Color
Typefaces & Fonts
8
D
A
T
A
B
A
S
E
Form Layout
Types of Forms
Tabular
Single Row
Sub-forms (one-to-many)
Switchboard
Controls
Form Properties
Form Events
Form
Order
Items
11
7
13
Dog
Dog
Cat
5
1
2
9
D
A
T
A
B
A
S
E
Tabular Form
Works best for single table.
Designer can control data
entry sequence.
Probably include buttons for
sorting.
10
D
A
T
A
B
A
S
E
Single Row (Columnar) Form
Data for only one row.
Designer can set optimal
layout.
Similar in appearance to
paper forms.
Can use color, graphics, and
command buttons to make
the form easier to use.
Note the importance of the
navigation buttons.
Probably want a Find
command.
Useful to include subforms.
11
D
A
T
A
B
A
S
E
Sub-Forms
Typically a one-to-many relationship.
Subform contents are linked to the main form through a common
column (not displayed on the subform.)
Can have multiple subforms (Independent or Nested).
12
D
A
T
A
B
A
S
E
Switchboard Form
Blank Form
Graphics/Picture/Backgroun
d
Identify User
Choose Task.
13
D
A
T
A
B
A
S
E
Menu Design
Main Menu
Customer Information
1.
Setup Choices
Daily Sales Reports
2.
Data Input
Friday Sales Meeting
3.
Print Reports
Monthly Customer Letters
4.
DOS Utilities
5.
Backups
Hard to understand
Exit
Organized by user tasks.
14
D
A
T
A
B
A
S
E
Menus
Consistency
With operating environment
Within project
Pull-down
Name, Action
Shortcut keys
Access keys (&File, File)
Breaks/groups (-)
Dimmed option
Check mark
Pop-up
Miniature form
Tied to location/pointer
Right-mouse button
Attribute settings
Modal (keeps focus) or not
Submenus (>)
Logical groupings
Tradeoff: length v depth
Form indicator (…)
15
D
A
T
A
B
A
S
E
Queries
Queries are used to
automatically look up data.
e.g., Customer name
e.g., Product description
Be very careful when using
queries.
Each form should store data
in only one table.
For multiple tables, use a
subform or separate forms.
Usually Lock the look up
data so it cannot be
changed accidentally.
16
D
A
T
A
B
A
S
E
Form Query Example
Clerk enters a CustomerID.
Stored in the Order table.
Query joins Sale and
Customer.
Automatically matches the
CustomerID.
Matching name is displayed
on the form.
Do not include the join column
(CustomerID) from the look up
table (Customer).
Customer Order
SaleID 1234
Date 7/25/01
CustomerID 17
Carly
Embry
17
D
A
T
A
B
A
S
E
Form Query: Underlying Tables
Customer Order
SaleID 1234
Date 7/25/01
CustomerID 17
Carly
Embry
Data
entry
Data
display
Customer
Sale
SaleID
1232
1233
1234
CustomerID
23
74
17
Sdate
7/24/01
7/24/01
7/25/01
Query
Join
CustomerID
15
16
17
First
Last
Connie Fisher
Rosie Wade
Carly
Embry
18
D
A
T
A
B
A
S
E
Form Properties (Some)
Data
Base Table / Query
Filters
Sort
Integrity
Edits
Additions, Deletions
Locks
Other
Format
Caption
Scroll Bars
Record Selectors
Navigation Buttons
Size and Centering
Background/Pictures
Colors
Tab Order
Pop-up menus
Menu Bar
Help
19
D
A
T
A
B
A
S
E
Controls on Forms (Basic)
Label
Drop down list
or combo box
Text box
Last Name
Clothing
Shoes
Electronics
Country
Payment Method
Credit Card
Check
Cash
Option button
List box
Check box
Options
x Gift wrap
x Gift card
Monogram
Sales
Command
button
20
D
A
T
A
B
A
S
E
Pictures
Background pictures
Unbound, unchanging.
Stored with the form.
Keep edit screen readable.
Sizing (zoom, scale, clip).
Pictures stored as data
Bound to a data column.
Define column as object.
Tie to scanner or graphics
package through OLE.
Beware of data size
Employee
Name: Che Zhang
ID: 3354
Phone: 222-111-1524
...
Photo:
Resolution
Number of colors
User machine capabilities.
21
D
A
T
A
B
A
S
E
Basic Controls
Label
Text Box
Option Group
(single response)
Command
Button
Combo Box
(click arrow to open)
List Box
(always open)
22
D
A
T
A
B
A
S
E
Combo & List Boxes
User selects from a list
Combo box can enter new
data, or restrict to list.
Two basic uses:
Insert a value into a table
Choose from a list of
preset options, e.g. gender.
Select from a different
table, e.g., choose a
customer.
Find the data record in this
form that matches the
choice.
Be careful! Many systems
do not distinguish between
the two uses (enter data
and search).
Example when you want to
use data entry:
On a sales form, use a
combo box for customer.
It takes a value from the
Customer table and inserts
the ID into the Sale table.
Example when you want to
use a search:
On a Customer edit form,
you might use a combo to
search the Customer table.
Be sure the combo is not
bound to the table!
Probably need to write code
for search.
23
D
A
T
A
B
A
S
E
Combo Box
ControlSource sets the
column to receive the choice
(in the Sale table)
RowSource generates the
list of data to display.
Uses standard SQL.
Note 4 columns displayed.
First column is the one to
store in the data table.
Properties
Name
CustomerID
ControlSource
CustomerID
Format
DecimalPlaces Auto
InputMask
RowSource/Type Table/Query
RowSource
SELECT . . .
ColumnCount
4
ColumnHeads
No
ColumnWidths
...
BoundColumn
1
SELECT Customer.CustomerID,
Customer.LastName,
Customer.FirstName,
Customer.Phone FROM Customer
ORDER BY Customer.LastName;
24
D
A
T
A
B
A
S
E
Combo Box Sources
Microsoft Access supports
three methods:
Fixed list.
Query from a table.
Defined function.
With some systems (e.g.,
Visual Basic), you write code
to generate each list entry.
You might use a fixed list for
simple lists like “male”,
“female”, “unknown”.
It is better to query from a
table, even for simple lists.
Use a one column table.
Easier to add to a table than
to change a combo box.
Useful feature of list combo
box.
The Row Source property is
a text string.
This string can be
generated by code.
List entries can be changed
in response to user actions.
Programmed function.
For straightforward cases, it
is easier to use a fixed list
and just change the text.
More complex cases, you
can write a subroutine that
generates the list choices
following a specific format.
25
D
A
T
A
B
A
S
E
Controls on Forms (Complex)
Calendar
Common
Tab
Tab
Grid
Calendar
Gauge
Grid
Slider
Spin Box
Additional
Purchase
Create your own (C++)
Gauge
Slider
Spin box
26
D
A
T
A
B
A
S
E
Charts
Sale 1
Build a query that generates
the data to be graphed.
Sale 2
Numeric data
Individual series
Aggregate data
Sale 3
Animal
Animal
Animal
Merchandise
Merchandise
Merchandise
Labels
Columns to link to form.
Summary chart--unlinked.
Insert chart.
Set chart type.
Set up data and labels.
Set chart properties.
Verify size.
Total Sales
Merchandise
Animals
27
D
A
T
A
B
A
S
E
Multiple Forms
Sale
Edit
Customer
FirstName: Mary
LastName: Jones
Address: 123 Oaxaca Ave.
Customer
FirstName: Mary
LastName: Jones
Animals Purchased
Address: 123 Oaxaca Ave.
City: Los Angeles
Merchandise
ZipCode: 90086
Gender: Female
Age: 20
AccountBalance: $150
28
D
A
T
A
B
A
S
E
Multiple Forms
Animal
Using data on other forms
The forms object collection
Forms![FormName]![Control]
AnimalID
Subtotals and subforms
The form property
Forms![MainForm]![SubForm].
Form![Control]
Sale
=AnimalID from Animal form
Multi-page v Separate forms
Same recordset
Screen size
Side-by-side
ItemsSold
------------Subtotal=Sum(Price*Quantity)
=Forms!Sale!ItemsSold.Form!Subtotal
Subtotal
Tax
OrderTotal
=Subtotal*[TaxRate]
=Subtotal+Tax
29
D
A
T
A
B
A
S
E
Integrity
Avoid relying on forms
Set integrity conditions in
table definitions
Be sure to set referential
integrity (relationships)
Use forms to make it easy to
enter quality data
Combo/list boxes
Menus
Pop-up forms
Ties to related forms
Data transfer across forms
Computations
Error checking & trapping
Controls
Security rights
Data formats
Data entry
Round-off
Selectivity
Visible
Enabled & Locked
Example: no production
change after item is sold.
User assistance
Tool tips
Status bar
Menu
Help--context sensitive
30
D
A
T
A
B
A
S
E
Design Standards
Large Projects
Templates
Customer Order
Colors, layout
Titles
Actions, common buttons
Assembly
Naming convention is crucial
Order
form
Forms
Controls
Event procedures
Variables
Team Coordination
Menu design
Within a form/standards
Across an application
Event / action diagrams
Switchboard
form
Assembly
form
Order form
Item#
item not
available
Backorder
Notice
& Form
Customer#
State diagram
Scenario diagram/messages
large
customer
Customer
Discount
31
D
A
T
A
B
A
S
E
Objects
Scenario diagrams
Messages are usually
initiated by calling exposed
functions in an object
Data can be passed directly,
or made available by
exposing properties
Objects
Properties
Events
Messages
Message: compute
discount using
Customer ID
& Order size
Market
Pricing
Object
Customer
Order object
Message: discount pct
32
D
A
T
A
B
A
S
E
International Attributes
Language
Character sets and
punctuation marks
Sorting
Data formats
Date
Time
Metric v English
Currency symbol and format
Separators (decimal, . . .)
Phone numbers
Separators
International code prefix
Postal codes
National ID Numbers
33
D
A
T
A
B
A
S
E
Direct Manipulation of Objects
Kennel/
Orders
Current Choices
Customer
Bird
Cat
Tabby
Dog
Fish
Mammal
Reptile
Spider
Brown Lab
A graphical approach.
Minimize data entry.
Drag and drop objects (blue arrows).
34
D
A
T
A
B
A
S
E
Creating a Graphical Approach
Get the hardware.
Images: Scanners
Sound: Microphone and Sound card
Video: Camera and capture card
Lots of disk space.
High speed processors.
Add an object column to your table definition.
Design the screens.
Be creative.
Get user input.
Make the user’s job easier.
Avoid using graphics just for show.
Double-click
Drag-and-drop
Programming!
35
D
A
T
A
B
A
S
E
Oracle Forms
Use List of Values (LOV) instead of select boxes.
36
D
A
T
A
B
A
S
E
Oracle Forms Designer
37
D
A
T
A
B
A
S
E
Oracle Forms: Sales
Oracle provides
minimal support for
updateable queries,
so several items are
grayed out to indicate
they cannot be
changed here.
38
D
A
T
A
B
A
S
E
Oracle Forms: Sales Design
Two new
data blocks
are used for
the
repeating
sections.
39
D
A
T
A
B
A
S
E
Oracle Forms Design Hints
Displaying non-updateable data from other tables is tricky.
In Master/Sale set:
DML Data Target Type = Table
DML Data Target Name = Sale
For SaleID, set PrimaryKey = Yes
Add the other tables
Query Data Source Type = Table
Query Data Source Name (parentheses are critical!)
(SELECT Sale.columns, Customer.Columns, Employee.Columns
FROM Sale, Customer, Employee
WHERE (Sale.CustomerID = Customer.CustomerID)
AND (Sale.EmployeeID = Employee.EmployeeID))
40
D
A
T
A
B
A
S
E
Oracle Forms Hints
Add non-updateable columns by hand. Use aliases in the query to
ensure all column names are unique. Then set properties:
General – Name:
cLastName
Functional – Enabled
No (optional but clearer to the user)
Database – Database Item
Yes
Database – Column Name
cLastName
Database – Query Only
Yes
Database – Insert Allowed
No
Database – Update Allowed No
41
D
A
T
A
B
A
S
E
Report Design
Report usage/user needs.
Report layout choices.
Tabular
Columns/Subgroups
Charts/graphs
Paper sizes.
Printer constraints.
How often is it generated?
Events that trigger report?
How large is the report?
Number of copies?
Colors?
Security controls
Distribution list
Unique numbering
Concealed/non-printed
data
Secured printers
Transmission limits
Print queue controls
Output concerns
Typefaces
Readability
Size
User disabilities
OCR needs
42
D
A
T
A
B
A
S
E
Terminology
Page Layout
Facing pages (portrait)
Landscape v. portrait
Margins
Gutter (binding space)
Typefaces
Serif (Times New Roman)
Sans-serif (Arial)
Ornamental
Fixed width
Font size
common: 10 - 12 point
72 points approx. 1 inch
pica (1/6 inch) (12 points)
gutter
margins
Landscape
Alignment marks for
color separations.
43
D
A
T
A
B
A
S
E
Report Types: Tabular
44
D
A
T
A
B
A
S
E
Report Types: Labels
45
D
A
T
A
B
A
S
E
Report Types
Column.
Column with groups.
46
D
A
T
A
B
A
S
E
Report Layout
Report Header
Page Header
Group Header1
Group Header2
...
Detail
...
Group Footer2
Group Footer1
Page Footer
Report Footer
47
D
A
T
A
B
A
S
E
Report Layout/Common Use
Report Header
Title pages that are printed
one time for entire report.
Page Header
Title lines or page notes that
are printed at the top of
every page.
Group Header
Data for a group (e.g.,
Order) and headings for the
detail section.
Group Footer
Subtotals for the group.
Page Footer
Printed at the bottom of
every page--page totals or
page numbers and notes.
Report Footer
Printed one time at the end
of the report. Summary
notes, overall totals and
graphs for entire data set.
Detail
Innermost data.
48
D
A
T
A
B
A
S
E
Report Layout/Groups
Often use groups/breaks for Customer(C#, Name, …)
one-to-many relationships. Order(O#, C#, Odate, …)
Use a query to join all
OrderItem(O#, Item#, Qty, …)
necessary tables.
Can include all columns.
Use query to create
computed columns (e.g.,
Extended:Price*Quantity).
Avoid creating aggregates
or subtotals in the query.
Each one-to-many
relationship becomes a new
subgroup.
Report of Orders
Group1: Customer
H1: Customer name, address, …
Group2: Order
H2: Order#, Odate, Salesperson.
Detail: Item#, Qty, Extended
F2: Order total: Sum(Extended)
F1: Customer total orders:
Rpt footer: graph orders by customer
49
D
A
T
A
B
A
S
E
Report Computations
Query
Same row computations.
Extended=Price*Quantity
Report
Group subtotals.
Page and report totals.
Mixed, e.g., commission = rate * total
Scope depends on location
Group footer: subtotal
Page footer: page total
Report footer: report total
50
D
A
T
A
B
A
S
E
Report Graphs
Graphs
Separate query.
Detail
Locate in detail or group footer section.
Avoid aggregation and groups in query.
Include column that links to detail query in
report.
Subtotals and totals
Typically located in report footer or header.
Compare group totals
Relies on Group By and aggregation.
Be sure query groups match report groups.
51
D
A
T
A
B
A
S
E
Report Graph for Group
52
D
A
T
A
B
A
S
E
Oracle Report Writer: Preview
53
D
A
T
A
B
A
S
E
Oracle Report Writer: Design
54
D
A
T
A
B
A
S
E
Oracle Reports: Data View
The data view can be used to
create reports with complex data
structures. It is primarily used for
master/detail reports.
In this example, each supplier can
be sent many purchase orders,
which each contain many items
being ordered. The report can
produce group breaks on all three
sections.
55
D
A
T
A
B
A
S
E
Application Features
Application organization
Menu
Toolbar
Help
Transactions
Improving forms
Customized reports
Distributing Applications
File Edit Help
Customer
Report
File Edit Help
File Edit Help File Edit Help
Switchboard
Sales
Report
56
D
A
T
A
B
A
S
E
Application Design
Customer Form
Order Form
Bad design:
Customer
Order
1592
Jane Doe
333 Elm St.
Customer:
1592 Jane Doe
333 Elm St.
Enter data twice.
Poor design:
Memorize data (ID) on one
form to enter on second.
Better design:
Automatically transfer data
across forms.
57
D
A
T
A
B
A
S
E
Application Importance
User interface
Make users’ jobs easier.
Tie input forms and reports.
Automate basic tasks
Tie to external data
collection devices.
Help system.
Ensure data integrity
Validate data.
Perform computations.
Verify totals.
Control user access.
Maintain related
transactions.
Backup and recovery.
Decision Support
Monitoring of events.
Analysis, Graphs, Reports.
Statistical analysis and
optimization.
Forecasts and simulation.
Linking to other software.
Expert Systems &
Intelligence
Logic and forward chaining.
Analysis and decisions in
code.
Databases of cases,
situations and solutions.
58
D
A
T
A
B
A
S
E
Application Organization
Organized by user needs.
Identify user.
Outline tasks.
Organize forms and reports.
Direct users to tasks.
Potential drawbacks
Too many layers makes it
difficult for users to find
anything.
Poor organization confuses
users and requires
additional support and
training.
Build forms and reports.
Start with a core concept.
Identify most important
features. Get them correct.
Add features, forms and
reports. Issue application
updates--number and date!
Use menu stubs for
incomplete and future work.
Make them invisible to the
user with the Visible
property.
Be sure they are disabled.
59
D
A
T
A
B
A
S
E
Application Structure
Forms and Reports Front end
Visual Basic
Internet
Oracle Forms
Database
Oracle
SQL Server
DB2
Access
Back end
Middle Tier
(Optional)
If x > 10,000 Then
Else
Business logic
End If
Rules
60
D
A
T
A
B
A
S
E
User Orientation
Database application is a model
of the organization.
Applications based on user jobs.
Flexibility and user control.
Application organization
User tasks.
User control over sequence.
Forms
Minimize user entry.
Anticipation.
Reports
Easy access from forms.
User selection of scope and
conditions or filters.
61
D
A
T
A
B
A
S
E
Initial Menu / Switchboard
Starting point for users.
Identify the user.
From network if possible.
Separate log in if needed.
Customized for users.
Hide restricted options.
Different forms as needed.
Avoid cluttered screens.
Use graphics and color to
enhance the presentation.
Limit the number of options.
62
D
A
T
A
B
A
S
E
Switchboard Uses
Acts as a directory for the application.
Identifies users.
Contains startup and shutdown code.
Can preload forms in background.
Make them invisible.
Speed up later usage.
Can initiate transaction and security logs.
Can establish network connections.
Contains copyright and usage notes.
63
D
A
T
A
B
A
S
E
Sally’s Pet Store: Poor Organization
Order
Merchandise
Item
Receive
Merchandise
Item
What is wrong?
Focus needs to be at higher level
(Order, Receipt, Sale); not Item.A
Sell
Merchandise
Item
Get
Customer
Data
You cannot go from Order to Receipt.
You cannot go from Receipt to Sale.
You need to get customer data before
recording the sale.
64
D
A
T
A
B
A
S
E
Sally’s Pet Store: Better Organization
Supplier
Customer
Orders
Receipt
Sale
Inventory
Items
More links--usually as buttons.
Separate sales from orders,
except for special orders.
65
D
A
T
A
B
A
S
E
Sally’s Pet Store: Initial VTOC
Sales
Sale Animal
Animals
Sale Merchandise
Customers
Animal Health
Animal Genealogy
Suppliers
Purchase Animals
Purchase Merchandise
Customer Receipts
Supplier Payments
Inventory
Sales Report
Accounting
Cash Flow
Marketing
Accounts Payable
Employees
Accounts Receivable
66
D
A
T
A
B
A
S
E
Menus
Why a custom menu?
Limit user actions.
Simplify user interface.
Add custom actions.
Menus can be activated by
keystrokes.
Accessibility
Touch-typists and
heads-down data entry.
Sometimes need different
menus for each form.
File Help
Contents
Search
About Rolling Thunder
File Edit
Help
Add Customer
Delete Customer Ctrl+D
Modify Customer Data
67
D
A
T
A
B
A
S
E
Creating Menus
View | Toolbars | Customize
Drag and Drop
Multilevel menu.
Sublevels/hierarchy.
Each level is a separate menu with its
own name.
Menu choices
Each entry has a name.
Access key: & (e.g., &File).
Status Bar Text
Actions
Submenu.
Run any code.
68
D
A
T
A
B
A
S
E
Toolbars
Print
Why toolbars?
Single click for complex
actions.
Commands available across
Switchboard
the application / shortcuts.
Position and customization
by user.
Toolbar components
Button
Text
Icon/graphic (bitmap)
Tool Tip
Status Bar description
Action
·Identify report
·Ask for single or
multiple pages.
·Preview or print.
Weekly Sales Analysis
Build graphs
Print reports
Export data to spreadsheet
69
D
A
T
A
B
A
S
E
Menus and Toolbars
70
D
A
T
A
B
A
S
E
Creating Toolbars
View | Toolbars | New
Customizing
Add new button.
Select from DBMS list.
Bring up query/form/report.
Run code.
Change icon.
Modify existing icon.
Replace icon.
Create your own icon and paste it on the button.
Place text label on button.
Tool tips are vital.
Status bar for description.
71
D
A
T
A
B
A
S
E
Icons
16 by 16 pixels
16 colors
Bright and shaded
Dither to mix colors
Outline in black
72
D
A
T
A
B
A
S
E
Help
On-line help replaces manuals
Context sensitive:
Pressing F1 key provides
information on topic with
current focus
Hypertext links to related topics
Sequential topics
Descriptions
Examples
Definitions / Glossary
Contents / overview
Index / keywords
Full-text search
Windows 95 & Win-NT
Sally’s Pet Store--Contents
Copyright Notice
The Firm
Introduction
Processes
Entering Data
Sales
Animal Health
Breeds (and other terms)
74
D
A
T
A
B
A
S
E
HTML Help
Get the Microsoft HTML Help Workshop:
http://msdn.microsoft.com/library/tools/htmlhelp/
Create each of the following
Help project files
Use separate directory
HTML topic files
Standard HTML with some additions for keywords
Topic Header and Text File
Graphics and multimedia
Avoid monster sizes
Contents files
Can auto-generate from heading tags (<H1>, <H2>, …)
Index files
Use Help workshop to set keywords within each topic
75
D
A
T
A
B
A
S
E
HTML Help Workshop
76
D
A
T
A
B
A
S
E
HTML Project Hints
Project Options
Project Title
Default file (first page)
Can create new files with File - New
Be sure to Add/Remove Topic files to project list
Edit – Compiler Information to add keywords to HTML file
Concentrate on creating useful help content
On large projects, hire/train someone to manage help
Add useful features
Keep content up to date
Manage/organize all the files
77
D
A
T
A
B
A
S
E
Context-Sensitive Help
Set the help file name in the form properties.
Set the topic number (Context Id) for each form or control.
78
D
A
T
A
B
A
S
E
Context Sensitive HTML Help
Create a Topic file for pop-up
topics
.topic Filename1
Description
.topic Filename2
Description
Create a header file to link
the topic names to numbers
Use HTML API to set the
filenames
#define Filename1 10000
#define Filename2 20000
…
…
79
D
A
T
A
B
A
S
E
Appendix: Oracle PL/SQL: Data Types
Primary Data Types
NUMBER(precision, scale)
precision: Number of digits
scale: Round-off point
NUMBER(7,4): 123.4567
INTEGER
Default: NUMBER(4)
CHAR
Fixed length string
VARCHAR2
Variable length string
LONG, LONG RAW
Binary data
DATE
BOOLEAN
Yes/No
80
D
A
T
A
B
A
S
E
Appendix: Oracle PL/SQL Structure
CREATE OR REPLACE PACKAGE myPackage AS
PROCEDURE myProcedure(oldProjectID IN NUMBER);
END myPackage;
CREATE OR REPLACE PACKAGE BODY myPackage AS
DECLARE
myGlobalVar NUMBER;
PROCEDURE myProcedure(oldProjectID IN NUMBER) IS
DELCARE
myLocalVar NUMBER;
BEGIN
myLocalVar := oldProjectID;
IF
END IF
COMMIT;
END myProcedure;
End myPackage;
81
D
A
T
A
B
A
S
E
Appendix: PL/SQL Operators
Operator
**
*, /
+, -, ||
=, !=, <, >, <=, >=,
IS NULL, LIKE, BETWEEN , IN
NOT, AND, OR
Operation
Exponentiation
Multiply, Divide
Add, Subtract, Concatenate
Comparison
Logical operators
82
D
A
T
A
B
A
S
E
Appendix: PL/SQL IF-THEN-ELSE-ELSEIF
DECLARE
X NUMBER(10,2);
BEGIN
-- retrieve the balance
IF (BALANCE > 0) THEN
X = BALANCE*1.10;
ELSE
X = 0.0;
END IF;
END;
Watch the semicolons!
Use ELSEIF for case statements.
IF (ACCOUNT = ‘P’) THEN
-- do personal accounts
ELSEIF (ACCOUNT = ‘C’) THEN
-- do corporate accounts
ELSEIF (ACCOUNT = ‘S’) THEN
-- do small business
ELSE
-- handle error
END IF;
83
D
A
T
A
B
A
S
E
Appendix: PL/SQL Loops
(Start statement)
LOOP
…
WHILE (condition) LOOP
EXIT;
…
EXIT WHEN (condition);
END LOOP;
…
FOR (variable) IN low...high LOOP
END LOOP;
…
END LOOP;
84
D
A
T
A
B
A
S
E
Appendix: Procedures or Subroutines
PROCEDURE DropOldAccounts (CutDate DATE) IS
-- local variables are defined here
BEGIN
-- First copy the data to a backup table
INSERT INTO OldAccounts
SELECT * FROM Account WHERE AccountID NOT IN
(SELECT AccountID FROM Order WHERE
Odate > CutDate);
-- Copy additional tables…
-- Delete from Account automatically cascades to others
DELETE FROM Account WHERE AccountID NOT IN
(SELECT AccountID FROM Order WHERE
Odate > CutDate);
END DropOldAccounts;
85
D
A
T
A
B
A
S
E
Appendix: SQL Cursors
DECLARE
CURSOR c1 IS
SELECT Name, Salary, DateHired FROM Employee;
varTotal Employee.Salary%TYPE;
BEGIN
varTotal = 0;
OPEN c1;
FOR recEmp in c1 LOOP
varTotal := varTotal + recEmp.Salary;
END LOOP;
CLOSE c1;
-- Now do something with the varTotal
END;
86
D
A
T
A
B
A
S
E
Appendix: Error Handling
PROCEDURE myProc ( ) IS
DECLARE
-- declare all local variables
BEGIN
-- SQL statements here
EXCEPTION
WHEN OTHERS THEN -- you can specify a particular error
-- but OTHERS captures all errors
-- PL/SQL code to execute if an error arises
END myProc;
87