в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:27Z

1 версия

Новая страница

==IN/NOT IN example(table collection)==



<source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE in_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 result BOOLEAN;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("C", "D", "E");
10 emp3 := charTable("A", "B", "C");
11
12 result := emp3 IN (emp1);
13 IF result THEN
14 DBMS_OUTPUT.PUT_LINE("emp3 in emp1");
15 END IF;
16
17
18 END in_example;
19 /
Procedure created.
SQL>

</source>

==IN operator checks whether a variable value is in a set of comma-delimited values.==



<source lang="sql">

SQL>
SQL> BEGIN
2 IF 1 IN (1,2,3) THEN
3 dbms_output.put_line("In the set.");
4 END IF;
5 END;
6 /
In the set.
PL/SQL procedure successfully completed.

</source>

==IN operator in PL SQL==



<source lang="sql">

SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> DECLARE
2 lv_test_bln BOOLEAN;
3 BEGIN
4 lv_test_bln := UPPER("AAA") IN ("TRE","B","N");
5 IF lv_test_bln THEN
6 DBMS_OUTPUT.PUT_LINE("Result: TRUE");
7 ELSE
8 DBMS_OUTPUT.PUT_LINE("Result: FALSE");
9 END IF;
10 END;
11 /
Result: FALSE
PL/SQL procedure successfully completed.

</source>

==NOT IN example (table collection)==



<source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE in_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 result BOOLEAN;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("C", "D", "E");
10 emp3 := charTable("A", "B", "C");
11
12
13
14 result := emp3 NOT IN (emp2);
15 IF result THEN
16 DBMS_OUTPUT.PUT_LINE("emp3 not in emp2");
17 END IF;
18 END in_example;
19 /
Procedure created.
SQL>

</source>

==Use IN operator in procedure==



<source lang="sql">

SQL>
SQL>
SQL>
SQL> -The IN operator used to test for long weekends.
SP2-0734: unknown command beginning "-The IN op..." - rest of line ignored.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 test_date DATE;
3 day_of_week VARCHAR2(3);
4 years_ahead INTEGER;
5 BEGIN
6 --Assign a date value to test_date.
7
8 test_date := TO_DATE("4-Jul-1997","dd-mon-yyyy");
9
10 FOR years_ahead IN 1..10 LOOP
11 day_of_week := TO_CHAR(test_date,"Dy");
12
13 IF day_of_week IN ("Mon","Fri","Sat","Sun") THEN
14 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " A long weekend!");
15 ELSE
16 DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " Not a long weekend.");
17 END IF;
18 --Advance one year (12 months)
19 test_date := ADD_MONTHS(test_date,12);
20 END LOOP;
21 END;
22 /
04-Jul-1997 A long weekend!
04-Jul-1998 A long weekend!
04-Jul-1999 A long weekend!
04-Jul-2000 Not a long weekend.
04-Jul-2001 Not a long weekend.
04-Jul-2002 Not a long weekend.
04-Jul-2003 A long weekend!
04-Jul-2004 A long weekend!
04-Jul-2005 A long weekend!
04-Jul-2006 Not a long weekend.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/PL SQL/IN - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия