试了很久,总是出错,求救~~~~~~~问题一:简单SQL
declare @aa number(1)
set @aa=1
select * from tablename where fieldname=@aa
问题二:带参数存储过程
create procedure mytest @aa number(1)
As
select * from tablename where fieldname=@aa
以上是SQL2000的,都是返回多条记录,请问在oracle里怎么写??本人刚接触oracle,高手给力啊~~~~~~
declare @aa number(1)
set @aa=1
select * from tablename where fieldname=@aa
问题二:带参数存储过程
create procedure mytest @aa number(1)
As
select * from tablename where fieldname=@aa
以上是SQL2000的,都是返回多条记录,请问在oracle里怎么写??本人刚接触oracle,高手给力啊~~~~~~
As
begin
select * from tablename where fieldname=aa
end;
用sql语句就就可以了 不需要存储过程啊
begin
select * from tablename where fieldname=aa;
end mytest;出错啊“在此select语句中缺少into语句”select出来时多条记录的
declare
aa number := 1;
select * from tablename where fieldname=aa;
end;
/
aa number(1);
begin
aa:=1;
for i in(select col1,col2 from tablename where fieldname=aa) loop
dbms_output.put_line(i.col1||' '||i.col2);
end loop;
end;
--问题二:带参数存储过程
create procedure mytest(aa number,cur out sys_refcursor)
As
begin
open cur for select * from tablename where fieldname=aa;
end;--调用
sqlplusvar cur refcursor
exec mytest(1,:cur)
print cur
"@"是sql2000的,oracle里运行出错的
输入 aa 的值: 7788
原值 1: select * from emp where empno=&aa
新值 1: select * from emp where empno=7788 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 900 20scott@YPCOST> /
输入 aa 的值: 7369
原值 1: select * from emp where empno=&aa
新值 1: select * from emp where empno=7369 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 900 20
declare
aa number := 1;
myrow tablename%rowtype;
begin
select * into myrow from tablename where fieldname=aa;
dbms_output.put_line('可以用myrow.field来打印信息');
end;
SQL> var cur refcursor
REFCURSOR not supportedSQL> exec mytest(1,:cur)begin mytest(1,:cur); end;Cursor variable :CUR cannot be nil
create procedure mytest(aa number,cur out sys_refcursor)
As
begin
open cur for select * from tablename where fieldname=aa;
end;====这个在pl/sql里怎么执行,得到所有返回的结果集啊
2 cur SYS_REFCURSOR;
3 rec info_right%rowtype;
4 BEGIN
5 pro_menu1(2,cur);
6 loop
7 fetch cur into rec;
8 exit when cur%notfound;
9 --dbms_output.put_line(rec.*);
10 dbms_output.put_line(rec.rightname);
11 end loop;
12 close cur;
13 END;
14 /PL/SQL procedure successfully completed======执行成功了,可是只返回“PL/SQL procedure successfully completed”,
可是我要查看所有结果集,应该怎么写?
sqlplus 下运行var cur refcursor
exec mytest(1,:cur)
print cur