We recommend

Scriptcase PHP Rapid Application Development
PHP + Oracle Rss

URL Manipulation

Posted on : 22-04-2013 | By : Gabriela Moraes | In : Uncategorized

0

Introduction

In web development,  is very common to use links. It is also common that the links need to be manipulated to add or remove parameters. In this post, we will see how to manipulate a link and its parameters so easy and safe.

Breaking a URL in parts

When a programmer doesn’t know the capabilities of PHP to handle URLs, it is common venture to manipulate the string with the URL directly, making operations such as checking if there is ‘?’ in the URL and get the content to the left or right, etc..

The main function to “parse” a URL and break it into parts is parse_url. This function can be used for two purposes: to get all the parts of a URL or to obtain a specific part of a URL (for example, the protocol, the domain, the query string, etc.). For all parts, just pass the URL as a parameter and don’t enter the second parameter:

For a specific part, just pass the second parameter of the function, which specifies which part is desirable. This parameter can have one of the constants:

PHP_URL_SCHEME
PHP_URL_HOST
PHP_URL_PORT
PHP_URL_USER
PHP_URL_PASS
PHP_URL_PATH
PHP_URL_QUERY
PHP_URL_FRAGMENT

Note: If you want the file name, just apply the function on the basename of $parts['path'], as an example:

Breaking the query string into parts

As you can see, the function parse_url returns the query string of the URL in the way it is and not divided into parts. To break the query string into parts, just use the function parse_str passing the query string as the first parameter and the second parameter as a vector. Vector will be populated with the variables present in the query string, so that the array index stores the variable name and each position points to the respective variable value (the value is automatically decoded urldecode). Here’s an example:


Modifying the URL or Query String

After breaking the URL and optionally the query string, just manipulate the vector $parts or $queryArray as desired. For example, let’s change the protocol from http to https, change the port from 80 to 81 and remove the parameter “x” and include the parameter “z” value “abc”:

Riding the URL with the parties

After manipulating the URL and/or query string, we now need to assemble the parts and form the URL as a string. To do this, simply use the function http_build_query to mount the query array to turn it into query string. This function now makes coding using urlencode, then you need not worry about reserved characters.

Unfortunately, http_build_url function is only available via PECL. However, it is a very simple function to implement. See below:

Now just use this function and run:

 

PHP Settings

Posted on : 03-04-2013 | By : Gabriela Moraes | In : Uncategorized

0

PHP is an interpreted language that allows multiple policies are configured, both the core of the language and its extensions.

Each policy has a “mode shift” that defines where and when it can be modified. For example, some can only be set in the settings file managed by the server administrator, for security reasons, while others can be changed by the programmer in the application itself.

The modes change the policies are:

PHP_INI_USER - can be changed with ini_set, file .user.ini or in the Windows registry.
PHP_INI_PERDIR - Can be modified in php.ini.htaccesshttpd.conf or .user.ini.
PHP_INI_SYSTEM - Can be modified in php.ini or httpd.conf.
PHP_INI_ALL - can be modified anywhere.

Note: some policies belonged to a mode change by a certain version of PHP, but switched to another mode in another version of PHP. This information is displayed in the list of language policy.

 

1.0 Configuration files (php.ini)

 

phpini

 

 

The default settings are defined in the PHP files “ini” which have syntax based on simple key/value. They are loaded so that a script starts running, but the values ​​can be cached and re-loaded periodically for performance.

The file name may vary according to sapi used. For example, the default file is php.ini, but if you run the CLI sapi (PHP commands in terminal) is the sought php-cli.ini file and if it is not found, it uses the default file .

These files are in a directory server settings (on Linux are usually in the directory “/etc/”, although this site may be modified during the compilation of PHP or some alternative, although not common). Usually only the server administrator has access to these files for changes, for security reasons. This is especially useful for hosting servers, where scripts from one domain can not interfere in another.

 

2.0 Settings by Programmer

 

