Transcript SQL Sakiniai PL/SQL-e

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.
SELECT select_list
•INTO
Syntax:
{variable_name[, variable_name]...
FROM
[WHERE
| record_name}
table
condition];
SELECT Sakiniai PL/SQL-e
• INTO dalis privaloma.
• Užklausa privalo grąžinti tik vieną eilutę.
SET SERVER OUTPUT ON
DECLARE
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;
/
PL/SQL Duomenų atrinkimas
• Visų darbuotojų atliginimų sumos iš tam tikro
padalinio paskaičiavimas.
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 susitarimai
DECLARE
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ų.
BEGIN
MERGE 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
SQL%NOTFOUND
SQL%ROWCOUNT
BOOLEAN atributas, kuris įgyja reikšmę TRUE ,
jei paskutinis SQL sakinys paveikia viena ar
kelias eilutes
BOOLEAN atributas, kuris įgyja reikšmę TRUE ,
jei paskutinis SQL sakinys ne paveikia nei vienos
eilutės
eilučių skaičius paveiktas paskutinio SQL sakinio
(sveika reikšmė)
Uždaro SQL kursoriaus
atributai
• Eilučių trynimas iš employees lentelės pagal
vartotojo 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