AngularJS2015. 12. 3. 11:17
반응형

The answer is NO. The ng-app directive is used to auto-bootstrap an AngularJS application. And according to AngularJS Documentation, only one AngularJS application can be auto-bootstrapped per HTML document. I'd like to quote a section from the documentation here:

Only one AngularJS application can be auto-bootstrapped per HTML document. The first ngApp found in the document will be used to define the root element to auto-bootstrap as an application. To run multiple applications in an HTML document you must manually bootstrap them using angular.bootstrap instead. AngularJS applications cannot be nested within each other.

I'm going to show three approaches, latter two being similar, to deal with the issue.

Before that let's take a look what the actual problem is.
<!DOCTYPE html>

<html>
<head>
<title>Multiple ngApp does not work</title>
</head>

<body>
       <div ng-app="firstApp">
              <div ng-controller="FirstController">
                 <p> 1# {{ name }} </p>
              </div>
       </div>

       <div ng-app="secondApp">
              <div ng-controller="SecondController">
                 <p> 2# {{ name }} </p>
              </div>
       </div>

       <script src="angular.js"></script>
       <script type="text/javascript">
              
            var firstApp = angular.module('firstApp', []);
            firstApp.controller('FirstController'function($scope) {
            
                 $scope.name = "I'm the first app.";
            });

            var secondApp = angular.module('secondApp', []);
            secondApp.controller('SecondController'function($scope) {
            
                $scope.name = "I'm the second app.";
            });
       </script>
</body>
</html>

As you can see, we defined two separate ng-app directives named firstApp and secondApp. And we also defined two controllers one for each ng-app module and each having its own scope variable name. As the documentation states, only the first ng-app module is auto-bootstrapped. So, in this case, only the firstApp module works as expected while the secondApp module does not. As a consequence, the browser renders the above page as follows:


1# I'm the first app.
2# {{ name }} 

Now that we discussed the problem, let's move ahead and see how to use alternatives.

Method 1: Injecting modules as dependencies of the root app

The idea here is to define only one top level ng-app in a root element like in <html> or <body>, define other two as modules and inject them as dependencies of the root app.
<!DOCTYPE html>

<html>
<head>
       <title>Injecting modules as dependencies of the root app </title>
</head>

<body ng-app="rootApp">

<div id="firstApp">
       <div ng-controller="FirstController">
              <p>1# {{ name }}</p>
       </div>
</div>

<div id="secondApp">
       <div ng-controller="SecondController">
              <p>2# {{ name }}</p>
       </div>
</div>

<script src="angular.js"></script>
<script type="text/javascript">

    // passing the two modules as dependencies of the root module
    var rootApp = angular.module('rootApp', ['firstApp','secondApp']);

    var firstApp = angular.module('firstApp', []);
         firstApp.controller('FirstController'function ($scope) {
         $scope.name = "I'm the first app.";
    });

    var secondApp = angular.module('secondApp', []);
    secondApp.controller('SecondController'function ($scope) {
         $scope.name = "I'm the second app.";
    });      

</script>
</body>
</html>
This will give the desired result:

1# I'm the first app.
2# I'm the second app.


Method 2: Manual bootstrapping the second module

In this method, we are going to leave the first ng-app as it is so that it is auto-bootstrapped by Angular. Whereas for the second ng-app, we're going to a manual bootstrapping method.
<!DOCTYPE html>

<html>
<head>
      <title>Manual bootstrapping the second module</title>
</head>

<body>
       <div ng-app="firstApp">
              <div ng-controller="FirstController">
                     <p>1# {{ name }}</p>
              </div>
       </div>

       <!-- using id attribute instead of ng-app -->
       <div id="secondApp">
              <div ng-controller="SecondController">
                     <p>2# {{ name }}</p>
              </div>
       </div>

<script src="angular.js"></script>
<script type="text/javascript">
      
       var firstApp = angular.module('firstApp', []);
       firstApp.controller('FirstController'function($scope) {

              $scope.name = "I'm the first app.";
       });

       var secondApp = angular.module('secondApp', []);
       secondApp.controller('SecondController'function($scope) {

              $scope.name = "I'm the second app.";
       });

       // manually boostrapping the second app
       var secondDiv = document.getElementById('secondApp');

       angular.element(document).ready(function() {
              angular.bootstrap(secondDiv, [ 'secondApp' ]);
       });
</script>
</body>
</html>

Method 3: Manual bootstrapping both modules

As I already mentioned, this method is similar to the previous one. Here, we don't rely on Angular's auto-bootstrapping to initialize the modules. We'll use manual bootstrapping method to initialize both modules as depicted in the example below:
<!DOCTYPE html>

<html>
<head>
      <title>Manual boostrapping both modules</title>
</head>

