INSTR
SELECT ZIPCODE,SIDO, GUGUN, DONG, RI,BLDG, BUNJI, SEQ
FROM TB_ZIP
WHERE INSTR(SIDO, '대전') > 0
OR INSTR(GUGUN, '대전') > 0
OR INSTR(DONG, '대전') > 0
OR INSTR(RI, '대전') > 0 ;
vo만들기
select 'private '||
DECODE( DATA_TYPE , 'NUMBER', 'Integer ', 'String ')||
LOWER(COLUMN_NAME)||';'
from cols
where TABLE_NAME = 'TB_ZIP'
ORDER BY COLUMN_ID;
WITH구문으로 인라인뷰 작성
WITH CARTB AS (
SELECT DISTINCT CART_MEMBER, CART_PROD
FROM CART
)
select a.mem_name
, a.mem_hp
, a.mem_mail
, c.*
from member A
LEFT OUTER JOIN CARTB B
ON(a.mem_id = b.cart_member)
LEFT OUTER JOIN prod c
ON (b.cart_prod = c.prod_id)
where MEM_ID='a001';
데이터 만들기 - INSERT SELECT
INSERT INTO board (
bo_no,
bo_content,
bo_title,
bo_writer,
-- bo_date, 기본값
-- bo_hit, 기본값
-- bo_rec, 기본값
bo_pass, bo_ip,
bo_mail
-- bo_parent 작성하는 모든 글은 root글이 된다
)
SELECT ROWNUM+(24*3) , --bo_no
MEM_NAME||'의 취미 생활은 '||MEM_LIKE,
MEM_NAME||'이 남긴 글',
MEM_NAME,
MEM_PASS,
'192.168.45.'||MOD(NVL(MEM_MILEAGE, 1), 256),
MEM_MAIL
FROM MEMBER;
