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


.5 Hàm NVL
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.
.6 Hàm NLSSORT
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.

.10 Hàm WM_CONCAT
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

  • select substr(txt, instr(txt, ',', 1, level) + 1,  instr(txt, ',', 1, level + 1) -instr(txt, ',', 1, level) -1) as token  from  (select ',' || '?' || ',' txt  from dual ) connect by level < length(txt) -length(replace(txt, ',', ''));
    => Kết quả:
    1 ?
  • select ',aa,bb,' || 'cc,dd' || ',' txt  from dual;
  • select  level from    dual connect by level <= 10;
  • ALTER TABLE fee_invoice ADD (id  NUMBER(15,0));
  • ALTER TABLE fee_invoice ADD PRIMARY KEY (id)
  • ALTER TABLE staff_abc ADD PRIMARY KEY (abc_code,staff_abc_code)
  • ALTER TABLE staff_abc ADD CONSTRAINT staff_abc_pk PRIMARY KEY (abc_code,staff_abc_code);
  • ALTER TABLE supplier DROP CONSTRAINT supplier_pk;
  • ALTER TABLE stock_daily_record ADD CONSTRAINT fk_stock_daily_record FOREIGN KEY (STOCK_ID) REFERENCES stock(STOCK_ID); 
  • ESCAPE in LIKE Conditions:
    
The syntax for the LIKE condition in SQL is:
    

    expression LIKE pattern [ ESCAPE 'escape_character' ]
    

    
Parameters or Arguments
    

    

    expression
    

    A character expression such as a column or field.
    

    pattern
    

    
A character expression that contains pattern matching. The wildcards that you can choose from are:
    

    

    WildcardExplanation
    
    

    %Allows you to match any string of any length (including zero length)
    

    _Allows you to match on a single character
    

    

    

    ESCAPE 'escape_character'
    

    Optional. It allows you to pattern match on literal instances of a wildcard character such as % or _.
    

    
For example:
    

    SELECT *
FROM suppliers
WHERE supplier_name LIKE 'Water!%' ESCAPE '!';
    

    
This Oracle LIKE condition example identifies the ! character as an escape character. This statement will return all suppliers whose name is Water%.
    

    SELECT last_name 
   FROM employees
   WHERE last_name 
   LIKE '%A\_B%' ESCAPE '\'; => '_' sẽ là ký tự literal.
select * from shop s where s.shop_path like '%_$$1234%'   escape  '$'; => chứa 1 ký tự $
select * from shop s where s.shop_path like '%_$\_1234%'   escape  '\';
  • IN, ANY, ALL, EXISTS
    Toán tử ANY chỉ ra bất kỳ giá trị liệt kê trong danh sách. ALL là tất cả các giá trị trong danh sách. EXISTS là tồn tại 1 bản ghi trả về trong câu truy vấn.
  • từ khóa HAVING: sẽ lọc kết quả truy vấn để lấy về 1 kết quả như mong muốn.
  • Kết nối các bảng csdl: join (inner join), left join, right join, full join
  • câu lệnh SELECT gồm những phép toán sau:
    >>Phép chiếu (Prjection): chỉ ra những column trong table muốn lấy data
    >>Phép chọn (Selection): lọc số lượng dòng dữ liệu cần lấy về từ 1 bảng
    >>Phép liên kết bảng (JOIN): lấy dữ liệu từ nhiều table thông qua kết nối các table với nhau bằng từ khóa join, left join, right join, full join
  • Hierarchical Queries



    Cú pháp:

    SELECT [COLUMNS]

FROM TABLE_NAME

START WITH [COLUMN] = [ROOT NODE VALUE]

CONNECT BY [CONDITION]

    Ví dụ:

    Let’s analyze it: the CONNECT BY clause, mandatory to make a hierarchical query, is used to define how each record is connected to the hierarchical superior.
    The father of the record having MGR=x has EMPNO=x.
    On the other hand, given a record with EMPNO=x, all the records having MGR=x are his sons.
    The unary operator PRIOR indicates “the father of”.
    START WITH clause is used to from which records we want to start the hierarchy, in our example we want to start from the root of the hierarchy, the employee that has no manager.
    The root of the hierarchy could be not unique. In this example it is.
    The LEVEL pseudocolumn indicates at which level each record stays in the hierarchy, starting from the root that has level=1.
    connect by level là vị trí trong hệ thống phân cấp của bản ghi hiện hành trong mối qua hệ với nút gốc (root node). Nó cũng đặc tả mối quan hệ giữa parent rows và child rows của hệ thống phân cấp các bản ghi do có cột này là parent của cột kia trong 1 bảng CSDL. => level cũng sẽ giới hạn số dòng trả về, ví dụ: bằng số INPUT truyền vào.

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
VendorCatalogSchema
OracleDoes 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)