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

Function Learning of Oracle

1. Character function is the most commonly used function in oracle.

Lower (char): converts a string to lowercase format

Upper (char): converts a string to uppercase format

Length (char): returns the length of the string

Substr (char,m,n): take the substring of a string

Show all employees' names in lowercase?

SQL > select lower (ename), sal from emp

LOWER (ENAM SAL

--

Smith 800

Allen 1600

Display in uppercase?

SQL > select upper (ename), sal from emp

UPPER (ENAM SAL

--

SMITH 800

Show employees with exactly 5 characters?

SQL > select * from emp where length (ename) = 5

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO

-

7369 SMITH CLERK 7902 17-DEC-80 800

twenty

Display the first three characters of all employee names?

SQL > select substr (ename,1,3) from emp; starts with the first character and is offset three characters backward

SUBSTR (ENAME

-

SMI

ALL

SQL > select substr (ename,2,3) from emp; starts with the second character and is offset three characters backward

SUBSTR (ENAME

-

MIT

LLE

Take out lines with uppercase initials?

SQL > select upper (substr (ename,1,1)) from emp; the first step is to take out the first letter and then replace it with uppercase

UPPE

-

S

A

Lowercase all characters except the first letter of a string?

The first step is to use the substr function to extract the second character to the last character, and the length is calculated by the length function.

SQL > select lower (substr (ename,2,length (ename)-1)) from emp

LOWER (SUBSTR (ENAME,2,LENGTH (ENAME)-1)

-

Mith

Llen

Query the condition that the first character is uppercase and the other characters are lowercase.

Just combine the two.

SQL > select upper (substr (ename,1,1)) | | lower (substr (ename,2,length (ename)-1)) from emp

UPPER (SUBSTR (ENAME,1,1)) | | LOWER (SUBSTR (E)

-

Smith

Allen

Ward | |: Oracle uses double vertical bars to represent string concatenation functions

Replace (char1,search_string,replace_string)

Display the names of all employees and replace "A" with "B"?

SQL > select replace (ename,'A','B') from emp the former is the old character and the latter is the new character (that is, the replaced character)

REPLACE (EN

-

SMITH

BLLEN

2. Mathematical function

The data types of input parameters and return values of mathematical functions are numeric. Mathematical functions include cos, cosh, exp, in,

Log 、 sinh 、 sqrt 、 tan 、 tanh 、 acos 、 asin 、 atan 、 round

Round (n, [m])

This function is used to perform rounding. If m is omitted, it is rounded to an integer. If m is positive, it is rounded to the m place of the decimal point. If m is negative, it is rounded before the m place of the decimal point.

SQL > select round (sal), sal from student where xm='xiaocai'

ROUND (SAL) SAL

--

1234 1234.34 rounded

SQL > select round (sal,1), sal from student where xm='xiaocai'

ROUND (SAL,1) SAL

--

1234.3 1234.34

Trunc (n, [m])

This function is used to intercept a number, if m is omitted, the integer part is intercepted, and if m is a positive number, it is truncated after the m place of the decimal point. If m is negative, it is intercepted to the first m place of the decimal point

SQL > select trunc (sal), sal from student where xm='xiaocai'

TRUNC (SAL) SAL

--

1234 1234.34

SQL > select trunc (sal,1), sal from student where xm='xiaocai'

TRUNC (SAL,1) SAL

--

1234.3 1234.34

Mod (mdirection n)

To take the module, to get is the remainder.

SQL > select mod (10jue 2) from dual; can use the dual table (virtual table) when doing oracle tests

MOD (10Phone2)

-

0

SQL > select mod (1BI 3) from dual; remainder is 1

MOD (1pr 3)

-

one

Floor (n)

Returns the largest integer less than or equal to n

SQL > select floor (sal), sal from student where xm='xiaocai'

FLOOR (SAL) SAL

--

1234 1234.34 the largest integer less than or equal to 1234.34 is 1234, that is, an integer slightly smaller than the original value (or equal)

Ceil (n)

Returns the smallest integer greater than or equal to n

SQL > select ceil (sal), sal from student where xm='xiaocai'

CEIL (SAL) SAL

--

1235 1234.34 an integer slightly larger than the original value (or equivalent)

3. Date function

The date function is used to process data of type date

By default the date format is dd- July-yy

A, sysdate: this function returns the current system time

SQL > select sysdate from dual

SYSDATE

-

26-SEP-16

B, add_months (dline n): add n months to d

Find out the employees who have been on the job for 8 months now?

SQL > select * from emp where sysdate > add_months (hiredate,8); the entry time is hiredate, which is increased by 8 months. At this time, the system time is longer than this, which means it appears to have been on the job for 8 months.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

How many days has it been since the entry time of the employee?

SQL > select sysdate-hiredate workday,ename from emp

WORKDAY ENAME

--

13057.8384 SMITH

12992.8384 ALLEN

SQL > select trunc (sysdate-hiredate) workday,ename from emp

WORKDAY ENAME

--

13057 SMITH

12992 ALLEN

C, last_day (d): returns the last day of the month in which the specified date is located

Find out all the employees and time hired on the penultimate day of the month?

SQL > select hiredate,last_day (hiredate) from emp

HIREDATE LAST_DAY (HIREDATE)

--

1980-12-17 1980-12-31 entry time is 1980-12-17, and the last day of January of that year is 1980-12-31

1981-2-20 1981-2-28

SQL > select hiredate,ename from emp where hiredate=last_day (hiredate)-2

HIREDATE ENAME

--

28-SEP-81 MARTIN

4. Conversion function

The conversion function is used to change the data type from one type to another, and in some cases, the data of the oracle server allowed value is different from the actual one.

Oracle server implicitly converts to data types

Create table T1 (id int)

Insert into T1 values ('10'): so oracle will automatically convert' 10' to 10

Create table T2 (id vachar2 (10))

Inset into T2 values (1); so oracle automatically converts 1 to'1'

Although oracle can perform implicit data type conversions, it is not suitable for all situations. To improve the reliability of the program

Conversion functions should be used for conversion.

Function to_char

Replace one of the fields of the table with the function to_char

SQL > select to_char (hiredate,'yyyy-mm-dd') from emp

TO_CHAR (HI

-

1980-12-17

SQL > select to_char (sysdate,'day') from dual; replaces the query result in the format of day string

TO_CHAR (SYSDATE,'DAY')

-

Thursday

The date can be displayed in hours / minutes / seconds

Yy: a two-digit year

Yyyy: a four-digit year

Number of months in mm:2 digits

Number of days of dd:2 digits

The number of hours in hh34:24, for example, 8 p.m.: 20:00

The hh22:8 point is 08:00.

Mi, ss display minutes, seconds

Example:

SQL > select ename,to_char (hiredate,'yyyy-mm-dd hh34:mi:ss') from emp

ENAME TO_CHAR (HIREDATE,'YYYY-MM-DDHH

SMITH 1980-12-17 00:00:00

ALLEN 1981-02-20 00:00:00

WARD 1981-02-22 00:00:00

JONES 1981-04-02 00:00:00

MARTIN 1981-09-28 00:00:00

SQL > select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from emp

TO_CHAR (SYSDATE,'YY

-

2016-09-26 10:39:51

2016-09-26 10:39:51

Salary yoga currency unit shows:

9: displays the number and ignores the previous 0, indicating how many valid numbers and supported decimal places are in the type of salary field.

2: display numbers. If there are not enough digits, supplement them with 0.

.: display the decimal point at the specified location

Display a comma at the specified location

$: add US dollars before the number

L: add the local currency before the number (oracle installs the character set used by the user to determine which country it is)

C: add the international monetary unit symbol before the number

G: displays component symbols at the specified location

D: displays the decimal point symbol at the specified location

SQL > select ename,to_char (sal,'L99999.99') from emp

ENAME TO_CHAR (SAL,'L99999.99')

-

SMITH ¥800.00

ALLEN ¥1600.00

WARD ¥1250.00

JONES ¥2975.00

MARTIN ¥1250.00

SQL > desc emp

Name Type Nullable Default Comments

--

EMPNO NUMBER (4)

ENAME VARCHAR2 (10) Y

JOB VARCHAR2 (9) Y

MGR NUMBER (4) Y

HIREDATE DATE Y

SAL NUMBER (7Pol 2) Y because the field sal of this table emp is number (7Pol 2), it uses a total of seven significant digits, two decimal places.

COMM NUMBER (7 dint 2) Y

DEPTNO NUMBER (2) Y

The year used by to_char () to fetch the hiredate field

SQL > select ename,to_char (hiredate,'year') from emp

ENAME TO_CHAR (HIREDATE,'YEAR')

SMITH nineteen eighty

ALLEN nineteen eighty-one

WARD nineteen eighty-one

SQL > select ename,to_char (hiredate,'yyyy') from emp

ENAME TO_CHAR (HIREDATE,'YYYY')

-

SMITH 1980

ALLEN 1981

WARD 1981

SQL > select to_char (sysdate,'day') from dual

TO_CHAR (SYSDATE,'DAY')

--

Friday

SQL > select to_char (sysdate,'dd') from dual

TO_CHAR (SYSDATE,'DD')

-

23 only shows the day of this day.

Inquire about the employee whose entry time is 1980?

SQL > select * from emp where to_char (hiredate,'yyyy') = 1980

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

Inquire about the employee whose entry month is December?

SQL > select * from emp where to_char (hiredate,'mm') = 12

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

Function sys_context: system function, here are some replacement values for this function

1. Terminal: identifier of the interrupt corresponding to the current session client

2. Language: language

3. Db_name: current database name

4. Nls_date_format: the date format corresponding to the current session customer

5. Session_user: the database user name corresponding to the current session customer

6. Current_schema: the solution name corresponding to the current session customer

7. Host: returns the name of the host where the database resides

Through this function, you can query some important information, such as which database are you using?

SQL > select sys_context ('userenv','db_name') from dual

SYS_CONTEXT ('USERENV','DB_NAME')

Orcl11g

View the languages supported by the current database

SQL > select sys_context ('userenv','language') from dual

SYS_CONTEXT ('USERENV','LANGUAG

SIMPLIFIED CHINESE_CHINA.AL32UTF8

View nls_date_format

SQL > select sys_context ('userenv','nls_date_format') from dual

SYS_CONTEXT ('USERENV','NLS_DAT

DD-MON-RR

SQL > select sys_context ('userenv','terminal') from dual

SYS_CONTEXT ('USERENV','TERMINAL')

Pts/0

View session_user, which is equivalent to show user; viewing the current user

SQL > select sys_context ('userenv','session_user') from dual

SYS_CONTEXT ('USERENV','SESSION

SCOTT

Check current_schema: the name of the solution corresponding to the current session customer, one user corresponds to one scheme, and one solution has many data objects

SQL > select sys_context ('userenv','current_schema') from dual

SYS_CONTEXT ('USERENV','CURRENT

SCOTT

SQL > select sys_context ('userenv','current_schema') from dual

SYS_CONTEXT ('USERENV','CURRENT_SCHEMA')

SCOTT

View the host host of the current database

SQL > select sys_context ('userenv','host') from dual

SYS_CONTEXT ('USERENV','HOST')

Aliyun_test

Schema:

Oracle manages data objects by the name of the scheme

When the database creates a user, oracle assigns a schema scheme to the user

What is stored in the plan?

A lot of data objects

Tables, views, triggers, stored procedures, etc. (manage data objects in a schematic way)

Http://blog.csdn.net/kimsoft/article/details/4627520

A schema is a collection of database objects (used by a user.).

Schema objects are the logical structures that directly refer to the database's data.

A user is a name defined in the database that can connect to and access objects.

Schemas and users help database administrators manage database security.

We can see from the definition that schema is a collection of database objects. In order to distinguish each collection, we need to give this collection a name.

These names are many of the user name-like nodes we see under the enterprise manager scheme, and these user name-like nodes are actually a schema

Schema contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.

A user generally corresponds to a schema, whose schema name is equal to the user name and serves as the user's default schema.

This is why we see that the schema name is the database user name under the enterprise manager scenario. Cannot create a new schema in the Oracle database

Creating a schema can only be solved by creating a user (although there is a create schema statement in Oracle, it is not used to create a schema)

When you create a user, create a schem for that user with the same name as the user name and use it as the default shcema for that user. That is, the number of schema is the same as that of user.

And the schema name corresponds to the user name and is the same, so we can call schema an alias for user, which is not accurate, but it is easier to understand.

A user has a default schema, whose schema name is equal to the user name, and of course a user can also use other schema.

If we access a table without indicating which schema the table belongs to, the system automatically adds the default sheman name to the table.

For example, when we access the database, we access the emp table under the scott user. Through select * from emp;, in fact, the complete sql statement is written as select * from scott.emp.

The full name of an object in the database is schema.object, not user.object. Similar to if we do not specify the schema of the object when we create it

The schema of this object is the user's default schema. This is like a user having a default tablespace, but the user can also use other tablespaces

If we do not specify a tablespace when we create an object, the object is stored in the default tablespace, and if we want the object to be stored in another tablespace, we need to specify the tablespace for that object when we create the object.

Add:

SQL > select trunc (sysdate+7) + (1ap24) from dual; for the current date and time, add 7 days to the date, the hour in the time plus 1, and the other minutes and seconds remain the same, 1 from dual; 24: one day divided by 24 hours, equal to 1 hour

SQL > select trunc ((sysdate+7) + (1go 24)) from dual; the above sql is similar to this, and the results are all the same.

SQL > select trunc ((sysdate+7) + (1 to 24)) from dual

TRUNC ((SY

-

03-OCT-16

= >:

Create the assignment of the parameter in the function: = > (the meaning of the symbol)

There are two corresponding ways for oracle real participation parameters.

1. One is location, which is similar to parameter passing in object-oriented languages.

two。 The other is = > as a formal parameter, because the position correspondence method has limitations, for example, a function has four parameters, but the third one can not be passed (with default values).

There is no way to position the corresponding method. This method is usually used to pass parameters within oralce (generally, when some parameters have default values, you need to skip some parameters to call)

The meaning of each symbol in Oracle

% (percent sign): used to represent any number of characters, or there may be no characters at all.

_ (underscore): indicates the exact unknown character.

? (question mark): used to indicate the exact unknown character.

# (pound sign): used to indicate the exact Arabic numeral, 0 to 9.

[amurd] (square brackets): used to indicate a range of characters, in this case from a to d.

Single quotation marks ('): in Oracle, only single quotation marks should be used to enclose text and characters and dates, not numbers (including single and double quotation marks).

Double quotation marks ("): single and double quotation marks have different meanings in Oracle. Double quotation marks are used to enclose column aliases containing specific characters or spaces. Double quotation marks are also used to put text in date format.

Apostrophes ('): in Oracle, apostrophes can also be written as two single quotation marks adjacent to each other. To find all supplier names with apostrophes in the middle of the supplier name, you can write code like this: select * from l_suppliers where supplier_name like'%'%'

& symbol: in Oracle, the & symbol is often used to indicate a variable. For example, & fox is a variable, a slightly different one & & fox. Whenever & fox appears in an Oracle script, you are asked to provide a value for it. With & & fox, you only need to provide the value of the variable to & & fox when it first appears. If you want to use the & symbol as a normal symbol, you should turn this feature off. To turn off this feature, run the following command: set define off, which is a SQLplus command, not a SQL command. SQLplus sets the environment in which SQL runs in Oracle.

Double vertical bar (| |): Oracle uses double vertical bar to represent string concatenation function.

An asterisk (*): select * means to select all columns, and count (*) means to calculate all rows, indicating 0 or any number of characters when representing wildcards.

Forward slash (/): in Oracle, used to terminate a SQL statement. More precisely, it means "run the SQL code that is now in the buffer". The forward slash is also used as a separator.

Multiline comments: / *. * /.

Does not mean: there are many ways to express:! =, ^ =, not xxx=yyy, not (xxx=yyy)

Insert insert data

When using the values clause, the amount of data inserted at one time is limited, and when subqueries are used to insert data, an insert statement can insert a large amount of data.

When processing row migration or loading data from external tables into the database, you can use subqueries to insert data

1. Create a single table first

SQL > create table mytable (myid number (4), myname varchar2 (20), mydept number (3)

Table created

SQL > desc mytable

Name Type Nullable Default Comments

--

MYID NUMBER (4) Y

MYNAME VARCHAR2 (20) Y

MYDEPT NUMBER (3) Y

2. Insert a row of queried data into the new table using a subquery

SQL > insert into mytable select empno,ename,deptno from emp where deptno=20

5 rows inserted

3. View the inserted data

SQL > select * from mytable

MYID MYNAME MYDEPT

7369 SMITH 20

7566 JONES 20

7788 SCOTT 20

7876 ADAMS 20

7902 FORD 20

Use subquery to migrate rows and database data to improve efficiency

Update with update combined with subquery

Do you want scott employees to have the same positions, salaries and allowances as smith employees?

SQL > update emp set (job,sal,comm) = (select job,sal,comm from emp where ename='SMITH') where ename='SCOTT'

1 row updated

SQL > select job,sal,comm from emp where ename='SMITH'

JOB SAL COMM

CLERK 800.00

SQL > select job,sal,comm from emp where ename='SCOTT'

JOB SAL COMM

CLERK 800.00

Oracle transaction processing

The transaction is used to ensure the consistency of data. It consists of a set of related dml statements, all of which either succeed or fail.

For example, online money transfer is typically handled by transactions to ensure data consistency

Transactions and locks

When performing a transaction operation, that is, an dml statement operation, oracle will lock the affected table to prevent other users from setting up the table.

It is very important for users to modify the structure of this table.

Commit transaction

When execution uses the commit statement to commit a transaction, when the commit statement is executed, it confirms the change of the transaction, ends the transaction, deletes the SavePoint, and releases the lock

After ending the transaction with the quit statement, other sessions will be able to see the data after the transaction has changed, and when exiting the plus command line, the transaction will be committed by default

Fallback transaction

Fallback transactions need to use a SavePoint to roll back misoperated data.

Savepoint a

Rollback to a

A 1R 8 o'clock

A2:12:00

A3:17:00

You can roll back from a3 to a2, and then from a2 to A1

But once the commit is submitted, the save point will be lost

SQL > savepoint A1; create SavePoint

Savepoint created

SQL > delete from mytable where myname='SMITH'; Simulation Delete data

1 row deleted

SQL > commit; submit transaction

Commit complete

SQL > rollback to A1; rollback the SavePoint but failed, indicating that the SavePoint will be invalidated after the transaction is submitted

Rollback to a1

ORA-01086: SavePoint 'A1' has never been created in this session or is invalid

Read-only transaction

Read-only transactions are transactions that only allow query operations, but not any other dml operations. Use read-only transactions to ensure that users can only get

Data at a certain point in time. Assuming that the ticket sales agency starts to count today's sales at 18:00 every day, it is possible to use a read-only transaction when setting a read-only transaction.

After that, although other sessions may commit new transactions, read-only transactions will not make changes to the latest data, thus ensuring that the

Data information at a specific point in time

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.