<body>
       <!-- using id attribute instead of ng-app -->
       <div id="firstApp">
              <div ng-controller="FirstController">
                     <p>1# {{ name }}</p>
              </div>
       </div>

       <!-- using id attribute instead of ng-app -->
       <div id="secondApp">
              <div ng-controller="SecondController">
                     <p>2# {{ name }}</p>
              </div>
       </div>

<script src="angular.js"></script>
<script type="text/javascript">
       var firstApp = angular.module('firstApp', []);
       firstApp.controller('FirstController'function($scope) {

              $scope.name = "I'm the first app.";
       });

       var secondApp = angular.module('secondApp', []);
       secondApp.controller('SecondController'function($scope) {

              $scope.name = "I'm the second app.";
       });

       var firstDiv = document.getElementById('firstApp');
       var secondDiv = document.getElementById('secondApp');

       // manually boostrapping the second app
       angular.element(document).ready(function() {
              angular.bootstrap(firstDiv, [ 'firstApp' ]);
              angular.bootstrap(secondDiv, [ 'secondApp' ]);
       });
</script>
</body>
</html>


Posted by 1010
AngularJS2015. 12. 2. 18:31
반응형
Posted by 1010
AngularJS2015. 12. 2. 18:28
반응형

Getting Started

This page explains how to install and configure AngularJS Eclipse. This Eclipse plugin is based on the powerful javascript inference engine tern.js which is written in javascript. To use this engine on Java context, tern.java is used. It executes tern.js with node.js. That's why you will see that, you must install node.js server or use an embed node.js.

If you don't install node.js or don't use an embed node.js, only syntax coloring and completions directives will be available in HTML editor.

Installation

AngularJS Eclipse is developed/tested with Eclipse 4.4 (Luna). It is advised to use Luna (even if AngularJS Eclipse could work with older version of Eclipse).

To install AngularJS Eclipse, please read Installation - Update Site section.

When you will use AngularJS Eclipse update site you will see that:

Update site

You must select:

  • AngularJS Eclipse Tooling which is AngularJS Eclipse plugins .
  • AngularJS support for JSP if you wish to use JSP with AngularJS.
  • Tern - Embed Node.js if you have not node.js installed on your computer. Node.js is required to execute tern.js.
  • Tern IDE to use tern with Eclipse IDE.
  • Tern - Tooling if you want to generate tern plugin, JSON Type Definition or Web Browser editor (CodeMirror, Ace, Orion) with tern. For more information please read Tern Toolings

AngularJS Configuration

Before using AngularJS Eclipse features (HTML features and JavaScript features) you must convert your project to AngularJS Project :

Convert To AngularJS Project

Preferences Settings

This section explains how to configure tern and angular.

Global Preferences

This section is about "global preferences" dialog that you open with Window/Preferences.

Node.js

AngularJS Eclipse is based on the javascript inference engine tern.js is written in JavaScript. To use it, tern is executed with node.js (Rhino is too slow for that). You must configure node.js. To do that you can :

  • use your installed node.js. For that, you must select the "Native node" install type and select the well node file :

Native Node

when the native node is selected, it searches node binary in default folders installation (ex : "C:\Program Files\nodejs\node.exe" for Windows) and if it doesn't find, it searches in your node in your "PATH" environment.

If you wish to download and install node.js, it's important to restart your computer before using AngularJS Eclipse in order to your OS update correctly your "PATH" environment with your installed node.js.

  • use an embed node. For that you must install the well embed node.js according your OS :

Embed Node

Project preferences

This section is about "project properties" dialog which is available if you select your project and use "Properties" menu item of the contextual menu (or Alt/Enter).

Tern Modules

Tern module is a Tern Plugin or JSON Type definition. Check that angular plugin is checked :

Tern Plugin.

The angular plugin gives you the capability to retrieve module, controllers,(custom) directives, etc from your javascript, manages completion hyperlink, hover, validation in HTML and JavaScript editor. It's enable to emulate the angular injection on your $scope, $http, etc.

You can select other tern module like jQuery for instance to benefit with jQuery completion inside JavaScript Editor.

Scripts path