There are basically 3 ways programmer override the settings of PHP: through ini_set (at runtime), the .htaccess (if using Apache) or file .user.ini (an alternative created in PHP 5.3). Note that in these cases, the behavior is modified specifically for a script or set of scripts and not all PHP files, as with the php.ini file.

2.1 Using ini_set

Using ini_set, simply specify two parameters: the first is the policy name and the second is the value you want to apply to it (as a string). To get the current value of the policy, there is a function ini_get (simply enter the name of the policy). For the value of all policies, or any policies of an extension, there is a function ini_get_all. And to return the policy value to its initial value (when started the script), just use the function ini_restore stating which policy should be restored. example:

 

2.2 Using .htaccess

Use the file .htaccess for Apache servers is allowed in PHP is being used as a module. Apache must be properly configured to accept this type of file in the application directory. In this case, a policy can be defined using two syntaxes. A non-Boolean values ​​to set and another to set boolean values ​​(which may take “on” or “off”) as examples:

Note: in httpd.conf policies can be modified with php_admin_value and php_admin_flag. This can be useful to apply different settings for different directories (or different virtual hosts).

 

2.3 Using .user.ini

Use the file .user.ini is an alternative to the .htaccess, and was incorporated to PHP in version 5.3. For now, it can only be used by SAPIs CGI or Fast CGI. The syntax used in these files is identical to that used in php.ini.

The file name used for this type of configuration ( “.user.ini“) can be modified in php.ini through policy user_ini.filename. This is typically required when the application is already using that name for another purpose.

Note: it is recommended to hide access to this file, as well as (usually) is done with .htaccess to prevent it can be read by any user accessing the system.

 

3.0 Settings useful to know

  • display_errors and display_startup_errors - Indicates whether errors should be displayed or omitted (normally “on” in the development environment and “off” in the production environment).
  • log_errors - Indicates whether errors should be logged to a log file (usually “off” when “display_errors” is “on”, and vice versa).
  • report_memleaks - Indicates whether the bursts of memory should be shown/logged (usually “on”).
  • memory_limit - amount of memory reserved for PHP during script execution. Normally a simple script needs no more than 10M, but some require much more than that. The directive must be set to a reasonable value for the expanded application and, in cases of tools that require more memory. To evaluate memory usage, see the functions memory_get_peak_usage and memory_get_usage.
  • max_execution_time - Sets the maximum time the script can run before it is aborted automatically by PHP (usually “30″, but can be configured with higher values ​​for heavier tools).
  • precision - Sets the precision of decimal places for real numbers (usually 14).
  • date.timezone - Sets the default timezone of the application (eg “America / Sao_Paulo”)
  • default_mimetype - Sets the mimetype of files generated by PHP which have left with the explicit call to header (‘Content-type: …’) (eg “text/html” or “application/xhtml+xml”).
  • default_charset - Sets the default charset of the files generated by PHP which have left with the explicit call to header (‘Content-type: …; charset = …’).
  • short_open_tag - Defines whether the application will accept the abbreviated notation of PHP tags: “<?” and “?>” (recommended “on” only in closed applications whose portability is not important).
  • aps_tags - Defines whether the application will accept the notation ASP to PHP tags “<%” and “%>” (recommended “on” only in closed applications, where portability is not important).
  • register_globals - Defines whether the application will create global variables to values ​​derived from EGPCS (Environment, GET, POST, Cookie, Server). It is strongly recommended to use “off” because it is a deprecated feature which makes the application more prone to security breaches.
  • magic_quotes_runtime and magic_quotes_gpc - sets whether addslashes automatically applied on the data submitted. It is strongly recommended to use “off” because it is a deprecated feature and featuring a performance disadvantage.
  • arg_separator.output - Separator used by standard PHP functions that build URL. It is recommended “&”, especially for applications XHTML).
  • session.auto_start - Automatically log (usually “off”)
  • session.use_cookies - Indicates whether sessions can use cookies to store session keys (recommended “on”).
  • session.use_only_cookies - Indicates whether sessions can only use cookies to traffic session keys, rather than inform them via GET (it is strongly recommended “on” for security reasons).
  • session.use_trans_sid - Indicates whether sessions can use the mechanism of “transparent sid” to travel the session keys (data passed by GET) (it is strongly recommended “off” for security reasons).

