Sequence, CONTINUE, named and mixed notation SQL, READ ONLY tables 11g

This article presents new features of 11g

  • sequence enhancements
  • CONTINUE in PL/SQL
  • named and mixed notation in SQL
  • read only table

Sequence enhancements

  •  can use directly NEXTVAL, CURRVAL in PLSQL expressions. No need to use SQL statement.
DROP sequence test_seq;
 
CREATE sequence test_seq START WITH 1;
 
DECLARE
  v_seq NUMBER;
BEGIN
  --pre 10g code
  SELECT start_seq.NEXTVAL INTO v_seq FROM dual;
 
  --new 11g much easier :)
  v_seq := start_seq.NEXTVAL;
  /*
    some dummy code
  */
  --new 11g much easier :)
  v_seq := start_seq.CURRVAL;
END;
/

PL/SQL CONTINUE statement

  • adds new functionality to control loop iterations
  • semantics similar to EXIT statement
  • simplifies programming
  • CONTINUE breaks current iteration in loop and jumps to new iteration
SET serveroutput ON
 
--breaks current iteration for i in 1,2,3,4
BEGIN
  FOR i IN 1..6
  LOOP
    IF i IN (1,2,3,4) THEN
      continue;
    END IF;
    DBMS_OUTPUT.put_line('i: '||i);
  END LOOP;  
END;
/
 
anonymous block completed
i: 5
i: 6
 
--breaks current iteration for i=4
BEGIN
  FOR i IN 1..6
  LOOP
    continue WHEN i=4;
    DBMS_OUTPUT.put_line('i: '||i);
  END LOOP;  
END;
/
 
anonymous block completed
i: 1
i: 2
i: 3
i: 5
i: 6
 
--example of using next iteration of outer loop
BEGIN
  <<outer_loop>>
  FOR i IN 1..3
  LOOP
    FOR j IN 1..4
    LOOP
      --breaks current loop
      continue WHEN j IN (2);
      --breaks current loop and jumps to outer_loop
      continue outer_loop WHEN i = 2;
      DBMS_OUTPUT.put_line('i,j: '||i||','||j);
    END LOOP;
  END LOOP;  
END;
/
 
anonymous block completed
i,j: 1,1
i,j: 1,3
i,j: 1,4
i,j: 3,1
i,j: 3,3
i,j: 3,4

Named and mixed notation from SQL

  • SQL allows to use named or mixed notation to call PL/SQL subroutines
  • it simplifies SQL coding for long parameter list
CREATE OR REPLACE FUNCTION test_fun
(p1 NUMBER,
 p2 NUMBER DEFAULT 6,
 p3 NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN 0;
END;
/
 
--pre 11g notation
SELECT test_fun(1, 5, 2) FROM dual;
 
--named notation
SELECT test_fun(p1=>5, p3=>4) FROM dual;
SELECT test_fun(p1=>1, p3=>11, p2=>4) FROM dual;
 
--mixed notation
SELECT test_fun(1, p2=>11, p3=>4) FROM dual;
SELECT test_fun(1, p3=>4) FROM dual;

READ ONLY tables

  •  new ALTER TABLE statement to change table to READ ONLY or READ WRITE
  • prevents DDL(that change data) or DML changes on table
  • prevents SELECT … FOR UPDATE on READ ONLY table
  • READ ONLY table can be droped
ALTER session SET nls_language=english;
 
DROP TABLE test_tbl;
 
CREATE TABLE test_tbl
(id NUMBER);
 
BEGIN
  FOR i IN 1..10
  LOOP
    INSERT INTO test_tbl VALUES(i);
  END LOOP;
  COMMIT;
END;
/
 
--switching table to READ ONLY
ALTER TABLE test_tbl read only;
 
INSERT INTO test_tbl VALUES(11);
 
--SQL Error: ORA-12081: update operation not allowed on table
 
SELECT * FROM test_tbl FOR UPDATE;
 
--ORA-12081: update operation not allowed on table
 
ALTER TABLE test_tbl add(id1 NUMBER);
 
--SQL Error: ORA-12081: update operation not allowed on table
 
--allowed DDL not changing data
ALTER TABLE test_tbl move;
--table TEST_TBL altered.
 
--switch back to READ WRITE mode
ALTER TABLE test_tbl read WRITE;
--table TEST_TBL altered.
 
INSERT INTO test_tbl VALUES(11);
1 rows inserted.

Have a fun
Tomasz

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *