PL/SQL Oracle

http://uule.iteye.com/blog/2061773 (source)

Bulk Import Script :

Open a Command Window > enter @ > It will let you select the file you want to import (you need to know the order of the imported files, Plsql will not automatically recognize this or write a script like LS)

such as: @d:\svn\1.5 database Script \oracle\import2.sql

http://blog.csdn.net/hbhgjiangkun/article/details/8208565

1, 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 Developer7.1.2->tools->preferences->oracle->logon history, "Store" is checked by default, tick "store With password "can, re-login in the input password once remember.

2. 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;

Setting method: PL/SQL Developer7.1.2-->tools->preferences-->window types, hook on "AutoSelect Statement" can.

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

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

================================================================================

1. 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 you often order (such as: Tables views Seq Functions Procedures) Move up a bit, and add color to distinguish, so your average time to find the table will be greatly shortened, try.

3. Double-click to display the table data

The default response when the mouse double-clicks a table or view is disappointing because I am most concerned with 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!

Setup method: Menu Tools---Preferences--Browser, 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.

Setup method: Menu Tools--Preferences---Keyword case-to-uppercase

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!

Setup method: Right--Special Copy

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.

Setup method: Menu Tools---Preferences Key Configuration

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.

8. 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;

Setting method: Tools->preferences-->window types, hook on "AutoSelect Statement" can.

9. Automatic capitalization of keywords

tools->; preferences->; Editor, select the keyword case uppercase. The keyword is usually capitalized, other such as table name, field name, etc. are lowercase. Everyone should develop a coding habit of their own and keep it going.

10. Change the default execution shortcut key F8 to Ctrl+enter (similar to toad)

Tools->preferences->key Configuration->session/execute, then press Ctrl+enter.

11. Autoreplace Auto-replace function

Tools->preferences->editor->autoreplace-> Select the enabled, and edit the file, adding the following content:

Sf=select * FROM

Scf=select Count (*) from

S=select

F=from

W=where

D=delete

U=update

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;

Select for Update and its paste function: Sometimes we need to import some data into the database, if using the UE spell INSERT statement, it will be more troublesome, and not strong. 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;

TNS Names: Menu Help-and support Info->tns Names to view Oracle's Tnsnames.ora;

Copy to Excel: Executes the SELECT statement in SQL window, after the result, right click on the data area below, select Copy to Excel, you can copy the data area 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 ' in the number ', and for the field of Numbe type in the database, it is best to use to_char output, otherwise it may display abnormal remember 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;   

  

PL/SQL Developer import and Export database method and description

PL/SQL Developer is the primary tool for importing or exporting databases in Oracle database, this article mainly introduces the process of using PL/SQL developer to import and export databases, and describes some considerations when importing or exporting.

Export steps:

1 Tools->export User Object Select option to export the. sql file.

2 Tools->export tables-> Oracle Export Select option exports. dmp files.

Import steps:

Note: It is better to delete the previous table before importing, except of course the import database.

1 tools->import tables->sql inserts import. SQL file.

2 tools->import talbes->oracle Import before importing the DMP file.

Some notes:

Tools->export User Objects exports the build table statements (including the storage structure).

Tools->export tables contains three kinds of export methods, three ways to export table structure and data, as follows:

Oracle Export

SQL Insert

PL/SQL Developer

The first is the file format exported to. DMP, which is binary, can be cross-platform, can also contain permissions, and is very efficient and widely used.

The second is exported as. sql files, can be viewed with a text editor, versatility is better, but less efficient than the first, suitable for small data import and export. In particular, it is important to note that there are no large fields (Blob,clob,long) in the table, and if so, you will not be able to export (prompt for the following: table contains one or more long columns cannot export in SQL Format,user PL/SQL Developer format instead).

The third is exported to. Pde format, the. PDE is PL/SQL Developer's own file format, can only be imported and exported by PL/SQL developer, and cannot be viewed with the editor.

About Oracle Database with PL/SQL Developer import and Export database knowledge is introduced here, I hope this introduction can bring you some harvest, thank you for your browsing!  

=============================================================

1, how to bulk export the build table statement?
SELECT [tools]–] [Export User Objects ...] from the menu and select the table you want to export in the open window.
There is no INSERT statement in the SQL script exported in this way, that is, the data in the table is not exported at the same time, only the DDL statement.

2. How to export initialization data?
SELECT [tools]–] from the menu [export Tables ...], select the table you want to export in the open window, uncheck the option [Delete Records] option, and then export the script.

3. Display all query data

Tools-->preference-->window types-->sql window-->records Per Page

4. View Oracle Version

Select Banner from Sys.v_$version

5. Automatic Login

PL/SQL developer-> tools, preferences->oracle-> login History fixed user fill in the information, for example:

Huilan231/[email PROTECTED]/ORCL

6. Beautify SQL

Select the SQL statement you want to format, then click the PL/SQL Beautifier button on the toolbar to

7. View Execution Plan

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.

8. Shortcut key Setting

Open the Tools->preferences->user interface->key Configuration and set the shortcut keys as follows:

File/new/sql Window Shift+s

File/new/command Windows Shift+a

File/close F4

Edit/excelpaste shift+e

Edit/selection/uppercase Shift+u

Edit/selection/lowercase shift+l

Edit/selection/comment Shift+c

Edit/selection/uncomment Shift+v

Tools/window List Shift+w

9. 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-->tools->preferences-->window types-->sql Windows, tick "AutoSelect Statement". (Auto SELECT statement)

10. Import Excel Data

Tools-->odbc importer-->

function settings for PL/sql:
Connection (Excel Files)-->connect (select an xls)-->data from ODBC, Tools-Perferences, UserInterface Select an import table--> select the above

Data to Oracle (select owner (login database) and table)
Key Configuration
File/new/sql Window CTRL + N
File/pl/sqlbeautiful Ctrl+d
File/open/sql Script Ctrl+w

Code Assistant
Mininute Charts:1 enter 1 characters and a prompt will appear.

Tools, Window list is the list of SQL that is opened by the current PL/SQL tool. The following settings do not have to tick the Window List every time.

Windows List window is not displayed by default, which is very inconvenient

1. Open the PL/SQL tool:

Option, User Interface, Tools-Preference

Tick Autosave desktop, click Apply

2. Click Tools, tick Window List

3. Close the PL/SQL tool and reopen the PL/SQL tool. You will see the window List automatically open.

4.window List window closed: Tools-Preference

Key Configuration, File close (only the current windowlist window is closed)

File Close all (closes all windowlist windows)

F8 is the execution query

Default automatically check my Objects after login
By default, after Plsql 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 measured 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 you often order (such as: Tables views Functions procedures Packages Job) Move up a bit, and add color

TNS View: Installation disk: \oracle\product\10.2.0\client_1\network\admin\tnsnames.ora

Oracle Export Import table view and data, etc.:
1.Tools, Export userobjects, select the tables and views you want to export. is a. sql file.
Dutput File (select path), Export Tables, 2.Tools. is a. dmp file
3 when importing data. Execute the SQL file first.
Copy the. sql file to here, Command Window, file-New.
This operation is to make the table structure, view, etc. are first built. Next, import the data.
Tools, Import Tables (select path). dmp file

PL/SQL Oracle