SQLコード：
ALTER FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
DECLARE @IP_Address varchar(255);

SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;

Return @IP_Address;
END

1.DBObjectのインポート
import com.mongodb.DBObject;
import com.mongodb.util.JSON;

2.$match
DBObject match = (DBObject) JSON.parse(“{$match:{‘status’:’1′,’time’:
{$gte:'”+beginDay+”‘,$lte:'”+endDay+”‘}}}”);
(beginDay-開始日付；endDay-終了日付)

3.$group
DBObject group = (DBObject) JSON.parse(“{$group:{_id:’$userId’,
count:{$sum:1},total:{$sum:’$amount’},maxSigle:{$max:’$amount’}}}”);

検索操作
List<DBObject> list = new ArrayList<DBObject>();
list.add(match); list.add(group);
AggregationOutput Output = mongoTemplate.getCollection(“tblName”).aggregate(list);

4.List<DBObject>のサンプル
DBObject match = (DBObject) JSON.parse(“{$match:{‘status’:{$in:[‘0′,’1′]},
type:{$ne:11},’time’:{$gte:'”+beginDay+”‘,$lte:'”+endDay+”‘}}}”);

DBObject sort = (DBObject) JSON.parse(“{$sort:{‘count’:-1}}”);//ソート

List<DBObject> list = new ArrayList<DBObject>();
list.add(match);
list.add(group);
list.add(groupMatch);
list.add(project);
list.add(sort);
AggregationOutput catchOutPut = mongoTemplate.getCollection(“tblName”)
.aggregate(list);//検索結果

5.BasicDBObject()の操作
BasicDBObject groupIndex = new BasicDBObject();
BasicDBObject distinctQuery = new BasicDBObject();
distinctQuery.put(“userId”, new BasicDBObject(“$in”, userIds));//UserIds配列
BasicDBObject initIndex = new BasicDBObject();
initIndex.put(“count”, 0);//countの値をリセット

Javaコード
String reduce = “function(doc, prev) {if(doc.status==0){prev.count+= 1;};}”;
List<Map<String, Object>> basicDBList = (List<Map<String, Object>>) mongoTemplate.
getCollection(“Collection”).group(groupIndex, distinctQuery, initIndex, reduce, null);

1.Query.put()
date条件検索
BasicDBObject Query = new BasicDBObject();
Query.put(“time”,new BasicDBObject(“$gte”, date + ” 00:00:00″)
.append(“$lte”, date + ” 23:59:59″));

or条件検索
BasicDBList values = new BasicDBList();
values.add(new BasicDBObject(“status”, new BasicDBObject(“$exists”,false)));
values.add(new BasicDBObject(“status”, 0));
Query.put(“$or”, values);

2.match
DBObject Match = new BasicDBObject(“$match”, Query);

3.GroupBy.put()
BasicDBObject GroupBy = new BasicDBObject();
GroupBy.put(“userId”, “$userId”);
GroupBy.put(“status”, “$status”);

4.Select.put()
DBObject Select = new BasicDBObject(“_id”, GroupBy);
Select.put(“count”, new BasicDBObject(“$sum”, 1));

5.$group
DBObject Group = new BasicDBObject(“$group”, Select);
import com.mongodb.AggregationOutput;
import org.springframework.data.mongodb.core.MongoTemplate;
AggregationOutput Output = mongoTemplate.getCollection(“tablenameAA”)
.aggregate(Match, Group);
検索結果：
Iterable<DBObject> map = Output.results();
//map loop
for (DBObject dbObject : map) {
Map<String, Object> resultMap = (Map<String, Object>) dbObject.get(“_id”);
Integer userId = Integer.parseInt(CommUtil.toString(ausgMap.get(“userId”)).replace(“.0″,””));

//count計算
Integer count = Integer.parseInt(CommUtil.toString(dbObject.get(“count”)));

}

構文
SELECT <table1.カラム> <table2.カラム>
INTO <新規テーブル> FROM <コピー元テーブル1> <コピー元テーブル2>
例
SELECT
Employee.LastName
, Orders.OrderNo
INTO Employee_OrderNew_Tbl
FROM
Employee
INNER JOIN Orders
ON Employee.Id_PNO = Orders.Id_PNO

1.会員情報登録ID を集計し、登録ID順で返す。
SELECT
LISTAGG(USERID, ‘, ‘) WITHIN GROUP (ORDER BY USERID) AS CAC_ID
FROM
MEMBER_TBL
WHERE
NO = 123456;

2.会員情報登録IDを集計し、登録日付順で返す。
SELECT
LISTAGG(USERID) WITHIN GROUP (ORDER BY CREATE_DATE) AS CAC_ID
FROM
MEMBER_TBL;

DENSE_RANKは同じ順位が複数あった場合でも、その後順位は飛ばされない。

SQL構文
SELECT
UT.ADDR
, DENSE_RANK() OVER (PARTITION BY UT.NO ORDER BY UT.LEVEL) AS USERID
FROM
USER_TBL UT

1.ロール確認
SELECT * FROM DBA_ROLE_PRIVS;

ロール作成
CREATE ROLE cmsrole;

ロール削除
DROP ROLE cmsrole;

2.ロールをユーザに付与
GRANT cmsrole TO ssuser;

ロールをユーザから削除
REVOKE cmsrole FROM ssuser;

3.ロールから権限を削除
REVOKE create table FROM cmsrole;

ロールからオブジェクト権限を削除
REVOKE insert ON user_tbl FROM cmsrole;

ロールからオブジェクト権限をすべて削除
REVOKE all ON user_tbl FROM cmsrole;

1.乱数の取得
SELECT DBMS_RANDOM.RANDOM() AS RAND FROM DUAL;

RANDDIG
1510339687

2.(0.0～1.0)の乱数
SELECT DBMS_RANDOM.VALUE AS RANDVAL FROM DUAL;

RANDVAL
0.54222246419720795713470908705902261109

3.自然数の乱数
SELECT DBMS_RANDOM.NORMAL ANDNML FROM DUAL;

ANDNML
0.9091261603929238106428661337349463173353

4.ランダムの英小文字
SELECT DBMS_RANDOM.STRING(‘L’,8) AS ENVA FROM DUAL;

ENVA
gfrzxtea
ランダムの英大文字

SELECT DBMS_RANDOM.STRING(‘U’,8) AS ENVA FROM DUAL;

ENVA
MHAIGYDQ

8.(6,9)の浮動小数点の乱数
SELECT DBMS_RANDOM.VALUE(6,9) AS BETRAND FROM DUAL;

BETRAND
7.52199019866418662856371710185996781826

1. NVL(対象文字列,変換したい文字列)
SELECT
NVL(A.NO, ”)
, NVL(A.NAME, ”)
FROM
TEST_TBL1 A;

2. CASEでNULLを0に置換
SELECT
CASE
WHEN B.NUM IS NULL
THEN 0
ELSE B.NUM
END
FROM
TEST_TBL2 B;

3.Likeで文字列の一部と比較
SELECT
NVL(C.NO, ”)
, C.NAME
FROM
TEST_TBL3 C WHERE LOWER(C.EMAIL) LIKE ‘cft%’ ;

1.SELECT SYSDATE FROM DUAL;
2018/03/05 16:21:50

2.SELECT TRUNC(SYSDATE-1, ‘MM’) FROM DUAL;
2018/03/01

SELECT TRUNC(SYSDATE-1, ‘YYYY’) FROM DUAL;
2018/01/01

3.SELECT TRUNC(SYSDATE -1 ) FROM DUAL;
2018/03/04