Some tips for using PL/SQL developer

Source: Internet
Author: User
Tags beautifier

1, default automatically check my Objects after login
By default, after Plsql developer is logged in, Brower will select all objects, and if you are logged on as a DBA, you will need to wait a few seconds for the tables directory to expand, and the response rate after selecting my objects is in milliseconds.
The Tools menu, Object Brower Filters, opens the Order window for Brower folders and sets the "My Objects" to default.
Tools menu--Object Brower Folders, a few directories that you often point to (such as: Tables views Seq Functions Procedures) Move up a bit, and add color to distinguish, This way your average look-up time will be greatly shortened, try it.
/* Set Method: Tools menu--brower Folders, will open the Brower Folders Order window, the "My Objects" to the top.
In the same way, you can move a few of the directories you often point (such as: Tables views Seq Functions Procedures) moved up a little, and add color to distinguish, so your average time to find the table will be greatly shortened, try. */

2, remember the password
This is a controversial feature because remembering passwords can be a problem for data security.
But if it is the development of the library, the password can even be the same as the user name, each time to enter a password is meaningless, you can consider let Plsql developer remember the password.
Location: Tools menu--preferences--oracle--logon history--store with password

3, double-click to display table data
Plsql developer The default response when you double-click a table or view in the mouse is disappointing, because I am most concerned about the table structure and data, but the two things do not happen after double-clicking, perhaps the default response is the master needs, but for me to view the data and table structure is the most important, the 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!
Location: Preferences--user Interface, on the right, double-click and drag-and-drop operations for different object types.

4,sql Statement characters all uppercase
Think this is a good habit, 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 that SQL statement in the vast log 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.
Set the position in editor. At the same time I think the equal width character can reduce the program error rate, so all my tools exclusively with Courier New, if an IDE does not support this font, I would basically choose to give up. Ah, I'm a guy full of tiglate.

5, Special copy
SQL is written in SQL window usually need to put in Java or other languages, you need to turn into a string and add the corresponding hyphen, this thing does not need to repeat, in the written SQL right-click, using special copy is ok!

6, custom shortcut keys
Plsql developer has a lot of keys reserved for users to customize, which is very hight thing. Unlike the overbearing word, basically all the keys have been pre-defined features, the change is very headache.
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 that the mouse point three things only need to press the key.

7, other
Quickly empty the table, TRUNCATE table, right-click can be found.
It's not clear. Two features: Analyze, VALID
7. Automatically SELECT statements based on cursor position in SQL window
Setup method: Preferences---window Types---SQL window, AutoSelect statement selected. Notice that you add a semicolon after each statement.

Add:

8. Start Plsql Developer,window List menu automatically
A two-step setup is required to first save the desktop settings and then tick the window list option, as follows:
A, select option in the user interface in the Preference option under Tools in the menu item, and tick the front check box on the right for AutoSave desktop.
B. The window list option is checked under Tools in the menu item.

9. Formatted SQL (format)
When you get a long SQL statement, you want to quickly see the logic, you can put it in this tool, format, the logic of the statement is clear

10. Automatic connection of database

Because the database does not operate for a period of time, it will automatically disconnect, and then need to manually connect themselves, because of the network, always stuck there. The tool provides the ability to detect connections, which can be automatically connected.
The specific settings are checked in front of the Tools-preferences-check Connection,check connection.

11. Common Shortcut Keys

In fact these are mentioned in the 6th item, here are my custom settings:
Ctrl+tab Switch Windows window (or alt+left/right)
ALT + n Create new SQL window
ALT + C close the current window

ALT + F format SQL

Add again:

1. Right-click menu

In the PL/SQL Developer (hereinafter referred to as PLD) in each of the text editing window, such as Window,command window and Porgram window, right-click 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

2. Select for Update

Sometimes we need to import some data into the database, if you use the UE to spell INSERT statement, it will be more troublesome, and not strong operability. 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 extract text from Excel insert into a database there are three columns in our Excel file: Create a temporary table in the database: Create TABLE T1 (Cino varchar2, Contno varchar2 (+), Loanno VARCHAR2 (100)) then enter select t1 for Update in SQL window and click on the lock mouse to enter the editing state: Click on 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 input 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 * FROM T1 can see the content:

3. 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 PL/SQL Beautifier could not parse text in the status bar below. 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.

4. TNS Names

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

5. Copy to Excel

Execute the SELECT statement in SQL window and, after the result, right-click on the data area below and select Copy to Excel, and you can copy the data area's record to Excel. But there are two points to note: One, field can not start with =, or Excel will be mistaken for function, second, the number should not exceed 17 bits, otherwise the number of digits will be set to 0, but can be by the number in front of ' to make Excel think that field is text, At the same time for the Numbe type of field in the database, it is best to use to_char output, otherwise it may show abnormal

6, PL/SQL developer remember login password

When using PL/SQL developer, you want PL/SQL developer to remember the username and password to log in to Oracle for your convenience;

Setup method: PL/SQL Developer 7.1.2->tools->preferences->oracle->logon, "Store History" is checked by default, tick "store With password "can, re-login in the input password once remember. (I can't seem to.)

7. Execute a single SQL statement

When using PL/SQL developer, press the F8 key, PL/SQL developer is the default to execute all of this window, and need to be set to the SQL statement that the mouse, that is, the execution of the current SQL statement;

Setup method: PL/SQL Developer 7.1.2-->tools->preferences-->window types, check "AutoSelect Statement".

7. Formatting SQL statements

When using PL/SQL developer, sometimes the SQL statement entered is too long or too messy, I hope to use a more general format words, so look good, or analysis;

To use: Select the SQL statement that you want to format, and then click the PL/SQL Beautifier button on the toolbar.

8. View Execution Plan

When using the SQL window of PL/developer, sometimes the efficiency of input SQL statement execution, 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 Explain Plan button on the toolbar (that is, execution plan), or press F5 directly.

9. Debugging Stored Procedures

When using PL/SQL developer to operate Oracle, some stored procedures are sometimes called, or stored procedures are debugged;

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 pop-up Test scrīpt window, For a parameter that is defined as an in type, you need to enter a value for the parameter's value; Last click 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).

10, Oralce streamline the use of the client

For PL/SQL to connect 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 and low cost sources. 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 Capitalization of keywords

knowing a little about programming common sense, coding style is important. In terms of reading code, maintaining a consistent coding style is easier to read, and maintaining a consistent coding style in terms of execution efficiency is more likely to be put into a shared SQL zone, which provides the efficiency of execution. The setting is also simple: tools->preferences->editor, keyword case is selected uppercase. I usually have the keyword capitalized, other things such as table name, field name, etc. are lowercase. Everyone should develop their own coding habits and keep going.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.