Sąveika su Oracle Duomenų bazė

Paskaita 4

Temos

• Kokie SQL sakiniai galimi vykdomajame PL/SQL bloke

• DML sakiniai PL/SQL-e• Transakcijos valdymo sakiniai PL/SQL-e• INTO dalės naudojimas SQL komandoje varčių

išsaugojimui• Skirtumai tarp uždarų ir atvirų kursorių• Kursoriaus atributų naudojimas

SQL Sakiniai PL/SQL-e

• Vienos eilutės atrinkimas iš DB naudojant SELECT komandą.

• Duomenų eilučių redagavimas vykdomas DML komandomis.

• Transakcijų valdymas vykdomas COMMIT, ROLLBACK ir SAVEPOINT komandomis.

SELECT Sakiniai PL/SQL-e• Duomenų atrinkimas iš duomenų bazės vykdomas SELECT sakinių pagalba.

• Syntax:

SELECT select_listINTO {variable_name[, variable_name]...

| record_name} FROM table[WHERE condition];

SELECT Sakiniai PL/SQL-e• INTO dalis privaloma.• Užklausa privalo grąžinti tik vieną eilutę.

SET SERVER OUTPUT ONDECLARE v_fname VARCHAR2(25);BEGIN SELECT first_name INTO v_fname FROM employees WHERE employee_id=200; DBMS_OUTPUT.PUT_LINE(' First Name is : '||v_fname);END;/

PL/SQL Duomenų atrinkimas: Pavyzdys• Grąžinome hire_date ir salary nurodytam darbuotojui.

DECLARE v_emp_hiredate employees.hire_date%TYPE; v_emp_salary employees.salary%TYPE; BEGIN SELECT hire_date, salary INTO v_emp_hiredate, v_emp_salary FROM employees WHERE employee_id = 100; END;/

• Visų darbuotojų atliginimų sumos iš tam tikro padalinio paskaičiavimas.

PL/SQL Duomenų atrinkimas

DECLARE v_sum_sal NUMBER(10,2); v_deptno NUMBER NOT NULL := 60; BEGIN SELECT SUM(salary) -- grupinė funkcija INTO v_sum_sal FROM employees WHERE department_id = v_deptno; DBMS_OUTPUT.PUT_LINE ('The sum of salary is ' || v_sum_sal);END;

Vardinimo susitarimaiDECLARE hire_date employees.hire_date%TYPE; sysdate hire_date%TYPE; employee_id employees.employee_id%TYPE := 176; BEGIN SELECT hire_date, sysdate INTO hire_date, sysdate FROM employees WHERE employee_id = employee_id; END;/

Vardinimo susitarimai

• Norint išvengti dviprasmybių WHERE dalyje nuduokite vardinimo susitarimus.

• Venkite duomenų lentelių stulpelių vardais vadinti savo kintamuosius.

• Sintaksinės klaidos galimos dėl to, kad PL/SQL visų pirmą tikrina lentelės stulpelio vardą.

• Lokalinių kintamųjų ir formalių parametrų vardai turi prioritetą prieš duomenų bazės lentelės vardą.

• Stulpelių vardai turi prioritetą prieš lokalinių kintamųjų vardus.

Duomenų manipuliavimas PL/SQL-e• Pakeitimai į duomenų bazę įnešami DML komandomis:

• INSERT• UPDATE• DELETE• MERGE

DELETE

INSERT

UPDATE MERGE

Naujų įrašų įterpimas: Pavyzdys• Informacija apie naują darbuotoją įterpimas į

lentelę EMPLOYEES.BEGIN INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES',CURRENT_DATE, 'AD_ASST', 4000);END;/

Duomenų pakeitimas: Pavyzdys• Visų darbuotojų kurie priklauso tam tikrai grupėj atliginimo padidinimas.

DECLARE sal_increase employees.salary%TYPE := 800; BEGIN UPDATE employees SET salary = salary + sal_increase WHERE job_id = 'ST_CLERK';END;/

. . .

Duomenų trynimas: Pavyzdys

• Informacija apie 10 skyrio darbuotojus trynimas iš employees lentelės.

DECLARE deptno employees.department_id%TYPE := 10; BEGIN DELETE FROM employees WHERE department_id = deptno;END;/

Eilučių sujungimas

• Eilučių įterpimas ar pakeitimas lentelėje copy_emp lentelės employees pagrindų.

BEGINMERGE INTO copy_emp c USING employees e ON (e.employee_id = c.empno) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, . . . WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, . . .,e.department_id);END;/

SQL Kursorius

• Kursorius tai rodykle į Oracle serverio rezervuoto atminties vieta naudojama SELECT sakinių rezultatams saugoti.

• Kursorių tipai.• Uždaras arba vidinis (Implicit): Kuriamas ir tvarkomas

interaktyviai Oracle serverio SQL sakiniams aptarnauti.• Atviras arba išorinis Explicit: Programuotojo deklaruojamas

Implicit cursor Explicit cursor

Uždaro SQL kursoriaus atributai• Naudojant SQL kursoriaus atributus, galima patikrinti SQL sakinių

rezultatus.

SQL%FOUND BOOLEAN atributas, kuris įgyja reikšmę TRUE , jei paskutinis SQL sakinys paveikia viena ar kelias eilutes

SQL%NOTFOUND BOOLEAN atributas, kuris įgyja reikšmę TRUE , jei paskutinis SQL sakinys ne paveikia nei vienos eilutės

SQL%ROWCOUNT eilučių skaičius paveiktas paskutinio SQL sakinio (sveika reikšmė)

Uždaro SQL kursoriaus atributai• Eilučių trynimas iš employees lentelės pagalvartotojo ID. Ištrintų eilučių skaičiaus atspausdinimas.

DECLARE v_rows_deleted VARCHAR2(30) v_empno employees.employee_id%TYPE := 176;BEGIN DELETE FROM employees WHERE employee_id = v_empno; v_rows_deleted := (SQL%ROWCOUNT || ' row deleted.'); DBMS_OUTPUT.PUT_LINE (v_rows_deleted);

END;

Transakcijos valdymo sakiniai

• Pradėti transakciją su pirma DML komanda iki COMMIT ar ROLLBACK

• Naudoti COMMIT ir ROLLBACK SQL sakiniuose transakcijos išoriniam (explicit) užbaigimui

Klausimas

• Naudojant PL/SQL SELECT sakinį INTO dalis privaloma, o užklausa gali grąžinti vieną arba daugiau eilučių.

1. True

2. False

Santrauka

• Sužinojome : • Kaip naudojami PL/SQL bloke DML, DDL ir

transakcijos valdymo sakinių.• Apie INTO dalės būtinumą PL/SQL SELECT sakinyje.• Skirtumus tarp uždaro ir atviro kursorių• SQL kursoriaus atributų naudojimą, norint patikrinti

SQL sakinių rezultatus

Praktika 4:

• This practice covers the following topics:• Selecting data from a table• Inserting data into a table• Updating data in a table• Deleting a record from a table