slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SAGE Computing Services Customised Oracle Training Workshops and Consulting PowerPoint Presentation
Download Presentation
SAGE Computing Services Customised Oracle Training Workshops and Consulting

Loading in 2 Seconds...

play fullscreen
1 / 75

SAGE Computing Services Customised Oracle Training Workshops and Consulting - PowerPoint PPT Presentation


  • 136 Views
  • Uploaded on

SAGE Computing Services Customised Oracle Training Workshops and Consulting. 11g New Features … of the SQL & PL/SQL Variety. Scott Wesley Systems Consultant. Documentation. Passwords. Sequences. Triggers. SQL. PL/SQL. Recursion. Read only tables. Virtual columns. 11g.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SAGE Computing Services Customised Oracle Training Workshops and Consulting' - diep


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

SAGE Computing Services

Customised Oracle Training Workshops and Consulting

11g New Features

… of the SQL & PL/SQL Variety

Scott Wesley

Systems Consultant

slide2

Documentation

Passwords

Sequences

Triggers

SQL

PL/SQL

Recursion

Read only tables

Virtual columns

11g

readme s are still around
Readme’s are still around
  • Features Not Available or Restricted in This Release
    • Edition-based redefinition is not available in Oracle Database 11g Release 1 (11.1). You cannot create an edition, an editioning view, or a crossedition trigger; nor can you use the ENABLE EDITIONS clause in the CREATE USER and ALTER USER commands. As a consequence, other related functionality (for example, the ALTER SESSION SET EDITION statement or the new overload of DBMS_Sql.Parse() that lets you specify an edition or a crossedition trigger) becomes uninteresting and attempting to use it will cause a semantic error.
slide7

SAGE@sw11g> conn scott/tiger

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

slide11

SYS@sw11g> select username, password_versions

from dba_users where username = 'SCOTT';

USERNAME PASSWORD

--------------- --------

SCOTT 10G 11G

1 row selected.

slide13

SYS@sw11g> @$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql

SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g;

Profile altered.

slide14

SCOTT@sw11g> password

Changing password for SCOTT

Old password:

New password:

Retype new password: sagesage

ERROR:

ORA-28003: password verification for the specified password failed

ORA-20006: Password too simple

Password unchanged

slide15

SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.

slide18

Available at every site

Can do pie charts

Free

Supported by Oracle

SQL*Plus

Thin

Fast

slide22

SCOTT@sw11g> create table T ( id number, value number );

Table created.

SCOTT@sw11g> create sequence id_seq;

Sequence created.

SCOTT@sw11g> create or replace

2 trigger populate_id

3 before insert on T

4 for each row

5 begin

6 -- dbms_db_version.ver_le_10

7 -- select id_seq.nextval into from dual;

8

9 -- dbms_db_version.ver_le_11

10 :new.id := id_seq.nextval;

11 end;

12 /

Trigger created.

slide23

This feature brings improved usability for the PL/SQL programmer and improved runtime performance and scalability.

slide24

SCOTT@sw11g> declare

2 n pls_integer;

3 begin

4 for i in 1 .. 50000 loop

5 select id_seq.nextval into n from dual;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.18

slide25

SCOTT@sw11g> declare

2 n pls_integer;

3 begin

4 for i in 1 .. 50000 loop

5 n := id_seq.nextval;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.68

slide26

alter session set sql_trace=true;

variable n number

begin

for i in 1 .. 100 loop

:n := scott.id_seq.nextval;

end loop;

end;

/

alter session set sql_trace=false;

slide27

Select ID_SEQ.NEXTVAL

from

dual

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 100 0.01 0.03 0 0 0 0

Fetch 100 0.00 0.05 0 0 5 100

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 201 0.01 0.09 0 0 5 100

slide29

SCOTT@sw11g> insert into T test2

2 select rownum, rownum from dual connect by level < 50000;

49999 rows created.

Elapsed: 00:00:04.01

slide30

SCOTT@sw11g> drop trigger populate_id;

Trigger dropped.

SCOTT@sw11g> insert into T test2

2 select id_seq.nextval, rownum

from dual connect by level < 50000;

49999 rows created.

Elapsed: 00:00:00.71

slide32

As Connor McDonald likes to say:

What we really need is...

slide33

create table typical_table

