Oracle

method One: by To_number function exception to judgeCREATE OR REPLACE FUNCTION is_number (Str_ VARCHAR2) RETURN VARCHAR2IsNUM_ number;BEGINNum_: = To_number (STR_);RETURN ' Y ';EXCEPTIONWhen OTHERS ThenRETURN ' N ';END Is_number;/Sql> Select

Decode (expression, search_1, result_1)Returns result_1 if expression results =search_1 results, similar to if elseDecode (expression, search_1, Result_1, search_2, result_2)Decode (expression, search_1, Result_1, search_2, Result_2, ....., Search_n,

simple use of syntax:Mysql:ALTER TABLE = MyISAM; ALTER TABLE ADD fulltext (abstract); SELECT * from Dataset_ods WHERE MATCH (abstract) against ('XX');Oracle:CREATE INDEXFulltextindex_dataset_ods onDataset_ods (ABSTRACT) Indextype isCtxsys. CONTEXT;

1.OverviewThe parttion by keyword is part of the analytic function in Oracle , which differs from the aggregation function in that it can return multiple records in a group, and the child aggregation function generally has only one result that

The provides a function for generating a UUID in Oracle SQL sys_guid: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/ functions187.htm#i79194 Http://en.wikipedia.org/wiki/Universally_unique_identifier sql> Select Sys_ GUID ()

Slow query speed due to a large number of delete operations causing the space to not be released and the horizontal bit high--high level position due to a large number of delete operations causing the space to not be releasedALTER TABLE

First, the businessA transaction is a logical unit of business that guarantees that all operations on the data are either successful or all fail.Second, the characteristics of the transaction:1. Atomicity: A transaction is the smallest execution

Common oracle functions-decode The Decode function is very useful in actual development and has powerful functions. Combined with other functions, it can simplify a lot of work; DECODE (field, condition 1, result value 1, condition 2, result value 2

Oracle Performance Optimization Operation 10: decomposing complex queries and replacing variables with Constants For complex Where condition combinations, Where contains multiple indexed fields, use the IF statement to discuss the situation; At

What are the usage and differences between Decode and nvl functions in Oracle?Decode Decode (condition, value 1, translation value 1, value 2, translation value 2 ,..., default Value... else if... else has the same meaningNVL Format:NVL (string1,

Conversion Function (learning notes), conversion function learning notes -- ****************************** Conversion Function-- Format the current date and time Select sysdate, to_char (SYSDATE, 'yyyy-MM-DD hh24: mi: ss') format date, to_char

General functions (learning notes), general function learning notes -- **************** Common functions-- NVL processing NULL-- NVL (number | column, default value). If the displayed number is NULL, the default value is used.-- NVL2 (number |

Transaction Processing (learning notes), transaction processing learning notes Transaction: A mechanism, an operation sequence, is the logical unit of database work One or more SQL statements that complete a set of related behaviors An inseparable

Create a tablespace (learning notes) and create learning notes Create a tablespace: Generally, it is created by the Administrator and logged on as the administrator during creation. Table space name: prefix with TBS _. You can also customize the

Lock (learning notes), learning notes Row-level locking and table-level locking: Row-Level Lock: When you execute, INSERT, UPDATE, DELETE, and select for update statements, ORACLE will implicitly implement the locking of records. If the transaction

Database Connection (study notes) and Database Study Notes After Oracle is installed, several additional services will be added to the service. Since Oracle consumes system resources, we recommend that you change the service type to manual. You can

Oracle Performance Optimization Operation 14: using function-based indexes As mentioned above, any operations on columns may cause full table scanning. For example: select * from emp where substr (ename, 1, 2) = 'sm '; However, this type of

Oralce time conversion usage TO_CHAR (DATE, FORMAT) 1. This week, this month, and this year -- This week select trunc (sysdate, 'D') + 1 from dual; -- Monday select trunc (sysdate, 'D') + 7 from dual; -- Sunday -- select trunc (sysdate, 'mm') from

Oracle Performance Optimization Operation 9: shielding useless Indexes Example of "oracle performance optimization operation 8" In actual queries, there are also queries involving DisposalCourseFlag = 6. If the index on this field is used, it is

Common oracle index creation rules 1. The table's primary key and foreign key must have an index; 2. Indexes should be available for tables with more than 300 of the data volume; 3. For tables that are often connected to other tables, indexes