折腾Oracle问题小菜记(三)

时间：2010-09-28 来源：路过秋天

说明：

为了让CYQ.Data 框架支持Oracle，这几天对Oracle进行了基本探索，并把中间遇到的问题轻轻的记录了下来,与大伙共享。

总共有三篇：

1：初折腾Oracle问题小记

2：初折腾Oracle问题小记二

3：就是本篇了：折腾Oracle问题小菜记(三)

本篇又有新突破，再记录一下：

1：自己写了一条分页存储过程，也是CYQ.Data默认产生的存储过程：

create or replace package MyPackage as
type MyCursor is ref cursor;
procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  resultCount out int, resultCursor out MyCursor);
end MyPackage;

create or replace package Body MyPackage is
procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  resultCount out int, resultCursor out MyCursor)
  is
  --定义变量
  rowStart  int;
  rowEnd    int;
  mySql varchar2(8000);
  whereOnly varchar2(8000);
  OrderOnly varchar2(400);
  begin
    mySql:='select count(*) from '||tableName;
    whereOnly:=whereStr;
    rowStart:=instr(whereStr,'order by');
    if whereStr is not null and  rowStart>0
      then
        whereOnly:=substr(whereStr, 1,rowStart-1);    --取得条件
        OrderOnly:=substr(whereStr,rowStart, length(whereStr)-rowStart+1);    --取得排序方式(order by 字段方式)
        end if;

    if length(whereOnly)>1
      then
       whereOnly:=' where '|| whereOnly;
       mySql:=mySql||whereOnly;
        end if;
        execute immediate mySql into resultCount;
       -- dbms_output.put_line('查询总条数SQL=>'||whereStr||'--'||mySql||resultCount);
    --执行查询,查询总条数


            --不分页查所有

        if pageIndex=0 and pageSize=0
        then
        mySql:='select count(*) from '||tableName||whereOnly||OrderOnly;
       else
--计算起始和结束索引

        rowStart:=(pageIndex-1)*pageSize+1;
        rowEnd:=rowStart+pageSize-1;
        mySql:='select * from (select t.*,RowNum as rn from ('||tableName||') t'||whereOnly||OrderOnly||') where rn between '||rowStart||' and '||rowEnd;

        end if;
    open ResultCursor for mySql;
dbms_output.put_line('SQL=>'||mySql);
    end SelectBase;
  end MyPackage;

执行测试语句：

declare
  ResultCursor MyPackage.MyCursor;
  ResultCount int;
  begin
  MyPackage.SelectBase(1,2,'USERS','id>1 order by id',ResultCount,ResultCursor);
  end;

说明：

为写这段存储过程历经了半天，需要看语法，又要调试，最后采用步步注释法才一条语句一条语句的写到最后。
测试调试也弄了半天，要定义游标传进去才行。

继续说明：

Oracle里的存储过程的可以有Package，等于一个名称空间了。

存储过程的代码里面有几个小问题，oracle->比较->mssql：

1：|| 为链接符号即+号

2：instr 函数和sql的函数charindex函数一样,只是里面的头两个参数的顺序要反过来。

3：substr函数和sql的函数substring函数一样。

4：length函数和sql的函数len函数一样。

5：if ...then...else end if, mssql里为 if begin end

6：“;"号一行语句一个，mssql里没有。

2：数据库表/视图的字段结构查询：

select COLUMN_NAME as ColumnName,
Data_length*2 as MaxSize,
case NULLABLE when 'Y' then 1 else 0 end as IsNullable,
0 as ReadOnly,
DATA_TYPE as SqlType
from USER_TAB_COLS where TABLE_NAME=upper(:TableName) order by COLUMN_ID

3：存储过程参数放在另一个表，独立查询：

select argument_Name as ColumnName,-1 as MaxSize,0 as IsNullable,0 as ReadOnly,'int' as SqlType from user_arguments where object_name=upper(:TableName)

4：查询所有表/视图/存储过程

Select object_name From user_objects Where object_type='TRIGGER'; --所有触发器
Select object_name From user_objects Where object_type='PROCEDURE'; --所有存储过程
Select object_name From user_objects Where object_type='VIEW'; --所有视图
Select object_name From user_objects Where object_type='TABLE'; --所有表