When tern is used for completion, validation, hover, hyperlink, it must load before (just the first time) a list of your JavaScript. To do that you must configure your script paths by selecting your js folder which contains your javascripts (it's the same thing than for Java build path) :

Script paths

For more information, please read Tern Script Path

Customize Directives syntax

In HTML editor, directives completion provides directive names with the ng-* syntax :

HTMLAngularConfigureDirective1

Angular supports several syntax like starting with 'x-', 'data-' and use ':', '-', '_' delimiters. You can customize the syntax that you wish for completion with the project properties. By default you will see this configuration :

HTMLAngularConfigureDirective2

You can select other starts with and delimiters. You can see in the textarea the directive names that completion will show :

HTMLAngularConfigureDirective3

After validating your configuration, completion will show you directive names with other syntaxes :

HTMLAngularConfigureDirective3

Validation

If you validate with "Validate" menu contextual menu :

HTMLAngularValidatorValidate

you will see that AngularJS directives will have warn messages :

HTMLAngularValidatorWarnDirective

In this sample you have 2 warnings messages :

  • a warning with ng-app which is an Angular directive
  • a warning with "a" attribute in the head element which doesn't exist.

You could disable the warning message for unknown attribute, but AngularJS Eclipse provides the "HTML Angular Syntax Validator" which is an extension of the "HTML Syntax Validator" to support Angular directives. To use this Angular validator, you must enable it and disable "HTML Syntax Validator" :

HTMLAngularValidatorUseAngular

If you revalidate you will see that directive are not marked as warning and other unknown attributes are marked as warning :

HTMLAngularValidatorWarnUnknownAttr

Validation & JSP

If you use JSP, you must disable JSP Content Validator and enable JSP Angular Content Validator.

Let's go!

At this step we have finished to configure AngularJS Eclipse, now it's time to check that everything works (tern with node.js is well configured).

HTML Editor

Open an HTML file by using standard WTP HTML, JSP editor.

Try completion on ng-app to open your module:

Angular Editor

This feature is managed by tern, if it doesn't work, see Troubleshooting section.

JavaScript Editor

Open a javascript editor and try to open completion for angular model:

Angular Editor

This feature is managed by tern, if it doesn't work, see Troubleshooting section.

Troubleshooting

If you have checked your configuration and completion doesn't work for HTML and JavaScript editor, it means that there is a problem with tern with node.js. To verify that you can see errors with :

  • Error log view.
  • Tern console

Error log view

TernErrorLog

Tern console

You can trace the start of node.js server and the request/response of the tern server by using the Eclipse console.

To do that, you must active the tern console for your project:

TernConsoleProjectProperties

and open the tern console:

TernConsoleOpen

If you retry to execute completion for instance to use tern server, you will see the node.js command and the error in the console:

TernConsoleError

When you will have not problem, you can see the JSON request/response of the tern server when it is use it:

TernConsoleOK

See Tern Console for more information.

If you have again, problem, please create an issue here.

Angular Explorer

Angular Explorer View gives you the capability to display Angular elements like modules, controllers of your AngularJS application. To open it go to Window/Show View and select Angular Explorer :

ShowViewAngularExplorer

After that you can see your modules, controllers, etc :

Angular Explorer

Please read Angular Explorer for more information.


Posted by 1010
02.Oracle2015. 10. 11. 16:50
반응형

String Aggregation Techniques

On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. Using the SCOTT.EMP table as an example, we might want to retrieve a list of employees for each department. Below is a list of the base data and the type of output we would like to return from an aggregate query.

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

Desired Output:

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

This article is based on a thread from asktom.oracle.com and contains several methods to achieve the desired results.

LISTAGG Analytic Function in 11g Release 2

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

WM_CONCAT Built-in Function (Not Supported)

If you are not running 11g Release 2 or above, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below, but Oracle have done all the work for you.

COLUMN employees FORMAT A50

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.

 WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, use a User-Defined Aggregate Function described below.

Also, WM_CONCAT has been removed from 12c onward, so you can't pick this option.

User-Defined Aggregate Function

The WM_CONCAT function described above is an example of a user-defined aggregate function that Oracle have already created for you. If you don't want to use WM_CONCAT, you can create your own user-defined aggregate function as described at asktom.oracle.com.

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

The aggregate function is implemented using a type and type body, and is used within a query.

COLUMN employees FORMAT A50

SELECT deptno, string_agg(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

3 rows selected.

Specific Function

One approach is to write a specific function to solve the problems. The get_employees function listed below returns a list of employees for the specified department.

CREATE OR REPLACE FUNCTION get_employees (p_deptno  in  emp.deptno%TYPE)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP
    l_text := l_text || ',' || cur_rec.ename;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS

The function can then be incorporated into a query as follows.

COLUMN employees FORMAT A50

SELECT deptno,
       get_employees(deptno) AS employees
FROM   emp
GROUP by deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.

To reduce the number of calls to the function, and thereby improve performance, we might want to filter the rows in advance.

COLUMN employees FORMAT A50

SELECT e.deptno,
       get_employees(e.deptno) AS employees
FROM   (SELECT DISTINCT deptno
        FROM   emp) e;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        
3 rows selected.

Generic Function using Ref Cursor

An alternative approach is to write a function to concatenate values passed using a ref cursor. This is essentially the same as the previous example, except that the cursor is passed in making it generic, as shown below.

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2
IS
  l_return  VARCHAR2(32767); 
  l_temp    VARCHAR2(32767);
BEGIN
  LOOP
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return || ',' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;
/
SHOW ERRORS

The CURSOR function is used to allow a query to be passed to the function as a ref cursor, as shown below.

COLUMN employees FORMAT A50

SELECT e1.deptno,
       concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM   emp e1
GROUP BY e1.deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.

Once again, the total number of function calls can be reduced by filtering the distinct values, rather than calling the function for each row.

COLUMN employees FORMAT A50

SELECT deptno,
       concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM   (SELECT DISTINCT deptno
        FROM emp) e1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

3 rows selected.

ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i

An example on williamrobertson.net uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions to achieve the same result without the use of PL/SQL or additional type definitions.

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

COLLECT function in Oracle 10g

An example on oracle-developer.net uses the COLLECT function in Oracle 10g to get the same result. This method requires a table type and a function to convert the contents of the table type to a string. I've altered his method slightly to bring it in line with this article.

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

The query below shows the COLLECT function in action.

COLUMN employees FORMAT A50

SELECT deptno,
       tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
FROM   emp
GROUP BY deptno;
       
    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        
3 rows selected.


Posted by 1010
54.iBATIS, MyBatis/iBatis2015. 7. 23. 12:19
반응형

ex)

SELECT * FROM job where (name1 = 'key1' or name2 = 'key2') and name3 = 'key3'


-------------------------------------------------------

<select id="job" parameterClass="Map">

  SELECT *

  FROM jobs

  <dynamic prepend="WHERE">

   <isNotNull prepend="AND" removeFirstPrepend="true" open="(" close=")" >

      <isNotEmpty prepend="OR" property="key1">

         name1 = #key1#

      </isNotEmpty>

      <isNotEmpty prepend="OR" property="key2">

         name2 = #key2#

      </isNotEmpty>

     </isNotNull>

   <isNotEmpty prepend="AND" property="key3">

   name3 = #key3#

   </isNotEmpty>

  </dynamic>

 </select> 

Posted by 1010
반응형

Another and easy way of increasing any number of levels(dimensions) in Jqgrid is by adding setGroupHeaders that number of times

If my columns are like, ColNames = ['Id','Date', 'Client', 'Amount','Tax','Total','Notes'];

Now add setGroupHeaders Like

jQuery("#list").jqGrid('setGroupHeaders', {
          useColSpanStyle: true, 
          groupHeaders:[
            {startColumnName: 'id', numberOfColumns: 1, titleText: '.'},
            {startColumnName: 'date', numberOfColumns: 8, titleText: 'Nice'},
            ]   
        });
        jQuery("#list").jqGrid('setGroupHeaders', {
          useColSpanStyle: true, 
          groupHeaders:[
            {startColumnName: 'id', numberOfColumns: 1, titleText: '.'},
            {startColumnName: 'date', numberOfColumns: 4, titleText: 'rice'},
            {startColumnName: 'total', numberOfColumns: 2, titleText: 'dice'}
            ]   
        });

        jQuery("#list").jqGrid('setGroupHeaders', {
          useColSpanStyle: true, 
          groupHeaders:[
            {startColumnName: 'id', numberOfColumns: 1, titleText: '.'},
            {startColumnName: 'date', numberOfColumns: 2, titleText: 'Price'},
            {startColumnName: 'amount', numberOfColumns: 2, titleText: 'Shiping'},
            {startColumnName: 'total', numberOfColumns: 2, titleText: 'bipping'}
            ]   
        });

Following is the output

| .  |                       Nice                              |
----------------------------------------------------------------
| .  |                 rice                |       dice        |
----------------------------------------------------------------    
| .  |       Price     |      Shipping     |       bipping     | 
----------------------------------------------------------------    
| id |  Date  | Client |  Amount   |  Tax  | Total  |  Notes   |   



The reason of restriction of one level of group headers in jqGrid exist because jqGrid provide more as just displaying the headers. You can see on the example of the demo created for the answer that the column headers after grouping are clickable (to sort by the column) and resizable (by drag&drop on the separator between column headers). If you use titleText option of setGroupHeaders you can include HTML fragments, inclusive <table> element, in the column header. It gives you the possibility to display milti-level headers. One can include resizable: false to deny resizing or one can write which custom resizeStop handler which resizes columns in the table added by titleText option of setGroupHeaders.

All what I described above sound theoretical. So I wrote the small demo which demonstrates the approach. It displays the following grid

enter image description here

The demo is written not for the common case, but it's clear that one can use it as basis to more common solution. In any way I hope that you can change it to any your multi-level grid.

The most important parts of the demo you will find below:

var grid = $("#list"),
    setHeaderWidth = function () {
        var $self = $(this),
            colModel = $self.jqGrid("getGridParam", "colModel"),
            cmByName = {},
            ths = this.grid.headers, // array with column headers
            cm,
            i,
            l = colModel.length;

        // save width of every column header in cmByName map
        // to make easy access there by name
        for (i = 0; i < l; i++) {
            cm = colModel[i];
            cmByName[cm.name] = $(ths[i].el).outerWidth();
        }
        // resize headers of additional columns based on the size of
        // the columns below the header
        $("#h1").width(cmByName.amount + cmByName.tax + cmByName.total - 1);
        $("#h2").width(cmByName.closed + cmByName.ship_via - 1);
    };

grid.jqGrid({
    ...
    colModel: [
        {name: "id", width: 65, align: "center", sorttype: "int", hidden: true},
        {name: "invdate", width: 80, align: "center", sorttype: "date",
            formatter: "date", formatoptions: {newformat: "d-M-Y"}, datefmt: "d-M-Y"},
        {name: "name", width: 70},
        {name: "amount", width: 75, formatter: "number", sorttype: "number", align: "right"},
        {name: "tax", width: 55, formatter: "number", sorttype: "number", align: "right"},
        {name: "total", width: 65, formatter: "number", sorttype: "number", align: "right"},
        {name: "closed", width: 75, align: "center", formatter: "checkbox",
            edittype: "checkbox", editoptions: {value: "Yes:No", defaultValue: "Yes"}},
        {name: "ship_via", width: 100, align: "center", formatter: "select",
            edittype: "select", editoptions: {value: "FE:FedEx;TN:TNT;IN:Intim", defaultValue: "IN"}},
        {name: "note", width: 70, sortable: false}
    ],
    resizeStop: function () {
        // see http://stackoverflow.com/a/9599685/315935
        var $self = $(this),
            shrinkToFit = $self.jqGrid("getGridParam", "shrinkToFit");

        $self.jqGrid("setGridWidth", this.grid.newWidth, shrinkToFit);
        setHeaderWidth.call(this);
    }
});
grid.jqGrid ("navGrid", "#pager",
             {edit: false, add: false, del: false, refresh: true, view: false},
             {}, {}, {}, {multipleSearch: true, overlay: false});
grid.jqGrid("setGroupHeaders", {
    useColSpanStyle: true,
    groupHeaders: [{
        startColumnName: "amount",
        numberOfColumns: 5,
        titleText:
            '<table style="width:100%;border-spacing:0px;">' +
            '<tr><td id="h0" colspan="2"><em>Details</em></td></tr>' +
            '<tr>' +
                '<td id="h1">Price</td>' +
                '<td id="h2">Shiping</td>' +
            '</tr>' +
            '</table>'
    }]
});
$("th[title=DetailsPriceShiping]").removeAttr("title");
$("#h0").css({
    borderBottomWidth: "1px",
    borderBottomColor: "#c5dbec", // the color from jQuery UI which you use
    borderBottomStyle: "solid",
    padding: "4px 0 6px 0"
});
$("#h1").css({
    borderRightWidth: "1px",
    borderRightColor: "#c5dbec", // the color from jQuery UI which you use
    borderRightStyle: "solid",
    padding: "4px 0 4px 0"
});
$("#h2").css({
    padding: "4px 0 4px 0"
});
setHeaderWidth.call(grid[0]);
shareimprove this answer


Posted by 1010
카테고리 없음2015. 5. 26. 11:03
반응형


http://ssamkj.tistory.com/20


커서의 내용을 미리 정의 해 놓고 사용하는 방법.

1
2
3
4
5
6
7
8
9
DECLARE
  CURSOR C_LIST IS
    SELECT MY_ID FROM MY_TABLE WHERE 조건;
BEGIN
 
  FOR I_ID IN C_LIST LOOP
    DBMS_OUTPUT.put_line(I_ID);
  END LOOP;
END;

비추천 

커서의 내용을 정할 때 select 문제 동적으로 parameter가 넘어가야 할 경우 사용이 불가능 하다. 왜냐하면 BEGIN 전에 정의하기 때문이다.



커서 변수를 미리 만들어 놓고 불러서 사용하는 방법.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
    I_ID   VARCHAR2(100);       -- 변수 정의               
  C_LIST SYS_REFCURSOR;     -- 커서 정의
BEGIN
  OPEN C_LIST FOR
  SELECT MY_ID  
    FROM MY_TABLE
    WHERE 조건;
  LOOP                  -- LOOP 돌기.
      FETCH C_LIST
      INTO  I_ID;           --  하나씩 변수에 넣기.
      EXIT WHEN C_LIST%NOTFOUND;    -- 더이상 없으면 끝내기.
      DBMS_OUTPUT.put_line(I_ID);    --  출력
  END LOOP;
  CLOSE C_LIST;
END;
재사용성이 있어서 나름 괜찮음. 
커서를 정의 한 뒤 그 때 그 때 커서의 내용을 채우는 방법이다.



 
동적으로 커서를 생성해서 사용하는 방법
1
2
3
4
5
6
7
8
9
DECLARE
 
BEGIN
 
  FOR C_LIST IN (SELECT MY_ID FROM MY_TABLE WHERE 조건)
  LOOP
    DBMS_OUTPUT.put_line(C_LIST.I_ID);
  END LOOP;
END;
강추~!!

커서를 미리 정의 할 필요도 없고, 변수를 미리 만들어 놓을 필요도 없다.


Posted by 1010
02.Oracle/DataBase2015. 5. 22. 14:18
반응형

출처 : [한국 Oracle Technology Network]의 기술 지원 게시판

 

========================================================================================

No. 22383 : DBMS_STATS.GATHER_SYSTEM_STATS를 이용한 통계정보 수집 가이드
========================================================================================

 

PURPOSE
-------

 

   이 자료는 DBMS_STATS.GATHER_SYSTEM_STATS 함수를 사용하여 system table에 대한

   통계 정보 수집 시 stats table과 AUX_STATS$ table에 나타나는 정보에 대한 분석 및 
   통계 정보 생성 시 필요한 INTERVAL에 대한 내용과 통계정보 수집 상태를 나타내는

   NOWORKLOAD, BADSTATS, AUTOGATHERING 상태에 대한 설명입니다.


Explanation
-----------

 

다음과 같은 문서를 참고하여 test 후, 확인하였습니다.
<Note:153761.1> Gathering System Statistics Returns only 'NOWORKLOAD'.
<Note:149560.1> Collect and Display System Statistics (CPU and IO) for CBO usage.

 

DBMS_STATS.GATHER_SYSTEM_STATS를 사용하여 20분 동안의 통계정보를 생성하는

간단한 절차를 test를 통해 알아보기로 한다. 통계정보 수집 시간이 최소 권장 사항인 60분

이상이 되지 않아도 오라클에서 통계정보를 측정하기 위한 기준치 만큼의 자원 소비가 있으면 
PVAL2가 badstats로 나오지 않고 autogathering으로 정상으로 나오는 것을 알 수 있다.


Step 
-----
1. sqlplus "/as sysdba"
2. EXECUTE DBMS_STATS.CREATE_STAT_TABLE( ownname => 'SYSTEM',

    stattab => 'mystats'); 
3. ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;

4. EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( interval => 20, stattab => 'mystats',

     statid => 'DAY', statown => 'SYSTEM'); 
    EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 20);

 

