这样写 create or replace procedure www as V_goods_no base_cust.goods_no%type ; //定义 V_goods_no和表中goods_no相同的类型,这是oracle规定的. V_tax_explain base_cust.tax_explain%type ; begin //返回值放在V_goods_no ,V_tax_explain
select goods_no ,tax_explain into V_goods_no ,V_tax_explain from base_cust; end www;
不好意思, 返回记录集必须用cursor, 上面只适合 where .... and rownum=1 ;即返回唯一值。
SQL是SQL SERVER吧? 不同的数据库,语法规范不同是正常的。。一点也不奇怪。。
select goods_no ,tax_explain from base_cust; 一定要有into 子句 select goods_no ,tax_explain into v_1,v_2 from base_cust;
create or replace procedure www as v_1 base_cust.goods_no%type; v_2 base_cust.tax_explain%type; begin select goods_no ,tax_explain into :v_1,v_2 from base_cust; end www;
to: lzj033(南方的狼) 我编译时通过,但是exec www 时就报错啦!
南方的狼说的很对,但是我还有一种写法,作用和他的一样 create or replace procedure www(v_1 base_cust%type,v_2 base_cust%type) asbegin select goods_no ,tax_explain into :v_1,v_2 from base_cust; end www;
错了,没有那个冒号,不好意思,我是COPY的南方的狼的部分代码,但是我想你会看出这个错误的
怎么还是提示:无效SQL语句的啊?!!
学习oracle的话,暂时忘了sql server吧. :)
如果是返回结果集,请使用游标. create or replace procedure www(v_1 varchar2,v_2 varchar2) as Cursor c_base_cust Select goods_no ,tax_explain FROM base_cust; begin Open c_base_cust; Loop Fetch c_base_cust Into v_1 ,v_2; Exit When c_base_cust%NotFound; DBMS_OUTPUT.PUT_LINE(v_1,v_2); End Loop; Close c_base_cust; end www;
不能只是select
create or replace procedure www as
V_goods_no base_cust.goods_no%type ;
//定义 V_goods_no和表中goods_no相同的类型,这是oracle规定的.
V_tax_explain base_cust.tax_explain%type ;
begin
//返回值放在V_goods_no ,V_tax_explain
select goods_no ,tax_explain
into V_goods_no ,V_tax_explain
from base_cust;
end www;
返回记录集必须用cursor,
上面只适合 where .... and rownum=1 ;即返回唯一值。
不同的数据库,语法规范不同是正常的。。一点也不奇怪。。
一定要有into 子句
select goods_no ,tax_explain into v_1,v_2 from base_cust;
v_1 base_cust.goods_no%type;
v_2 base_cust.tax_explain%type;
begin
select goods_no ,tax_explain into :v_1,v_2 from base_cust;
end www;
我编译时通过,但是exec www 时就报错啦!
create or replace procedure www(v_1 base_cust%type,v_2 base_cust%type) asbegin
select goods_no ,tax_explain into :v_1,v_2 from base_cust;
end www;
create or replace procedure www(v_1 varchar2,v_2 varchar2) as
Cursor c_base_cust
Select goods_no ,tax_explain FROM base_cust;
begin
Open c_base_cust;
Loop
Fetch c_base_cust Into v_1 ,v_2;
Exit When c_base_cust%NotFound;
DBMS_OUTPUT.PUT_LINE(v_1,v_2);
End Loop; Close c_base_cust;
end www;