Chapter Oracle query. Data Types. Function

2011-05-24  来源:本站原创  分类:Database  人气:121 

Chapter 2 of Oracle query Oracle data types, functions

Note: This chapter provides Oracle SQL help file (PDF) Download

1, the technical objectives

  • Understanding of Oracle data types
  • Understand the data definition language and data manipulation language
  • Understand the transaction control language and data control language
  • With SQL operators and SQL functions

2, SQL Introduction

  • SQL (Structured Query Language) is a structured query language
  • SQL based database language from IBM in the early 1970s to control the development of relational databases, Oracle's implementation of full compliance with ANSI SQL standard SQL language, Oracle uses SQL to store and retrieve information
  • SQL is a nonprocedural language for query, add, delete and modify data and objects is very simple commands
  • SQL is a relational database for all the common language of

Oralce Oralce user interaction with SQL, as shown:

Chapter Oracle query. Data Types. Function

SQL supports the following types of commands:

  • Data definition language (DDL)
  • Data manipulation language (DML)
  • Transaction Control Language (TCL)
  • Data Control Language (DCL)

2.1) DDL (Data Definition Language) that contains the instructions below,

  • CREATE: create a new database data objects
  • ALTER: modify the data structure of objects in the database
  • DROP: delete database objects
  • DISABLE / ENABLE TRIGGER: modify the state of the trigger
  • UPDATE STATISTIC: update table / view statistics
  • TRUNCATE TABLE: empty data in the table
  • COMMENT: add comments to the data object
  • RENAME: change the data object name

2.2) DML (Data Manipulation Language) contains the instructions below,

  • INSERT: insert data into the table or view
  • DELETE: delete data from a table or view
  • SELECT: retrieve from the table or view data, such as:

    Use 1: use of existing tables to create tables, syntax:
    CREATE TABLE table name ASSELECT new original column names FROM table;

    Use 2: Select no duplicate rows, use the DISTINCT keyword, the syntax:
    SELECT DISTINCT column name FROM table;

    Use 3: Query for the column alias, use the AS keyword syntax:
    SELECT column names FROM table AS column aliases;

  • UPDATE: update the data in the table or view
  • MERGE: merging the data operations (insert / update / delete)

2.3) TCL (Transaction Control Language)
Transaction is the smallest unit of work, work as a whole to ensure the overall success or failure of transaction,

Known as transaction control, the instructions contained in the following,

  • COMMIT: the end of the transaction submitted and
  • SAVEPOINT: mark the point you can roll back a transaction
  • ROLLBACK: revocation of a transaction has completed work
  • SET TRANSACTION: set transaction options

Use: Set the transaction and rollback point
- Perform a modify operation
UPDATE orderMaster SET delDate = '18-5 Month -08 'WHERE orderNo
<= 'O8008';
- Set the rollback point 1
DELETE FROM orderMaster WHERE orderNo = 'O8008';
- Set the rollback point 2
- Roll back transactions to rollback point 1
- Commit the transaction

2.4) DCL (Data Control Language) contains the instructions below,

  • GRANT: give the user some control rights
  • REVOKE: to cancel the user some control authority

3, Oracle data types

User to create tables, you must specify the data type for each column is the following Oracle data types categories:

  • Character type
  • Numeric Types
  • Date Time Type
  • RAW and LONG RAW types
  • LOB types

4, there are three types of characters

4.1) CHAR type

  • When fixed-length string, use the CHAR data type.
  • CHAR data type stores alphanumeric value.
  • CHAR data type column length can be 1 to 2000 bytes.
  • If the value is less than the specified length, then padded with spaces to a fixed length
  • If the value is greater than the specified length, it returns an error report

4.2) VARCHAR2 type

  • VARCHAR2 data type supports variable-length strings
  • VARCHAR2 data type stores alphanumeric value
  • The size of VARCHAR2 data type in the 1-4000 byte range

Compared with CHAR type, VARCHAR can save storage space, such as a as a VARCHAR2 type, size is 50 bytes, 20 bytes of user input characters, the end is only 20 bytes in length, and will account for 50 words CHAR section, the remaining part is padded with spaces

