PL/SQL - Reading data from databse and Inserting data into database

Reading data from database:

We can use the "SELECT INTO" statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list , there must be a corresponding, type compatible variable in the INTO list.

Usecase: fetch the customer name and address from customer table for customer id =10

DECLARE

c_id number:=10;

c_name varchar2(50);

c_addr varchar2(50);

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

Whats is %type:

DECLARE

c_id customer.customer_id%type:=10;

c_name customer.first_name%type;

c_addr customer.address%type;

BEGIN

SELECT first_name, address INTO c_name, c_addr FROM customer WHERE cutomer_id := c_id;

DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);

END;

/

Inserting data into Database:

We use "INSERT INTO" statement to insert data into database table.

DECLARE

c_id customer.customer_id%type :=1;

c_fname customer.first_name%type := 'Sri';

c_lname customer.last_name%type :='Das';

c_addr customer.address%type := ' 22 AE block Salt Lake kolkata';

BEGIN

INSERT INTO CUSTOMER(customer_id, first_name, last_name, address) VALUES (c_id,c_fname,c_lname,c_address);

COMMIT;

dbms_output.put_line('Data successfully Inserted');

END;

/

Note: we can also perform update a record and delete a record in the database table.