Explain the Oracle SQLPlus runtime environment, common Oracle System Function instances, and sqlplus instances

Explain the Oracle SQLPlus runtime environment, common Oracle System Function instances, and sqlplus instances

1. Introduction to the SET command

Set command sets the running environment of SQL * Plus. The syntax FORMAT OF THE set command is:

SET system_variable value

The system_variable parameter represents the variable name, and the value parameter represents the variable value.

For example, set time on is a temporary operation and will be restored after logon is restarted.

2. Use the SET command to SET the runtime environment

The PAGESIZE variable set pagesize can change the number of rows on a page. The show pagesize variable shows the number of rows.

NEWPAGE variable

LINESIZE variable

PAUSE variable

NUMFORMAT variable

ORACLE character Functions

1 ASCII (arg1)

Returns the decimal number of the arg1 parameter. If the database is set to ASCII, the ASCII character is used. If it is set to EBCDIC, The EBCDIC character is used.

Select ascii ('A') from dual;

ASCII ('A ')

----------

65

2 CHR (arg1, [using nchar_cs])

Returns the characters specified by the arg1 code. The returned characters depend on the underlying character encoding settings used by oracle.

Select chr (65) from dual;

CHR (65)

-------

A

3 CONCAT (arg1, arg2)

Returns the splicing result of arg1 and arg2. equivalent to arg1 | arg2

Select concat ('abc', 'def ') from dual;

CONCAT ('abc', 'def ')

-------------------

ABCDEF

4 INITCAP (arg1)

Converts the arg1 parameter to an uppercase format for each word.

Select INITCAP ('Hello World') from dual;

INITCAP ('helloworld ')

---------------------

Hello World

5 INSTR (arg1, to_find, pos, occurrence)

Search for the to_find substring in arg1 and return an integer to indicate its position. the pos parameter specifies the position where the search starts in arg1. if the pos is positive, the search starts at the position specified by the number. if pos is negative, the search starts at the position specified by the number, but the position is specified from the end of the string. and reverse search. the occurrence parameter specifies the number of times a string appears in the search.

-- Start searching from the left side with 1st characters. The first time ABC occurs

Select instr ('habchabc', 'abc', 1, 1) from dual;

INSTR ('habchabc', 'abc', 1, 1)

---------------------------

2

-- Start searching from the left side with 3rd characters. The first time ABC occurs

Select instr ('habchabc', 'abc', 3, 1) from dual;

INSTR ('habchabc', 'abc', 3, 1)

---------------------------

6

---- The search starts with 1st characters on the left and returns the location of ABC for the second time.

Select instr ('habchabc', 'abc', 1, 2) from dual;

INSTR ('habchabc', 'abc', 1, 2)

---------------------------

6

-- The search starts with 1st characters on the right and returns the location of ABC for the second time.

Select instr ('habchabc', 'abc',-1, 2) from dual;

INSTR ('habchabc', 'abc',-1, 2)

----------------------------

2

6 LENGTH (arg1)

The length of the returned arg1 parameter. arg1 can be char, varchar2, nchar, nvarchar2, clob, or nclob.

Select length ('abcdefg') from dual;

LENGTH ('abcdefg ')

-----------------

7

7 lower (arg1)

Returns the lowercase format of the arg1 parameter.

Select lower ('abcdefg') from dual;

LOWER ('abcdefg ')

----------------

Abcdefg

8 LPAD (arg1, n, arg3)

Returns the string that contains several new arg3 characters on the left of arg1. If n is smaller than the length of arg1, arg1 is truncated from the left.

-- Add Ha consecutively before ABC until the total length is 10.

Select lpad ('abc', 10, 'ha') from dual;

LPAD ('abc', 10, 'ha ')

-------------------

Hahahabc

Select lpad ('abc', 2, 'ha') from dual;

LPAD ('abc', 2, 'ha ')

------------------

AB

9 LTRIM (arg1, arg2)

Remove consecutive arg2. if arg2 is not specified on the left of arg1, spaces are deleted by default.

Select ltrim ('aaaasa ', 'A') from dual;

LTRIM ('aaaasa ', 'A ')

-------------------

SA

Select ltrim ('aaa') from dual;

LTRIM ('aaa ')

------------

AAA

10 REPLACE (arg1, search, replace)

This function replaces all search parameters that appear with the replace parameter.

Select replace ('abcabce ', 'abc', 'x') from dual;

REPLACE ('abcabce ', 'abc', 'x ')

-----------------------------

Xdxe

11 SUBSTR (arg1, pos. len)

The substring of the specified len is intercepted from the position pos specified in arg1 to the right. If the pos is a regular character, the count starts from the left side of the arg1 string. If the pos is negative, the count starts from the right side of the string.

If len is not specified, the substring from position pos to end of the substring is returned.

SQL> -- truncates 5 characters from the left 3rd characters.

SQL> select substr ('123', 1234567890) from dual;

SUBSTR ('123', 1234567890)

------------------------

34567

-- Truncates 5 characters from the first character on the right.

SQL> select substr ('20140901',-1234567890) from dual;

SUBSTR ('20140901',-1234567890)

-------------------------

890

-- Start from the 3rd bits on the left and return to the end of the string.

Select substr ('20140901', 3) from dual;

SUBSTR ('123', 3)

----------------------

34567890

Number Functions

1. The CEIL (n) function returns the smallest integer greater than or equal to n.

2. ROUND (n1, n2) Function

3. POWER (n1, n2) Functions

Date and Time Functions

1. SYSDATE () function

2. ADD_MONTHS (d, I) Function

Conversion functions

1. TO_CHAR () function

2. TO_NUMBER (s [, format [lan] Function

Aggregate functions

1. AVG (X [DISTINCT | ALL]) Function

2. COUNT (x [DISTINCT | ALL]) Function

3. SUM (x [DISTINCT | ALL]) Function