Shulou(Shulou.com)06/01 Report--

Use SQL*Plus

1. View the table structure

SQL > desc regions_copy

Name Null? Type

-

REGION_ID NOT NULLNUMBER

REGION_NAME VARCHAR2 (25)

2. Edit the SQL statement

SQL > list

1 CREATE OR REPLACE PROCEDURE update_product_price (

2 p_product_id IN products.product_id%TYPE

3 p_factor IN NUMBER

4) AS

5 product_count INTEGER

6 BEGIN

7-- count the number of products with the

8-supplied product_id (should be 1 if the product exists)

9 SELECT COUNT (*)

10 INTOproduct_count

11 FROMproducts

12 WHERE product_id = p_product_id

thirteen

14-ifthe product exists (product_count = 1) then

15-update that product's price

16 IFproduct_count = 1 THEN

17 UPDATE products

18 SETprice = price * p_factor

19 WHERE product_id = p_product_id

20 COMMIT

21 ENDIF

22 EXCEPTION

23 WHEN OTHERS THEN

24 * ROLLBACK

25 END update_product_price

SQL > l

1 * select dbms_metadata.get_ddl ('TABLE','GEGIONS_COPY','HR') from dual

SQL > c/GEGIONS_COPY/REGIONS_COPY

1 * select dbms_metadata.get_ddl ('TABLE','REGIONS_COPY','HR') from dual

SQL > LIST

1 * select dbms_metadata.get_ddl ('TABLE','REGIONS_COPY','HR') from dual

(1) commonly used editing commands

A [PPEND] text appends text to the current line

C [HANGE] / old/new replaces the old of the current line with new

CL [EAR] BUFF [ER] clears all lines in the buffer

DEL deletes the current line

DEL x delete line x

L [IST] lists all lines in the buffer

L [IST] x lists line x

R [UN] runs statements saved in the buffer

/ ditto

X take line x as the current line

SQL > l

1 SELECT EMAIL

2 * FROM EMPLOYEES

SQL > l 1

1 * SELECT EMAIL

SQL > APPEND, SALARY

1 * SELECT EMAIL, SALARY

SQL > L

1 SELECT EMAIL, SALARY

2 * FROM EMPLOYEES

SQL > RUN

1 SELECT EMAIL, SALARY

2 * FROM EMPLOYEES

3. Save, retrieve and run files

SQL > select dbms_metadata.get_ddl ('TABLE','REGIONS_COPY','HR') from dual

SQL > save / s01/regions_copy_get_ddl.sql

SQL > get/s01/regions_copy_get_ddl.sql

1 * selectdbms_metadata.get_ddl ('TABLE','REGIONS_COPY','HR') from dual

SAVE filename [{REPLACE | APPEND}] saves the contents of the sqlplus buffer to a file specified by filename

GET filename reads the contents of the file specified by filename into the sqlplus buffer

START filename | @ filename runs the contents of the file. Read into the buffer first.

EDIT opens an editor, edits the contents of the buffer, and writes back to the buffer

SPOOL filename

SPOOL OFF writes the output to the file

SQL > EDIT

The default is the vi editor in LINUX and the Notepad editor in Windows.

Modify the default editor

DEFINE _ EDITOR = 'editor'

DEFINE _ EDITOR = 'vi'

4. Format the column

COLUMN {column | alias} [option]

Specify the column name to format

Specifies the alias of the column to format

Option

Option option

FOR [MAT] format specifies the display format of the column, determined by format

HEA [DING] heading column header

Alignment of the output of the JUS [TIFY] column

[{left | center | right}]

WRA [PPEND] wraps the end of a string

WORD_WRAPPED is the same as above to ensure that a single word cannot cross lines.

CLE [AR] any setting of the clear column is the default

For format

1. For strings, you can use Ax to specify the width of the characters

2. For numbers, you can specify the display format

3. For dates, specify the display format, such as MM-DD-YYYY

5. Set the page size

SETPAGESIZE 100

The maximum page size is 50000

6. Set the number of rows

SETLINESIZE 100

7. Clear column format

SQL > COLUMN product_id CLEAR-- clears the specified column

SQL > CLERA COLUMN-- reset column format and restore default format

8. Use variables

Use variables

After the variable is created, it can be used instead of the actual value in the SQL statement.

