Home » Other » Client Tools » how to use variable in procedures
how to use variable in procedures [message #14886] Mon, 13 September 2004 22:20 Go to next message
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 Go to previous messageGo to next message
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
how to execute it in TOAD [message #14891 is a reply to message #14889] Tue, 14 September 2004 02:52 Go to previous messageGo to next message
Silpa
Messages: 23
Registered: July 2002
Junior Member
plese tell me how to execute it in toad.
Re: how to execute it in TOAD [message #14893 is a reply to message #14891] Tue, 14 September 2004 03:49 Go to previous message
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
Previous Topic: Sql loader error -- Toad
Next Topic: Can't log onto iSql
Goto Forum:
  


Current Time: Fri Mar 29 10:23:24 CDT 2024