db2

An overview of databases using SQL / PLSQL / PostgreSQL.

Começar. É Gratuito
ou inscrever-se com seu endereço de e-mail
db2 por Mind Map: db2

1. PL/SQL Language overview

1.1. Declarations

1.1.1. Optional section where variables, cursors, subprograms and other elements are defined to be used later on in the program

1.1.2. DECLARE

1.1.2.1. Variables (global or local)

1.1.2.1.1. String

1.1.2.1.2. Integer

1.1.2.1.3. Real

1.1.2.1.4. Date

1.1.2.1.5. Boolean

1.1.2.2. Constants

1.1.2.2.1. varname CONSTANT datatype := value;

1.1.2.3. Procedures

1.1.2.3.1. CREATE OR REPLACE PROCEDURE prcd_name IS BEGIN dbms_output.put_line('This is the output'); END; /

1.1.2.3.2. CREATE PROCEDURE prc_name (i IN DOUBLE, f IN INTEGER h IN OUT FLOAT) IS BEGIN IF i<f THEN h:=f; ELSE h:=i;

1.1.2.4. Functions

1.1.2.4.1. CREATE FUNCTION F_Counter (i IN FLOAT, j IN OUT DOUBLE) RETURN INTEGER IS count_var INTEGER:=0; BEGIN SELECT count(*) INTO count_var FROM employees WHERE emp_id>90; RETURN count_var; END;/

1.1.2.4.2. CREATE OR REPLACE F_Sentence (i IN INTEGER) RETURN VARCHAR2(20) IS word_var; BEGIN IF i>10 word_var='LessThan 10' ELSE IF i<20 word_var='Less than 20' ELSE word_var='More'; RETURN word_var; END;

1.1.2.5. Cursors

1.1.2.5.1. CURSOR c_name IS SELECT * FROM employees WHERE id>0;

1.1.2.6. Triggers

1.1.2.6.1. triggers ~ stored programs which are automatically executed or fired in response to an event upon a table, view, schema or database

1.1.2.6.2. CREATE TRIGGER budget_rules BEFORE UPDATE OF salary ON employees WHEN (NEW.salary>1.5*OLD.salary)

1.1.2.6.3. create or replace trigger emp_bid before insert or delete FOR EACH ROW on employee referencing new as new3r old as old3r begin if to_char(sysdate,'Dy') in ('Sat','Sun') then raise_application_error(-29999,'No create/delete employees on weekend!'); end if; end; /

1.1.2.7. Exceptions (User-defined)

1.1.2.7.1. customError EXCEPTION;

1.2. Executable Commands

1.2.1. Mandatory section, consists of PL/SQL statements of the program. Must have at least one executable line of code (NULL included). Between Begin and End

1.2.2. BEGIN and END

1.2.2.1. Operators

1.2.2.1.1. + | - | * | / | **

1.2.2.1.2. = | != | <> | ~= | > | < | >= | <=

1.2.2.1.3. LIKE | BETWEEN | IN | IS | NULL

1.2.2.1.4. AND | OR | NOT

1.2.2.2. Conditions

1.2.2.2.1. IF - THEN

1.2.2.2.2. IF - THEN - ELSE

1.2.2.2.3. IF - THEN - ELSIF

1.2.2.2.4. CASE statemet

1.2.2.2.5. IF - THEN (nested)

1.2.2.3. Loops

1.2.2.3.1. LOOP

1.2.2.3.2. WHILE LOOP

1.2.2.3.3. FOR LOOP

1.2.2.3.4. LOOP (nested)

1.2.2.3.5. <<others

1.2.3. Using cursors

1.2.3.1. 1. OPEN cr_name;

1.2.3.2. 2. FETCH cr_name INTO cr_name, cr_id;

1.2.3.3. 3. CLOSE cr_name;

1.2.4. Using procedures

1.2.4.1. executing procedure

1.2.4.1.1. from a pl/sql block

1.2.4.1.2. from the sql prompt

1.2.4.2. deleting procedures

1.2.4.2.1. DROP PROCEDURE prcd_name;

1.2.5. Using functions

1.2.5.1. executing function

1.2.5.1.1. from the pl sql block

1.2.5.1.2. from sql prompt

1.2.5.2. deleting functions

1.2.5.2.1. DROP FUNCTION F_Counter

1.3. Exception Handling

1.3.1. Optional section that contains exceptions that handle program errors. Between Begin and End

1.3.2. BEGIN and END

1.3.2.1. EXCEPTION

1.3.2.1.1. WHEN ErrorNo_data_found THEN dbmps_output.put_line('No such entry');

1.3.2.2. Raising Exceptions

1.3.2.2.1. IF table.var > 10 THEN RAISE ExceptionName; END IF;

1.3.2.2.2. IF table.string = NULL THEN RAISE customError; END IF;

1.4. others

1.4.1. to run code from sql command line you might need to type / at the end of the program, on a new line

1.4.2. Package

1.4.2.1. Package Specification

1.4.2.1.1. DECLARE variables, subprograms, etc that can be referenced from outside the package

1.4.2.2. Package Body Definition

1.4.2.2.1. CREATE the code and instructions inside the subprogram

1.4.2.3. Using package

