1 / 75

SAGE Computing Services Customised Oracle Training Workshops and Consulting

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.

diep
Télécharger la présentation

SAGE Computing Services Customised Oracle Training Workshops and Consulting

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SAGE Computing Services Customised Oracle Training Workshops and Consulting 11g New Features … of the SQL & PL/SQL Variety Scott Wesley Systems Consultant

  2. Documentation Passwords Sequences Triggers SQL PL/SQL Recursion Read only tables Virtual columns 11g

  3. Documentation is your friend

  4. 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.

  5. Can you log in?

  6. SAGE@sw11g> conn scott/tiger ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.

  7. SAGE@sw11g> conn scott/Tiger Connected.

  8. SYS@sw11g> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = false; System altered.

  9. SCOTT@sw11g> conn scott/tiger Connected.

  10. SYS@sw11g> select username, password_versions from dba_users where username = 'SCOTT'; USERNAME PASSWORD --------------- -------- SCOTT 10G 11G 1 row selected.

  11. Password Complexity

  12. SYS@sw11g> @$ORACLE_HOME/RDBMS/ADMIN/utlpwdmg.sql SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g; Profile altered.

  13. 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

  14. SYS@sw11g> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL; Profile altered.

  15. Quick win: SQL*Plus supports BLOBs

  16. Available at every site Can do pie charts Free Supported by Oracle SQL*Plus Thin Fast

  17. Let’s get our hands dirty...

  18. Allow Sequence in PL/SQL expressions

  19. 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.

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

  21. 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

  22. 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

  23. 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;

  24. 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

  25. Same Same. But Different

  26. SCOTT@sw11g> insert into T test2 2 select rownum, rownum from dual connect by level < 50000; 49999 rows created. Elapsed: 00:00:04.01

  27. 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

  28. Triggers are still an overhead (in this case) Seth Godin -->

  29. As Connor McDonald likes to say: What we really need is...

  30. create table typical_table ( id_col number default id_seq.nextval, ...

  31. But we do have trigger improvements

  32. create or replace trigger populate_id before insert on T for each row disable begin :new.id := id_seq.nextval; end; /

  33. Compound triggers

  34. 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.

  35. 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.

  36. To accumulate rows destined for a second table so that you can periodically bulk-insert them

  37. create table audit_emp (employee_id number(20) ,old_salary number(10) ,new_salary number(10) ,ts timestamp);

  38. 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; /

  39. 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;

  40. 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;

  41. 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;

  42. 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;

  43. 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.

  44. Triggers are still ok (in this case)

  45. 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; /

  46. HR@sw11g> update employees set salary=1 where employee_id = 99; package_trigger custom_stuff 1 row updated.

  47. What about “PRECEDES”?

More Related