Problems with charset? Never again!

Posted on : 28-03-2013 | By : Gabriela Moraes | In : Uncategorized

0

In this article we will see how to use UTF-8 at all and never see characters being displayed wrong.

1.  Save the source code in UTF-8

First of all, choose a good source code editor that allows you to define which encoding used in saved files. Normally this is the editor settings or options of saving time. If you use text mode editors, you may need to configure it in the settings of the terminal command (gnome-terminal, xterm, etc.).

Note: some publishers have option to save the file with the BOM. It is recommended that doesn’t include these bytes, because they can cause unexpected behavior in PHP. For example, you will not be able to call functions such as header or use the namespace feature, which requires that the namespace declaration is the first thing in the script.

2. Tell the browser that you use UTF-8

When a PHP file and generates a HTML is sent to the browser, along with the file go to header (HTTP protocol), where you specify the file type and encoding. If you don’t report it explicitly in your code, your HTTP server (for example, Apache) will send this file with a mime-type pattern (usually “text/html”) and a default encoding (usually “ISO-8859-1 “).

To change this header explicitly, and properly inform the mime-type and encoding of the document you are creating, use the header function, passing the policy “Content-type” as such:


If the file is of another type, just change the mime-type to the corresponding type (eg “text/css”, “text/xml”, “application/xhtml+xml”, etc).

However, the files aren’t always generated via PHP. There are static HTML you need to inform the HTTP header with the mime-type and correct coding. In this case, there is an alternative that is using the meta tag with the attribute “http-equiv” (equivalent HTTP). With it, you can “simulate” HTTP header by the contents of the HTML document. This is done as follows:

In HTML 5, it was simplified:

If you use XML or XHTML, remember to inform the UTF-8 encoding in the XML header:

 

 3. Communicate with the BD via UTF-8

For information to be trafficked between PHP and the database using UTF-8, you must declare this encoding logo that connects to the database. This varies from bank to bank, but let’s see some common examples:

MySQL (PDO):


MySQLi:

MySQL (functions):

Note: the connection to MySQL functions are deprecated. Prefer to use PDO or MySQLi.

PostgreSQL (PDO):

 PostgreSQL (functions):

 

 4. Create your database in UTF-8
The text fields stored in databases also need a character encoding. If it isn’t defined when you create the field, the default encoding is taken from the table or from the database. To set a default encoding of a database, use the command:
MySQL:

 PostgreSQL:

 5 Remember to specify the UTF-8 which you can

Some functions in PHP receive as parameter encoding to be considered. Some of the most important things that should be highlighted are: htmlentities and htmlspecialchars.

Furthermore, when performing operations with regular expressions PCRE, remember to use the modifier “u” at the end of the expression, indicating that it is UTF-8, as an example:


An important set of functions takes into account the location (with encryption) to function. So it is also important to properly set the locale to locale UTF-8:

Remember that the location depends on the server and the name used may vary.

Conclusion:
Taking the necessary steps, you can use UTF-8 with no big problems at all layers of their system: in HTML, PHP and database. Problems with charset? Never again!

Compound Triggers in Oracle 11g

Posted on : 19-03-2013 | By : Gabriela Moraes | In : Uncategorized

0

From Wikipedia: “A database trigger is procedurale cod that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.”

Oracle 11g offers a new twist on triggers, the compound trigger, a trigger that can act both before and after an update, insert or delete has occurred. This makes possible the ability in one trigger to perform processing similar to a stored procedure without having to write such a procedure to call from a traditional trigger. Compound triggers can be used to avoid the dreaded mutating table error or to process and accept or reject updates to a table based upon desired criteria. Before we look at such an example a description of how a compound trigger is constructed is in order.

