Some tips for PL/SQL DEVELOPER

1. My Objects is automatically selected by default after logon.
By default, after PLSQL Developer logs on, All objects will be selected in Brower. If the user you log on to is dba, it will take several seconds to expand the tables directory, after selecting My Objects, the response speed is calculated in milliseconds.
Tools menu --> Object Brower Filters. The order window of Brower Folders is opened, and "My Objects" is set to default.
Tools menu --> Object Brower Folders, which moves the directories (for example, Tables Views Seq Functions Procedures) that you frequently click to a point and adds the color to differentiate them, in this way, the average table searching time will be greatly shortened. Try again.
/* Setting method: Tools menu -- Brower Folders. The order window of Brower Folders is opened, and "My Objects" is moved to the top.
Similarly, you can move several directories (such as tables Views Seq Functions Procedures) that you frequently click to a point and add colors to differentiate them, which will greatly shorten the average table searching time, try it. */

2. Remember the password.
This is a controversial feature, Because remembering the password will cause data security issues.
However, if the password is the same as the user name in the development database, it makes no sense to enter the password each time. Therefore, PLSQL Developer can remember the password.
Location: Tools menu -- Preferences -- Oracle -- Logon HIstory -- Store with password

3. Double-click to display table data
In PLSQL Developer, I am disappointed with the default response when double-clicking a table or view, because I am most concerned with the table structure and data, but none of these two things 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!
Location: Preferences -- User Interface. On the right side, bind double-click and drag-and-drop operations for different Object types.

4. All SQL statement characters are in uppercase
I think this is a good habit. The core of the information system is the database. When the system goes wrong, the first query is the SQL statement, how to quickly find the SQL statement in the vast log is quite painful.
SQL statements in uppercase cannot completely solve this problem, but it is easier to find a line of all uppercase characters in the middle of a pile of code. Thank you in your eyes.
Set the location in the Editor. At the same time, I think the same width character can reduce the error rate of the program, so all my tools use Courier New. If an IDE does not support this font, I will basically give up. Haha, I'm a guy who is full of picks.

5. Special Copy
The SQL statements written in the SQL Window usually need to be placed in Java or other languages, and you need to convert them into strings and add the corresponding hyphens. This does not need to be repeated, right-click the prepared SQL statement and use the special Copy function!

6. Custom shortcut keys
PLSQL Developer reserves a lot of keys for user customization, which is very high. 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.

7. Others
Quickly clear the TABLE, truncate table, and right-click it to find it.
Two features that are not clearly understood: Analyze and VALID
7. automatically select a statement based on the cursor position in the SQL Window
Setting Method: Preferences --> Window Types --> SQL Window. Select AutoSelect statement. Note that each statement must be followed by a plus sign.

Supplement:

8. Start PLSQL Developer. The window list menu is automatically called out.
You need to set the settings in two steps. First, save the desktop settings and check the Window list option. The specific operations are as follows:
A. select Option from the User Interface in Preference options under Tools of the menu item, and select the check box above in Autosave desktop on the right.
B. Check the Window list option under Tools of the menu item.

9. format SQL)
When you get a long SQL statement and want to quickly view the logic, you can put it in this tool for formatting, and the statement logic will be clear at a glance.

10. The database automatically detects connections
Because the database is automatically disconnected after a period of time and needs to be manually connected, it will always be stuck there due to network issues. The tool provides the connection detection function to automatically connect.
You can select Tools-Preferences-Check connection and Check connection.

11. Common shortcut keys
In fact, as mentioned in Item 1, the following are my habits:
Ctrl + tab switch windows window (or alt + left/right)
Alt + n create new SQL window
Alt + c close current window

Alt + f format SQL

Supplement:

1. Right-click the menu

Right-click an object name in each text editing Window in PL/SQL Developer (PLD), such as SQL Window, Command Window, And Porgram Window, 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.

2. Select for Update

Sometimes we need to import some data into the database. If we use the UE spelling Insert statement, it will be troublesome and the operability is not strong. The SQL Window of PLD allows you to query, add, modify, and delete table content. You can add, delete, and modify a query. You only need to add for update after the select statement, lock the table row, and click the lock icon in the window to enter the editing status. The following describes 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), loanno varchar2 (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: go to Excel, select the content of the database to be inserted, switch to PLD, press Ctrl + V: Click √, and then click the Commit button. Then, the data is submitted to table t1, run select * from t1 to view the following content:

3. PL/SQL Beautifier (PL/SQL Beautifier)

PLD 6 and later versions have the DML code Formatting Function. In SQL Window or Program Window, select Edit-> PL/SQL Beautifier from the menu to obtain the formatted code. For invalid DML statements or DDL statements, PLD will prompt PL/SQL Beautifier 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.

4. TNS Names

Menu Help à Support Info à TNS Names, you can view the tnsnames. ora of Oracle.

5. Copy to Excel

Run 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: first, the field cannot start with =; otherwise, Excel will mistakenly think of it as a function; second, the number should not exceed 17 digits; otherwise, the subsequent digits will be set to 0, however, you can add 'to the front of the number to make Excel think that the field is text. At the same time, it is best to use to_char to output Numbe fields in the database. Otherwise, it may be abnormal.

6. Remember the logon password for PL/SQL Developer

When PL/SQL Developer is used, PL/SQL Developer needs to remember the username and password for logging on to Oracle for ease of work;

Setting Method: PL/SQL Developer 7.1.2-> tools-> Preferences-> Oracle-> Logon History. "Store history" is selected by default. Check "Store with password, remember to enter the password once you log on again. (I don't think so)

7. Execute a single SQL statement

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: PL/SQL Developer 7.1.2 --> tools-> Preferences --> Window types. Just check "AutoSelect Statement.

7. Format SQL statements

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.

8. view the execution plan

When PL/SQL Developer's SQL Window is used, sometimes the efficiency of the input SQL statement execution is analyzed to analyze the table structure. You can view the execution plan provided by Oracle to improve the query efficiency;

Usage: select the SQL statement to be analyzed, click the Explain plan button on the toolbar, or press F5.

9. debug the Stored Procedure

When PL/SQL Developer is used to operate Oracle, some stored procedures are sometimes called or debugged;

Method for calling 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, select the Stored Procedure for debugging and right-click, select Test. in the displayed Test scr limit pt window, you need to enter a Value for the parameter defined as in. Finally, click the number button above: start debugger or press F9. Click RUN or Ctrl + R.

(For details about how to adjust a stored procedure, refer to the operation manual, which describes the application ).

10. Use oralce to streamline the client

To connect PL/SQL to the oracle database, in addition to PL/SQL Developer, you also need the Oracle client. A more convenient method is to use the simplified Oracle client, which can be downloaded in many places and the file is small, less resource consumption. After the installation is complete, 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 )))

11. Automatic uppercase of keywords

Anyone familiar with programming knows that the encoding style is very important. In terms of code reading, it is easier to read with consistent encoding styles. In terms of execution efficiency, consistent encoding styles are more likely to be placed in the shared SQL zone, in this way, the execution efficiency is provided. The setting is also simple: Tools-> Preferences-> Editor, select Uppercase for Keyword case. Generally, I use uppercase keywords. Others, such as table names and field names, are lowercase. Everyone should develop a coding habit and keep it up.