Oracle PL/SQL Language Pocket Reference- P9

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

Thêm vào BST

Báo xấu

116
lượt xem 19
download

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

Oracle PL/SQL Language Pocket Reference- P9: 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- P9

total_type IN VARCHAR2--ALL or NET ); /* || For every employee hired more than five years ago, || give them a bonus and send them an e-mail notification. */ FOR emp_rec IN emp_cur (ADD_MONTHS (SYSDATE, -60)) LOOP apply_bonus (emp_rec.employee_id); send_notification (emp_rec.employee_id); END LOOP; -- IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN COMMIT; END IF; FUNCTION display_user (user_id IN NUMBER /* Must be valid ID */, user_type IN VARCHAR2) The first example uses the single-line comment syntax to include endline descriptions for each parameter in the procedure specification. The second example uses a multiline comment to explain the purpose of the FOR loop. The third example uses the double-hyphen to comment out a whole line of code. The last example embeds a comment in the middle of a line of code using the block comment syntax. These two types of comments offer the developer flexibility in how to provide inline documentation. The rest of this section offers guidelines for writing effective comments in your PL/SQL programs. 3.6.1 Comment As You Code It is very difficult to make time to document your code after you have finished writing your program. Psychologically, you want to (and often need to) move on to the next programming challenge after you get a program working. You may also have a harder time writing your comments once you have put some distance between your brain cells and those lines of code. Why exactly did you write the loop that way? Where precisely is the value of that global variable set? Unless you have total recall, post-development documentation can be a real challenge. The last and perhaps most important reason to write your comments as you write your code is that the resulting code will have fewer bugs and (independent of the comments themselves) be easier to understand. When you write a comment you (theoretically) explain what your code is meant to accomplish. If you find it difficult to come up with that explanation, there is a good chance that you lack a full Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
understanding of what the program does or should do. The effort that you make to come up with the right comment will certainly improve your comprehension, and may also result in code correction. In this sense, good inline documentation can be as beneficial as a review of your code by a peer. In both cases, the explanation will reveal important information about your program. 3.6.2 Explain the Why -- Not the How -- of Your Program What do you think of the comments in the following Oracle Forms trigger code? -- If the total compensation is more than the maximum... IF :employee.total_comp > maximum_salary THEN -- Inform the user of the problem. MESSAGE ('Total compensation exceeds maximum. Please re-enter!'); -- Reset the counter to zero. :employee.comp_counter := 0; -- Raise the exception to stop trigger processing. RAISE FORM_TRIGGER_FAILURE; END IF; None of these comments add anything to the comprehension of the code. Each comment simply restates the line of code, which in most cases is self-explanatory. Avoid adding comments simply so that you can say, "Yes, I documented my code!" Rely as much as possible on the structure and layout of the code itself to express the meaning of the program. Reserve your comments to explain the Why of your code: What business rule is it meant to implement? Why did you need to implement a certain requirement in a certain way? In addition, use comments to translate internal, computer-language terminology into something meaningful for the application. Suppose you are using Oracle Forms GLOBAL variables to keep track of a list of names entered. Does the following comment explain the purpose of the code or simply restate what the code is doing? /* Set the number of elements to zero. */ :GLOBAL.num_elements := 0; Once again, the comment adds no value. Does the next comment offer additional information? /* Empty the list of names. */ :GLOBAL.num_elements := 0; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This comment actually explains the purpose of the assignment of the global to zero. By setting the number of elements to zero, I will have effectively emptied the list. This comment has translated the "computer lingo" into a description of the effect of the statement. Of course, you would be even better off hiding the fact that you use this particular global variable to empty a list and instead build a procedure as follows: PROCEDURE empty_list IS BEGIN :GLOBAL.num_elements := 0; END; Then to empty a list you would not need any comment at all. You could simply include the statement: empty_list; and the meaning would be perfectly clear. 3.6.3 Make Comments Easy to Enter and Maintain You shouldn't spend a lot of time formatting your comments. You need to develop a style that is clean and easy to read, but also easy to maintain. When you have to change a comment, you shouldn't have to reformat every line in the comment. Lots of fancy formatting is a good indication that you have a high-maintenance documentation style. The following block comment is a maintenance nightmare: /* =========================================================== | Parameter Description | | | | company_id The primary key to company | | start_date Start date used for date range | | end_date End date for date range | =========================================================== */ The right-justified vertical lines and column formatting for the parameters require way too much effort to enter and maintain. What happens if you add a parameter with a very long name? What if you need to write a longer description? A simpler and more maintainable version of this comment might be: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
/* =========================================================== | Parameter - Description | | company_id - The primary key to company | start_date - Start date used for date range | end_date - End date for date range =========================================================== */ I like to use the following format for my block comments: /* || I put the slash-asterisk that starts the comment on a line all by || itself. Then I start each line in the comment block with a double || vertical bar to highlight the presence of the comment. Finally, || I place the asterisk-slash on a line all by itself. */ On the negative side, the vertical bars have to be erased whenever I reformat the lines, but that isn't too much of an effort. On the positive side, those vertical bars make it very easy for a programmer who is scanning the left side of the code to pick out the comments. I put the comment markers on their own lines to increase the whitespace in my program and set off the comment. That way I can avoid "heavy" horizontal lines full of delimiters, such as asterisks or dashes, and avoid having to match the longest line in the comment. 3.6.4 Maintain Indentation Inline commentary should reinforce the indentation and therefore the logical structure of the program. For example, it is very easy to find the comments in the make_array procedures shown below. I do not use any double-hyphens, so the slash-asterisk sequences stand out nicely. In addition, all comments start in the first column, so I can easily scan down the left-hand side of the program and pick out the documentation: PROCEDURE make_array (num_rows_in IN INTEGER) /* Create an array of specified numbers of rows */ IS /* Handles to Oracle Forms structures */ col_id GROUPCOLUMN; rg_id RECORDGROUP; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN /* Create new record group and column */ rg_id := CREATE_GROUP ('array'); col_id := ADD_GROUP_COLUMN ('col'); /* || Use a loop to create the specified number of rows and || set the value in each cell. */ FOR row_index IN 1 .. num_rows_in LOOP /* Create a row at the end of the group to accept data */ ADD_GROUP_ROW (return_value, END_OF_GROUP); FOR col_index IN 1 .. num_columns_in LOOP /* Set the initial value in the cell */ SET_GROUP_NUMBER_CELL (col_id, row_index, 0); END LOOP; END LOOP; END; The problem with these comments is precisely that they do all start in the first column, regardless of the code they describe. The most glaring example of this formatting "disconnect" comes in the inner loop, repeated below: FOR col_index IN 1 .. num_columns_in LOOP /* Set the initial value in the cell */ SET_GROUP_NUMBER_CELL (col_id, row_index, 0); END LOOP; Your eye follows the three-space indentation very smoothly into the loop and then you are forced to move all the way to the left to pick up the comment. This format disrupts your reading of the code and therefore its readability. The code loses some of its ability to communicate the logical flow "at a glance," because the physical sense of indentation as logical flow is marred by the comments. Finally, you may end up writing full-line comments which are much longer than the code they appear next to, further distorting the code. Your comments should always be indented at the same level as the code which they describe. Assuming the comments come before the code itself, those lines of descriptive text will initiate the indentation at that logical level, which will also reinforce that structure. The make_array procedure, properly indented, is shown below: PROCEDURE make_array (num_rows_in IN INTEGER) /* Create an array of specified numbers of rows */ IS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
/* Handles to Oracle Forms structures */ col_id GROUPCOLUMN; rg_id RECORDGROUP; BEGIN /* Create new record group and column */ rg_id := CREATE_GROUP ('array'); col_id := ADD_GROUP_COLUMN ('col'); /* || Use a loop to create the specified number of rows and || set the value in each cell. */ FOR row_index IN 1 .. num_rows_in LOOP /* Create a row at the end of the group to accept data */ ADD_GROUP_ROW (return_value, END_OF_GROUP); FOR col_index IN 1 .. num_columns_in LOOP /* Set the initial value in the cell */ SET_GROUP_NUMBER_CELL (col_id, row_index, 0); END LOOP; END LOOP; END; END LOOP; END LOOP; END; 3.6.5 Comment Declaration Statements I propose the following simple rule for documenting declaration statements: Provide a comment for each and every declaration. Does that sound excessive? Well, I must admit that I do not follow this guideline at all times, but I bet people who read my code wish I had. The declaration of a variable which seems to me to be perfectly clear may be a source of abiding confusion for others. Like many other people, I still have difficulty understanding that what is obvious to me is not necessarily obvious to someone else. Consider the declaration section in the next example. The commenting style is inconsistent. I use double-hyphens for a two-line comment; then I use the standard block format to provide information about three variables all at once. I provide comments for some variables, but not for others. It's hard to make sense of the various declaration statements: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DECLARE -- Assume a maximum string length of 1000 for a line of text. text_line VARCHAR2 (1000); len_text NUMBER; /* || Variables used to keep track of string scan: || atomic_count - running count of atomics scanned. || still_scanning - Boolean variable controls WHILE loop. */ atomic_count NUMBER := 1; still_scanning BOOLEAN; BEGIN Let's recast this declaration section using my proposed guideline: a comment for each declaration statement. In the result shown below, the declaration section is now longer than the first version, but it uses whitespace more effectively. Each declaration has its own comment, set off by a blank line if a single-line comment: DECLARE /* Assume a maximum string length of 1000 for a line of text. */ text_line VARCHAR2 (1000); /* Calculate length of string at time of declaration */ len_string NUMBER; /* Running count of number of atomics scanned */ atomic_count NUMBER := 1; /* Boolean variable that controls WHILE loop */ still_scanning BOOLEAN ; BEGIN Previous: 3.5 Formatting Oracle PL/SQL Next: 3.7 Documenting the Packages Programming, 2nd Edition Entire Package 3.5 Formatting Packages Book Index 3.7 Documenting the Entire Package The Oracle Library Navigation Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 3.4 Formatting Chapter 3 Next: 3.6 Using Comments PL/SQL Blocks Effective Coding Style Effectively 3.5 Formatting Packages A package is a collection of related objects, including variables, TYPE statements (to define structures for records, tables, and cursors), exceptions, and modules. We have already covered structuring all the different objects which make up a package. Now, let's take a look at how to structure the package itself. A package has both a specification and a body. The package specification contains the declarations or definitions of all those objects that are visible outside of the package -- the public objects. This means that the objects can be accessed by any account that has been granted EXECUTE authority on the package. The package body contains the implementation of all cursors and modules defined in the specification, and the additional declaration and implementation of all other package objects. If an object, such as a string variable, is declared in the body and not in the package, then any module in the package can reference that variable, but no program outside of the package can see it. That variable is invisible or private to the package. The first point to make about the package structure is that all objects declared in the specification exist within the context of the package and so should be indented from the PACKAGE statement itself, as shown below: PACKAGE rg_select IS list_name VARCHAR2(60); PROCEDURE init_list (item_name_in IN VARCHAR2, fill_action_in IN VARCHAR2 := 'IMMEDIATE'); PROCEDURE delete_list; PROCEDURE clear_list; END rg_select; The same is true for the package body. I suggest that you always include a label for the END statement in a package so that you can easily connect up that END with the end of the package as a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
whole. I place the IS keyword on a new line to set off the first declaration in the package from the name of the package. You could always use a blank line. Notice that I use blank lines in rg_select to segregate different modules which are related by function. I think that logical grouping is always preferable to an arbitrary grouping such as alphabetical order. The other important element in formatting a package is the order in which objects are listed in the package. I generally list objects in the order of complexity of their structure, as follows: q Scalar variables, such as a VARCHAR2 declaration q Complex datatypes, such as records and tables q Database-related declarations, such as cursors q Named exceptions q Modules (procedures and functions) As with simple variable declarations, I sometimes have many different but related objects in my package. If so, I might group those types of objects together. But within that grouping, I still follow the above order. Previous: 3.4 Formatting Oracle PL/SQL Next: 3.6 Using Comments PL/SQL Blocks Programming, 2nd Edition Effectively 3.4 Formatting PL/SQL Book Index 3.6 Using Comments Blocks Effectively 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: 3.3 Formatting Chapter 3 Next: 3.5 Formatting Control Structures Effective Coding Style Packages 3.4 Formatting PL/SQL Blocks As I've outlined in Chapter 2, every PL/SQL program is structured as a block containing up to four sections: q Header q Declaration section q Executable section q Exception section The PL/SQL block structure forms the backbone of your code. A consistent formatting style for the block, therefore, is critical. This formatting should make clear these different sections. (See Chapter 15, Procedures and Functions, for more information about the block structure.) Consider the following function: FUNCTION company_name (company_id_in IN company.company_id% TYPE) RETURN VARCHAR2 IS cname company.company_id%TYPE; BEGIN SELECT name INTO cname FROM company WHERE company_id = company_id_in; RETURN cname; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; You know that this program is a function because the first word in the program is FUNCTION. Other than that, however, it is very difficult to follow the structure of this program. Where is the declaration section? Where does the executable section begin and end? Here is that same function after we apply some straightforward formatting rules to it: FUNCTION company_name (company_id_in IN company.company_id %TYPE) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
RETURN VARCHAR2 IS cname company.company_id%TYPE; BEGIN SELECT name INTO cname FROM company WHERE company_id = company_id_in; RETURN cname; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; Now it is easy to see that the header of the function consists of: FUNCTION company_name (company_id_in IN company.company_id %TYPE) RETURN VARCHAR2 The declaration section, which comes after the IS and before the BEGIN, clearly consists of a single declaration of the cname variable. The executable section consists of all the statements after the BEGIN and before the EXCEPTION statement; these are indented in from the BEGIN. Finally, the exception section shows a single specific exception handler and a WHEN OTHERS exception. Generally, indent the statements for a given section from the reserved words which initiate the section. You can also include a blank line before each section, as I do above, for the executable section (before BEGIN) and the exception section (before EXCEPTION). I usually place the IS keyword on its own line to clearly differentiate between the header of a module and its declaration section. Previous: 3.3 Formatting Oracle PL/SQL Next: 3.5 Formatting Control Structures Programming, 2nd Edition Packages 3.3 Formatting Control Book Index 3.5 Formatting Packages Structures 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: 3.2 Formatting Chapter 3 Next: 3.4 Formatting PL/ SQL Statements Effective Coding Style SQL Blocks 3.3 Formatting Control Structures The control structures in your program are the most direct representation of the logic needed to implement your specifications. The format of these control structures, therefore, will have a significant impact on the readability of your code. Indentation is the most important element of control structure layout. Always keep statements of the same "logical level" at the same indentation level. Let's see what this means for the various control structures of PL/SQL. 3.3.1 Formatting IF Statements This conditional construct comes in three flavors: IF IF IF END IF; ELSE ELSEIF END IF; ELSE END IF; In general, the IF statement is composed of clauses in which there is a Boolean expression or condition and a section of code executed when that condition evaluates to TRUE. So if you want to use indentation to reveal the logical structure of the simplest form of the IF statement (IF-END IF), I suggest one of these two styles: New Line for THEN Same Line for THEN IF IF THEN THEN executable_statements executable_statements; END IF; END IF; IF IF THEN THEN executable_statements executable_statements; ELSE ELSE else_executable_statements; else_executable_statements; END IF; END IF; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
IF 1 IF 1 THEN THEN executable_statements1; executable_statements1; ELSEIF THEN ELSEIF executable_statements2; THEN ... executable_statements2; ... ELSEIF THEN executable_statementsN; ELSEIF THEN ELSE executable_statementsN; else_executable_statements; END IF; ELSE else_executable_statements; END IF; Notice that in both versions the executable statements are indented three spaces from the column in which the IF and END IF reserved words are found. The only difference between the two formats is the placement of the THEN reserved word. I prefer the new line format, in which the THEN appears on a line by itself after the IF condition. This format provides more whitespace than the other. I could create the whitespace by using a blank, rather than indenting three spaces, but then the executable statements for the IF clause are made distinct from the condition -- and they are logically connected. Let's examine some actual code to get a better sense of the differences. The following example shows proper IF statement indentation with THEN on the same line: IF max_sales > 2000 THEN notify_accounting ('over_limit'); RAISE FORM_TRIGGER_FAILURE; END IF; This code has proper IF statement indentation with THEN on the next line: IF max_sales > 2000 THEN notify_accounting ('over_limit'); RAISE FORM_TRIGGER_FAILURE; END IF; 3.3.2 Formatting Loops You are going to be writing many loops in your PL/SQL programs, and they will usually surround some of the most complicated code in your application. For this reason, the format you use to structure your loops will make a critical difference in the overall comprehensibility of your programs. PL/SQL offers the following kinds of loops: q Infinite or simple loop q WHILE loop q Indexed FOR loop (numeric and cursor) Each loop has a loop boundary (begin and end statements) and a loop body. The loop body should be indented from the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
boundary (again, I recommend three spaces of indentation). As with the IF statement, you can either choose to leave the LOOP reserved word at the end of the line containing the WHILE and FOR statements or place it on the next line. I prefer the latter, because then both the LOOP and END LOOP reserved words appear at the same column position (indentation) in the program. Here are my recommendations for formatting your loops: q The infinite or simple loop: LOOP executable_statements; END LOOP; q The WHILE loop: WHILE condition LOOP executable_statements; END LOOP; q The numeric and cursor FOR loops: FOR for_index IN low_value .. high_value LOOP executable_statements; END LOOP; FOR record_index IN my_cursor LOOP executable_statements; END LOOP; 3.3.3 Formatting Exception Handlers PL/SQL provides a very powerful facility for dealing with errors. An entirely separate exception section contains one or more "handlers" to trap exceptions and execute code when that exception occurs. Logically, the exception section is structured like a conditional CASE statement (which, by the way, is not supported by PL/SQL). As you might expect, the format for the exception section should resemble that of an IF statement. Here is a general example of the exception section: EXCEPTION WHEN NO_DATA_FOUND THEN executable_statements1; WHEN DUP_VAL_ON_INDEX THEN executable_statements1; ... WHEN OTHERS THEN Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
otherwise_code; END; Instead of an IF or ELSIF keyword, the exception handler uses the word WHEN. In place of a condition (Boolean expression), the WHEN clause lists an exception name followed by a THEN and finally the executable statements for that exception. In place of ELSE, the exception section offers a WHEN OTHERS clause. Follow these guidelines: q Indent each WHEN clause in from the EXCEPTION keyword that indicates the start of the exception section, as I've shown above. Place the THEN directly below the WHEN. q Indent all the executable statements for that handler in from the THEN keyword. q Place a blank line before each WHEN (except for the first). Previous: 3.2 Formatting Oracle PL/SQL Next: 3.4 Formatting PL/ SQL Statements Programming, 2nd Edition SQL Blocks 3.2 Formatting SQL Book Index 3.4 Formatting PL/SQL Statements Blocks 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: 3.1 Fundamentals Chapter 3 Next: 3.3 Formatting of Effective Layout Effective Coding Style Control Structures 3.2 Formatting SQL Statements Because PL/SQL is an extension to the SQL language, you can place SQL statements directly in your PL/SQL programs. You can also define cursors based on SELECT statements. This section summarizes my suggestions for formatting SQL statements and cursors for maximum readability. PL/SQL supports the use of four SQL DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE, and SELECT. Each of these statements is composed of a series of "clauses," as in the WHERE clause and the ORDER BY clause. SQL statements can be very complex, to say the least. Without a consistent approach to indentation and alignment inside these statements, you can end up with a real mess. I have found the following guidelines useful: Right-align the reserved words for the clauses against the DML statement. I recommend that you visually separate the SQL reserved words which identify the separate clauses from the application-specific column and table names. The following table shows how I use right-alignment on the reserved words to create a vertical border between them and the rest of the SQL statement: SELECT INSERT UPDATE DELETE SELECT INSERT INTO UPDATE DELETE FROM VALUES SET FROM WHERE WHERE WHERE INSERT INTO AND SELECT OR FROM WHERE GROUP BY HAVING AND OR ORDER BY Here are some examples of this format in use: SELECT last_name, first_name FROM employee WHERE department_id = 15 AND hire_date < SYSDATE; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SELECT department_id, SUM (salary) AS total_salary FROM employee GROUP BY department_id ORDER BY total_salary DESC; INSERT INTO employee (employee_id, ... ) VALUES (105 ... ); DELETE FROM employee WHERE department_id = 15; UPDATE employee SET hire_date = SYSDATE WHERE hire_date IS NULL AND termination_date IS NULL; Yes, I realize that the GROUP BY and ORDER BY keywords aren't exactly right-aligned to SELECT, but at least the primary words (GROUP and ORDER) are aligned. Notice that within each of the WHERE and HAVING clauses I right-align the AND and OR Boolean connectors under the WHERE keyword. This right alignment makes it very easy for me to identify the different clauses of the SQL statement, particularly with extended SELECTs. You might also consider placing a blank line between clauses of longer SQL statements (this is possible in PL/SQL, but is not acceptable in "native" SQL executed in SQL*Plus). Don't skimp on the use of line separators. Within clauses, such separation makes the SQL statement easier to read. In particular, place each expression of the WHERE clause on its own line, and consider using a separate line for each expression in the select list of a SELECT statement. Place each table in the FROM clause on its own line. Certainly, put each separate assignment in a SET clause of the UPDATE statement on its own line. Here are some illustrations of these guidelines: SELECT last_name, C.name, MAX (SH.salary) best_salary_ever FROM employee E, company C, salary_history SH WHERE E.company_id = C.company_id AND E.employee_id = SH.employee_id AND E.hire_date > ADD_MONTHS (SYSDATE, -60); UPDATE employee SET hire_date = SYSDATE, termination_date = NULL WHERE department_id = 105; NOTE: You can place blank lines inside a sql statement when you are coding that sql from within a pl/ sql block. You may not, on the other hand, embed white space in sql statements you are executing from the sql*Plus command line. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Use meaningful abbreviations for table and column aliases It drives me crazy when a query has a six-table join and the tables have been assigned aliases A, B, C, D, E, and F. How can you possibly decipher the WHERE clause in the following SELECT? SELECT ... select list ... FROM employee A, company B, history C, bonus D, profile E, sales F WHERE A.company_id = B.company_id AND A.employee_id = C.employee_id AND B.company_id = F.company_id AND A.employee_id = D.employee_id AND B.company_id = E.company_id; With more sensible table aliases (including no tables aliases at all where the table name was short enough already), the relationships are much clearer: SELECT ... select list ... FROM employee EMP, company CO, history HIST, bonus, profile PROF, sales WHERE EMP.company_id = CO.company_id AND EMP.employee_id = HIST.employee_id AND CO.company_id = SALES.company_id AND EMP.employee_id = BONUS.employee_id AND CO.company_id = PROF.company_id; Previous: 3.1 Fundamentals Oracle PL/SQL Next: 3.3 Formatting of Effective Layout Programming, 2nd Edition Control Structures 3.1 Fundamentals of Book Index 3.3 Formatting Control Effective Layout Structures 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: 2.7 Block Chapter 3 Next: 3.2 Formatting SQL Structure Statements 3. Effective Coding Style Contents: Fundamentals of Effective Layout Formatting SQL Statements Formatting Control Structures Formatting PL/SQL Blocks Formatting Packages Using Comments Effectively Documenting the Entire Package You can learn everything about a programming language -- its syntax, high-performance tips, and advanced features -- and still write programs that are virtually unreadable, hard to maintain, and devilishly difficult to debug -- even by you, the author. You can be very smart and very clever, and yet develop applications that obscure your talent and accomplishments. This chapter addresses the "look-and-feel" of your code -- the aesthetic aspect of programming. I am sure that you have all experienced the pleasure of reading well-structured and well-formatted code. You have also probably experienced a pang of jealousy at that programmer's style and effort, wondering where she or he found the time to do it right. Developers always experience a feeling of intense pride and satisfaction from carefully and artfully designing the visual layout of their code. Yet few of us take the time to develop a style and use it consistently in our work. Of course, the impact of a coding style goes well beyond the personal satisfaction of any individual. A consistent, predictable approach to building programs makes it easier to debug and maintain that code. If everyone takes her own approach to structuring, documenting, and naming her code, every program becomes its own little pool of quicksand. It is virtually impossible for another person to put in a foot and test the water (find the source of a problem, analyze dependencies, etc.) without being pulled under. I discuss the elements of an effective coding style in the PL/SQL language at this juncture, before we get to any code, for two reasons: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.