DATABASE INTERACTION IN PL/SQL

DATABASE INTERACTION IN PL/SQL

We can use any or the following SQL statements for SQL queries in PL/SQL code:

SELECT: for extracting data.

INSERT: for add new data.

UPDATE: for change in existing data.

DELETE: for remove existing data.

SELECT INTO STATEMENT: in SQL select statement is used to display the results on the screen. But in PL/SQL SELECT INTO statement is used to fetch the single record from the database and store it into another variables for further processing. Syntax:

SELECT INTO FROM [WHERE CONDITION];

Write the Code to input the emp’s no and print the name and salary of that emp.

DECLARE

ENO NUMBER;

EMP_NAME EMP.ENAME%TYPE;

SALARY EMP.SAL%TYPE;

BEGIN

ENO := &EMPNO;

SELECT ENAME, SAL INTO EMP_NAME, SALARY FROM EMP WHERE EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE(EMP_NAME);

DBMS_OUTPUT.PUT_LINE(SALARY);

END;