【免费】oraclepl/sqlprogramming资源-CSDN文库

5星 · 超过95%的资源需积分: 0 100 浏览量 2007-10-31 08:03:59 上传评论收藏 3.15MB PDF 举报

资源详情

资源评论

资源推荐

[Appendix A] What's on the Companion Disk?

Table of Contents

A. What's on the Companion Disk?..................................................................................................................2

A.1 Installing the Guide...........................................................................................................................2

...............................................................................................................................................................................3

A.2 Using the Guide................................................................................................................................3

...............................................................................................................................................................................5

B. Calling Stored Procedures from PL/SQL Version 1.1................................................................................6

B.1 Using Stubs to Talk to Server−Side PL/SQL....................................................................................7

...............................................................................................................................................................................9

B.2 Restrictions on Calling Stored Procedures........................................................................................9

B.2.1 No Server−Side PL/SQL Datatypes..................................................................................9

B.2.2 No Direct Stored Package Variable References..............................................................10

B.2.3 No Direct Remote Procedure Calls.................................................................................12

B.2.4 No Default Parameter Values..........................................................................................12

.............................................................................................................................................................................14

C. Built−In Packages........................................................................................................................................15

C.1 Using the Built−in Packages...........................................................................................................16

.............................................................................................................................................................................18

C.2 DBMS_ALERT...............................................................................................................................18

C.2.1 The REGISTER procedure.............................................................................................18

C.2.2 The REMOVE procedure................................................................................................18

C.2.3 The REMOVEALL procedure........................................................................................18

C.2.4 The SET_DEFAULTS procedure...................................................................................18

C.2.5 The SIGNAL procedure..................................................................................................18

C.2.6 The WAITANY procedure.............................................................................................19

C.2.7 The WAITONE procedure..............................................................................................19

.............................................................................................................................................................................20

C.3 Oracle AQ, the Advanced Queueing Facility..................................................................................20

C.3.1 DBMS_AQ (PL/SQL 8 Only).........................................................................................20

C.3.2 DBMS_AQADM (PL/SQL 8 Only)...............................................................................21

.............................................................................................................................................................................24

C.4 DBMS_DDL...................................................................................................................................24

C.4.1 The ALTER_COMPILE procedure................................................................................24

C.4.2 The ANALYZE_OBJECT procedure.............................................................................24

.............................................................................................................................................................................25

C.5 DBMS_ JOB...................................................................................................................................25

C.5.1 The BROKEN procedure................................................................................................25

C.5.2 The CHANGE procedure................................................................................................25

C.5.3 The INTERVAL procedure.............................................................................................25

C.5.4 The ISUBMIT procedure................................................................................................25

C.5.5 The NEXT_DATE procedure.........................................................................................26

C.5.6 The REMOVE procedure................................................................................................26

C.5.7 The RUN procedure........................................................................................................26

C.5.8 The SUBMIT procedure.................................................................................................26

C.5.9 The USER_EXPORT procedure.....................................................................................26

C.5.10 The WHAT procedure...................................................................................................26

.............................................................................................................................................................................28

C.6 DBMS_LOB (PL/SQL8 Only).......................................................................................................28

C.6.1 The APPEND procedure.................................................................................................28

C.6.2 The COMPARE function................................................................................................28

C.6.3 The COPY procedure......................................................................................................29

[Appendix A] What's on the Companion Disk?

Table of Contents

C.6.4 The ERASE procedure....................................................................................................29

C.6.5 The FILECLOSE procedure...........................................................................................29

C.6.6 The FILECLOSEALL procedure....................................................................................29

C.6.7 The FILEEXISTS function.............................................................................................29

C.6.8 The FILEGETNAME procedure.....................................................................................29

C.6.9 The FILEISOPEN function.............................................................................................30

C.6.10 The FILEOPEN procedure............................................................................................30

C.6.11 The GETLENGTH function.........................................................................................30

C.6.12 The INSTR function......................................................................................................30

C.6.13 The READ procedure....................................................................................................30

C.6.14 The SUBSTR function..................................................................................................31

C.6.15 The TRIM procedure.....................................................................................................31

C.6.16 The WRITE procedure..................................................................................................31

.............................................................................................................................................................................33

C.7 DBMS_LOCK.................................................................................................................................33

C.7.1 The ALLOCATE_UNIQUE procedure..........................................................................33

C.7.2 The CONVERT function................................................................................................33

C.7.3 The RELEASE function..................................................................................................34

C.7.4 The REQUEST function.................................................................................................34

