The following tips only for PL / SQL Developer 6 or later, version 5 only one part of the property.
1, right-click menu in PL / SQL Developer (below referred to as PLD) in a text editor window for each, such as the SQL Window, Command Window and Porgram Window, right-click an object name will pop up a menu containing the command operation object, we This is called right-click menu. Below:
Object type can be a table, view, synonym, stored procedures and functions and so on. According to the different object types, there are differences between the pop-up menu. Tables and views are View, Edit, Rename, Drop, Query data and Edit data and other functions. View and Edit are to view and modify table structure information, such as fields, primary keys, indexes, and constraints. Query data is equivalent to open a new window, and execute select * from table. Edit data equivalent to open a new window, and execute select * from table for update. Test stored procedures and functions are functions, selected after entering debug state.
Sometimes, because PLD recognition errors, right-click the object and can not come out right menu, the object is located in the DDL or DML statements in front of a semicolon, so that the PLD will be able to determine the correct object type
2, Select for Update
Sometimes we need to put some data into the database, if you fight with the UE Insert statement, would be more trouble, but not strong. SQL Window of PLD can query, add, modify, and delete the contents of the table. Query Needless to say, and add, delete and modify, just in a select statement to join for update, the table row-level locking, and then click on the window lock icon, you can enter edit mode.
Here's how to extract text from Excel into an Excel database, we have three files:
Create temporary table in the database:
create table t1 (cino varchar2 (100), contno varchar2 (100), loanno varchar2 (100))
Then in the SQL Window, enter select t1 for update, and click the lock-type mouse, and enter edit mode:
Right-click on the first line of the input window, then the PLD will deadlock a few seconds, then you can see the cursor in the first row of the input box flashing, use the mouse to CINO, CONTNO, LOANNO selected:
Into Excel, the need to insert the contents of the database is selected, then switch to the PLD, press Ctrl + V:
Click √, then click the Commit button, the data submitted to the table t1, perform select * from t1 can see the contents:
3, PL / SQL Beautifier
PLD 6 above have the function of the DML code formatting. In the SQL Window or Program Window, select the part of the code (if you do not choose the code for the operation of the entire window), select the menu Edit à PL / SQL Beautifier, to be formatted code. For DML statements or illegal DDL statements, PLD will be at the bottom status bar PL / SQL Beautifier could not parse text.
In the default state, PLD will DML statements are ranked in each field a separate line, this is not convenient viewing. In the menu select Edit à PL / SQL Beautifier Options, enter the Preferences window, select Edit, edit the configuration file into the interface:
Select the tab bar DML, in the middle of the window Select, Insert and Update group box, select the Fit, and then click Save, the PLD configuration file to the installation directory, click Close to close. In the Rules file, enter the location of the configuration file, click OK, complete the switch configuration file. Then re-format the code, you can make as much as possible in each field on the line.
4, TNS Names
Menu Help à Support Info à TNS Names, you can view the Oracle tnsnames.ora.
5, Copy to Excel
In the SQL Window Select statement executed, the results came out, right-click on the following data areas, select the Copy to Excel, you can record the data area of the intact copy to Excel. But there are two points to note: First, field can not start with =, otherwise Excel will mistakenly think that is a function; two, not more than 17 numbers, or bits will be set back to zero, but can be preceded by the numbers 'to make Excel that the field is text, while for the database Numbe type of field, it is best to use to_char output, or may not display properly
1, PL / SQL Developer Password Remember me
Using PL / SQL Developer, in order to facilitate the work want to PL / SQL Developer Oracle users to remember login name and password;
Setting method: PL / SQL Developer 7.1.2 -> tools-> Preferences-> Oracle-> Logon History, "Sto
re history "is checked by default, the hook on" Store with password "to re-enter a password at login is remembered.
2, execute a single SQL statement
Using PL / SQL Developer of the SQL Window, press the F8 key, PL / SQL Developer default implementation of the window all the SQL statements, you need to set the mouse to which the SQL statements that execute the current SQL statement;
Setting method: PL / SQL Developer 7.1.2 -> tools-> Preferences -> Window types, hook on "AutoSel
ect Statement "can be.
3 Formatting SQL statements
Using PL / SQL Developer of the SQL Window, sometimes enter the SQL statement too long or too messy, hoping to pass more
Format, then look at the wording used, so it looks look better, beating analysis;
Usage: Select the format of the SQL statement, and then click on the toolbar of the PL / SQL beautifier button.
4, view the execution plan
Using PL / SQL Developer of the SQL Window, sometimes enter the SQL statement execution efficiency analysis in the following table structure,
How can improve the efficiency of query, you can view the Oracle provides for its implementation;
Usage: Select the SQL statement analysis, and then click the Explain plan button on the toolbar (that is, implementation plan), or you can directly press F5.
5, debugging stored procedures
Using PL / SQL Developer Oracle operation, sometimes called some of the stored procedure, or debugging stored procedures;
Call the stored procedure method: First, PL / SQL Developer on the left of the Browser, select Procedures, find the need to call the stored procedure; then select debug stored procedures, right click and select Test, the Test scrīpt in the pop up window. For the type defined in the parameters need to input the parameter values Value; Finally, click the button above the number of articles: Start
debugger or press F9; Finally, click on: RUN or Ctrl + R.
(Specific to tuning a stored procedure, the operator's manual, the instructions about the next application).
6, oralce the use of thin client
To PL / SQL to connect oracle database, in addition to PL / SQL Developer needed in addition to the Oracle client, there is a more convenient way is to use the Oracle thin client, many can be downloaded, the file is very small, less consumption of resources. After installation is complete, modify the installation directory \ Oracle \ ora90 \ network \ ADMIN \ tnsnames.ora file: the following format:
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
# (SERVICE_NAME = dealer)
(SID = SID_NAME)
# (SERVER = DEDICATED)
7 keywords automatically capitalized:
Programming knowledge to understand that people know that coding style is very important. In reading the code, the consistent coding style, is easier to read; in the implementation of efficiency, consistent coding style, are more likely to be placed in the shared SQL area, so it provides the efficiency of execution.
Set is very simple: Tools-> Preferences-> Editor, select the Keyword case Uppercase.
I usually make keywords uppercase, the other such as table names, field names are all lowercase. We should develop a habit of their own code, and maintained.