Thứ Tư, 8 tháng 1, 2020

Bài 2.PL/SQL cơ bản - Cách tổ chức dữ liệu

1.     Kiểu DL cơ sở:

Basic Type
Specific Types
Numeric
INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, SHORTDECIMAL, NUMBER
Text
TEXT, NTEXT, ID
Boolean
BOOLEAN
Date
DATETIME, DATE


Character Datatypes
Đúng
Sai
CUSTNO NOT NULL CHAR(2)
SELECT transamt FROM cust_trans WHERE custno > '11';


Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
Char[size]
Maximum size of 2000 bytes.
Maximum size of 2000 bytes.
Maximum size of 2000 bytes.
Where size is the number of characters to store. Fixed-length strings. Space padded.
Size: có thể có hoặc không (VD column1 char hoặc column1 char(10))
nchar(size)
Maximum size of 2000 bytes.
Maximum size of 2000 bytes.
Maximum size of 2000 bytes.
Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)
Maximum size of 4000 bytes.
Maximum size of 4000 bytes.
Maximum size of 4000 bytes.
Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)
Maximum size of 4000 bytes.
Maximum size of 4000 bytes.
Minimum size is 1; maximum size is 4,000.)
Where size is the number of characters to store. Variable-length string.
Size: bắt buộc (VD colum1 varchar2 sẽ báo lỗi)
long
Maximum size of 2GB.
Maximum size of 2GB.
Maximum size of 2GB.
Variable-length strings. (backward compatible)
An EMP_IMAGE column cannot be included in the ORDER BY clause.
You cannot add a new column to the table with LONG as the data type.
You cannot alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
raw
Maximum size of 2000 bytes.
Maximum size of 2000 bytes.
Maximum size of 2000 bytes.
Variable-length binary strings
long raw
Maximum size of 2GB.
Maximum size of 2GB.
Maximum size of 2GB.
Variable-length binary strings. (backward compatible)

Ví dụ minh hoạ việc lưu trữ dữ liệu xâu chữ giữa các kiểu dữ liệu khác nhau
Numeric Datatypes
Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
number(p,s)
Precision can range from 1 to 38.
Scale can range from -84 to 127.
Precision can range from 1 to 38.
Scale can range from -84 to 127.
Precision can range from 1 to 38.
Scale can range from -84 to 127.
Where p is the precision and s is the scale.
For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
Input Data
Specified As
Stored As
7,456,123.89
NUMBER
7456123.89
7,456,123.89
NUMBER(*,1)
7456123.9
7,456,123.89
NUMBER(9)
7456124
7,456,123.89
NUMBER(9,2)
7456123.89
7,456,123.89
NUMBER(9,1)
7456123.9
7,456,123.89
NUMBER(6)
(not accepted, exceeds precision)
7,456,123.89
NUMBER(7,-2)
7456100

From <https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm>
numeric(p,s)
Precision can range from 1 to 38.
Precision can range from 1 to 38.
Precision can range from 1 to 38.
Where p is the precision and s is the scale.
For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float



CREATE TABLE test (col1 NUMBER(5,2), col2 FLOAT(5));
INSERT INTO test VALUES (1.23, 1.23);
INSERT INTO test VALUES (7.89, 7.89);
INSERT INTO test VALUES (12.79, 12.79);
INSERT INTO test VALUES (123.45, 123.45);
SELECT * FROM test;
      COL1       COL2
---------- ----------
      1.23        1.2
      7.89        7.9
     12.79         13
    123.45        120

From <https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm>
dec(p,s)
Precision can range from 1 to 38.
Precision can range from 1 to 38.
Precision can range from 1 to 38.
Where p is the precision and s is the scale.
For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)
Precision can range from 1 to 38.
Precision can range from 1 to 38.
Precision can range from 1 to 38.
Where p is the precision and s is the scale.
For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer




int




smallint




real




double precision





Date/Time Datatypes
Đúng
Sai
inv_date = '15-february-2008' : uses implicit conversion
inv_date > '01-02-2008' : uses implicit conversion
--> Chuyển thành inv_date>to_date('01-02-2008','dd-mm-yyyy'), inv_date kiểu date
inv_no BETWEEN '101' AND '110' : uses implicit conversion

TRANSDATE DATE
SELECT transdate + '10' FROM cust_trans;

SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;
SELECT SYSDATE - '01-JAN-2007' FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL;
SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;
Elements of the Date Format Model
---------------------------------------------------------------------
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month
MM Two-digit value for the month
MON Three-letter abbreviation of the month
MONTH Full name of the month
YYYY Full year in numbers
YEAR Year spelled out (in English)



Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
date
A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
A date between Jan 1, 4712 BC and Dec 31, 9999 AD.

