Common oracle knowledge accumulation and oracle knowledge accumulation

Common oracle knowledge accumulation and oracle knowledge accumulation
1. Basic operation 1. Table operation 1.1 copy and create a table

Create table test as select * from dept; -- copy data and structure from a known table

Create table test as select * from dept where 1 = 2; -- copy the structure from a known table but not including data

1.2 copy and insert

Insert into test select * from dept;

1.3 create a temporary table

A temporary table dynamically allocates space only when data is inserted into a table during a SESSION or during a TRANSACTION. The SESSION-level temporary table data exists throughout the SESSION until the SESSION is completed. The TRANSACTION-level temporary table data disappears after the TRANACTION ends, that is, the temporary TRANACTION table data is cleared by COMMIT/ROLLBACK or end SESSION. Syntax:

Create global temporary table name (table definition)

On commit preserve | delete rows;

Note:

(1) When preserve is used, it is a SESSION-level temporary table, and delete is a TRANSACTION-level temporary table;

(2) The SESSION-level temporary table is used by this SESSION. When deleting the table, you need to end this SESSION.

Example:

Create global temporary table temp_dept

(Dno number,

Dname varchar2 (10 ))

On commit delete rows;

Insert into temp_dept values (10, 'abc ');

Commit;

Select * from temp_dept;-- No data display. Data is automatically cleared at the end of the transaction and should be used before the transaction.

Drop table temp_dept;

2. Sequential operations

Oracle provides a sequence object, which is a sequence number provided by the system. It is usually used to generate the auto-increment primary key or serial number of database data records. It is generally used in combination with triggers.

Sequence is a feature of the database system. Some databases have Sequence and some do not. For example, Sequence exists in Oracle, DB2, and PostgreSQL databases, and Sequence exists in MySQL, SQL Server, Sybase, and other databases.

Sequence is a table in which the data is stored in a specific ascending Sequence. This table is controlled by the database system, at any time, the database system can obtain the next record in the table based on the current number of records plus the step size. This table has no practical significance and is often used for primary keys.

2.1 create

Requirements:

First, you must have the create sequence or create any sequence permission.

Syntax:

Create sequence sequence // CREATE a SEQUENCE name

[Increment by n] // the incremental sequence value is n. If n is a positive number, it increases. If n is a negative number, it decreases. The default value is 1.

[Start with n] // The START value. The default value of increment is minvalue and the value of increment is maxvalue.

[{MAXVALUE n | NOMAXVALUE}] // maximum value

[{MINVALUE n | NOMINVALUE}] // minimum value

[{CYCLE | NOCYCLE}] // cyclic/non-cyclic

[{CACHE n | NOCACHE}]; // The allocated data is stored in the memory to improve access efficiency.

Example:

Create sequence LOCATIONS_SEQ

Increment by 1

Start with 1

Max value 9900

MINVALUE 1

CACHE 2;

 

SELECT LOCATIONS_SEQ.nextval from dual;--Take the next Sequence Value

SELECT LOCATIONS_SEQ.currval from dual;--Obtains the current sequence value.

2.2 modify

Syntax:

Alter SEQUENCESequence//Sequence name

[INCREMENTN] //The incremental sequence value is n. If n is a positive number, it increments. If it is a negative number, the default value is 1.

[STARTN] //The start value. The default value is minvalue, Which is maxvalue.

[{MAXVALUEN| NOMAXVALUE}] //Maximum Value

[{MINVALUEN| NOMINVALUE}] //Minimum value

[{CYCLE | NOCYCLE}] //Loop/non-Loop

[{CACHEN| NOCACHE}]; //Allocated and stored in memory

Note:

(1) it must be the sequence owner or have the ALTER permission on the sequence.

(2) Only future sequence values will be changed

(3) Changing the initial values of a sequence can only be achieved by deleting the sequence and recreating the sequence.

2.3 Delete

Use the drop sequence statement to delete a SEQUENCE. After deletion, the SEQUENCE cannot be referenced again.

Ii. Operators

Arithmetic Operator: +-*/can be used in select statements

Join OPERATOR: | select deptno | dname from dept;

Comparison OPERATOR: >>=! = <= Like between is null in

Logical operators: not and or

Set operators: intersect, union, union all, minus

Select * from emp intersect select * from emp where deptno = 10; -- Take the intersection

Select * from emp minus select * from emp where deptno = 10; -- get difference set

Select * from emp where deptno = 10 union select * from emp where deptno in (); -- duplicate rows not included

Select * from emp where deptno = 10 union all select * from emp where deptno in (); -- including duplicate rows

[Note]Notes for using set operators:

1. The number of columns corresponding to the set is the same as the data type

2. the query cannot contain long columns.

3. The label of the column is the label of the first set.

4. When order by is used, the position sequence number must be used. column names,

