Transcript E-business and The Future

Progress Training
MFG/PRO
Dec,14, 2007
SoftSpeed Consultant Ltd.
Add Month 2000
Agenda日程安排
<一>
<二>
<三>
<四>
<五>
<六>
<七>
Progress简介
数据类型和运算符
最简单的Progress程序
逻辑运算符和判断语句
循环结构
数组
过程与函数
www.softspeed.com.hk
Agenda日程安排
<八>
<九>
<十>
<十一>
<十二>
<十三>
<十四>
编程访问数据库
Database的创建
Database的备份及恢复
Database的启动文件和常用的实用工具
Cimload的使用
举例讲解标准的报表、维护、查询程序
Progress的实用编程技巧
www.softspeed.com.hk
<一> Progress 简介
1.1
1.2
1.3
1.4
Programming in Progress
Progress Files and Procedures
Starting A Progress Session
Using The Procedure Editor
PROGRESS属于4GL数据库语言，没有指针没有类也不用搞什么高级接口等
www.softspeed.com.hk
1.1 Programming in Progress
(Accessing A Database With Progress)
Application
Code
Data Dictionary
Procedure Editor
User Interface Builder
User Interface
Add
Del
ACCESS
DATABASE
Update
www.softspeed.com.hk
Order
Entry
1.1 Programming in Progress
(Progress Model For Data Access)
Memory
DATABASE
Record
Buffer
Screen
Buffer
Screen
Printer
File
www.softspeed.com.hk
User
1.1 Programming in Progress
(Progress Statements)
Sample:
define variable code like cm_addr.
for each cm_mstr no-lock:
display cm_addr .
end.
Prompt-for "Cust:" code with No-labels.
find first cm_mstr where cm_addr = Input code.
display cm_addr .
for each so_mstr where so_cust = cm_addr:
display so_nbr with 2 Columns.
for each sod_det where sod_nbr = so_nbr no-lock:
display sod_line sod_part sod_qty_ord.
end.
end.
www.softspeed.com.hk
1.2 Progress File And Procedure
(Database File Types)
.db
Database File
Main Database (Data)
Store the Table structure / Index / Data / Trigger .....
.lg
Event log File
Log File for Datebase
Startup/Shutdown database Will be Recorded
The format is TXT.
.bi
Before-Image
Buffer For Database
Like Buffer to Store the Data Before Write to Database.
If System fails, It can be use to Restore the Database.
.lk
lock File
Lock File For Database
Signifies that the database is use.
You must delete the Lock file , if system fail.
www.softspeed.com.hk
1.2 Progress File And Procedure
(Progress Program File Types)
.P
.I
Procedure File
Main Procedure
You Can Directly Run In MFG/PRO
Sub-Procedure
Call By Main Procedure
Include file
A file that is included in procedure during compilation
.W
User interface Program
Program file containing User Interface elements .eg. Dialog Box ..
.R
Compiled File
Can Directly Run in MFG/PRO
All .P file can create corresponding .R file
www.softspeed.com.hk
1.2 Progress File And Procedure
(Internal And External Procedures)
Comparing
Internal
Procedure
External
Procedure
Re-use
Use By Main
Program Only
Use by All
Program
Code-length
Relatively
short
Any length
Performance
Allocate
Memory
Needs to be
Loaded
www.softspeed.com.hk
1.2 Progress File And Procedure
(Include Files) .I File
MyProg.p
MyCode.i
DO:
….
Display record.
End.
www.softspeed.com.hk
DO:
….
Display record.
End.
1.3 Starting a Progress Session
(Progress UI Environments)
Progress.ini (Progress.svg)
Define the default Progress Environment.when
You startup Progress.
UI Environment
Environment File
MS Windows
PROGRESS.INI( \Dlc\Bin )
Character
PROTERMCAP
[ Fonts ] [Startup] [Propath]
www.softspeed.com.hk
1.3 Starting a Progress Session
(Progress Environment Variables)
Variable
Points to Path Name of ….
DLC
Progress Install Directory
Procfg
Configuration information Directory (C:/Dlc)
Promsgs Error Message File (C:\Dlc)
ProPath
www.softspeed.com.hk
Directory List for Process
Searches Procedure File
1.3 Starting a Progress Session
(Progress Propath)
Propath is a list of directories Progress uses to find
Procedures you in a session.
How To set Propath ?
1. Edit Configure file Progress.ini
2. Using the Propath Editor in PRO*Tools
3. In Progress Editor , Assign to Propath
www.softspeed.com.hk
1.3 Starting a Progress Session
(Database Connections)
Will Be Discuss In Progress
Administration Training Guide
www.softspeed.com.hk
1.4 Using The Procedure Editor
(How to Use Procedure Editor)
Key
Function
F2
Help
F1
Run
F7
Next Buffer
F9
Find
Show Buffer List
Multiple Procedure Windows
Checking Syntax
www.softspeed.com.hk
1.4 Using The Procedure Editor
(How to Use Compile Procedure)
 Using the Compile statment
Compile Procedure-name Save into Directory
 Using the Application compiler tool
 Using Application Tool MFG-UTIL
