Functions
Using Single-Row Functions to Customize Output
■ Character functions
LOWER, UPPER, INITCAP
CONCAT, | |, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM ( 'x' FROM 'xxx'), REPLACE
■ Number Functions
ROUND, TRUNC, MOD
■ Working with Dates
RR Date Format & YY Date Format
MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC
Using Conversion Functions and conditional Expressions
■ Convertion Functions
To_char (1, 'format_model', 'nls_parameters'), To_date (1,2,3), To_number (1,2,3)
SQL> desc nls_session_parameters
Whether the name is empty? Type
----------------------------------------- -------- -- ------------
PARAMETER VARCHAR2 (60)
VALUE VARCHAR2 (80)
■ General functions
NVL (1,2), NVL2 (1,2,3), NULLIF (1,2), COALESCE (1,2,3 ,...)
■ Conditional expressions
CASE, DECODE (col, ex1, res1, .., default)
case expr when expr1 then return_expr1
when expr2 then return_expr2
when expr3 then return_expr3
..
else return_expr
end;
■ Impicit Data Type Conversion
From: varchar2 or char To number or date
From: number or date To varchar2 or char
Reporting Aggregated Data Using the Group Function
■ Types of Group Functions
AVG, COUNT, MAX, MIN, SUM, STDDEV (standard deviation), VARIANCE
■ Syntax
Group functions ignore null values in the column
Group function does not consider the null value. . .
The NVL function force group functions to include null values
select GroupFunction (NVL (XX, XX)) from XX
■ Count () returns the number of rows with non-null values
count (*) = cont (1)
count (distinct expr)
■ Group by
All columns in the SELECT list that are not in group functions must be in the GROUP BY clause;
■ HAVING
to restict groups
● Execute follows:
1, Rows are grouped;
2, The group function is applied;
3, Groups matching the HAVING clause are displayed;
Related Posts of Functions
-
3_SQL 11g (Advanced Queries_4)
■ The Analytic Functions (2) ■ Inverse Percentile Functions PERCENTILE_DISC (x) examines the cumulative distribution values in each group until it finds one that is greater than or equal to x; in a manner the reverse of CUME_DIST () PERCENTILE_CONT ( ...
-
2_SQL 11g (functions, Dates and Times)
Chapter 4: Using Simple Functions ... ■ Regular Expression Functions Regular expression functions were introduced in Oracle Database 10g, and additional items have been added to 11g. ・ REGEXP_LIKE (x, pattern [, match_option]) To search x for the reg ...
-
1_SQL 11g
Document: modified_store_schema.zip Size: 6KB Download: Download Chapter 2: Retrieving Information from Database Tables ■ Pseudo column: ・ROWID which is used internally by the Oracle database to store the physical location of the row. A rowid is an 1 ...
-
Collection date function oracle
Reprint: A common data format date YYY or YY or 1.Y last year, two or three SQL> Select to_char (sysdate, 'Y') from dual; TO_CHAR (SYSDATE, 'Y') -------------------- 7 SQL> Select to_char (sysdate, 'YY') from dual; TO_CH ...
-
Encyclopedia of common command oracle sqlplus
show and the set command is used for the maintenance of the two SQL * Plus system variables in order SQL> show all - View all 68 values of system variables SQL> show user - shows the current connection users SQL> show error - error SQL> s ...
-
oracle set order to introduce
1). Set the current session whether to modify the data automatically submitted SQL> SET AUTO [COMMIT] (ON | OFF | IMM [EDIATE] | n) 2). Start command with the implementation of a sql script, whether the script is running the SQL statement SQL> ...
-
Sql statement to create control file
DECLARE CURSOR C1 IS SELECT FILE_NAME FROM DBA_DATA_FILES; CURSOR C2 IS SELECT A. GROUP # GRP, A. MEMBER MEM, B. BYTES / 1024 / 1024 BYT FROM V $ LOGFILE A, V $ LOG B WHERE A. GROUP # = B. GROUP # ORDER BY A. GROUP #; CURSOR C3 IS SELECT TABLESPACE_N ...
-
Oracle RMAN backup of the physical meaning of RMAN configuration
Rman details below to talk about the meaning of the configuration: rn 1, Configuring the Default Device Type for Backups: configure the default backup device rn Rman default default disk backup device for disk, also can be configured as other media s ...
-
char, varchar2 difference
Distinction: rn 1. CHAR is fixed length, and the length of VARCHAR2 is changing, for example, store the string "abc", for CHARrn (20), that you will store the 20-byte characters (including null character 17), and the same VARCHAR2rn (20) oc ...
Leave a Reply