Iii. Common functions 1. Date functions 1.1 add_months (d, n)

Returns the date after (Before) the specified month after the specified date plus (minus:

Select sysdate S1, add_months (sysdate,-1) S2, add_months (sysdate, 1) S3 from dual; -- query the current time and the time of the previous month

1.2 last_day (d)

Returns the date of the last day of the specified date month:

Select last_day (sysdate) from dual;

1.3 months_between (d1, d2)

Returns the month difference between dates:

Select months_between ('13-February-18', '15-October-17') S3 from dual;

1.4 next_day (d, day)

Returns the date of the next week (day is 1-7 or Sunday-Saturday, 1 indicates Sunday ):

Select sysdate S1, next_day (sysdate, 1) S2, next_day (sysdate, 'sunday') S3 FROM DUAL

1.5 round (d, [fmt])

Rounding to the nearest date (excluding time), here fmt is an optional parameter, similar to precision, accurate to day by default, round to the nearest Sunday when the parameter is set to day:

Select sysdate S1,

Round (sysdate) S2,

Round (sysdate, 'Year') year,

Round (sysdate, 'month') month,

Round (sysdate, 'day') day,

Round (sysdate, 'hh') YEAR,

From dual;

1.6 greatest (expr1,... expr_n)

Get the maximum value, number by size, and character by first character comparison (if equal, then downward comparison), date returns the latest date:

Select greatest ('01-January-04 ', '04-January-04', '10-February-04 ') from dual;

1.7 extract (expr)

The extract () function in oracle is introduced from oracle 9i to extract specific parts from a date or interval type.

Syntax:

EXTRACT (

{YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}

| {TIMEZONE_HOUR | TIMEZONE_MINUTE}

| {TIMEZONE_REGION | TIMEZONE_ABBR}

FROM {date_value | interval_value })

We can only extract year, month, and day from a date type (the date format is yyyy-mm-dd ); you can also extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with time zone to get the specific time interval between two dates. The extract function is the best choice.

Example:

Select systimestamp s,

Extract (year from Hangzhou imestamp) year,

Extract (month from Hangzhou imestamp) month,

Extract (day from week imestamp) day,

Extract (minute from policimestamp) minute,

Extract (second from policimestamp) second,

Extract (timezone_hour from policimestamp) th, -- Time Zone: 8

Extract (timezone_minute from policimestamp) tm,

Extract (timezone_region from systimestamp) tr,

Extract (timezone_abbr from paiimestamp) ta

From dual;

 

[Note] We will study TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, and TIMEZONE_ABBR.

 

2. Number function 2.1 integer function

Ceil rounded up and floor rounded down:

Select ceil (66.6) N1, floor (66.6) N2 from dual; -- the results are 67 and 66

2.2 power and square root (sqrt)

Select power (3, 2) N1, sqrt (9) N2 from dual;

2.3 calculate the remainder mod (n1, n2)

Select mod (9,5) from dual;

2.4 returns a fixed number of decimal places

Round (num, len) Rounding, trunc (num, len) directly truncation:

Select round (66.667, 2) N1, trunc (66.667, 2) N2 from dual;

2.5 sign of the return value

Sign (n), positive returns 1, negative returns-1:

Select sign (-32), sign (293) from dual;

3. The character function 3.1 initcap (st)

Returns the first letter of each word in the st case, and all other letters in the lower case.

3.2 lower (st)

Returns st to lowercase letters of each word.

3.3 upper (st)

Returns the value st, in which all the letters of each word are capitalized.

3.4 concat (st1, st2)

Returns the end of st2 connected to st1 (available operator "| ")

3.5 lpad (st1, n [, st2])

Returns the right-aligned st. st is filled with st2 on the left of st1 until the length is n. The default value of st2 is space.

3.6 rpad (st1, n [, st2])

Returns the left-aligned st. st is filled with st2 on the Right of st1 until the length is n. The default value of st2 is space.

3.7 ltrim (st [, set])

The return value is st, And st is to delete the characters in the set from the left until the first character is not in the set. Lack of time, refers to Space

3.8 rtrim (st [, set])

The return value is st, And st is to delete the characters in the set from the right until the first character is not in the set. Lack of time, refers to Space

3.9 replace (st, search_st [, replace_st])

Replace the search_st that appears in the st with replace_st, and return an st. Delete search_st

3.10 substr (st, m [, n])

N = returns the substring of the st string, starting from the m position and taking n characters in length. Lack of time, always return to the end of st

3.11 length (st)

Numeric value, returns the number of characters in the st.

3.12 instr (st1, st2 [, m [, n])

Returns the position where st1 appears at the nth occurrence of st2. the default values of m and n are 1.

4. Conversion Function 4.1 to_char (type, [fmt])

Here, the type is date, timestamp, int, float, and numeric. The data types are different, and the fmt Format is also different.

4.1.1 date to character conversion

Select to_char (sysdate) s1, -- day-month-year, the format is related to the language environment

To_char (sysdate, 'yyyy-mm-dd') s2,

To_char (sysdate, 'yyyy') s3,

To_char (sysdate, 'yyyy-mm-dd hh12: mi: ss') s4,

To_char (sysdate, 'hh24: mi: ss') s5,

To_char (sysdate, 'day') s6

From dual;

4.1.2 time stamp to character conversion

Select sysdate,

Systimestamp,

To_char (systimestamp, 'yyyymmdd hh24: mi: ssxff6 '),

To_char (systimestamp, 'yyyymmdd hh24: mi: ss. ff6 '),

To_char (timestamp '2017-09-14 12:52:42. 123', 'yyyy-MM-DD ')

From dual;

4.1.3 numeric to character conversion

Select to_char (88877) s1,

To_char (1234567890, '123') s2, -- 099999999999999

To_char (12345678, '123') s3,

To_char (123456, '99. 999 ') s4, -- overflow, displayed #####

To_char (1234567890, '000000') s5,

To_char (123, 'xx') s6, -- hexadecimal, 7b

To_char (4567, 'xxxx') s7, -- 11d7

To_char (12345, '$99999') s8, -- $12345

To_char (12345.6, '$99,999.00') s9 -- $12345.60

From dual;

[Note] timestamp refers to the timestamp, precise to fractional seconds (fractional_seconds_precision), which can be 0 to 9. The default value is 6:

Select systimestamp, systimestamp (9) from dual;

4.2 to_date (c, [fmt]) 4.3 to_timestamp (c, [fmt]) 4.4 to_number (c, [fmt]) 5. Grouping Function

There are two groups in Oracle: group by and partition:

Group by emphasizes a whole, that is, a group. Only one record meeting the aggregate function can be displayed in a group. partition by emphasizes the individual after the whole and displays the records of all individuals in the group.

The corresponding grouping functions can also be divided into two categories:

5.1 aggregation function 5.1.1 statistical method 5.1.1.1 rollup

Perform statistics and the final subtotal based on the first column of the group:

Select t. apply_id, t. disposal_field, count (1)

From ebill_dump_apply_detail t

Group by rollup (t. apply_id, t. disposal_field );

 

5.1.1.2 cube

Statistics by all the columns in the group and the final Subtotal:

Select t. apply_id, t. disposal_field, count (1)

From ebill_dump_apply_detail t

Group by rollup (t. apply_id, t. disposal_field );

 

5.2 analysis function 5.2.1 what is an analysis function

The analysis function is a powerful function used by Oracle to solve complex report statistics requirements. It can group data and then calculate a group-based statistical value, in addition, each row in each group can return a statistical value.

5.2.2 form of analysis function

The syntax structure of the analysis function is generally:Analysis Function Name (parameter) OVER (partition by clause order by clause ROWS/RANGE clause).

It consists of the following three parts:

Analysis Function Name: Aggregate functions such as sum, max, min, count, and avg, and comparison functions of lead and lag rows;

Over: Keyword, indicating that the previous function is an analysis function, not a common set function;

Analysis clause: Registration content after the over keyword;

The analysis clause consists of the following three parts:

Partition: Grouping clause, indicating the computing range of the analysis function. Different Groups are irrelevant;

ORDER: Sorting clause, indicating the group sorting method After grouping;

ROWS/RANGE: Window clause, which is a subgrouping (also called a window) in A group after a group (partition by). In this case, the computing range window of the function is analyzed, instead of PARTITON. There are two types of Windows: ROWS and RANGE; 

Example:

WITH t

(SELECT (CASE

When level in (1, 2) THEN

1

When level in (4, 5) THEN

6

ELSE

LEVEL

END) ID

FROM dual

Connect by level <10)

 

SELECT id,

SUM (ID) over (order by id) default_sum,

SUM (ID) over (order by id range between unbounded preceding and current row) range_unbound_sum,

SUM (ID) over (order by id rows between unbounded preceding and current row) rows_unbound_sum,

SUM (ID) over (order by id range between 1 preceding AND 2 following) range_sum,

SUM (ID) over (order by id rows between 1 preceding AND 2 following) rows_sum

FROM t;

 

The preceding example shows that:
(1) The Window clause must be used together with the order by clause. If the order by clause is specified but the window clause is not specified, the default value is range between unboundedpreceding and current row, in the preceding example, defult_sum is equal to range_unbound_sum;

(2) If the analysis function does not specify the order by clause, the calculation of the ROWS/RANGE window does not exist;

(3) range is a logical window that specifies the range value of the corresponding value of the current row. The number of columns is not fixed. As long as the row value is within the range, the corresponding column is included, analysis results of range_sum (range 1 preceing and 2 following) in the preceding example:

When id = 1, sum is the sum of 1-1 <= id <= 1 + 2, that is, sum = 1 + 1 + 3 = 5 (take id as 1, 1, 3 );

When id = 3, sum is the sum of 3-1 <= id <= 3 + 2, that is, sum = 3 (take id as 3 );

When id = 6, the sum is 6-1 <= id <= 6 + 2, that is, sum = 6 + 6 + 6 + 7 + 8 = 33 (take id 6, 6, 6, 7, 8 );

The result is shown in the preceding example.

4. rows is a physical window. It calculates the data of the first N rows and the last N rows after sorting by the orderby clause (it is irrelevant to the value of the current row, in the preceding example, the result of rows_sum is the sum of the first row and the last two rows. The result of rows_sum is analyzed as follows:

When id = 1 (first 1), the first row is not counted, the second row is 1 and 3, sum = 1 + 1 + 3 = 5;

When id = 3, the first row id = 1, and the second row id is 6, then sum = 1 + 3 + 6 + 6 = 16;

The result is shown in the preceding example.

Note: The row comparison analysis functions lead and lag do not have window clauses.

 

Reference:

Http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html;

Https://www.cnblogs.com/cjm123/p/8033892.html;

Http://blog.itpub.net/21251711/viewspace-1068855 /;

6. Other functions 6.1 Branch functions

This is my classification. All Branch judgment functions are listed here.

6.1.1 DECODE Function

The DECODE function is a unique function computing method provided by ORACLE's SQL software oracle pl/SQL.

DECODE Syntax: DECODE (value, if1, then1, if2, then2, if3, then3 ,..., else), indicating that if the value is equal to if1, the result of the DECODE function returns then1 ,..., if it is not equal to any if value, else is returned.

Note that if, then, and else can both be functions or computing expressions.

6.1.2 NVL Function

NVL is a function in Oracle PL/SQL. The format is NVL (string1, replace_with ). If string1 is NULL, The NVL function returns the value of replace_with. Otherwise, the value of string1 is returned. If both parameters are NULL, NULL is returned.

Note: string1 and replace_with must be of the same data type, unless explicitly converted using the TO_CHAR function. It is equivalent to the isnull method in SQL.

6.1.3 NVL2 Functions

Oracle extends the NVL function and provides the NVL2 function.

NVL2 (E1, E2, E3) function: If E1 is NULL, the function returns E3; otherwise, the function returns E2. Equivalent to the ternary operator in. Net.

6.1.4 NULLIF Function

NULLIF (ex1, ex2). If the value is equal, return NULL. Otherwise, return the first value.

6.2 TRUNC Function

Return the number or date after the truncation according to the specified requirements.

6.2.1 TRUNC (for number)

The TRUNC function returns the processed value. Its working mechanism is very similar to that of the ROUND function, except that the function does not ROUND or select the part before or after the specified decimal number.

The syntax format is as follows:

TRUNC (number [, decimals])

Where:

Number the value to be intercepted

Decimals indicates the number of digits after the decimal point to be retained. Optional. If this parameter is ignored, all decimal parts are truncated.

The usage of this function is as follows:

TRUNC (89.985, 2) = 89.98

TRUNC (89.985) = 89

TRUNC (89.985,-1) = 80

Note: The second parameter can be a negative number, indicating that the part after the specified number of digits on the left of the decimal point is truncated. It is similar to integer. For example, if the parameter is 1, the value is rounded to the nearest bit. If the parameter is-1, the value is rounded to ten digits, and so on.

6.2.2 TRUNC (for dates)

The date value intercepted by the TRUNC function for the specified element.

The syntax format is as follows:

TRUNC (date [, fmt])

Where:

Date: A date value.

Fmt date format, which is truncated by the specified Element format. Ignore it and it is intercepted by the latest date.

The usage of this function is as follows:

TRUNC (TO_DATE (24-Nov-199908: 00 pm), dd-mon-yyyyhh: mi am)

= 24-Nov-1999 12:00:00 am

TRUNC (TO_DATE (24-Nov-199908: 37 pm, dd-mon-yyyyhh: mi am), hh) = 24-nov-1999 08:00:00 am

Trunc (sysdate, yyyy) -- returns the first day of the current year.

Trunc (sysdate, mm) -- returns the first day of the current month.

Trunc (sysdate, d) -- returns the first day of the current week.

Trunc (sysdate, dd) -- returns the current year, month, and day

Fmt value reference:

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

 

4. Reserved Words 4.1 users

Select user from dual; -- return the Login user Name

4.2 sysdate

Select sysdate fromdual; -- returns the current system time

Http://streamsong.iteye.com/blog/1023922