PL/SQL - ANONYMOUS BLOCK, UPDATE COLUMNS

Declare
   V_dept_id xxdtj_Employees.department_Id%Type:=10;
Begin
   Update Xxdtj_Employees Set Salary=Salary + 1000
   Where Department_Id=V_dept_id;
   Dbms_Output.Put_Line(V_DEPT_ID);
End;

OUTPUT :
   10

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL - TRIGGER with Example Programs

First, create two tables for the same structure.
   create table empl(name varchar2(50),company       varchar2(50),join_date date);
   create table employee as select * from empl where 1 = 1;

create or replace trigger insert_empl
after insert or update or delete
on empl
for each row
declare
      v_name varchar2(30);
begin
     if inserting then
         insert into employee values(:new.name,:new.company,sysdate);
     end if;
end;


Output :
       insert into empl values('suriya','abc',sysdate);
      select * from employee;
           suriya abc 09-MAR-18.

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL - PROCEDURE IN /OUT PARAMETER

create or replace procedure xx1
(a in out number)
is
begin
     a:=a+4;
end;

OUTPUT :
  declare
      a number:=&a;
begin
     xx1(a);
     dbms_output.put_line('the value is' || a);
end;

  Enter number 6
    the value is10

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL - SELECT statement using cursor

DECLARE
       CURSOR c_deptno IS SELECT first_name, salary, department_id
       FROM EMP;
BEGIN
     For x in c_deptno
    Loop
     dbms_output.put_line(x.first_name ||' '||x.salary||' '||x.department_id);
    End loop;
     close c_deptno;
End;

output:
        Steven 24000 90
        Neena 17000 90
        Lex 17000 90
        Alexander 9000 60

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL: SELECT Using Explicit Cursor to Fetch All Records

Note:

        An explicit cursor returns more than one row.

declare
       cursor v_cur is select employee_id,first_name,salary
       from emp;
       v_id emp.employee_id%type;
       v_name emp.first_name%type;
       v_sal emp.salary%type;
begin
      open v_cur;
      loop
      fetch v_cur into v_id,v_name,v_sal;
      exit when v_cur%notfound;
      dbms_output.put_line(v_id||' '||v_name||' '||v_sal);
      end loop;
      close v_cur;
end;

output:
100 Steven 24000
101 Neena 17000
102 Lex 17000
103 Alexander 9000
104 Bruce 6000
105 David 4800

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.