[Linux-Biella] [OT] Oracle views

[Marco Ermini]

Stefano Porrino disse:
> ma e'  vero che una view di oracle puo' essere SOLO read-only???

NON SOLO in Oracle: in genere e' cosi' in quasi tutti i DB.

Comunque, nel caso particolare di Oracle la risposta e' NO, esistono anche
le view modificabili ("updateable views"), ma devono rispondere a
determinati requisiti (es. una view creata su una outer-join NON sara'
modificabile, perche' il codice e le condizioni necessarie a renderla
updateable sarebbero troppo complicati).

da
(http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_85a.htm#2065512)

"Notes on Creating Updatable Views

An updatable view is one you can use to insert, update, or delete base
table rows. You can create a view to be inherently updatable, or you can
create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable
view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary
view. (The information displayed by this view is meaningful only for
inherently updatable views.)

    * If you want the view to be inherently updatable, it must not contain
any of the following constructs:
          o A set operator
          o A DISTINCT operator
          o An aggregate or analytic function
          o A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause
          o A collection expression in a SELECT list
          o A subquery in a SELECT list
          o Joins (with some exceptions as described in the paragraphs
that follow).
    * In addition, if an inherently updatable view contains pseudocolumns
or expressions, you cannot update base table rows with an UPDATE
statement that refers to any of these pseudocolumns or expressions.
    * If you want a join view to be updatable, all of the following
conditions must be true:
          o The DML statement must affect only one table underlying the join.
          o For an INSERT statement, the view must not be created WITH
CHECK OPTION, and all columns into which values are inserted
must come from a key-preserved table. A key-preserved table in
one for which every primary key or unique key value in the base
table is also unique in the join view.
          o For an UPDATE statement, all columns updated must be extracted
from a key-preserved table. If the view was created WITH CHECK
OPTION, join columns and columns taken from tables that are
referenced more than once in the view must be shielded from
UPDATE.
    * For a DELETE statement, if the join results in more than one
key-preserved table, then Oracle deletes from the first table named in
the FROM clause, whether or not the view was created WITH CHECK
OPTION."


Non vorrei sembrarti un nerd, ma credo che a queste domande (che
difficilmente possono essere considerate on topic parlando di Linux...)
potresti trovare facilmente risposta da solo nell'OTN
(http://otn.oracle.com). La registrazione e' gratuita :-)

Tra l'altro, stai chiedendo una cosa abbastanza elementare... fa parte dei
concetti basilari di Oracle, per cui PRIMA di usare Oracle sarebbe
CALDAMENTE consigliabile dare una letta alla documentazione :-) non lo
dico per sembrare scortese o antipatico, lo dico sinceramente: se ti leggi
le caratteristiche principali dello strumento, poi lo saprai usere molto
meglio... fai meno fatica ecc.

Concetti elementari di Oracle:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/toc.htm

Documentazione sulla CREATE VIEW:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_85a.htm#2065512

Documentazione su SQL in generale:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm

Pagina di ricerca di partenza per Oracle 9i:
http://otn.oracle.com/pls/db92/db92.homepage


HTH

ciao
-- 
Marco Ermini
http://macchi.markoer.org