4.3) LONG type

  • LONG datatype to store variable-length character data
  • LONG data type can store up to 2GB

The main types of storage capacity for storing more than VARCHAR2 long text message, its length by the available disk storage space limitations, the use of LONG datatype also need to comply with the following rules:

  1. A table can have only one LONG type can be
  2. LONG type column can not use a unique constraint or primary key constraint
  3. LONG type columns can not be indexed
  4. Stored procedure or process can not accept the argument of type LONG

5, the value type NUMBER

Type NUMBER can store integer values, floating point and real numbers, the most high-precision 38-bit,
The format of numeric data types:

NUMBER [(p [, s])]
P denotes precision, S said decimal digits (-84 ~ 127)

Use 1: column name number, define the integer 38
Use 2: column name number (median), define an integer number of bits specified
Use 3: column name number (digits, decimal places), the total number of digits and decimal point defines the right end of the specified number of digits of floating point

6, the date and time types

Datetime data type stores date and time values, including year, month, day, hour, minute, second

6.1) DATE type

The DATE type is used to store the date and time data, accounting for 7-byte fixed-length, respectively, each byte stores century, year, month, day, hour, minute, second, DATE type value from the AD
4712 January 1 to AD 9999 December 31, Oracle provides a function sysdate
Returns the current date and time,
Note: The default date format for data type "DD-MON-RR", "Day - Month - Year", as
"18-5 -08 months," said the May 18, 2008

Use 1: Add record to specify the default date
INSERT INTO order_master
VALUES ('D1008', '18-5 Month -08 ',' X800 ',' abc ', '25-5 Month -08');

Use 2: Use the TO_DATE function to add records conversion date
INSERT INTO my_table (date_col)
VALUES (TO_DATE ('2005-10-18 ',' YYYY-MM-DD '));

6.2) TIMESTAMP type

TIMESTAMP type to store the date's year, month, day and time of hours, minutes and seconds values,
Values accurate to one second after the decimal point 6, while the time zone of the type information, Oracle provides a function
systimestamp returns the current date, time and time zone

7, RAW and LONG RAW type

RAW and LONG RAW data type for storing binary

7.1) RAW type

RAW data type for storing binary data, this type can store up to 2000 bytes.
The type is not the default size, use size to be specified, RAW types can be indexed

7.2) LONG RAW type

LONG RAW type used to store variable-length binary data, store up to 2GB, this type of LONG data types need to comply with the rules of the same

8, LOB type

LOB also known as "large object" data type can store 4GB of unstructured information, such as voice, video, allowing the data efficient, random, segment access. LOB according to the position relative to the database, you can make an external or internal, operations can use LOB data types as follows:

  • SQL Data Manipulation Language
  • Using PL / SQL supplied package DBMS_LOB

A table can have as a LOB type, the more LOB columns can be different LOB types, Oracle
Provides three LOB types are:

  • CLOB
  • BLOB

8.1) CLOB (Character LOB, character LOB) types <br /> this type can store large amounts of character data, character data can be single-byte characters or multibyte characters,
XML documents can also be used to store data

8.2) BLOC (Binary LOB, binary LOB)
Type can store large binary data (images, audio and video, etc.)

8.3) BFILE (Binary File, binary file)
This type can store binary files in the operating system files outside the database, the table in the BFILE
Column only stores a BFILE locator, the locator points to the file system binary files, can store files up to 4GB

9, Oracle pseudo-columns

Pseudo-column table is like a column that is not stored in the table, the pseudo-column from the table in the query but can not insert, update, delete its value, there are two pseudo-column, ROWID pseudo-column ROWNUM pseudo-column and

9.1) ROWID pseudo-column:
Each table has a row address line, ROWID pseudo column returns the address of the line,
Its value can uniquely identify the rows, using the pseudo-column table can be defined as a line,
To see the ROWID value: select rowid from table name;

Pseudo-column ROWID pseudo column there are the following effects:
1 to the fastest way to access the table rows
2 shows how the table is stored in row
3 As a unique identifier for rows in the table

