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)

          隨筆分類

          隨筆檔案

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 修水县| 沾益县| 祥云县| 巴林左旗| 咸丰县| 鹤岗市| 休宁县| 拉萨市| 淮安市| 隆昌县| 阿克陶县| 遂川县| 邵东县| 岗巴县| 札达县| 巧家县| 肃北| 湘西| 青龙| 屏山县| 大悟县| 浙江省| 金门县| 东乡| 石屏县| 璧山县| 南昌市| 玛纳斯县| 越西县| 阿尔山市| 黔西| 漳浦县| 荔波县| 枣强县| 宁远县| 乌恰县| 无锡市| 沁阳市| 社旗县| 凭祥市| 册亨县|