Oracle PL/SQL Language Pocket Reference- P10

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

104
lượt xem 17
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle PL/SQL Language Pocket Reference- P10: This pocket guide features quick-reference information to help you use Oracle's PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. ...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle PL/SQL Language Pocket Reference- P10

q Anchor declarations of variables back to the database tables and columns they represent. Whenever you declare a variable which has anything to do with a database element, use the % TYPE or %ROWTYPE declaration attributes to define the datatype of those structures. If those database elements change, your compiled code is discarded. When recompiled, the changes are automatically applied to your code. q Always fetch from an explicit cursor into a record declared with %ROWTYPE, as opposed to individual variables. Assuming that you followed my last piece of advice, that cursor is declared in a package. That cursor may, therefore, be changed without your knowledge. Suppose that another expression is added to the SELECT list. Your compiled code is then marked as being invalid. If you fetched into a record, however, upon recompiliation that record will take on the new structure of the cursor. q Encapsulate access to your data structures within packages. I recommend, for example, that you never repeat a line of SQL in your application; that all SQL statements be hidden behind a package interface; and that most developers never write any SQL at all. They can simply call the appropriate package procedure or function, or open the appropriate package cursor. If they don't find what they need, they ask the owner of the package (who is intimate with the complex details of the data structure) to add or change an element. This last suggestion will have the greatest impact on your applications, but it is also among the most difficult to implement. To accomplish this goal (always execute SQL statements through a procedural interface), you will want to generate packages automatically for a table or view. This is the only way to obtain the consistency and code quality required for this segment of your application code. By the time this second edition is published, you should be able to choose from several different package generators. You can also build your own. 1.7.3 Center All Development Around Packages Little did I know when I wrote the first edition of this book how much more I was to learn about PL/ SQL -- and most of it was about packages. You should center all your PL/SQL development effort around packages. Don't build standalone procedures or functions unless you absolutely have to (some frontend tools cannot yet recognize the package syntax of dot notation: package.program). Expect that you will eventually construct groups of related functionality and start from the beginning with a package. The more you use packages, the more you will discover you can do with them. The more you use packages, the better you will become at constructing clean, easy-to-understand interfaces (or APIs) to your data and your functionality. The more you use packages, the more effectively you will encapsulate acquired knowledge and then be able to reapply that knowledge at a later time -- and share it with others. My second book, Advanced Oracle PL/SQL Programming with Packages, offers a detailed set of "best practices" for package design and usage; highlights follow: q Don't declare data in your package specification. Instead, "hide" it in the package body and build "get and set" programs to retrieve the data and change it. This way, you retain control Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
over the data and also retain the flexibility to change your implementation without affecting the programs which rely on that data. q Build toggles into your packages, such as a "local" debug mechanisms, which you can easily turn on and off. This way, a user of your package can modify the behavior of programs inside the package without having to change his or her own code. q Avoid writing repetitive code inside your package bodies. This is a particular danger when you overload multiple programs with the same name. Often the implementation of each of these programs is very similar. You will be tempted to simply cut and paste and then make the necessary changes. However, you will be much better off if you take the time to create a private program in the package which incorporates all common elements, and then have each overloaded program call that program. q Spend as much time as you can in your package specifications. Hold off on building your bodies until you have tested your interfaces (as defined by the specifications) by building compilable programs which touch on as many different packages as possible. q Be prepared to work in and enhance multiple packages simultaneously. Suppose that you are building a package to maintain orders and that you run into a need for a function to parse a string. If your string package does not yet have this functionality, stop your work in the orders package and enhance the string package. Unit-test your generic function there. When you've got it working, deploy it in the orders package. Follow this disciplined approach to modularization and you will continually build up your toolbox of reusable utilities. q Always keep your package specifications in separate files from your package bodies. If you change your body but not your specification, then a recompile only of the body will not invalidate any programs referencing the package. q Compile all of the package specifications for your application before any of your bodies. That way, you will have minimized the chance that you will run into any unresolved or (seemingly) circular references. 1.7.4 Standardize Your PL/SQL Development Environment When you get right down to it, programming consists of one long series of decisions punctuated by occasional taps on the keyboard. Your productivity is determined to a large extent by what you spend your time making decisions on. Take some time before you start your programming effort to set up standards among a wide variety of aspects. Here are some of my favorite standards, in no particular order: q Set as a rule that individual developers never write their own exception-handling code, never use the pragma EXCEPTION_INIT to assign names to error numbers, and never call RAISE_APPLICATION_ERROR with hardcoded numbers and text. Instead, consolidate exception handling programs into a single package, and predefine all application-specific exceptions in their appropriate packages. Build generic handler programs that, most importantly, hide the way you record exceptions in a log. Individual handler sections of code should never expose the particular implementation, such as an INSERT into a table. q Never write implicit cursors (in other words, never use the SELECT INTO syntax). Instead, always declare explicit cursors. If you follow this advice, you will no longer spend time debating with yourself and others which course is the best. ("Well, if I use ROWNUM < 2 I never get the TOO_MANY_ROWS exception. So there!") This will improve your Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
productivity. And you will have SQL which is more likely (and able) to be reused. q Pick a coding style and stick to it. If you ever find yourself thinking things like "Should I indent three spaces or four?" or "How should I do the line breaks on this long procedure call?" or "Should I do everything in lowercase or uppercase or what?" then you are wasting time and doing an injustice to yourself. If you don't have a coding style, use mine -- it is offered in detail in Chapter 3, Effective Coding Style. 1.7.5 Structured Code and Other Best Practices Once you get beyond the "big ticket" best practices, there are many very concrete recommendations for how to write specific lines of code and constructs. Many of these suggestions have been around for years and apply to all programming languages. So if you took a good programming class in college, for example, don't throw away those books! The specific syntax may change, but the fundamental common sense motivation for what you have learned in the past will certainly work with PL/SQL as well. Without a doubt, if you can follow these guidelines, you are sure to end up with programs which are easier to maintain and enhance: q Never exit from a FOR loop (numeric or cursor) with an EXIT or RETURN statement. A FOR loop is a promise: my code will iterate from the starting to the ending value and will then stop execution. q Never exit from a WHILE loop with an EXIT or RETURN statement. Rely solely on the WHILE loop condition to terminate the loop. q Ensure that a function has a single successful RETURN statement as the last line of the executable section. Normally, each exception handler in a function would also return a value. q Don't let functions have OUT or IN OUT parameters. The function should only return values through the RETURN clause. q Make sure that the name of a function describes the value being returned (noun structure, as in "total_compensation"). The name of a procedure should describe the actions taken (verb-noun structure, as in "calculate_totals"). q Never declare the FOR loop index (either an integer or a record). This is done for you implicitly by the PL/SQL runtime engine. q Do not use exceptions to perform branching logic. When you define your own exceptions, these should describe error situations only. q When you use the ELSIF statement, make sure that each of the clauses is mutually exclusive. Watch out especially for logic like "sal BETWEEN 1 and 10000" and "sal BETWEEN 10000 and 20000." q Remove all hardcoded "magic values" from your programs and replace them with named constants or functions defined in packages. q Do not "SELECT COUNT(*)" from a table unless you really need to know the total number of "hits." If you only need to know whether there is more than one match, simply fetch twice with an explicit cursor. q Do not use the names of tables or columns for variable names. This can cause compile errors. It can also result in unpredictable behavior inside SQL statements in your PL/SQL code. I Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
once did a global search and replace of :GLOBAL.regcd to regcd (a local variable declared as VARCHAR2(10) but also, unfortunately, the name of a column). Our Q&A procedures were very weak and we ended up rolling out into production a program with a DELETE statement containing a WHERE clause that looked like this: WHERE regcd = regcd Needless to say, this caused many headaches. If I had simply changed the global reference to v_regcd, I would have avoided all such problems. There is lots more I could say about best practices for PL/SQL development, especially concerning the application of new Oracle8, object-oriented features. But if you follow the ideas I offer in this section, you will be writing code that is superior to just about everyone else's on this strange planet. So...read on! Previous: 1.6 A Few of My Oracle PL/SQL Next: 2. PL/SQL Language Favorite (PL/SQL) Things Programming, 2nd Edition Fundamentals 1.6 A Few of My Favorite Book Index 2. PL/SQL Language (PL/SQL) Things Fundamentals The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 1.5 Advice for Chapter 1 Next: 1.7 Best Practices for Oracle Programmers Introduction to PL/SQL PL/SQL Excellence 1.6 A Few of My Favorite (PL/SQL) Things PL/SQL is a powerful, many-featured product. This is a lengthy book. I have gone to great lengths to make all the information within the covers highly accessible. Still, I thought it would be helpful to offer a quick review of some of my favorite aspects of the PL/SQL language. It's all wonderful, of course, and I wouldn't trade PL/SQL for any other programming language in the world. Yet certain features and techniques have stood out for me as ways to improve the efficiency of my code and the productivity of my development effort. The topics in the following sections offer just enough information to give you a sense of what is possible. Go to the appropriate chapter for detailed information. 1.6.1 Anchored declarations You can use the %TYPE and %ROWTYPE declaration attributes to anchor the datatype of one variable to that of a previously existing variable or data structure. The anchoring data structure can be a column in a database table, the entire table itself, a programmer-defined record, or a local PL/SQL variable. In the following example, I declare a local variable with the same structure as the company name: my_company company.name%TYPE; See Chapter 4 for details. 1.6.2 Built-in functions PL/SQL offers dozens of built-in functions to help you get your job done with the minimum amount of code and fuss possible. Some of them are straightforward, such as the LENGTH function, which returns the length of the specified string. Others offer subtle variations which will aid you greatly -- but only when you are aware of those variations. Two of my favorites in this category of hidden talents are SUBSTR and INSTR, both character Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
functions. SUBSTR returns a subportion of a string. INSTR returns the position in a string where a substring is found. Most developers only use these functions to search forward through the strings. By passing a negative starting location, however, SUBSTR will count from the end of the string. And INSTR will actually scan in reverse through the string for the nth occurrence of a substring. See the chapters in Part 3 for details. 1.6.3 Built-in packages In addition to the many built-in functions provided by PL/SQL, Oracle Corporation also offers many built-in packages. These packages of functions, procedures, and data structures greatly expand the scope of the PL/SQL language. With each new release of the Oracle Server, we get new packages to improve our own programs. It is no longer sufficient for a developer to become familiar simply with the basic PL/SQL functions like TO_CHAR, ROUND, and so on. Those functions have now become only the innermost layer of useful functionality. Oracle Corporation has built upon those functions, and you should do the same thing. See Appendix C for a summary of the Application Programming Interfaces (APIs) of the built-in packages. 1.6.4 The cursor FOR loop The cursor FOR loop is one of my favorite PL/SQL constructs. It leverages fully the tight and effective integration of the Ada-like programming language with the power of the SQL database language. It reduces the volume of code you need to write to fetch data from a cursor. It greatly lessens the chance of introducing loop errors in your programming -- and loops are one of the more error-prone parts of a program. Does this loop sound too good to be true? Well, it isn't -- it's all true! See Chapter 7, Loops, for more information. 1.6.5 Scoping with nested blocks The general advantage of -- and motivation for -- a nested block is that you create a scope for all the declared objects and executable statements in that block. You can use this scope to improve your control over activity in your program, particularly in the area of exception handling. In the following procedure, I have placed BEGIN and END keywords around a sequence of DELETE statements. This way, if any DELETE statement fails, I trap the exception, ignore the problem, and move on to the next DELETE: PROCEDURE delete_details IS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN BEGIN DELETE FROM child1 WHERE ...; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN DELETE FROM child2 WHERE ...; EXCEPTION WHEN OTHERS THEN NULL; END; END; I can in this way use my nested blocks to allow my PL/SQL program to continue past exceptions. See Chapter 15, Procedures and Functions, for details. 1.6.6 Module overloading Within a package and within the declaration section of a PL/SQL block, you can define more than one module with the same name! The name is, in other words, overloaded. In the following example, I have overloaded the value_ok function in the body of the check package: PACKAGE BODY check IS /* First version takes a DATE parameter. */ FUNCTION value_ok (date_in IN DATE) RETURN BOOLEAN IS BEGIN RETURN date_in 0; END; END; Overloading can greatly simplify your life and the lives of other developers. This technique consolidates the call interfaces for many similar programs into a single module name. It transfers the burden of knowledge from the developer to the software. You do not have to try to remember, for example, the six different names for programs which all add values (dates, strings, Booleans, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
numbers, etc.) to various PL/SQL tables. Instead, you simply tell the compiler that you want to "add" and pass it the value you want added. PL/ SQL and your overloaded programs figure out what you want to do and they do it for you. See Chapter 15 for details. 1.6.7 Local modules A local module is a procedure or function defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is only defined within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside of that enclosing block. See Chapter 15 for details. 1.6.8 Packages A package is a collection of related elements, including modules, variables, table and record TYPEs, cursors, and exceptions. Packages are among the least understood and most underutilized features of PL/SQL. That is a shame, because the package structure is also one of the most useful constructs for building well-designed PL/SQL-based applications. Packages provide a structure in which you can organize your modules and other PL/SQL elements. They encourage proper programming techniques in an environment that often befuddles the implementation of good design. With packages, you can: q Create abstract datatypes and employ object-oriented design principles in your Oracle-based applications. q Use top-down design techniques comprehensively. You can build package specifications devoid of any code and actually compile programs that call the modules in these "stub" packages. q Create and manipulate data that persist throughout a database session. You can use variables that are declared in a package to create global data structures. See Chapter 16, Packages, for details. The disk that accompanies this book contains many examples of packages. The frontend software gives you an easy-to-use interface to the code and explanations for using it. Previous: 1.5 Advice for Oracle PL/SQL Next: 1.7 Best Practices for Oracle Programmers Programming, 2nd Edition PL/SQL Excellence 1.5 Advice for Oracle Book Index 1.7 Best Practices for PL/ Programmers SQL Excellence Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 1.4 PL/SQL Chapter 1 Next: 1.6 A Few of My Versions Introduction to PL/SQL Favorite (PL/SQL) Things 1.5 Advice for Oracle Programmers This whole book is full of advice about PL/SQL programming, but in this section I offer a few basic principles. These principles guide my own use of PL/SQL and I'd like to encourage you to adopt them as well. 1.5.1 Take a Creative, Even Radical Approach We all tend to fall into ruts, in almost every aspect of our lives. People are creatures of habit: you learn to write code in one way; you come to assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can't be done. Developers become downright prejudiced about their own tools, and often not in positive ways. "It can't run any faster than that; it's a pig." "I can't make it work the way the user wants; that'll have to wait for the next version." "If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle." Sadly (or is it happily?), the reality is that your program could almost always run a little faster. The screen could function just the way the user wants it to. Although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn't it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and created a solution? How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just your cubicle). Reassess the programming habits you've developed, particularly regarding fourth-generation language (4GL) development with the Oracle tools. Be creative -- step away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business. Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn, how you will grow as a programmer and problem- solver. Over the years, I have surprised myself over and over with what is really achievable when I stopped saying "You can't do that!" and instead simply nodded quietly and murmured "Now, if I do it this way..." Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1.5.2 Get Ready to Establish New Habits PL/SQL was initially a way to increase the flexibility of the ANSI-standard SQL, and soon a multi- purpose "Swiss Army Knife" for many of Oracle's tools. Suddenly, developers found they could plop function calls, IF-THEN-ELSE clauses, loops, and even GOTOs right into the midst of their otherwise pristinely declarative screen modules and batch database processing routines. Did the appearance of PL/SQL instantly transform Oracle-based applications into shining examples for programmers of all faiths? Hardly. Sure, 4GLS are lots more productive than the older software technology: now you can dig yourself into a pretty deep hole with a 4GL much more efficiently than was ever possible with good old FORTRAN. In fact, for a number of reasons, the level of sophistication of programming in PL/SQL has proven very uneven. While many SQL*Forms developers came out of a 3GL programming environment with a strong history in structured programming and strict guidelines, these principles have been largely forgotten or considered inapplicable in the new 4GL world of SQL and SQL*Forms. What does "structured code" mean when a screen is not composed of lines of code in a file, but rather as a series of pictures and boxes of attributes in the Designer? How do you flowchart a SQL statement? Many of us left our good programming manners behind when we sauntered into the world of SQL*Forms. It's been hard to return to those habits, especially given some of the limitations of PL/ SQL Version 1. On the other hand, many Oracle developers are not seasoned programmers, but relative newcomers who may have little or no formal training in computer sciences and programming. They might, for example, have started out as end users who needed some ad hoc queries and ended up building forms. The first release of PL/SQL (and the later versions, for that matter) was not intended to be a comprehensive procedural language in the same league as, say, C or COBOL. Officially, it existed only to provide some programming constructs around the SQL language to facilitate batch database procedures. PL/SQL did not interact with the operating system, had no debugger whatsoever, and didn't support the normal 3GL concepts of link libraries and modularized code. As soon as Oracle made PL/SQL available in SQL*Forms Version 3, however, thousands of Oracle developers moved quickly to put it to work in their forms. Suddenly they could code all (well, almost all) the fancy gizmos their users wanted.[2] [2] And they could do so without committing the most unnatural acts (for example, user exits to provide pop-up windows or SQL*Forms Version 2.3 triggers that called themselves recursively and were impossible to debug). Damn the torpedoes and full speed ahead! But what about standards? What about modularization? What about reusable code? Such concerns were often ignored as the volume of PL/SQL programming exploded throughout the Oracle community. In their press to meet management expectations of extraordinary productivity, developers did what they needed to do in what little time they had. And few of us had time to take training in PL/SQL, even when it was offered. Few of us had time to think about whether what we wrote could be maintained or enhanced easily. Instead we Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
just coded. And coded. And coded. When it came time to debug a PL/SQL module, we discovered that there wasn't any debugger in PL/ SQL at all, and precious little to work with in SQL*Forms and SQL*Plus. Programmers with backgrounds in established languages like COBOL or FORTRAN or C shook their heads and did what they could. The many people introduced to software development through Oracle software didn't know what they were missing. They just knew that it was very difficult to identify and then repair problems in their code. PL/SQL has come a long way from its first hesitant offering for the RDBMS in 1990. Developers who have worked with the language since its first release must make sure to adapt to the changing features and potential of PL/SQL. 1.5.3 Assume that PL/SQL Has What You Need Programmers who are new to PL/SQL often make the mistake of starting their coding efforts before they are sufficiently familiar with everything the language has to offer. I have seen and heard of many instances where a developer spends valuable time writing procedures or functions that duplicate built-in functionality provided by PL/SQL. Please don't write a function that looks through each character in a string until it finds a match and then returns the index of that match in the string. The INSTR function does this for you. Please don't write a function to convert your string from uppercase to lowercase by performing ASCII code-table shifting. Use the LOWER function instead. With the PL/SQL of the 1990s, you also have to keep in mind much more than these basic functions. Each new release of the database and the tools include packages that stretch the boundaries of the PL/ SQL language itself. These packages extend PL/SQL by providing additional datatypes, functions, and procedures to handle more specialized situations. You can use DBMS_ JOB to schedule processes from the database. You can use DBMS_PIPE to communicate information between different Oracle sessions. The ideas and the list of prebuilt code goes on and on. Take some time to stroll through Part 3, Built-In Functions , and Appendix C, and get familiar with all the features that are built into the PL/SQL language. 1.5.4 Share Your Ideas Oracle Corporation, along with its flavor of SQL and the PL/SQL language, has been around for close to 15 years. They have listened to user requests, kept up with the standards committees, and generally sought to create a very robust environment for developers and users. As I've said, there is a very good chance that what you need is already available in the language. If so, use it. If not, build it yourself in the most general and reusable way possible. Then share it. Share your ideas and your creations with others in your company, your Oracle User Group, even the worldwide Oracle community through the International Oracle User's Group and User's Week convention. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 1.4 PL/SQL Oracle PL/SQL Next: 1.6 A Few of My Versions Programming, 2nd Edition Favorite (PL/SQL) Things 1.4 PL/SQL Versions Book Index 1.6 A Few of My Favorite (PL/SQL) Things The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 1.3 The Origins Chapter 1 Next: 1.5 Advice for of PL/SQL Introduction to PL/SQL Oracle Programmers 1.4 PL/SQL Versions One thing that may complicate using PL/SQL is that it is not a single product. There are several distinct, supported versions out there. Table 1.2 summarizes the various versions; the following sections describe the main features available in each of the versions in use today. Table 1.2: PL/SQL Versions Version/Release Characteristics Version 1.0 First available in SQL*Plus as a batch-processing script. Oracle Version 6.0 was released at approximately the same time. PL/SQL was then implemented within SQL*Forms Version 3, the predecessor of Oracle Forms. Release 1.1 Available only in the Oracle Developer/2000 Release 1 tools. This upgrade supports client-side packages and allows client-side programs to execute stored code transparently. Version 2.0 Available with Release 7.0 (Oracle Server). Major upgrade to Version 1. Adds support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables, and many package extensions, including DBMS_OUTPUT and DBMS_PIPE. Release 2.1 Available with Release 7.1 of the Oracle Server Version. Supports programmer- defined subtypes, enables the use of stored functions inside SQL statements, and offers dynamic SQL with the DBMS_SQL package. With Version 2.1, you can now execute SQL DDL statements from within PL/SQL programs. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Release 2.2 Available with Release 7.2 of the Oracle Server Version. Implements a binary "wrapper" for PL/SQL programs to protect source code, supports cursor variables for embedded PL/SQL environments such as Pro*C, and makes available database-driven job scheduling with the DBMS_JOB package. Release 2.3 Available with Release 7.3 of the Oracle Server Version. Enhances functionality of PL/SQL tables, offers improved remote dependency management, adds file I/ O capabilities to PL/SQL, and completes the implementation of cursor variables. Version 8.0 Available with Oracle8 Release 8.0. The drastic change in version number reflects Oracle's effort to synchronize version numbers across related products. PL/SQL8 is the version of PL/SQL which supports the many enhancements of Oracle8, including large objects (LOBs), object-oriented design and development, collections (VARRAYs and nested tables), and Oracle/AQ (the Oracle/Advanced Queueing facility). 1.4.1 Working with Multiple Versions of PL/SQL All of the releases of PL/SQL Version 2 are linked directly to the release of the Oracle Server on which they run. PL/SQL Release 1.1 is available only with the Oracle Developer/2000 Release 1 tools. The presence of these different versions can make your life complicated. Consider the following scenarios: q You want to take full advantage of all the latest features of the Oracle software family, from the frontend tools to the backend RDBMS and stored procedures. You will therefore use PL/ SQL Release 1.1 (until Oracle makes available the second version of its Oracle Developer/2000 toolset) to build client-side programs and will use PL/SQL Release 2.X through PL/SQL8 for your stored programs. q You develop applications in a distributed environment and support different versions of the Oracle Server on different platforms. If you need to build stored programs to run in each of these databases, you will need to work with more than one release of PL/SQL. Given this complexity, you need to be aware of the differences between the releases, as well as restrictions on PL/SQL development in Release 1.1 of PL/SQL. 1.4.2 How This Book Handles Different Versions of PL/SQL This book uses Version 2.0 of the PL/SQL language as the "base" version for purposes of presenting the technology. If you are using any of the more recent releases of the PL/SQL language (2.1, 2.2, 2.3, or 8.0), you will be able to take advantage of all the standard features of PL/SQL and, in addition, leverage the enhancements of that release. If you are using Release 1.1 of PL/SQL, you will Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
not be able to use all the features of Versions 2.0 through Version 8.0. The new Oracle8-related functionality (delivered in PL/SQL8) is covered primarily in Part 5, New PL/ SQL8 Features. Additional coverage of PL/SQL8-specific features is provided in chapters which cover that area of technology. For example, Chapter 4, Variables and Program Data, introduces the new datatypes of PL/SQL8, including large objects (LOBs). I will note explicitly throughout the book when a particular feature is available only in a specific PL/ SQL version or release. If this book does not cover a particular feature, that is mentioned as well. The following sections summarize the new features of each PL/SQL release, as well as the restrictions placed on the use of PL/SQL Release 1.1. Review these sections now so you can more easily identify and use them later in your programming efforts. NOTE: If you are using the Oracle Developer/2000 suite of development tools, then you will be using PL/SQL Release 1.1 for all local PL/SQL programs. You may also run PL/SQL Version 2-based programs stored in the database from the Oracle Developer/2000 application. See the section below for those features of PL/SQL Version 2.0 that may be used in programs based on PL/SQL Release 1.1.1. 1.4.3 PL/SQL Version 2.0 PL/SQL Version 2.0 was first released with the Oracle Server and expanded significantly the ability of PL/SQL to support large-scale, complex, distributed application development efforts. With Version 2.0, you can modularize your programs into procedures, functions, and -- most importantly -- packages. You can store your modules in the database and call them from any Oracle session, on both the client and server sides of distributed applications. The features of PL/SQL Version 2.0 are described in the following sections. 1.4.3.1 Integration with SQL PL/SQL was originally designed to provide procedural extensions to the SQL language. From within PL/SQL you can execute any DML statement (SELECT, UPDATE, INSERT, and DELETE). You cannot, however, execute a DDL statement, such as CREATE TABLE. This capability is available only in later releases through the use of specially provided packages, such as DBMS_SQL. This integration with SQL also means that from within the native PL/SQL language you can make full use of all SQL operators, predicates, and built-in functions. Outside a SQL statement you can call the TO_CHAR function to convert a date to a string and check to see if one string is LIKE another string (an operator usually found in the WHERE clause of a SQL statement). In addition to DML statements, you can use SQL's transaction-oriented statements to commit and roll back transactions. You can also mark SAVEPOINTs and roll back to those intermediate phases in a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
transaction. Because the SQL is called from within PL/SQL, you can make use of PL/SQL constructs in the SQL statement. The following example references the PL/SQL variable last_hire_date in the WHERE clause of a SELECT statement: SELECT employee.last_name -- Database column INTO new_hire_name -- Local PL/SQL variable FROM employee -- Name of table WHERE hire_date = -- References column and PL/ SQL variable last_hire_date; With Version 2.0, PL/SQL also supports the syntax required to perform distributed SQL. The following update changes the data in a remote table by selecting information from a second remote table: UPDATE employee@NEW_YORK SET salary = (SELECT MAX(salary) FROM employee@TORONTO); NEW_YORK and TORONTO are database links to employee tables in different database instances (presumably in those cities). PL/SQL Version 2.0 also lets you include hints to the optimizer (structured as comments within the SQL statement, enclosed by the /* and */ delimiters) to modify the execution plan used by the optimizer. 1.4.3.2 Expanded set of datatypes for variables and constants PL/SQL lets you declare local variables and constants and then use those identifiers in your PL/SQL program. You can declare the variables and constants to be a datatype known to the RDBMS, such as NUMBER or VARCHAR2. However, you can also make use of PL/SQL-specific data structures such as: BOOLEAN A true Boolean datatype whose value is TRUE, FALSE, or NULL. BINARY_INTEGER Similar to NUMBER, BINARY_INTEGER datatype represents values as signed binary numbers of virtually any size. Because signed binary is the internal format for numeric values, you can perform calculations with this datatype that do not require any conversions. PL/SQL record Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
A record contains one or more fields and is similar to a row in a database table. You can assign values to variables and SELECT information from the database into these variables. In addition to these datatypes, PL/SQL Version 2.0 has added ROWID, RAW, LONG RAW, and MLSLABEL (for Trusted Oracle). PL/SQL Version 2.0 also predefines a set of "subtypes," which applies constraints to an existing "base" subtype. These subtypes include NATURAL (all integers greater than zero, a subtype of BINARY_INTEGER), and REAL (a subtype of NUMBER). 1.4.3.3 Programmer-defined records A record is a composite data structure, consisting of one or more fields or columns. A record in PL/ SQL roughly corresponds to a row in a database table. With earlier versions of PL/SQL, you can create records using the %ROWTYPE attribute; however, these records can only reflect the structure of a table or cursor. With PL/SQL Version 2.0, you can create records with whatever structure you decide upon, completely independent of any particular table or cursor. A programmer-defined record may even have another record as a field in its record, thereby allowing nested records and the ability to represent real-world structures in your programs. Here is an example of the definition of a record type, followed by a declaration of the actual record: DECLARE /* Create a record to hold four quarters of sales data. */ TYPE sales_quarters_rectype IS RECORD (q1_sales NUMBER, q2_sales NUMBER, q3_sales NUMBER, q4_sales NUMBER); /* || Create a record to hold sales information for a customer. || Notice the nested record. */ TYPE customer_sales_rectype IS RECORD (customer_id NUMBER (5), customer_name customer.name%TYPE, total_sales NUMBER (15,2), sales_by_quarter sales_quarters_rectype); /* Create a record for use in the program of this record type. */ customer_sales_rec customer_sales_rectype; BEGIN You can pass records as parameters and perform aggregate assignments with records -- that is, with a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
single assignment operation you can assign all the values of one record to another, compatible record. 1.4.3.4 PL/SQL tables One of the first questions I ever heard posed about PL/SQL Version 1.1 was, "Where are the arrays?" Programmers who coded in the nonprocedural interface of SQL*Forms, after spending a decade with languages like FORTRAN and C, got all excited when they heard about PL/SQL: they thought they'd get all the good stuff they had in their 3GL environments -- particularly arrays. Imagine the shock and disappointment when PL/SQL not only lacked the ability to read and write disk files, but also did not support arrays! The designers of the PL/SQL language recognized the need to manipulate data in array-like structures in memory, but they also wanted to make sure to maintain PL/SQL's nature as an extension to SQL. The result is the PL/SQL table, available with Version 2.0. The PL/SQL table is a memory-resident object that gives you array-like access to rows of information. It is similar to, but not the same as, a database table. Currently, a PL/SQL table may contain only one column (with the datatype of your choice) and one primary key (with a mandatory datatype of BINARY_INTEGER). The following declaration section contains the definition of a table type, followed by the declaration of a table, that can be used in a program: DECLARE /* Table of strings to hold company names. */ TYPE company_names_tabtype IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; /* Declaration of actual table to be used in code. */ company_names company_names_tabtype; BEGIN PL/SQL tables can be passed as parameters in modules. Unlike arrays found in 3GL programs, PL/ SQL tables are unconstrained and sparse. Unconstrained means that, as with database tables, you do not have to decide the size of a PL/SQL table in advance. Sparse means that the only rows defined in memory are those you create with an assignment to that row. 1.4.3.5 Built-in functions PL/SQL Version 2.0 supports a wide range of built-in functions to manipulate data in your programs. These built-ins may be categorized as follows: Character functions Functions that analyze and modify the contents of CHAR and VARCHAR2 string variables Date functions Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Utilities that allow programmers to perform high-level actions on date variables, including date arithmetic Numeric functions A full range of functions that manipulate numbers, including trigonometric, logarithmic, and exponential functions Conversion functions Functions that convert from one datatype to another, often formatting the output data at the same time 1.4.3.6 Built-in packages In addition to the built-in functions, each release of PL/SQL offers built-in packages, which bundle together related programs. These packages will prove to be invaluable in your development efforts; they are summarized in Appendix C, Built-In Packages. Here are some examples of built-in packages available with PL/SQL Release 2.0: DBMS_OUTPUT Displays information to the screen; useful for debugging PL/SQL scripts. DBMS_PIPE Communicates between Oracle sessions via "pipes." Oracle Corporation uses this package to parallelize their database, and you can use it to parallelize your own programs. DBMS_LOCK Requests and manages programmer-defined locks. You would, for example, use the PUT_LINE procedure in the DBMS_OUTPUT package to display information to your screen (or, in the case of the World Wide Web, to your home page): DBMS_OUTPUT.PUT_LINE ('Option selected is ' || option_desc); 1.4.3.7 Control structures PL/SQL provides procedural extensions to SQL to implement the following types of control structures: q Conditional control via IF statements q Iterative control via loops, including FOR, WHILE, and simple loops q Sequential control via GOTO and NULL statements Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.