1.4.2.3.1. pack_name.prc_name(2.4,6.8);

1.4.3. Record

1.4.3.1. addhere

1.4.4. -- comments are preceded by --

2. *

2.1. INDEX

2.1.1. e.g: CREATE [UNIQUE] INDEX index_name ON table_name [column_name1,...]

2.1.1.1. an index is a pointer to data in a table.

2.1.1.1.1. Used for

2.1.1.1.2. Not indicated for:

2.1.1.2. [column_name,...] is optional and creates the index for only those columns specified. Can be single-column indexes and composite intexes

2.1.1.3. [UNIQUE] is optional and creates unique values within the index . Helps with performance and integrity

2.1.2. e.g: DROP INDEX intex_name

2.2. STORED PROCEDURES

2.2.1. These are PL/SQL functions, packages or procedures. Running any of the following commands will place you in PL/SQL mode

2.2.1.1. CREATE FUNCTION

2.2.1.2. CREATE LIBRARY

2.2.1.3. CREATE PACKAGE

2.2.1.4. CREATE PACKAGE BODY

2.2.1.5. CREATE PROCEDURE

2.2.1.6. CREATE TRIGGER

2.2.1.7. CREATE TYPE

2.3. DESCRIBE

2.3.1. addhere

3. MANIPULACAO - DML

3.1. Insert

3.1.1. e.g: INSERT INTO Employees [(EmployeeID,LastName,FirstName,...)] VALUES (14, 'Tenzin', 'Gyatso', 07/06/1935, 'dl14.pic' , 'The 14th Dalai Lama')

3.1.1.1. Specifying the field names before the VALUES is [optional], but useful when you only need to insert to certain fields and leave the others NULL. Otherwise, you input values in the order they appear in the table structure

3.2. Update

3.2.1. e.g: UPDATE Employees SET LastName='Gyatso', FirstName='Tenzin' [WHERE] EmployeeID=14

3.2.1.1. WHERE is not mandatory, but if not present it will evaluate to 1 and UPDATE the whole table only with 'Tenzin Gyatso'. That's not usualy something that you want

3.3. Delete

3.3.1. ```SQL e.g: DELETE FROM Employees [WHERE] EmployeeID=14 ```

3.3.1.1. WHERE is not mandatory, but if not present it will evaluate to 1 and DELETE the whole table records. That's not usualy something that you want

4. DEFINICAO - DDL

4.1. Create objects (tables, views, triggers, store procedures), change the structure of databases, delete objects altogether or just its contents.

4.2. CREATE ROLE role_chosen_name [identified by parola_mea_secreta]

4.3. Create

4.3.1. CREATE TABLE SpecialPersonnel( Name CHAR(25) NOT NULL, SecurityClearence CHAR (1) NOT NULL, LastLogin TIME DateOfEmployment DATE, PRIMARY KEY( EmployeeID ) UNIQUE );

4.3.1.1. Tells the database you want to create a table with the specified FieldNames and DataTypes. NOT NULL is not mandatory but it specifies that the field can't ever be NULL. The same with UNIQUE, they can both be used on the same fieldname

4.3.2. CREATE [OR REPLACE] VIEW view_name AS SELECT column_name from table_name WHERE condition

4.4. Alter

4.4.1. ALTER TABLE SpecialPersonnel [ADD / DROP COLUMN / MODIFY COLUMN / MODIFY / ADD CONSTRAINT / DROP CONSTRAINT / DROP CONSTRAINT / DROP PRIMARY KEY] <....>

4.4.1.1. ALTER TABLE SpecialPersonnel ADD Age INTEGER

4.4.1.1.1. Adds a new column with name and data type

4.4.1.2. ALTER TABLE SpecialPersonnel DROP COLUMN Age

4.4.1.2.1. Deletes a column specified

4.4.1.3. ALTER TABLE SpecialPersonnel MODIFY COLUMN Age INTEGER [NOT NULL]

4.4.1.3.1. NOT NULL specifies that the Age can't be null on insertion/altering

4.4.1.4. ALTER TABLE SpecialPersonnel ADD CONSTRAINT MyConstraint CHECK (SecurityClearence >3)

4.4.1.4.1. Sets a condition for the SecurityClearance that it should be more >3, otherwise it can't exist in this table (when inserted, updated..).

4.4.1.5. ALTER TABLE SpecialPersonnel ADD CONSTRAINT AddPKConstraint UNIQUE (Name,PRIMARYKEY)

4.4.1.5.1. Sets the constraint that the Name and PRIMARY KEY fields have to be UNIQUE

4.4.1.6. ALTER TABLE SpecialPersonnel ADD PRIMARY KEY (PrimaryKey..);

4.4.1.6.1. Adds a Primary Key constraint to a field

4.4.1.7. ALTER TABLE SpecialPersonnel DROP CONSTRAINT MyUniquePKConstraint / MyConstraint

4.4.1.7.1. Removes a NamedConstrained from the table

4.5. Drop

4.5.1. DROP TABLE SpecialPersonnel

4.5.1.1. Deletes the TABLE from the database. It notifies by confirming 'Query OK'. If we use DESC SpecialPersonnel afterwards, we get an error saying it doesn't exist