wego

Oracle复合数据结构一个需要注意点

2009-01-06T03:06:00Z

CREATE OR REPLACE TYPE t_str_array AS TABLE OF VARCHAR2 (500);

SELECT b.COLUMN_VALUE FROM bd_pc_type a, TABLE (t_str_array ('手提', '工作站', '手提机')) b

WHERE a.code(+) = b.COLUMN_VALUE AND a.code IS NULL

无记录返回, 若改成

SELECT a.code, b.COLUMN_VALUE FROM bd_pc_type a, TABLE (t_str_array ('手提', '工作站', '手提机')) b

WHERE a.code(+) = b.COLUMN_VALUE AND a.code IS NULL

有记录返回

LINQ 无短路

2008-12-27T05:03:00Z

昨天在用 LINQ 时, 发现其不像其 if 或其它条件表达式一样会自动产生短路效应. 若各位刚好用到时请加以注意, 避免不必要的运行时错误.

请见如下 Code:

public static void SetPageInfo(BasePage page)
{
DataTable rights = GetOperationRights();

var relatedRights = from right in rights.AsEnumerable()

                        where (right.Field<string>("module_code") == null || right.Field<string>("module_code").Equals(page.Module)) &&
                              (right.Field<string>("function_group_code") == null || right.Field<string>("function_group_code").Equals(page.Group)) &&
                              (right.Field<string>("function_key") == null || right.Field<string>("function_key").Equals(page.Member))

                        orderby right.Field<string>("module_code") descending,
                                right.Field<string>("function_group_code") descending,
                                right.Field<string>("function_key") descending

                        select new
                        {
                            CanQry = right.Field<decimal>("can_qry"),
                            CanNew = right.Field<decimal>("can_new"),
                            CanEdit = right.Field<decimal>("can_edt"),
                            CanDel = right.Field<decimal>("can_del")
                        };

var relatedRight = relatedRights.FirstOrDefault();

...

}

当实际某 right.Field<string>("module_code") 为 null 时, 该 Code 会产生运行时错误. 故 where 子句需改成如下:

where (right.Field<string>("module_code") == null || (right.Field<string>("module_code") ?? String.Empty).Equals(page.Module)) &&
(right.Field<string>("function_group_code") == null || (right.Field<string>("function_group_code") ?? String.Empty).Equals(page.Group)) &&
(right.Field<string>("function_key") == null || (right.Field<string>("function_key") ?? String.Empty).Equals(page.Member))

令外, LINQ 中已把原 DBNull 转成 DotNet 的 null, 所以不用 Convert.IsDBNull(right.Field<string>("module_code")).

UpdatePanel & UpdateProgress & Download File 的相关问题

2008-10-20T05:40:00Z

程序中有时会因要执行业务比较复杂, 运行时间长的一系统操作, 需要显示如 "执行中 .." 等用户友好提示, 可用ASP.NET AJAX 的 UpdatePanel & UpdateProgress 控件完成. 但若执行结果需要下载文件时, 则会出现执行完成但文件却下载不了的情况. 因为 UpdateProgress 是异步进行中. 解决方法可以建立一个 'DownloadFile.aspx' 页面作辅助, 请见如下主要代码片断

------- Data.aspx -----------

<asp:ScriptManager ID="ScriptManager" runat="server" />

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>

               <asp:UpdateProgress ID="UpdateProgress1" runat="server" DisplayAfter="2" DynamicLayout="true">
                            <ProgressTemplate>
                                <img src='<%=CommonUrl.Images %>wait_clock.gif' />
                                <span id='spanProgressNote'></span>
                            </ProgressTemplate>
              </asp:UpdateProgress>

</ContentTemplate>
</asp:UpdatePanel>

Sys.WebForms.PageRequestManager.getInstance().add_initializeRequest(initializeRequestHandler);

function initializeRequestHandler(sender, e) {
var postBackElementId = e.get_postBackElement().id;

            switch (postBackElementId) {
                case '<%= btnSearch.ClientID %>':
                    spanProgressNote.innerHTML = 'Loading ..';
                    break;

                case '<%= imgExport.ClientID %>':
                    spanProgressNote.innerHTML = 'Exporting ..';
                    break;
              }

}

