PL-SQL

PL-SQL.md

<title>PL-SQL</title>

PL/SQL

IP:193.226.51.37 aici avem tot (intra de pe un browser si pune-l in search bar)

( http://193.226.51.37/down/SGBD/index.html )

Mihai Gabriela: Sala 318, criogab@fmi.unibuc.ro
Username: grupa41
Password: oracle
Hostname: 193.226.51.37
Port: 1521
Service Name: o11g

SQL*Plus: Comenzile SQL*Plus accesează baza de date: Nu

PL/SQL

• Procedural, interpretat
• Trimitem blocuri de cod din client la server, ca SQL
• Definim functii/proceduri globale, sa fie folosite oricand
• Accesam cu el date din baza de date ^^
• In loc sa lansam o singura comanda, lansam un block de comenzi
• Daca o comanda nu e intr-un bloc PL/SQL, atunci e executata separat.
• Mai multe comenzi in afara = mai multe requesturi la server

Setup

In SQL Developer, View > DBMS Output > Click pe plusul verde din fereastra care a aparut > selecteaza conexiunea curenta

Printing

DBMS_OUTPUT.PUT_LINE ('Hello World');

Data Types

NUMBER cu subtipurile DEC/DECIMAL/NUMERIC, FLOAT/DOUBLE PRECISION,  
INT/INTEGER/SMALLINT, REAL
	NUMERIC(p, s) unde p=n total digits, s=n decimals
BINARY_FLOAT și BINARY_DOUBLE  
BINARY_INTEGER/PLS_INTEGER cu subtipurile NATURAL, NATURALN, POSITIVE,  
POSITIVEN, SIGNTYPE, SIMPLE_INTEGER

Variabile (de legatura)

VARIABLE my_var VARCHAR2(20)
...
SELECT department_name INTO :my_var FROM departments
-- Trebuie sa returneze un singur rand

Alternativ, putem face asa:

SELECT .... INTO dog.name, dog.age FROM ...

Daca avem un array dogs, putem face:

SELECT .... BULK COLLECT INTO dogs FROM ...

Daca vrem sa facem delete (gen pop):

DELETE FROM Dogs
WHERE breed = 'roachdog'
RETURNING dog_id BULK COLLECT INTO my_dog_ids;

Type Inferrence

my_dog Dogs%ROWTYPE;
my_var Employees.Manager_Id%TYPE;

Operatori:

:= Assignment
:x := &x Citeste de la tastatura (ATENTIE: Stringurile trebuie cu '')

Blocuri PL/SQL

DECLARE
	nume VARCHAR2(256) := 'Hello World';
BEGIN
...
EXCEPTION
....
END;
/

ATENTIE: Dupa END la un block, trebuie sa punem acel ‘/’
Avem voie sa bagam un alt DECLARE/BEGIN/END in alt bloc ^^
Astea nu intorc ceva by default, doar fac ceva

Scopeuri

<< MyScope >>
DECLARE
	myVar INT;
BEGIN
	...
	<< MyOtherScope >>
	BEGIN
...
MyScope.myVar := 20

Flow Control

IF / ELSIF / ELSE

For

FOR i IN 1..10 LOOP
	t(i):=i;
END LOOP;

Records (Data Structures)

TYPE Dog IS RECORD(age INT(2), name VARCHAR2(25) NOT NULL := 'caca');
dog Dog;
dog dogs%ROWTYPE; -- Acelasi ca in database

Putem sa facem asta apoi

SELECT nume, varsta INTO dog FROM dogs
DELETE FROM .... RETURNING nume, varsta INTO dog -- Pop gen

Daca zicem cu CREATE in fata, astea o sa fie salvate in database

Arrays (start at 1)

TYPE my_table_type IS TABLE OF NUMBER;
t my_table_type := my_table_type(1);
...
t.extend;
t(t.last) := 1;
t.extend;
t(t.last) := 2;
for i in 1..t.count loop
dbms_output.put_line(t(i));
end loop;

Maps

TYPE my_table_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;  
my_table my_table_type;

Accesam asa

my_table(7) := 420;
my_table.count -- Numarul de elemente setate

Null:

if NULL -- Asta intoarce false!

Warning: E o algebra intreaga, nu da niciodata cum te-ai astepta cand faci operatii cu null!!

Cursoare

Cursor = zona de memorie si modalitate de a parcurge un Active Set linie cu linie.

Active Set = Randurile intoarse de o cerere

CURSOR my_cursor IS SELECT ...;
OPEN   my_cursor;

Iteram prin el:

LOOP
	FETCH my_cursor INTO dog_iterator;
	EXIT WHEN my_cursor%NOTFOUND;
	...
END LOOP;
CLOSE my_cursor;

Sau mai simplu:

FOR elem IN my_cursor LOOP
	... elem.name
END LOOP;

Sau

FOR elem IN (select ...) ....

Note: Le inchide/deschide automat cu for.

Pus date in variabile:

FETCH my_cursor BULK COLLECT INTO my_employees;

Good to know:

my_cursor%ROWCOUNT = randul curent

Update cu cursor

CURSOR c IS (SELECT ... FOR UPDATE OF name NOWAIT);
FOR ...
UPDATE ... SET name = 'asdf' WHERE CURRENT OF c;

Proceduri / Functii

Functions (return something, usable in query)

CREATE OR REPLACE FUNCTION get_double(n number) RETURN NUMBER IS
	returned_number NUMBER;
BEGIN
	returned_number := n * 2;
	return returned_number;
EXCEPTION
	WHEN OTHERS THEN print...
END get_double;
get_double(20);

Procedures (no return, not usable in query)

Prin proceduri nu intoarcem ceva, dar putem modifica parametri.

CREATE OR REPLACE PROCEDURE print(msg VARCHAR) AS
BEGIN
	dbms_output.put_line(msg);
END;
/

Pentru parametri, avem keywordurile IN, OUT si IN OUT
- IN = read only
- OUT = assign a value
- IN OUT = modify its value

Triggers

• LMD (INSERT, UPDATE sau DELETE)
• INSTEAD OF (LMD pe views)
• LDD

Exista 2 tipuri de triggere:

• La nivel de comanda (normali) - aici nu ai voie sa accesezi :NEW si :OLD
• La nivel de linie (FR EACH ROW)

Exista 2 timpuri de trigger:

• BEFORE - Poti modifica datele care uremaza sa fie introduse
  • :NEW.money := 100
• AFTER - Aici nu prea ai voie

Sunt simpli, doar sintaxa e multa. Here goes:

CREATE TRIGGER my_trigger
	BEFORE DELETE or INSERT OF name ON Dogs
	[INSTEAD OF UPDATE sau INSERT sau DELETE]
	[FOR EACH ROW]
	[WHEN NEW.age < OLD.age]
BEGIN
	IF (:NEW.name = 'Ello') ...
		:NEW.name := 'Worll';
	IF UPDATING ...
	IF USER = 'grupa41' ...
	RAISE_APPLICATION_ERROR(-20001, 
END