CREATE TABLE student ( id NUMBER, name VARCHAR2(10), book varchar2(10) ); / SQL> insert into student values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));1 row insertedSQL> insert into student values (1,'bbb',to_date('2002-07-02','yyyy-mm-dd'));1 row insertedSQL> insert into student values (1,'ccc',to_date('2002-07-03','yyyy-mm-dd'));1 row insertedSQL> commit;Commit completeSQL> select * from student; ID SNAME DATE_TIME ---------- ---------- ----------- 1 aaa 2002-7-1 1 bbb 2002-7-2 1 ccc 2002-7-3 create or replace function get_sname(p_id in varchar2) return varchar2 is Result varchar2(100);
cursor rad_cursor(v_id varchar2) is select sname from student where id=v_id ; v_count Number; begin v_count:=0; Result:=''; for v_sor in rad_cursor(p_id) loop if v_count=0 then Result:=v_sor.sname; v_count:=v_count+1; else Result:=Result||','||v_sor.sname; end if; end loop; return(Result); end get_sname; /SQL> select id,get_sname(id) from student group by id; ID GET_SNAME(ID) ---------- -------------------------------------------------------------------------------- 1 aaa,bbb,ccc
我打算使用ado,请告诉我command.commandtext应该怎么给。
asp调用函数: <% set rs3 = Server.CreateObject("ADODB.Recordset")rs3.ActiveConnection = MM_conn_STRING rs3.Source = "SELECT TYPENAME, TYPENUM,to_char(getdate(week),'yyyy-mm-dd') v_date FROM EXPERTBC_VIEW WHERE EXPERTNO = '"&Request.QueryString("id")&"' ORDER BY getdate(week)" //getdate()是oracle一个函数 rs3.CursorType = 0 rs3.CursorLocation = 2 rs3.LockType = 3 rs3.Open() %>asp调用过程: set cm1 = Server.CreateObject("ADODB.Command") cm1.ActiveConnection = MM_conn_STRING cm1.CommandType = 1 cm1.CommandTimeout = 0 cm1.Prepared = true cm1.CommandText = "{call pkg_test.get(?)}"//pkg_test.get()是oracle过程 set para = cm1.Parameters para.Append cm1.CreateParameter("p_id",3,1,,1) set rs = Server.CreateObject("ADODB.Recordset") set rs=cm1.Execute %>
在sqlplus中还是不行,请看我在sqlplus中的操作: SQL> desc usertree; Name Null? Type ----------------------------------------- -------- ------------------- USER_ID NUMBER FATHER NUMBERSQL> select text from user_source where name='GETSONS';TEXT ----------------------------------------------------------------------- FUNCTION "GETSONS" (UserID in varchar2) return varchar2 as str1 varchar2(10); sons varchar2(500); cursor c1 is select user_id from usertree where father = UserID; begin open c1; sons := ''; fetch c1 into str1;TEXT ----------------------------------------------------------------------- while c1%found loop sons := sons + ',' + str1; fetch c1 into str1; end loop; close c1; return sons; end getsons; 20 rows selected. SQL> GETSONS('100016'); SP2-0734: unknown command beginning "GETSONS('1..." - rest of line ignored.最后一个命令应怎样写?到底怎样调用这个定义好的函数?
有几处地方出错了,修改如下: create or replace FUNCTION GETSONS(UserID in varchar2) return varchar2 as
sons varchar2(500); cursor c1(p_userid varchar2) is select user_id from usertree where father=p_userid; v_count Number; begin v_count:=0; sons:=''; for v_sor in c1(userid) loop if v_count=0 then sons:=v_sor.user_id; v_count:=v_count+1; else sons:= sons||','||v_sor.user_id; end if; end loop; return sons; end getsons;
看漏了眼,把参数类型改为number。 create or replace FUNCTION GETSONS(UserID in number) return varchar2 as sons varchar2(500); cursor c1(p_userid number) is select user_id from usertree where father=p_userid; v_count Number; .......
TO 楼上 我写的那个stored function 其实也对,只不过似乎只能被嵌入在sql语句中用。 那么总结一下,似乎有 1. stored function 被调用时要被嵌入在sql语句中。 2. stored procedure才可以用{CALL xxx(?)}调用(ASP+ADO)。而且返回为空或者recordset类型。 有什么错误否?此外, create or replace FUNCTION GETSONS(UserID in number) return varchar2 is
此外, create or replace FUNCTION GETSONS(UserID in number) return varchar2 is 这里使用is 和as有什么区别吗?
(
id NUMBER,
name VARCHAR2(10),
book varchar2(10)
);
/
SQL> insert into student values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));1 row insertedSQL> insert into student values (1,'bbb',to_date('2002-07-02','yyyy-mm-dd'));1 row insertedSQL> insert into student values (1,'ccc',to_date('2002-07-03','yyyy-mm-dd'));1 row insertedSQL> commit;Commit completeSQL> select * from student; ID SNAME DATE_TIME
---------- ---------- -----------
1 aaa 2002-7-1
1 bbb 2002-7-2
1 ccc 2002-7-3
create or replace function get_sname(p_id in varchar2) return varchar2 is
Result varchar2(100);
cursor rad_cursor(v_id varchar2) is
select sname from student
where id=v_id ;
v_count Number;
begin
v_count:=0;
Result:='';
for v_sor in rad_cursor(p_id) loop
if v_count=0 then
Result:=v_sor.sname;
v_count:=v_count+1;
else
Result:=Result||','||v_sor.sname;
end if;
end loop;
return(Result);
end get_sname;
/SQL> select id,get_sname(id) from student group by id; ID GET_SNAME(ID)
---------- --------------------------------------------------------------------------------
1 aaa,bbb,ccc
<%
set rs3 = Server.CreateObject("ADODB.Recordset")rs3.ActiveConnection = MM_conn_STRING
rs3.Source = "SELECT TYPENAME, TYPENUM,to_char(getdate(week),'yyyy-mm-dd') v_date FROM EXPERTBC_VIEW WHERE EXPERTNO = '"&Request.QueryString("id")&"' ORDER BY getdate(week)" //getdate()是oracle一个函数
rs3.CursorType = 0
rs3.CursorLocation = 2
rs3.LockType = 3
rs3.Open()
%>asp调用过程:
set cm1 = Server.CreateObject("ADODB.Command")
cm1.ActiveConnection = MM_conn_STRING
cm1.CommandType = 1
cm1.CommandTimeout = 0
cm1.Prepared = true
cm1.CommandText = "{call pkg_test.get(?)}"//pkg_test.get()是oracle过程
set para = cm1.Parameters
para.Append cm1.CreateParameter("p_id",3,1,,1)
set rs = Server.CreateObject("ADODB.Recordset")
set rs=cm1.Execute
%>
SQL> desc usertree;
Name Null? Type
----------------------------------------- -------- -------------------
USER_ID NUMBER
FATHER NUMBERSQL> select text from user_source where name='GETSONS';TEXT
-----------------------------------------------------------------------
FUNCTION "GETSONS" (UserID in varchar2)
return varchar2
as
str1 varchar2(10);
sons varchar2(500);
cursor c1 is
select user_id from usertree where father = UserID;
begin
open c1;
sons := '';
fetch c1 into str1;TEXT
-----------------------------------------------------------------------
while c1%found
loop
sons := sons + ',' + str1;
fetch c1 into str1;
end loop;
close c1;
return sons;
end getsons;
20 rows selected.
SQL> GETSONS('100016');
SP2-0734: unknown command beginning "GETSONS('1..." - rest of line ignored.最后一个命令应怎样写?到底怎样调用这个定义好的函数?
create or replace FUNCTION GETSONS(UserID in varchar2)
return varchar2
as
sons varchar2(500);
cursor c1(p_userid varchar2) is
select user_id from usertree where father=p_userid;
v_count Number;
begin
v_count:=0;
sons:='';
for v_sor in c1(userid) loop
if v_count=0 then
sons:=v_sor.user_id;
v_count:=v_count+1;
else
sons:= sons||','||v_sor.user_id;
end if;
end loop;
return sons;
end getsons;
create or replace FUNCTION GETSONS(UserID in number)
return varchar2
as
sons varchar2(500);
cursor c1(p_userid number) is
select user_id from usertree where father=p_userid;
v_count Number;
.......
我写的那个stored function 其实也对,只不过似乎只能被嵌入在sql语句中用。 那么总结一下,似乎有
1. stored function 被调用时要被嵌入在sql语句中。
2. stored procedure才可以用{CALL xxx(?)}调用(ASP+ADO)。而且返回为空或者recordset类型。
有什么错误否?此外, create or replace FUNCTION GETSONS(UserID in number)
return varchar2
is
return varchar2
is
这里使用is 和as有什么区别吗?