</script>

------- Data.cs -----------

protected void imgExport_Click(object sender, ImageClickEventArgs e)
{
...

// SimpleWebUtils.DownloadFile(Response, filePath, Path.GetFileName(filePath)); // 不能直接在当前页下载文件

ScriptManager.RegisterStartupScript(this, this.GetType(), "RegisterStartupScript"
, String.Format("window.navigate('../Control/'DownloadFile.aspx?path={0}');", Server.UrlEncode(filePath)), true);

//ScriptManager.RegisterStartupScript(this, this.GetType(), "RegisterStartupScript"
// , String.Format("window.navigate('{0}');", fileUrl), true); // 用这种方式可以不用 ''DownloadFile.aspx' 页面但下载文件直接打开是用网页方式而不是实际文件方式 (如: Excel)

}

------- DownloadFile.cs -----------

protected void Page_Load(object sender, EventArgs e)
{
        string path = Request.QueryString["path"];
        SimpleWebUtils.DownloadFile(Response, path, Path.GetFileName(path));
        ScriptManager.RegisterStartupScript(this, this.GetType(), "RegisterStartupScript", "window.close();", true);
}

当心 Oracle Procedure 中的 IN 参数也会被你无意中修改了它的值

2008-10-08T07:29:00Z

在 Oracle Procedure 中使用 IN 参数似乎很好地对该参数起到保护作用, 其实不然! 请看如下 Code

-- Spec Part

PACKAGE TEST
IS
g1 NUMBER;

PROCEDURE proc1;

PROCEDURE proc2 (p1 IN NUMBER);
END;

-- Body Part

PACKAGE BODY TEST
IS
   PROCEDURE proc1
   IS
   BEGIN
      g1 := 1;
      proc2 (g1);
      DBMS_OUTPUT.put_line ('g1=' || g1);
   END;

   PROCEDURE proc2 (p1 IN NUMBER)
   IS
      c1   CONSTANT NUMBER := p1;
   BEGIN
      --p1 := p1 + 1;  这句是编译不通过的, 似乎已对 IN 参数保护了.
      DBMS_OUTPUT.put_line ('p1=' || p1);
      DBMS_OUTPUT.put_line ('c1=' || c1);
      g1 := g1 + 1; -- 这句间接地改变了 IN 参数值!
      DBMS_OUTPUT.put_line ('after p1=' || p1);   -- 已经被改了!
      DBMS_OUTPUT.put_line ('after c1=' || c1);
   END;
END;

运行 proc1 后输出:

p1=1
c1=1
after p1=2
after c1=1
g1=2

总结:

1) 若要确保过程内任意地方都能访问到 IN 参数的初始值, 需要使用附加常量

2) 尽可能少用全局变量 (风险很大)

3) 其它非 Oracle 数据库也可能出现该情况.

ASP.NET 自定义控件之解析标签内属性内嵌

2008-09-04T03:13:00Z

实现如下:

<smic:Note runat=server ID='NoteInfo'>
     <b>Development Tools</b>
     <br />
     C#, JAVA, PHP
</smic:Note>

代码片段:

[ToolboxBitmap(typeof(Label))]
[AspNetHostingPermission(SecurityAction.Demand, Level = AspNetHostingPermissionLevel.Minimal)]
[AspNetHostingPermission(SecurityAction.InheritanceDemand, Level = AspNetHostingPermissionLevel.Minimal)]
[ToolboxData("<{0}:Note runat=\"server\"></{0}:Note>"), DefaultProperty("Message"), ParseChildren(false), Designer("System.Web.UI.Design.WebControls.LabelDesigner, System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")]
[ControlBuilder(typeof(LabelControlBuilder)), ControlValueProperty("Message"), DataBindingHandler("System.Web.UI.Design.TextDataBindingHandler, System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")]
public class Note : WebControl
{

        [PersistenceMode(PersistenceMode.InnerDefaultProperty), Bindable(true), Localizable(true)]
        public string Message
        {
            get { return ViewState["Message"] as string; }
            set { ViewState["Message"] = value; }
        }

