Some techniques used by Pl/sql DEVELOPER _mssql

1, automatically select my Objects
after logging on by default, Plsql developer will select all Objects after login, if you are logged on as a DBA, To expand the tables directory, it normally takes a few seconds to wait, and the response rate after choosing my objects is measured in milliseconds. The
Tools menu--> Object Brower Filters opens the Order window for Brower folders and sets "My Objects" as default. The
Tools menu--> Object Brower Folders, where you can move a few of your regular directories (for example, Tables views Seq functions Procedures) up a little, and add color distinctions, So your average time to find a table will be greatly shortened, try.
/* Set Method: Tools menu--brower Folders, will open Brower Folders Order window, the "My Objects" to the top can be moved.
by the same token, you can move a few of your regular directories (for example, table views Seq functions Procedures) up a bit, and add color to differentiate, so that 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 give data security problems.
But if it is the development of the library, the password can even be the same as the user name, each input password is really 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 really disappointing to me, because I am most concerned about the table structure and data, but after double-clicking these two things did not happen, 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 that it can be set, you can double-click and drag and drop the necessary events, such as: Double-click Edit Data, drag and drop display table structure, yeah!
Position: Preferences--user Interface, on the right, double-click and drag-and-drop operations for different object types.

4,sql Statement characters All caps
Since this is a good habit, the core of the information system is the database, the system is the first to check the problem is the SQL statement, how to quickly find the vast log of the SQL statement is a more painful thing.
All uppercase SQL statements do not solve the problem completely, but it is relatively easy to find a line of all uppercase characters in the middle of a heap of code, and your eyes will thank you.
Set position in editor. At the same time I think that the equal width character can reduce the error rate of the program, so all my tools exclusively with Courier New, if an IDE does not support this font, I will basically choose to give up. Ah, I'm a guy with a big pick.

5, Special copy
A SQL statement written in SQL window usually needs to be placed in Java or another language, and it needs to be turned into a string and added to the corresponding hyphen, which is not required to be repeated. Right-click on the written SQL, use special copy that ok!

6, custom Shortcuts
plsql Developer has a lot of keys reserved for users to customize, which is a very hight thing. Unlike the overbearing word, basically all the keys have been predefined function, the modification is a headache.
Normally, when you open Plsql developer, the most common thing to do is to open the SQL window and command Window, and define the shortcuts for both operations, Alt+s and ALT + C, so you can just click the key to take the mouse point three times.

7, other
Quickly clear the table, TRUNCATE tables, the right button can be found.
No clear two functions: Analyze, VALID
7. Automatically SELECT statements based on cursor position in SQL window
Set up method: Preferences--> window Types--> SQL window, select AutoSelect statement. Notice that each statement is followed by a semicolon.

Add:

8, start plsql Developer,window list menu automatically pull up
You need two steps, first save your desktop settings, and then check the window list options as follows:
A, select option in the user interface in the Preference option under the tools of the menu item, and tick the previous check box on the right AutoSave desktop.
B, in the menu items under the Tools of the window list options on the hook.

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

10, the database automatically detect the connection situation
Because the database does not operate after a period of time, it will automatically disconnect, and then need to manually connect themselves, because of network reasons, always stuck there. The tool provides the ability to detect connections and can be automatically connected.
Specific settings in the Tools-preferences-check Connection,check connection before the check can be selected.

11, the commonly used shortcut keys
In fact these are mentioned in the 6th item, the following is my custom setting:
Ctrl+tab Switch Windows window (or alt+left/right)
ALT + n Create a new SQL window
ALT + C close the current window

ALT + F format SQL

Add again:

1, right button menu

In Pl/sql Developer (hereinafter referred to as PLD) in each text editing window, such as SQL 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 the right button menu.

Object types can be tables, views, synonyms, stored procedures, and functions. Depending on the type of object, the pop-up menu is also different. Tables and views have features such as view, edit, Rename, Drop, Query data, and edit data. View and edit are viewing and modifying the table's structural information, 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 are checked to enter the debug state. Sometimes because the PLD recognition error, right click on the object does not come out the correct menu, can be in the object of the DDL or DML statement before, plus a semicolon, so that the PLD can correctly determine the type of object

2. Select for Update

Sometimes we need to import some data into the database, if we use UE to insert statements, it will be more cumbersome and not very operational. PLD SQL window can query, add, modify, and delete the contents of the table. Query from needless to say, and new, delete and modify, simply add a for update after the SELECT statement, row-level lock on the table, and then click on the window lock icon, you can enter the editing state. Here's how to extract text from Excel to insert into a database our Excel file has three columns: create a temporary table in the database: Create TABLE T1 (Cino varchar2), Contno varchar2 (MB), Loanno VARCHAR2 (100)) then enter select t1 for Update in SQL window and click on the lock mouse to enter edit state: Click the Input window of the first row with the mouse, then the PLD will deadlock for a few seconds, 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 √, 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 landscaping device)

PLD version 6 has the ability to format the DML code. Select part of the code in SQL window or program window (if you do not select the entire window), select Edit-> pl/sql beautifier in the menu to get the formatted code. For illegal DML statements or DDL statements, the PLD will prompt Pl/sql Beautifier could not parse text in the status bar below. By default, PLD will place each field of a DML statement on a separate line, which is not convenient to view. Choose Editàpl/sql beautifier Options in the menu, enter the Preferences window, select Edit, and enter the profile editing interface: Select DML in the tab bar, selected in the middle of the window, In the Insert and Update group box, select the Fit and click Save to save the configuration file to the PLD installation directory and click Close. Enter the location of the configuration file in the rules file and click OK to complete the configuration file switch. Then you can format the code so that each field is on one line as much as possible.

4. TNS Names

Menu Helpàsupport infoàtns Names, you can view Oracle's Tnsnames.ora.

5, Copy to Excel

Execute the SELECT statement in SQL window, after the result, right click on the data area, select Copy to Excel, you can copy the record of the data area to excel. But there are two points to note: First, field can not start with =, otherwise Excel will be mistaken for a function, and two, the number should not exceed 17 digits, otherwise the number of digits will be set to 0, but can be by adding ' to the number ' to make Excel think that the 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 appear abnormal

6, Pl/sql Developer Remember login password

In the use of Pl/sql developer, in order to work conveniently hope Pl/sql developer remember to login Oracle username and password;

Setup method: Pl/sql Developer 7.1.2->tools->preferences->oracle->logon History, "store History" is the default check, "Store With password ", you can log in again and remember to enter the password once. (I can't seem to.)

7. Execute a single SQL statement

When using Pl/sql Developer SQL window, press the F8 key, Pl/sql developer The default is to execute all of the SQL statements of the window, you need to set the mouse is the SQL statement, that is, execute the current SQL statement;

Set method: Pl/sql Developer 7.1.2-->tools->preferences-->window types, on the hook "AutoSelect Statement" can be.

7. Format SQL statements

When using Pl/sql Developer SQL window, sometimes the input of the SQL statement is too long or too messy, hope to be able to use a more general format words, so that looks good, or analysis;

How to: Select the SQL statement you want to format, and then click the Pl/sql beautifier button on the toolbar.

8. View the execution plan

When using Pl/sql Developer SQL window, sometimes the efficiency of the input SQL statement, the analysis of the following table structure, how to improve the efficiency of the query, you can view the implementation plan provided by Oracle;

How to: Select the SQL statement you want to analyze, and then click the Explain Plan button (that is, the execution Schedule) of the toolbar, or press F5 directly.

9. Debug stored Procedure

When using Pl/sql developer to manipulate Oracle, some stored procedures are sometimes invoked, or stored procedures are debugged;

Methods to invoke a stored procedure: First, select procedures in the browser to the left of Pl/sql developer, find the stored procedure that needs to be invoked, and then select the stored procedure to debug, right-click, and select Test, and in the pop-up Test Scrīpt window, For a parameter that is defined as in type, the value of the parameter needs to be entered, and the last number of buttons is clicked: Start debugger or press F9, and then click: RUN or Ctrl+r.

(Specific to the mode of a stored procedure, please refer to the Operation manual, the general description of the application).

10, the use of Oralce streamlined client

For Pl/sql to connect to Oracle databases, there is a more convenient way to use Oracle clients than Pl/sql Developer, 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 under 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, keyword automatic capitalization

people who understand a little bit of programming knowledge know that coding styles are important. In terms of reading code, maintaining a consistent coding style, easy to read, and consistent coding style in execution efficiency, and more likely to be put into shared SQL areas, provides the efficiency of execution. setting is also very simple: tools->preferences->editor, will keyword case select uppercase. I usually make the keywords uppercase, others such as table name, field name, etc. are lowercase. Everyone should develop a coding habit of their own and keep it going.