C.7.5 The SLEEP procedure.....................................................................................................34

.............................................................................................................................................................................36

C.8 DBMS_MAIL.................................................................................................................................36

C.8.1 The SEND procedure......................................................................................................36

.............................................................................................................................................................................37

C.9 DBMS_OUTPUT............................................................................................................................37

C.9.1 The DISABLE procedure................................................................................................37

C.9.2 The ENABLE procedure.................................................................................................37

C.9.3 The GET_LINE procedure..............................................................................................37

C.9.4 The GET_LINES procedure...........................................................................................37

C.9.5 The NEW_LINE procedure............................................................................................37

C.9.6 The PUT procedure.........................................................................................................38

C.9.7 The PUT_LINE procedure..............................................................................................38

.............................................................................................................................................................................39

C.10 DBMS_PIPE.................................................................................................................................39

C.10.1 The CREATE_PIPE function.......................................................................................39

C.10.2 The NEXT_ITEM_TYPE function...............................................................................39

C.10.3 The PACK_MESSAGE procedure...............................................................................40

C.10.4 The PURGE procedure.................................................................................................40

C.10.5 The RECEIVE_MESSAGE function............................................................................40

C.10.6 The REMOVE_PIPE function......................................................................................40

C.10.7 The RESET_BUFFER procedure.................................................................................40

C.10.8 The SEND_MESSAGE function..................................................................................41

C.10.9 The UNIQUE_SESSION_NAME function..................................................................41

C.10.10 The UNPACK_MESSAGE procedure.......................................................................41

.............................................................................................................................................................................42

C.11 DBMS_ROWID (PL/SQL8 Only)................................................................................................42

C.11.1 The ROWID_CREATE function..................................................................................42

C.11.2 The ROWID_INFO procedure......................................................................................42

C.11.3 The ROWID_TYPE function........................................................................................42

C.11.4 The ROWID_OBJECT function...................................................................................42

C.11.5 The ROWID_RELATIVE_FNO function....................................................................43

C.11.6 The ROWID_BLOCK_NUMBER function.................................................................43

[Appendix A] What's on the Companion Disk?

Table of Contents

C.11.7 The ROWID_ROW_NUMBER function.....................................................................43

C.11.8 The ROWID_TO_ABSOLUTE_FNO function...........................................................43

C.11.9 The ROWID_TO_EXTENDED function.....................................................................43

C.11.10 The ROWID_TO_RESTRICTED function................................................................43

C.11.11 The ROWID_VERIFY function.................................................................................43

.............................................................................................................................................................................45

C.12 DBMS_SESSION.........................................................................................................................45

C.12.1 The CLOSE_DATABASE_LINK procedure...............................................................45

C.12.2 The IS_ROLE_ENABLED function.............................................................................45

C.12.3 The RESET_PACKAGE procedure.............................................................................45

C.12.4 The SET_LABEL procedure.........................................................................................45

C.12.5 The SET_NLS_LABEL procedure...............................................................................45

C.12.6 The SET_NLS procedure..............................................................................................45

C.12.7 The SET_ROLE procedure...........................................................................................46

C.12.8 The SET_SQL_TRACE procedure...............................................................................46

C.12.9 The UNIQUE_SESSION_ID function.........................................................................46

.............................................................................................................................................................................47

C.13 DBMS_SNAPSHOT.....................................................................................................................47

C.13.1 The DROP_SNAPSHOT procedure.............................................................................47

C.13.2 The GET_LOG_AGE procedure..................................................................................47

C.13.3 The PURGE_LOG procedure.......................................................................................47

C.13.4 The REFRESH procedure.............................................................................................47

C.13.5 The REFRESH_ALL procedure...................................................................................48

C.13.6 The SET_UP procedure................................................................................................48

C.13.7 The WRAP_UP procedure............................................................................................48

.............................................................................................................................................................................49

C.14 DBMS_SQL..................................................................................................................................49

C.14.1 The BIND_ARRAY procedure.....................................................................................49

C.14.2 The BIND_VARIABLE procedure...............................................................................49

C.14.3 The CLOSE_CURSOR procedure................................................................................50

C.14.4 The COLUMN_VALUE procedure..............................................................................50

C.14.5 The DEFINE_COLUMN procedure.............................................................................51

C.14.6 The EXECUTE function...............................................................................................51

C.14.7 The EXECUTE_AND_FETCH function......................................................................52

C.14.8 The FETCH_ROWS function.......................................................................................52

C.14.9 The IS_OPEN function.................................................................................................52