        protected override void AddParsedSubObject(object obj)        // 必须 Override WebControl 的 AddParsedSubObject 方法
        {
            if (this.HasControls())
            {
                base.AddParsedSubObject(obj);
            }
            else if (obj is LiteralControl)
            {
                this.Message = ((LiteralControl)obj).Text;
            }
            else
            {
                string message = this.Message;
                if (message.Length != 0)
                {
                    this.Message = string.Empty;
                    base.AddParsedSubObject(new LiteralControl(message));
                }
                base.AddParsedSubObject(obj);
            }
        }

        protected override void Render(HtmlTextWriter output)
        {
            output.AddAttribute("class", "container");
            output.RenderBeginTag(HtmlTextWriterTag.Div);
            output.Write(this.Message);
            output.RenderEndTag();
        }

}

Lambda 不支持 yield

2008-07-08T07:50:00Z

今天将 Lambda 与 yield 结合起来使用, 结果编译不通过. (如下)

public IEnumerable<int> DeletedIDs
{
    get
    {

        var deletedIDs = new int[]{123, 456};

Array.ForEach(deletedIDs, i => (yield return i));

}
}

要写成如下:

public IEnumerable<int> DeletedIDs
{
    get
    {

       var deletedIDs = new int[]{123, 456};

       foreach (int deletedID in deletedIDs)
           yield return deletedID;
    }
}

Oracle 处理数据高并发响应

2008-07-03T01:00:00Z

某些系统可能对系统应付数据高并发的响应要求比较高 (如: 飞行航空订票系统). 本文采用 Oracle 的 lock table 解决之.

Procedure 中关键 code 如下:

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

lock table tb_book_ticket in share mode;

...

insert into tb_book_ticket (id, scheduled_flight, ticket_no, book_time) values (ID.nextval, v_scheduled_flight, v_ticket_no, SYSDATE); -- mark1

... -- mark2

select count(*) into v_book_count from tb_book_ticket where scheduled_flight = v_scheduled_flight; -- mark3

if v_book_count <= v_book_max_count then

v_result := 'book successfully';

commit;

else

v_result := 'book unsuccessfully';

rollback;

end if;

return v_result;

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

以上有2个地方需注意:

1) 采用 lock table table_name in share mode 方式而非 lock table table_name in exclusive mode 提高并发性处理, 尽量减少不必要执行等待.

2) 按常规思路, 可能会将 mark1 与 mark3 顺序交换, 但此举不行

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

select count(*) into v_book_count from tb_book_ticket where scheduled_flight = v_scheduled_flight; -- mark3

... -- mark2

if v_book_count < v_book_max_count then

insert into tb_book_ticket (id, scheduled_flight, ticket_no, book_time) values (ID.nextval, v_scheduled_flight, v_ticket_no, SYSDATE); -- mark1

-- mark4

commit;

end if;

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

试想若 userA 执行到 mark4, 而同时 userB 执行到 mark3, 就可能会出现由于 userA 尚未commit 而导致 userB 订到机票但实际没有座位的情况.

若确希望使用常规逻辑来实现, 需要用到 Oracle 的表的读锁功能 (精细访问策略), 而此非本文范围, 且其对于SYS用户无效.

window.history.go(-N) 及页面自动刷新数据的解决方案

2008-06-03T08:07:00Z

使用情况:

1) 用户在 [查询页] 选取所需的 DropDown 项或填写查询的 TextBox 值

2) 点击结果表格的一条记录链接进入 [编辑页] (不是弹出 Form 形式, 而是整个页面跳转)

3) 在 [编辑页] 中修改有关项后保存 (保存后可能又进行修改后又保存)

4) 按 [Close] button 回 [查询页]

5) 要求 [查询页] 保留之前的查询状态 (即各DropDown, TextBox 项保留用户刚才选取或录入的值), 而表格数据则要求自动刷新 (因为刚才在 [编辑页] 中可能作了某些修改)

注意事项:

1) 因为 HTTP 的无状态性, 所以采用 Session 保留页面间的信息传递 (注意: 不能使用 URL 传递方式, 如: window.location.href = 'Search.aspx?serverRefreshed=yes', 因为这样页面原状态将不可还原 )