( id_col number default id_seq.nextval,

...

slide35

create or replace

trigger populate_id

before insert on T

for each row

disable

begin

:new.id := id_seq.nextval;

end;

/

slide37

CREATE OR REPLACE TRIGGER compound_trigger

FOR UPDATE OF salary ON employees

COMPOUND TRIGGER

-- Declarative part (optional)

-- Variables declared here have firing-statement duration.

threshold CONSTANT SIMPLE_INTEGER := 200;

BEFORE STATEMENT IS

BEGIN

NULL;

END BEFORE STATEMENT;

BEFORE EACH ROW IS

BEGIN

NULL;

END BEFORE EACH ROW;

AFTER EACH ROW IS

BEGIN

NULL;

END AFTER EACH ROW;

AFTER STATEMENT IS

BEGIN

NULL;

END AFTER STATEMENT;

END compound_trigger;

/

Trigger created.

slide38

To avoid the mutating-table error

eg: A business rule states that an employee's salary increase must not exceed 10% of the average salary for the employee's department.

slide40

create table audit_emp

(employee_id number(20)

,old_salary number(10)

,new_salary number(10)

,ts timestamp);

slide41

create or replace trigger old_way

after update of salary

on emp_large

for each row

begin

insert into audit_emp

values (:new.employee_id

,:old.salary

,:new.salary

,systimestamp);

end old_way;

/

slide42

SAGE@sw11g> update emp_large set salary = salary -1;

107892 rows updated.

Elapsed: 00:00:08.75

SAGE@sw11g> select count(*) from audit_emp;

COUNT(*)

----------

107892

1 row selected.

alter trigger old_way disable;

slide43

create or replace trigger new_way

for update of salary on emp_large

compound trigger

threshhold constant simple_integer := 100;

type audit_t is table of audit_emp%rowtype index by simple_integer;

t_auditaudit_t;

ln_indexsimple_integer := 0;

procedure flush_array is

n constant SIMPLE_INTEGER := t_audit.count();

begin

forall j in 1..n

insert into audit_emp values t_audit(j);

t_audit.delete();

ln_index := 0;

end flush_array;

after each row is

begin

ln_index := ln_index + 1;

t_audit(ln_index).employee_id := :new.employee_id;

t_audit(ln_index).old_salary := :old.salary;

t_audit(ln_index).new_salary := :new.salary;

t_audit(ln_index).ts := systimestamp;

if ln_index >= threshhold then

flush_array;

end if;

end after each row;

after statement is

begin

flush_array;

end after statement;

end new_way;

/

create or replace trigger new_way

for update of salary on emp_large

compound trigger

threshhold constant simple_integer := 100;

type audit_t is table of

audit_emp%rowtype index by simple_integer;

t_audit audit_t;

ln_index simple_integer := 0;

slide44

create or replace trigger new_way

for update of salary on emp_large

compound trigger

threshhold constant simple_integer := 100;

type audit_t is table of audit_emp%rowtype index by simple_integer;

t_auditaudit_t;

ln_indexsimple_integer := 0;

procedure flush_array is

n constant SIMPLE_INTEGER := t_audit.count();

begin

forall j in 1..n

insert into audit_emp values t_audit(j);

t_audit.delete();

ln_index := 0;

end flush_array;

after each row is

begin

ln_index := ln_index + 1;

t_audit(ln_index).employee_id := :new.employee_id;

t_audit(ln_index).old_salary := :old.salary;

t_audit(ln_index).new_salary := :new.salary;

t_audit(ln_index).ts := systimestamp;

if ln_index >= threshhold then

flush_array();

end if;

end after each row;

after statement is

begin

flush_array;

end after statement;

end new_way;

/

procedure flush_array is

n constant SIMPLE_INTEGER := t_audit.count();

begin

forall j in 1..n

insert into audit_emp values t_audit(j);

t_audit.delete();

ln_index := 0;

end flush_array;

after each row is

begin

ln_index := ln_index + 1;

t_audit(ln_index).employee_id := :new.employee_id;

t_audit(ln_index).old_salary := :old.salary;

t_audit(ln_index).new_salary := :new.salary;

t_audit(ln_index).ts := systimestamp;

if ln_index >= threshhold then -- index >= 100

flush_array;

end if;

end after each row;

slide45

create or replace trigger new_way

for update of salary on emp_large

compound trigger

threshhold constant simple_integer := 100;

type audit_t is table of audit_emp%rowtype index by simple_integer;

t_auditaudit_t;

ln_indexsimple_integer := 0;

procedure flush_array is

n constant SIMPLE_INTEGER := t_audit.count();

begin

forall j in 1..n

insert into audit_emp values t_audit(j);

t_audit.delete();

ln_index := 0;

end flush_array;

after each row is

begin

ln_index := ln_index + 1;

t_audit(ln_index).employee_id := :new.employee_id;

t_audit(ln_index).old_salary := :old.salary;

t_audit(ln_index).new_salary := :new.salary;

t_audit(ln_index).ts := systimestamp;

if ln_index >= threshhold then

flush_array;

end if;

end after each row;

after statement is

begin

flush_array;

end after statement;

end new_way;

/

procedure flush_array is

n constant SIMPLE_INTEGER := t_audit.count();

begin

forall j in 1..n

insert into audit_emp values t_audit(j);

t_audit.delete();

ln_index := 0;

end flush_array;

after statement is

begin

flush_array;

end after statement;

slide46

SAGE@sw11g> update emp_large set salary = salary -1;

107892 rows updated.

Elapsed: 00:00:04.01

SAGE@sw11g> select count(*) from audit_emp;

COUNT(*)

----------

107892

1 row selected.

slide48

create or replace trigger package_trigger

after update of salary

on employees

for each row

begin

dbms_output.put_line('package_trigger');

end old_way;

/

create or replace trigger custom_stuff

after update of salary

on employees

for each row

follows package_trigger

begin

dbms_output.put_line('custom_stuff');

end old_way;

/

slide49

HR@sw11g>

update employees set salary=1

where employee_id = 99;

package_trigger

custom_stuff

1 row updated.

slide52

create or replace function

f(p1 in integer

,p2 in integer := 2

,p3 in integer := null) return number is

begin

return nvl(p1,0)

+nvl(p2,0)

+nvl(p3,0);

end;

/

slide53

SAGE@sw11g> select f(1,2,3) from dual;

F(1,2,3)

----------

6

1 row selected.

slide54

SAGE@sw11g> select f from dual;

select f from dual

*

ERROR at line 1:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'F'

slide55

SAGE@sw11g> select f(1,null) from dual;

F(1,NULL)

----------

1

1 row selected.

slide56

SAGE@sw11g> select f(1,p3=>3) from dual;

F(1,P3=>3)

----------

6

1 row selected.

slide58

declare

x number := 0;

begin

<< my_loop >>

loop -- after continue statement, control resumes here

dbms_output.put_line ('Inside loop: x = ' || to_char(x));

x := x + 1;

continue my_loop when x < 3;

dbms_output.put_line ('Inside loop, after CONTINUE: x = '

|| to_char(x));

exit when x = 5;

end loop my_loop;

dbms_output.put_line ('After loop: x = ' || to_char(x));

end;

/

Inside loop: x = 0

Inside loop: x = 1

Inside loop: x = 2

Inside loop, after CONTINUE: x = 3

Inside loop: x = 3

Inside loop, after CONTINUE: x = 4

Inside loop: x = 4

Inside loop, after CONTINUE: x = 5

After loop: x = 5

PL/SQL procedure successfully completed.

slide60

create or replace

function factorial_interpreted(p_n number)

return number is

begin

if (p_n = 1)

then

return 1;

else

return factorial_interpreted(p_n-1)*p_n;

end if;

end;

/

slide61

create or replace

function factorial_native(p_n number)

return number is

begin

if (p_n = 1)

then

return 1;

else

return factorial_native(p_n-1)*p_n;

end if;

end;

/

slide62

ALTER PROCEDURE factorial_native

COMPILE PLSQL_CODE_TYPE=NATIVE

REUSE SETTINGS;

slide63

declare

l_n number;

begin

for i in 1..500000

loop

l_n := factorial_interpreted(50);

end loop;

end;

/

Elapsed: 00:00:14.85

slide64

declare

l_n number;

begin

for i in 1..500000

loop

l_n := factorial_native(50);

end loop;

end;

/

Elapsed: 00:00:10.26

slide67

HR@sw11g> update logons set user_id = upper(user_id);

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "HR"."LOGONS"

slide68

conn sage/sage

SAGE@sw11g> select privilege

from user_tab_privs

where table_name = 'LOGONS';

PRIVILEGE

--------------------

UPDATE

1 row selected.

slide69

SAGE@sw11g> update hr.logons set user_id = upper(user_id)

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "HR"."LOGONS"

slide72

Very

Versatile

Virtual

Verticals

virtual columns
Virtual Columns
  • Formula/computed columns – on the database
  • Further constraints – on the database
  • New category for partitioning – on the database
  • Creative referential integrity – on the database

Without

  • Triggers - expensive
  • Views – sometimes forgotten
  • Re-design – too much hard work!

Demo…

slide74

Query result cache

PL/SQL Result Cache

PL/SQL Inlining Optimisation

SQL Plan Management

And there’s more!

Invisible indexes

SQL Performance Analyser

Pivot / Unpivot

DBA Stuff

slide75

SAGE Computing Services

Customised Oracle Training Workshops and Consulting

Questions and Answers?

Presentations are available from our website:

http://www.sagecomputing.com.au

enquiries@sagecomputing.com.au

scott.wesley@sagecomputing.com.au