Plsql Developer Tips for use

Plsql Developer Tips for use

Oracle database is believed to be an important back-end support for many enterprises to build their own critical business, and is often used by many developers behind the scenes. But Oracle's own set of client-side tools is not very easy to use, greatly affecting the productivity, fortunately now has the PL/SQL Developer this tool. Now more and more developers become its loyal fans. This article is based on the practical basis, summed up the tools to change the common skills:

1. Remember the login password

For your convenience, PL/SQL Developer Remember to log in to Oracle user name and password; Setup method: PL/SQL Developer 7.1.2->tools->preferences->oracle-> Logon history, "store" is the default check, tick "store with password", and re-login in the input password once remember;

2. my Objects is automatically selected by default 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.

Setup method:

Tools menu--Brower Filters opens the Order window for Brower folders and sets "My Objects" to default.

Tools menu--brower Folders, put a few directories that you often order (for example: Tables views Seq Functions Procedures) Move up a bit, and add color to distinguish, so that your average time to find the table will be greatly shortened.

3. class SQL PLUS window

File->new->command Window This is similar to Oracle's client-side tool SQL Plus, but it's much more useful than it is;

4. automatic capitalization of keywords

Tools->preferences->editor, select the keyword case uppercase. When you enter SQL statements in a window, the keywords are automatically capitalized, and the others are lowercase. In this way, it is easier to read the code and maintain a good coding style, so in Tools->preferences->code Assistant you can set the database object in uppercase, lowercase, capitalized, and so on.

5. View the execution plan

Select the SQL statement you want to analyze, then click the Explain Plan button on the toolbar (that is, execution plan), or press F5 directly; this is mainly used to analyze the efficiency of SQL statement execution, analyze the structure of the table, to provide an intuitive basis for SQL tuning.

6. using custom shortcut keys

PL/SQL developer can also use custom shortcuts like other Ides to improve writing code efficiency and save time.

As we usually use the most frequent select * from the SQL window, we can set a shortcut to simplify the input of the select * from.

1). Create a text file Shortcuts.txt, and write the following:

s = SELECT * FROM

sc = SELECT count (*) from

Copy code is saved to the ~/plugins directory under the PL/SQL Developer installation path

2). Tools-->preferences-->user Interface-->editor-->autoreplace, select the Enable check box, Then browse to the file and select the Shortcuts.txt you created before clicking Apply

3). Restart the PL/SQL Developer, enter the s+ space in the windows, sc+ space to do the test

7. execute a single SQL statement

PL/SQL Developer 7.1.2-->tools->preferences-->window types, hook up "AutoSelect Statement". When you use SQL window with PL/developer, the F8 key, PL/SQL developer, is the default SQL statement that executes the window, and it needs to be set to the one that the mouse is in, executing the current SQL statement.

8.pl/sql Beautifier (PL/SQL beautifying Device)

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.

9. 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

10.Select for Update and its pasting function

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. Examples of 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;

11.TNS Names

Menu Help-and support Info->tns Names, you can view Oracle's Tnsnames.ora;

12.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:

1). field cannot start with = or Excel will mistakenly consider it a function;

2). Do not exceed 17 digits, otherwise the number of digits will be set to 0, but you can make Excel think that field is text by adding ' to the number ', and for fields of type Numbe in the database, it is best to use to_char output, otherwise it may show abnormal

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 test Script window that is bouncing, 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;

14.oralce streamlining the use of clients

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. Modify the/oracle/ora90/network/admin/tnsnames.ora in the installation directory after the installation is complete

File: Format 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)).

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!

Setup method: Right--Special Copy

custom shortcut keys

Plsql developer has a number of keys reserved for users to customize. For example, usually after opening Plsql developer, the most frequently done thing 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.

Setup method: Menu Tools---Preferences Key Configuration

Plsql Developer Tips for use