Database Terminology: Site (Site) has been established or to be established, such as the Chinese Station (China), International Station (Alibaba), Chinese Business Station, etc. OLPS Online Process System, online processing system, that is, our member can browse the database connected to the database, is also the front desk we said. BOPS Back Office Process System, a background processing system, a publishing system of our website. BZO Business Object (?), The company's low-level Java object is mainly responsible for dealing with the database. First, naming agreement 1. It is a naming agreement to database objects such as Table, Sequence, Procedure, Column, etc. 2. Named meaning English vocabulary, in addition to individual universal (see list), avoid using abbreviations), multiple words, scriberal segmentation, 3. In addition to the database name length of 1-8 characters, the rest is 1-30 characters, and the Database Link name should not exceed 30 characters; 4. Naming can only use English letters, numbers, and underscores; 5. Avoid using Oracle's reserved words such as Level, keywords such as Type (see a list); 6. The relevant column names between the table are as namen; 7. Named of the database: The online database is named "OLPS" represents 2-4 characters of the site (Site, the following), the background database named "BOPS" represents 2-4 characters of the site. The test database is named "OLPS | BOPS" "TEST", the development database is named "OLPS | BOPS" "TEST", distinguishes different sites different from the schema / user. 8. Index naming: Table_name column_name index_type (1 byte) IDX, each part is divided (_) split. Column names composed of more words, take a few words first letters, and the end of the words column_name.
Such as: Sample Table MEMBER_ID INDEX: Sample_MID_IDX News Table Title's unique index: news_titile_uidx; 9. Sequence naming: SEQ_ TABLE_NAME; Second, Note Description 1. This annotation is mainly used for PL / SQL programs and other SQL files, others can be referenced; 2. There are three comments accepted by SQLPlus: - This is a comment / * This is a comment * / remote is a comment 3. Start comments, similar to start comments in Javak, mainly listing file names, writing dates, copyright instructions, programming functions, modified records: Rem $ header: FileName, Version, Created Date, Auther Rem Rem Copyright Rem Function Rem Function Explanation Rem Notes Rem Modified (YY / MM / DD) Rem Who When - for What, Recently Goes First 4. Block comments, as a table comment, Procedure Comments, etc. Single line comments, such as column notes: login_id varcha2 (32) Not null, - Member ID 3, indent low-level statement after high-level statement, generally indented 4 spaces: Declare v_memberid varcha2 (32), begin select admin_member_id INTO V_MEMBERID from Company WHERE ID = 10; DBMS_OUTPUT.PUT_LINE (v_memberid); END; the indentation of different parts of the same statement, if the Sub Statement is usually 2 spaces, if it is closely related to a part of the previous sentence, then aligned therewith to reduced: BEGIN FOR v_TmpRec IN (SELECT login_id, gmt_created, - here indented as column above satus FROM member - sub statement WHERE site = 'china' AND country = 'cn') LOOP NULL; END LOOP; END; Fourth, breaking • One line can not exceed 80 characters • The same statement is between the other words • Spaces after the comma • Sales, other split spaces SELECT OFFER_NAME || ',' || Offer_count as offer_category, ID from category where sar_category_id_1 = 0; five Case, a keyword size, a table name, column name, etc. of Oracle.
6. Selection of column types • Use char (1) instead of Boolean values; • Try to use VARCHAR2 instead of char type; • VARCHAR (2) Up to 4000 characters; • Date is accurate to microseconds, not day; • Use Clob to replace Long , BLOB instead of long Raw; • Oracle only has a data type, please give a given length when using; seven, the primary key selection Select meaningful, not too long and uniquely identified the column of the record line, no such column It is considered to use Sequence to do the job health. 8. The selection of the column length should select the column length according to actual needs. There is a corresponding web page, which is consistent with the column length on the page. Verification of data is controlled in the performance layer in addition to the database end implementation. Nine, use the SQL statement of the agreement 1. Try to avoid using SQL statements in the loop. 2. Avoiding a function in the WHERE sentence: select * from service_promotion where to_char (gmt_modified, 'yyyy-mm-dd') = '20001-09-01'; should be used: SELECT * from service_promotion where gmt_modified> = to_date ('2001-9-01', 'YYYY-MM-DD') AND GMT_MODIFIED Twelve, the definition of table repeat attributes can not have multiple repetitive attribute columns; for multiple selection repetitive attribute columns, if you do not query columns and more repetition, you should use ID_VARRAY instead, if you want to use it as a query column , The number of repetitions, should be built another table, and the number of times and the column value should be used, and the Number type, an add-on operation should be used. 13. Data modification agreed to discover the data in the Production environment, need to be corrected, should be submitted to the data update form on the RA Terminal, and the database operator has been modified before 17 o'clock. Database more programmed structural changes and data updates, similar processing. Submitting SQTT tests requires data or update structures, temporarily write to the test request, and the database operator is modified by the database operator by SQTT Comrade Mail (indicate the test.). After the internal network TEAM adds a new form, the test request is submitted separately. XIV. Database design processes meet in the design phase of the new project, there should be DBA participation. Encoding can be started after SCHEMA. Abbreviations list: payment pymt my trade activity mta list of special characters: ACCESS DECIMAL INITIAL ON START ADD NOT INSERT ONLINE SUCCESSFUL ALL DEFAULT INTEGER OPTION SYNONYM ALTER DELETE INTERSECT OR SYSDATE AND DESC INTO ORDER TABLE ANY DISTINCT IS PCTFREE THEN AS DROP LEVEL PRIOR TO ASC ELSE LIKE PRIVILEGES TRIGGER AUDIT EXCLUSIVE LOCK PUBLIC UID BETWEEN EXISTS LONG RAW UNION BY FILE MAXEXTENTS RENAME UNIQUE FROM FLOAT MINUS RESOURCE UPDATE CHAR FOR MLSLABEL REVOKE USER CHECK SHARE MODE ROW VALIDATE CLUSTER GRANT MODIFY ROWID VALUES COLUMN GROUP NOAUDIT ROWNUM VARCHAR COMMENT HAVING NOCOMPRESS ROWS VARCHAR2 COMPRESS IDENTIFIED NOWAIT SELECT VIEW CONNECT IMMEDIATE NULL SESSION WHENEVER CREATE IN NUMBER SET WHERE CURRENT INCREMENT OF SIZE WITH DATE INDEX OFFLINE SMALLINT CHAR VARHCAR VARCHAR2 NUMBER DATE LONG CLOB BLOB BFILE INTEGER DECIMAL SUM COUNT GROUPING AVERAGE TYPE only for reference, welcome to discuss technical PL / SQL PL / SQL coding rules should follow Author: Steven Feuerstein Four simple and easy guidelines with the number of PL / SQL code numbers and quality I start writing PL / SQL code since 1990. This means that I have written a tens of thousands of software code, but I am sure that most of the code is very poor, and it is difficult to maintain. Fortunately, I found that I found and follow the new way to write better code is still not too late. It was last year, my code quality has been significantly improved; these improvements are mainly due to the development of some simple rules, and complied with discipline. This article puts four suggestions for PL / SQL novices and experienced developers; abide by any one, your code quality will increase. These four suggestions are adopted, you may be surprised to discover: You are a very good programmer, far from your imagination. All work is complete We rarely have someone working; most PL / SQL development work is carried out in a relatively large mechanism. But we basically work alone in your own small compartment. There is almost no PL / SQL development team for regular code review or system testing. I can't change the basic state of your development team through this article. Therefore, I carefully select the following suggestions. Implementing any point does not need to be consent. Regardless of your group is small, you don't have to let everyone agree with these coding rules. You just need to change your own coding method as follows: 1. Strictly follow the naming conventions, as if they are your life pillars. 2. Qualifies the preparation of SQL hobby: The less SQL written is, the better. 3. Make the execution part short: bid farewell to "Italian top code". 4. Find a partner: I agree with someone to supervise your work. 1. Follow the naming agreement If you build and strictly follow a set of naming conventions, especially for application components, you can save a lot of time. Of course, there is no new idea that follows the name agreement, you may have been annoyed. So I don't propose a magnificent naming plan, but give some very specific and clear agreements, then prove how useful. I have been designing a new tool for PL / SQL developers a few months ago. It is named SWYG (which can be found in www.swyg.com), can help programmers complete the code generation, test, and reuse. It has several unique components. I specified a abbreviation name consisting of two letters as follows: SF-SWYG's basic part of SM-SWYG's metadata SG-SWYG generating program SL-SWYG's code base ST-SWYG unit test So I follow the naming agreement in Table 1 and use these abbreviations. What are the benefits of following these conventions? Generally speaking, if I ask consistent naming rules, I can write code more and more efficiently. It is clear that these conventions have predictable, meaning that I have written SQL program generate useful scripts. For example, by using the convention in Table 1, the installation script of all the basic packets in the SWYG can be generated. SQL * PLUS scripts that perform these work are shown in Listing 1. This type of script is very useful because it means that I don't have to maintain the installation script manually. When I add another table to the SWYG scheme, when I generate a group of related packages, I just run my script, and the updated installation script will jump out. 2. Qualify the preparation of SQL hobby The less SQL written, the better, which seems to be unintended to our intuition. For PL / SQL developers, this is a strange suggestion, because one of the main advantages of PL / SQL is to write SQL statements in the code effortlessness. However, this simpleness is also a fatal weakness of this language. A pure SQL statement can be placed directly in the PL / SQL code without the need for JDBC or an ODBC. Therefore, whenever, the PL / SQL developers will usually embed the SQL statement from their application code as long as they need SQL statements. So what is the problem? Using SQL statements everywhere in the PL / SQL code will inevitably lead to the following consequences: Although the actual performance is different, the same logical statement still repeats, resulting in excessive grammar analysis, and it is difficult to optimize the performance of the application. Exposure of business rules and programs. This includes logic that executes business rules directly in the SQL statement. These rules are always changing, so the maintenance cost of the application will increase dramatically. Of course, every PL / SQL application you have to write is almost all based on the base table and view. You need to perform SQL statements. The problem is not whether it is executed, but when is executed, how to execute. If you encapsulate the data structure or hide them in a PL / SQL code layer (usually a code package), then your application will be more robust, and you will also find more and more easier. . Let's take a simple example. Assume that I need to write a program that handles a work of an employee. The first thing is to get the full name of the employee, defined as "Name Comma (,)"; then I can analyze. Listing 2 gives an example of such code I am likely to write in this case. Everything seems to be so simple and straight; what do these codes may have? It is actually very bad. The most important thing is that I expose a business rule: a full name. I may take a few hours to test this code and its application. But when I just put into use, I only knew that the customer would constantly call me, in fact, their full name should be expressed as "name-free surname". What should we do now? Search all single comma in quotation marks? Realistic solutions are to use hidden details, only provide a set of predefined, predictive and pre-optimized and can complete all tasks. Listing 3 is a process_employee process that is rewritten based on the package code. The HR_EMPLOYEE_TP package provides a type for defining a local variable of the saved name; HR_EMPLOYEE_RP contains a function of returning a full name based on a business rule. It is easy to display the PL / SQL statement to the SQL code. Similarly, it is important to discuss how these statements are important. On the other hand, the code to write to the encapsulation task is challenging; even unrealistic. Generate these packages may be more meaningful. A few years ago, I helped building such a generating program. The block is PL / Generator, which is now owned by Quest Software, and the PL / SQL development community can be used free of charge. You can download from my website www.stevenfeuerstein.com/pute/gencentral.htm. It is to know that its package architecture is different from the agreement I summarized earlier. PL / Generator created a separate package that contains all the contents of the table, query and change logic. When you no longer write too much SQL, but when you call the execution SQL program, your application will benefit your application whether you are generating or preparing your own custom packaging. 3. Small the execution part In the face of reality: always with our judgment and the latest series of new year resolutions, we must stop writing the pasta code: huge and lengthy, people can't understand them, not to say maintenance or upgrade. How can I avoid "Italian noodles"? In fact, the answer is simple: I will never allow the execution to more than 50 or 60 lines. This size allows you to view the entire logical structure of the code block on one sheet or one screen, which means you can realize the intent of the program, and you can understand it completely. You may agree with the above views, but at the same time, we laugh at me: The program code will never exceed 50 lines. Yes, you should laugh, because this is of course impossible. There is no doubt that you need more than 50 rows of executable code; the problem is where you put these code and how you organized. If you take the following practice, you can deal with a variety of complex requirements and limit the code within 50 lines: Place all business rules and discrete logic blocks in its own procedure (usually a function), thereby cauting to reuse the code at any time. Try to use partial modules, procedures, and functions defined in the program's declaration section. Assume that I have prepared a call center application. I need to write a program that it is necessary to meet the following requirements: "For each employee of a particular department, it is compared to the average workload of the employees of the staff. If a employee The workload is lower than the average workload, and the next pending call is assigned to this person, and the agreement is arranged based on this situation. " I learned from the previous work: My friend Claudia has written an analysis package, which will return information on workload. However, assignment to handle calls and arrangements are all new work, and the rest of the demand document has been described in detail. Initially I want to read this 15 pages, but I don't do that. I used a technology called "Step-seeking" or "by the top-down design" and presented the code in Listing 4 to implement the program. The interpretation of the most critical code rows in Listing 4 is given below; the final start of the program (compact execution section) is performed. This seems to be discouraged, but this is indeed the best way to read the procedures that are gradually prepared to prepare. 22 to 30 lines. Use a cursor for loop to iterate all employees of the designated department. On lines 24-25, the program in the analysis package is used to determine whether the current employee is not working. In line 27-28, three programs are called: assign_next_open_case, schedule_case, and next_appinkment. I don't know how to implement these programs, but I know that they express their work that needs to be completed in advance through their name and parameter table. Rows 10-19. Create "Stub" for the three programs in the 27th to 28th, which is the placeholder. Note that they are partial modules, defined in Assign_Workload, and cannot be called from any of the other programs. Articles 5 ~ 8. Define a cursor to get all the employees of the designated department. You can now compile this code. Successful completion of such a small program is like a small victory, and it is true. Complete correctly, then simply test, then add a little code, then correctly compile, so that small victories such as this class creates excellent constructive procedures, and it will be very satisfied. I can also verify that the analysis program is effective and identifies the right employee to be assigned. After these works are all completed, I will pick up one from three programs, such as assign_next_open_case, perform the fine design of the next or next level. I want to read the documentation of this task and write a short execution section in Assign_Next_Open_Case, which reflects the overview of the task. Soon, my partial process has its own partial processes and functions, but in every step of the process, my code is very short, readable, easy to test, can be adjusted as needed. 4. Find a good partner Computers are not programmed, talent will. How many times, you bent your waist, the hump is sitting in front of the computer, because you can't find the error in your code, is it very depressed? It was a few minutes a few minutes, then after a few hours. Finally, I am bored with yourself, I feel very failed, you reach your head and ask your friends to help you see. There is usually one of the three situations: When your friend stands up from her chair, everything is very clear. Your friend glances at the screen, immediately pointed out the problem. Your friend is not responsible for the part you do in the system, so you must show what your program is doing. When you gradually explain the logic, the problem that causes the wrong problem will suddenly expose to you. The fact is that it is difficult to debug your code, because you have an investment to it, too focused. Next step Read more information about Oracle Database 10g Oracle.com/Database More articles in Feuerstein Otn.racle.com/oramag/webcolumn/2003 The best solution for this problem is to create such a culture by the development manager: Various ideas is sharing, do not understand is that it can be forgiven and will not be punished and regularly constructive code review. Unfortunately, these cultural changes are difficult to achieve. At the same time, I suggest that you should take the lead in helping to change the culture of your team. Find another developer, it is better than you experience, and build a "partner" relationship: When there is a problem, he can act as your staff, of course, you can act as his staff. Cooperation beforehand: I don't know if all the answers have nothing wrong. Then make a simple rule for yourself: don't think more than half an hour for a mistake. After 30 minutes, you call your partner, let human psychology serve you, not to your right. Four steps to get a new way of work This article provides you with four steps that can be taken to chase your programming experience without investing new tools or change the workflow of the entire team. These four steps can even follow, as long as you follow a step, you will benefit you. Steven feuerstein (Steven@stevenfeuerstein.com) is an authoritative in PL / SQL language. FeUerstein wrote 9 books about PL / SQL (all published by O'Reilly & Associates published), including "Oracle PL / SQL Best Practices" and "Oracle PL / SQL programming (Oracle PL / SQL Programming ". He is still a senior technical consultant from Quest Software. Object Name structure Comment Generate (DDL) file table Table names, such as SM_TASK, used to define the tasks defined in a normal Mentat component. Primary key column id The standard is: (almost) each table contains a ID column generated in a sequence. There are some exceptions to the crosstab. N / a Sequence used to generate primary keys Used for a sequence name of a table primary key, such as SG_Script_Seq, which generates a new primary key for the Mentat generating script table. Query package It contains standard APIs to retrieve data in a base table (such as sl_source_qp), which helps to query the source code of each element in the Mentat recreate library. Description: Change the package A standard API containing data in a base table (such as st_unittest_cp), which enables me to maintain the unit test definition. Description: Contains predefined types for specifying tables (such as SG_GEN_RUN_TP) - including collections, ref cursors, records. Description: Rule package Contains programs, usually a function, which hides details of business rules that are related to the entity. Description: Other object packages Contains custom logic of specific entities. Description: Keyword
