Oracle/PLSQL FUNCTION
.1> REPLACE Function
Description
The Oracle/PLSQL REPLACE function replaces a sequence of characters in a string with another set of characters.
Syntax
REPLACE( string1_source, string_to_replace_source [, replacement_string_new_replace] )
ví dụ:
REPLACE('123123tech', '123');
Result: 'tech'
.2> Các hàm thống kê, ngày tháng, number, chuỗi
.3> INSTR ()
Hàm INSTR trả về vị trí của một chuỗi con trong một chuỗi cho trước.
Cú pháp:
INSTR( p_string, p_substring [, p_start_position [, p_occurrence ] ] )
INSTR( string, substring [, start_position [, th_appearance ] ] )
Với '
th_appearance' là lần xuất hiện thứ
th_appearance của
substring, ngầm định là 1
ví dụ:
INSTR('Tech on the net', 'e')
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 2)
Result: 11 (the second occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 3)
Result: 14 (the third occurrence of 'e')
INSTR('Tech on the net', 'e', -3, 2)
Result: 2
.4 substr
The Oracle/PLSQL SUBSTR functions allows you to extract a substring from a string.
The syntax for the SUBSTR function in Oracle/PLSQL is:
SUBSTR( string, start_position [, length ] )
For example:
SUBSTR('This is a test', 6, 2)
Result: 'is'
SUBSTR('This is a test', 6)
Result: 'is a test'
Hàm mở rộng của SUBSTR là REGEXP_SUBSTR có cú pháp như sau:
Chi tiết:
REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )
chú ý:
| [^ ] | Used to specify a nonmatching list where you are trying to match any character except for the ones in the lis |
Ví dụ:
SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 1)
FROM dual;
Result: 'TechOnTheNet '
SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 2)
FROM dual;
Result: 'is '
select regexp_substr('2234,5678','[^,]+', 1, level)
from dual
connect by regexp_substr('2234,5678','[^,]+', 1, level) is not null;
Result: '2234'
'5678'
Những ví dụ trên đã gọi hàm REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance ] ] )
ví dụ khác:
-- có rCodes = '22er,1123,fecb,mnpq'
select regexp_substr(rCodes,'[^,]+', 1, level) LCODE
from dual
connect BY regexp_substr(rCodes, '[^,]+', 1, level)
is not null) ;
sẽ trả về các bản ghi
LCODE
---------
22er
1123
fecb
mnpq
The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.
The syntax for the NVL function in Oracle/PLSQL is:
NVL( string1, replace_with )
For example:
SELECT NVL(supplier_city, 'n/a')
FROM suppliers;
The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.
NLSSORT returns the string of bytes used to sort char.
Ví dụ:
select * from TABLE1 order by nlssort(ename,'nls_sort=vietnamese')
VIETNAM collation:
CREATE INDEX emp_idx1 ON emp(NLSSORT(ename, 'NLS_SORT=Vietnamese'));
.7 Hàm to_date
In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In SQL Server, you can use CONVERT or TRY_CONVERT function with an appropriate datetime style.
-- Specify a datetime string and its exact format
SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;
.8 Hàm TRUNC
The syntax for the TRUNC function in Oracle/PLSQL is:
TRUNC ( date [, format ]
Ví dụ: SELECT TRUNC(sysdate,'MM') FROM dual; -- trả về 9/1/2016
SELECT TRUNC(TO_DATE('22-SEP-2016'),'MM') FROM dual; -- trả về 9/1/2016
SELECT TRUNC(TO_DATE('22-SEP-2016')) FROM dual; -- trả về 0 giờ, 0 phút, 0 giây ngày 9/22/2016
.9 Hàm DECODE
The syntax for the DECODE function in Oracle/PLSQL is:
DECODE( expression , search , result [, search , result]... [, default] )
Parameters or Arguments
- expression
- The value to compare.
- search
- The value that is compared against expression.
- result
- The value returned, if expression is equal to search.
- default
- Optional. If no matches are found, the DECODE function will returndefault. If default is omitted, then the DECODE function will return null (if no matches are found).
Example
The DECODE function can be used in Oracle/PLSQL.
You could use the DECODE function in a SQL statement as follows:
SELECT supplier_name,
DECODE(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
The above DECODE statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
ELSE
result := 'Gateway';
END IF;
The DECODE function will compare each supplier_id value, one by one.
WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). Also, WM_CONCAT has been removed from 12c onward, so you can't pick this option.
It let you aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. Using the SCOTT.EMP table as an example, we might want to retrieve a list of employees for each department.
Base Data:
DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
Desired Output:
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
If you are not running 11g Release 2 or above, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you:
COLUMN employees FORMAT A50
SELECT deptno, wm_concat(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
3 rows selected.
Description
REGEXP_LIKE return boolean value ofregular expression matching in the WHERE clause.
Syntax
REGEXP_LIKE ( expression, pattern [, match_parameter ] )
ví dụ:
select rowid, REGISTER_NUMBER from document WHERE REGISTER_NUMBER is not null and regexp_like(REGISTER_NUMBER, '^[0-9]+$');
SELECT rowid, (case when Not regexp_like(d.register_number, '^[0-9]+[0-9+-.\s]*$') then 0 else to_number(d.register_number) end) reg FROM document d order by reg desc;
Một số câu lệnh Oracle căn bản
Một số khái niệm RDBMS
PL/SQL trong oracle là Procedural Language/Structured Query Language, tương đương với một ngôn ngữ lập trình hướng thủ tục được áp dụng trong oracle để viết ra các function/procedure thay vì sử dụng các câu truy vấn DB thông thường.
Function: trả về giá trị, về căn bản sẽ không có tham số kiểu
OUT or IN OUT
Procedure: tương thự hàm void trong java, không trả về giá trị, nhưng lại có tham số kiểu OUT hoặc INOUT tương đương với trị trả về.
Hình sau nếu rõ Cấu trúc căn bản của khối lệnh PL/SQL:
DDL = data definition language, to create a database schema with CREATE and ALTER statements, creating tables, indexes, sequences, .. it is used to define data structures.
DML = data manipulation language to manipulate and retrieve data (insertions, updates, and deletions, retrieve data by executing queries with restrictions, projections, and join operations (including the Cartesian product). For reporting, use SQL to group, order, and aggregate data as necessary; nest SQL statements inside each other (subselects)
Vendor Support for Catalog and Schema Objects
| Vendor | Catalog | Schema |
|---|
| Oracle | Does not support catalogs. When specifying database objects, the catalog field should be left blank. | Typically the name of an Oracle user ID. |
- server instance == database == catalog == all data managed by same execution engine
- schema == namespace within database, identical to user account
- user == schema owner == named account, identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas to identify any object you need (schema name + object name)
