PL/SQL Usage tips

1. PL/SQL Developer remember login password when using PL/SQL developer, in order to work conveniently hope PL/SQL Developer remember login Oracle user name and password; Setup method: PL/SQL Developer->tools->preferences->oracle->logon history, "Definition" in the Right interface, "Store history" is checked by default, then tick "Store with password", you can. If the above method is not good, use the following way: In the above-mentioned interface, "Fixed users", add the user name/password @oracle_sid that need to be directly selected to login, such as: cbsdb/[email protected] re-login At the time, from the login interface of Oracle logon to the username behind ... button, select the user you want to log in to.

3. Formatted SQL statements when using PL/SQL developer, sometimes the input SQL statement is too long or too messy, I hope to use a more general format words, so look good, or analysis; How to use: Select the SQL statement you want to format (you don't need to check it), then click the PL/SQL Beautifier button under Tools.

4. Review the execution plan when using SQL window with PL/SQL developer, it is sometimes the efficiency of the inputs to the execution of the statement, the analysis of the following table structure, how to improve the efficiency of the query, you can view the execution plan provided by Oracle; To use: Select the SQL statement you want to analyze, and then click the Tools-explain Plan button (the execution plan), or press F5 directly.

9. Select for Update Sometimes we need to import some data into the database, if you use the UE spell INSERT statement, it will be more cumbersome, and not strong. PLD SQL window can query, add, modify and delete the contents of the table. Query from needless to say, and new, delete and modify, just after the SELECT statement to add for update, the table row-level lock, and then click the window lock icon, you can enter the editing state.

Here's how to insert text from Excel into a database, we have three columns in our Excel file, create a temp table in the database: Create TABLE T1 (Cino varchar2, Contno varchar2 (100), Loanno varchar2 (100)) then enter select t1 for Update in SQL window and click on the lock mouse, enter the editing state, click the first line of the input window with the mouse, then PLD will deadlock a few seconds, and then you can see the cursor in the first line of the output Into the box flashing, with the mouse to Cino, Contno, Loanno selected, into Excel, the need to insert the contents of the database selected, and then switch to PLD, press CTRL + V, click Yes, and then click the Commit button, the data submitted to the table T1, execute SELECT * The from T1 can see the content.

TNS Names Menu Help->support info->tns Names, you can view the Tnsnames.ora of Oracle.

Copy to Excel executes the SELECT statement in SQL window, after the result, right click on the data area below, select Copy to Excel, you can copy the data area record to Excel. But there are two points to note: (1) field cannot start with =, or Excel will be mistaken for a function; (2) The number should not exceed 17 bits, otherwise the number of digits will be set to 0, but you can make Excel think that field is text by adding ' in the number, and for the field of Numbe type in database, it is best to use to_char output, otherwise it may show abnormal;

14. Quickly find a table or other object with a known table name: On the Tools menu, tick the object Browser, open it, double-click the folder where an object in the Object Browser is located, such as the table is in the Tables folder, and then enter the table name as fast as possible. You can find the object that starts with a few letters that you enter. Enter keywords in the object filter box and enter to blur the query

18. Shortcut key definition location: Tools->preferences->user interface->key Configuration Select the item you want to define, and then press a shortcut key combination, if the shortcut key you pressed is already defined, There will be a hint, at this time cancel, another choice of shortcut key combinations can be; Usually, after opening Plsql developer, the most often do is to open the SQL window and Command window, the two operations have defined shortcut keys, alt+s and ALT + C, so take the mouse point three things only need to press the key. Setup method: Menu Tools, Preferences, Key Configuration

The order of the custom object in Object brower and the default automatically check my Objects after login by default, after PL/SQL developer is logged in, Brower selects all Objects if the user you are logged in to is a DBA, To expand the tables directory, wait a few seconds for the normal situation, and the response rate after selecting my objects is measured in milliseconds. Setup Method: Tools menu (new Configure) Object Brower Filters, will open define Browser Filters interface, check "My Objects" and tick "default"   Can. The Tools menu, Object Brower Folders, opens the Define Browser Folders interface, where you can put several frequently used directories (for example: Tables views Seq Functions Procedures Move up a little bit and color-coded so that your average time to find the table will be much shorter and try it out.

21. Double click to display table data The default response of the mouse to double-click a table or view is disappointing, because I am most concerned about the table structure and data, but the two things do not happen after double-click, perhaps the default response is the master needs, but for me to view the data and table structure is the most important, other I do not care. But the good thing is this can be set, you can give the mouse double-click and drag and drop the required events, such as: Double-click Edit Data, drag and drop display table structure, yeah! Setup method: Menu Tools--(new version is directly Preferences) Preferences, Object Browser, on the right, double-click and drag-and-drop operations for different Object type bindings. For example, in objecttype Select table, select query Data in Double_click action;

2. Execute a single SQL statement (automatically select a statement in SQL window based on the cursor position) when using SQL window with PL-SQL developer, press F8, PL/SQL developer default is all SQL statements that execute the window. It needs to be set to the SQL statement where the mouse is located, that is, executing the current SQL statement; Setting method: PL/SQL Developer->tools->preferences->sql window->window types, hook "AutoSelect Statement" can. Notice that you add a semicolon after each statement.

5. Debug stored procedures sometimes call some stored procedures or debug stored procedures when using PL/SQL developer to operate Oracle;   Method of calling the stored procedure: first, select procedures in the browser to the left of PL/SQL developer to find the stored procedure that needs to be called, then select the stored procedure for debugging, right-click, select Test, and in the test Script window that is bouncing,   For a parameter that is defined as an in type, you need to enter a value for that parameter's value;   Last click on the number of bars button above: Start debugger or press F9;   Last click: Run or Ctrl+r. (in order to tune a stored procedure, please refer to the operating manual, this is probably the application of the instructions).

6. Oralce thin client use to PL/SQL connection to an Oracle database, Oracle clients are required in addition to PL/SQL Developer, and a more convenient way is to use the Oracle thin client, which can be downloaded in many places, with small files, There are also few sources of cost.      After installation, modify the \oracle\ora90\network\admin\tnsnames.ora file in the installation directory: The format is as follows: database_name = (DESCRIPTION = (Address_list =     (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521)) (Connect_data = # (service_name = dealer) (SID = sid_name) # (SERVER = dedicated))

7. Keyword Auto-capitalization: People who know a little about programming know that coding style is important. In terms of reading code, maintaining a consistent coding style, easy to read; In terms of execution efficiency, consistent coding styles are more likely to be placed in the shared SQL area, which provides the efficiency of execution.

In addition, the core of the information system is the database, the first thing to check when the system problem is the SQL statement, how to quickly find the vast log of the SQL statement is a more painful thing. SQL statement capitalization does not solve this problem completely, but finding a line of all uppercase characters in the middle of a heap of code is relatively easy, and your eyes will thank you. The setting is also simple: tools->preferences->editor, keyword case is selected uppercase. I usually have the keyword capitalized, other such as table names, field names, etc. are lowercase. Everyone should develop a coding habit of their own and keep it going.

8. The right-click menu in the PL/SQL Developer (hereinafter referred to as PLD) in each of the text editing windows, such as Window,command window and Porgram window, right-clicking on an object name, will pop up a menu containing the Action Object command, We call this a right-click menu. Object types can be tables, views, synonyms, stored procedures, functions, and so on. The menu that pops up differs depending on the type of object. Tables and views have features such as view, edit, Rename, Drop, Query data, and edit data. View and edit are the structure information for viewing and modifying tables, such as fields, primary keys, indexes, constraints, and so on. Query data is equivalent to opening a new window and executing the SELECT * from table. Edit data is equivalent to opening a new window and executing the SELECT * from table for update. Stored procedures and functions have the test function and can be checked to enter the debug state. Sometimes due to PLD recognition error, right-click on the object does not come out the correct menu, can be in the object's DDL or DML statement before, plus a semicolon, so that PLD can correctly determine the type of object

PL/SQL Beautifier (PL/SQL beautification) PLD 6 or later has the ability to format DML code. Select the part of the code in the SQL window or program window (if you don't select the entire window's code action), and in the menu, choose Edit-PL/SQL Beautifier to get the formatted code. For illegal DML statements or DDL statements, PLD will prompt in the status bar below: PL/SQL Beautifier could not parse text. In the default state, PLD will arrange each field of the DML statement in a separate line, so it is not easy to see. Choose Editàpl/sql beautifier Options in the menu, go to the Preferences window, select Edit, go to the profile editing interface, select DML in the tab bar, in the middle of the window, In the Insert and update groups box, select Fit and click Save to save the configuration file to the PLD installation directory, and click Close. In the rules file, enter the location of the configuration file, click OK to complete the profile switch. Then you can format the code so that each field is as close as possible on one line.

13. When the last SQL script that was opened is re-entered into PL/developer, the Window list can open the document at the time of the Last Exit: (1) The menu Tools->window list option is ticked; (2) on the right side of the Tools->perferences->user interface->options, select "Autosave desktop". (3) Exit PL/SQL Developer re-enter.

15. Quickly close the document window that opens in Windows list: Hold down the SHIFT key and left-click the document window you want to close.

private replication in PL/SQL Developer (special copy) If you are writing SQL and PL/C code in PL/C Developer and then you are using code in other tools, such as a programming language like 3GL, you You may need to convert the code to a slightly different format. Let's assume that you have written and tested such an SQL statement in PL/SQL Developer: Select Deptno, sum (SAL) mgr_sal from emp where job = ' MANAGER ' gr OUP by Deptno ORDER BY mgr_sal Desc

For example, if you want to use this statement in Borland Delphi, you might need a format like this: SQL: = ' select Deptno, sum (SAL) mgr_sal from emp ' + #13 # + ' where job = ' MANAGER ' + #13 # # + ' GROUP by Deptno ' + #13 # # + ' ORDER by mgr_sal Desc ';

The first line requires a preface to the variable that is assigned to SQL, followed by a cr/lf pair. The last line does not need to have cr/lf, but it needs to be terminated with a semicolon. All other rows need only cr/lf to be followed. If <line_1> and <line_n> are the same as <line_*>, you can ignore them. In some languages, you need to use a code-changing sequence for specific characters. For example, in C + +, you would use \ t for the tab character (ASCII code 9). To define these transcoding sequences, use #define Keywords: #define CHAR (9) = \ t #define \ = \ string ("<line_1>\n") + String ("<line_*> \ n ") + String (" <line_n> "); You can also use "#define compress" to indicate that you want to remove all the extra empty characters (spaces, tabs, and line breaks) from the results. Note that the name of the. Copy file will be included in the menu, so you should use a descriptive file name.

17. Easy way to copy Row Records in PL/SQL Developer (1) Click the black triangle to the left of the row record you want to copy, and the row is selected and right-copied. (2) Paste into Notepad, then copy the content you just pasted. (This step is somehow not missing) (3) Click the small black triangle on the left side of the new record and paste right.

Note: If you set a shortcut key that does not work, go back to the key configuration interface, tools---Preferences, click the "..." button on the right of the top "Default Administrator" to play The "Preference set" interface, set the "Personal Preferences" and the "Definition", "Description" below.

19. Display the full path of the file in the window title bar Tools->preferences->user interface->options tick "show complete file path in Windows titles"

22. Remove the italic style of the Comment: Menu tools, Preferences, User interface->editor in the right side of the interface "Syntax highlighting", remove the "Comment" to the right of " Italic "is selected.

PL/SQL Usage tips