2) 由于 window.history.go(-N) 后页面的 cs 代码不会执行, 故采用 Ajax 方式调用服务端代码

代码片断:

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

Edit.aspx:

<asp:HiddenField ID="hfPostCount" runat="server"/>
<asp:button id="btnSave" runat="server" Text="Save" OnClientClick="document.getElementById(hfDataSaved).value = 'Yes'" OnClick="btnSave_Click"></asp:button>
<input type=button id="btnClose" value="Close" onclick="FormClose();" />

function FormClose()
{
    if (document.getElementById(hfDataSaved).value == 'Yes') RRSCore.SessionInfo.SetServerRefreshed("No");
    var postCount = document.getElementById(hfPostCount).value;
    postCount++;
    window.history.go(-postCount);
}

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

Edit.aspx.cs:

protected void Page_Load(object sender, EventArgs e)
{
   if (!this.IsPostBack)
       hfPostCount.Value = "0";
   else
       hfPostCount.Value = (int.Parse(hfPostCount.Value) + 1).ToString();

Page.ClientScript.RegisterStartupScript(Page.GetType(), "SetClientVariable", "hfPostCount='" + this.hfPostCount.ClientID.ToString() + "';", true);
}

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

Search.aspx

window.onload = function () {
if (RRSCore.SessionInfo.GetServerRefreshed().value == 'No') document.forms[0].submit();
RRSCore.SessionInfo.SetServerRefreshed("Yes");
}

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

SessionInfo.cs (Ajax Source)

namespace RRSCore
{

public class SessionInfo
{

         [AjaxPro.AjaxMethod]
         public static void SetServerRefreshed(string serverRefreshed)
         {
              HttpContext.Current.Session["ServerRefreshed"] = serverRefreshed;
         }

         [AjaxPro.AjaxMethod]
         public static string GetServerRefreshed()
         {
               return HttpContext.Current.Session["ServerRefreshed"];
         }

}

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

Dot Net 2.0 使用3.5的 Linq

2008-05-14T05:38:00Z

注意事项:

1) 前提是开发者机器要装有Framework 3.5, 引入Reference : System.Core

2) 若发布服务器没装 Framework 3.5, 需手工把 System.Core.dll 加入到 bin 下再发布

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

using System.Linq;

string[] list = new string[] { "mary", "tom", "david", "cathy", "tom" }; // 除数组外, 其它集合类也可 (如: List, Hashtable , Dictionary)
IEnumerable<string> result;

//
Console.WriteLine("--- 'FirstOrDefault' --");
result = Enumerable.Where<string>(list,
                 delegate(string item)
                 {
                     return item == "tom";
                 });
Console.WriteLine(Enumerable.FirstOrDefault(result));

//
Console.WriteLine("--- 'OrderBy' --");
result = Enumerable.OrderBy<string, string>(list,
                delegate(string item)
                {
                    return item;
                });
foreach (string str in result)
{
    Console.WriteLine(str);
}

//
Console.WriteLine("--- 'Min' --");
string result_str = Enumerable.Min<string, string>(list,
                 delegate(string item)
                 {
                     return item;
                 });
Console.WriteLine(result_str);

//
Console.WriteLine("--- 'Distinct' --");
result = Enumerable.Distinct<string>(list);
foreach (string str in result)
{
Console.WriteLine(str);
}

//
Console.WriteLine("--- 'All' --");
bool result_bool = Enumerable.All<string>(list, delegate(string item)
                 {
                     return item.CompareTo("d") > 0;
                 });
Console.WriteLine(result_bool);

//
Console.WriteLine("--- 'Distinct' & 'OrderBy' & 'Where' --");
result = Enumerable.Distinct<string>(Enumerable.OrderBy<string, string>(Enumerable.Where<string>(list,
                delegate(string item)
                {
                    return item == "tom" || item == "david";
                }),
                delegate(string item)
                {
                    return item;
                }));
foreach (string str in result)
{
    Console.WriteLine(str);
}

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

输出如下:

