Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL intro #12

Views, sequences, synonyms
by

Rodion Mironov

on 12 February 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #12

Introduction to Oracle SQL.
Views, sequences,
synonyms. Agenda views, DML on views;
sequences;
synonyms. VIEW is named query
stored in database. create or replace view sales_people
as
select first_name, last_name
from employees
where job_id in ('SA_REP', 'SA_MAN'); Views usage:
restrict access to column and/or rows;
simplify queries;
reuse code;
isolate application from DB structure. UPDATE restrictions:
SET operations;
DISTINCT;
Aggregate functions and/or GROUP BY clause;
expressions. DELETE restrictions:
SET operations;
DISTINCT;
Aggregate functions and/or GROUP BY clause; Join views have some additional limitations
(aka "key-preserved table" rule).

If you can't modify view data directly,
you can use INSTEAD OF trigger. Use WITH CHECK OPTION to ensure that row will not "disappear" from view after modification. Note: query text is stored, not query result. Note 2: materialized views store both text and result, but it's another topic. select * from sales_people; DML statements are allowed for views with some restrictions: INSERT restrictions:
not included NOT NULL columns;
SET operations;
DISTINCT;
Aggregate functions and/or GROUP BY clause;
expressions. create or replace view sales_people
as
select first_name, last_name, job_id
from employees
where job_id in ('SA_REP', 'SA_MAN')
WITH CHECK OPTION; SEQUENCE
is special schema object
generating unique numbers. CREATE SEQUENCE seq_test
START WITH 1
INCREMENT BY 1; Using sequence:
select seq_test.nextval from dual;
select seq_test.currval from dual; NEXTVAL and CURRVAL can be used:
The select list of a SELECT statement that is not contained in a subquery, materialized view, or view
The select list of a subquery in an INSERT statement
The VALUES clause of an INSERT statement (+MERGE)
The SET clause of an UPDATE statement (+MERGE) select seq_test.nextval, seq_test.nextval
from dual Sequences tips:
the only reliable way to get current sequence value is to select NEXTVAL;
However, if you are looking for current sequence value, you are solving the wrong problem probably. Sequences tips:
sequence-generated values in some column can include gaps due to rollbacks, system crashes, NEXTVAL calls without saving value;
However, if you are looking for sequence without gaps, you are solving the wrong problem probably. Sequences tips:
to use sequence for PK generation, you can:
create trigger;
just use sequence in all insert statements for that table. Note:
CURRVAL returns the last value generated in this session;
CURRVAL can be used after NEXTVAL only; select seq_test.nextval
from (select 1 from dual
union all
select 1 from dual) select seq_test.currval, seq_test.nextval
from (select 1 from dual
union all
select 1 from dual) SYNONYM - alias for schema object. CREATE [PUBLIC] SYNONYM synonym
FOR object; Synonyms usage:
simplify queries;
hide real object name and schema (for futher renaming); public synonym is visible to all DB users;
private - to creator only, as other schema objects. Don't overuse synonyms:
slightly more RDBMS efforts on query parsing;
more complicated code analysis;
good practice is to isolate application in its own schema(s). http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns002.htm#i1006157 Nextval and Currval Concepts: views, sequences, synonyms http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CFAGCHCD Useful links CREATE VIEW http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#i2065510 CREATE SEQUENCE http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6015.htm#SQLRF01314 CREATE SYNONYM http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7001.htm#CJAJCDDF NEXTVAL is incremented ones per row:
returned by SELECT statement (individual, insert...select, multitable insert, CTAS);
inserted by single-row insert;
updated by UPDATE;
merged by MERGE; CREATE SEQUENCE sequence
[START WITH n]
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
[{ORDER|NOORDER}];
Full transcript