|
|
|
|
|
|
| |
You will often need to create auto-incrementing fields in your Oracle tables, most usually in order to generate a unique number for a table's primary key. |
|
Although there is no inbuilt support for this in Oracle, it is a relatively simple process to make this behaviour happen. You need to use Oracle sequences to generate the numbers, and triggers to automagically give the ID field a value whenever you insert a row into the database.
|
|
We'll use the tried and tested emp table as an example, created with: |
|
create table emp
(
id number(5),
name varchar2(20),
surname varchar2(30)
);
|
|
|
|
It's a bit simple, but we're only interested in one field in this case, the id field.
|
|
The next step is to create a sequence: |
|
create sequence seq_emp_id
start with 1
increment by 1;
/
|
|
|
Next, the trigger: |
|
create or replace trigger trig_emp
before insert on emp
for each row
begin
select seq_emp_id.nextval into :new.id from dual;
end;
/
|
|
|
Once created, this trigger will be activated whenever a row is inserted into the table.
|
|
The select statement may look daunting to everyone but Oracle gurus, but you should be able to understand the gist of what is going on. The next value is pulled out of the sequence and this value is entered into the id field.
|
|
Now, when you insert a couple of rows from PHP: |
|
$sql1 = "insert into emp (name, surname) values ('Ali', 'G' )";
$sql2 = "insert into emp (name, surname) values ('Keith', 'Chegwin' )";
$stmt = OCIParse( $conn, $sql1 );
OCIExecute( $stmt )
$stmt = OCIParse( $conn, $sql2 );
OCIExecute( $stmt )
|
|
|
and then issue a select statement, you will see that the id fields have been assigned a unique number without you having to explicitly ask it to in your PHP code:
|
|
SQL> select * from emp order by id;
|
|
|
ID NAME SURNAME
---------- -------------------- ------------------------------
1 Ali G
2 Keith Chegwin
|
|
|
|
|
| Abstracting Oracle Connectivity with PHP and OCI8
Categories : PHP, Databases, OCI8, Oracle | | | Descriptions of Common Data Types
Categories : MySQL, Databases, PHP, PHP options/info, General | | | Simple Connection to Oracle with PHP
Categories : PHP, Oracle, Databases | | | Saving Images in MySQL
Categories : MySQL, PHP, Graphics, Databases | | | Honey, I Shrunk My Website
Categories : PHP, PHP options/info, Site Planning, Other | | | PHP 101 Part 8 of 15 : Databases and Other Animals
Categories : PHP, Beginner Guides, Databases | | | Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation | | | Custom MySQL-functions
Categories : Databases, MySQL, PHP, PHP Functions | | | Referer Statistics
Categories : PHP, MySQL, HTTP, Databases | | | PHP 101 Part 9 of 15 : SQLite My Fire!
Categories : PHP, Beginner Guides, Databases, SQLite | | | Simple Connection to Sybase with PHP
Categories : PHP, Sybase, Databases | | | User identification using cookies in PHP and MySQL
Categories : PHP, Databases, MySQL, Cookies | | | Simple ODBC Connections with PHP
Categories : PHP, ODBC, Databases | | | Multicolumn Output from a Database with PHP
Categories : PHP, Databases, HTML and PHP, MySQL | | | Simple Connection to Informix with PHP
Categories : PHP, Informix, Databases | |
| |
|
|
