Oracle SQL雜記

[code="sql"]SELECT

   rank() over(ORDER BY phone_no) AS rankNum

   , CUST_NAME AS custName

   , COMPANY_NAME AS companyName

   , PHONE_NO AS phoneNo

   , EMAIL AS email

  FROM PHONE_LOG

  WHERE 1=1 



[code="sql"]

SELECT 'WEB'

||TO_CHAR(SYSDATE,'YYMMDD')

|| trim(

TO_CHAR((SELECT

NVL(MAX(TO_NUMBER(SUBSTR(wo_no,10))),0)+1

FROM WO_HEAD

WHERE

INSTR(wo_no,'WEB'||TO_CHAR(SYSDATE,'YYMMDD'))>0),'000')

) AS woNo

FROM dual



显示Oracle版本号,在command窗口下，输入


[code="sql"]show release

SQL窗口
select * from product_component_version

 

lpad重新格式化字符串

 

update user_ u
set u.screenname = substr(u.screenname,0,2) || lpad(substr(u.screenname,3), 3,'0')
where u.screenname like 'lr%'

 

 

a