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
|
|
|
|
Re: how to execute it in TOAD [message #14893 is a reply to message #14891] |
Tue, 14 September 2004 03:49 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Execute what in TOAD?
- The pl/sql block? copy and paste it in your Procedure edit window. Of course you need to format it and remove unnecessary comments and code.
- The sql statement? copy and paste it in your SQL Window. Of course you remove the SQL prompt and the line numbers.
- The (PER-)function you created? You can execute it in a SQL window through a select:SELECT per ('EMP', 'SAL') x
FROM DUAL Or, you can use dbms_output.put_line:BEGIN
DBMS_OUTPUT.put_line (per ('EMP', 'SAL'));
END; MHE
|
|
|