1、分页sql
SELECT * FROM ( SELECT A.*,ROWNUM RN FROM ( ) A WHERE <![CDATA[ROWNUM <= #{limit}]]> ) WHERE <![CDATA[RN >= #{offset}]]>
2、新增修改表字段类型
alter table tableName modify 字段名 varchar2(100);--修改列字段
alter table tableName add 字段名 NUMBER(2,0) DEFAULT 0;---新增一列
3、查询重复数据
1、根据单个字段,查找表中多余的重复记录
select * from table where id in (select id from table group by id having count(id) > 1);
2、根据多个字段,查找表中多余的重复记录
select * from table a where (a.id,a.name) in (select id,name from table group by id,name having count(*) > 1);
3、对重复数据按照特定字段进行排序,用到函数:DENSE_RANK(),PARTITION BY 分组, ORDER BY 排序。对结果集进行排序,排名值没有间断。
select id,name,score , icno DENSE_RANK() over(partition by icno order by score desc) pm from table;
4、删除表中多余的重复记录(多个字段),只留有rowid最小(最大)的记录
delete from table
where id in (select id from table group by id having
count(*) > 1)
and rowid not in(select max(rowid) from table group by id having count(*)>1);
--(select min(rowid) from table group by id having count(*)>1);
4、建表sql
CREATE TABLE TABLE_NAME
( ID NUMBER,.......
CREATETIME DATE DEFAULT sysdate,
TYPE NUMBER(2,0) DEFAULT 0
)
--建序列sql
CREATE SEQUENCE TABLE_NAME_SEQ --序列名
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10;CREATE OR REPLACE EDITIONABLE TRIGGER TRIGGER_TABLE_NAME
BEFORE INSERT ON TABLE_NAME FOR EACH ROW
BEGIN SELECT TABLE_NAME_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; END;
COMMENT ON TABLE TABLE_NAME is '表名';
COMMENT ON COLUMN XJ_STUDENT_BUS.CREATETIME IS '创建时间';
COMMENT ON COLUMN XJ_STUDENT_BUS.ID IS '编号';
COMMENT ON COLUMN XJ_STUDENT_BUS.TYPE IS '类型 1 :类型1 2 :类型2';
5、新建存储过程
CREATE OR REPLACE PROCEDURE procedure_name
IS
BEGIN
-- 在这里编写存储过程的逻辑
END; /
6、 查询存储过程
SELECT text
FROM
all_source
WHERE owner = 'schema_name' -- 替换为包含存储过程的模式(schema)名称
AND name = 'procedure_name' -- 替换为存储过程的名称 ORDER BY line;