3.Interacting with Oracle Server

PL/SQL
(Using SELECT/UPDATE/INSERT/DELETE & DDL in Plsql)
------------------------------------------------

Using SELECT in plsql blocks

Syntax: BEGIN
SELECT T1.COL1,
T1.COL2,
T2.COL1,
T2.COL2
INTO variable1,
variable2,
variable3,
variable4
FROM TABLE1 T1,
TABLE2 T2
WHERE CONDITION;

Points to remembers:

  1. All columns selected should be assigned to variable using INTO
  2. Select statement should return only one record, and it should not return more than one record
  3. Data types of selected columns and variable should be matched
  4. All select statements should be in executable statment begin..end

Example: Develop a program to select a supplier information (select vendor_id and vendor name)

SQL> ed filename.sql

DECLARE
ln_venor_id number;
lc_vendor_name varchar2(10);
BEGIN
SELECT s.vendor_id,
s.vendor_name
INTO ln_vendor_id,
lc_vendor_name
FROM supplier s
WHERE s.vendor_id = 101;

dbms_output.put_line ('Supplier Id '  ||ln_vendor_id);
dbms_output.put_line ('Supplier name '||lc_vendor_name);

END;

SQL> @ filename.sql

Supplier Id 101

Supplier name dell

Example2: Select mutiple records or 2 records

DECLARE
ln_vendor_id number;
lc_vendor_name varchar2(10);

ln_eno     number;
lc_ename     emp.ename%type;  -- %type is called attribute which will capture the data type from the table
ln_sal     emp.sal%type;
ld_doj     date;

BEGIN

BEGIN
SELECT s.vendor_id,
s.vendor_name
INTO ln_vendor_id,
lc_vendor_name
FROM supplier s
WHERE s.vendor_id = 101;

dbms_output.put_line ('Supplier Id '||ln_vendor_id);
dbms_output.put_line ('Supplier name '||lc_vendor_name);

END;

BEGIN
SELECT s.vendor_id,
s.vendor_name
INTO ln_vendor_id,
lc_vendor_name
FROM supplier s
WHERE s.vendor_id = 102;

dbms_output.put_line ('Supplier Id '||ln_vendor_id);
dbms_output.put_line ('Supplier name '||lc_vendor_name);

END;

BEGIN
SELECT eno,
ename,
sal,
doj
INTO ln_eno,
lc_ename,
ln_sal,
ld_doj
FROM emp
where eno = 105;
dbms_output.put_line (‘Eno ‘||ln_eno);
dbms_output.put_line (‘Ename ‘||lc_ename);
dbms_output.put_line (‘sal ‘||ln_sal);
dbms_output.put_line (‘doj ‘||ld_doj);
END;
END;

SQL> @ filename.sql

In the above example if any block is failed to execute then plsql engine stop and not continue the execution process.
ex. if there is an error in 1st block then 2nd and 3rd block will not be executed
ex. if 2nd block has an error then 3rd block will not be executed

Using Update

SQL> ed is3.sql

DECLARE
LN_ENO NUMBER:=&LN_ENO;
BEGIN
UPDATE EMP_NEW
SET SAL=SAL+200;
WHERE ENO=LN_ENO;
COMMIT;
DBMS_OUTPUT.PUT_LINE (‘Successfully updated’);
END;

SQL> @ IS3.SQL
10 /
Enter value for ln_eno: 111
old 2: LN_ENO NUMBER:=&LN_ENO;
new 2: LN_ENO NUMBER:=111;
Successfully updated

sql> SELECT * FROM EMP_NEW;

Using Insert and select

DECLARE
ln_eno number;
lc_ename emp_new.ename%type;
ln_dept_id number;

BEGIN
— Inserting values

BEGIN
INSERT INTO EMP_NEW(ENO,ENAME,DEPT_ID)
VALUES(121,’ZZZ’,555);
DBMS_OUTPUT.PUT_LINE (‘iNSERTED SUCCESSFULLY’);
commit;
END;

— Selecting values
BEGIN
SELECT eno,
ename,
dept_id
INTO ln_eno,
lc_ename,
ln_dept_id
FROM emp_new
where ENO=121;

DBMS_OUTPUT.PUT_LINE (‘ENO ‘||LN_ENO);
DBMS_OUTPUT.PUT_LINE (‘ENAME ‘||LC_ENAME);
DBMS_OUTPUT.PUT_LINE (‘DEPT ID ‘||LN_DEPT_ID);
END;

END;

Task: Select the purchase order infomration along with vendor details of a po.
Task: Increase the price of a po in purchase table
Task: Show deptid, deptname and number of employees in selected dept.
o/p
dept id = 101
dept name = dell
number of employees = 20