CREATE OR REPLACE FUNCTION GET_NAME
(V_ADDRESS IN VARCHAR2)
RETURN VARCHAR2
AS
V_NAME VARCHAR2(40);
BEGIN
SELECT SNAME
INTO V_NAME
FROM STUDENT
WHERE SADDRESS = V_ADDRESS;
RETURN V_NAME;
END;SID SNAME SBIRTH SCLASS SADDRESS
S06037201 黄小明 1985-10-3 C0010602 上海
S05037312 王凡 1984-11-5 C0010513 天津
S05037314 齐经国 1985-5-5 C0010513 北京
S06037234 郑经 1984-12-8 C0010602 广州
SID VARCHAR2(10)
SNAME VARCHAR2(8)
SBIRTH DATE
SCLASS VARCHAR2(10)
SADDRESS VARCHAR2(20)用SELECT GET_NAME('北京') FROM STUDENT无法调出结果,是哪里出问题了?
谢谢!!!!
(V_ADDRESS IN VARCHAR2)
RETURN VARCHAR2
AS
V_NAME VARCHAR2(40);
BEGIN
SELECT SNAME
INTO V_NAME
FROM STUDENT
WHERE SADDRESS = V_ADDRESS;
RETURN V_NAME;
END;SID SNAME SBIRTH SCLASS SADDRESS
S06037201 黄小明 1985-10-3 C0010602 上海
S05037312 王凡 1984-11-5 C0010513 天津
S05037314 齐经国 1985-5-5 C0010513 北京
S06037234 郑经 1984-12-8 C0010602 广州
SID VARCHAR2(10)
SNAME VARCHAR2(8)
SBIRTH DATE
SCLASS VARCHAR2(10)
SADDRESS VARCHAR2(20)用SELECT GET_NAME('北京') FROM STUDENT无法调出结果,是哪里出问题了?
谢谢!!!!
INTO V_NAME
FROM STUDENT
WHERE SADDRESS = V_ADDRESS;
修改为:
execute immediate 'SELECT SNAME
FROM STUDENT
WHERE SADDRESS ='||''''||V_ADDRESS||'''' into V_NAME ;
SQL> insert into tt values('S06037201','黄小明',date'1985-10-3','C0010602','上海');已创建 1 行。SQL> insert into tt values('S05037314','齐经国',date'1985-5-5','C0010513','北京');已创建 1 行。SQL> create or replace function get_name(v_add varchar2) return varchar2
2 as
3 v_name tt.sname%type;
4 begin
5 select sname into v_name from tt where SADDRESS
6 =v_add;
7 return v_name;
8 end;
9 /函数已创建。SQL> select get_name('北京') from dual;GET_NAME('北京')
--------------------------------------------------------------------------------
齐经国SQL> select get_name('北京') from tt;GET_NAME('北京')
--------------------------------------------------------------------------------
齐经国
--估计是权限的问题
--调用加个distinct
SQL> select distinct get_name('北京') "姓名" from tt;姓名
--------------------------------------------------------
齐经国
SELECT SNAME FROM STUDENT WHERE SADDRESS ='北京'
SQL> CREATE OR REPLACE FUNCTION GET_NAME
2 (V_ADDRESS IN VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 V_NAME VARCHAR2(40);
6 BEGIN
7 SELECT SNAME
8 INTO V_NAME
9 FROM STUDENT
10 WHERE SADDRESS = V_ADDRESS;
11 RETURN V_NAME;
12 END;
13 /Function created.SQL> SELECT GET_NAME('北京')
2 FROM STUDENT
3 /
SELECT GET_NAME('北京')
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCHOOL.GET_NAME", line 7
可以使用 select distinct get_name('北京') "姓名" from tt;