< 참고 > statid 로 DAY 와 NIGHT 이 있으므로, DBMS_STATS.GATHER_SYSTEM_STATS 
             함수의 파라미터로 선택하여 사용 가능함.

 

5. select * from system.mystats; 
    select count(*) from system.mystats;

 

   위의 select 수행 결과 no rows selected로 나오면 아래와 같이 수행한다. 

   EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'START',

    stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');

 

   또한 START 를 MANUAL하게 해주지 않으면 다음과 같이 NOWORKLOAD로 나타난다.

 

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

 

PNAME     PVAL2
---------  --------------------------------------------------------------------
STATUS    NOWORKLOAD


6. DBMS_STATS.GATHER_SYSTEM_STATS 함수에서 START 를 해주면 아래와 같이 
   AUTOGATHERING 상태로 바뀐다.

 

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

 

PNAME    PVAL2
--------  ---------------------------------------------------------------------
STATUS   AUTOGATHERING


7. 아직 20분이 경과되지 않으면 아래와 같이 진행 상황을 확인할 수 있다.

 

SQL> alter session set nls_date_format='mm-dd-YYYY HH24:MI';
SQL> select c1, to_date(c2), to_date(c3) from system.mystats;

 

C1                               TO_DATE(C2)        TO_DATE(C3)
--------------------------------------------------------------
MANUALGATHERING      12-21-2004 02:46   12-21-2004 02:46
 