Compound triggers can have up to four sections:

  • the BEFORE section
  • the BEFORE EACH ROW section
  • the AFTER EACH ROW section
  • the AFTER section

At least two of the sections must be included (including only one of the four would result in a traditional trigger) and it does not matter which two of the sections are used. For example such a trigger can include a BEFORE EACH ROW section and an AFTER section; the two sections need not be ‘matched’ (BEFORE, BEFORE EACH ROW, for instance). Also the COMPOUND TRIGGER STATEMENT must be included so Oracle will recognize the above four constructs and treat them accordingly. The general syntax is:

Since compound triggers are relatively new and many may not have had the opportunity to write or use them I have provided a working example. Setting the stage for this trigger, HR has set a restriction on the size of a raise to be given; based on the department the raise cannot exceed 12 percent of the department average salary. A compound trigger can be used to process the raise amounts assigned. Such a compound trigger is shown below, along with several ways of executing the raises:

PHP Oracle – Scriptcase 7 is out!

Posted on : 15-03-2013 | By : Santos | In : Oracle, PHP

0

PHP Oracle – The new version  of Scriptcase 7 came out in January!

With it came many improvements like Dynamic Group By, HTML 5 Charts, Integration with Social Networks, Upload multiple files, Export PDF in Forms, LDAP security, Menus for mobile devices and more!

See a list with some of the new features:

 

  • SQL PDO Drivers (MySQL, PostgreSQL, SQLServer)
  • Toolbar option
  • Mobile menu support
  • Integration: PayPal, Facebook, Google+, Twitter
  • Grid Ajax events
  • PDF export on forms
  • Progress Bar/Drag’n Drop Upload
  • Multiple Upload
  • HTML5 Charts
  • Javaless PDF generator
  • Reworked GroupBy with Multiple Rules
  • Percent(%) field
  • Single-record-detail on master/detail
  • LDAP Support for security module
  • Scriptcase Macros improvement
  • New Scriptcase Macros created

Check more about Scriptcase 7 at: www.scriptcase.net

PHP Oracle Blog

PHP Oracle – Selecting from the DUAL Table

Posted on : 18-01-2013 | By : Santos | In : Oracle

0

PHP Oracle – Selecting from the DUAL Table

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the nameDUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to “About SQL Functions” for many examples of selecting a constant value from DUAL.

Note:

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT theDUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.

The dual table “works” almost just the way any other table works: it is a table from which you can select records.

This means, for example, you can describe the table. Here, in SQL*Plus:

So, the table has one column, named dummy which is a varchar2(1).

The table has, by design, one record (at least if nobody fiddled with it):

So, in order to get the same behaviour with dual2 as you have with dual, you have to insert one record into dual. Better yet, create it with a create table as select (ctas):

Now, your query works:

Earlier, I said that dual almost works like any other table. So, when does it not work like any other table?

It behaves differently, if no value from the table itself is selected. Again, with your queries, I let Oracleexplain them …

… in order to see how the table is accessed:

It can be seen that the statement does a full table access on dual2.

Now, same thing with dual:

This is where the dual table behaves differently: the value of dummy is not needed, so a fast dualoperation is executed, in order for the instance not to read the actual value on the disk.

 

PHP Oracle Blog

PHP Oracle – Oracle Resources Consolidation and Cloud Computing for SPARC Supercluster

Posted on : 07-01-2013 | By : Santos | In : Oracle

5

PHP Oracle – Oracle Resources Consolidation and Cloud Computing for SPARC Supercluster

 

Oracle Corporation announced new software enhancements that were implemented for the SPARC Supercluster system, allowing customers greater consolidation of any combination of enterprise databases, middleware and mission-critical applications. All this being done in a single system and implementing fast, secure services in self-service cloud computing.

The SPARC Supercluster is able to achieve a rate 10x higher in the consolidation of applications using the new virtualization overhead zero layered together to offload functions (charge transfer), the storage server Oracle Exadata Database Machine and an extremely efficient network.
Resources Offered to Customers of SPARC Supercluster