9.2) ROWNUM pseudo column
ROWNUM pseudo column returns a value representative of the row number, the first line of ROWNUM of 1, the second act 2
And so on, using the pseudo-column limit the number of rows returned by the query, such as the use ROWNUM to extract 10
Records: select * from table where rownum <11;

10, SQL Operators

Oracle Classification map of operators:

Chapter Oracle query. Data Types. Function

10.1) arithmetic operators, including :(+), minus (-), multiplication (*), division (/)
You can use arithmetic expressions in SQL statements, arithmetic expressions by the numeric data type column names, numeric constants and arithmetic operators to connect their components

10.2) relational operators include:
=,!=,<,>,<=,>=, BETWEEN ... AND, IN, LIKE, and IS NULL, etc.
Relational operators compare two expressions for the value of

10.3), logical operators, including: (AND), or (OR) and non-(NOT)
Logical operators used to combine multiple operations regardless of the result to generate a true or false result

10.4), set operators, including:
Joint (UNION), jointly owned (UNION ALL), intersection (INTERSECT), reduced set (MINUS)

  • UNION operator to return two query selected all rows do not repeat
  • UNION ALL operator in line and two query selected all rows, including duplicates
  • INTERSECT operator returns only two public inquiries line
  • MINUS operator returns the first results from the second query to exclude rows that appear in the

Set operator to combine the results of two queries a result, the use of set operators are required to abide by the rules:

  • Collection of operators connected by the query have the same number of columns and the corresponding columns must be the same type of
  • Query should not contain columns of type LONG
  • Column headings from the first select statement

Use: check your order form order number, order details in the existing exclusion order number:
SELECT orderNo FROM orderMaster
MINUS SELECT orderno FROM orderDetail;

10.5) concatenation operator: | |
For two or more strings into one string, or a string with a combined value by using concatenation operator, the table can be combined into a logical multiple columns on a column, such as :
SELECT (venName | | 'address is' | | venadd1 | | '' | | venadd2 | | ''
| | Venadd3) address FROM vendorMaster WHERE vencode = 'V001';

11, operator precedence

Priority from the top down:

  • Arithmetic operators --------> highest priority
  • Concatenation operator
  • Comparison Operators
  • NOT logical operator
  • AND logical operator
  • OR logical operator --------> lowest priority

12, Oracle SQL functions

Oracle provides for a special function to perform specific operations, Oracle function can be divided into one-way functions, group functions (aggregate functions), analytic functions

12.1) one-way function

One-way functions, also known as "scalar function", the query from the table for each row, the function returns a value,
Single function in select, where clause, one-way function can be divided into:
1 Date Functions:
add_months (date, months)
The function to specify the date plus the specified number of months
months_between (Date 1, Date 2)
This function returns the number of months between two dates,
If the date is later than the date 1 period 2 is positive, or negative,
If the two dates for the same month, the following conditions:
1 two months of the date the same day or the last day, the result is always an integer
2 unusual day, will be based on January 31st day basis and to consider a fractional time difference
last_day (date)
This function returns the specified date value of the date of the last day of the month
round (date, [format])
This function will be rounded up to the date specified in the format of the unit,
Format is an optional value, rounded to the nearest date of default of that day,
If you specify the format of "year" year, then rounded up to the beginning of the Year (January 1),
If the format is "month" month, then rounding to the first day of the month,
If the format is "day" week, then rounded to the nearest Sunday
next_day (date, day of week)
This function returns the date of the next week,
Week means any day of week, for example:
select next_day (sysdate, 'Wednesday') from dual;
Shows the current date on the first Wednesday of the date, the parameter 'Wednesday'
4 can be used to represent integer

Note: dual keyword is not used in the target table query

2 character function:
Chapter Oracle query. Data Types. Function

3 digital functions:

Chapter Oracle query. Data Types. Function

4 conversion functions: convert a value from one type to another type

to_char (date | number [, format])
The function of the specified date or number format to varchar2 type, such as:
select to_char (sysdate, 'YYYY' 'years' 'fmMM' 'month' 'fmDD' 'Day'
'HH24: MI: SS') from dual;
Results: xxxx in xx month xx day xx: xx: xx
x is the value of the current date