8. MANUAL하게 STOP을 하려면 아래와 같이 수행한다.

 

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'STOP',

stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM'); 


<주의> 만약, DBMS_STATS.GATHER_SYSTEM_STATS 함수를 이용하여 STOP을
실행한 이후에 아래 9번과 같은 명령을 수행 시 PVAL2가 badstats 로 나온다면 이것은

interval 이 충분하지 않아 그 기간 동안에 작업 WORKLOAD가 부족하기 때문입니다.


따라서, interval 동안 SINGLE BLOCK I/O와 multiblock I/O에 대한 workload가 오라클에서

통계정보를 측정하기 위한 기준치 만큼의 자원 소비가 없으면 정보가 부족하여 발생하는

현상으로 보입니다. 즉, 지속적인 i/o에 대한 통계치를 제대로 수집하지 못해서 발생하는

것입니다. 시스템에 대한 통계 정보 수집 시 interval은 최소한 60분, default로 120분입니다. 
  

9. 20분이 지나면 아래와 같은 spool 결과를 볼 수 있고, sys.aux_stats$ table에서

    PVAL2 가 AUTOGATHERING에서 COMPLETED 로 변경됨을 알 수 있다. 
     
SQL> select SNAME, PNAME, PVAL1, PVAL2 from SYS.AUX_STATS$

         where pname ='STATUS';

 

