我一直使用SQL Server,最近不得不更换oracle数据库,想把原有的SQL里的存储过程移植到oracle中,可是一开始就遇到问题
如在sql中,我有一个查询test1表的存储过程sp_test1create proc sp_test1
as
begin
select *
from test1
end
我尝试着在oracle中写成create or replace procedure sp_test1 is
begin
select *
from test1
end在PL/SQL下就报错,想请问一下我该怎么才能达到目的
如在sql中,我有一个查询test1表的存储过程sp_test1create proc sp_test1
as
begin
select *
from test1
end
我尝试着在oracle中写成create or replace procedure sp_test1 is
begin
select *
from test1
end在PL/SQL下就报错,想请问一下我该怎么才能达到目的
create or replace procedure sp_test1 is
type t is table of test1%rowtype;
begin
select * into t
from test1;
end;
Compilation errors for PROCEDURE FXC.SP_TEST1Error: PLS-00321: ¸³ÖµÓï¾ä×ó±ßµÄ±í´ïʽ 'T' ²»ÕýÈ·
Line: 4
Text: select * into tError: PL/SQL: ORA-00904: ÎÞЧÁÐÃû
Line: 6
Text: from tbs_code;Error: PL/SQL: SQL Statement ignored
Line: 4
Text: select * into t
create or replace procedure sp_test1 is
type t is table of test1%rowtype index by binary_integer;
test t;
begin
select * bulk collect into test from test1;
end;
type t is table of test1%rowtype;
v_t t;
begin
select * bulk collect into v_t
from test1;
end;
is
begin
select * from test1;
end;
create or replace procedure sp_test1 is
type t is table of sys.users%rowtype index by binary_integer;
test t;
begin
select * bulk collect into test from sys.users;
end;
我把语句这样运用了,可是编译都没过去--PROCEDURE SYS.SP_TEST1
--PLS-00597: INTO列表中的表达式'TEST' 无效
--select * bulk collect into test from sys.users;--ORA-00904: 无效列名
--select * bulk collect into test from sys.users;--SQL Statement ignored
--select * bulk collect into test from sys.users;到底是哪里不对啊
type t is table of sys.users%rowtype index by binary_integer;
test t;
begin
select * bulk collect into test from sys.users;
end;这段代码没问题的 我编译是通过的
create or replace procedure sp_test1 is
type t is table of sys.users%rowtype index by binary_integer;
test t;
begin
select * bulk collect into test from sys.users;end;
这是我的代码,我是在PL/SQL里编译的 提示的语句前面已经贴过了。
9.0.1.0.0其实我想实现的功能就很简单,就是向查询 select * from sys.users 一样,当执行存储过程名sp_test1的时候,存储过程返回查询的结果集就行因为我是之前是使用sqlserver的,对于这样的运用在sqlserver里很简单create proc sp_test1
as
begin
select * from sys.users
end就行,可是在oracle里这样的语句显然是不行了,能帮我写一句嘛,谢谢
下面是返回表users的结果集,以游标返回
create or replace procedure sp_test1
(o_table out sys_refcursor) as
begin
open o_table for select * from sys.users;
end;