The SPARC system SuperClusster enables its customers to quickly deploy applications and mission-critical multi-tenancy, without virtualization overhead from the use of isolation capabilities with reference to Oracle Solaris Zone. In addition, customers can virtualize Oracle databases run on Exadata Database Machine X3 Storage Server with Solaris Zones, with the intention of ensuring the delivery of safe and isolated from the database in the cloud.

There will also be the possibility of implementing various fields of enterprise applications in each computing node SPARC Supercluster, to maximize the consolidation and flexibility in use of cloud computing. As a result, customers can also reallocate the CPU and memory resources in existing fields and areas of SPARC Supercluster, aiming to meet the needs of changing workloads.

Implementation of Insurance Services with Flexibility and Faster

The SPARC Supercluster with Oracle Enterprise Manager 12c provides an up to 10x faster provisioning of cloud services, in relation to multi-tenancy applications in relation to manual provisioning, using various tools, as well as an implementation up to 32x faster than one instance of Oracle Database compared to the installation manual. In addition, the SPARC Supercluster provides a comprehensive management solution lifecycle cloud with Oracle Enterprise Manager 12c.
Maximum Efficiency, Decrease Costs and Increased Performance

Compared to integrated systems of customers, the SPARC Supercluster features a 5x return faster and reduce the time since its installation process to production with client systems. Previously, this process took months to complete and can now be done in weeks. We also observed a 10x better performance of data warehouse by combining the latest servers Oracle Exadata Database Machine X3 Storage Servers SPARC Supercluster incorporated into the platform.

Remember that virtualized applications that run quickly and efficiently implementations bare-metal hypervisor using the embedded processor SPARC T4. Customers reduce operating costs by consolidating Oracle Solaris 10 and earlier environments with high performance and maximum efficiency of the SPARC Supercluster, without changing the day to day operations of these environments.

SPARC Supercluster clients also have access to Oracle Premier Support, including Oracle Platinum Services, which offers customers a configuration system designed Oracle support 24 x 7 remote monitoring of failures, and better response times and restoration industry . There is also a presentation of quarterly patch updates directly implemented in systems with high availability in mission-critical environments.

 

Know more:

Oracle Corporation – SPARC SuperClusterhttp://www.oracle.com/us/products/se…88105-ptb.html

 

PHP Oracle Blog

PHP Oracle – PHP 5.5.0 Alpha2 released

Posted on : 26-12-2012 | By : Santos | In : PHP

0

PHP Oracle - PHP 5.5.0 Alpha2 released

 

PHP Oracle Blog – The PHP development team announces the immediate availability of PHP 5.5.0alpha2. This release adds new features and fix some bugs from alpha1. All users of PHP are encouraged to test this version carefully, and report any bugs in the bug tracking system.

THIS IS A DEVELOPMENT PREVIEW – DO NOT USE IT IN PRODUCTION!PHP 5.5.0 Alpha 2 comes with new features and improvements such as (incomplete list) :

  • Support for using empty() on the result of function calls and other expressions,
  • Systemtap support by enabling systemtap compatible dtrace probes on linux,
  • Optimized access to temporary and compiled VM variables. 8% less memory reads.

Please, note that this alpha version also introduces the ext/mysql depreciation.

You can read the full list of changes in the NEWS file contained in the release archive.

For source downloads of PHP 5.5.0 Alpha 2 please visit the download page, Windows binaries can be found on windows.php.net/qa/.

Thank you for helping us making PHP better.

PHP Oracle – Login System with PHP object-oriented

Posted on : 20-12-2012 | By : Santos | In : PHP

1

PHP Oracle – Login System with PHP object-oriented

In this new post we will develop an authentication system with PHP using object orientation. We will need three files: a file to contain the User class, another to perform authentication and redirection and make an HTML page that will contain the form. Well, get to work.

The user class. This class will be on the file user.php

 

 

The HTML Page:

 

The page that will make the authentication data from the HTML page:

 