SNAME                  PNAME              PVAL1        PVAL2                    
--------------------------------------------------------------------------------
SYSSTATS_INFO     STATUS                               COMPLETED     


10. 통계정보를 보관하기 위해 생성한 'mystats' 라는 stats table을 drop하려면 
     이와 같이 DBMS_STATS.DROP_STAT_TABLE procedure를 수행한다. 

 

EXECUTE DBMS_STATS.DROP_STAT_TABLE( ownname => 'SYSTEM', stattab =>'mystats');

 

< 권장 사항 >

dbms_stats.gather_system_stats package 사용 시 이 두 개의 파라미터를 Tuning 후

parameter file에 설정하면 보다 나은 CBO의 효과를 얻을 수 있다.

  ~ optimizer_index_caching  (range of value : 0 to 100)
  ~ optimizer_index_cost_adj (range : 1 to 10000)


< 참고 >

이와 같은 시스템 통계정보 생성 작업을 dbms_job.submit 프로시져를 이용하여 수행할 수도 있다.

이렇게 job으로 등록했을 경우에는 통계정보 생성 중에 dba_jobs 와 dba_jobs_running 뷰를

조인하여 확인해 보면 WHAT 컬럼에 아래와 같은 job scheduling이 걸리는 것을 볼 수 있다.

 

