กระทู้เก่าบอร์ด อ.สุภาพ ไชยา
2,479 8
URL.หัวข้อ /
URL
การสร้าง Function ใน PostgreSQL
สมมติต้องการให้ฟีลด์ product_id มีรูปแบบ
PROD000001
PROD000004
PROD000003
…
ไปเรื่อยๆ
ถ้าเป็นใน Access ก็ทำได้โดยการเขียนโค้ดในฟอร์มช่วย จากตัวอย่างเก่าๆ ผมจะใช้ Double Click event ของ Text Box ของฟีลด์เป้าหมายช่วยใส่รหัสนี้ให้
แต่ถ้าเป็นใน PostgreSQL เราสามารถเขียน Function ไว้ในฐานข้อมูลช่วยได้
ความสามารถตรงนี้ยังไม่มีใน Access ครับ
ถ้าเป็น Postgres ผมจะนึกถึงฟังก์ชันขึ้นมาทันที่ เพื่อจะนำมาช่วยงานครั้งนี้
1. สร้างตารางเป้าหมายก่อน
ให้มี 2 ฟีลด์คือ product_id และ product_name
mydb=# create table products (
mydb(# product_id char(10),
mydb(# product_name varchar(255)
mydb(# );
CREATE
เปิดดูโครงการสร้างของตารางที่เพิ่งจะสร้างขึ้น
mydb=# \d products
Table "products"
Column | Type | Modifiers
--------------+-----------------------+-----------
product_id | character(10) |
product_name | character varying(80) |
2. ใช้ count(*) ช่วยในการนับจำนวนข้อมูลว่ามีกี่อันในตารางเป้าหมาย
ต้องสร้างเป็นฟังก์ชันช่วย ผมตั้งชื่อฟังก์ชัน mymax() โดยคืนค่าเป็น bigint
mydb=# create or replace function mymax() returns bigint as '
mydb'# select count(*) from products;
mydb'# ' language sql;
CREATE
ลองทดสอบ
mydb=# select mymax();
mymax
-------
0
(1 row)
3. สร้างอีกฟังก์ชันหนึ่ง เพื่อจัดรูปแบบของ product_id ให้เป็นตามต้องการคือ PRODXXXXXX
ผมจะใช้ฟังก์ชัน rpad(), ltrim() ของ Postgres ช่วย
(ดูฟังก์ชันที่จัดการ String ของ Postgres ได้ที่หน้า http://agserver.kku.ac.th/agro/html/functions-string.html)
mydb=# create or replace function mynext() returns text as '
mydb'# select rpad(''PROD'',10,ltrim(to_char(mymax()+1, ''000009''),'' ''));
mydb'# ' language SQL;
CREATE
ฟังก์ชันข้างบนชื่อ mynext() คืนค่ามาเป็น text
ทดสอบ
mydb=# select mynext();
mynext
------------
PROD000001
(1 row)
4. ลองเพิ่มข้อมูล ใช้ insert into ชื่อตารางเป้าหมาย ค่าที่จะใส่ลงไป
mydb=# insert into products values (
mydb'# mynext(), ‘Postgres’);
เพื่อให้การเพิ่มข้อมูลของเราทำได้เร็วยิ่งขึ้น ลองเขียนฟังก์ชันสำหรับเพิ่มข้อมูลขึ้นมาอีกอัน
mydb=# create or replace function myadd(text) returns integer as '
mydb'# insert into products values (
mydb'# mynext(), $1);
mydb'# select 1;
mydb'# ' language sql;
CREATE
ฟังก์ชันนี้ชื่อ myadd() รับค่าเป็น text แล้วคืนค่าเป็น integer
ลองเพิ่มข้อมูลโดยใช้ฟังก์ชันข้างต้น
mydb=# select myadd('Oracle 8i');
myadd
-------
1
(1 row)
ลองวิวดูข้อมูลที่เพิ่งใส่ลงไป
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | Oracle 8i
(2 rows)
จะเห็นว่ามี product_id ลำดับถัดไปให้เองอัตโนมัติ
5. ลองใช้ default value ของฟีลด์เป้าหมายช่วยในการเพิ่มข้อมูลก็ได้
ผมใช้ default mynext() เป็นค่า Default Value ของฟีลด์ชื่อ product_id
ลบตารางเก่าทิ้งก่อน
mydb=# drop table products;
แล้วสร้างใหม่
mydb=# create table products (
mydb(# product_id char(10) default mynext(),
mydb(# product_name varchar(255)
mydb(# );
CREATE
ดูโครงสร้างของตารางที่สร้างขึ้น
mydb=# \d products;
Table "products"
Column | Type | Modifiers
--------------+------------------------+------------------
product_id | character(10) | default mynext()
product_name | character varying(255) |
ให้สังเกตตางช่อง Modifiers ของฟีลด์แรกครับ จะมี default mynext() ด้วย
ลองใส่ข้อมูลแบบใช้ insert into
mydb=# insert into products (product_name) values('PostgreSQL');
INSERT 237802 1
จะสังเกตเห็นว่า ผมจะบอกแค่ฟีลด์ product_name อย่างเดียว เพราะฟีลด์ product_id จะมีการเพิ่มรหัสถัดไปให้เราเอง
ดูผลลัพธ์
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
(1 row)
ลองใส่ข้อมูลอีกที
mydb=# insert into products (product_name) values('MySQL');
INSERT 237803 1
ดูผลลัพธ์
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | MySQL
(2 rows)
ลองสร้างตารางเดิม แต่กำหนดให้ product_id เป็น Primary Key ด้วย
อย่าลืมลบตารางเก่าทิ้งก่อน
mydb=# create table products (
mydb(# product_id char(10) default mynext(),
mydb(# product_name varchar(255)
mydb(# ,
mydb(# primary key(product_id)
mydb(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'products_pkey' for table 'products'
CREATE
ผมเอา Primary Key มาต่อท้ายสุด ซึ่งจะสามารถระบุได้หลายๆ ฟีลด์
หรือจะเอา Primary Key มาต่อท้ายชื่อฟีลด์ก็ได้
mydb=# create table products (
product_id char(10) primary key default mynext(),
product_name varchar(255)
);
ลองดูตารางที่สร้างขึ้น
mydb=# \d products
Table "products"
Column | Type | Modifiers
--------------+------------------------+---------------------------
product_id | character(10) | not null default mynext()
product_name | character varying(255) |
Primary key: products_pkey
ลองเพิ่มข้อมูลโดยเรียกฟังก์ชัน myadd()
mydb=# select myadd('PostgreSQL');
myadd
-------
1
(1 row)
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
(1 row)
หรือใส่ข้อมูลในรูปแบบ insert into
mydb=# insert into products(product_name) values('MySQL');
INSERT 237810 1
จะสังเกตเห็นว่า เมื่อกำหนดค่า default ของฟีลด์ product_id แล้ว ไม่ต้องระบุสิ่งที่จะใส่ลงไปในฟีลด์นี้อีก
และถ้าเรา Link ตารางนี้ไปยัง Access เราก็แค่กรอกชื่อ product_name ลงไปอย่างเดียวเช่นกัน
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | MySQL
(2 rows)
อ่านวิธีการสร้างตารางในฐานข้อมูลหลายๆ ตัวเพิ่มเติมได้ที่ http://www.eh10.pwp.blueyonder.co.uk/gisq/howto/xcreatepostgres.htm
ใน MySQL ยังไม่มีความสามารถในการสร้างฟังก์ชันเองได้ครับ
PROD000001
PROD000004
PROD000003
…
ไปเรื่อยๆ
ถ้าเป็นใน Access ก็ทำได้โดยการเขียนโค้ดในฟอร์มช่วย จากตัวอย่างเก่าๆ ผมจะใช้ Double Click event ของ Text Box ของฟีลด์เป้าหมายช่วยใส่รหัสนี้ให้
แต่ถ้าเป็นใน PostgreSQL เราสามารถเขียน Function ไว้ในฐานข้อมูลช่วยได้
ความสามารถตรงนี้ยังไม่มีใน Access ครับ
ถ้าเป็น Postgres ผมจะนึกถึงฟังก์ชันขึ้นมาทันที่ เพื่อจะนำมาช่วยงานครั้งนี้
1. สร้างตารางเป้าหมายก่อน
ให้มี 2 ฟีลด์คือ product_id และ product_name
mydb=# create table products (
mydb(# product_id char(10),
mydb(# product_name varchar(255)
mydb(# );
CREATE
เปิดดูโครงการสร้างของตารางที่เพิ่งจะสร้างขึ้น
mydb=# \d products
Table "products"
Column | Type | Modifiers
--------------+-----------------------+-----------
product_id | character(10) |
product_name | character varying(80) |
2. ใช้ count(*) ช่วยในการนับจำนวนข้อมูลว่ามีกี่อันในตารางเป้าหมาย
ต้องสร้างเป็นฟังก์ชันช่วย ผมตั้งชื่อฟังก์ชัน mymax() โดยคืนค่าเป็น bigint
mydb=# create or replace function mymax() returns bigint as '
mydb'# select count(*) from products;
mydb'# ' language sql;
CREATE
ลองทดสอบ
mydb=# select mymax();
mymax
-------
0
(1 row)
3. สร้างอีกฟังก์ชันหนึ่ง เพื่อจัดรูปแบบของ product_id ให้เป็นตามต้องการคือ PRODXXXXXX
ผมจะใช้ฟังก์ชัน rpad(), ltrim() ของ Postgres ช่วย
(ดูฟังก์ชันที่จัดการ String ของ Postgres ได้ที่หน้า http://agserver.kku.ac.th/agro/html/functions-string.html)
mydb=# create or replace function mynext() returns text as '
mydb'# select rpad(''PROD'',10,ltrim(to_char(mymax()+1, ''000009''),'' ''));
mydb'# ' language SQL;
CREATE
ฟังก์ชันข้างบนชื่อ mynext() คืนค่ามาเป็น text
ทดสอบ
mydb=# select mynext();
mynext
------------
PROD000001
(1 row)
4. ลองเพิ่มข้อมูล ใช้ insert into ชื่อตารางเป้าหมาย ค่าที่จะใส่ลงไป
mydb=# insert into products values (
mydb'# mynext(), ‘Postgres’);
เพื่อให้การเพิ่มข้อมูลของเราทำได้เร็วยิ่งขึ้น ลองเขียนฟังก์ชันสำหรับเพิ่มข้อมูลขึ้นมาอีกอัน
mydb=# create or replace function myadd(text) returns integer as '
mydb'# insert into products values (
mydb'# mynext(), $1);
mydb'# select 1;
mydb'# ' language sql;
CREATE
ฟังก์ชันนี้ชื่อ myadd() รับค่าเป็น text แล้วคืนค่าเป็น integer
ลองเพิ่มข้อมูลโดยใช้ฟังก์ชันข้างต้น
mydb=# select myadd('Oracle 8i');
myadd
-------
1
(1 row)
ลองวิวดูข้อมูลที่เพิ่งใส่ลงไป
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | Oracle 8i
(2 rows)
จะเห็นว่ามี product_id ลำดับถัดไปให้เองอัตโนมัติ
5. ลองใช้ default value ของฟีลด์เป้าหมายช่วยในการเพิ่มข้อมูลก็ได้
ผมใช้ default mynext() เป็นค่า Default Value ของฟีลด์ชื่อ product_id
ลบตารางเก่าทิ้งก่อน
mydb=# drop table products;
แล้วสร้างใหม่
mydb=# create table products (
mydb(# product_id char(10) default mynext(),
mydb(# product_name varchar(255)
mydb(# );
CREATE
ดูโครงสร้างของตารางที่สร้างขึ้น
mydb=# \d products;
Table "products"
Column | Type | Modifiers
--------------+------------------------+------------------
product_id | character(10) | default mynext()
product_name | character varying(255) |
ให้สังเกตตางช่อง Modifiers ของฟีลด์แรกครับ จะมี default mynext() ด้วย
ลองใส่ข้อมูลแบบใช้ insert into
mydb=# insert into products (product_name) values('PostgreSQL');
INSERT 237802 1
จะสังเกตเห็นว่า ผมจะบอกแค่ฟีลด์ product_name อย่างเดียว เพราะฟีลด์ product_id จะมีการเพิ่มรหัสถัดไปให้เราเอง
ดูผลลัพธ์
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
(1 row)
ลองใส่ข้อมูลอีกที
mydb=# insert into products (product_name) values('MySQL');
INSERT 237803 1
ดูผลลัพธ์
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | MySQL
(2 rows)
ลองสร้างตารางเดิม แต่กำหนดให้ product_id เป็น Primary Key ด้วย
อย่าลืมลบตารางเก่าทิ้งก่อน
mydb=# create table products (
mydb(# product_id char(10) default mynext(),
mydb(# product_name varchar(255)
mydb(# ,
mydb(# primary key(product_id)
mydb(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'products_pkey' for table 'products'
CREATE
ผมเอา Primary Key มาต่อท้ายสุด ซึ่งจะสามารถระบุได้หลายๆ ฟีลด์
หรือจะเอา Primary Key มาต่อท้ายชื่อฟีลด์ก็ได้
mydb=# create table products (
product_id char(10) primary key default mynext(),
product_name varchar(255)
);
ลองดูตารางที่สร้างขึ้น
mydb=# \d products
Table "products"
Column | Type | Modifiers
--------------+------------------------+---------------------------
product_id | character(10) | not null default mynext()
product_name | character varying(255) |
Primary key: products_pkey
ลองเพิ่มข้อมูลโดยเรียกฟังก์ชัน myadd()
mydb=# select myadd('PostgreSQL');
myadd
-------
1
(1 row)
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
(1 row)
หรือใส่ข้อมูลในรูปแบบ insert into
mydb=# insert into products(product_name) values('MySQL');
INSERT 237810 1
จะสังเกตเห็นว่า เมื่อกำหนดค่า default ของฟีลด์ product_id แล้ว ไม่ต้องระบุสิ่งที่จะใส่ลงไปในฟีลด์นี้อีก
และถ้าเรา Link ตารางนี้ไปยัง Access เราก็แค่กรอกชื่อ product_name ลงไปอย่างเดียวเช่นกัน
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | MySQL
(2 rows)
อ่านวิธีการสร้างตารางในฐานข้อมูลหลายๆ ตัวเพิ่มเติมได้ที่ http://www.eh10.pwp.blueyonder.co.uk/gisq/howto/xcreatepostgres.htm
ใน MySQL ยังไม่มีความสามารถในการสร้างฟังก์ชันเองได้ครับ
8 Reply in this Topic. Dispaly 1 pages and you are on page number 1
2 @R04588
ถ้าเป็นใน MS SQL Server จะทำอย่างไร
ก็เข้าไปสร้างฟังก์ชันเหมือนกันครับ
สร้างฟังก์ชัน mymax() ขึ้นมาก่อน
create function mymax()
returns char(10)
as
begin
declare @chrText varchar(10)
declare @chrText2 varchar(10)
declare @intLen int
set @intLen = (select convert(int,(select max(right(product_id,6)) from products))+1)
set @chrText = replicate('0',6-len(@intLen))+ ltrim(str(@intLen))
if (select count(*) from products)= 0
set @chrText2 = ('PROD000001')
if (select count(*) from products)> 0
set @chrText2 = ('PROD'+ @chrText)
return @chrText2
end
จะสังเกตเห็นว่ามีการใช้ฟังก์ชัน replicate() ซึ่งจะเทียบได้กับ lpad() ของ Postgres หรือ Strint() ของ Access
และ convert() ในการแปลง data type ซึ่งจะเหมือน CInt() ของ Access หรือ to_number() ของ Postgres
อันดับต่อไปสร้างตาราง products ขึ้นมา
create table products
(
product_id char(10) primary key default dbo.mymax(),
product_name varchar(254)
)
จะใช้ syntax คล้ายๆ กันกับ Postgres แต่ให้สังเกตการอ้างชื่อฟังก์ชัน mymax() ครับ
ลองใส่ข้อมูล
insert into products(product_name) values('Postgres')
แล้วลองวิวดู
select * from products
ถ้าต้องการที่จะสร้างอีกเป็น stored procedure เพื่อให้การคีย์ข้อมูลสะดวก ก็สามารถทำได้ ลองสร้างดูเองนะครับ
ก็เข้าไปสร้างฟังก์ชันเหมือนกันครับ
สร้างฟังก์ชัน mymax() ขึ้นมาก่อน
create function mymax()
returns char(10)
as
begin
declare @chrText varchar(10)
declare @chrText2 varchar(10)
declare @intLen int
set @intLen = (select convert(int,(select max(right(product_id,6)) from products))+1)
set @chrText = replicate('0',6-len(@intLen))+ ltrim(str(@intLen))
if (select count(*) from products)= 0
set @chrText2 = ('PROD000001')
if (select count(*) from products)> 0
set @chrText2 = ('PROD'+ @chrText)
return @chrText2
end
จะสังเกตเห็นว่ามีการใช้ฟังก์ชัน replicate() ซึ่งจะเทียบได้กับ lpad() ของ Postgres หรือ Strint() ของ Access
และ convert() ในการแปลง data type ซึ่งจะเหมือน CInt() ของ Access หรือ to_number() ของ Postgres
อันดับต่อไปสร้างตาราง products ขึ้นมา
create table products
(
product_id char(10) primary key default dbo.mymax(),
product_name varchar(254)
)
จะใช้ syntax คล้ายๆ กันกับ Postgres แต่ให้สังเกตการอ้างชื่อฟังก์ชัน mymax() ครับ
ลองใส่ข้อมูล
insert into products(product_name) values('Postgres')
แล้วลองวิวดู
select * from products
ถ้าต้องการที่จะสร้างอีกเป็น stored procedure เพื่อให้การคีย์ข้อมูลสะดวก ก็สามารถทำได้ ลองสร้างดูเองนะครับ
3 @R04589
มาสร้าง Store Procedure กันต่อดีกว่า
create procedure myadd
@varText varchar(254)
as
Insert Into products(product_name) values(@varText)
go
เวลาเรียกใช้งาน
exec myadd 'MS SQL Server'
create procedure myadd
@varText varchar(254)
as
Insert Into products(product_name) values(@varText)
go
เวลาเรียกใช้งาน
exec myadd 'MS SQL Server'
4 @R04594
ไม่ทราบว่า d/l PostgreSQL ได้ที่ไหนครับ?
สนใจอยากจะศึกษาครับ
สนใจอยากจะศึกษาครับ
5 @R04596
อีกครั้งน่ะครับ ไม่ทราบว่าผมใช้ OS เป็น winMe winXP สามารถใช้PostgreSQL ได้หรือไม่ครับ
6 @R04597
ลองดูกระทู้ http://www.thai-access.com/suphap.php?topic_id=1732 ครับ พร้อมวิธีการติดตั้ง
ผมลองกับ Windows 2000 ครับ แต่คิดว่า XP น่าจะเล่นได้ ไม่แน่ใจว่าจะเล่นกับ WinMe ได้หรือเปล่าครับ
ผมลองกับ Windows 2000 ครับ แต่คิดว่า XP น่าจะเล่นได้ ไม่แน่ใจว่าจะเล่นกับ WinMe ได้หรือเปล่าครับ
7 @R04612
มาลองดูใน Oracle บ้างครับ
1. สร้างตารางเหมือนกัน
SQL> create table products
(
product_id char(10) primary key,
product_name varchar(20)
)
;
Table created.
2. ลองดูรายละเอียดของตารางที่เพิ่งจะสร้างขึ้น
SQL> describe products;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
PRODUCT_ID NOT NULL CHAR(10)
PRODUCT_NAME VARCHAR2(254)
จะเห็นว่าใช้คำสั่ง describe คล้ายๆ MySQL (หรือ MySQL เอาของเขาไป?)
3. มาสร้าง stored procedure ซึ่งจะเป็นตัวสำคัญ
CREATE OR REPLACE PROCEDURE my_add (
my_name IN VARCHAR)
AS
my_Text CHAR(10);
my_Count NUMBER;
my_Max CHAR(6);
BEGIN
select count(*)
into my_count
from products;
select lpad(to_number(substr(max(product_id),5)+1),6,'0')
into my_MAX
from products;
select concat('PROD',my_Max)
into my_Text
from dual;
IF (my_count > 0) THEN
insert into products values(my_Text, my_name);
END IF;
IF (my_count = 0) THEN
insert into products values('PROD000001', my_name);
END IF;
END;
ให้นำโค้ดข้างต้นไป paste ลงใน SQL * Pluse
SQL> CREATE OR REPLACE PROCEDURE my_add (
2 my_name IN VARCHAR)
3 AS
4 my_Text CHAR(10);
5 my_Count NUMBER;
6 my_Max CHAR(6);
7
8 BEGIN
9
10 select count(*)
11 into my_count
12 from products;
13
14 select lpad(to_number(substr(max(product_id),5)+1),6,'0')
15 into my_MAX
16 from products;
17
18 select concat('PROD',my_Max)
19 into my_Text
20 from dual;
21
22 IF (my_count > 0) THEN
23 insert into products values(my_Text, my_name);
24 END IF;
25 IF (my_count = 0) THEN
26 insert into products values('PROD000001', my_name);
27 END IF;
28 END;
29 /
ให้สังเกตว่า จะต้องปิดด้วย / เพื่อบอกว่าจบด้วย ไม่งั้นมันจะไม่ยอมสร้างให้สักที่ครับ
ถ้าไม่มีอะไรผิดพลาด เราจะเห็นข้อความนี้
Procedure created.
4. ใส่ข้อมูลแรกดู
SQL> execute my_add('Oracle');
จะเห็นว่า Oracle ใช้คำสั่ง execute แต่ SQL Server ใช้ exec
พร้อมกับเห็นข้อความนี้
PL/SQL procedure successfully completed.
5. ลองดูข้อมูลที่ใส่ลงไป
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
ใส่อีกอัน
SQL> execute my_add('PostgreSQL');
PL/SQL procedure successfully completed.
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
ใน Oracle จะมีฟังก์ชันจัดการ string อยู่หลายๆ ตัว
ตัวแรกที่ผมใช้คือ substr() ซึ่งจะทำงานคล้ายกับ mid() ของ Access ครับ
SQL> select substr(product_id,5) from products;
SUBSTR
------
000001
000002
และ length() จะเหมือน len() ของ Access/SQL Server
SQL> select length(product_id) from products;
LENGTH(PRODUCT_ID)
------------------
10
10
6. คราวนี้มาถึงการใส่ข้อมูลกระโดดไปลำดับที่สูงๆ ดูบ้าง
SQL> insert into products values('PROD001234','Supap Chaiya');
1 row created.
ลองดูผลลัพธ์
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
PROD001234 Supap Chaiya
และลองใส่ผ่าน stored procedure ที่เราได้สร้างขึ้นครับ
SQL> execute my_add('Basic Eng');
PL/SQL procedure successfully completed.
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
PROD001234 Supap Chaiya
PROD001235 Basic Eng
จะเห็นว่ามันเพิ่มลำดับได้ถูกต้อง
1. สร้างตารางเหมือนกัน
SQL> create table products
(
product_id char(10) primary key,
product_name varchar(20)
)
;
Table created.
2. ลองดูรายละเอียดของตารางที่เพิ่งจะสร้างขึ้น
SQL> describe products;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
PRODUCT_ID NOT NULL CHAR(10)
PRODUCT_NAME VARCHAR2(254)
จะเห็นว่าใช้คำสั่ง describe คล้ายๆ MySQL (หรือ MySQL เอาของเขาไป?)
3. มาสร้าง stored procedure ซึ่งจะเป็นตัวสำคัญ
CREATE OR REPLACE PROCEDURE my_add (
my_name IN VARCHAR)
AS
my_Text CHAR(10);
my_Count NUMBER;
my_Max CHAR(6);
BEGIN
select count(*)
into my_count
from products;
select lpad(to_number(substr(max(product_id),5)+1),6,'0')
into my_MAX
from products;
select concat('PROD',my_Max)
into my_Text
from dual;
IF (my_count > 0) THEN
insert into products values(my_Text, my_name);
END IF;
IF (my_count = 0) THEN
insert into products values('PROD000001', my_name);
END IF;
END;
ให้นำโค้ดข้างต้นไป paste ลงใน SQL * Pluse
SQL> CREATE OR REPLACE PROCEDURE my_add (
2 my_name IN VARCHAR)
3 AS
4 my_Text CHAR(10);
5 my_Count NUMBER;
6 my_Max CHAR(6);
7
8 BEGIN
9
10 select count(*)
11 into my_count
12 from products;
13
14 select lpad(to_number(substr(max(product_id),5)+1),6,'0')
15 into my_MAX
16 from products;
17
18 select concat('PROD',my_Max)
19 into my_Text
20 from dual;
21
22 IF (my_count > 0) THEN
23 insert into products values(my_Text, my_name);
24 END IF;
25 IF (my_count = 0) THEN
26 insert into products values('PROD000001', my_name);
27 END IF;
28 END;
29 /
ให้สังเกตว่า จะต้องปิดด้วย / เพื่อบอกว่าจบด้วย ไม่งั้นมันจะไม่ยอมสร้างให้สักที่ครับ
ถ้าไม่มีอะไรผิดพลาด เราจะเห็นข้อความนี้
Procedure created.
4. ใส่ข้อมูลแรกดู
SQL> execute my_add('Oracle');
จะเห็นว่า Oracle ใช้คำสั่ง execute แต่ SQL Server ใช้ exec
พร้อมกับเห็นข้อความนี้
PL/SQL procedure successfully completed.
5. ลองดูข้อมูลที่ใส่ลงไป
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
ใส่อีกอัน
SQL> execute my_add('PostgreSQL');
PL/SQL procedure successfully completed.
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
ใน Oracle จะมีฟังก์ชันจัดการ string อยู่หลายๆ ตัว
ตัวแรกที่ผมใช้คือ substr() ซึ่งจะทำงานคล้ายกับ mid() ของ Access ครับ
SQL> select substr(product_id,5) from products;
SUBSTR
------
000001
000002
และ length() จะเหมือน len() ของ Access/SQL Server
SQL> select length(product_id) from products;
LENGTH(PRODUCT_ID)
------------------
10
10
6. คราวนี้มาถึงการใส่ข้อมูลกระโดดไปลำดับที่สูงๆ ดูบ้าง
SQL> insert into products values('PROD001234','Supap Chaiya');
1 row created.
ลองดูผลลัพธ์
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
PROD001234 Supap Chaiya
และลองใส่ผ่าน stored procedure ที่เราได้สร้างขึ้นครับ
SQL> execute my_add('Basic Eng');
PL/SQL procedure successfully completed.
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
PROD001234 Supap Chaiya
PROD001235 Basic Eng
จะเห็นว่ามันเพิ่มลำดับได้ถูกต้อง
8 @R04613
มาถึงทีของ Access ของเราบ้าง
1. สร้างตารางก่อน
Sub createtb()
CurrentDb.Execute "create table products " _
& "(product_id text(10) CONSTRAINT MyCon Primary Key, product_name text(254))"
End Sub
เมื่อเขียนเสร็จให้กด F5 ได้เลย
แล้วไปดูในส่วนของ Tables ว่ามีการสร้างตาราง products ให้หรือยัง
2. สร้างฟังก์ชัน เพื่อใช้ในการใส่ข้อมูล
Function myadd(strName As String)
Dim strID As String, lngCount As Long
lngCount = DCount("*", "products")
If lngCount = 0 Then
CurrentDb.Execute "insert into products values('PROD000001','" & strName & "')"
Else
strID = Format(DMax("val(mid([product_id],6)) + 1", "products"), "000000")
strID = "PROD" & strID
CurrentDb.Execute "insert into products values('" & strID & "','" & strName & "')"
End If
End Function
คงไม่ต้องบรรยายกันมาก เพราะคุ้นเคยกับคำสั่งต่างๆ ดีอยู่แล้ว
3. ลองใส่ข้อมูลแรก
ให้กด Ctrl + G เพื่อเปิดหน้า Debug Window
แล้วพิมพ์
? myadd("MS Access")
แล้วให้กด Enter
ไปเปิดดูตาราง products ว่ามีการเพิ่มข้อมูลให้หรือยังครับ
4. ใส่ข้อมูลแบบกระโดด
ให้พิมพ์
currentdb.execute("insert into products values('PROD001234','Supap Chaiya')")
ลงไป แล้วกด Enter
กลับไปดูตาราง
แล้วก็ให้เพิ่มข้อมูลไปอีก 2 รายการดังนี้
? myadd("PostgreSQL")
? myadd("Oracle")
เปิดดูตารางจะได้
product_id product_name
PROD000001 MS Access
PROD001234 Supap Chaiya
PROD001235 PostgreSQL
PROD001236 Oracle
พระเอกต้องมาที่หลังครับ
1. สร้างตารางก่อน
Sub createtb()
CurrentDb.Execute "create table products " _
& "(product_id text(10) CONSTRAINT MyCon Primary Key, product_name text(254))"
End Sub
เมื่อเขียนเสร็จให้กด F5 ได้เลย
แล้วไปดูในส่วนของ Tables ว่ามีการสร้างตาราง products ให้หรือยัง
2. สร้างฟังก์ชัน เพื่อใช้ในการใส่ข้อมูล
Function myadd(strName As String)
Dim strID As String, lngCount As Long
lngCount = DCount("*", "products")
If lngCount = 0 Then
CurrentDb.Execute "insert into products values('PROD000001','" & strName & "')"
Else
strID = Format(DMax("val(mid([product_id],6)) + 1", "products"), "000000")
strID = "PROD" & strID
CurrentDb.Execute "insert into products values('" & strID & "','" & strName & "')"
End If
End Function
คงไม่ต้องบรรยายกันมาก เพราะคุ้นเคยกับคำสั่งต่างๆ ดีอยู่แล้ว
3. ลองใส่ข้อมูลแรก
ให้กด Ctrl + G เพื่อเปิดหน้า Debug Window
แล้วพิมพ์
? myadd("MS Access")
แล้วให้กด Enter
ไปเปิดดูตาราง products ว่ามีการเพิ่มข้อมูลให้หรือยังครับ
4. ใส่ข้อมูลแบบกระโดด
ให้พิมพ์
currentdb.execute("insert into products values('PROD001234','Supap Chaiya')")
ลงไป แล้วกด Enter
กลับไปดูตาราง
แล้วก็ให้เพิ่มข้อมูลไปอีก 2 รายการดังนี้
? myadd("PostgreSQL")
? myadd("Oracle")
เปิดดูตารางจะได้
product_id product_name
PROD000001 MS Access
PROD001234 Supap Chaiya
PROD001235 PostgreSQL
PROD001236 Oracle
พระเอกต้องมาที่หลังครับ
Time: 0.0563s
ถ้ามีการลบข้อมูลใดออก แล้วสร้างเพิ่ม product_id จะไปซ้ำกับค่าในฐานข้อมูลที่มีอยู่แล้ว
เช่น ถ้าข้อมูลที่ใส่แล้วเป็นอย่างนี้
PROD000001 | PostgreSQL
PROD000002 | MySQL
แต่บังเอิญว่า ต้องมีการลบข้อมูลลำดับที่ 1 ออก
จะเหลือ
PROD000002 | MySQL
เมื่อมีการเพิ่มข้อมูลลำดับต่อไป จะได้รหัสซ้ำกับรหัสเดิม จะต้องเกิด Error แน่นอน
ฉะนั้นการใช้ count(*) จะไม่ได้ช่วยอะไรเลยครับ
ผมมาคิดดูว่าน่าจะใช้ Select Case หรือ If Then ช่วย แต่ยังนึกไม่ออกว่าจะทำอย่างไร
ผมเลยแก้ปัญหาแบบลูกทุ่งไปก่อน โดยการใช้ Case Then Else End ช่วยครับ
อันดับแรกต้องเขียนฟังก์ชันขึ้นมาอีกอัน โดยใช้ Max() ช่วย เพื่อใช้ในกรณีที่มีการใส่ข้อมูลลงไปบ้างแล้ว
ผมสร้าง mymax1() อย่างนี้ครับ
mydb=# create or replace function mymax1() returns numeric as '
mydb'# select max(to_number(product_id,''9999999999'')) from products;
mydb'# ' language sql;
แล้วสร้าง mymax2() เพื่อใช้ในกรณีที่ยังไม่มีข้อมูลอยู่เลย
mydb=# create or replace function mymax2() returns bigint as '
mydb'# select count(*) from products;
mydb'# ' language sql;
แล้วค่อยไปแก้ mymax() ตัวเดิมที่เคยสร้างไว้แล้ว ให้เป็น
mydb=# create or replace function mymax() returns numeric as '
mydb'# select case mymax2() when 0 then mymax2() else mymax1() end;
mydb'# ' language sql;
ไม่รู้ว่าจะสามารถนำทั้ง 3 ฟังก์ชันนี้มารวมเป็นหนึ่งเดียวจะได้หรือไม่ คอยติดตามตอนต่อไปนะครับ