www.softspeed.com.hk
<二> 数据类型以及运算符
2.1
2.2
2.3
2.4
2.5
Progress的数据类型
常量与变量
运算符
表达式
一些特殊符号
www.softspeed.com.hk
2.1 Progress的数据类型
CHARACTER:
DATE :
DECIMAL:
HANDLE:
INTEGER:
LOGICAL:
www.softspeed.com.hk
X(8)
99/99/99
->>,>>9.99
>>>>>>9
->,>>>,>>9
yes/no
2.2 常量与变量
变量必须在第一次被使用之前定义，可以定义在程序的任何地方！
但是通常为了增加程序的可读性，变量定义都放在程序的开始处！
以下是变量定义的几个实例：
define variable str01 as char label "DemoString" initial "hello".
def var str02 like str01.
def var dt01 as date extent 5.
def var inte01 as inte format ">>>9".
说明，第一行：
str01 是变量名， 变量名不要与系统关键字重复，字符或者下划线
开头，比如strModel,_Model ；
char 表示变量类型，本例指字符型变量，其它类似的比如integer，
date，logical等 ；
label 就是后续程序中对变量的描述，比如需要用户输入这个变量
值时，系统提示“DemoString" ；
initial 变量的初始值
www.softspeed.com.hk
2.2 常量与变量
第二行：
def和var都是简写，def是define的简写，var是variable的简写；
PROGRESS支持语法简写，但是初学者最好先写全，后面有经验
了再简写。
like和as的不同点是：as后面直接说明变量类型，而like后面跟另外
一个变量或者字段。
第三行：
extent 5 ，表示该变量是数组变量。
第四行：
format ">>>9" 变量格式
指定变量的格式一个最大的好处就是预留宽度，这个对变量的输
入或者报表的输出都很有用的。
比如年份的宽度一定是4位的，那么你就可以指定格式 format
"9999"。
PROGRESS程序每行结束必须有个“.”号！！！
www.softspeed.com.hk
2.2 常量与变量
Define
variable
Assign
variable
Display
variable
www.softspeed.com.hk
Variable Buffer
Screen
Buffer
2.2 常量与变量
As Data type format format
Syntax :
Define variable var-name
init value Label “ABC“.
Like Field
Sample:
Define
Define
Define
Define
Define
variable
variable
variable
variable
variable
Var1
Var2
Var3
Var4
Var5
as
as
as
as
as
decimal
Character
integer
Character
logical
Data Type
www.softspeed.com.hk
decimals 1 label "Qty".
format "x(5)".
Extent 3 .
Format "Q 9" Extent 4 initial [1,2,3,4].
Format "Detail/Summary" initial "Detail".
2.2 常量与变量
Syntax :
Assign Variable = Value
Display Variable .
Sample:
Define variable Var1 as decimal
decimals 1 label "Qty".
Define variable Var2 as Character format "x(5)".
Assign Var1 = 1
var2 = “ABC”.
Display Var1 Space(10) skip(1) Var2.
www.softspeed.com.hk
2.2 常量与变量
Syntax :
SET
** Assign it to Record Buffer.
Record
Buffer
Prompt-for
Sample:
Screen
Buffer
** Save it to screen buffer ONLY.
Define variable name as character format "x(20)" label "Enter You Name".
Define variable greeting as Character format "x(27)".
Set name.
Assign greeting = "Hello," + name + "!".
/* Prompt-for name.
Assign greeting = "Hello," + Input name + "!".
*/
Display greeting.
www.softspeed.com.hk
2.3 运算符 & 2.4表达式
＋ － * / ，就是 加 减 乘 除了！比如：
ttl_amoount = ttl_ammount + dtl_amount.
x_a = x_b / x_c.
str_ttl = "I'm" + "sure!".
dt01 = today - 1.
dt01 = 3/22/2005. /* 日期 */
当然，日期和字符串不能做乘法或者除法！
www.softspeed.com.hk
2.5 一些特殊符号
Symbols
Role
/* */
Enclosing a comment
*
Multi Character wildcard
.
Terminating a statement
Delimiter for fully qualified field name
:
Terminating a block header or block label
?
Specifying the unknown value
“” Or ‘’
Enclosing of a character string
{filename}
Include file reference
[]
Define Array variable
www.softspeed.com.hk
<三> 最简单的Progress程序
3.1 Progress的三种程序结构
3.2 Progress中的输入和输出
3.3 使用代码连接或中断数据库以及
在数据库中怎样使用os的命令
www.softspeed.com.hk
3.1 Progress的三种程序结构
1. 顺序结构
2. 分支结构
If … then … else
3. 循环结构
Repeat … end
Do … end
www.softspeed.com.hk
3.2 Progress的输入和输出
Every procedure automatically gets one input stream and
one output stream.
• Input
Prompt-for, assign, update, set
Sample:
Repeat :
Prompt-for customer.cust_num.
find customer using cust_num.
display
name state credit_limit.
End.
www.softspeed.com.hk
3.2 Progress的输入和输出
Sample:
For each customer :
update name state credit_limt.
end.
find first customer where name = “AA” no-lock no-error.
if not availabel customer then do :
create customer.
assign
name = “AA”
state = “”
credit_limit = “”.
end.
www.softspeed.com.hk
3.2 Progress的输入和输出
2. Output
Display, put, output
Sample:
DEFINE VARIABLE f AS CHARACTER NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE j AS INTEGER NO-UNDO.
i = 0.
j = 0.
f = "out.txt".
OS-DELETE VALUE(f).
OUTPUT TO VALUE(f) APPEND.
www.softspeed.com.hk
3.2 Progress的输入和输出
FOR EACH gltr_hist:
i = i + 1.
IF i = 50001 THEN DO:
i = 1.
OUTPUT CLOSE.
j = j + 1.
f = "out_" + STRING( j) + ".txt".
OS-DELETE VALUE(f).
OUTPUT TO VALUE(f) APPEND.
END.
EXPORT DELIMITER "~011" gltr_hist.
END.
OUTPUT CLOSE.
www.softspeed.com.hk
3.3 使用代码连接或中断数据库以及在数据库中怎样使用os的命令
Syntax:
Connect Physical Database –ld logical Name No Error
Disconnect logical Name
No Error
Logical Name Physical Name
Sample:
DB Type
Connect E:\Database\mfgtrain.db -ld "testDB".
Display "Connect To" LDBName(1) PDBName(1) format "x(40)" DBType(1)
skip with width 100 .
Connect E:\Database\mfgtrain.db -ld "TrainDB".
Display "Connect To" LDBName(2) PDBName(2) format "x(40)" DBType(2)
skip with width 100 .
Display "Number of database connections:" Num-DBS.
Disconnect testDB.
Disconnect trainDB.
www.softspeed.com.hk
DB Counter
3.3 使用代码连接或中断数据库以及在数据库中怎样使用os的命令
Syntax:
Os-statement [SILENT ] os-specific-command
Sample:
OS-Create-DIR c:\temp\test.
DOS Silent Copy c:\*.txt c:\temp\test\*.txt.
Display Opsys.
www.softspeed.com.hk
<四> 逻辑运算符和判断语句
4.1
4.2
4.3
4.4
逻辑运算符
关系运算符
条件语句及Case语句
程序举例
www.softspeed.com.hk
4.1 逻辑运算符 & 4.2 关系运算符
Operators: + - * /
Logical Operators:
Not And Or
Comparison Operators: = <> < > <= >= Begins Matches
Precedence in Logical Operations:
1)
2)
3)
4)
www.softspeed.com.hk
()
Not
And
Or
4.1 逻辑运算符 & 4.2 关系运算符
Sample:
Define var x as integer init 5.
For each so_mstr:
find first sod_det where sod_nbr = so_nbr no-lock no-error.
if (not availabel sod_det and x <> 5) then do :
create so.
assign
sod_nbr = “”.
end.
www.softspeed.com.hk
4.3 条件语句及Case语句
判断最简单了，就是一个if 条件 then ... else ...，比如：
if str01 = "cpu" then disp "CPU"
else disp "Not CPU".
当然，这个”条件“可能有很复杂的组合。如果，涉及到
的动作比较多，那么做法是用do: ... end.，比如
if str01 = "cpu" then do:
str01 = "my cpu".
disp "CPU".
end.
else do:
str01 = "my memory".
disp "Not CPU".
end.
if 支持嵌套。
www.softspeed.com.hk
4.3 条件语句及Case语句
Syntax:
IF Condition THEN expression1 ELSE expression2.
IF Condition THEN DO:
Sample:
Statement1.
Statement2.
Connect E:\database\mfgtrain.db -ld mfgtrain.
if connected("mfgtrain") Then do:
END.
display "You Are Connected to :" DBName(1).
ELSE
display skip(1) LDBName(1) " Is a " DBTYPE(1)
end.
DO:
else
Statement3.
do:
Message "Cannot Connect to the database".
Statement4.
Pause.
END.
Quit.
end.
www.softspeed.com.hk
“DB".
4.3 条件语句及Case语句
Syntax:
Case expression:
When Value1 then Statement1.
When Value2 then Statement2.
When Value3 then Statement3.
Otherwise statement4.
End Case
Sample:
Define variable OS as character.
Assign OS= opsys.
Case OS:
When "UNIX" Then UNIX ls.
When "MSDOS" OR When "OS2" Then OS-Command Dir.
When "VMS" Then VMS Directory.
When "BTOS" Then BTOS "[Sys]<sys>files.run" files.
When "WIN32" Then display "Windows OS".
Otherwise Display OPSYS "is An Unsupported OS".
End Case.
www.softspeed.com.hk
<五> 循环结构
5.1 Do…End语句
5.2 Repeat…End语句及Leave语句
5.3 程序举例
www.softspeed.com.hk
5.1 Do: … End 语句
Syntax:
DO [Var = expression1 To expression2 [BY n]]:
statement.
END.
Sample:
Define variable i as integer.
Do i = 1 to 10 by 2:
Display i.
Pause.
End.
www.softspeed.com.hk
5.2 Repeat … End 语句及Leave语句
Syntax:
Repeat [Var = expression1 To expression2 [BY n]]:
statement.
END.
Sample:
Define variable i as integer.
Define variable count as integer format "99" initial 0.
Repeat i = 1 TO Num-entries(propath):
Display ENTRY(i, propath) format "x(45)".
Count = count + 1.
End.
Message "There are" count "items in the list".
www.softspeed.com.hk
5.2 Repeat … End 语句及Leave语句
Syntax:
Repeat [Var = expression1 To expression2 [BY n]]:
statement.
END.
Sample:
Define variable i as integer.
Define variable count as integer format "99" initial 0.
Repeat i = 1 TO Num-entries(propath):
/* Do I = 1 to xxx with down */
Display ENTRY(i, propath) format "x(45)".
Count = count + 1.
End.
Message "There are" count "items in the list".
www.softspeed.com.hk
5.2 Repeat … End 语句及Leave语句
Syntax:
Loop:
Do / Repeat [While expression]:
statements.
If xxx Then Leave loop.
Statements.
End.
Sample:
Define variable count as integer format "99" initial 0.
AA:
Repeat:
count = count + 1.
if count = 13 then do:
pause.
display "count = " count.
leave AA.
end.
end.
Display “Process OK”.
www.softspeed.com.hk
<六> 数组
6.1
6.2
6.3
6.4
一维数组的定义和引用
数字数组
字符数组
程序举例（略）
www.softspeed.com.hk
6.1 一维数组的定义和引用
Syntax:
DEFINE VARIABLE variable {AS datatype} [EXTENT n]
[INITIAL {constant|{[constant[,constant]...]}}]
[LABEL string[, string]...]
Sample:
Define variable v_month as integer extent 12
init [1,2,3,4,5,6,7,8,9,10,11,12]
label [“一”,“二”,”三”,”四”,”五”,”六”,”七”,”
八”,”九”,”十”,”十一”,”十二”]
www.softspeed.com.hk
6.2数字数组
Syntax:
DEFINE VARIABLE int_value AS INTEGER EXTENT 3 INITIAL [1, 2, 3].
Sample:
DEFINE VARIABLE int_value AS INTEGER EXTENT 3 INITIAL [1, 2, 3].
DEFINE VARIABLE i AS INTEGER.
DEFINE VARIABLE tot AS INTEGER LABEL "The total is".
DO i = 1 TO EXTENT(int_value):
tot = tot + int_value[i].
END.
DISPLAY tot.
www.softspeed.com.hk
6
6.3 字符数组
Syntax:
DEFINE VARIABLE int_value AS CHAR EXTENT 3 INITIAL [“1”,”2”, “3”].
Sample:
DEFINE VARIABLE int_value AS CHAR EXTENT 3 INITIAL [“1”,”2”, “3”].
DEFINE VARIABLE i AS INTEGER.
DEFINE VARIABLE tot AS INTEGER LABEL "The total is".
DO i = 1 TO EXTENT(int_value):
tot = tot + int_value[i].
END.
DISPLAY tot.
www.softspeed.com.hk
123
<七> 过程和函数
7.1
7.2
7.3
7.4
过程及其调用
函数极其调用
系统常用函数
程序举例
www.softspeed.com.hk
7.1 过程及其调用
Syntax:
Procedure procedure-name:
[Define Input/Output ] Parameter parameter as Datatype
Statement code.
End Procedure.
Define variable factorialResult as integer.
Sample:
Define variable factorialInput as integer.
Repeat:
Set FactorialInput.
RUN factorial (Input FactorialInput , Output FactorialResult).
Display FactorialResult.
End.
Procedure
Define
Define
Define
www.softspeed.com.hk
factorial:
input Parameter Pterm as integer.
Output Parameter FactorialResult as integer.
variable workingResult As Integer.
If Pterm <=1 Then Do:
FactorialResult = 1.
Return.
End.
Else do:
Run factorial (Input Pterm - 1,Output workingResult).
FactorialResult = Pterm * WorkingResult.
End.
End Procedure.
7.2 函数及其调用
Syntax:
FUNCTION function-name [RETURNS] data-type
[(param [, param ]...)]
Sample:
FUNCTION doubler RETURNS INTEGER (INPUT parm1
AS INTEGER).
RETURN (2 * parm1).
END FUNCTION.
/* Reference doubler() */
DISPLAY
"doubler(0)=" doubler(0) skip
"doubler(1)=" doubler(1) skip
"doubler(2)=" doubler(2) skip.
www.softspeed.com.hk
7.3 系统常用函数(一)
Function
Sample
String
Display String(1) + “A”
Begins
Display “ABCD” begins “AB”
Matches
Display "ABCD" Matches "A.C*D*"
Length
Display length("ABCD")
Index
Display Index("ABCD","C")
Substring Display substring("AVC",2,1)
Trim
Display Trim(" ABC ") + "D"
Fill
Display Fill(“A",10)
www.softspeed.com.hk
7.3 系统常用函数(二)
Function
Sample
Today
Display Today
Date
Display Date(10,13,2001)
Day
Display day(today)
Month
Display day(Month)
Weekday Display Weekday(today)
Year
Display Year(today)
Time
Display time
+
Display Today + 3
www.softspeed.com.hk
<八> 编程访问数据库
8.1
8.2
8.3
8.4
从数据库中返回数据
修改数据库中的数据
锁定与事务
怎样使用Frame作为显示模板
www.softspeed.com.hk
8.1 从数据库中返回数据 (Introduction)
How to Retrieving Data in Progress :
 Find
Retrieves ONE Record form database
Transaction “RCT-PO”
PO Receive Data
 For Each
Retrieves ONE or MORE records going forwards only in the set of recorders
Customer
ONE
ONE
ONE
www.softspeed.com.hk
Sales Order
TO
TO
TO
ONE
MORE
NONE
8.1 从数据库中返回数据
(Using Find to Retrieving Records)
Syntax :
Next
Prev
Last
Find First record-name [WHERE expression]
[USE-INDEX index-name][NO-ERROR]
IF Available( record-name ).
Sample :
What is Last Inventory Transaction
 Find last tr_hist.
Display tr_type tr_part tr_effdate tr_qty_chg.
 Find first tr_hist where tr_nbr = "xxx" and tr_type ="RCT-PO".
Display tr_part.
PO xxx Already Received
How to improve speed for data retrieving ?
www.softspeed.com.hk
8.1 从数据库中返回数据 (Using Find to Retrieving
Records)
Syntax :
Next
Prev
Last
Find First record-name [WHERE expression]
[USE-INDEX index-name][NO-ERROR]
Sample :
What is Last Inventory Transaction
 Find last tr_hist.
Display tr_type tr_part tr_effdate tr_qty_chg.
 Find first tr_hist where tr_nbr = "xxx" and tr_type ="RCT-PO".
Display tr_part.
PO xxx Already Received
How to improve speed for data retrieving ?
www.softspeed.com.hk
8.1 从数据库中返回数据
(Using Find Parameter ---- WHERE)
= , > , < ,<>,>=,<=,Begins,Matches
Syntax :
WHERE Variable / Field Begins string
WHERE Variable / Field Matches string (*ABC*)
Sample :
Find first prh_hist where prh_nbr begins “PO” no-error.
Display prh_hist
www.softspeed.com.hk
8.1 从数据库中返回数据
(Using Find Parameter ---- INDEX)
Wrong Index will lowest search speed!!!!
Index is a structure that holds sorted data from one or more filed.
An index on a field helps retrieve records faster when retrieval is
Based on that field.
How to find item”D”safty
stock fastest ???
Item
SS
A
INDEX
Item
SS
100
C
400
B
200
A
100
C
400
E
500
D
300
B
200
E
500
D
300
Syntax :
Find first table-name WHERE condition USE-INDEX index-name
Sample :
Find first pt_mstr Where pt_part = “D” USE-INDEX pt_part.
www.softspeed.com.hk
8.1 从数据库中返回数据
(Using Find Parameter----LOCK)
 Default is Share-lock.
 Only lock RECORD.
 NO-LOCK
To read a record even if another user has it EXCLUSIVE-LOCKed.
 EXCLUSIVE-LOCK
Other users cannot read or update a record that is EXCLUSIVE-LOCKed,
except by using the NO-LOCK option.
 SHARE-LOCK
Ther users can still read a record that is share locked,
but they cannot update it.
In Customization Report, Please use
no-lock
in Retrieving Data
Find first pt_mstr share-lock.
Find first pt_mstr share-lock.
update pt_desc1.
update pt_desc1.
For each pt_mstr EXCLUSIVE-LOCK. Find first pt_mstr share-lock.
update pt_part.
end.
www.softspeed.com.hk
display pt_part
8.1 从数据库中返回数据 (Using FOR EACH )
Use the FOR EACH statement when you want to retrieve a set of records and want to:
 Use the same selection criteria for all of these records.
 Loop through the list of records and process each of them in each iteration of the
FOR EACH block.
Syntax :
FOR EACH record-phrase WHERE condition ……:
Statement.
END.
Sample :
for each prh_hist no-lock:
display prh_rcp_date prh_receiver prh_nbr prh_part prh_rcvd .
end.
www.softspeed.com.hk
8.1 从数据库中返回数据
(Using FOR EACH Parameter - BY )
Sorting Records By Certain Field Values
 You Can Sorting By ONE field OR More fields
 With the DESCENDING option of BY you can specify the opposite sort order.
Can i know last receiver
information for specific PO
Sample :
for each prh_hist by prh_rcp_date desc by prh_nbr no-lock:
display prh_rcp_date prh_receiver prh_nbr prh_part prh_rcvd .
end.
www.softspeed.com.hk
8.1 从数据库中返回数据
(Using FOR EACH - Retrieving Data from Multiple Tables )
Syntax :
FOR EACH table-name1 , EACH table-name2 Where table-name1.field1 =
table-name2.Field1 NO-LOCK:
statement.
END.
DO YOU KNOW ?
Table-name.Field-name
Sample :
For each tr_hist ,each pt_mstr where tr_part = pt_part NO-LOCK:
display tr_type tr_part pt_desc1 pt_desc2 tr_effdate.
End.
www.softspeed.com.hk
8.1 从数据库中返回数据 (Using FOR EACH - Summary )
Syntax :
FOR EACH table-name BREAK BY field-name1 BY field-name2 NO-LOCK:
IF FIRST-OF(field-name1) Then statement1.
ACCUMULATE Qty ( TOTAL).
ACCUMULATE Qty ( TOTAL BY field-name1).
ACCUMULATE Qty * Price ( TOTAL BY field-name2 ).
statement2 .
IF LAST-OF(field-name1) Then Display ACCUM TOTAL BY field-name1 Qty.
IF LAST-OF(field-name2) Then Display ACCUM TOTAL BY field-name2 Qty * Price.
END.
Key Words
www.softspeed.com.hk





BREAK BY
FIRST-OF
LAST-OF
ACCUMULATE
ACCUM
8.1 从数据库中返回数据 (Using FOR EACH - Summary )
Sample :
Show PO Receive information
Order by ITEM and Receiver NO
FOR EACH tr_hist WHERE tr_type = "RCT-PO" ,EACH pt_mstr WHERE pt_part =
tr_part BREAK BY tr_lot BY tr_part :
If FIRST-OF(tr_part) Then Display pt_part pt_desc1 with frame a.
ACCUMULATE tr_qty_loc ( TOTAL ).
ACCUMULATE tr_qty_loc ( TOTAL BY tr_lot ).
ACCUMULATE tr_qty_loc ( TOTAL BY tr_part ).
Display tr_part tr_lot tr_qty_loc.
if LAST-OF(tr_part) Then display ACCUM TOTAL BY tr_part tr_qty_loc
label "SUM Part".
if LAST-OF(tr_lot) Then display ACCUM TOTAL BY tr_lot tr_qty_loc
label "SUM RE".
End.
www.softspeed.com.hk
8.2 修改数据库中的数据 (Changing Database Data )
Objectives :
 Create table (temp table)
 Create a new record in the database
 Change data records
 Delete existing database records
www.softspeed.com.hk
8.2 修改数据库中的数据
(Changing Database Data – Create temp table )
Syntax :
DEFINE [[NEW] SHARED] {WORK-TABLE|WORKFILE}
[NO-UNDO] [LIKE tablename][FIELD
datatype}|{LIKE
work-table-name
field-name {{AS
field }} [field-options]]...
Sample :
DEFINE WORK-TABLE ptmstr
FIELD ptpart
LIKE pt_part LABEL
“Part"
FIELD ptdesc1
LIKE pt_desc1 LABEL
“Desc1"
FIELD ptdesc2
AS character “x(40)” LABEL
www.softspeed.com.hk
“Desc2".
8.2 修改数据库中的数据
(Changing Database Data – Create a new record to table )
Syntax :
CREATE
table-name.
ASSIGN
CREATE BLANK RECORD
field-name1 = Variable1
ASSIGN DATA TO FIELD
field-name2 = Variable2.
Sample :
DEFINE WORK-TABLE ptmstr
FIELD ptpart
LIKE pt_part LABEL
“Part"
FIELD ptdesc1
LIKE pt_desc1 LABEL
“Desc1"
FIELD ptdesc2
AS character “x(40)” LABEL
CREATE ptmstr.
Assign ptpart =
display ptmstr.
www.softspeed.com.hk
“AAAA”.
“Desc2".
8.2 修改数据库中的数据
(Changing Database Data – Change Existing Data )
Syntax :
UPDATE Table-name / Field-name.
Sample :
DEFINE WORK-TABLE ptmstr
FIELD ptpart
LIKE pt_part LABEL
“Part"
FIELD ptdesc1
LIKE pt_desc1 LABEL
“Desc1"
FIELD ptdesc2
AS character “x(40)” LABEL
CREATE ptmstr.
Assign ptpart =
Update ptmstr.
www.softspeed.com.hk
“AAAA”.
“Desc2".
8.2 修改数据库中的数据
(Changing Database Data – Delete A Record )
Syntax :
Delete table-name / Field-name.
Delete one record ONLY
Sample :
DEFINE WORK-TABLE ptmstr
FIELD ptpart
LIKE pt_part LABEL
“Part"
FIELD ptdesc1
LIKE pt_desc1 LABEL
“Desc1"
FIELD ptdesc2
AS character “x(40)” LABEL
CREATE ptmstr.
Assign ptpart =
“AAAA”.
CREATE ptmstr.
Assign ptpart =
“BBBB”.
“Desc2".
FOR EACH ptmstr.
DELETE ptmstr.
END.
www.softspeed.com.hk
CLEAR TEMP TABLE RECORD
8.3 锁定与事务
(Multi-user Client/Server Session)
PROWIN32.EXE
Remote User
CONNECT
Remote User
CONNECT
CLIENT
Startup
SERVER
Local User
Local User
_mprosrv.exe
DATABASE
UNIX Environment
www.softspeed.com.hk
GUI Environment
8.3 锁定与事务
(LOCKING)
Sure, I Can send you 7
AAA Tomorrow!
Sales Person A
ITEM
QTY
AAA
10
Sales Person B
Sure, I Can send you 6
AAA Tomorrow!
www.softspeed.com.hk
8.3 锁定与事务 (LOCKING)
Sure, I Can send you 7
AAA Tomorrow!
Sales Person A
ITEM
QTY
AAA
10
Someone
order AAA ?
Sales Person B
I Can send you 3
AAA Tomorrow!
www.softspeed.com.hk
8.4 怎样使用Frame作为显示模板
Controlling the display with frames , including
Variable , field ......
Function :

Display Report Header/Foot

Display fixed format in Programming
Scope :
Where To Use ?
 Define Frame Statement
 Statements that display data , such as DISPLAY, SET, and so on
 DO Block header
 REPEAT block header
 FOR EACH block header
Syntax :
WITH [[expression] DOWN][Frame Frame-name]
[Title string][NO-BOX][NO-LABEL]......
www.softspeed.com.hk
8.4 怎样使用Frame作为显示模板
Sample:
Define variable ii as integer.
Display propath.
Do ii = 1 to Num-Entries(Propath) with down:
Display Entry(ii,Propath) Format "x(35)“ down.
ENd.
www.softspeed.com.hk
8.4 怎样使用Frame作为显示模板
Sample:
for each pt_mstr :
display pt_part with frame a side-label.
display pt_desc1 pt_desc2 with frame b .
end.
( Using Column in a Frame)
Sample:
for each pt_mstr :
display pt_mstr with 2 column.
end.
www.softspeed.com.hk
8.4 怎样使用Frame作为显示模板
Sample:
for each pt_mstr :
display pt_part with NO-BOX.
end.
( Specifying Exact Location for the Frame)
Sample:
for each pt_mstr :
display pt_part with Centered .
/*
display pt_part with column 15 ROW 5 .*/
end.
www.softspeed.com.hk
8.4 怎样使用Frame作为显示模板
Sample:
for each pt_mstr :
display pt_part pt_desc with Title “Item Master Maintenance”.
end.
(Controlling Layout of items Inside the Frame)
Sample:
for each pt_mstr:
display pt_part at 20 with frame a.
display pt_part to 20 with frame b.
display pt_part at column 20 row 2 with frame c.
end.
www.softspeed.com.hk
8.4 怎样使用Frame作为显示模板
Syntax:
DEFINE FRAME Frame-name
Variable / Field /SPACE[n]/SKIP[n]
WITH [side-labels].
DISPLAY ...... WITH FRAME Frame-name.
Sample:
define variable fname as character format "x(10)"
label "First Name" init "Sam".
define variable lname as character format "x(10)"
label "Last" init "Song".
define variable co
as character format "x(10)"
label "Company" init "Softspeed".
define frame f-name
fname colon 15
lname colon 15
co
colon 15
with side-labels row 1 centered .
define frame f-co
fname
at row 2 column 8
"Work at" at row 2 column 15
co
at row 2 column 25
with no-label centered no-box.
view frame f-name.
display fname lname co with frame f-name.
display fname co with frame f-co.
www.softspeed.com.hk
8.4 怎样使用Frame作为显示模板
Question :
Show following Data format specify format
Introduce
Introduce
My name is Jane X Doe
My name is
Jane X Doe
Answer :
define variable fname as char format "x(10)" init "Jane".
define variable mname as char format "x(1)" init "X".
define variable lname as char format "x(15)" init "DOE".
display "My Name is"
fname mname lname with no-label title "Introduction".
/* Display “My Name is“ at ROW 2 column 10
fname
at ROW 3 column 10 mname ROW 3 column 18
lname
at ROW 3 column 20 with no-label title “Introduction” */
www.softspeed.com.hk
<九> Database的创建
9.1 单卷数据库的创建
9.2 多卷数据库的创建
9.1 单卷数据库的创建
• Windows Version:
www.softspeed.com.hk
www.softspeed.com.hk
9.1 单卷数据库的创建
•
•
•
•
CHAR模式下命令：
Prodb /app/test.db /app/progress/empty.db
Windows下命令：
Prodb d:\test.db \c:\dlc\empty.db
• 只是路径不同而已
www.softspeed.com.hk
9.2 多卷数据库的创建
扩展名一定要为ST
• 9.2.1 先建立一个文本文件test.st,内容如下：
单位为K，大概500M容量
d f:\dg\test.d1 f 500000
d f:\dg\test.d2 f 500000
d f:\dg\test.d3 f 500000
d f:\dg\test.d4 f 500000
d f:\dg\test.d5 f 500000
d f:\dg\test.d6 f 500000
d f:\dg\test.d7 f 500000
d f:\dg\test.d8 f 500000
b f:\dg\test.b1
www.softspeed.com.hk
D—数据文件
B—BI文件
9.2 多卷数据库的创建
• 9.2.2 CHAR下的命令：
– 假如ST文件在/app/test.db下
– Prostrct create /app/test.db 或者
– Prostrct create /app/test.db /app/test.st
•
9.2.3 Windows下的命令：
– 假如ST文件在d:\test.db下
– Prostrct create d:\test.db 或者
– Prostrct create d:\test.db d:\test.st
www.softspeed.com.hk
<十> Database的备份及恢复
10.1 Procopy语句
10.2 Backup语句
10.3 Restore语句
www.softspeed.com.hk
10.1 Procopy语句
• Copies an existing database. Use this utility to do any of the
following:
• -• -• --
Copy a single-volume database to create another singlevolume database.
Convert a single-volume database to a multi-volume
database structure.
Convert a multi-volume database to a different multivolume structure.
www.softspeed.com.hk
10.1 Procopy语句
•
•
•
•
UNIX, Windows:
----------------------------------------------------------procopy source-db-name target-db-name
-----------------------------------------------------------
•
•
•
•
Example:
----------------------------------------------------------procopy d:\testold.db d:\testnew.db
-----------------------------------------------------------
www.softspeed.com.hk
10.2 Probkup语句
• Backs up a PROGRESS database (the database and
BI files).
• UNIX, Windows:
• ----------------------------------------------------------• probkup [on-line] db-name [incremental] devicename
•
• ----------------------------------------------------------www.softspeed.com.hk
10.2 Probkup语句
• Sample:
– Probkup online d:\test.db d:\bkup\onlinetest1027.db
此时要在test.db在多模式启动下，
也就是ERP在正常使用的情况下
才能操作成功
此时要在test.db没有被
启动下才能执行操作成
功
– Probkup d:\test.db d:\bkup\offlinetest1027.db
www.softspeed.com.hk
10.3 Prorest语句
• Restores a full backup of a database.
• UNIX, Windows:
• ----------------------------------------------------------• prorest db-name device-name
• Example:
– Prorest d:\test.db d:\onlinetest1027.db
– Prorest d:\test.db d:\offlinetest1027.db
www.softspeed.com.hk
<十一> Database的启动及常用的实用工具
11.1
11.2
11.3
11.4
11.5
Shutdown Database
Startup Database
Shutdown Parameter的详解
Startup Parameter的详解
Promon实用工具
www.softspeed.com.hk
11.1 Shutdown Database
11.1.1 Proshut.exe d:\test
_mprshut.exe d:\test -B 800 -L 500 -pf mfglang.pf –by
11.1.2 promon d:\test
回车后
选择 8
www.softspeed.com.hk
11.1 Shutdown Database
www.softspeed.com.hk
选择 2  等用户的指令已全
部写入数据库然后就Down掉，
这样能保证用户的数据完整
性。
选择 3  不管用户在做什么，
强制性Down掉数据库。
11.2 Startup Database
11.2.1 _mprosrv.exe d:\test
-H hostname -N TCP
www.softspeed.com.hk
-B 5000 -L 3000 -pf mfglang.pf -S erptest
代表后面要调
11.3 Shutdown Database 参数详解
用.PF文件作为参
数引入
_mprshut.exe d:\test.db -B 800 -L 1000 -pf mfglang.pf -by
Shutdown 数据
库所用的Buffer
大小
800实际大小是
800 * 4 K =
3.2KK = 3.2M
www.softspeed.com.hk
Shutdown 数据
库所用的LOG
大小
1000实际大小是
1000 * 4 K =
4KK = 4M
代表后面要调
用.PF文件作为参
数引入
11.4 startup Database 参数详解
_mprosrv.exe d:\test.db
-B 5000 -L 3000 -pf mfglang.pf -S erptest -H Hostname -N TCP
数据库的
服务名
startup 数据库
所用的Buffer 大
小
5000实际大小是
5000 * 4 K =
20KK = 20M
www.softspeed.com.hk
startup 数据库
The number of entries in
the record locking table.
If you specify a value
that is not a multiple of
32, Progress rounds the
value you specify to the
next highest multiple of
32.
主机名
_
11.5 Promon 实用工具
Starts the database monitor that displays database information.
UNIX, Windows:
----------------------------------------------------------promon db-name
-----------------------------------------------------------
www.softspeed.com.hk
11.5 Promon 实用工具
•
•
•
•
•
•
•
•
1用户控制
2等待和锁定的状态显示
3块访问情况
4正被锁定的表及记录
5数据库活动日志
6数据库共享资料情况
7数据库状态情况
8Shutdown数据库相关
www.softspeed.com.hk
<十二> Cimload的使用
1．CIMLOAD文件编写
@@batchload 程序名 /*必要的开头格式，指明要CIMLOAD的程序*/
“aaaaa” - - - - “bbb” /*输入字符类型aaaaa，再同一UPDATE的内容
情况下再输入四个默认的值，然后再输入字符类型bbb*/
“ccc”
/*在更改了UPDATE范围的情况下，再输入字符
类型ccc*/
.
/* . 表示退出 */
@@end
www.softspeed.com.hk
<十二> Cimload的使用
2．CIMLOAD的查错方法与步骤
a. 已执行36.15.2后，进36.16.3查看含错误信息
b. 假如有就表示一定含有错误，但显示为0也不代表完全正常
c. 当我们看见错误时要查看相对应的组表志，然后对CIMLOAD
的内容用手工在 MFGPRO中再输入一次，看错误是否可
以从演，通常的错误是：
(1) CIMLOAD内容含系统不认的字体和符号
(2) 所输入内容的长度超出系统字段的限制
(3) 所输入内容不符系统的规限的，譬如所输入的栏位
要输入合法的通用代码， 但实际输入栏 位输入了
不合法的通用代码，此时我们要先维护好数据库，
加入所必须的内容
www.softspeed.com.hk
<十二> Cimload的使用
d.当我们没看见错误信息时，则需要对CIMLOAD的内容与已
CIMLOAD的内容作对比，步骤如下：
(1) 连接CIMLOAD的数据库，获得CIMLOAD的TABLE名与
UNIQUEINDEX的字段名，进入PROGRESS的编辑器键
”select count(*) from table_name ”, 得到已CIMLOAD
的数据数目，对比要CIMLOAD的条数，假如符合就表示没
错误，假如不符则要做下面动作
(2) 在PROGRESS编辑器中键入”
output to 文件名
for each table_name:
display field_name1 field_name2 field_name.
end.”
(注意：field_name1+field_name2+field_name=table_name
的UNIQUE INDEX字段)
www.softspeed.com.hk
<十二> Cimload的使用
(3) 获取刚才产生的文件，用EXCEL打开，并将必要字段进行合并成
一个字段，对要CIMLOAD的内容也作相对应的字段合并工作，然后
用EXCEL的VLOOKUP函数对此进行比较得出非相同的内容，就可
查出CIMLOAD失败的内容
3. 下面讲解一个实例：
Sample:
for each vp_mstr where (vend = "" or vp_vend >= vend)
and (vend1 = "" or vp_vend <= vend1 )
and (part = "" or vp_part >= part)
and (part1 = "" or vp_part <= part1 ) :
usection = TRIM ( string(year(TODAY)) + string(MONTH(TODAY))
+ string(DAY(TODAY))) + trim(STRING(TIME))
+ trim(string(RANDOM(1,100))) .
www.softspeed.com.hk
<十二> Cimload的使用
<<接上页>>
output to value( trim(usection) + ".txt") .
v_price1 = vp_q_price .
v_price = vp_q_price *(100 + v_percent) / 100 .
v_price = v_price + v_values .
/* + - Price */
v_price = rounttook ( v_price ,v_decimal ).
display
if trim(vp_part) = "" then " - " else " " + """" + trim(vp_part) + """" format "x(21)"
if trim(vp_vend) = "" then " - " else " " + """" + trim(vp_vend) + """" format "x(11)"
if trim(vp_vend_part) = "" then " - " else " " + """" + trim(vp_vend_part) + """"
format "x(33)"
skip
if trim(vp_um) = "" then " - " else " " + """" + trim(vp_um) + """" + " " format "x(5)"
vp_vend_lead
if vp_tp_use_pct = yes then " Y " else " N "
vp_tp_pct
www.softspeed.com.hk
<十二> Cimload的使用
<<接上页>>
if trim(vp_curr) = "" then " - " else " " + """" + trim(vp_curr) + """" format "x(6)"
v_price
v_effdate
vp_q_qty
if trim(vp_pr_list) = "" then " - " else " " + """" + trim(vp_pr_list) + """" format "x(11)"
if trim(vp_mfgr) = "" then " - " else " " + """" + trim(vp_mfgr) + """" format "x(11)"
if trim(vp_mfgr_part) = "" then " - " else " " + """" + trim(vp_mfgr_part) + """" format "x(21)"
if trim(vp_comment) = "" then " - " else " " + """" + trim(vp_comment) + """" format "x(43)"
skip
"."
with no-box no-labels width 300.
output close.
input from value ( usection + ".txt") .
output to value ( usection + ".cim") .
{gprun.i ""ppvpmt.p""}
input close.
output close.
unix silent value("rm -rf " + trim(usection) + ".txt").
unix silent value("rm -rf " + trim(usection) + ".cim").
www.softspeed.com.hk
<十三> 举例讲解标准的报表、维护、查询程序
12.1 标准的报表程序
参见 ppptrp08.p 库存明细
12.2 标准的查询程序
参见 ppptiq04.p 零件数据查询
www.softspeed.com.hk
<十四> Progress的实用编程技巧
(Creating Alert Boxes)
 Message
Question
Information
Error
Warning
Syntax:
MESSAGE msg-string [VIEW-AS ALERT-BOX]
[BUTTONS YES-NO/YES-NO-CANCEL/OK/OK-CANCEL]
[TITLE string] [UPDATE] field AS LOGICAL/LIKE field ]
MESSAGE msg-string
Sample:
Message " Save file" VIEW-AS ALERT-BOX QUESTION
BUTTONS YES-NO-CANCEL
Title "SAVE AS" Update aa as logical .
display aa.
www.softspeed.com.hk
(Exercise 1 )
Question 1:
Allow the user to enter their full name, Create a monogram
And display it to the user.
Sample:
First Name
---------Jane
Answer:
Last Name
Middle Name
---------- ----------Doe
X
JXD
Monogram
-----------
JXD
How To Use Function
CAP / Substring
Define variable lname as char format "x(15)" label "Last Name".
Define variable fname as char format "x(25)" label "First Name".
Define Variable mi as char format "x(15)" label "Middle Name".
Define variable monogram as char format "x(3)".
Set fname lname mi.
Assign monogram = CAPS (substring(fname,1,1) + substring(mi,1,1) + substring(lname ,1,1)).
Display monogram .
www.softspeed.com.hk
(Exercise 2 )
Question:
User input a date , Calculate last date of this month.
Sample:
Input : 10/23/01
Output :
31
02/10/01
28
12/04/01
31
Answer:
Define variable aa as date label "Date".
Define variable lastdate as integer label "Last Date".
Aa = today.
update aa .
If month(aa) = 12 then
LastDate = Day( Date( 1,1,Year(aa) + 1 ) - 1).
Else
LastDate = Day( Date(month(aa) + 1,1,Year(aa)) - 1).
Display Lastdate.
www.softspeed.com.hk
How To Use Function
Date / Today /Month
Year / Day / +
(Exercise 2 )
Question:
User input a date , display day of week.
Answer:
Define variable aa as date init today label "Date" .
Define variable bb as char .
Update aa.
case
weekday(aa).
when 1 then bb = "Sunday" .
when 2 then bb = "Monday".
when 3 then bb = "Tuesday".
when 4 then bb = "Wednesday".
when 5 then bb = "Thursday".
when 6 then bb = "Friday".
otherwise bb = "Saturday".
end case.
display bb label "Week".
www.softspeed.com.hk
How To Use
Case
(Sql 标准语法 VS Progress 语法)
1. SELECT table1.field1, table1.field2, table2.field1, table2.field2, table3.field1, table3.field2
FROM table1, table2, table3
WHERE table1.field1=table2.field1 and table2.field1=table3.field1
and (table1.field2 > 1000 or table2.field2 like '%abc%' or table3.field2 in (1,5,8,9))
ORDER BY table1.field1
for each table1,
each table2,
each table3 where table1.field1=table2.field1 and table2.field1=table3.field1
and (table1.field2 > 1000 or table2.field2 matches "*" + "abc" + "*" or
(table3.field2 = 1 or table3.field2 = 5 or table3.field2 = 8 or table3.field2 = 9 ) )
no-lock break by table1.field1 :
display
table1.field1
table1.field2
table2.field1
table2.field2
table3.field1
table3.field2 .
end. /* for each table1 */
www.softspeed.com.hk
(Sql 标准语法 VS Progress 语法)
<<example:>>
for each pt_mstr,
each prh_hist,
each tr_hist where pt_part = prh_part and prh_part = tr_part
and pt_desc1 matches '*' + '°×' + '*'
no-lock :
disp
pt_part
pt_desc1
prh_nbr
prh_receiver
prh_rcvd
tr_nbr
tr_trnbr
tr_type
tr_qty_chg
.
end .
www.softspeed.com.hk
(Sql 标准语法 VS Progress 语法)
2.分组
SELECT table1.field1, table2.field1, SUM(table1.field2), AVG(table2.field2)
FROM table1, table2
WHERE table1.field1=table2.field1
GROUP BY table1.field1, table2.field1
for each table1,
each table2 where table1.field1=table2.field1
no-lock
break by table1.field1 by table2.field1 :
ACCUM table1.field2 (TOTAL BY table1.field1 by table2.field1 ).
ACCUM table2.field2 (AVG BY table1.field1 by table2.field1 ).
if last-of(table2.field1) then do:
display
table1.field1
table2.field1
(accumulate total by table2.field1 by table1.field2 ) label "SUM"
(accumulate avg by table2.field1 by table2.field2 )
label "AVG" .
end .
end .
www.softspeed.com.hk
(Sql 标准语法 VS Progress 语法)
<< Sample >>:
for each pt_mstr,
each prh_hist,
each tr_hist where pt_part = prh_part and prh_part = tr_part
and tr_part = '0001-0000'
no-lock
break by prh_receiver by tr_nbr :
ACCUM prh_rcvd (TOTAL BY prh_receiver by tr_nbr ).
ACCUM tr_qty_chg (AVG by prh_receiver by tr_nbr ).
if last-of( tr_nbr ) then do :
disp
prh_receiver
tr_nbr
(accumulate total by tr_nbr prh_rcvd ) label "SUM"
(accumulate avg by tr_nbr tr_qty_chg ) label "AVG" .
.
end.
end .
www.softspeed.com.hk
Thank you !
SoftSpeed Consultant Ltd
Add Month 2000
www.softspeed.com.hk