Home » Other » Client Tools » how to use variable in procedures
how to use variable in procedures [message #14886] |
Mon, 13 September 2004 22:20 |
kesava
Messages: 3 Registered: September 2004
|
Junior Member |
|
|
Is the following code correct.� Here i would lke to send a table and field name as�parameters to the procedure
create or replace function per(tbl1 is varchar2(30),clm1 is number)
�return number
� is
� declare
�� ind number;
�� ct number;
�� temp_id '&tbl1'.'&clm1'%type;
�� cursor temp_cur is select '&clm1' from '&tbl1' order by '&clm1' desc;
� begin
�� select count(*) into ct from '&tbl1';
�� dbms_output.put_line('number of records:'|| ct);
�� ind:=round((ct*25)/100);
�� dbms_output.put_line('records to be deleted for� 75th� percetile:'|| ind);
�� open temp_cur;
�� for i in 1..ind loop
������ fetch temp_cur into temp_id;�
�� end loop;
�� fetch temp_cur into temp_id;�
�� dbms_output.put_line(' The 75th percetail:'|| temp_id);
�� ids:=temp_id;
�� return ids;
� end;
/
|
|
|
Re: how to use variable in procedures [message #14889 is a reply to message #14886] |
Tue, 14 September 2004 00:20 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
You obviously tried to convert an anonymous pl/sql block into a function. There are, at first glance, a lot of errors present:
- ampersants are not used. It is not interactive, we don't want sql*Plus to prompt us. It won't anyway.
- we need dynamic sql. So we use a ref cursor and execute immediate.
Basically, you could do something like this:/* Formatted on 2004/09/14 10:23 (Formatter Plus v4.8.0) */
CREATE OR REPLACE FUNCTION per (
tbl1 IN VARCHAR2 -- IN, not IS and no size in params ,
clm1 IN VARCHAR2
) -- IN, not IS
RETURN NUMBER
IS
-- no 'declare', it is no anonymous block
ind NUMBER;
ct NUMBER;
--temp_id 'tbl1'.'clm1'%type; -- You *know* it is datatype NUMBER
temp_id NUMBER;
-- Use a ref cursor...
TYPE curtyp IS REF CURSOR;
temp_cur curtyp;
--cursor temp_cur is select 'clm1' from 'tbl1' order by 'clm1' desc;
BEGIN
-- use dynamic sql
-- select count(*) into ct from '&tbl1';
EXECUTE IMMEDIATE 'SELECT COUNT(*) cnt FROM ' || tbl1
INTO ct;
ind := ROUND ((ct * 25) / 100);
OPEN temp_cur
FOR 'select ' || clm1 || ' from ' || tbl1 || ' order by ' || clm1
|| ' desc';
FOR i IN 1 .. ind
LOOP
FETCH temp_cur
INTO temp_id;
END LOOP;
FETCH temp_cur
INTO temp_id;
RETURN temp_id:
END;
/ Or, you could use plain sql instead:SQL> select max( sal ) sal
2 , max( ename ) ename
3 from ( SELECT first_value(e.sal) over ( order by v.dist75 desc ) sal
4 , first_value(e.ename) over ( order by v.dist75 desc ) ename
5 FROM ( select (25 - rnk*100) dist75
6 , empno
7 from ( select percent_rank() over ( order by sal desc ) rnk
8 , empno
9 from emp
10 order by sal desc
11 ) v
12 where rnk > 0.25
13 ) v
14 , emp e
15 where e.empno = v.empno
16 )
17 /
SAL ENAME
--------- ----------
2850 BLAKE
Caution, it is a quick and dirty example. I *think* it might do the trick but I haven't tested the function's code and I just ran the sql once. Furthermore, I'm on 8i here, and 9i has a lot more possibilities. Look into the PERCENT_RANK() function, it might come in handy...
HTH,
MHE
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 11 05:29:32 CST 2025
|