常见sql for oracle

最新推荐文章于 2025-05-23 09:54:10 发布

阮減显

最新推荐文章于 2025-05-23 09:54:10 发布

阅读量341

点赞数

CC 4.0 BY-SA版权

分类专栏： database

本文为博主自创文章，不确保代码或者观点正确，如因引用文章中的代码或者观点造成的损失，恕不负责。

本文链接：https://blog.youkuaiyun.com/lewy7/article/details/46311265

database 专栏收录该内容

30 篇文章

订阅专栏

本文提供了Oracle数据库中SQL语句的实用案例，包括日期和数值的格式化、空值处理、条件判断、数学函数应用、分页查询、表结构及约束查询等。此外还介绍了表创建、索引创建及触发器的实现。

select to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss.ff3'),to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_date('2010-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss') from dual;/*类型转换*/

select to_char(-485,'999') ,to_char(1485,'9,999'),to_char(1485.8,'99999.99') ,to_char(1485.8898,'99999.99')from dual;/*类型转换*/
select to_number('39202.88000','999999.99999') from dual;/*类型转换*/
select nvl('','null') from dual;/*空值转换*/
select decode(1,1,'man',2,'woman','unknow') from dual;/*条件翻译*/

select substr(message,length(message)-1,2) from tb_liuyi;/*右截取*/
select round(3.1459,2),trunc(3.1415926,3),floor(3.943883),ceil(3.94),abs(-999.9),0-9999.8 from dual;
select count(*) as rn , sendtime from tb_liuyi having count(*)>1 group by sendtime ;/*统计查询*/

select sid from tb_liuyi where rownum<=5; /*取前5条*/
select * from (select rownum as rn ,A.* from (select * from tb_liuyi) A where rownum <10) where rn>5;/*分页查询5-10*/

select * from ALL_TABLES where table_name=upper('tb_liuyi');/*表空间*/
select * from ALL_TAB_COLUMNS where table_name=upper('tb_liuyi');/*表结构*/

select * from ALL_CONSTRAINTS where table_name=upper('tb_liuyi');/*表约束*/
select * from ALL_CONS_COLUMNS where table_name=upper('tb_liuyi');/*行约束*/

select * from user_col_comments where table_name=upper('tb_liuyi');/行注释/
select * from user_tab_comments where table_name=upper('tb_liuyi');/表注释/

/*建表*/
drop sequence SEQ_MY_TABLE_ID;
drop table MY_TABLE cascade constraints;
drop table MY_TABLE;
CREATE TABLE MY_TABLE
(
   sid NUMBER(16) NOT NULL PRIMARY KEY,
   message varchar2(255),
   sendtime date default sysdate ,
   fee NUMBER(12.2),
   mrk varchar2(1) default 'N' NOT NULL,
   overtime timestamp default current timestamp
);
comment on column MY_TABLE.sid is '消息序号';
/*建立索引*/
create unique index MY_TABLE_IDX1 on MY_TABLE(
sid,
sendtime
);
--create sequence
create sequence SEQ_MY_TABLE_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

/*触发器*/

Create Or Replace Trigger Trig_liuyi
  After Insert Or Delete Or Update On tb_liuyi  For Each Row
Begin
  If Inserting Then
    Insert Into tb_liuyi_trig Values ('insert', Sysdate);
  Elsif Updating Then
    Insert Into tb_liuyi_trig Values ('update', Sysdate);
  Elsif Deleting Then
    Insert Into tb_liuyi_trig Values ('delete', Sysdate);
  End If;
End;

大规模导入数据使用sqlldr（详情见网络），

另外，oracle提供块查询的接口，具体见网络。

收藏于 2012-07-20

来自于百度空间