--- 'FirstOrDefault' --
tom
--- 'OrderBy' --
cathy
david
mary
tom
tom
--- 'Min' --
cathy
--- 'Distinct' --
mary
tom
david
cathy
--- 'All' --
False
--- 'Distinct' & 'OrderBy' & 'Where' --
david
tom

Oracle 泛型使用

2008-05-07T03:48:00Z

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

-- procedure part

FUNCTION IIF (p_bool IN BOOLEAN, p_result1 IN ANYDATA, p_result2 IN ANYDATA)
   RETURN ANYDATA
IS
   atype                ANYTYPE;
   data_type_argument   EXCEPTION;
BEGIN
   IF p_result1.gettype (atype) <> p_result2.gettype (atype)
   THEN
      RAISE data_type_argument;
   END IF;

   CASE p_result1.gettype (atype)
      WHEN DBMS_TYPES.typecode_number
      THEN
         RETURN CASE
            WHEN p_bool
               THEN ANYDATA.convertnumber (p_result1.accessnumber)
            ELSE ANYDATA.convertnumber (p_result2.accessnumber)
         END;
      WHEN DBMS_TYPES.typecode_varchar2
      THEN
         RETURN CASE
            WHEN p_bool
               THEN ANYDATA.convertnvarchar2 (p_result1.accessvarchar2)
            ELSE ANYDATA.convertnvarchar2 (p_result2.accessvarchar2)
         END;
      WHEN DBMS_TYPES.typecode_date
      THEN
         RETURN CASE
            WHEN p_bool
               THEN ANYDATA.convertdate (p_result1.accessdate)
            ELSE ANYDATA.convertdate (p_result2.accessdate)
         END;
   END CASE;
EXCEPTION
   WHEN data_type_argument
   THEN
      DBMS_OUTPUT.put_line
          ('The data type of the ''IIF'' function both result parameter is different');
      RETURN NULL;
END;

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

-- caller part

v_str                   VARCHAR2 (50)
         := iif (1 > 2,
                 ANYDATA.convertvarchar2 ('Yes'),
                 ANYDATA.convertvarchar2 ('No')
                ).accessnvarchar2;

-- 类似于 C# 使用 string v_str = iif<string> (1> 2, 'Yes', 'No');

v_num NUMBER
:= iif (1 > 2, ANYDATA.convertnumber (1), ANYDATA.convertnumber (0)).accessnumber;

-- 类似于 C# 使用 int v_num = iif<int> (1> 2, 1, 0);

DBMS_OUTPUT.put_line (v_str); -- output 'No'

DBMS_OUTPUT.put_line (v_num); -- output '0'

数学对编程的重要

2008-02-23T03:14:00Z

且看如下2例

(1) 算出1加到100的总和。

低效:　

int sum = 0; 　　

for (int i=1; i<=100; i++)

sum += i;

高效:　
int sum = ((1 + 100) * (100 - 1 + 1)) / 2; 或更精简的 int sum = (101 * 100) >> 2;

(2) 计算1加到10000，奇数和偶数的总和。

低效:　

         int oddSum = 0; 　　
          int evenSum = 0; 　　
          for (int i=1; i<=10000; i++) 　　
         { 　　
               if (i % 2 == 0) 　　
                     evenSum += i; 　　

           else
　　        oddSum += i;
         }

高效:　

          int sum = (10001 * 10000) >> 2; 　　
            int evenSum = 5001 * 5000; 　　
         int oddSum = sum - evenSum;

    1到10000之间的偶数总和是是2+4+6+...+10000，把它们全部除以2的话会变成1+2+3+...+5000，所以1到10000偶数的总和不就是1加到5000的两倍
---------------------------------------------

可见, 千万不要把曾经和你相伴十多年的数学课程知识给遗忘掉噢 !!

ASP.NET Download Web File

2007-10-23T04:10:00Z

今天写了个 DownloadWebFile 函数, 采用读取 --> 输出方式. 但发现当第二次打开 Download 的文件时, 文件报已损坏错误. code 如下:

