week 2 february 1
DESCRIPTION
Week 2 February 1. PL/SQL Creating a Form. Coding Standards. Element names (functions, objects, variables, etc.): 30-character maximum length Uppercase letters for keywords Lowercase letters for user-defined elements 80 characters per line One command per line Comments - PowerPoint PPT PresentationTRANSCRIPT
1
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Week 2Week 2February 1February 1
• PL/SQLPL/SQL• Creating a FormCreating a Form
2
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Coding StandardsCoding Standards
• Element names (functions, objects, variables, etc.): 30-Element names (functions, objects, variables, etc.): 30-character maximum length character maximum length
• Uppercase letters for keywordsUppercase letters for keywords• Lowercase letters for user-defined elementsLowercase letters for user-defined elements• 80 characters per line80 characters per line
– One command per lineOne command per line• CommentsComments
– Header comments: /* Header comments: /* comment comment */ */– Inline comments: -- Inline comments: -- commentcomment
• Prefix object namesPrefix object names
3
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
PL/SQLPL/SQLProcessing Language/SQLProcessing Language/SQL
• Procedural processing language for various Oracle tools:Procedural processing language for various Oracle tools:– FormsForms– ReportsReports– GraphicsGraphics
• Character set Character set – Alphabetic, numeric and special charactersAlphabetic, numeric and special characters– Arithmetic and relational operatorsArithmetic and relational operators– Others (;, ., :=, | |, --, /* */)Others (;, ., :=, | |, --, /* */)
4
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
PL/SQL BlockPL/SQL Block
Declaration SectionDeclaration Section
Executable Section Executable Section
Exception Section Exception Section
Declares variables and constants Declares variables and constants (optional).(optional).
Procedural Procedural programmingprogramming
Defines the exception Defines the exception handlers invoked for both handlers invoked for both predefined and user-defined predefined and user-defined exceptions (optional).exceptions (optional).
5
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
PL/SQL BlockPL/SQL Block
Declare sectionDeclare section
Exception sectionException section
Executable Executable sectionsection
6
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
PL/SQLPL/SQLDeclaration SectionDeclaration Section
• Declare variable and constant names and types Declare variable and constant names and types • Data typesData types
– Character (varchar2)Character (varchar2)– NumericNumeric– DateDate– BooleanBoolean
7
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Variable and Constant DeclarationVariable and Constant Declaration
• Defines the existence of all variables used in the procedureDefines the existence of all variables used in the procedure......declaredeclarevariable-namevariable-name data-typedata-type;;constant-nameconstant-name := := valuevalue;;– For example...For example...
declaredeclareline_count number;line_count number;product_request varchar2(10);product_request varchar2(10);max_count := 20;max_count := 20;
Data types
Constant
8
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
For Example...For Example...
declare declare product_request varchar2(3);product_request varchar2(3);product_descrip varchar(30);product_descrip varchar(30);price real;price real;cursor product iscursor product is
select product_description, product_msrp from productsselect product_description, product_msrp from productswhere manufacturer_code = product_request;where manufacturer_code = product_request;
......begin;begin;
open product;open product;looploopfetch product into product_descrip, price;fetch product into product_descrip, price;......close product;close product;
Order of variables Order of variables mustmust match the order in the select match the order in the select
Query assigned to “product”
9
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Executable SectionExecutable Section
• Types of statementsTypes of statements– AssignmentAssignment– Flow-of-controlFlow-of-control– SQLSQL– CursorCursor
• All statements All statements mustmust be terminated by a semi-colon (;) be terminated by a semi-colon (;)
10
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Exception SectionException Section
• Instructs PL/SQL how to handle particular exceptionsInstructs PL/SQL how to handle particular exceptions
exceptionexceptionwhen when exception-nameexception-name then then
PL/SQL statementsPL/SQL statements;;when when exception-nameexception-name then then
PL/SQL statementsPL/SQL statements;;end;end;
11
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Exception NamesException Names
• Predefined - Defined in OraclePredefined - Defined in Oracle– DUP_VAL_ON_INDEX (duplicate value on a unique DUP_VAL_ON_INDEX (duplicate value on a unique
index)index)– INVALID_NUMBER INVALID_NUMBER – NO_DATA_FOUND (no rows returned)NO_DATA_FOUND (no rows returned)– TOO_MANY_ROWS (multiple rows returned)TOO_MANY_ROWS (multiple rows returned)– VALUE_ERROR VALUE_ERROR
• User-defined - Defined in the declarations as an exception User-defined - Defined in the declarations as an exception (data type).(data type).
12
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Oracle DeveloperOracle Developer
• Forms (interactive)Forms (interactive)– Presenting information and entering data onlinePresenting information and entering data online
• Reports (reporting)Reports (reporting)– Page-oriented display of informationPage-oriented display of information
• Graphics (charts)Graphics (charts)– Graphic representation of dataGraphic representation of data
13
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
14
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Forms DesignerForms Designer
• Four major componentsFour major components– Object NavigatorObject Navigator– Layout editorLayout editor– Property palette (sheet)Property palette (sheet)– PL/SQL editorPL/SQL editor
15
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Object NavigatorObject Navigator
Forms Forms modulemodule with various with various objectsobjects
ModulesModules Modules:Modules:• FormsForms• MenuMenu• LibrariesLibraries• Built-in PackagesBuilt-in Packages• Database ObjectsDatabase Objects
16
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
ToolbarToolbarObject type HierarchyObject type HierarchySpecifies the order in Specifies the order in which objects and items which objects and items are executedare executed
MenusMenus
17
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Window and Canvas ObjectsWindow and Canvas Objects
WindowWindow
Canvas-ViewCanvas-View
Interface ItemInterface Item
Boilerplate objectsBoilerplate objects(lines, images, boxes, etc.)(lines, images, boxes, etc.)
Canvas-ViewCanvas-View
Four types of canvas-views: Four types of canvas-views: Content, Stacked, Horizontal Content, Stacked, Horizontal Toolbar, and Vertical ToolbarToolbar, and Vertical Toolbar
18
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Window-Canvas-Block-Items RelationshipWindow-Canvas-Block-Items Relationship
WindowWindow
Canvas-ViewCanvas-View
BlockBlock
ItemsItems
PL/SQLPL/SQL
Menu objectMenu object
19
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Layout EditorLayout Editor
Tool paletteTool palette
Tool barTool bar
Layout work areaLayout work area
RulersRulers
20
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Property PaletteProperty Palette
Properties of the canvasProperties of the canvas
Set an Set an object’s object’s
attributesattributes
21
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
TriggersTriggers
Trigger Trigger
22
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
PL/SQL EditorPL/SQL Editor
TriggerTrigger
List of List of triggerstriggers
PL/SQL codePL/SQL code
23
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Inheriting PropertiesInheriting Properties
Stereos to Go!Stereos to Go!
EnterEnter
ExitExit
Stereos to Go!Stereos to Go!
ViewView
ReportReport
GraphGraph
ExitExit
Class PropertiesClass Properties
24
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Creating a Property ClassCreating a Property Class
• Create a Property Class object in the parent moduleCreate a Property Class object in the parent module• Add the property and its valuesAdd the property and its values• Select the object in the childSelect the object in the child
– Under Under Subclass InformationSubclass Information,,specify the property class specify the property class
25
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Create the Property Class in the Create the Property Class in the ParentParent
26
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Add the Properties to the ClassAdd the Properties to the Class
Added to the classAdded to the class
27
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Inherit the Property Class in the Inherit the Property Class in the ChildChild
Select the object and its Select the object and its Property PaletteProperty Palette Select Select Subclass InformationSubclass Information Specify the object or property class name and its form Specify the object or property class name and its form
modulemodule
Parent moduleParent moduleProperty classProperty class
28
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
MenusMenus MenusMenus
Menu Menu itemsitems
File MenuFile Menu
29
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Menu ModuleMenu Module
Menu moduleMenu module
Menu objectMenu object
Menu itemsMenu items
Five Simple Steps:Five Simple Steps:Create the menu moduleCreate the menu moduleAdd the menu objectsAdd the menu objectsAdd the menu items to the menu objectsAdd the menu items to the menu objectsProgram the items in PL/SQLProgram the items in PL/SQLAttach the menu module to the form Attach the menu module to the form
modulemodule
30
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Why Multiple Forms?Why Multiple Forms?
• Build more functional units (forms)Build more functional units (forms)– Design the form to support a narrow scope of functionsDesign the form to support a narrow scope of functions– Reduce the complexity of a single formReduce the complexity of a single form
• Reduce the time to develop and implement an applicationReduce the time to develop and implement an application• Enhance maintainability of the applicationEnhance maintainability of the application
((plug-and-play!plug-and-play!))• Promotes reusability or Promotes reusability or cloningcloning
31
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Calling and Called FormsCalling and Called Forms
CallingCalling CalledCalled
• The The callingcalling form module form module transfers control (calls, opens)transfers control (calls, opens)to the to the calledcalled form form
CALL_FORMCALL_FORM((form-module-nameform-module-name,,displaydisplay,,switch-menuswitch-menu))
– Calling form remains presentCalling form remains presentbehind the called formbehind the called form
– Called module assumes the calling module’s menu Called module assumes the calling module’s menu modulemodule
32
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Calling and Called FormsCalling and Called Forms
• Upon exit of the called form,Upon exit of the called form,control returns to the calling control returns to the calling formmoduleformmodule
EXIT_FORMEXIT_FORM
CallingCalling CalledCalled
33
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Calling a Form ModuleCalling a Form Module
• Create or select an item (i.e., push button) on the canvas or Create or select an item (i.e., push button) on the canvas or in the menu module in the menu module
• Program the item in the PL/SQL EditorProgram the item in the PL/SQL Editor– Select a trigger that will activate the procedureSelect a trigger that will activate the procedure– Enter the Enter the CALL_FORM CALL_FORM statementstatement– Compile the codeCompile the code
• Test the form moduleTest the form module
34
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
Causes Oracle Forms to clear the calling form Causes Oracle Forms to clear the calling form from the screen before drawing the called form. from the screen before drawing the called form. HIDE is the default parameter. (NO_HIDE)HIDE is the default parameter. (NO_HIDE)
Form module nameForm module name Causes Oracle Forms to keep the Causes Oracle Forms to keep the default menu application of the calling default menu application of the calling form active for the called form. form active for the called form. (REPLACE)(REPLACE)
In the PL/SQL EditorIn the PL/SQL Editor
35
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento
NEW_FORMNEW_FORM
• NEW_FORM closes the calling formNEW_FORM closes the calling form
Greeting ScreenGreeting Screen
Main MenuMain Menu
QueryQuery
NEW_FORMNEW_FORM
EXIT_FORMEXIT_FORM
EXIT_FORMEXIT_FORMMenuMenu
CALL_FORMCALL_FORM
36
R. Ching, Ph.D. • MIS Area • California State University, SacramentoR. Ching, Ph.D. • MIS Area • California State University, Sacramento