从sql server移植一存储过程,很简单,跑一条sql,返回一个结果集,仿照sql server 写成:
create or replace procedure se_samelevel(menuId in varchar2, sameMenus in varchar2) is
v_sql varchar(1000);
begin
v_sql := 'SELECT MenuIndex help_index, MenuLevel help_level,Caption name, '||
'(decode (MenuIndex,'||menuId||',''true'',''false'')) selected, '||
'Methods eventhandler, DisplayMode displaymode, '||
'UpFileName upfilename, DownFileName downfilename, '||
'ParentIndex help_parent '||
'FROM SYSMENUS '||
'WHERE MenuIndex IN ('||sameMenus ||') '||
'ORDER BY displaymode, menuindex ';
execute immediate v_sql;
end se_samelevel;然后,执行
declare
-- Local variables here
i integer;
begin
se_samelevel('21','21');
end;没有返回预期结果集,所有操作在sqlserver中都可以
create or replace procedure se_samelevel(menuId in varchar2, sameMenus in varchar2) is
v_sql varchar(1000);
begin
v_sql := 'SELECT MenuIndex help_index, MenuLevel help_level,Caption name, '||
'(decode (MenuIndex,'||menuId||',''true'',''false'')) selected, '||
'Methods eventhandler, DisplayMode displaymode, '||
'UpFileName upfilename, DownFileName downfilename, '||
'ParentIndex help_parent '||
'FROM SYSMENUS '||
'WHERE MenuIndex IN ('||sameMenus ||') '||
'ORDER BY displaymode, menuindex ';
execute immediate v_sql;
end se_samelevel;然后,执行
declare
-- Local variables here
i integer;
begin
se_samelevel('21','21');
end;没有返回预期结果集,所有操作在sqlserver中都可以
解决方案 »
- 在EXCEL中有回车换行的内容导入Oracle中却不自动换行,变成内容都连一行了,请问怎么解决?
- plsql中用select ... into ... from 提示missing keyword的错误
- 【疑问】查询的to_number问题。
- 两个不同的表空间可以创建相同表名的表吗?
- sql*load使用序列的问题
- number类型问题
- java.sql.sqlexception Protocol violation
- O
- 关于过程创建中的一个问题
- 输入日期的变量出现 ora-00904 :"NOV":invalid indentifier ?
- 进来看看?那些地方出的问题,我怀疑是数据库的设置问题?(我抛分!)
- 关于oracle的建库的问题
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
Java程式:
……
CallableStatement call = conn.prepareCall("{ call p_test(?) }");
call.registerOutParameter(1, OracleTypes.CURSOR);// 註冊out參數的SQL數據類型
call.execute();
ResultSet rs=(ResultSet)call.getObject(1);// 取得得數据結果集合
while(rs.next())
……
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/create or replace procedure se_samelevel(menuId in varchar2, sameMenus in varchar2,p_rc out pkg_test.myrctype) is
v_sql varchar2(1000);
begin
v_sql := 'SELECT MenuIndex help_index, MenuLevel help_level,Caption name, (decode (MenuIndex,'||menuId||',''true'',''false'')) selected,Methods eventhandler, DisplayMode displaymode, UpFileName upfilename, DownFileName downfilename, ParentIndex help_parent FROM SYSMENUS WHERE MenuIndex IN ('||sameMenus ||') ORDER BY displaymode, menuindex ';
open p_rc for v_sql;
end se_samelevel;
/declare
v_rc pkg_test.myrctype;
v1 ..
v2 ..
....
begin
se_samelevel('21','21',v_rc);
loop
fetch v_rc into v1,v2,....;
exit when v_rc%notfound;
dbms_output.put_line(v1||v2||...);
end loop;
end;
/