Well folks, this is an idea for an authentication system. Safety habits like store the password encrypted in the database are important and should be used. To post questions, suggestions and improvements to the code. I have not tested the system. It’s just a tutorial teaching, but either way, it does not cost study and test. In the event that access the database, do not forget to write a connection method.

That’s it folks. Until next.

 

PHP Oracle Blog

PHP Oracle – Aggregate Functions

Posted on : 06-12-2012 | By : Santos | In : Oracle, SQL

0

PHP Oracle – Aggregate Functions

If you are new to SQL, you still might have heard about aggregate functions. These are functions that can be used inside a SELECT query. In this tutorial the PHP Oracle Blog is going to look a bit on what it is, and how it can be used.

Introduction

Aggregate functions are common in most (if not all) relational database management systems (RDBMS).

In a database like Oracle, you will find many single-row functions like SUBSTRTO_NUMBERROUND, etc. These are…like the name indicates: Functions that return a single result row for each row in your resultset (the data returned back from your query).

An aggregate Function, on the other hand, return a single result row based on a group of rows.

As an example, let us pretend we have the following scenario:

  • 100 people are gathered in a large empty building
  • They all have a last name, and many of them actually have the same last name.

There are many ways we can look at these people. We can order them alphabetically, by age, etc. But, 100 people are a lot of people to deal with, and what we really wanted was to count how many had the different last names (just for this simple example). What we can do is to line up everyone alphabetically, and then go through all of them to write down their names…and then count.

But, what if there was a smarter way?

Aggregate Function Explained

Let us again think about the 100 people we mentioned above. What if we had some guys volunteer to organize the people into groups, one per last name. Each of the volunteers would gather their group, and when it was their turn to report back to the person that gathered all the people…they would stand up and shout: “Hansen – 10 people”, the next volunteer would yell “Bailey – 5 people”, etc.

So, basically, if we were the ones gathering the people in the building, all we would need to do would be to come in at the end to note down the different last names, and how many there were of the different names. Essentially this is what happens in the database when we use aggregate functions. We tell the database engine that we want to “gather some people”, and how we want to group them…and the database takes case of the rest.

The syntax for using an aggregate function in Oracle is:

1 SELECT  column_1, coumn_2, column_3,..column_n, AGGREGATE_FUNCTION(expression)
2 FROM    table_1, .. table_n
3 WHERE   where_clause
4 GROUP BY column_1, column_2
5 HAVING  having_condition
6 ORDER BY column_1

Aggregate Function COUNT sample

Ok, so we have some background. Let us look at a practical example. We want to list all the last names, and then show how many there are of each of the names.

1 SQL> SELECT  e.last_name
2   2         ,COUNT(*) name_count
3   FROM   hr.employees e
4   GROUP BY e.last_name
5   5  ;
6
7 LAST_NAME                 NAME_COUNT
8 ------------------------- ----------
9 Abel                               1
10 Ande                               1
11 Atkinson                           1
12 Austin                             1
13 /* A lot of records go here */
14 Cabrio                             1
15 Cambrault                          2
16 Chen                               1
17 Chung                              1
18 Colmenares                         1
19 Davies                             1
20 De Haan                            1
21
22 LAST_NAME                 NAME_COUNT
23 ------------------------- ----------
24 /* A lot of records go here */
25 Gietz                              1
26 Grant                              2
27
28 LAST_NAME                 NAME_COUNT
29 ------------------------- ----------
30 /* A lot of records go here */
31 King                               2
32 Kochhar                            1
33 /* A lot of records go here */
34 LAST_NAME                 NAME_COUNT
35 ------------------------- ----------
36 /* A lot of records go here */
37 Olsen                              1
38 Olson                              1
39 Sarchand                           1
40
41 LAST_NAME                 NAME_COUNT
42 ------------------------- ----------
43 Smith                              2
44 /* A lot of records go here */
45 Sullivan                           1
46 Taylor                             2
47 /* A lot of records go here */
48
49 102 rows selected
50 SQL> /
51
52 LAST_NAME                 NAME_COUNT
53 ------------------------- ----------
54 /* A lot of records go here */
55 Bull                               1
56 Cabrio                             1
57 Cambrault                          2
58 /* A lot of records go here */
59
60 LAST_NAME                 NAME_COUNT
61 ------------------------- ----------
62 /* A lot of records go here */
63 Gietz                              1
64 Grant                              2
65 Greenberg                          1
66 /* A lot of records go here */
67
68 LAST_NAME                 NAME_COUNT
69 ------------------------- ----------
70
71 /* A lot of records go here */
72 Khoo                               1
73 King                               2
74 /* A lot of records go here */
75
76 LAST_NAME                 NAME_COUNT
77 ------------------------- ----------
78 /* A lot of records go here */
79 Russell                            1
80 Sarchand                           1
81
82 LAST_NAME                 NAME_COUNT
83 ------------------------- ----------
84 Sewall                             1
85 Smith                              2
86 Stiles                             1
87 Taylor                             2
88
89 /* A lot of records go here */
90
91 102 rows selected

