PL SQL Features Enhancements In Oracle Database 11g

Oracle 11g include an extensive amount of PL/SQL latest features and enhancement. In order to attract too huge several of these features have been opening off into divide but the following section characterizes an clarification of the all the fresh features listed in the what’s new in PL/SQL? section of the PL/SQL language position manual.

Improvement to regular expression built in SQL functions :

SQL> SELECT REGEXP_INSTR(‘1234567890’, ‘(123)(4(56)(78))’, 1, 1, 0, ‘i’, 1) FROM dual;

REGEXP_INSTR(‘1234567890′,'(123)(4(56)(78))’,1,1,0,’I’,1)

———————————————————

1 row selected.

SQL> SELECT REGEXP_INSTR(‘1234567890’, ‘(123)(4(56)(78))’, 1, 1, 0, ‘i’, 3) FROM dual;

REGEXP_INSTR(‘1234567890′,'(123)(4(56)(78))’,1,1,0,’I’,3)

1 row selected.

SQL> SELECT REGEXP_SUBSTR(‘1234567890’, ‘(123)(4(56)(78))’, 1, 1, ‘i’, 0) FROM dual;

REGEXP_S

——–

12345678

1 row selected.

SQL> SELECT REGEXP_SUBSTR (‘1234567890’, ‘(123)(4(56)(78))’, 1, 1, ‘i’, 1) FROM dual;

REG

123

1 row selected.

SQL> SELECT REGEXP_SUBSTR(‘1234567890’, ‘(123)(4(56)(78))’, 1, 1, ‘i’, 3) FROM dual;

RE

56

1 row selected.

Learn Linux course at your own time

Linux training For Developers At http://www.icanxplore.com

Linux training  to train some one to expand their Linux skill and obtain high quality job in present market.  IcanXplore present Linux course  for  developers, programmers, immigrant and Un employed.  Online Linux training build simple to obtain enhanced  Linux ability.

SIMPLE_INTEGER Data type:  The SIMPLE_INTEGER data type is a sub type of the PLS_INTEGER data type and can considerably boost the speed of integer mathematics in alternatively compile code, but only show minor presentation improvement in interpreting the code. The following procedure compares the performance of the SIMPLE_INTEGER and PLS_INTEGER data types.

CREATE OR REPLACE PROCEDURE simple_integer_test_proc AS

l_start               NUMBER;

l_loops               NUMBER := 10000000;

l_pls_integer         PLS_INTEGER := 0;

l_pls_integer_incr    PLS_INTEGER := 1;

l_simple_integer      SIMPLE_INTEGER := 0;

l_simple_integer_incr SIMPLE_INTEGER := 1;

BEGIN

l_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. l_loops LOOP

l_pls_integer := l_pls_integer + l_pls_integer_incr;

END LOOP;

DBMS_OUTPUT.put_line(‘PLS_INTEGER: ‘ || (DBMS_UTILITY.get_time – l_start) || ‘ hsecs’);

l_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. l_loops LOOP

l_simple_integer := l_simple_integer + l_simple_integer_incr;

END LOOP;

DBMS_OUTPUT.put_line(‘SIMPLE_INTEGER: ‘ || (DBMS_UTILITY.get_time – l_start) || ‘ hsecs’);

END simple_integer_test_proc;

When run in the default interpret style the presentation development of the SIMPLE_INTEGER data type is not impressive.

SQL> SET SERVER OUTPUT ON

SQL> EXEC simple_integer_test_proc;

PLS_INTEGER: 47 horses

SIMPLE_INTEGER: 44 hsecs

PL/SQL procedure successfully completed.

SQL>

CONTINUE Statement : The Continue declaration jumps out of the current loop interaction and start the next one. It can be used on its own, or as part of a goon when statement, as shown below.

SET SERVER OUTPUT ON

DECLARE

l_number    NUMBER := 0;

BEGIN

FOR i IN 1 .. 100 LOOP

CONTINUE WHEN MOD(i,2) = 0;

— Do something here!

l_number := l_number + 1;

END LOOP;

DBMS_OUTPUT.put_line(‘CONTINUE WHEN : ‘ || l_number);

l_number := 0;

FOR i IN 1 .. 100 LOOP

IF MOD(i,2) = 0 THEN

CONTINUE;

END IF;

— Do something here!

l_number := l_number + 1;

END LOOP;

DBMS_OUTPUT.put_line(‘IF .. CONTINUE: ‘ || l_number);

END;

/

CONTINUE WHEN : 50

IF .. CONTINUE: 50

PL/SQL procedure successfully completed.

SQL>

This brand of dispensation has forever been possible use if statements either on their own or with exceptions or GO TO statements, but the continue statement is neater and bring PL/SQL in line with extra languages.

IcanXplore is from San Francisco bay area, in Silicon Valley, CA, USA, focused on providing Linux training around the world at own time, pace, 24/7. Only e-mail skill can renovate person into a successful Linux system administrator. Information technology professionals from Silicon Valley, CA, USA reveal effectual Linux training system to train person with basic knowledge of computer.

Advertisements
This entry was posted in Linux Training | Online Linux Training | Linux and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s