to_date (string [, format])
The function of type char or varchar string date into a date format specified type,
Such as: select to_date ('2008-08-18 ',' yyyy-mm-dd ') from dual;
Results 18 - August -08

to_number (string)
This function will convert a string containing the number of number types, often without the use of this function,
Oracle can be implicitly converted to a numeric string

5 Other functions, introduce a few functions to convert an empty value:
Note: NULL indicates a null (unknown) value of zero indicates 0 (Unknown)

nvl (expression 1, expression 2)
If the expression 1 expression 2 to NULL is returned, otherwise the expression 1,
If the expression 1 and 2 are different types, then the expression 2 into the expression of type 1

nvl2 (expression 1, expression 2, expression 3)
If expression 1 is not NULL, the return expression 2
If expression 1 is NULL, the return expression 3

nullif (expression 1, expression 2)
If two expressions are equal, then return NULL, otherwise the expression 1, the
Function is equivalent to the following expression:
when the expression 1 = expression 2
then NULL
else expression 1

12.2) group function, also known as aggregate functions

  • avg, averaged
  • max, maximum demand
  • sum, the total demand
  • count (*), only the line number, including duplicates and null values
  • count (column name), only the line number does not include null values
  • count (distinct column names), and count (column name) is similar, but when you remove the duplicate values in the statistical

12.3) analytic function <br /> analysis function according to a set of rows to calculate the aggregate value of these functions are often used to complete the aggregate value of the total ranking, moving averages and other calculations and reports, using the analysis function, you need to complete all of the connection and all the where, group by, having clause, so the analysis function can only appear in the select list or order by clause, we introduce several analysis functions are as follows,

  • ROW_NUMBER returns consecutive rank, regardless of the value is equal
  • RANK equal value line ranking with the same ordinal then jump
  • DENSE_RANK equal value line ranking with the same serial number is continuous

Refer to the specific use "Oracle SQL help documentation"

