в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:29Z

1 версия

Новая страница

==CONVERSION functions: Print the current date/time as a character string and modifies the format==



<source lang="sql">

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_sysdate DATE := SYSDATE;
3 v_date DATE;
4 v_char VARCHAR2(20);
5 BEGIN
6
7
8
9 v_char := TO_CHAR(v_sysdate, "DD:MM:YYYY HH24:MI:SS");
10 DBMS_OUTPUT.PUT_LINE("Display as CHARACTER DD:MM:YYYY HH24:MI:SS: "||v_char);
11
12
13 END;
14 /
Display as CHARACTER DD:MM:YYYY HH24:MI:SS: 26:10:2009 10:41:36
PL/SQL procedure successfully completed.
SQL>

</source>

==convert a negative number into one with a trailing "minus sign,"==



<source lang="sql">

SQL>
SQL> SELECT
2 TO_CHAR(-1234,"9999MI") result
3 FROM dual;
RESUL
-----
1234-
SQL>

</source>

==Pass variable into to_char function==



<source lang="sql">

SQL> DECLARE
2 lv_format_txt CONSTANT VARCHAR2(11) := "$99,999.99";
3 lv_test_txt VARCHAR2(11);
4 lv_test_num NUMBER := 54321;
5 BEGIN
6 lv_test_txt := TO_CHAR(lv_test_num, lv_format_txt);
7 DBMS_OUTPUT.PUT_LINE("Test Value: " || lv_test_txt);
8 END;
9 /
Test Value: $54,321.00
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

</source>

==The TO_CHAR() function converts the expression into character data.==



<source lang="sql">

Syntax: TO_CHAR(<expression> [,[FORMAT] [,NLS_PARAM]] )
SQL>
SQL> SELECT
2 LENGTHB(NCHR(216)) national,
3 LENGTHB(TO_CHAR(NCHR(216))) converted
4 FROM dual;
NATIONAL CONVERTED
---------- ----------
2 1

</source>

==to_char(bdate,"fmMonth ddth, yyyy")==



<source lang="sql">

SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> select ename
2 , to_char(bdate,"fmMonth ddth, yyyy")
3 from emp;
Enter...
Tom December 17th, 1965
Jack Februari 20th, 1961
Wil Februari 22nd, 1962
Jane April 2nd, 1967
Mary September 28th, 1956
Black November 1st, 1963
Chris Juni 9th, 1965
Smart November 26th, 1959
Peter November 17th, 1952
Take September 28th, 1968
Ana December 30th, 1966
Jane December 3rd, 1969
Fake Februari 13th, 1959
Mike Januari 23rd, 1962
14 rows selected.
SQL> drop table emp;
Table dropped.

</source>

==to_char(round(sqrt(sal),2),"9999.99")==



<source lang="sql">

SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> select ename, sal, to_char(round(sqrt(sal),2),"9999.99")
2 "Bonus" from emp;
ENAME SAL Bonus
---------- ---------- --------
SMITH 800 28.28
ALLEN 1600 40.00
WARD 1250 35.36
JONES 2975 54.54
MARTIN 1250 35.36
BLAKE 2850 53.39
CLARK 2450 49.50
SCOTT 3000 54.77
KING 5000 70.71
TURNER 1500 38.73
ADAMS 1100 33.17
ENAME SAL Bonus
---------- ---------- --------
JAMES 950 30.82
FORD 3000 54.77
MILLER 1300 36.06
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.

</source>

==to_char(sysdate,"DAY dy Dy") as day, to_char(sysdate,"MONTH mon") as month==



<source lang="sql">

SQL>
SQL> select to_char(sysdate,"DAY dy Dy") as day
2 , to_char(sysdate,"MONTH mon") as month
3 from dual;
DAY MONTH
--------------- -------------
MAANDAG ma Ma OKTOBER okt
SQL>
SQL>

</source>

==to_char(sysdate, "Day", "nls_date_language=Dutch")==



<source lang="sql">

SQL>
SQL> select to_char(sysdate, "Day")
2 , to_char(sysdate, "Day", "nls_date_language=Dutch")
3 from dual;
TO_CHAR(S TO_CHAR(S
--------- ---------
Maandag Maandag
SQL>
SQL>

</source>

==to_char(sysdate,"hh24:mi:ss") as time==



<source lang="sql">

SQL> select sysdate as today
2 , to_char(sysdate,"hh24:mi:ss") as time
3 , to_char(to_date("01/01/2006","dd/mm/yyyy")
4 ,""is on "Day") as new_year_2006
5 from dual;
TODAY TIME NEW_YEAR_2006
---------- -------- ---------------
26-10-2009 10:03:46 is on Zondag
SQL>
SQL>

</source>

==Use to_char function to init an text value==



<source lang="sql">

SQL>
SQL> DECLARE
2 lv_current_date DATE := SYSDATE;
3 lv_current_month_txt VARCHAR(3) := TO_CHAR(SYSDATE, "MON");
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE("Current Date: " ||lv_current_date);
6 DBMS_OUTPUT.PUT_LINE("Current Month: " ||lv_current_month_txt);
7 END;
8 /
Current Date: 19-JUN-08
Current Month: JUN
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Conversion Functions/TO CHAR - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия