sqlplus userid/passwd < sql_text.sql
해당 .sql 파일이 있는 위치에서 sqlplus 로 접속해서
@c:\sample.sql
select * from (
select rownum as rnum, A.* from (
[Original SQL]
) A where rownum <= [offset + limit] order by rownum desc
) where rownum <= [limit] order by rnum asc;
SELECT * FROM
( SELECT a.*, rownum+rnum-1 as total_rows
FROM ( SELECT a.*, rownum rnum
FROM (
원하는 ORDER BY 절이 포함된 SELECT 쿼리
) a
ORDER BY rnum DESC ) a
ORDER BY rnum )
WHERE rnum BETWEEN ? AND ?
예)
SELECT * FROM
( SELECT a.*, rownum+rnum-1 as total_rows
FROM ( SELECT a.*, rownum rnum
FROM (
SELECT column1, column2
FROM table
WHERE column like '%'
ORDER BY column DESC
) a
ORDER BY rnum DESC ) a
ORDER BY rnum )
WHERE rnum BETWEEN 1 AND 10
주의) 오라클 8i이상만 지원한다.
참고) BETWEEN ? AND ?의 start와 end 구하기
int currentPage = 1; // 현재페이지
int ItemCountPerOnePage = 10; // 한페이지 보여줄 목록 개수
int end = currentPage * ItemCountPerOnePage;
int start = end - (ItemCountPerOnePage - 1);
컬럼변경쿼리
ALTER TABLE member_info MODIFY (celphone_number VARCHAR2(11))
ALTER TABLE member_info MODIFY (celphone_number not null)
ALTER TABLE member_info MODIFY (celphone_number null)
ALTER TABLE table RENAME COLUMN original_name TO new_name;