Oracle sql collation used

2011-04-12  来源:本站原创  分类:Database  人气:89 

Tidy up the Oracle base commonly used in the project statement, be a small summary overview.

1, to obtain the current time

select sysdate from dual
2, time to get a char

select to_char (sysdate, 'yyyy-MM-dd') as mydate from dual

3, accurate to seconds

select to_char (sysdate, 'yyyy-MM-dd HH24: MI: SS') as mydate from dual

4, Oracle sql statement used to change the field type.
alter table trans_buycoupon_record rename column buy_num to buy_num1;

alter table trans_buycoupon_record add buy_num number;

update trans_buycoupon_record set buy_num = cast (buy_num1 as number);

alter table trans_buycoupon_record drop column buy_num1;

-------------------------------------------------- -------------------------------------------------- ------------------------------

(Added April 21)

5, A table associated with Table B, A and B are col this field, but maintain a record in Table A Table B is not, and now in the col to the table B updates the value of the field with A Table the same value in the col update prepay_send_record ps set end_date = (select m.end_date from member_prepay_account m where m.merchant_id = ps.merchant_id and ps.customer_id = m.customer_id) to note here is to find the table to be modified and data sources table relationships.

April 12, the first sum up here, after the last update.

August 15 update:

To add a new Oracle table index:

CREATE INDEX IND_CARBNOW_FRAMEID ON sms_send_record (marketing_id, biz_type);

The syntax is:

create INDEX index name on table (field names, field names 2);

相关文章
  • Oracle sql collation used 2011-04-12

    Tidy up the Oracle base commonly used in the project statement, be a small summary overview. 1, to obtain the current time select sysdate from dual 2, time to get a char select to_char (sysdate, 'yyyy-MM-dd') as mydate from dual 3, accurate to second

  • Save BLOB type reported java.lang.ClassCastException: oracle.sql.BLOB solution 2009-03-20

    Default tomcat's common / lib is not under ojdbc14.jar this jar package, However, if you want to use jndi configure the database connection pool, then have to manually put ojdbc14.jar into common / lib, the When publishing the works which need to ach

  • Oracle SQL Optimization 2010-02-15

    SQL optimization should be adjusted from five aspects: 1. Remove unnecessary large table full table scans 2. Cached small table full table scans 3. Inspection optimize the use of the index 4. Test the connection optimization technology 5. Cost to min

  • ORACLE SQL statement Categories 2010-03-11

    Oracle SQL statements can be divided into the following categories: Data manipulation language statements [Data manipulation language, DML] From one or more tables or views in the query data (SELECT); for operation [fetch] is scrollable [scrollable]

  • Oracle SQL Optimization (change) 2010-03-16

    Oracle SQL Optimization (change) Keywords: oracle Oracle SQL optimization rules: Minimize the use of IN operator, essentially all of the IN operator can, using EXISTS instead of IN the advantages of writing out the SQL is relatively easy to write and

  • Oracle SQL statements used to maintain 2010-03-31

    Oracle SQL statements used to maintain 1, see table space name and size select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespac

  • ubuntu Configuration of Oracle SQL Developer 2010-04-03

    ubuntu Configuration of Oracle SQL Developer Download Oracle SQL Developer is free to install version contains JDK start when an error occurs liangshihong @ liangshihong-kmimagine: ~ / Soft / sqldeveloper $ sh. / sqldeveloper.sh Oracle SQL Developer

  • ORACLE SQL statement optimization summarized in 2010-02-23

    ORACLE SQL statement optimization summarized in (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name

  • Create the linked server (oracle, sql server, access, excel) 2010-04-05

    ///////////////////////////// SQL SERVER /////////////////// ///////// Exec sp_droplinkedsrvlogin DBVIP, Null Exec sp_dropserver DBVIP EXEC sp_addlinkedserver @ Server = 'DBVIP', - was to visit the server alias @ Srvproduct ='', @ Provider = 'SQLOLED

  • oracle SQL Tracking: sql_trace and 10 046 events 2010-04-30

    SQL trace settings to SQL_TRACE very simple, SQL_TRACE is a parameter, only in the session layer, set it to TRUE, to usher in a SQL trace, Oracle SQL statement will be the implementation of records to trace file. Through access to trace files, will h

  • ORACLE SQL Properties of commonly used functions and worry of 2010-05-12

    NVL (eExpression1, eExpression2) If the calculation results eExpression1 null value, NVL () return eExpression2. If the calculation results eExpression1 not null value is returned eExpression1. eExpression1 and eExpression2 can be any data type. If e

  • Oracle.SQL Server.Sybase.MySQL Performance Optimization 2010-05-22

    Reprinted: http://hi.baidu.com/zhizhesky/blog/item/c0a64190dc061287a977a4d3.html (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in

  • Solution java.lang.classcastexception oracle.sql.blob cannot be cast to oracle.sql.blob 2010-06-03

    Save the file to the database at the time, reported java.lang.classcastexception oracle.sql.blob cannot be cast to oracle.sql.blob Solutions, see red OutputStream out = rs.getBlob (1). SetBinaryStream (1L);

  • Reprinted: oracle SQL query used classic 2010-06-03

    oracle SQL query used classical 1, see table space name and size select t.tablespace_name, round (sum (bytes / (1024 * 1024)), 0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;

  • ORACLE SQL and foreign key constraints - cascade delete 2010-06-04

    ORACLE SQL and foreign key constraints - cascade delete Recent software system to delete a record, it is necessary to also delete a lot of related tables, constraints still exist between them relations. So take into account when creating tables with

  • Oracle sql performance tuning adjustment 2010-06-08

    1. The types of ORACLE optimizer ORACLE optimizer There are 3 kinds: a. RULE (rule) b. COST (based on cost) c. CHOOSE (optional) Set the default optimizer, you can file OPTIMIZER_MODE init.ora parameters on the various statements, such as RULE, COST,

  • Hibernate operation Blob / Clob, the place cannot be cast to oracle.sql.BLOB error analysis (unfinished) 2010-06-18

    Environment: Hibernate3.5.2, jdk1.6, Oracle9.2.0.8, odbc14 (9.2.0.8). Jar, connection pool proxool0.9.1, tomcat6.0 Blob operation when the return on the Hibernate object of type BLOB blob compulsory type of conversion, Namely: (oracle.sql.BLOB) blob,

  • When using the Oracle SQL trace the problem to note 2010-06-19

    When using the Oracle SQL trace the problem to note http://www.it168.com 2009 November 30 Day IT168 website Director: IT168 Laoxiong Editor: Xiao Xiong Comments: 1 This Tag: Oracle Oracle Database Management Oracle Database Optimization Technical doc

  • DB2.ORACLE SQL wording of the main differences 2010-07-13

    To tell the truth, ORACLE spoiled the domestic programmers, code ORACLE SQL filled with a lot of features, few people know the ANSI standard SQL is like, cause the program could not run away from ORACLE Fortunately, with hibernate in epidemic, the si

  • Driver's Blob representation is of an unsupported type: oracle.sql.BLOB 2010-07-19

    Start today, when the web server always prompt as the Exception: 1: java.sql.SQLException: Driver's Blob representation is of an unsupported type: oracle.sql.BLOB reasons: tocmat the lib directory to place the oracle driver ojdbc14.jar packages, and