sql语句实现不了
SQL > select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
SQL > select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
另外,想再问一下
有一字符串:123,314324,13423
我想取某个数字,ORACLE中有这样的函数吗?XIE XIE
17:13:39 2 instr('123,314324,13423',',',2)-1) from dual;SUB
---
314已用时间: 00: 00: 00.31
17:16:51 2 instr('123,314324,13423',',',1,2)-instr('123,314324,13423',',')-1) from dual;SUBSTR
------
314324已用时间: 00: 00: 00.16
17:16:59 SQL>
1、表名是一个变量,CURSOR如何写?
2、怎么遍历形如12,13这种字符串里的每个数值?
如下:
CREATE OR REPLACE FUNCTION F_GetFieldValByID(
v_TblName VARCHAR2,
v_FieldName VARCHAR2,
v_ID VARCHAR2
)
return VARCHAR2
IS
c_Field INTEGER;
v_Val VARCHAR2(1000);
v_Result VARCHAR2(1000);
v_SQLStr VARCHAR2(1000);
v_Dummy NUMBER;
BEGIN
v_Result := '';
BEGIN
v_SQLStr := 'SELECT '||v_FieldName||' FROM '||v_TblName||'Tbl WHERE '||v_TblName||'ID IN ('||v_ID||')';
c_Field := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (c_Field, v_SQLStr, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (c_Field, 1, v_Val,1000);
v_Dummy := DBMS_SQL.EXECUTE (c_Field); LOOP
IF DBMS_SQL.FETCH_ROWS (c_Field) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE (c_Field, 1, v_Val);
IF v_Result IS NULL THEN
v_Result := v_Val;
ELSE
v_Result := v_Result||','||v_Val;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (c_Field);
EXCEPTION
WHEN OTHERS THEN
v_Result := '';
END;
return v_Result;
END F_GetFieldValByID;
/但是现在在ASP中调用有点问题,在SQLPLUS里调用是正常的,问题如下:
调用形如:SELECT F_GetFieldValByID('ATBL','TYPE',Type) FROM ATbl
如果数据量小的话,如只有几十条上百条记录,好像还比较正常,如果数据量大的话会出现很多Type明明有值的也为空,不知道是不是跟我ASP里调用的语句有关系?
rs.Open sql,cn,3 这样调用有问题吗?