13, Summary

  • SQL is a common database language
  • SQL commands can be divided into data definition language, data manipulation language, transaction control language and data control language
  • Oracle supports data types including character, numeric, date, time, RAW, LOB, etc.
  • SQL support operators include arithmetic, comparison, logical, set, and the concatenation operator
  • SQL functions can be divided into one-way functions, aggregate functions and analysis functions

  • Chapter Oracle query. Data Types. Function 2011-05-24

    Chapter 2 of Oracle query Oracle data types, functions Note: This chapter provides Oracle SQL help file (PDF) Download 1, the technical objectives Understanding of Oracle data types Understand the data definition language and data manipulation langua

  • Oracle 10g data types 2011-07-08

    Oracle 10g data types ☆ data type Definition: the scalar (SCALAR), composite (COMPOSITE), reference (REFERENCE) and four data types LOB ☆ scalar (SCALAR) Valid scalar type columns in the database used by the same type, in addition it also has some ex

  • Oracle 10g data types Daquan 2010-05-08

    Raiders all oracle data types: Data Type Range Description Notes Char (n) n = 1 to 2000 bytes Store fixed-length string. The default length is 1. Varchar2 (n) n = 1 to 4000 bytes Variable-length string, maximum length specified in the specific defini

  • oracle query data paging solution to the problem repeated 2010-06-24

    Paging in oracle query, we recognized a similar comparison shown below efficient database paging query (Effective Oracle by Design have described, many users have been a test oracle). Wrote, SELECT * FROM ( SELECT A. *, ROWNUM RN FROM (SELECT * FROM

  • Oracle 9i data types 2010-07-16

    Oracle 9i provides a total of 16 species of scalar data types Oracle 9i scalar data types Name Meaning Char Used to describe the fixed length of character data, length <= 2000 bytes varchar2 Used to describe the variable length character data, length

  • ORACLE query data dictionary views used 2010-09-21

    1. Users View the table of all users SQL>select * from user_tables; View the current user's default tablespace SQL>select username,default_tablespace from user_users; View the current user's role SQL>select * from user_role_privs; View the curren

  • oracle basic data types 2011-04-11

    VARCHAR2 (size) variable length string, maximum length of size bytes; size of the maximum value is 4000, and the minimum is 1; you must specify a VARCHAR2 in size; NVARCHAR2 (size) variable length strings, based on the chosen national character set,

  • Mysql Oracle Java data types were 2011-06-29

    Mysql Oracle Java BIGINT NUMBER (19,0) java.lang.Long BIT RAW byte [] BLOB BLOB RAW byte [] CHAR CHAR java.lang.String DATE DATE java.sql.Date DATETIME DATE java.sql.Timestamp DECIMAL FLOAT (24) java.math.BigDecimal DOUBLE FLOAT (24) java.lang.Double

  • Chapter 2 of Oracle query Oracle Data type functions 2011-05-01

    Chapter 1 Introduction to Oracle Oracle Chapter 3, Oracle locks, table partitioning Chapter 2 of Oracle query Oracle data types, functions Note: This chapter provides Oracle SQL help file (PDF) Download 1, the technical objectives Understanding of Or

  • Learning Oracle: Oracle data types 2010-06-04

    § 1.7 ORACLE data type Oracle Database data types, compared with other database systems, it is not much data type ,Oracle That data in other database systems than to save for many keywords. Oracle only NUMBER(m,n) Can express any complex digital data

  • Oracle database, common data types and jdbc connection string 2010-05-04

    1. Oracle database data types commonly used in varchar2 (length) variable length strings char (length) fixed length number () that an integer or floating point number (8) number (8,2) clog character large object blog binary large object jdbc connecti

  • SQLServer Oracle data types and data type conversion 2010-12-15

    Doing today SQLServer2005 database to Oracle database migration, note that Oracle's data types and SQLServer data types is not the same, you can run the SQLServer: SELECT * FROM msdb.dbo.MSdatatype_mappings SELECT * FROM msdb.dbo.sysdatatypemappings

  • Oracle paging query data duplication 2011-06-16

    oracle query page, encounter data duplication, the following pages teach you a solution to the oracle query data duplication problem, hope you can help. Paging in oracle query, we recognized a similar comparison shown below efficient database paging

  • Oracle data types and storage methods 2010-06-28

    Oracle data types and stored Yuan Guangdong original overview of an example, a comprehensive and thorough analysis of oralce basic data types and their storage methods. ORACLE 10G basis to introduce oralce 10g introduces new data types. Oracle data t

  • Oracle data types and storage (a) the character type 2010-08-26

    Oracle data types and storage methods Overview by example, a comprehensive and in-depth analysis of oralce basic data types and their storage. To ORACLE 10G basis, introduce oralce 10g introduces new data types. Oracle data types allow you to have a

  • oracle PL / SQL basic composition, block structure and basic syntax requirements, data types, variable definitions, operators and functions 2011-10-08

    oracle PL / SQL basic composition, block structure and basic syntax requirements, data types, variable definitions, operators and functions Feature PL / SQL language is the SQL language extensions, designed for application developers with features su

  • meter management (data types, tables created to delete, data CRUD operation) 2010-03-04

    Fun combat tutorial oracle (next day) Introduction 1. On the section reviews table management √ 3. Basic query √ 4. Complex queries √ database created √ Desired goals 1. Master oracle table management (create / maintain) 2. To maste

  • Oracle objects - abstract data types. Variable array. Nested table. Object tables and object views 2010-02-23

    Oracle Database is RDBMS (relational database) is ORDBMS (object-oriented database). RDBMS: When reusing a function, will need to be compiled; ORDBMS: allows you to define object types; in a relational database to store complex business models; devel

  • Oracle data types and functions 2010-04-28

    Oracle data types Data Type Description char The maximum length of the character, 2000B, The default length is 1B nchar The national character set based on the NLS character- , Maximum length 2000B, defaults to 1 Characters varchar2 Variable-length c

  • oracle query / subquery / copy tables and data 2010-06-16

    1, the connection query 1. In connection connections within the connection to meet the conditions for return all records. By default, the implementation of the connection query if the operator does not specify any connection, then these queries are a