oracle page sql

 

 

 

 

---

PL/SQL是Oracle对SQL扩展的过程性语言，通常运行在数据库服务器上。PL/SQL是过程性的，不是声明性的，这意味着代码规定了如何完成工作。需要以某种方式将PL/SQL代码发送到服务器中执行。  
  SQL*Plus是一个交互式程序，它让你输入并执行SQL语句，它还可以让你输入PL/SQL代码，并将它们发送到服务器中执行。它是开发和创建PL/SQL存储过程及函数中最常见的前端工具之一。

 

---

 

看字段名与数据类型  
  select   *   from   cols     WHERE   TABLE_name=upper('table_name')  
   
  查看主键：  
  select   *   from   user_constraints   where   constraint_type='P'  
  and   TABLE_name=upper('table_name')  

 

---

 

 

select * from
(select t.*, rownum as rn from tablename t  order by create_time desc)
where rn > 10 and rn <15

 

---

select * from
(select t.*, rownum as rn from lottery_pursue t  order by create_time desc)
where rn between 10 and 15

---

select * from

(select t.*,rownum row_num from mytable t order by t.id) b

where b.row_num between 1 and 10

结果发现由于该语句会先生成rownum 后执行order by 子句,因而排序结果根本不对,后来在GOOGLE上搜,原来多套一层select 就能很好的解决该问题,特此记录,语句如下:

select * from

 

(select a.*,rownum row_num from

(select * from mytable t order by t.id desc) a

) b where b.row_num between 1 and 10

 

验证过程如下：

 

select create_time from
(select t.*, rownum as rn from tablename t order by t.create_time desc )
where rn between 1 and 5

select create_time from

(select a.*,rownum row_num from

(select * from tablename  t order by t.create_time desc) a

) b where b.row_num between 1 and 5