sql>dbms_stats.gather_system_stats(gathering_mode => 'AUTO_STOP', statown => 'SYS');


Example
----------

위의 작업에 대한 spool on 수행 결과입니다.

 

SQL> select * from sys.aux_stats$;

SQL> SELECT count(*) from system.mystats;

SQL> select job, what from user_jobs;                                    
SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

SQL> select * from sys.aux_stats$;

SQL> exec DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM');

BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM'); END;

*
ERROR at line 1:
ORA-20003: Unable to import system statistics 
ORA-06512: at "SYS.DBMS_STATS", line 5437 
ORA-06512: at line 1


SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'STOP',

          stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');

 

SQL> select c1, to_date(c2), to_date(c3) from system.mystats;

C1                             TO_DATE(C2)      TO_DATE(C3)                     
------------------------------ ---------------- ----------------                
COMPLETED              12-21-2004 02:46   12-21-2004 03:10                
                                                                               

SQL> select SNAME, PNAME, PVAL1, PVAL2 from SYS.AUX_STATS$

         where pname ='STATUS';

 

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode=>'START',

         stattab => 'mystats', statid => 'DAY', statown => 'SYSTEM');

 

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

                                                                               

SQL> select count(*) from system.mystats;

SQL> select PNAME, PVAL2 from SYS.AUX_STATS$ where pname ='STATUS';

                                                                               

SQL> exec DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM');

BEGIN DBMS_STATS.IMPORT_SYSTEM_STATS ('mystats','DAY','SYSTEM'); END;

*
ERROR at line 1:
ORA-20003: Unable to import system statistics 
ORA-06512: at "SYS.DBMS_STATS", line 5437 
ORA-06512: at line 1


SQL> spool off

 

위의 DBMS_STATS.IMPORT_SYSTEM_STATS 수행 시 ORA-20003 error에 대해서는

이 문서 <Note:154601.1>을 참조하시기 바랍니다. User table로부터의 system 통계 정보를

Data Dictionary로 transfer할 때 ORA-20003 에러에 대한 해결방법입니다.

 

위 과정 전체를 JOB으로 등록하여 DBMS_JOB.SUBMIT으로 수행하는 방법은 아래

참고 노트에 나와 있습니다.


Reference Documents
-------------------
<Note:153761.1>
<Note:149560.1>

Posted by 1010
02.Oracle/DataBase2015. 5. 22. 14:17
반응형
테이블의 정확한 통계 수집을 위해 미리 알려줘야할 정보를 수집하는 DBMS함수

