--给你个例子,之前写的CREATE PROCEDURE GetVehicleInfo (table_name in varchar2,rs out sys_refcursor) AS BEGIN OPEN rs FOR 'select * from '||table_name||' where rownum=1'; --动态游标获取结果集 END; / DECLARE rs sys_refcursor; rs_test emp%rowtype; table_name varchar2(10):='EMP'; BEGIN GetVehicleInfo(table_name,rs); LOOP FETCH rs INTO rs_test ; EXIT WHEN rs%NOTFOUND; Dbms_Output.put_line('NAME='||rs_test.ename); END LOOP; END; /结果:47 PL/SQL block, executed in 0 sec. NAME=SMITH Total execution time 0.016 sec.
select * from all_arguments t where t.object_name=upper('函数名')
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as scott
SQL> SQL> create or replace procedure proc_test_dynamic(i_table in varchar) 2 as 3 cv_dynamic sys_refcursor; 4 cv_col sys_refcursor; 5 v_colname varchar2(4000); 6 v_sql varchar2(4000); 7 v_sql2 varchar2(4000); 8 v_sql3 varchar2(4000); 9 begin 10 11 open cv_col for 'select COLUMN_NAME from user_tab_columns t where t.table_name=upper('''||i_table||''')'; 12 v_sql:='declare cv_dynamic sys_refcursor; '; 13 v_sql2:='fetch cv_dynamic into '; 14 loop 15 fetch cv_col into v_colname; 16 exit when cv_col%notfound; 17 if v_sql2 !='fetch cv_dynamic into ' then 18 v_sql2:=v_sql2||','; 19 end if; 20 v_sql:=v_sql||' v_'||v_colname||' '||i_table||'.'||v_colname||'%type; '; 21 v_sql2:=v_sql2||'v_'||v_colname; 22 v_sql3:=v_sql3||'dbms_output.put_line(v_'||v_colname||');'; 23 24 end loop; 25 v_sql:=v_sql||' begin '; 26 v_sql:=v_sql||'open cv_dynamic for select * from '||i_table||' where rownum=1;'; 27 v_sql:=v_sql||' loop '||v_sql2||';'; 28 v_sql:=v_sql||'exit when cv_dynamic%notfound; '; 29 v_sql:=v_sql||v_sql3; 30 v_sql:=v_sql||' end loop; close cv_dynamic; end;'; 31 execute immediate v_sql; 32 end; 33 /
Procedure created
SQL> set serveroutput on
SQL> exec proc_test_dynamic('emp');
7369 SMITH CLERK 7902 17-12月-80 800.1
20
PL/SQL procedure successfully completed
SQL>
select /*+ use_hash(c) use_hash(btm bt)*/ row_number() over(partition by 1 order by en_fare0 desc nulls last) as rn_num这是一部分语句 里面的/*+ use_hash(c) use_hash(btm bt)*/ row_number() over 这个是什么啊?
--给你个例子,之前写的CREATE PROCEDURE GetVehicleInfo (table_name in varchar2,rs out sys_refcursor)
AS
BEGIN
OPEN rs FOR 'select * from '||table_name||' where rownum=1'; --动态游标获取结果集
END;
/
DECLARE
rs sys_refcursor;
rs_test emp%rowtype;
table_name varchar2(10):='EMP';
BEGIN
GetVehicleInfo(table_name,rs);
LOOP
FETCH rs INTO rs_test ;
EXIT WHEN rs%NOTFOUND;
Dbms_Output.put_line('NAME='||rs_test.ename);
END LOOP;
END;
/结果:47 PL/SQL block, executed in 0 sec.
NAME=SMITH
Total execution time 0.016 sec.
你传递tablename没什么意义嘛,因为你的rs_test emp%rowtype;
类型已经定死了,是表emp的。我的意思就是这个rs_test不固定是某个表的某一行。
Connected as scott
SQL>
SQL> create or replace procedure proc_test_dynamic(i_table in varchar)
2 as
3 cv_dynamic sys_refcursor;
4 cv_col sys_refcursor;
5 v_colname varchar2(4000);
6 v_sql varchar2(4000);
7 v_sql2 varchar2(4000);
8 v_sql3 varchar2(4000);
9 begin
10
11 open cv_col for 'select COLUMN_NAME from user_tab_columns t where t.table_name=upper('''||i_table||''')';
12 v_sql:='declare cv_dynamic sys_refcursor; ';
13 v_sql2:='fetch cv_dynamic into ';
14 loop
15 fetch cv_col into v_colname;
16 exit when cv_col%notfound;
17 if v_sql2 !='fetch cv_dynamic into ' then
18 v_sql2:=v_sql2||',';
19 end if;
20 v_sql:=v_sql||' v_'||v_colname||' '||i_table||'.'||v_colname||'%type; ';
21 v_sql2:=v_sql2||'v_'||v_colname;
22 v_sql3:=v_sql3||'dbms_output.put_line(v_'||v_colname||');';
23
24 end loop;
25 v_sql:=v_sql||' begin ';
26 v_sql:=v_sql||'open cv_dynamic for select * from '||i_table||' where rownum=1;';
27 v_sql:=v_sql||' loop '||v_sql2||';';
28 v_sql:=v_sql||'exit when cv_dynamic%notfound; ';
29 v_sql:=v_sql||v_sql3;
30 v_sql:=v_sql||' end loop; close cv_dynamic; end;';
31 execute immediate v_sql;
32 end;
33 /
Procedure created
SQL> set serveroutput on
SQL> exec proc_test_dynamic('emp');
7369
SMITH
CLERK
7902
17-12月-80
800.1
20
PL/SQL procedure successfully completed
SQL>
row_number() over(partition by col order by col2) rn 分析函数
就是按col分组然后 在组内排序 并排个名 如
col col2 分析函数
1 10 1
1 20 2
2 30 1
2 50 2
2 60 3