FAQs about Oracle Development

FAQs about Oracle Development

Directory

Insert and display number data of Oracle

Insert and read-out display of Oracle time data

Oracle Auto-increment Field

[1] insert and display Oracle number data

(1) Insert data of the number type

Convert the string to the number type before inserting it. Use the to_number function. In fact, this function is automatically converted without conversion.

SQL> insert into terminaldevice values (to_number ('000000'), '1', '1', '000000', '1', '1', '2 ', '3', '4', '5', '6', '7', '8', to_date ('2017-01-01 13:14:20 ', 'yyyy-mm-dd hh24: MI: ss'), to_date ('2017-01-01 13:14:20 ', 'yyyy-mm-dd hh24: MI: ss ')
);

(2) query display

Convert data of the number type to Char by using the to_char function.

SQL> select to_char (terminaldevice_id), terminaldevice_installdate from terminaldevice;

To_char (terminaldevice_id) terminaldevice
------------------------------------------------------
11111111111111111111-1 month-05
11111111111111111112-1 month-05
01-1 month-05

If no conversion is performed, it is displayed in the same way as the scientific notation.

SQL> select terminaldevice_id, terminaldevice_installdate from terminaldevice;

Terminaldevice_id terminaldevice
-------------------------------
1.1111e + 19 01-1-05
1.1111e + 19 01-1-05

[2] insert and read-out display of Oracle time data

(1) Insert Date data:

Character to date data, character string 20050101131420 to date

SQL> insert into terminaldevice (terminaldevice_installdate) values (to_date ('2017-01-01 13:14:20 ', 'yyyy-mm-dd hh24: MI: ss '));

SQL> Update terminaldevice set terminaldevice_installdate = to_date ('201312', 'yyyymmddhh24miss ') Where terminaldevice_id = 20050101131420

SQL> select to_date ('2017-01-01 13:14:20 ', 'yyyy-mm-dd hh24: MI: ss') from dual;

To_date ('2017-
--------------
01-1 month-05

SQL> select to_date ('20140901', 'yyyymmddhh24mis') from dual;

SQL> select terminaldevice_installdate from terminaldevice;

Terminaldevice
--------------
01-1 month-05

(2) format and display date data

Convert date data to characters, and convert date to string 20050101131420

SQL> select to_char (terminaldevice_installdate, 'yyyy-mm-dd hh24: MI: ss') as installdate from terminaldevice;

Installdate
-------------------
13:14:20

SQL> select to_char (terminaldevice_installdate, 'yyyymmddhh24mis') as installdate from terminaldevice;

Installdate
--------------
20050101131420

The 24-hour format is displayed with hh24
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual;
Select to_date ('1970-01-01 13:14:20 ', 'yyyy-mm-dd hh24: MI: ss') from dual;
To_date () function
1. Description of date format parameters
D. day of the week
The name of day, which is filled with spaces to 9 characters.
Day of DD month
The day of the year in DDD
Short Name of Dy day
Week of the Year of the iw iso Standard
Four-digit year of the iyyy ISO Standard
Yyyy four-digit year
Last three digits of YYY, YY, and Y years, two digits, one digit
HH hours, at 12 hours
Hh24 hours, in 24 hours
Mi score
SS seconds
Mm Month
Abbreviated month of Mon
Full name of month
W the week of the month
The day of the week in WW 1. Date interval operation
Current Time minus 7 minutes
Select sysdate, sysdate-interval '7' minute from dual
Current Time minus 7 hours
Select sysdate-interval '7' hour from dual
Current Time minus 7 days
Select sysdate-interval '7' day from dual
Current Time minus July
Select sysdate, sysdate-interval '7' month from dual
Current Time minus 7 years
Select sysdate, sysdate-interval '7' year from dual
Time Interval multiplied by a number
Select sysdate, sysdate-8 * interval '2' hour from dual
2. Date-to-character operations
Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: MI: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-DDD hh: MI: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm IW-D hh: MI: ss') from dual
3. Character-to-date operations
Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: MI: ss') from dual
The usage is similar to that of to_char.
4. to_number
Use the to_number function to convert characters to numbers.
To_number (char, ['format'])
Digit format
9 represents a number
0 force display 0
$ Place a $ character
L place a floating local currency character
. Display decimal point
, Display the thousands of indicators

[3] Oracle Auto-increment Field

In Oracle, there is no auto-incrementing integer data type as in SQL Server.
The auto-increment field of Oracle is implemented through sequence.

Create sequence goods_goods_id
Minvalue 1
Max value 99999999999999999999
Start with 1
Increment by 1
Nocache;

Insert into goods (goods_id, goods_name, goods_count, goods_unit, goods_price, goods_discountrate, goods_money, invoice_id) values (values, 'farmer roast chicken ', 10, 'only', '15. 0', '0. 80', '123', '123 ');

Insert into goods (goods_id, goods_name, goods_count, goods_unit, goods_price, goods_discountrate, goods_money, invoice_id) values (values, 'Magic scarves ', 10, 'two', '123. 0', '0. 80', '123', '123 ');

In Oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.
1. Create Sequence
First, you must have the create sequence or create any sequence permission,
Create sequence emp_sequence
Increment by 1 -- add several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Cache 10; -- set the cache sequence. If the system goes down or otherwise, the sequence will be discontinuous. You can also set it to --------- nocache.
The sequence created for s_depart is as follows:

Create sequence s_s_depart
Minvalue 1
Max value 999999999999999999999999999
Start with 1
Increment by 1
Nocache;

Once emp_sequence is defined, you can use currval, nextval
Currval = returns the current Sequence Value
Nextval = increase the sequence value, and then return the Sequence Value
For example:
Emp_sequence.currval
Emp_sequence.nextval

Where sequence can be used:
-Select statements that do not contain subqueries, snapshot, and view
-The insert statement is in the subquery.
-In the value of the nsert statement
-Update in Set

See the following example:

Insert into s_depart (departid, partition name, departorder) values (s_s_0000.nextval, '20140901', 1 );