Oracle แทรก PL/SQL อัปเดต ลบ & เลือกลงใน [ตัวอย่าง]

ธุรกรรม DML ใน PL/SQL

DML stands for Data Manipulation Language, the group of SQL commands that change the data stored in a table. Within a บล็อก PL/SQL, these commands carry out the manipulation work, while PL/SQL supplies the surrounding logic. DML deals with the operations below.

• การแทรกข้อมูล
• อัปเดตข้อมูล
• การลบข้อมูล
• การเลือกข้อมูล

In PL/SQL, data manipulation is performed only through SQL commands.

การแทรกข้อมูล

In PL/SQL, rows are added to a table with the SQL command INSERT INTO. This command takes the table name, the target columns, and the column values as input, then inserts the value into the base table.

The INSERT command can also take the values directly from another table using a SELECT statement rather than giving the values for each column. Through a SELECT statement, as many rows as the source table contains can be inserted at once.

ไวยากรณ์:

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

The above syntax shows the INSERT INTO command. The table name and values are mandatory fields, whereas column names are optional when the INSERT statement supplies values for every column of the table. The keyword VALUES is mandatory when the values are given separately, as shown above.

ไวยากรณ์:

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

This second form of INSERT INTO takes the values directly from <table_name2> using the SELECT command. The keyword VALUES must not be present here because the values are not supplied separately.

อัปเดตข้อมูล

Data update means changing the value of a column in an existing row. This is done with the UPDATE statement, which takes the table name, the column name, and the new value as input and updates the data.

ไวยากรณ์:

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;

The above syntax shows the UPDATE statement. The keyword SET instructs the PL/SQL engine to update the column with the value given. The WHERE clause is optional; if it is not given, the value of the mentioned column is updated in the entire table.

การลบข้อมูล

Data deletion means removing one full record from the database table. The DELETE command is used for this purpose.

ไวยากรณ์:

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

The above syntax shows the DELETE command. The keyword FROM is optional, and with or without the FROM clause the command behaves the same way. The WHERE clause is optional; if it is not given, then the entire table will be emptied.

การเลือกข้อมูล

Data projection, or fetching, means retrieving the required data from the database table. This is achieved with the SELECT command together with the INTO clause. The SELECT command fetches the values from the database, and the INTO clause assigns those values to the local variables of the PL/SQL block.

The points below need to be considered while using a SELECT statement with INTO:

• A SELECT statement should return only one record while using the INTO clause, because one variable can hold only one value. If the SELECT returns more than one row, the TOO_MANY_ROWS exception ถูกยกขึ้น
• The SELECT statement assigns the value to the variable in the INTO clause, so it needs at least one record to populate the value. If it does not find any record, the NO_DATA_FOUND exception is raised.
• The number of columns and their datatypes in the SELECT clause should match the number of variables and their datatypes in the INTO clause.
• ค่าต่างๆ จะถูกดึงออกมาและเติมในลำดับเดียวกับที่กล่าวไว้ในคำสั่ง
• The WHERE clause is optional and lets you place more restrictions on the records that are fetched.
• A SELECT statement can be used in the WHERE condition of other DML statements to define the values of the conditions.
• A SELECT statement used inside INSERT, UPDATE, or DELETE statements should not have an INTO clause, as it does not populate any variable in those cases.

ไวยากรณ์:

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

The above syntax shows the SELECT-INTO command. The keyword FROM is mandatory and identifies the table from which the data needs to be fetched. The WHERE clause is optional; if it is not given, then data from the entire table will be fetched.

1 ตัวอย่าง: In this example, we will see how to perform DML operations in PL/SQL. We will insert the four records below into the emp table.

Then we will update the salary of ‘XXX’ to 15000, delete the employee record ‘ZZZ’, and finally project the details of the employee ‘XXX’.

The screenshot below shows the complete PL/SQL block used for this example.

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;
/

Output:

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

Code คำอธิบาย:

• Code บรรทัดที่ 2-5: การประกาศตัวแปร
• Code บรรทัดที่ 7-14: Inserting the records into the emp table.
• Code สาย 15: Committing the insert transactions.
• Code บรรทัดที่ 17-19: Updating the salary of the employee ‘XXX’ to 15000.
• Code สาย 20: Committing the update transaction.
• Code สาย 22: Deleting the record of ‘ZZZ’.
• Code สาย 23: Committing the delete transaction.
• Code สาย 25: Selecting the record of ‘XXX’ and populating the variables l_emp_name, l_emp_no, l_salary, and l_manager.
• Code บรรทัดที่ 26-30: Displaying the fetched record values.

คำถามที่พบบ่อย