Please note that I skipped showing a lot of records so that you should not be annoyed scrolling through them. ;-)

So, let us shorten down the list a bit.

Aggregate Function COUNT using HAVING sample

Let us show ONLY the last names that are owned by more than one employee.

1 SQL> SELECT  e.last_name
2   2         ,COUNT(*) name_count
3   FROM   hr.employees e
4   GROUP BY e.last_name
5   HAVING COUNT(*) > 1
6   6  ;
7
8 LAST_NAME                 NAME_COUNT
9 ------------------------- ----------
10 Cambrault                          2
11 Grant                              2
12 King                               2
13 Smith                              2
14 Taylor                             2

Here you can see that we have used the HAVING clause to limit the data in our result set. It is kind-of the WHERE of aGROUP BY statement.

Ok, let us now pretend we want to show the last names that are owned by two or more people for only a given set of departments. This can be a sample of that:

1 SQL> SELECT  e.last_name
2   2         ,COUNT(*) name_count
3   FROM   hr.employees e
4   WHERE  e.department_id IN (50, 80)
5   GROUP BY e.last_name
6   HAVING COUNT(*) > 1
7   7  ;
8
9 LAST_NAME                 NAME_COUNT
10 ------------------------- ----------
11 Taylor                             2
12 Cambrault                          2
13 Smith                              2

Above we used both a WHERE and a HAVING. What will happen in this query is that it will select employees from the given departments, and then group the people.

You can also use two (or more) aggregate functions in your SELECT part of your query, and still only use one of these functions in your HAVING filter. As shown in this example:

1 SQL> SELECT  e.last_name
2   2         ,COUNT(*) name_count
3   3          ,SUM(e.salary) salary_sum
4   FROM   hr.employees e
5   WHERE  e.department_id IN (50, 80)
6   GROUP BY e.last_name
7   HAVING SUM(e.salary) > 15000
8   8  ;
9
10 LAST_NAME                 NAME_COUNT SALARY_SUM
11 ------------------------- ---------- ----------
12 Cambrault                          2      18500
13 Smith                              2      15400

The GROUP BY columns

As you see from the query above, we are GROUPing our data on the last_name. But, you can group on more than one column. If you again look at the query above, you can SELECT any column from the employee table, but all the columns you SELECT has to be present in your GROUP BY clause.

But, you can GROUP BY any columns from the employee table, even if they are not present as your SELECT columns.

Also, note that the order of the columns in your GROUP BY clause might matter in some situations The most important column to GROUP BY is the most left one on your GROUP BY line.

Additional Aggregate Functions in Oracle

In addition to the COUNT and SUM used in the queries above, there are many different aggregate functions in oracle. If you want to read more about these functions, I would suggest you take a look at the Oracle documentation:

Oracle 11g R2 Aggregated Functions

If you have any questions about aggregate functions, please leave a comment so that we can all help each other.

 

PHP Oracle Blog