Two basic variable types can be used in SQLPLUS

1. A temporary variable is valid only in SQL statements that use it, and the value cannot be retained.

2. The value of the defined variable is retained until it is explicitly deleted

(1) temporary variable

& use & to define a temporary variable, followed by the variable name.

Temporary variable & variable_name

-- controls the output line

SQL > SET VERIFY OFF-- disables the display of information for original and new lines

SQL > SETVERIFY ON

-- modify variable definition characters

SQL > SET DEFINE'#'

SQL > SET DEFINE'&'

-- & & prevent repeated input

-- define variables

In the SQL statement, you can define variables before using them. You can also use this variable multiple times in the same SQL statement.

Defined variables remain until they are explicitly deleted, redefined, or exited from SQLPLUS.

Use the DEFINE command to define and view variables

Use the ACCEPT command to define and set variables

The ACCEPT command waits for the user to enter a value for the variable.

The ACCEPT command can either set an existing variable to a new value or define a new variable.

ACCEPT variable_name [type] [FORMAT format] [PROMPT prompt] [HIDE]

Type: variable type. You can specify CHAR, NUMBER, DATE

Default is CHAR

Format: specify the format of the variable

Prompt: prompt text to prompt the user for variables

HIDE: hides the values entered for variables, such as password information, with the hidden symbol *

9i was previously shown as *, but not in 10g.

-- Delete a defined variable.

SQL > undefine definename

SQL > UNDEFINE v_customer_id

SQL > UNDEFINE v_date

SQL > UNDEFINE v_password

SQL > UNDEFINE v_product_id

9. Create a simple report

You can use temporary or defined variables in sqlplus, so you can create scripts that prompt the user for variables, and then use them to generate reports.

If you need to generate complex reports, you need to use software such as oracle reports.

-- use accept in scripts

ACCEPT variable_name [type] [FORMAT format] [PROMAT prompt] [HIDE]

[eg]

SQL > ACCEPT v_customer_id NUMBER FORMAT 99 PROMPT 'Customer id'

-- add headers and footers

The TTITLE and BTITLE commands can be used to report Tianjian headers and footers.

[eg]

TTITLE LEFT 'Run date:' _ DATE CENTER' Run by the 'SQL.USER' user'

RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP 2

Run date: 12-AUG-07 Run by the HR user Page: 1

-- calculation subtotal

The break on clause allows sqlplus to separate the output according to the range of column values.

The compute clause allows sqlplus to calculate the sum of a column

The two can be used together

BREAK ON product_type_id

COMPUTE SUM OF price ON product_type_id

SET ECHO OFF

SET VERIFY OFF

SET PAGESIZE 50

SET LINESIZE 70

CLEAR COLUMNS

COLUMN price HEADING Price FORMAT $999.99

SELECT product_type_id,name,price

FROM products

ORDER BY product_type_id

CLEAR COLUMNS

10. Get help from SQL*PLUS

SQL > HELP

SQL > HELP INDEX

SQL > help index

Enter Help [topic] for help.

@ COPY PAUSE SHUTDOWN

@ @ DEFINE PRINT SPOOL

/ DEL PROMPT SQLPLUS

ACCEPT DESCRIBE QUIT START

APPEND DISCONNECT RECOVER STARTUP

ARCHIVE LOG EDIT REMARK STORE

ATTRIBUTE EXECUTE REPFOOTER TIMING

BREAK EXIT REPHEADER TTITLE

BTITLE GET RESERVED WORDS (SQL) UNDEFINE

CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE

CLEAR HOST RUN WHENEVER OSERROR

COLUMN INPUT SAVE WHENEVER SQLERROR

COMPUTE LIST SET XQUERY

CONNECT PASSWORD SHOW

11. Automatically generate SQL statements

Now you want to generate a script to delete all the tables in the database.

SQL > select 'DROP TABLE' | | table_name | |';'

2 from user_tables

'DROPTABLE' | | TABLE_NAME | |';'

-

DROP TABLE REGIONS

DROP TABLE LOCATIONS

DROP TABLE DEPARTMENTS

DROP TABLE JOBS

DROP TABLE EMPLOYEES

DROP TABLE JOB_HISTORY

DROP TABLE REGIONS_COPY

DROP TABLE BINARY_TEST

DROP TABLE COUNTRIES

9 rows selected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.