Oracle SQL and PL/SQL (2 of 3)

Manipulating Data

Describe each DML statement

INSERT, UPDATE, DELETE, (INSERT, SET TRANSACTION, EXPLAIN PLAN)

Insert a row into a table

 insert into parientes(miembro,nombre) values (12,'Alexia');
 insert into relatives(member,name) values(12,'Alexia');

Update a row in a table

 update parientes set apellido='Rubio' where miembro=12; 
 update relatives set lastname='Rubio' where member=12;

Delete a row in a table

 delete parientes where miembro=12;

Control Transactions

commit and rollback savepoint . When a savepoint is defined within a statement, it can be rolledback up to that particular point. Example:

 insert into nomina(sueldo) values(1000000);
 savepoint excelente;
 insert into nomina(sueldo) values(100000);
 savepoint bueno;
 insert into nomina(sueldo) values(10000);
 savepoint salario_mexicano;
 commit;

Even though the commit was already issued, we can still rollaback up to a savepoint location with:

 ROLLBACK to bueno

That would rollback the insert with a value of 10000

Creating and Managing Tables

Describe the main database objects

INDEX, SEQUENCE, TABLE ,VIEW,CLUSTER, PROCEDURE,SYNONYM,TRIGGER, CONSUMER GROUP,RESOURCE PLAN ,FUNCTION,INDEXTYPE, JAVA CLASS, JAVA RESOURCE, LIBRARY, OPERATOR, PACKAGE, PACKAGE BODY, QUEUE.

Create Tables

create table alumnos (
 nombre varchar2(25), 
 apellido varchar2(25),
 matricula integer primary key,
 salon number );   

 create tables pupils (
 name varchar2(25),
 lastname varchar2(25),
 id integers primary key,
 classroom number);

Describe the Datatypes that can be used when specifying column definitions

char(n): A fixed length character string,will take up 200 bytes regardless of how long the string actually is. The maximum length of char is 2000 bytes.
varchar(n): A variable length character string, that will take up space accordingly
Other Datatype: CHAR,NCHAR,VARCHAR2, NVARCHAR,DATE,NUMBER,LONG,RAW,LONG RAW, MISLABEL, BLOB,CLOB,NCLOB,BFILE,ROWID y UROWID

Alter Table Definitions

To drop a column :

                 alter table mifamilia drop column no_sirve;
                 alter table myfamily drop column irrelevant;

To add a column :

                 alter table mifamilia add (telefono number(15));
		 alter table myfamily add (telephone number(15));

To rename a column (two steps):

                 alter table mifamilia set unused parientes;(This deactivates the column)
		 alter table mifamilia add (familiares varchar2(40));(Created a new column)

When the column is marked unused, the information is not deleted it still exists. So you can then copy the data from the unused column (parientes) to the new column (familiares).

To change variable type in column :

 
                alter table mifamily modify(nombre varchar2(43));

		NOTE: To modify the Datatype the column must be all null.

Alter storage parameter for the table:

                ALTER TABLE branch MODIFY PARTITION branch_mex
		STORAGE (MAXEXTENTS 75) LOGGING;


		ALTER TABLE emp
		PCTFREE 30 PCTUSED 60;

Drop,rename and truncate tables.

To Drop a table:

              drop table esta_no_sirve;
	      drop table useless;

To Rename a table:

              alter table mal_nombre rename to buen_nombre;
	      alter table bad_name rename to good_name;

or the rename command can also be used: rename mal_nombre to buen_nombre

To Truncate:

              truncate table se_perdera_todo;
	      truncate table all_is_lost;

NOTE: A truncate statement cannot be rollbacked. Truncate is used to RESET the STORAGE parameters to the values when the table or cluster was created. Deleting rows with the TRUNCATE statement can be more efficient than dropping or re-creating a table. Dropping and re-creating a table invalidates the tables dependent objects , the priviliges on the table have to be regranted , and also requires re-creating the tables indexes, integrity constraints, triggers, and of course respecify its storage parameteres. TRUNCATING has none of these efffcts. thus its usefullness.

Including Constraints

Describe constraints

A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.

Describe constraints

A constraint is used to implement certain rules on columns in the table that will be created, these are normally business or operational rule.

Create and maintain constriants

not null : Requires a value for this column.
unique : Two rows cant have the same value in this column ( side effect, Oracle creates an index on this column )
primary key :Same as unique except that no row can have a null value, also it is possible to refer from other tables to this key.
check : Limit the range of the column. Example: rango integer check ( rango > 6 and rango <= 9)
references : This column can only contain values present in another tables primary key . Example:usuario not null references empleado

Constraints can also apply to multiple columns:

                     create table los_mejores (
		     identificacion integer primary key,
		     nombre_titulo  varchar2(45),
		     edad           integer,
		     unique(edad,identificacion) 
		     ); 

		     create table los_mejores (
		     identificacion integer primary key,
		     nombre_titulo  varchar2(45),
		     edad           integer,
		     check (edad <=35 or identificacion <=1000 )
		     );