C.14.10 The LAST_ERROR_POSITION function..................................................................52

C.14.11 The LAST_ROW_COUNT function..........................................................................52

C.14.12 The LAST_ROW_ID function....................................................................................52

C.14.13 The LAST_SQL_FUNCTION_CODE function.........................................................52

C.14.14 The OPEN_CURSOR function...................................................................................53

C.14.15 The PARSE procedure................................................................................................53

C.14.16 The VARIABLE_VALUE procedure.........................................................................53

.............................................................................................................................................................................54

C.15 DBMS_TRANSACTION.............................................................................................................54

C.15.1 The ADVISE_COMMIT procedure.............................................................................54

C.15.2 The ADVISE_NOTHING procedure............................................................................54

C.15.3 The ADVISE_ROLLBACK procedure........................................................................54

C.15.4 The COMMIT procedure..............................................................................................55

C.15.5 The COMMIT_COMMENT procedure........................................................................55

C.15.6 The COMMIT_FORCE procedure...............................................................................55

C.15.7 The READ_ONLY procedure.......................................................................................55

[Appendix A] What's on the Companion Disk?

iii

Table of Contents

C.15.8 The READ_WRITE procedure.....................................................................................55

C.15.9 The ROLLBACK procedure.........................................................................................56

C.15.10 The ROLLBACK_FORCE procedure........................................................................56

C.15.11 The ROLLBACK_SAVEPOINT procedure...............................................................56

C.15.12 The SAVEPOINT procedure......................................................................................56

C.15.13 The USE_ROLLBACK_SEGMENT procedure.........................................................56

C.15.14 The BEGIN_DISCRETE_TRANSACTION procedure.............................................56

C.15.15 The PURGE_MIXED procedure................................................................................57

C.15.16 The PURGE_LOST_DB procedure............................................................................57

C.15.17 The LOCAL_TRANSACTION_ID function.............................................................57

C.15.18 The STEP_ID function................................................................................................57

.............................................................................................................................................................................59

C.16 DBMS_UTILITY..........................................................................................................................59

C.16.1 The ANALYZE_SCHEMA procedure.........................................................................59

C.16.2 The COMMA_TO_TABLE procedure.........................................................................59

C.16.3 The COMPILE_SCHEMA procedure..........................................................................59

C.16.4 The FORMAT_CALL_STACK function.....................................................................59

C.16.5 The FORMAT_ERROR_STACK function..................................................................59

C.16.6 The GET_TIME function..............................................................................................60

C.16.7 The IS_PARALLEL_SERVER function......................................................................60

C.16.8 The NAME_RESOLVE procedure...............................................................................60

C.16.9 The NAME_TOKENIZE procedure.............................................................................60

C.16.10 The PORT_STRING function.....................................................................................61

C.16.11 The TABLE_TO_COMMA procedure.......................................................................61

.............................................................................................................................................................................62

C.17 UTL_FILE.....................................................................................................................................62

C.17.1 Setting Up UTL_FILE..................................................................................................62

.............................................................................................................................................................................65

1. Introduction to PL/SQL...............................................................................................................................66

1.1 What Is PL/SQL?.............................................................................................................................66

.............................................................................................................................................................................68

1.2 The Concept of Programming in Oracle Applications....................................................................68

.............................................................................................................................................................................70

1.3 The Origins of PL/SQL....................................................................................................................70

1.3.1 Improved Application Portability with PL/SQL..............................................................70

1.3.2 Improved Execution Authority and Transaction Integrity with PL/SQL........................71

.............................................................................................................................................................................72

1.4 PL/SQL Versions.............................................................................................................................72

1.4.1 Working with Multiple Versions of PL/SQL..................................................................72

1.4.2 How This Book Handles Different Versions of PL/SQL................................................73

1.4.3 PL/SQL Version 2.0........................................................................................................73

1.4.4 PL/SQL Release 2.1.........................................................................................................80

1.4.5 PL/SQL Release 2.2.........................................................................................................82

1.4.6 PL/SQL Release 2.3.........................................................................................................83

1.4.7 PL/SQL Version 8.0........................................................................................................84

1.4.8 PL/SQL Release 1.1.........................................................................................................86

.............................................................................................................................................................................88

1.5 Advice for Oracle Programmers......................................................................................................88

1.5.1 Take a Creative, Even Radical Approach........................................................................88

1.5.2 Get Ready to Establish New Habits.................................................................................88

1.5.3 Assume that PL/SQL Has What You Need.....................................................................89

[Appendix A] What's on the Companion Disk?