PHP Oracle
-
Upload
nur-hidayat -
Category
Technology
-
view
5.076 -
download
5
description
Transcript of PHP Oracle
PHP+Oracle
PHP IndonesiaSaturday, 5th May 2102
Presented by:Nur Hidayat
Who Am I?
Sehari-hari sebagai Software Developer di ESQ Leadership Center
Di waktu luang mencoba mencari segenggam berlian untuk istri dan anak-anak
Agenda
Introduction to Oracle PHP Oracle Extensions Instalasi Oracle Database XE Zend Server VirtualBox Appliance Oracle Database Administration Interacting with Oracle Database XE Programming PHP+Oracle
Introduction to Oracle
1977,Larry Ellison founded Software Development Laboratories (SDL) Changed name in 1979 to Relational Software Inc. (RSI), 1979 created the first commercial SQL-based relational database. Changed name in 1983 to Oracle Corporation. 1983, Oracle 3 the first relational database to run on mainframes,
minicomputers, and PCs 1988, PL/SQL, the first built-in procedural language with very high
influence from C and Ada. 1992, Oracle7, released in 1992, introduced PL/SQL stored procedures
and triggers. 1995, the first to 64-bit database. 1999, the database with XML support 2001, Oracle Real Application Clusters (Oracle RAC) 2003, enterprise grid computing environment. In late 2005, released its first full-featured, 100 percent free database
server, Oracle Database XE. 2007, Oracle 11g, improved Manageability, diagnosability, and availability
See Also: http://www.oracle.com/technetwork/issue-archive/2007/07-jul/ o4730-090772.html for an article summarizing the evolution of Oracle Database
Oracle Database Family
Express Edition Includes all feature available in Enterprise
Edition Limited to single CPU Limited to 1 GB or RAM Limited to 4 GB (10g) or 11 GB (11g) of data Very simple installation
Personal Edition Includes all feature available in Enterprise
Edition Limited to 1 user
Oracle Database Family
Standard Edition One Limited to 2 CPU Minus Oracle RAC
Standard Edition Limited to 4 CPU Support Oracle RAC
Enterprise Edition No CPU Limitation Advanced High Availability (Flashback Table,
Flashback Database, Data Guard, etc.)
Other Oracle Products
Times Ten, in memory database Berkeley DB, embedded database MySQL, leading free open source
database Application Server, web server which
integrate seamlessly with oracle database
E-Business Suite PeopleSoft JD Edwards .... and many more
PHP Oracle Extensions
Oracle Extension, This extension was designed for Oracle 7 and should be avoided since it uses a deprecated version of the Oracle API that will not be available in future releases.
ODBC within Windows, while ODBC provides some connection pooling and other built-in features, it lacks access to many of Oracle’s capabilities such as the ability to store large objects (LOBs).
OCI8 Extension, this extension supports most of Oracle’s Oracle Call Interface (OCI).
PDO Extension. This portable database API makes it easy to change your database without changing a lot of your code.
Database Abstraction Libraries ADOdb PEAR DB PEAR MDB2
Instalasi Oracle Database XE
Pilihan Web Server XAMPP Zend Server
Instalasi Oracle XE di Windows
Zend Server VirtualBox Appliance
Import Zend Appliance to VirtualBox Username: root Password: zend1234
Konfigurasi Zend Server http://localhost:10081/
Konfigurasi Oracle Database XE /etc/init.d/oracle-xe configure
Oracle Database Administration
Starting and Stopping Oracle Database
Database and Instance Oracle Storage Structure Oracle Memory Structure Schemas and Users
Starting and Stopping
Listener The database listener is an Oracle Net program
that listens for and responds to requests to the database.
Database The database is another process that runs in
memory, and needs to be started before Oracle Net can handle connection requests to it.
Requirements, user must included in dba group on Linux, or ORA_DBA on Windows
Starting and Stopping
On Linux Starting▪ From Gnome: Select Applications > Oracle Database
10g Express Edition > Start Database.▪ From KDE: Select K Menu > Oracle Database 10g
Express Edition > Start Database.▪ From linux shell: # /etc/init.d/oracle-xe restart
Stopping▪ From Gnome: Select Applications > Oracle Database
10g Express Edition > Stop Database.▪ From KDE: Select K Menu > Oracle Database 10g
Express Edition > Stop Database.▪ From linux shell: # /etc/init.d/oracle-xe stop
Starting and Stopping
On Windows Starting
▪ From Menu: Start > Oracle Database 10g Express Edition > Start Database.
▪ From Control Panel: Start > Settings > Control Panel > Administrative Tools > Services, and select the OracleXETNSListener service. Right click on the Listener service, and select Start.
▪ From Control Panel: Start > Settings > Control Panel > Administrative Tools > Services, and select the OracleServiceXE service. Right click on the Listener service, and select Start.
Stopping▪ From Menu: Start > Oracle Database 10g Express Edition > Stop
Database.▪ From Control Panel: Start > Settings > Control Panel >
Administrative Tools > Services, and select the OracleXETNSListener service. Right click on the Listener service, and select Stop.
▪ From Control Panel: Start > Settings > Control Panel > Administrative Tools > Services, and select the OracleServiceXE service. Right click on the Listener service, and select Stop.
Database and Instance
An Oracle database server consists of a database and at least one database instance
Database A database is a set of files,
located on disk, that store data. These files can exist independently of a database instance.
Database instance An instance is a set of memory
structures that manage database files. The instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. An instance can exist independently of database files.
Database and Instance
Database and Instance
Oracle Storage Structure
Physical Storage Structures Data files ▪ Every Oracle database has one or more physical
data files, which contain all the database data. Control files ▪ Every Oracle database has a control file. Metadata
specifying the physical structure of the database. Online redo log files▪ Every Oracle Database has an online redo log. An
online redo log is made up of redo entries (also called redo records), which record all changes made to data.
Oracle Storage Structures
Logical Storage Structures Data blocks ▪ At the finest level of granularity, Oracle Database data is stored
in data blocks. One data block corresponds to a specific number of bytes on disk.
Extents ▪ An extent is a specific number of logically contiguous data
blocks, obtained in a single allocation, used to store a specific type of information.
Segments ▪ A segment is a set of extents allocated for a user object (for
example, a table or index), undo data, or temporary data. Tablespaces ▪ A database is divided into logical storage units called
tablespaces. A tablespace is the logical container for a segment. Each tablespace contains at least one data file.
Oracle Storage Structure
Oracle Database Processes Client Processes
to run the software code of an application program or an Oracle tool. Most environments have separate computers for client processes.
Server Processes These processes communicate with client processes
and interact with Oracle Database to fulfill requests. Background Process
Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.
Oracle Memory Structure System Global Area (SGA)
The SGA is a group of shared memory structures that contain data and control information for one database instance. Examples of SGA components include cached data blocks and shared SQL areas.
Program Global Areas (PGA) A PGA is a memory region that contain data and
control information for a server or background process. Access to the PGA is exclusive to the process. Each server process and background process has its own PGA.
Schemas and Users
A database schema is a collection of logical data structures, or schema objects.
A database schema is owned by a database user and has the same name as the user name.
Schema objects are user-created structures that directly refer to the data in the database.
The database supports many types of schema objects, the most important of which are tables and indexes.
Interacting with Oracle Database
Command Line Interface SQL Developer Application Express
Programming PHP+Oracle
Connecting to Oracle Database Executing SQL Statements Using PL/SQL, Stored Procedure and
Triggers Using Oracle LOB Database Transactions
Oracle Rule of Thumb
Use single SQL statement whenever possible
If not possible, use PL/SQL or stored procedure
If not possible, use Java or other programming language
If still not possible, rethink why you want to do it
Connecting to Oracle Database Oracle Connection Types
Standard Connections▪ For basic connection to Oracle use PHP’s oci_connect() call:▪ $c = oci_connect($username, $password, $dbname);
▪ You can call oci_connect() more than once in a script. If you do this and use the same username and database name, then you get a pointer to the original connection.
Multiple Unique Connections▪ To get a totally independent connection use oci_new_connect():▪ $c = oci_new_connect($username, $password, $dbname);
▪ Each connection is separate from any other. This lets you have more than one database session open at the same time. This can be useful when you want to do database operations independently from each other.
Persistent Connections▪ Persistent connections can be made with oci_pconnect():▪ $c = oci_pconnect($username, $password, $dbname);
▪ Persistent connections are not automatically closed at the end of a PHP script this makes oci_pconnect() fast for frequently used web applications. Reconnection does not require re-authentication to the database.
Connecting to Oracle Database
Connecting to Oracle Database
Oracle Database Name Connection Identifiers The $dbname connection identifier is the
name of the local or remote database that you want to attach to.
Connection Identifiers can be one of: An Easy Connect string A Connect Descriptor string A Connect Name
Connection Identifiers
An Easy Connect string The Easy Connect string is JDBC-like. The
Oracle 10g syntax is:▪ [//]host_name[:port][/service_name]
Using Oracle 11g libraries, the enhanced 11g syntax can be used:▪ [//]host_name[:port][/service_name][:server_type]
[/instance_name]
$c = oci_connect('hr', 'welcome', 'localhost/XE');$c = oci_connect(scott', ‘tiger', '127.0.0.1:1521/XE');
Connection Identifiers
A Connect Descriptor string The full Oracle Net connect descriptor string gives total
flexibility over the connection. The syntax can be more complex than this example,
depending on the database and Oracle Net features used. For example, by using the full syntax, you can enable features like load balancing and tweak packet sizes.
The Easy Connect syntax does not allow this flexibility.
Connection Identifiers
A Connect Name A stored Connect Descriptor string In PHP you would use the connect name MYA to connect
to the database:▪ $c = oci_connect($username, $password, 'MYA');
PHP needs to be able to find the tnsnames.ora file to resolve the MYA name. The directory paths that Oracle searches for tnsnames.ora depend on your operating system
Closing Oracle Connection
oci_close() The oci_close() function works by
reference counting. Only when all PHP references to the database connection are finished will it actually be closed and database resources freed.
Demo on Close.php
Executing SQL Statements
The possible steps are: Parse: Prepares a statement for execution. Bind: Optionally lets you bind data values, for
example, in the WHERE clause, for better performance and security.
Define: Optional step allowing you to specify which PHP variables will hold the results. This is not commonly used.
Execute: The database processes the statement and buffers any results.
Fetch: Gets any query results back from the database.
Executing SQL Statement Modified FizzBuzz Problem, solved using single
SQL statement
Using PL/SQL
Sample code using PL/SQL function to generate composite field
Function called using SELECT statement
Using Oracle LOB
Sample code for inserting LOB data into Oracle database
Sample code for etrieving LOB data from Oracle database
Database Transactions
Sample code, increase one salary and decrease another as a single transaction.
Resources +Tools
Oracle and Zend @Zend http://www.zend.com/en/company/partners/strategic/zend-oracle
PHP Developer Center @Oracle http://www.oracle.com/technetwork/topics/php/whatsnew/index.html
Oracle Database XE 11gR2 http://www.oracle.com/technetwork/products/express-edition/downloads
/index.html