In case you want a more friendly message, and not a cryptic constraint violated you can name the constraint like so:

                     create table los_mejores (
		     identificacion integer primary key,
		     nombre_titulo  varchar2(45),
		     edad           integer,
		     constraint los_primeros  
		     check (edad <=35 or identificacion <=1000 )
		     );

If the table already existed:

                    alter table mifamilia add(constraint los_primeros check(edad < 35 or id >1000));
		    alter table myfamily add(constraint the_first check(age < 35 or id > 1000));

To delete a constraint from an existing table:

                    alter table mifamilia drop primary key;
		    alter table myfamily drop primary key;

Or if you check the constraint name of the table in USER_CONSTRAINTS or ALL_CONSTRAINTS then you can drop the constraint by constraint_name

                   alter table mifamilia drop constraint los_primeros;
		   alter table myfamily drop constraint the_first;

In case the constraint is referenced by another table then the previous commands will fail unless the cascade parameter is specified.

Creating Views

Describe a View

When a certain query is used, it is often beneficial that instead of typing the long query every time, a view be created, the other reason a View might be created is Privacy, perhaps the developer should only have access to certain parts of the table, thus he is only granted permissions to the view. The actual query takes place in the same way as would a normal SQL query

Create a View

create view los_ciclistas
as
select nombre,marca,edad from usuarios us,pasatiempos pas where
us.id=pas.id ;
create view cyclists
as
select name,brand,age from users us,hobbys hob where us.id=hob.id;

Retrieve data thorugh a view

select marca from los_ciclistas
(Query the same as any simple table)

The data is inserted,updated and deleted as it were a table.

A join view is defined as a view that has more than one table or view in its FROM clause (a join) and that does not use any of the clauses : DISTINCT, AGGREGATION,GROUP BY, START WITH, CONNECT BY , ROWNUM , and set operations (UNION ALL, INTERSECT,etc...)

An updatable join view , which ivolves two or more base tables or views, where UPDATE, INSERT, and DELETE opearations are permitted. The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS and USER_UPDATABLE_COLUMNS contain information that indicates which of the view columns are updatable,deletable or insertable.

Create a View

drop view los_ciclistas
(Same as any table)

Oracle Data Dictonary

Describe the data dictonary views a user may access

There are three types of views generated when a database dictonary table when the database is created

DBA_ views : Contain information on all tables created
ALL_ views : Contain information on all objects to which the user querying the table has access.
USER_ views : Contain information on all objects the user querying the table owns.

NOTE: There also exists the V$ (dynamic performance) views which are normally accessed by DBAs. Both types of views (DBA_/USER_/ALL_) and V$ are generated on the X$ tables (internal RDBMS tables) and the dictonary tables (tab$,seg$,cons$... and all the others ending in $).

Query the data from the data dictonary

There are various tables and views from the data dictonary a user (mostly DBAs) can query like:

V$SGASTAT (Shows the SGA statistics) , DBA_CONSTRAINTS (Shows all the constraints on all tables), DBA_DATA_FILES (shows mapping between data files and tablespace ), etc,etc.

NOTE: It should be noted that the Data Dictionary is not a table, in most Oracle documentation the Data Dictionary is refered to as any table and/or view that contains data about data (thus the name dictionary)

Other Database objects

Describe database objects and their uses

Sequences are used to provide an order number list, they are normally used for primary keys in tables.

An index is generated when a specific column in a table contains a primary key or a unique constraint. This type of index (B*Tree) holds the value of the columns plus its ROWID. When Oracle queries for information on a specfic row in a table, it looks up the ROWID in the index and then pulls the data directly from the table.

Create,maintain and use sequences

create sequence la_primera_serie;
create sequence la_primera_serie start with 1;
select la_primera_serie.nextval from dual;
select la_primera_serie.currval from dual;
drop sequence la_primera_serie;

Create and maintain indexes

Index (B*Tree):
This type of index is created as :
```
    create index CODIGO_POSTAL
    on ESTADOS(Codigo_Postal, Descripcion)
  
```
This previous index is created on two columns in the ESTADOS table.
Index Organized Tables :
Index-Organized tables store their data as if the entire table was stored as an index. A normal index (B*Tree) normally stores only the indexed columns in the index (primary key, unique), an Indexed organized table stores all the tables columns in the index. Because all the table columns are stored as an index, the rows of the table do not have ROWIDs, the only constraint in order to declare a table an Index Organized Table is that is has to have a primary key, this type of Index is recommended ONLY for VERY static data.
```
    create table ESTADOS(
    Codigo_postal CHAR(2) primary key,
    Description
    VARCHAR2(25)
    )
    organization index;
  
```
Bitmap Index :
These types of index are used for tables that have very low cardinality (different values in the same column), for example a table that has a column width of 10000 rows, of only YES and NO, would be an appropriate case.
```
    create bitmap index EMPLEADO_CON_SEGURO
    on empleados(seguro_medico);
  
```
The other type of index is a CLUSTER INDEX, this index is the one created on the column shared by tables in the clusters.

