sql function

          1 "'
          2 ||
          3 isql*plus? http://127.0.00.1/isqlplus
          4 desc author
          5 initcap('SQL Course')
          ? INSERT('JellwWord','W')? 6
          ?? LPAD (salary,10,'*')? *****24000
          ?? RPAD (salary,10,'*')? 24000*****
          ?? TRIM ('H' from 'HolloWorld') olloWord
          ?? substr('helloword',1,5)? hello
          ?? substr('helloword',-1,5) oword
          6? Number Functions
          ?? round(45.926,2)?? 45.93
          ?? round(45.926,-2) 0
          ?? round(55.926,-2) 100
          ?? trunc(45.926,2)?? 45.92
          ?? mod(1600,300)? 100
          7 data function
          ?? systdate
          ?? (sysdate-hire_date)/7 as weeks
          ?? months_between? number of months between two dates
          ?? months_between ('01-sep-95','11,jan-94')? 19.6774194
          ?? add_months????? add calendar months to date
          ?? add_months('11-JAN-94',6)? '11-JUL-94'
          ?? next_day??????? next day of the date specified
          ?? next_day('01-SEP-95','FRIDAY') '08-SEP-95'
          ?? last_day??????? last day of the month
          ?? last_day('01-feb-95')? '28-feb-95'
          ?? round?????????? round date
          ?? assume sysdate='25-jul-95'
          ?? round(sysdate,'month') 01-aug-95
          ?? round(sysdate,'year')? 01-JAN-96
          ?? trunc?????????? truncate date
          ?? trunc(sysdate,'month') 01-Jul-95
          ?? trunc(sysdate,'month') 01-JAN-95
          8? Conversion Functions?
          ? 1) implicit data typ conversion
          ?? varchar2 or char? ---number
          ?? varchar2 or char? ---date
          ?? numbeer?????????? ---varchar2
          ?? date????????????? ---varchar2
          ? 2) to_char(date,'format')
          ? format:
          ??? YYYY Full year in numbers
          ??? YEAR Year spelled out
          ??? MM?? Two-digit value for month
          ??? MONTH Full name of the month
          ??? MON? THree-letter abbreviation of the month
          ??? DY?? Three-letter abbreviation of the day of the week
          ??? DAY? Full name of the day of hte week
          ??? DD?? Numberic day of the month
          ??? HH24:MI:SS AM? 15:45:32:PM
          ??? DD "of"? MONTH 12 of october
          ? 3) to_char function with number?
          ?? TO_CAHR(number,'format_model')
          ?? These are some of the format elements you can use with the to_char function to display number as a character.
          ??? 9 Reqresents a number
          ??? 0 Forces a zero to be displayed
          ??? $ Places a flationg dollar sign
          ??? L Uses the floating local currency symbol
          ??? . Prints a decimal point
          ??? , Print a thousand director
          ? select to_char(qtym,"$999.99")
          ? 4) Using t_number and to _date functions
          ?? a converting a character string to a number format using to_number function
          ?? to_number(char,"format")l
          ?? b converting a character string to a date format
          ?? to_date(char,"format")
          5 Nesting Functions
          .Single-row function can be nested to many level
          .Nested function can be evaluated from deepest level
          6General Function
          These function work with any data type and pertain to using nulls
          nvl(expr1,expr2);
          nvl2(expr1,expr2,expr3)
          nullif(expr1,expr2)
          coalesce(expr1,expr2,,,,exprn)
          ?1) nvl function
          ?convert a null to an actual function
          ?a Data type can be used are data character and number
          ?b Data types must match??????????????????????????????
          ?(set wrap off
          ? set line 1000
          ?)
          ?2)Using the COALESCE Function
          ?a The advantage of the coalesce function over nal function is that coalesce function can take multiple alternative value
          ?b If the first value is not null, it return that expression,otherwise,it does a coalesce of remaining expressions
          6 Conditional Expressions
          ?a Provide the use of if-then-else logic
          ?b use two methods: case expression decode function
          ? select last_name,job_id,salary,
          ???????? case job_id when 'it' then 1*salary
          ????????????????????? when 'manager' then 1.2*salary
          ???????? else salary end;
          ? from employee.

          ? select last_namek,job_id,salary,
          ???????? decode(job_id,'it' ,1*salary,
          ??????????????????????? 'manager',1.2*salary,
          ??????????????? salary)
          ?? from employees
          ??

          posted on 2006-09-22 11:50 康文 閱讀(374) 評論(0)  編輯  收藏 所屬分類: 數據庫

          <2006年9月>
          272829303112
          3456789
          10111213141516
          17181920212223
          24252627282930
          1234567

          導航

          統計

          常用鏈接

          留言簿(1)

          隨筆分類

          隨筆檔案

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 剑川县| 清徐县| 谢通门县| 临颍县| 德化县| 普定县| 新民市| 多伦县| 平湖市| 灌阳县| 扶风县| 平山县| 西青区| 富阳市| 吉安县| 邳州市| 平原县| 乐平市| 新乐市| 罗江县| 潢川县| 河源市| 阿拉善盟| 崇仁县| 车险| 东源县| 天气| 隆回县| 陆良县| 白山市| 洞口县| 循化| 新建县| 兴海县| 凌海市| 庆云县| 新邵县| 南阳市| 当阳市| 资源县| 唐河县|