Re: CASE strucrure in PL/SQL

"Onismus Radebe" <onismusr_at_absa.co.za> wrote in message news:<3edd8c23$0$231_at_hades.is.co.za>...
> Is CASE strucrure supported in PL?
>
> SQL> list
> 1 DECLARE
> 2 TYPE department_table_type IS TABLE OF
> 3 departments%ROWTYPE
> 4 INDEX BY BINARY_INTERGER ;
> 5 department_table department_table_type ;
> 6 v_department_id departments.department_id%TYPE ;
> 7 c_counter CONSTANT NUMBER(2) := 7 ;
> 8 BEGIN
> 9 FOR i IN 1..7
> 10 LOOP
> 11 v_department_id :=
> 12 CASE i
> 13 WHEN 1 THEN 10
> 14 WHEN 2 THEN 20
> 15 WHEN 3 THEN 50
> 16 WHEN 4 THEN 60
> 17 WHEN 5 THEN 80
> 18 WHEN 6 THEN 90
> 19 WHEN 7 THEN 110
> 20 END ;
> 21
> 22 SELECT *
> 23 INTO department_table(i)
> 24 FROM departments
> 25 WHERE department_id = v_department_id
> 26 END LOOP ;
> 27
> 28 FOR i IN department_table.FIRST..department.LAST
> 29 LOOP
> 30 DBMS_OUTPUT.PUT_LINE (CHR(10)) ;
> 31 DBMS_OUTPUT.PUT_LINE (department_table(i).department_name) ;
> 32 END LOOP ;
> 33* END ;
> SQL>
>
> SQL> @0502
> CASE i
> *
> ERROR at line 12:
> ORA-06550: line 12, column 4:
> PLS-00103: Encountered the symbol "CASE" when expecting one of the
> following:
> ( - + mod not null <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> avg
> count current exists max min prior sql stddev sum variance
> execute forall time timestamp interval date
> <a string literal with character set specification>
> <a number> <a single-quoted SQL string>

Mere syntax questions should not be asked here but looked up in the pl/sql manual for your version (which is evidently, but not surprising too much trouble for you to include in your post). The code above is awful: you could easily put those hardcoded literals in a pl/sql table.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Wed Jun 04 2003 - 06:11:57 CDT