最近有一个需求,要做站内的关键词搜索。关键词要保存到Oracle的表,如果表里没有该记录就新增,如果有记录就将搜索次数加1。虽然看似简单,可写SQL时却不知从何下手。找了一下,原来是这样写法的。记录一下。
MERGE INTO KEYWORD T1
USING (SELECT :NAME AS NAME,:PIN_YIN AS PIN_YIN,:PIN_YIN_FIRST AS PIN_YIN_FIRST,1 As TIMES FROM dual) T2
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE SET T1.TIMES = T1.TIMES + 1
WHEN NOT MATCHED THEN
INSERT(NAME,PIN_YIN,PIN_YIN_FIRST,TIMES) VALUES(T2.NAME,T2.PIN_YIN,T2.PIN_YIN_FIRST,T2.TIMES)
另外站内的搜索关键词高亮脚本如下
//定义的配置
Keyword = {
//如果配置,则只搜索ID为此的元素,否则搜索整个BODY
elementid: 'content',
//是否匹配整个单词
exact: false,
//最大的搜索节点,避免文档过大时的假死
max_nodes: 1000,
//是否在加载时调用
onload: true,
//样式的名称
style_name: 'keyword',
//样式是否有后缀,true则可以使不同的关键词有不同的颜色
style_name_suffix: true,
//前一跳转页的地址,此参数只用于调试时
debug_referrer: '',
//用于指定固定要做高亮处理的关键词
light_key: '',
//用于指定固定要做链接处理的关键词
link_key: ''
};
// 网页处理的主函数
Keyword.main = function(strTask_type) {
// If 'debug_referrer' then we will use that as our referrer string
// instead.
// q是网页从何跳转来的URL地址,也可以自己定。q要做解码处理
// e是网页的内容或者是指定元素的内容
// lightkey是要加亮的关键字列表
// linkkey是要做链接的关键字列表
task_type = strTask_type;
var q = Keyword.debug_referrer ? Keyword.debug_referrer : document.referrer;
var e = document.getElementById(Keyword.elementid) ? document.getElementById(Keyword.elementid) : document.body;
q = decodeURIComponent(q);
//高度处理
var lightkey = Keyword.GetLightKeyList(q);
if (lightkey && e)
{
Keyword.lightElement(e, lightkey);
}
//链接处理
var linkkey = Keyword.GetLinkKeyList(q);
if (linkkey && e)
{
Keyword.linkElement(e, linkkey,strTask_type);
}
};
// 返回要加亮的关键字列表
Keyword.GetLightKeyList = function(referrer) {
var query = null;
if (referrer.match(/^.*key=([^&]+)&?.*$/i)){
query = RegExp.$1;
}
query = query + " " + Keyword.light_key;
if (query){
query = query.replace(//'|"/g, '');
query = query.split(/[/s,/(,/),/|,-,/+/.]+/);
}
return query;
};
//返回要加链接的关键字列表
Keyword.GetLinkKeyList = function(referrer) {
//此处从网页控件得到固定要做链接处理的关键词
try
{
Keyword.link_key = document.form1.txb_CommonKeyWord.value;
}
catch(err)
{
}
var query = null;
if (referrer.match(/^.*link=([^&]+)&?.*$/i)){
query = RegExp.$1;
}
query = query + " " + Keyword.link_key;
if (query){
query = query.replace(//'|"/g, '');
query = query.split(/[/s,/(,/),/|,-,/+/.]+/);
}
return query;
};
// 设置高亮
Keyword.lightElement = function(elm, lightkey) {
if (!lightkey || elm.childNodes.length == 0)
return;
var qreLight = new Array();
for (var i = 0; i < lightkey.length; i ++) {
lightkey[i] = lightkey[i].toLowerCase();
if (Keyword.exact)
qreLight.push('//b'+lightkey[i]+'//b');
else
qreLight.push(lightkey[i]);
}
qreLight = new RegExp(qreLight.join("|"), "i");
var stylemapper = {};
for (var i = 0; i < lightkey.length; i ++) {
if (Keyword.style_name_suffix)
stylemapper[lightkey[i]] = Keyword.style_name+(i % 5);
else
stylemapper[lightkey[i]] = Keyword.style_name+0;
}
var textproc = function(node) {
var match = qreLight.exec(node.data);
if (match) {
var val = match[0];
var k = '';
var node2 = node.splitText(match.index);
var node3 = node2.splitText(val.length);
var span = node.ownerDocument.createElement('SPAN');
node.parentNode.replaceChild(span, node2);
span.className = stylemapper[val.toLowerCase()];
span.appendChild(node2);
return span;
}
else{
return node;
}
};
Keyword.walkElements(elm.childNodes[0], 1, textproc);
};
// 设置链接
Keyword.linkElement = function(elm, linkkey) {
if (!linkkey || elm.childNodes.length == 0)
return;
var qreLink = new Array();
for (var i = 0; i < linkkey.length; i ++) {
linkkey[i] = linkkey[i].toLowerCase();
if (Keyword.exact)
qreLink.push('//b'+linkkey[i]+'//b');
else
qreLink.push(linkkey[i]);
}
qreLink = new RegExp(qreLink.join("|"), "i");
var textproc = function(node) {
var match = qreLink.exec(node.data);
if (match) {
var val = match[0];
var k = '';
var node2 = node.splitText(match.index);
var node3 = node2.splitText(val.length);
var link = node.ownerDocument.createElement('A');
node.parentNode.replaceChild(link, node2);
link.href = "FullTextSearch.aspx?searchtype=txt&key=" + val;
link.title = "搜索“" + val + "”";
link.target="_blank";
link.appendChild(node2);
return link;
}
else{
return node;
}
};
Keyword.walkElements(elm.childNodes[0], 1, textproc);
};
//用于对网页元素的遍历
Keyword.walkElements = function(node, depth, textproc) {
var skipre = /^(script|style|textarea)/i;
var count = 0;
while (node && depth > 0) {
count ++;
if (count >= Keyword.max_nodes) {
var handler = function() {
Keyword.walkElements(node, depth, textproc);
};
setTimeout(handler, 50);
return;
}
if (node.nodeType == 1) { // ELEMENT_NODE
if (!skipre.test(node.tagName) && node.childNodes.length > 0) {
node = node.childNodes[0];
depth ++;
continue;
}
} else if (node.nodeType == 3) { // TEXT_NODE
node = textproc(node);
}
if (node.nextSibling) {
node = node.nextSibling;
} else {
while (depth > 0) {
node = node.parentNode;
depth --;
if (node.nextSibling) {
node = node.nextSibling;
break;
}
}
}
}
};