说明:
为了让CYQ.Data 框架支持Oracle,这几天对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
-- 定义变量
newtableName varchar2 ( 4000 );
rowStart int ;
rowEnd int ;
mySql varchar2 ( 8000 );
whereOnly varchar2 ( 8000 );
OrderOnly varchar2 ( 400 );
begin
newtableName: = tableName;
mySql: = ' select count(*) from ' || tableName;
if whereStr is not null and length(whereStr) > 0
then
rowStart: = instr(whereStr, ' order by ' );
if rowStart > 0
then
whereOnly: = substr(whereStr, 1 ,rowStart - 1 ); -- 取得条件
OrderOnly: = substr(whereStr,rowStart, length(whereStr) - rowStart + 1 ); -- 取得排序方式(order by 字段 方式)
else
whereOnly: = whereStr;
OrderOnly: = '' ;
end if ;
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 * from ' || tableName || whereOnly || OrderOnly;
else
-- 计算起始和结束索引
rowStart: = (pageIndex - 1 ) * pageSize + 1 ;
rowEnd: = rowStart + pageSize - 1 ;
mySql: = ' select * from (select t.*,RowNum as rn from (select * from ' || newtableName || whereOnly || OrderOnly || ' ) t) where rn between ' || rowStart || ' and ' || rowEnd;
end if ;
open ResultCursor for mySql;
-- dbms_output.put_line('SQL=>'||mySql);
end SelectBase;
end MyPackage;
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
-- 定义变量
newtableName varchar2 ( 4000 );
rowStart int ;
rowEnd int ;
mySql varchar2 ( 8000 );
whereOnly varchar2 ( 8000 );
OrderOnly varchar2 ( 400 );
begin
newtableName: = tableName;
mySql: = ' select count(*) from ' || tableName;
if whereStr is not null and length(whereStr) > 0
then
rowStart: = instr(whereStr, ' order by ' );
if rowStart > 0
then
whereOnly: = substr(whereStr, 1 ,rowStart - 1 ); -- 取得条件
OrderOnly: = substr(whereStr,rowStart, length(whereStr) - rowStart + 1 ); -- 取得排序方式(order by 字段 方式)
else
whereOnly: = whereStr;
OrderOnly: = '' ;
end if ;
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 * from ' || tableName || whereOnly || OrderOnly;
else
-- 计算起始和结束索引
rowStart: = (pageIndex - 1 ) * pageSize + 1 ;
rowEnd: = rowStart + pageSize - 1 ;
mySql: = ' select * from (select t.*,RowNum as rn from (select * from ' || newtableName || whereOnly || OrderOnly || ' ) t) 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 ;
ResultCursor MyPackage.MyCursor;
ResultCount int ;
begin
MyPackage.SelectBase( 1 , 2 , ' USERS ' , ' id>1 order by id ' ,ResultCount,ResultCursor);
end ;
说明:
为写这段存储过程历经了半天,需要看语法,又要调试,最后采用步步注释法才一条语句一条语句的写到最后。
测试调试也弄了半天,要定义游标传进去才行。
测试调试也弄了半天,要定义游标传进去才行。
继续说明:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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里没有。
存储过程的代码里面有几个小问题,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:数据库表/视图的字段结构查询:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
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:查询所有表/视图/存储过程
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 ' ; -- 所有表
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 ' ; -- 所有表