call dbms_stats.getther_table_stats('user',''table');


Posted by 1010
02.Oracle/DataBase2015. 5. 22. 14:14
반응형

출처 : http://blog.naver.com/darkturtle/50070269004


B. GATHER_SCHEMA_STATS Procedure

스키마 OWNER 의 모든OBJECT 에 대한 통계정보를 수집한다.

전체적으로 GATHER_TABLE_STATS 와 같고 아래 부분이 다르다.

스키마 유저만 지정 하면 GATHER OPTION  이 작동하여 모든 Object 의 정보가 수집 된다.



DBMS_STATS.GATHER_SCHEMA_STATS (

   ownname          VARCHAR2,

   estimate_percentNUMBER   DEFAULT to_estimate_percent_type

                 (get_param('ESTIMATE_PERCENT')),

   block_sample     BOOLEAN DEFAULT FALSE,

   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),

   degree           NUMBER   DEFAULT

 to_degree_type(get_param('DEGREE')),

  granularity      VARCHAR2 DEFAULTGET_PARAM('GRANULARITY'),

   cascade          BOOLEAN  DEFAULT

to_cascade_type(get_param('CASCADE')),

   stattab          VARCHAR2 DEFAULT NULL,

   statid           VARCHAR2 DEFAULT NULL,

   options          VARCHAR2 DEFAULT 'GATHER',

   objlist          OUT      ObjectTab,

   statown          VARCHAR2 DEFAULT NULL,

   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (

                                     get_param('NO_INVALIDATE')),

  force             BOOLEAN DEFAULT FALSE);

  

DBMS_STATS.GATHER_SCHEMA_STATS (

   ownname          VARCHAR2,

   estimate_percentNUMBER   DEFAULT to_estimate_percent_type

                                               (get_param('ESTIMATE_PERCENT')),

   block_sample     BOOLEAN DEFAULT FALSE,

   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),

   degree           NUMBER  DEFAULT to_degree_type(get_param('DEGREE')),

  granularity      VARCHAR2 DEFAULTGET_PARAM('GRANULARITY'),

   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),

   stattab          VARCHAR2 DEFAULT NULL,

   statid           VARCHAR2 DEFAULT NULL,

   options          VARCHAR2 DEFAULT 'GATHER',

   statown          VARCHAR2 DEFAULT NULL,

   no_invalidate    BOOLEAN DEFAULT to_no_invalidate_type (

                                     get_param('NO_INVALIDATE'),

   force            BOOLEAN DEFAULT FALSE);

 

 

 

Parameter

Description

Options

통계정보가 수집될 Object 에 대한 설명

GATHER : 스키마의 모든 Object 에 대해서 수집하라.

GATHER AUTO : 필요한 모든 정보를 자동으로 수집하라.

    GATHER AUTO 시에 설정 가능한 값은 ownname, stattab, statid, objlist,

     ,statown 만 가능하고 나머지 파라미터는 무시 된다.

GATHER STALE : *_tab_modification views 를 통해서 결정된 상태 변경이

     많은 테이블만 대상으로 통계정보를 수집한다.

GATHER EMPTY : 현재 통계정보가 존재하는 테이블만 대상으로

통계정보를 수집한다.

LIST AUTO : GATHER AUTO 옵션으로 진행될 Object list  Return 한다.

LIST STALE : GATHER STALE 옵션으로 진행될 Object list  Return 한다.

LIST EMPTY : GATHER EMPTY 옵션으로 진행될 Object list  Return 한다.

Objlist

통계정보가 상했거나없는 Object List


1. 통계정보가맛간 테이블 리스트 출력

<!--[if !supportLists]--><!--[endif]-->

SQL> setserveroutput on

SQL> declare

    mystaleobjs dbms_stats.objecttab;

    begin

    -- check whether there is any stale objects

   dbms_stats.gather_schema_stats(ownname=>'SCOTT', -

 options=>'LISTSTALE',objlist=>mystaleobjs);

    for i in 1 .. mystaleobjs.count loop

   dbms_output.put_line(mystaleobjs(i).objname);

    end loop;

    end;

  /


<!--[if !supportLists]-->2. 자동으로 통계정보수집 시에 대상 테이블 리스트 출력

SQL> setserveroutput on

SQL>declare

     mystaleobjs dbms_stats.objecttab;

    begin

    dbms_stats.gather_schema_stats(ownname=>‘SCOTT’,

options=>'LISTAUTO',objlist=>mystaleobjs);

    for i in 1 .. mystaleobjs.count loop

   dbms_output.put_line(mystaleobjs(i).objname);

    end loop;

   end ;

    /

PL/SQL proceduresuccessfully completed.

 

 

 

 -- 통계정보 생성이 필요한 테이블 리스트업 (DATA 이행등, STALE TABLE LIST 추출) 
SELECT *
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_OWNER IN ( 'SCOTT' )
and INSERTS > 100000 ;

-- 통계정보 생성 스크립트 작성 
SELECT 'exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'''||table_owner||''',TABNAME=>'''||table_name||''');'
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_OWNER IN ( 'SCOTT' )
and INSERTS > 100000 ;


Posted by 1010