函数功能:动态查询指定表的指定列数据 按照主键值检索
输入参数:1.列名称 columnName nvarchar2
2.列显示名称 即:中文名称 displayName nvarchar2
3.主键值 Oid nvarchar2
4.数据表名称 tableName nvarchar2select t.columnName displayName from tableName t where t.id=Oid
输入参数:1.列名称 columnName nvarchar2
2.列显示名称 即:中文名称 displayName nvarchar2
3.主键值 Oid nvarchar2
4.数据表名称 tableName nvarchar2select t.columnName displayName from tableName t where t.id=Oid
create or replace procedure liushengpiaoxu(ooid in nvarchar2,
tableName in nvarchar2,
columnName in nvarchar2,
displayName in nvarchar2,
o_cur out sys_refcursor) as
v_sql varchar2(200);
begin
v_sql := 'select t.' || columnName || ' ' || displayName || ' from ' ||
tableName || ' t where t.id=' || ooid;
open o_cur for v_sql;
end;
--过程:
SQL> create or replace procedure p_get_info(
2 columnName in nvarchar2, --列名
3 displayName in nvarchar2, --列显示名称
4 oid in nvarchar2, --主键值
5 tableName in nvarchar2, --表名称
6 cur out sys_refcursor --返回结果
7 )
8 as
9 v_sql varchar2(200);
10 begin
11 v_sql := 'select ' || columnName || ' ' || displayName || ' from ' ||
12 tableName || ' t where t.empno=' || oid; --这里t.empno 实际你做的时候换成你的 t.id
13 open cur for v_sql;
14 end;
15 /Procedure created.SQL> var cur refcursor
SQL> EXEC p_get_info('ename','姓名','7369','emp',:cur);PL/SQL procedure successfully completed.SQL> print cur姓名
--------------------
SMITHSQL> --函数实现:
SQL> create or replace function p_get_info123(
2 columnName in nvarchar2, --列名
3 displayName in nvarchar2, --列显示名称
4 oid in nvarchar2, --主键值
5 tableName in nvarchar2 --表名称
6 )
7 return sys_refcursor --返回结果
8 as
9 v_sql varchar2(200);
10 cur sys_refcursor;
11 begin
12 v_sql := 'select ' || columnName || ' ' || displayName || ' from ' ||
13 tableName || ' t where t.empno=' || oid; --这里t.empno 实际你做的时候换成你的 t.id
14 open cur for v_sql;
15 return cur;
16 end;
17 /Function created.SQL> var cur refcursor
SQL> begin
2 :cur:= p_get_info123('ename','姓名','7369','emp');
3 end;
4 /PL/SQL procedure successfully completed.SQL> print cur姓名
--------------------
SMITHSQL>
create or replace function p_get_infoFun(
columnName in nvarchar2, --列名
displayName in nvarchar2, --列显示名称
premKey in nvarchar2, --主键列名
oid in nvarchar2, --主键列值
tableName in nvarchar2, --表名称
cur out sys_refcursor --返回结果
)
as v_sql varchar2(200);
cur sys_refcursor;
begin
v_sql := 'select ' || columnName || ' ' || displayName || ' from ' ||
tableName || ' t where t.'|| premKey ||'=' || oid;
open cur for v_sql;
return cur;
end;
创建函数异常
SQL> create or replace function liushengpiaoxu1(ooid in nvarchar2,
2 tableName in nvarchar2,
3 columnName in nvarchar2,
4 displayName in nvarchar2)
5 return sys_refcursor as
6 v_sql varchar2(200);
7 v_cur sys_refcursor;
8 begin
9 v_sql := 'select t.' || columnName || ' ' || displayName || ' from ' ||
10 tableName || ' t where t.id=' || ooid;
11 open v_cur for v_sql;
12 return v_cur;
13 end;
14 /Function createdSQL>
columnName in nvarchar2, --列名
displayName in nvarchar2, --列显示名称
premKey in nvarchar2, --主键列名
oid in nvarchar2, --主键列值
tableName in nvarchar2 --表名称
) return sys_refcursor ---加个返回的结果集
as v_sql varchar2(200);
cur sys_refcursor;
begin
v_sql := 'select ' || columnName || ' ' || displayName || ' from ' ||
tableName || ' t where t.'|| premKey ||'=' || oid;
open cur for v_sql;
return cur;
end;
---记得调用的时候释放游标 不然你会有事情做的