Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Passing parameters to views...

Re: Passing parameters to views...

From: Emmanuel <zouzou_at_yahoo.com>
Date: 13 Jan 2000 16:24:35 GMT
Message-ID: <01bf5deb$8653cd80$3601017e@EHM.cirra.fr>


Why don't you use a function instead of a view ? Here's an example from the Oracle Documentation :

CREATE FUNCTION get_bal(acc_no IN NUMBER)

	RETURN NUMBER 
	IS 
		acc_bal NUMBER(11,2); 
	BEGIN 
		SELECT balance 
			INTO acc_bal 
			FROM accounts 
			WHERE account_id = acc_no; 
		RETURN(acc_bal); 
	END 

The GET_BAL function returns the balance of a specified account. When you call the function, you must specify the argument ACC_NO, the number of the account whose balance is sought. The datatype of ACC_NO is NUMBER.
The function returns the account balance. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.

The function uses a SELECT statement to select the BALANCE column from the row identified by the argument ACC_NO in the ACCOUNTS table. The function uses a RETURN statement to return this value to the environment in which the function is called.
The above function can be used in a SQL statement. For example:

SELECT get_bal(100) FROM DUAL;

maylee <mayleel_at_my-deja.com> a écrit dans l'article <85knva$2f$1_at_nnrp1.deja.com>...
> Actually, the kind of parameter I need to pass is at runtime. The date
> range can change at any point, it's not a fixed value.
>
> In article <387D00D4.62151E67_at_assigncorp.com>,
> mbuhari_at_assigncorp.com wrote:
> > Hi,
> >
> > You can always pass parameters to a view ( using "WHERE" predicate ).
> So
> > create a view and U can limit the range by specifying "WHERE" clause
> on that
> > view.
> >
> > Here is an example
> >
> > CREATE OR REPLACE VIEW emp_view
> > AS
> > SELECT a.name,b.dept_name FROM emp a,dept b
> > WHERE a.deptno=b.deptno;
> >
> > SELECT * from emp_view
> > WHERE dept_name='MARKETING';
> >
> > Mohamed.
> >
> > maylee wrote:
> >
> > > I need to create a view for a group of tables based on a range of
> values
> > > (i.e. date ranges)
> > > I was wondering if there is a way to pass parameters to a view, so
> only
> > > this range will be in teh view.
> > >
> > > The underlying tables grow exponentially, so a regular view would
> return
> > > too much data. I need to limit it somehow.
> > >
> > > Thanks,
> > > Maylee
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Thu Jan 13 2000 - 10:24:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US