Plsql developer tips

Plsql developer Tips 1. remember the Logon Password. For ease of work, PL/SQL Developer needs to remember the username and Password for logging on to Oracle. Set options: Tools> Preferences> Oracle> Logon History> Store With Password, "Store history" is selected by default. Just check "Storewith password". Remember to log on again and enter the password once. 2. after logon, My Objects is automatically selected by default. After PLSQL Developer logs on, All objects is selected in Brower. If the user you log on to is dba, expand the tables directory, in normal cases, Wait takes several seconds, and the response speed after selecting My Objects is calculated in milliseconds. Setting Method: Tools menu --> BrowerFilters. The order window of Brower Folders is opened and "My Objects" is set to the default one. Tools menu-Brower Folders: Move the directories you frequently clicked (for example, Tables Views Seq Functions Procedures) to a higher level and add colors to differentiate them, in this way, the average table searching time will be greatly shortened. 3. double-clicking displays the table data. double-clicking the table or view shows the default response. This is really disappointing because I am most concerned about the table structure and data, but these two events did not happen after double-clicking, the default response may be required by experts, but it is important for me to view data and table structures. I don't care about others. However, this can be set. You can double-click and drag and drop the mouse to bind the required event, for example, double-click to edit the data, drag and drop to display the table structure, Yeah! Setting Method: choose Tools> Preferences> Browser to bind double-click and drag-and-drop operations to different Object types on the right. 4. The key words are automatically capitalized Tools-> Preferences-> Editor. Select Uppercase for Keywordcase. In this way, when an SQL statement is entered in the window, the keywords are automatically capitalized, while the others are all lowercase. In this way, it is easier to read the Code and maintain a good coding style. Similarly, in Tools> Preferences> Code Assistant, you can set the upper-case, lower-case, and upper-case letters of database objects. 5. view the execution plan, select the SQL statement to be analyzed, click the Explain plan button on the toolbar (that is, the execution plan), or press F5 directly. This is mainly used to analyze the SQL statement execution efficiency, the structure of the analysis table, which provides an intuitive basis for SQL optimization. 6. Using the custom shortcut key PLSQL Developer, many keys are reserved for user customization. Unlike the domineering Word, basically all the keys have predefined functions, and it is a headache to modify them. Generally, after you open PLSQL Developer, the most common task is to open SQL Window and Command Window. The Shortcut Keys ALT + S and ALT + C are defined for these two operations, in this way, you only need to press the key to take the mouse three times. Setting Method: menu Tools> Preferences> Key Configuration 7. automatic replacement of PL/SQL Developer can also use custom shortcuts like other ides to improve coding efficiency and save time. For example, if we use the most frequent select * from statement in the SQL window, we can set a shortcut key to simplify the input of select * from statement. 12.16.create A shortcuts.txt file and write the following content: s = SELECT * FROM SC = SELECT count (*) FROM copy the code and save it to the installation path of PL/SQL Developer ~ /PlugIns directory 2 ). tools --> Preferences --> UserInterface --> Editor --> autoreplace, select the enableoption, then select the created shortcuts.txt before the page view, and click Apply 3 ). restart PL/SQL Developer, enter s + space in the SQL window, and SC + space for Test 8. execute a single SQL statement (automatically select the statement based on the cursor position) tools> Preferences --> Window types, and check "AutoSelectStatement ". When you use the SQL Window of PL/SQL Developer, press F8. By default, PL/SQL Developer executes all SQL statements in the Window and must be set to the SQL statement where the mouse is located, execute the current SQL statement. Setting Method: Preferences --> Window Types --> SQL Window. Select AutoSelect statement. Note that each statement must be followed by a plus sign. 9. PL/SQLBeautifier (PL/SQL beautifier) PLD 6 and later versions have the DML code Formatting Function. Select some code in SQLWindow or Program Window (if not selected, operate on the code of the entire Window), and select Edit-> PL/SQL Beautifier in the menu to get the formatted code. For invalid DML statements or DDL statements, PLD will prompt PL/SQLBeautifier cocould not parse text in the following status bar. By default, PLD will sort every field in the DML statement in a separate row, which is inconvenient to view. Select Edit à PL/SQL Beautifier Options from the menu, enter the Preferences window, and Select Edit to go to the configuration file editing page: Select DML In the tab bar, Select in the middle of the window, select Fit in the Insert and Update groups, click Save, Save the configuration file to the PLD installation directory, and click Close to Close the configuration file. In Rules file, enter the location of the configuration file and click OK to switch the configuration file. In this case, you can format the code so that each field is on a row as much as possible. When PL/SQL Developer's SQL Window is used, sometimes the entered SQL statements are too long or too messy. If you want to use a common format, it looks better and better for analysis; usage: select the SQL statement to be formatted, and click PL/SQL beautifier In the toolbar. 10. right-click each text editing Window in PL/SQL Developer (PLD), such as SQL Window, Command Window, And Porgram Window, and right-click an object name, A menu containing the operation object command is displayed, which is called the Right-click menu. Objects can be tables, views, synonyms, stored procedures, and functions. The pop-up menu varies depending on the object type. Tables and views include View, Edit, Rename, Drop, Query data, and Edit data. View and Edit are used to View and modify the structure information of a table, such as fields, primary keys, indexes, and constraints. 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 select * from table for update. Stored Procedures and functions have the Test function. After selection, you can enter the debugging status. Sometimes, due to PLD recognition errors, right-clicking an object does not produce a correct menu. You can add a semicolon before the DDL or DML statement of the object, in this way, PLD can correctly determine the object type 11. selectfor Update and Its pasting function sometimes we need to import some data into the database. If we use UE to spell the Insert statement, it will be troublesome and the operability is not strong. The example shows how to extract text from Excel and insert it into the database. There are three columns in our Excel file: create table t1 (cino varchar2 (100) in the database ), contno varchar2 (100), loannovarchar2 (100), enter select t1 for update in SQL Window, and click the lock mouse to enter the editing status: click the input window of the first line. Then, the PLD will be deadlocked for several seconds. Then, you can see the cursor flashing in the input box of the first line. Click CINO, CONTNO, LOANNO and select: in Excel, select the content that needs to be inserted into the database, switch to PLD, press Ctrl + V: Click √, and then click the Commit button to submit the data to table t1, run select * from t1 to view the content. 12. TNSNames menu Help-> Support Info-> TNS Names, you can View the tnsnames of Oracle. ora; 13. copyto Excel execute the Select statement in the SQL Window. After the result is displayed, right-click the data area below and Select Copy to Excel to Copy the records in the data area to the Excel file. But there are two points to note: 1 ). field cannot start with =; otherwise, Excel will mistakenly think of it as a function; 2 ). the number must not exceed 17 digits. Otherwise, the number after the field is set to 0. However, you can add 'before the number to make Excel think that the field is text, at the same time, it is best to use to_char output for Numbe fields in the database, otherwise it may be displayed as abnormal 14. when you use PL/SQL Developer to operate Oracle, you can call some stored procedures or debug stored procedures. To call a stored procedure, first, select Procedures in the Browser on the left of PL/SQL Developer to find the stored procedure to be called. Then, right-click the stored procedure to be debugged and choose Test, in the pop-up Test script window, you need to enter a Value for the Value of a parameter defined as in. Finally, click start debugger or press F9; click RUN or Ctrl + R; 15.or. To use the alce thin client to connect to the oracle database through PL/SQL, in addition to PL/SQL Developer, the Oracle client is also required. A more convenient method is to use the Oracle thin client, download is available in many places, with very small files and less resource consumption. After the installation is complete, modify/Oracle/ora90/network/ADMIN/tnsnames in the installation directory. ora file: Format: 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 ))). 16. the SQL statements written by special Copy in SQL Window usually need to be put in Java or other languages, and need to be converted into strings and added with corresponding hyphens, this task does not need to be repeated. Right-click the prepared SQL statement and use the special Copy function! Setting Method: Right click --> Special Copy