Oracle Reports培训教程20.doc

Size: px

Start display at page:

Download "Oracle Reports培训教程20.doc"

肯角钱
7 years ago
Views:

1 HAND : : : : MD060 : 1 : 1 2

2 MD /02/ ii

3 MD060...ii Date Model Layout Parameter Form Report Triggers Program Units...4 Attached Libraries...4 PL/SQL...4 PL/SQL iii

..4...4...4 Program Units...4 Attached Libraries...4 PL/SQL.

4 MD Reports Applications...4 Report...4 Application Reports...4 Reports...4 iv

5 MD060 Oralce Reports Hand 5

6 MD060 Oracle SQL PL/SQL Oracle 6

7 MD060 Developer Oracle Reports 2.5 Oracle Applications 11 Oracle Forms & Reports 6I 6 I Oracle Applications 11 I 2.5 6I 7

8 MD060 Oracle Reports2.5 Oracle Reports 1 Oracle Oracle 2 Developer/2000-Reports 2.5 8

9 MD060 3 Oracle network/admin SQLNET.ORA TNSNAMES.ORA SQL*NET Oracle SQLNET.ORA TRACE_LEVEL_CLIENT = OFF names.directory_path = (TNSNAMES) names.default_domain = world name.default_zone = world TNSNAMES.ORA SSE.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = ) (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = ) (Port = 1521) 9

default_domain = world name.default_zone = world TNSNAMES.ORA SSE.

10 ) MD060 (CONNECT_DATA = (SID = ora816) ) ) 4 Reports Designer File/Connect Oracle Reports 10

11 MD060 1 SQL VIEW CAT VIEW SQL> desc cat; TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) SELECT TABLE_NAME,TABLE_TYPE FROM CAT; 2 Reports Designer \ \Developer 2000 for NT\ Reports Designer Reports Designer File\Connect Connect 3 Date Model Date Model SQL Q_1Q_1 Q_1 Select Statement SELECT TABLE_NAME,TABLE_TYPE 11

$CAT; 2 Reports Designer \ \Developer 2000 for NT\ Reports Designer Reports Designer$

12 FROM CAT; MD060 OK 4 Layout Date Model Default Layout Style Tabular 12

13 MD060 Data/Selection Report Down Column TABLE_NAME Label TABLE_TYPE OK 13

14 MD060 5 File\Save test1.rdf 14

15 6 MD060 Object Navigator Run Run Report rdf rep 1 Object Navigator File \Administration\Generate rdf rep test1.rep 15

$\Administration\Generate rdf$

16 2 MD060 \ \Developer 2000 for NT\Reports Runtime Reports Runtime File \Connect File\Run rep Run Report Print Reports R untime Windows C:\ORANT\BIN \R25RUN32.EXE d:\test1.rdf system/manager@meng_prod Forms application Application Report Server Web 16

$untime Windows C:\ORANT\BIN \R25RUN32.EXE d:\test1.$

17 MD060 Date Model Queries Groups Columns Links Parameter Queries Groups Columns Links Parameter 17

18 MD SQL SELECT Tabular Mailing Label Form Letter

19 MD060 q_ g_ q_emp g_emp First Filter n Last Filter n PL/SQL PL/SQL PL/SQL PL/SQL PL/SQL 1 SELECT 19

20 MD060 RAW LONG RAW URL REF Read from File Yes Report Builder REPORT30_PATH Read from File No BMPCALSCGMGIFJFIF PCD PCXPICTRASTIFF 2 1 Formula Column SAL*1.2 SAL SAL+COMM SALCOMM PL/SQL CF_n PL/SQL PL/SQL 2 Placeholder Column PL/SQL CP_n n Before Reports PL/SQL 3 Summary Column Oracle Reports 20

21 MD060 1 Name 30 SELECT CF_n CP_n CS_n n Comment 64K Datatype CharacterDateLongLong Raw NumberRawRefVarcharVarchar2 Num ber Number Width Value if Null 2 Break Order PL/SQL PL/SQL Formula PL/SQL 3 PL/SQL PL/SQL Formula PL/SQL 4 Function Source Computer At Reset At Product Order Product Order 21

22 MD060 // SQL WHEREHAVINGSTART WITH SQL / SQL HAVING START WITHWHERE =< <=>>=<> 22

23 MD060 SELECT PL/SQL 11 BACKGROUN D COPIES YesNo No 1 CURRENTY DECIMAL. DESFOMAT DESNAME DfltPslan80 Dflt Dflt DESTYPE Fil Screen Screen Printer MODE ORIENTATIN PRINT JOB BitmapCharacter Landscape Portrait Landscape Portrait YESNO PRINT JOB Default Landsca pe Portrait YES THOUSANDS, SELECT WHERE SELECT SELECT SELECT 23

