Using with to solve the problem of variable input parameters multi-input parameters when selecting an input parameter, the input parameter volume is variable, generally achieved through dynamic SQL spelling where statement, this method is not safe, poor readability. We can use the with statement and table function of Oracle to implement the variable input parameter function.
-- Created on 2008-03-19 by Administrator
Declare
-- Local variables here
IInteger;
V_tab insu_table;
Begin
Select'31'Bulk Collect IntoV_tabFromDual;
P1 (v_tab );
End; |
Create Or Replace TypeInsu_tableIs Table Of Varchar2(3 );
Create Or Replace ProcedureP1 (p_tab insu_table)Is
V_aVarchar2(20 );
Begin
WithDataAs(
Select*
From Table(P_tab ))
SelectAab001
IntoV_a
FromAb15
WhereAae140In(Select*FromData)
AndRownum = 1;End; |
Use the with statement to virtualize a dataset and access it as a table in the SELECT statement.
