Oracle PL/SQL Infoga, Uppdatera, Ta bort och välj i [Exempel]

DML-transaktioner i PL/SQL

DML står för Data Manipulation Language, gruppen av SQL kommandon som ändrar data som lagras i en tabell. Inom en PL/SQL-block, dessa kommandon utför manipulationsarbetet, medan PL/SQL tillhandahåller den omgivande logiken. DML hanterar operationerna nedan.

• Datainsättning
• Uppdatering av data
• Radering av data
• Dataval

I PL/SQL utförs datamanipulation endast via SQL-kommandon.

Datainsättning

I PL/SQL läggs rader till i en tabell med SQL-kommandot INSERT INTO. Detta kommando tar tabellnamnet, målkolumnerna och kolumnvärdena som indata och infogar sedan värdet i bastabellen.

INSERT-kommandot kan också hämta värdena direkt från en annan tabell med hjälp av ett SELECT-uttryck istället för att ange värdena för varje kolumn. Genom ett SELECT-uttryck kan så många rader som källtabellen innehåller infogas samtidigt.

Syntax:

BEGIN
INSERT INTO <table_name>(<column1>,<column2>,...,<column_n>)
VALUES(<value1>,<value2>,...,<value_n>);
END;

Syntaxen ovan visar INSERT INTO-kommandot. Tabellnamnet och värdena är obligatoriska fält, medan kolumnnamnen är valfria när INSERT-satsen anger värden för varje kolumn i tabellen. Nyckelordet VALUES är obligatoriskt när värdena anges separat, som visas ovan.

Syntax:

BEGIN
INSERT INTO <table_name>(<column1>,<column2>,...,<column_n>)
SELECT <column1>,<column2>,...,<column_n> FROM <table_name2>;
END;

Denna andra form av INSERT INTO tar värdena direkt från med hjälp av SELECT-kommandot. Nyckelordet VALUES får inte finnas här eftersom värdena inte anges separat.

Uppdatering av data

Datauppdatering innebär att ändra värdet på en kolumn i en befintlig rad. Detta görs med UPDATE-satsen, som tar tabellnamnet, kolumnnamnet och det nya värdet som indata och uppdaterar informationen.

Syntax:

BEGIN
UPDATE <table_name>
SET <column1>=<value1>,<column2>=<value2>,<column_n>=<value_n>
WHERE <condition that uniquely identifies the record that needs to be updated>;
END;

Syntaxen ovan visar UPDATE-satsen. Nyckelordet SET instruerar PL/SQL-motorn att uppdatera kolumnen med det angivna värdet. WHERE-satsen är valfri; om den inte anges uppdateras värdet för den nämnda kolumnen i hela tabellen.

Radering av data

Dataradering innebär att ta bort en hel post från databastabellen. DELETE-kommandot används för detta ändamål.

Syntax:

BEGIN
DELETE FROM <table_name>
WHERE <condition that uniquely identifies the record that needs to be deleted>;
END;

Syntaxen ovan visar DELETE-kommandot. Nyckelordet FROM är valfritt, och med eller utan FROM-klausulen beter sig kommandot på samma sätt. WHERE-klausulen är valfri; om den inte anges kommer hela tabellen att tömmas.

Dataval

Dataprojektion, eller hämtning, innebär att hämta nödvändig data från databastabellen. Detta uppnås med SELECT-kommandot tillsammans med INTO-klausulen. SELECT-kommandot hämtar värdena från databasen, och INTO-klausulen tilldelar dessa värden till de lokala variablerna i PL/SQL-blocket.

Följande punkter måste beaktas när du använder en SELECT-sats med INTO:

• En SELECT-sats ska endast returnera en post när INTO-satsen används, eftersom en variabel bara kan innehålla ett värde. Om SELECT returnerar mer än en rad, TOO_MANY_ROWS undantag höjs.
• SELECT-satsen tilldelar värdet till variabeln i INTO-satsen, så den behöver minst en post för att fylla i värdet. Om den inte hittar någon post utlöses undantaget NO_DATA_FOUND.
• Antalet kolumner och deras datatyper i SELECT-klausulen ska matcha antalet variabler och deras datatyper i INTO-klausulen.
• Värdena hämtas och fylls i i samma ordning som nämns i uttalandet.
• WHERE-klausulen är valfri och låter dig sätta fler begränsningar för de poster som hämtas.
• En SELECT-sats kan användas i WHERE-villkoret för andra DML-satser för att definiera värdena för villkoren.
• En SELECT-sats som används inuti INSERT-, UPDATE- eller DELETE-satser bör inte ha en INTO-sats, eftersom den i dessa fall inte fyller i någon variabel.

Syntax:

BEGIN
SELECT <column1>,...,<column_n> INTO <variable1>,...,<variable_n>
FROM <table_name>
WHERE <condition to fetch the required records>;
END;

Syntaxen ovan visar SELECT-INTO-kommandot. Nyckelordet FROM är obligatoriskt och identifierar tabellen från vilken data ska hämtas. WHERE-klausulen är valfri; om den inte anges kommer data från hela tabellen att hämtas.

Exempel 1: I det här exemplet ska vi se hur man utför DML-operationer i PL/SQL. Vi kommer att infoga de fyra posterna nedan i tabellen emp.

Sedan uppdaterar vi lönen för 'XXX' till 15000, tar bort medarbetarposten 'ZZZ' och projicerar slutligen detaljerna för medarbetaren 'XXX'.

Skärmdumpen nedan visar det kompletta PL/SQL-blocket som används i det här exemplet.

DECLARE
l_emp_name VARCHAR2(250);
l_emp_no NUMBER;
l_salary NUMBER;
l_manager VARCHAR2(250);
BEGIN
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('BBB',1000,25000,'AAA');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('XXX',1001,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('YYY',1002,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('ZZZ',1003,7500,'BBB');
COMMIT;
Dbms_output.put_line('Values Inserted');
UPDATE EMP
SET salary=15000
WHERE emp_name='XXX';
COMMIT;
Dbms_output.put_line('Values Updated');
DELETE emp WHERE emp_name='ZZZ';
COMMIT;
Dbms_output.put_line('Values Deleted');
SELECT emp_name,emp_no,salary,manager INTO l_emp_name,l_emp_no,l_salary,l_manager FROM emp WHERE emp_name='XXX';
Dbms_output.put_line('Employee Detail');
Dbms_output.put_line('Employee Name:'||l_emp_name);
Dbms_output.put_line('Employee Number:'||l_emp_no);
Dbms_output.put_line('Employee Salary:'||l_salary);
Dbms_output.put_line('Employee Manager Name:'||l_manager);
END;
/

Produktion:

Values Inserted
Values Updated
Values Deleted
Employee Detail
Employee Name:XXX
Employee Number:1001
Employee Salary:15000
Employee Manager Name:BBB

Code Förklaring:

• Code rad 2-5: Deklarera variablerna.
• Code rad 7-14: Infogar posterna i emp-tabellen.
• Code rad 15: Verkställer infogningstransaktionerna.
• Code rad 17-19: Uppdaterar lönen för medarbetaren 'XXX' till 15 000.
• Code rad 20: Utför uppdateringstransaktionen.
• Code rad 22: Tar bort posten 'ZZZ'.
• Code rad 23: Verkställer borttagningstransaktionen.
• Code rad 25: Markera posten 'XXX' och fyll i variablerna l_emp_name, l_emp_no, l_salary och l_manager.
• Code rad 26-30: Visar de hämtade postvärdena.

Vanliga frågor