V. Oracle transactions and built-in functions

Transactions and built-in functions in Oracle

What is a transaction?

A transaction is used to ensure data consistency. It consists of a group of related DML statements. The statements in this group are either all successful or all failed.

For example, online transfer is a typical transaction processing to ensure data consistency.

Transactions and locks:

When you execute a transaction (DML statement), Oracle locks the target table to prevent other users from modifying the table structure. This is very important for you.

Several important operations of transactions:

1. Set save point

Savepoint A1;

1. Cancel some transactions and roll back

Rollback to A1;

1. Cancel all transactions

Rollback

Read-only transactions

A read-only transaction is a transaction that only allows the execution of the query statement (select), and does not allow any other DML statement operations. With read-only transactions, you can only obtain data at a certain time point.

For example, if the ticket sales site starts to count the sales status of today at every day, you can use a read-only transaction. After a read-only transaction is set, although other sessions may submit new transactions, however, read-only transactions do not change the latest transactions, so that data information at specific time points can be obtained.

 

SQL function usage

Character Functions

Description: character functions are the most common functions in Oracle.

Lower (char): converts a string to lowercase;

Upper (char): converts a string to uppercase;

Length (char): returns the length of the string;

Substr (char, startindex, contentlength): truncates string strings;

For example, employee names with uppercase letters and lowercase letters are displayed;

Select upper (substr (ename, 1,1) | Lower (substr (ename, 2, length (ename)-1) from EMP;

Note: | used in Oracle to connect two strings

For example, the names of all employees are displayed in lowercase letters;

Select lower (ename) | Upper (ename, 2, length (ename)-1) from EMP;

Replace (char, search_string, replace_string): String replacement;

Instr (char1, char2, [, N [, m]): obtains the position of the string in the string;

 

Mathematical functions

The input parameters and returned values of mathematical functions are numeric. Mathematical functions include cos, cosh, sin, Sinh, exp, LN, SQRT, tan, Tanh, ACOs, asin, atan, and round.

Commonly used:

Round (n, [m]): Performs rounding. If M is omitted, It is rounded to an integer. If M is a positive number, it is rounded to the next M digit of the decimal point. If M is a negative number, the m rounded to the decimal point is the first;

Trunc (n, [m]): This function is used to intercept numbers. If M is saved, the fractional part is truncated. If M is a positive number, it is truncated to the M digit after the decimal point. If M is a negative number, it is truncated to the M digit before the decimal point.

MoD (m, n): modulo

Floor (n): returns the largest integer less than or equal to N, rounded down;

Ceil (n): returns the smallest integer greater than or equal to N, and returns the rounded integer;

The processing of numbers is the most used in the financial system or banking system. Different processing methods have different effects on the financial statements.

For example, if the value of one month is 30 days, the remaining amount is ignored;

Select trunc (SAL/30), ename form EMP;

Select floor (SAL/30), ename from EMP;

 

Other mathematical functions:

ABS (n): returns the absolute value of number n.

ACOs (n): returns the arc cosine of a number.

Asin (n): returns the arc sine of a number.

Atan (n): returns the arc tangent of a number.

Cos (n): returns the cosine of a number.

Exp (n): returns the nth power of E.

Log (m, n): returns the logarithm value.

Power (m, n): returns the N power of M.

 

Date Functions

The date function is used to process data of the date type.

By default, the Oracle date format is dd-mon-yy, that is, 12-8 months-98.

Sysdate: This function returns the system time

Add_months (d, n): Specifies the date to add n months

Last_day (d): returns the last day of the month of the specified date.

For example, search for employees who have been in the company for more than 8 months.

Select * from EMP where add_months (hiredate, 8) <sysdate;

For example, the name and employment date of an employee who has served for 10 years are displayed.

Select ename, hiredate from EMP where sysdate> = add_months (hiredate, 10*12 );

For example, for each employee, the number of days of joining the company is displayed.

Select ename, floor (sysdate-hiredate) "days of employment" from EMP;

Select ename, trunc (sysdate-hiredate) "days of employment" from EMP;

For example, find all employees who are employed on the last three days of each month

Select ename from EMP where hiredate = last_day (hiredate)-2;

 

Conversion functions

Conversion functions are used to convert data types from one type to another. In some cases, the data types allowed by the Oracle server are not the same as those of the actual data type. In this case, the Oracle server implicitly converts the data type.

For example:

Create Table T1 (ID, INT );

Insert into T1 values ('10'); then oracle will automatically convert '10' to 10.

Create Table T2 (ID, varchar2 (10 ));

Insert into T2 values (10); Oracle will automatically convert 10 to '10 '.

Note: Although oracle can perform implicit data type conversion, it is not suitable for all situations. To improve program reliability, you should use the Conversion Function for conversion.

 

To_char ()

We can use select ename, hiredate, Sal from EMP where deptno = 10; to display information, but in some cases it cannot meet all requirements;

For example, how to display the date format as hour/minute/second

 

For example, how to display the salary as the specified currency

Select ename, to_char (SAL, 'l99999. 99') from EMP;

The returned result is: rmb800.00.

 

 

 

For example, the entry date of each employee is displayed, accurate to hour, minute, and second.

Select ename, to_char (hiredate, 'yyyy-mm-dd hh24: MI: ss') from EMP;

 

Example: show all employees who joined in June 1980

Select ename form EMP where to_char (hiredate, 'yyy') = 1980;

Example: show all employees who joined in February

Select ename from EMP where to_char (hiredate, 'mm') = 12;

 

To_date

The to_date () function converts a string to the date type.

Example: Add a date based on Chinese habits: year-month-day

Insrt into EMP (hiredate) values (to_date ('2017-12-09 ', 'yyyy-mm-dd') where deptno = 1990;

 

System Function: sys_context

1. Terminal: the terminal identifier corresponding to the current session customer.
2. Language: Language
3. Db_name: Current Database Name
4. Nls_date_format: Date Format corresponding to the current session customer
5. Session_user: the database user name corresponding to the current session customer
6. Current_schema: default solution for current session customers
7. HOST: host name corresponding to the database

This function allows you to view important system information, such as querying the database in use :,

Select sys_context ('userenv', 'db _ name') from dual;