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