PL SQL开发指导

359025439

1. Use the correct datatype so that implicit conversion will be avoided
2. Use bind variable to avoid unnecessary parsing
3. Use BULK COLLECT, % ATTRIBUTES wherever required
4. Use WHEN OTHERS exception as the last resort and handle exceptions
5. Use %TYPE and %ROWTYPE attributes. No code change is required when schema structure changes
6. Write as Little Code as Possible
7. Synchronize Program and Data Structures
8. Center All Development Around Packages
9. Don't declare data in your package specification
10. Avoid writing repetitive code inside your package bodies
11. Never exit from a FOR or WHILE LOOP (cursor) with an EXIT or RETURN statement
12. Don't let functions have OUT or IN OUT parameters
13. Do not use exceptions to perform branching logic
14. Do not " SELECT COUNT(*)“
15. Do not use the names of tables or columns for variable names
16. Walk through each other’s code
17. Adapt a consistent, readable format that is easy to maintain
18. Adapt logical, consistent naming conventions for modules and data structures
19. Standardize module and program headers
20. Tag module END statements with module names
21. Name procedures with verb phrases and functions with noun phrases
22. Self-document using block and loop labels
23. Express complex expressions unambiguously using parentheses
24. Use vertical code alignment to emphasize vertical relationships
 
25. Comment tersely with value-added information
26. Adopt meaningful naming conventions for source files
27. Use CONSTANT declarations for variables whose values do not change
28. Perform complex variable initialization in the executable section
29. Replace complex expressions with Boolean variables and functions
30. Remove unused variables and code
31. Beware of and avoid implicit datatype conversions
32. Package application-named literal constants together
33. Centralize TYPE definitions in package specifications
34. Use package globals judiciously and only in package bodies
35. Move static expressions outside of loop and SQL statements
36. Set guidelines for application-wide error handling before you start coding
37. Catch all exceptions and convert to meaningful return codes before returning to non-PL/SQL host programs
38. Use your own raise procedure in place of explicit calls to RAISE_APPLICATION_ERROR
39. Use WHEN OTHERS only for unknown exceptions that need to be trapped
40. Standardize named application exceptions in package specifications
41. Use incremental COMMITs to avoid rollback segment errors when changing large number of rows
42. Define multi-rows cursors in packages so they can be used for multiple programs
43. Fetch into cursor records, never into a hard-coded list of variables
44. Use COUNT only when the actual number of occurrences is needed
45. Use a cursor FOR loop to fetch all rows in a cursor unconditionally and never use to fetch just one row
46. Specify columns to be updated in a SELECT FOR UPDATE statement
47. Reference cursor attributes immediately after executing the SQL operation
48. Use FORALL to improve performance of collection-based DML
 
49. Never return NULL from Boolean functions
50. Minimize the size of the trigger execution sections
51. Implement server problem logs and “to do” lists using database triggers
52. Validate complex business rules with DML triggers
53. Freeze and build package specifications before implementing package bodies
54. Handle expected and named exceptions when perforiming file I/O
55. Soft-code directory names in your calls to UTL_FILE.FOPEN
56. Backup/Purge process should be done periodically
57. Database health check-up must be performed periodically and if required, it is necessary to resize the database files appropriately
58. Instead of having the full code in a Program or Routine, should split the code into multiple sub Paragraphs which will increase the code readability.