timestamp (fractional seconds precision)
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
Includes year, month, day, hour, minute, and seconds.
For example:
timestamp(6)
timestamp (fractional seconds precision) with time zone
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
Includes year, month, day, hour, minute, and seconds; with a time zone displacement value.
For example:
timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
fractional seconds precision must be a number between 0 and 9. (default is 6)
Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone.
For example:
timestamp(4) with local time zone
interval year
(year precision)
to month
year precision is the number of digits in the year. (default is 2)
year precision is the number of digits in the year. (default is 2)
year precision is the number of digits in the year. (default is 2)
Time period stored in years and months.
For example:
interval year(4) to month
interval day
(day precision)
to second (fractional seconds precision)
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6)
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6)
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6)
Time period stored in days, hours, minutes, and seconds.
For example:
interval day(2) to second(6)

Large Object (LOB) Datatypes
Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
bfile
Maximum file size of 4GB.
Maximum file size of 232-1 bytes.
Maximum file size of 264-1 bytes.
File locators that point to a binary file on the server file system (outside the database).
blob
Store up to 4GB of binary data.
Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).
Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).
Stores unstructured binary large objects.
clob
Store up to 4GB of character data.
Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.
Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.
Stores single-byte and multi-byte character data.
nclob
Store up to 4GB of character text data.
Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.
Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.
Stores unicode data.

Rowid Datatypes
Data Type
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Explanation
(if applicable)
rowid
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.
Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)



Universal rowid.
Where size is optional.

Pasted from <http://www.techonthenet.com/oracle/datatypes.php>

·         Convert dữ liệu
Implicit Conversion
Char --> Number
Number --> Char
Syntax of Explicit Data Type Conversion
Functions
TO_NUMBER(char1, [format mask], [nls_parameters]) = num1
TO_CHAR(num1, [format mask], [nls_parameters]) = char1
TO_DATE(char1, [format mask], [nls_parameters]) = date1
TO_CHAR(date1, [format mask], [nls_parameters]) = char1

2.   Kiểu dữ liệu có cấu trúc
·         Cursors
Cursor là kiểu biến có cấu trúc, cho phép ta xử lý dữ liệu gồm nhiều dòng. Số dòng phụ thuộc vào câu lệnh truy vấn dữ liệu sau nó. Trong quá trình xử lý, ta thao tác với cursor thông qua từng dòng dữ liệu. Dòng dữ liệu này được định vị bởi một con trỏ. Với việc dịch chuyển con trỏ, ta có thể lấy được toàn bộ dữ liệu trả về
-- Thực hiện lệnh từ output của câu lệnh trước
DECLARE
cursor c1 is
select 'alter index '||index_owner||'.'||index_name||' rebuild SUBpartition '||SUBpartition_name||' tablespace pps_indx parallel 8 online' name from dba_ind_SUBpartitions
where   status like 'UN%'
union all
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' tablespace pps_indx parallel 8 online' name from dba_ind_partitions
where  status like 'UN%';
begin
    for r1 in c1
    loop
        execute immediate r1.name;
    end loop;
end;

·         Fetch 1 dòng dữ liệu từ cursor: (phải mở, đóng cursor)
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees WHERE department_id =30;
empno employees.employee_id%TYPE;
lname employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO empno, lname;
DBMS_OUTPUT.PUT_LINE(empno ||' '||lname);
CLOSE emp_cursor;
END;
/
Con trỏ emp_cursor trả về 6 employees có department_id=30 nhưng câu lệnh FETCH sẽ lấy về chỉ 1 row, muốn lấy tất cả các row thì phải dùng vòng lặp (for)

·         Fetch lấy all row từ cursor: LOOP...FETCH (phải mở, đóng cursor)
DECLARE
-- Khai báo cursor để truy vấn dữ liệu
CURSOR c_Emp IS
SELECT *
FROM emp
WHERE dept_id = 10;

-- Khai báo biến cursor tương ứng để chứa dòng dữ liệu
v_Emp c_EMP%rowtype;   -- v_Emp giống như kiểu record

BEGIN
-- Mở cursor
OPEN c_Emp;
LOOP
-- Lấy dòng dữ liệu từ cursor
FETCH c_Emp INTO v_Emp;

-- Thoát khỏi vòng lặp nếu đã lấy hết dữ liệu trong cursor
EXIT WHEN c_Emp%notfound;

