杂

sqlplus
var cur refcursor
exec test1(:cur);
print cur;

 

--行转列
with temp as(
  select '小明' username, '语文' course , 98 score from dual union all
  select '小明' username, '数学' course , 88 score from dual union all
  select '小明' username, '英语' course , 77 score from dual union all
  select '刚豆' username, '语文' course , 99 score from dual union all
  select '刚豆' username, '数学' course , 96 score from dual union all
  select '刚豆' username, '英语' course , 100 score from dual
)
select t.username,
  sum(decode(t.course, '语文', score, null)) as CHINESE,
  sum(decode(t.course, '数学', score, null)) as MATH,
  sum(decode(t.course, '英语', score, null)) as ENGLISH
from temp t
group by t.username

 

--表变量使用
declare
  type my_tab is table of base_fi2_busiunit%rowtype
  index by binary_integer;
  new_tab my_tab;
 begin
  select * bulk collect into new_tab from base_fi2_busiunit;
  for i in 1..new_tab.count loop
  dbms_output.put_line(new_tab(i).f_dwmc);
  end loop;
end;

 

--带游标存储过程调用
declare v_a int; p_c sys_refcursor;
begin
  P_QS_YDKH('2014', '10', 22, p_c);
end;

wm_concat(column)函数
把列值以","号分隔起来,并显示成一行
中文乱码：wm_concat(to_char(....))

示例:按ID分组合并name
SQL> select id,wm_concat(name) name from test group by id;

SQL> select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT';

 

--c#获取游标
private void fenye()
{
    OracleConnection conn = new OracleConnection("Data Source=FI2;User ID=SKYFI2;Password=Skyadmin123;Unicode=True");
    conn.Open();

    try
    {
      OracleCommand cmd = new OracleCommand();
      cmd.Connection = conn;
      cmd.CommandType = CommandType.StoredProcedure;

      //调用存储过程查询数据
      cmd.CommandText = "P_DTQS";
      OracleParameter[] parameters = new OracleParameter[1];
      parameters[0] = new OracleParameter("rcursor", OracleType.Cursor);
      parameters[0].Direction = ParameterDirection.Output;

      foreach (OracleParameter parameter in parameters)
      {
          cmd.Parameters.Add(parameter);
      }
      //执行命令
      OracleDataAdapter oda = new OracleDataAdapter(cmd);
      DataSet ds = new DataSet();
      oda.Fill(ds);

    }
    catch (Exception ex)
    {
      throw;
    }
    finally
    {
      conn.Close();
    }
    
}