protected void DownloadFile(string url)
  {
   System.Net.HttpWebRequest Myrq = (System.Net.HttpWebRequest)System.Net.HttpWebRequest.Create(url);
   System.Net.HttpWebResponse myrp = (System.Net.HttpWebResponse)Myrq.GetResponse();
   Stream respStream = myrp.GetResponseStream();

Response.Expires = 0;
Response.Clear();

Response.ContentType = myrp.ContentType;

   const int BUFFER_SIZE = 16*1024;
   byte[] buffer = new byte[BUFFER_SIZE];
   int length;

   length = respStream.Read(buffer, 0, BUFFER_SIZE);
   while(length > 0)
   {
      Response.BinaryWrite(buffer);
      length = respStream.Read(buffer, 0, BUFFER_SIZE);
   }

   Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", "Competitor's list.xls"));
   Response.AddHeader("pragma", "no-cache");
   Response.AddHeader("expires", "0");
   Response.AddHeader("cache-control", "must-revalidate, post-check=0, pre-check=0");

   Response.Flush();
   Response.End();
  }

后在 Wicky 同志的帮助下, 采用读取 --> 中传站存放 -> 输出方式得已解决 !

Response.Expires = 0;
Response.Clear();

Response.ContentType = myrp.ContentType;

   const int BUFFER_SIZE = 16*1024;
   byte[] buffer = new byte[BUFFER_SIZE];
   int length;

   MemoryStream ms = new MemoryStream();

   length = respStream.Read(buffer, 0, BUFFER_SIZE);
   while(length > 0)
   {
      ms.Write(buffer, 0, length);
      length = respStream.Read(buffer, 0, BUFFER_SIZE);
   }

Response.BinaryWrite(ms.GetBuffer());

   Response.Flush();
   Response.End();
  }

FireBird 求指定行数记录

2007-10-08T08:52:00Z

数据库 FireBird 求指定行数记录的 sql 语法不是Oracle的rownum，也不是SQL Server的Top n. 而是使用以下比较特别的形式.

1) select First 2 skip 3 * from table1 order by field1 desc -- 取第4行到第5行

2) select * from table1 order by field1 desc ROWS 2 TO 4 -- 取第2行到第4行

3) select First 3 skip 0 * from table1 order by field1 desc -- 同 SQL Server 的 Top 3

First n skip m形式会在忽略掉m行后，取前n行；

而rows m to n形式，则是从第m行取到第n行。

Oracle竖变横一句通

2007-09-19T06:32:00Z

设有如下表 T

A   B
-----------
1   01
1   02
1   03
2   04
2   05
3   06

执行如下 sql

select a,replace(max(sys_connect_by_path(b,';')),';','') b
from (select a,b,
(row_number() over(order by a,b)
+ dense_rank() over(order by a)) rn,
min(b) over(partition by a) mb
from t
)
start with b = mb
connect by rn-1 = prior rn
group by a

可得如下结果

A   B
--------------------
1   010203
2   0405
3   06

ORACLE 的 GROUP BY ROLLUP 与 ratio_to_report 激情碰撞

2007-09-12T08:44:00Z

假设有如下表: SALE_AMOUNT

BU AMOUNT

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

CRD 100

SHK 200

RSJ 500

运行如下 sql

SELECT DECODE (GROUPING_ID (BU), 1, 'Total', BU) as BU, SUM (AMOUNT) as AMOUNT, (ratio_to_report (SUM (AMOUNT)) OVER ()) * 2 as AMOUNT_RATIO FROM SALE_AMOUNTGROUP BY ROLLUP (BU)

ORDER BY BU

可得如下结果

BU AMOUNT AMOUNT_RATIO

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

CRD 100 0.0125

RSJ 500 0.625

SHK 200 0.25

Total 800 1

当它们激情碰撞时, 有两点是需要注意的:

1) ratio_to_report (SUM (AMOUNT)) OVER () 中的SUM () 须写于内层而不能写于外层. 如SUM (ratio_to_report (AMOUNT) OVER ())

2) ratio_to_report 后须乘以 2, 因为ratio_to_report (SUM (AMOUNT)) OVER () 已除多一个 total 了. 即AMOUNT / (2 * TOTAL_ AMOUNT )