1) DML Transactions in PL/SQL:
===========================
DML stands for Data Manipulation Language. These statements are mainly used to perform the manipulation activity. It deals with the below operations.
Data Insertion
Data Update
Data Deletion
Data Selection
In PL/SQL, we can do the data manipulation only by using the SQL commands.
INSERT Statement:
================
BEGIN
INSERT INTO <table_name>(<column1 >,<column2>,…<column_n>)
VALUES(<valuel><value2>,…:<value_n>);
END;
Example:
========
begin
insert into customer values( 100,’NIC IT Academy’,9090909090,32,20);
COMMIT;
end;
INSERT INTO SELECT:
==================
BEGIN
INSERT INTO <table_name>(<columnl>,<column2>,…,<column_n>)
SELECT <columnl>,<column2>,.. <column_n> FROM <table_name2>;
END;
Example:
========
begin
insert into customer select * from customer1;
COMMIT;
end;
Update Statement:
=================
BEGIN
UPDATE <table_name>
SET <columnl>=<VALUE1>,<column2>=<value2>,<column_n>=<value_n>
WHERE <condition that uniquely identifies the record that needs to be update>;
END;
Example:
========
begin
update customer set cust_name=’Welcome’; –where cust_id=100003;
COMMIT;
end;
DELETE Statement:
=================
BEGIN
DELETE
FROM
<table_name>
WHERE <condition that uniquely identifies the record that needs to be update>;
END;
SELECT Statement:
=================
BEGIN
SELECT <columnl>,..<column_n> INTO <vanable 1 >,. .<variable_n>
FROM <table_name>
WHERE <condition to fetch the required records>;
END;
Example code:
=============
DECLARE
v_cust_id NUMBER;
v_cust_name VARCHAR2(250);
v_mobile NUMBER;
v_city VARCHAR2(250);
BEGIN
insert into customer values( 100,’NIC IT Academy’,9090909090,32,20);
COMMIT;
Dbms_output.put_line(‘Values Inserted’);
update customer set cust_name=’Welcome’; –where cust_id=100003;
COMMIT;
Dbms_output.put_line(‘Values Updated’);
DELETE from customer WHERE cust_id=100000;
COMMIT;
Dbms_output.put_line(‘Values Deleted’);
SELECT cust_id,cust_name INTO v_cust_id,v_cust_name FROM customer WHERE cust_id=100;
Dbms_output.put_line(‘Cust Details ‘||v_cust_id||’ name ‘||v_cust_name);
END;
/
2) % TYPE Attribute
syntax:
variable_name tablename.column_name%TYPE;
3) %ROWTYPE
Syntax:
variable_name table_name%rowtype;
—————————————————————-
create table customer
(
cust_id number(8),
cust_name varchar2(40),
dob date,
mobile_no number(10),
city varchar2(40)
);
insert into customer values(1000,’Arun’,to_date(’12/09/1985′,’mm/dd/yyyy’),9090909090,’Chennai’);
insert into customer values(1001,’John’,to_date(’01/27/1982′,’mm/dd/yyyy’),9090909093,’Pune’);
insert into customer values(1002,’Babu’,to_date(’06/23/1995′,’mm/dd/yyyy’),9090909089,’Hyd’);
commit;
select * from customer;
‘+91-8909909090’
declare
v_mobile_no number(10);
begin
select mobile_no into v_mobile_no from customer where cust_id=1002;
dbms_output.put_line(‘The mobile number is ‘||v_mobile_no);
end;
alter table customer modify mobile_no varchar2(40);
rename customer to customer_bkp;
create table customer
(
cust_id number(8),
cust_name varchar2(40),
dob date,
mobile_no varchar2(20),
city varchar2(40)
);
insert into customer (select cust_id,cust_name,dob,’+91-‘||mobile_no,city from customer_bkp);
commit;
truncate table customer;
select * from customer;
declare
v_mobile_no hr.customer.mobile_no%type;
begin
select mobile_no into v_mobile_no from customer where cust_id=1002;
dbms_output.put_line(‘The mobile number is ‘||v_mobile_no);
end;
declare
v_customer hr.customer%rowtype;
begin
select * into v_customer from customer where cust_id=1002;
dbms_output.put_line(‘The customer name is ‘||v_customer.cust_name);
dbms_output.put_line(‘The customer dob is ‘||v_customer.dob);
end;