Maintaining the Index

alter index ..... rebuild

Change Storage Values and Location :

alter index EMPLEADOS_CON_SEGURO rebuild
tablespace INDEXES
storage (initial 2M next 2M pctincrease 0);

Allocate the Index in the appropiate Tablespace before it is generated by the primary key column in the Table:

create table comida_rapida
( Tipo_de_comida varchar2(25),
Costo
number,
Lugar_de_restaurant varchar2(40),
constraint UBICACION primary key
(Lugar_de_Restaurant)
using index tablespace INDEXES
storage (initial 2M next 2M pctincrease 0));

Create private and public synonyms

Synonyms are pointers to other tables in the database, when a synonym is called Oracle replaces the synonym with the canonical table name.

Here is how Oracle Resolves a query for a table named GANGSTERS :

First the server checks to see if the table GANGSTERS exists in the user SCHEMA.
If the table does not exist, it check to see if a private synonym named GANGSTERS exists.
If the syononym exists then it substituted with the canonical table.
If the private synonym does not exist, the existence of a public synonym is checked.
If the public synonym does not exist it returns an error: table or view does not exist.

Create Views

  create public synonym GANGSTERS for licenciados
  create synonym GANGSTERS for licenciados

The first declaration is a public synoym an thus is available to all users. The second is a private synonym and is available for only the user who created it.Oracle does not check for the validity of the synonyms so they should be tested.

Drop Views

drop public synonym GANGSTERS
drop synonym GANGSTERS

As noted, if the synonym is public in order to drop it must be specified that the synonym is public, otherwise Oracle will not drop it.

Controlling User Access

Create users

In order to create a user, you have to specify at least the keyword identified which will be the password for the user. Other parameters which are recommended at creation are default tablespace and temp tablespace. These parameters can always be specfied at a later time along with other options such as quota, and grant - revoke individual privilieges or roles.

  create user daniel identified by daniel
  default tablespace users
  temporary tablespace temp;

Create roles to ease setup and maintanence of the security model

Once a user has been created it is necessary to grant him the appropriate privileges in order to use Oracle and its objects. Oracle defines roles which are lumped privileges used in order to facilitate the management of the database.

Oracle provides 3 system-level roles:

CONNECT : This role gives the user the privileges: CREATE SESSION,ALTER SESSION, CREATE CLUSTER,CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SYNONYM,CREATE TABLE, CREATE VIEW. The users who are given this role will not be able to create tables or clusters unless a specfic quota is granted to them. Other privileges which are not available are: create procedures,triggers, packages, abstract datatypes.
RESOURCE: The resource role has the following priviliges: :CREATE CLUSTER,CREATE INDEXTYPE,CREATE OPERATOR,CREATE PROCEDURE,CREATE SEQUENCE,CREATE TABLE,CREATE TRIGGER,CREATE TYPE. User who are granted this role are also granted UNLIMITED TABLESPACE, what this does is override any quota that was given on the user.
DBA : Has all the system privileges, including the admin option, what this means is that the user is able to grant SYSTEM privileges to other users.

In order to create a role you issue :

  create role SOLO_DESARROLLADORES;
  grant CREATE SESSION to SOLO_DESARROLLADORES;
  grant CONNECT to SOLO_DESARROLLADORES;

To grant this generated role to a user, you grant it like any other privilege or role:

grant SOLO_DESARROLLADORES to daniel;

The ALTER ROLE command can also be used to change the authorization needed to grant the role, the parameters can be:

  alter role .... identfied ( by password|externally|globally)

Views that store Role Information are :

DBA_ROLES : Contains the name of roles and their password status.
DBA_ROLE_PRIVS : Users who have been granted roles.
DBA_SYS_PRIVS : Users who have been granted system privileges.
DBA_TAB_PRIVS : Users who have been granted privileges on tables.
DBA_COL_PRIVS : Users who have been granted privileges on columns.
ROLE_ROLE_PRIVS : Roles that have been granted to other roles.
ROLE_SYS_PRIVS : System Privileges that have been granted to roles.
ROLE_TAB_PRIVS : Table Privileges that have been granted to roles.

Use the GRANT and REVOKE statements to grant and revoke object priviliges.

The following grants the select privilege on table employees to user daniel, NOTE: Instead of a user this could also be a role

  grant select on employees to daniel

To revoke the privilege:

  revoke select on employess from daniel

Other possiblities include :

grant select on employess to daniel with grant option

This previous command argument with grant option allows the granted user prviliges to designate OTHER users with this privilege. There is also the option with admin option which in functionality is pretty similar, the real difference is that in case the original grantee revokes the privilege from the original user it only revokes the privilege from the first user in case any other users got the privilege from this (now revoked) user, they will be protected and still posses the privilege. This is in contrast to the with grant option in which all other users in the same line (with the same parent) will also be revoked of the privilege, even though the revoke does not take place explicitly on the user name, in other words if your parent (the one who gave you the grant) is revoked from the privilege, you will also loose it.