24 MD060 1 SQL PL/SQL : SELECT FROM 2 SELECT FROM WHERE GROUP BY ORDER BY HAVING CONNECT BY START WITH & PL/SQL PL/SQL 3 WHEREGROUP BYORDER BY HAVING CONNECT BYSTART WITH & SELECT 24

25 MD060 25

26 MD060 Layout 26

27 MD060 Header 27

28 Body Margin MD060 Frames Repeating Frames Fields Boilerplate Anchors Buttons OLE2 Oracle Graphics Frames M_n n Boilerplate 28

29 MD060 SELECT SELECT Reporting Frames / Source Print Drection / / Maximum Record per Page 3 Minimum Widow Records 3 Column Mode

30 MD060 / / Fields 1 &current date&logical page number &panel number &physucal page number &total logical pages &total panel &total physical pages CopiesBackground &fieldname Boilerplate 30

31 MD ok 4 GMG Oracle 31

32 MD GGMOracle URL Anchors F_

33 MD % 75% % 75% PL/SQL 33

34 MD060 PL/SQL PL/SQL PL/SQL OLE2 OLE2 OLE2 OLE2 OLE2 OLE2 OLE2 OLE2 OLE2 Insert Object OK OLE2 OLE2 Oracle Graphics Oracle Graphics Oracle Graphic Builder 8 Tabular 34

35 MD Form-like Mailing Label Form Letter Fieldname (Group Left) Group Above

36 MD Matrix Matrix Crosstab Reports Cross-Product Group Matrix With Group 36

37 MD060 Parameter Form 1 37

38 MD060 Report Triggers PL/SQL Before Parameter Form PL/SQL 38

39 After Parameter Form MD060 Befor Reort Between Pages After Report Oracle*Mail 39

40 MD060 Program Units PL/SQL PL/SQL 40

41 MD060 Attached Libraries PL/SQL PL/SQL PLL PL/SQL PL/SQL PLL Reports Designer 41

42 MD060 1 PL/SQL Libraries PL/SQL Libraries Program Units Attached Libraries 2 Program Units 3 4 PL/SQL PL/SQL PL/SQL Oracle 1 Attached Libraries Attach library 42

43 MD060 2 PL/SQL Find PL/SQL Attach PL/SQL 3 Yes REPORTS25_PATH Reports 2.5 REPORTS60_PATH Reports 6I No PL/SQL PL/SQL 43

44 MD select dept,job,sum(sal) from emp group by dept,job order by dept,job ; Q_1 G_1 44

45 4 G_1 DEPT JOB G_2G_3 MD060 5 G_2G_3 6 Matrix Date/Select G_3 Repeat Across 45

46 MD060 46

47 MD060 7 select disti nct dept from emp; 47

48 select distinct job from emp; MD060 select dept,job,sum(sal) from emp group by dept,job order by dept,job; G_1G_2 G_1 dept G_3 dept1 G_2 job G_3 job1 48

49 MD060 6 Matrix Date/Select G_2 Repeat Across dept1 job1 49

50 MD060 50

51 MD060 create table emp( dept varchar2(10), job varchar2(10), sal number) ; insert into emp(dept,job,sal) values('dept1','job1',10); insert into emp(dept,job,sal) values('dept1','job2',70); insert into emp(dept,job,sal) values('dept1','job3',60); insert into emp(dept,job,sal) values('dept2','job1',50); insert into emp(dept,job,sal) values('dept2','job2',40); insert into emp(dept,job,sal) values('dept2','job3',30); insert into emp(dept,job,sal) values('dept3','job1',10); insert into emp(dept,job,sal) values('dept3','job2',20); insert into emp(dept,job,sal) values('dept3','job3',100); commit; 51

52 MD060 52

53 MD060 Reports Applications Report Report Reports Designer Report Report FTP rdf reports CUX Application Reports / 53

54 MD060 / 54

55 Reports / / Report / /

56 MD060 56

6-1 Table Column Data Type Row Record 1. DBMS 2. DBMS MySQL Microsoft Access SQL Server Oracle 3. ODBC SQL 1. Structured Query Language 2. IBM

CHAPTER 6 SQL SQL SQL 6-1 Table Column Data Type Row Record 1. DBMS 2. DBMS MySQL Microsoft Access SQL Server Oracle 3. ODBC SQL 1. Structured Query Language 2. IBM 3. 1986 10 ANSI SQL ANSI X3. 135-1986