Oracle常用的函數和文法

1、強大的insert  into values()

to_date('2021-01-01','yyyy-mm-dd')

可以在下面的插入語句中使用：insert into 表名（列名）values(值) 

insert into 表名（列名）values(值)  values子句也可使用select

--oracle insert...values() 文法的values是否支援select
--插入成功
insert into test001(fid,fid2) values((select count(1) from user_tables),200)
--插入成功
insert into test001(fid,fid2) select count(1),200 from user_tables
           

2、查詢表的定義

select t.table_name 表名,
c.comments 字段名稱,
t.column_name 字段編碼,
t.data_type || '(' || to_char(t.data_length) || ')' 類型
FROM user_tab_cols t, user_col_comments c
WHERE c.table_name = t.table_name
and c.column_name = t.column_name
and t.hidden_column = 'NO'
and c.TABLE_NAME In ('T_FA_CARD')
and c.column_name in ('FDISPOSALTYPE')
order by c.TABLE_NAME, t.column_name
           

3、查詢某一個表前面幾行資料

select * from  user_tab_cols where rownum< 10;
select * from user_col_comments where rownum< 10;
           

4、字元類函數 

--去除空格
select rtrim(ltrim('   abc   ')) from dual;
--字元串長度
select length('abc') from dual;
--子字元串
select substr('abcde',1,2) from dual;
           

5、查詢結果帶出序号的row_number()函數，同sql server

select row_number() over (order by fid) from test001;
           

6、匿名塊

create table test002(fid int ,fname varchar2(100));

begin
insert into test002(fid,fname) values(1,'aa');
insert into test002(fid,fname) values(2,'bb');
alter table test002 rename to test003;
end;