One , The author gives a brief account of
First of all, introduce yourself , The author works in a traditional job , The first year is basically done with oracle, The technology is very backward , But there is no way. At that time, my strength was really weak , It needs to be honed , Also in urgent need of living expenses to provide for oneself , Don't say more .
I believe many of my friends are like me . You know what you know , It's the most important thing to strengthen yourself .
Two , Tool recommendation
Next, I would like to recommend some Oracle Common functions and tools of .
1. Common functions
select greatest('11','5') from dual;-- Take the minimum value of the string
select least(4,1) from dual;-- Take the minimum number
select wm_concat( Name ) from dual;-- Turn a column into a row
rank() over (partition by Group column name order by Sort column names DESC) -- Group and sort and rank according to a column
select listagg(o.serialno, ',') within group(order by o.serialno)from REPORT_FORMS o -- Columns can be wrapped with commas
SELECT A.COL FROM A WHERE TRANSLATE(A.COL,'*0123456789','*') IS NULL;-- Filter a column of pure numbers ( Columns contain other strings and numbers ):
select * from user_tab_comments a where a.table_name=' Table name ' ; -- Look at the table and explain
COMMENT ON TABLE b_so IS ' Table explanation ';-- Explain the table when you create it
substr(' character string ',-8) -- For string interception function
replace('V_ENDRERATIOWAYQUO','*',''); -- For string replacement function
select substr('AAA-BBB',1,instr('AAA-BBB','-',-1)-1) value from dual; -- Intercepts all values before a string
-- Single column primary key
alter table Table name add constraint Primary key name primary key ( The primary key field to set );
-- Combined the primary key
alter table ECS_STORE.TF_B_AIR_CONFIG add constraint TF_B_AIR_CONFIG_PK primary key (TYPE_ID , PROVINCE_CODE);
2. Create primary key auto increment
-- Create primary key auto increment --
/* First step : Create a table */
create table t_user(
id int primary key, -- Primary key , Self growth
username varchar(20),
password varchar(20),
type varchar(20)
);
/* The second step : Create custom sequence*/
CREATE SEQUENCE user_sequence
increm.........