MySQL GROUP_CONCAT VS Oracle LISTAGG

Several time i need to groups some values in one column and make it into one single column which separated by commas, for example when i have table category_product which is contains column productId, productName, and category. Foreach category of a product stored on a single row entry and each products can have more than one category. To select each product and its categories then make list of categories within single row for each product. 

On complex way with create such a procedure thas has a loop and concate all category on a variable maybe can solved the problem. But yes.. it needs a time and more effort, so why we not use the easy way?

There is GROUP_CONCAT on MySQL or LISTAGG on Oracle that can group value from some column and group them by specific id into single strings.

GROUP_CONCAT on MySQL

Syntax:
group_concat(column_name) or group_concat(column_name separator 'separator_type')

Example:
select id, group_concat(category) from table1 group by id;

select id, group_concat(category separator ';') from table1 group by id;

Note: Default separator on group_concat function is commas. Add "separator" after column name to change the separator.

LISTAGG on Oracle

Syntax:
listagg(column_name, separator) within group (order by column_name) 
over (partition by column_name)

Example:
 select id, listagg(category, ',') within group (order by id) from table1 group by id;

 select id, listagg(category, ',') within group (order by id) over (partition by id) from table1;

Note: Instead of group by use over(partition by ....) to groupping the result.

references:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm

Object Oriented Analysis and Design

Object Oriented Analysis and Design (OOAD) probably is the most popular method used by developer to analyzing and designing system or application today. OOAD can be used on simple or complex system. OOAD method approach to develop the system rather then use the traditional way which have defined structure, OOAD more likely make several object which have identity and methods then each object will be compiled on paralel way. Instead of run every step from defined structures, OOAD just need call an object which have the process you need.

There is three characteristic from OOAD:

1. Encapsulation, way to let on private the detail of object.
2. Inheritance, there is identity or method which is can be used by the child from their parent.
3. Classes, to put all method inside of class.

Tools on OAAD

Object Oriented Analysis and Design Using UML

Unified Modelling Language (UML) is a tools to draw the analisys and design on OOAD. Diagrams on UML:

• Use Case Diagram
• Sequence Diagram
• Class Diagram
• Object Diagram
• Object State Diagram
• etc.

Applications to make UML

Here some tools, application or software to draw UML :

• Microsoft Visio
• Astah
• StarUML

For information about OOAD on the following links.

http://www.umsl.edu/~sauterv/analysis/488_f01_papers/quillin.htm

Yii PHP Framework

The first time i used Yii Framework was when i need to create a website for my class project. Yii Framework easy to learn and simple use but powerful enough to develop web application. Even though it based on object oriented programming which usually the load data is from model on MVC.  But Yii framework good enough to perform and execute a natural sql query.

Installing Yii Framework on XAMPP

Here some tutorial to install Yii Framework on XAMPP at Windows OS:

1. Download the Yii Framework from http://www.yiiframework.com/download/.
2. Extract the yii-x.x.x.xxxxx.tar.gz and then move the folder to your xampp/htdocs location. Rename the extracted folder like "yii" to make it easy when you install and use the framework.
3. Run the command prompt. Press Windows + R from keyboard. Then type cmd.
4. On command prompt, open php folder on your xampp folder. For example if your xampp folder is on drive C then type cd xampp/php. But if your xampp is on other drive, for example if it is on D drive then you need to change the drive first by typing d: then press enter.
5.  Then type php.exe D:\xampp\htdocs\yii\framework\yiic webapp D:\xampp\htdocs\yiiapp
   Description: D:\xampp\htdocs\yiiapp means the application will be installed on xampp - htdocs, while yiiapp is the name of your application that will be created.
6.  When the following confirmation to create the application appear, type yes and enter.
   Your application has been succesfully created under D: \ xampp \ htdocs \ yiiapp
7. Open your web browser and go to http://localhost/yiiapp. You can see the basic web page of your application which was created by Yii Frame work. 

 Note: Don't delete the yii folder even though you already create the application.

ORACLE Execute Immediate

Execute Dynamic SQL Statement

The EXECUTE IMMEDIATE can be used to execute dynamic sql statement or on anynomous block in Oracle PL/SQL. Especially when we need to execute the data definition language (DDL) statement, such as create, drop, alter, etc. Of course you can use it on DML too like select, delete, insert. The Execute Immediate statement simply will execute sql or query on text forms.

Syntax:

EXECUTE IMMEDIATE <SQL>
  [INTO <variable list>]
  [USING <bind variable list>];
 

Example:

DECLARE
 query varchar2(1000);
 v_row table1%rowtype;
 v_tablename varchar2(10);

BEGIN
 query := 'create table table1 (id number, name varchar2(50))';
 execute immediate query;

 query := 'insert into table1 values (:a :b)';
 execute immediate query using 1, 'yourname';

 query := q'{select * from table1 where name=:a or name like '%:a%'}';
 execute immediate query into v_row using 'your';
 dbms_output.put_line(v_row.id||v_row.name);

 v_tablename := 'table1';
 query := 'delete from '||v_tablename||' where id=1';
 execute immediate query;

END;
/

Tips: From example above to solving the problem with quotes, try use q'{ }' instead the usual ' .
   
For more information about Execute Immediate see Oracle Database Documentation Library.