-- Bổ sung dữ liệu vào Emp_ext với dữ liệu lấy được từ cursor
INSERT INTO Emp_ext (empno, ename, job) VALUES (v_Emp.empno, v_Emp.ename, v_Emp.job);
END LOOP;
-- Đóng cursor
CLOSE c_Emp;
END;




·         Cursor FOR.. LOOP
SET SERVEROUTPUT ON
DECLARE
CURSOR c_Emp IS
SELECT employee_id, last_name FROM employees
WHERE department_id =30;
Emp_record c_Emp%ROWTYPE;
BEGIN
FOR emp_record IN c_Emp LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id||' ' ||emp_record.last_name);
END LOOP;
END;
/

·         %ROWCOUNT and %NOTFOUND
SET SERVEROUTPUT ON
DECLARE
empno employees.employee_id%TYPE;
ename employees.last_name%TYPE;
CURSOR emp_cursor IS SELECT employee_id,
last_name FROM employees;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empno, ename;
EXIT WHEN emp_cursor%ROWCOUNT > 10 OR emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno) ||' '|| ename);
END LOOP;
CLOSE emp_cursor;
END ;
/
·         Kiểu dữ liệu Record
TYPE t_Emp IS
RECORD OF (
empno     number(4) not null,
ename     char(10),
job     char(9),
mgr     number(4),
hiredate     date default sysdate,
sal     number(7,2),
comm     number(7,2),
deptno     number(2) not null);

v_Emp_record     t_Emp;
+ Gán giá trị:

t_Emp.empno := 10;
t_Emp.ename := "BinhTV"
...

·         ROWTYPE: dữ liệu một dòng
Bản ghi trong PL/SQL. là một biến có thể giữ nhiều giá trị và là một tập hợp các biến tương ứng với các trường trong table.
Khai báo kiểu dữ liệu bản ghi.
Tên biến     Tên bảng%ROWTYPE;
Ví dụ:
v_Emp emp%ROWTYPE;

Truy nhập đến các trường trong dữ liệu bản ghi dùng giống như trong 1 dòng dữ liệu trả về. Ví dụ:
v_Emp.empno, v_Emp.sal, ...
...
DEFINE employee_number = 124
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employees
WHERE employee_id = &employee_number;
INSERT INTO retired_emps(empno, ename, job, mgr,hiredate, leavedate, sal, comm, deptno)
VALUES (emp_rec.employee_id, emp_rec.last_name,emp_rec.job_id,emp_rec.manager_id,emp_rec.hire_date, SYSDATE, emp_rec.salary,emp_rec.commission_pct, emp_rec.department_id);
END;
/
SET SERVEROUTPUT ON
SET VERIFY OFF
DEFINE employee_number = 124
DECLARE
emp_rec retired_emps%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM retired_emps;
emp_rec.leavedate:=SYSDATE;
UPDATE retired_emps SET ROW = emp_rec WHERE
empno=&employee_number;
END;
/
SELECT * FROM retired_emps;
DECLARE
v_data abcd.efgh%TYPE;
v_data_row abcd%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'SELECT efgh FROM abcd WHERE efgh = :text_string' 
INTO v_data
USING 'ijkl';
DBMS_OUTPUT.PUT_LINE( 'Column Variable: ' || v_data );

EXECUTE IMMEDIATE 'SELECT * FROM abcd WHERE efgh = :text_string' 
INTO v_data_row
USING 'ijkl';
DBMS_OUTPUT.PUT_LINE( 'Row Variable: ' || v_data_row.efgh );
END;

INFO: Column Variable: ijkl
INFO: Row Variable: ijkl

From <http://www.dba-oracle.com/t_oracle_execute_immediate.htm>

·         TYPE: Kiểu dữ liệu của một cột
Cú pháp:
Tên biến     Tên cột dữ liệu%TYPE;

DECLARE
salary NUMBER(6):=6000;
sal_hike VARCHAR2(5):='1000';
total_salary salary%TYPE; // total_salary trùng kiểu DL salary
BEGIN
total_salary:=salary+sal_hike;
dbms_output.put_line(total_salary);
END;
/

·         Kiểu dữ liệu Table
Cú pháp:
TYPE Tên_kiểu_Table IS
TABLE OF Tên kiểu dữ liệu [NOT NULL] INDEX BY BINARY_INTEGER;

Tên biến     Tên_kiểu_Table;
Ví dụ:
TYPE t_Name IS
TABLE OF Emp.Ename%TYPE INDEX BY BINARY_INTEGER;

v_First_name     t_Name;
v_Last_name     t_Name;


@ Trần Văn Bình - Founder of Oracle DBA AZ #OraAz #OracleDBAAz #OracleTutorial
By lúc
Labels:

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master