=====PL/SQL Queries [HR SCHEMA]=====

1)PRINT EMPID,JOBID,SALARY AND DEPTID OF EACH EMPLOYEE(HR SCHEMA)

declare
e_id employees.employee_id%type;
j_id employees.job_id%type;
sal employees.salary%type;
dept employees.department_id%type;

cursor c1 is
select employee_id,job_id,salary,department_id from employees;
begin
open c1;
loop
fetch c1 into e_id,j_id,sal,dept;
exit when c1%notfound;
dbms_output.put_line(e_id||' '||j_id||' '||sal||' '||dept);
end loop;
close c1;
end;
/

2)PRINT NAME OF EACH EMPLOYEE WITH A "HELLO" AS PREFIX(HR SCHEMA)

declare
msg varchar2(20) := 'Hello';
e_name employees.first_name%type;

cursor c1 is
select first_name from employees;
begin
open c1;
loop
fetch c1 into e_name;
exit when c1%notfound;
dbms_output.put_line(msg||'-'||e_name);
end loop;
end;

3) PRINT 1 TO n (n is user input)

declare
num number:= ⦥
begin
--num := 1
for i in 1..num loop
dbms_output.put_line(i);
end loop;
end;
/

4) PRINT RADIUS,DISMETER,PERIMETER,AREA OF A CIRCLE(INPUT IS RADIUS)

--PRINT RADIUS,DIAMETER,PERIMETER,AREA OF A CIRCLE(INPUT IS RADIUS)
declare
radius number := &user_radius;
diameter number;
perimeter number;
area number;
pi constant number := 3.14;
begin
diameter := radius*2;
perimeter := 2*pi*radius;
area := pi*radius**2;
DBMS_OUTPUT.PUT_LINE('Diameter: '||''||diameter);
dbms_output.put_line('Perimeter: '||''||perimeter);
dbms_output.put_line('Area: '||''||area);

end;
/

5) Check if a number is even or odd

declare
num number := &number;
result number;
begin
result := mod(num,2);
if result = 0 then
dbms_output.put_line(num||' is an even number');
else
dbms_output.put_line(num||' is an odd number');
end if;
end;
/

6) --increase score of personid=2 by +22(Example Schema) if the person's score is

less than 20
declare
p_id number;
begin
select PERSONID into p_id from person where PERSONID=2;
if p_id < 20 then
update person set score = score+22 where personid=2;
end if;
end;
/

7) The following program uses a nested basic loop to find the prime numbers from 2
to 100 −

DECLARE
i number(3);
j number(3);
BEGIN
i := 2;
LOOP
j:= 2;
LOOP
exit WHEN ((mod(i, j) = 0) or (j = i));
j := j +1;
END LOOP;
IF (j = i ) THEN
dbms_output.put_line(i || ' is prime');
END IF;
i := i + 1;
exit WHEN i = 50;
END LOOP;
END;
/
8) example of nested for loop
DECLARE
i number(1);
j number(1);
BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END loop;
END loop;
END;
/

9) example of if,elsif,else
DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
dbms_output.put_line('Value of a is 20' );
ELSIF ( a = 30 ) THEN
dbms_output.put_line('Value of a is 30' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
dbms_output.put_line('Exact value of a is: '|| a );
END;
/

10. Cursor with add select statement in the loop

declare
r_cust customer_orders%ROWTYPE;
v_id customer_orders.order_id%type;
cursor c1 is
select * from customer_orders;
begin
open c1;
loop
fetch c1 into r_cust.ORDER_ID, r_cust.CUSTOMER_ID, r_cust.ORDER_DATE,
r_cust.ORDER_AMOUNT;
exit when c1%notfound;
select order_id into v_id from customer_orders where order_id =
r_cust.ORDER_ID;
dbms_output.put_line(v_id);
end loop;
close c1;
end;

11. %rowtype examples

declare
cursor c1 is
select TRX_DATE, MERCHANT from payments_data;
v_payment_cursor c1%rowtype;
begin
open c1;
loop
fetch c1 into v_payment_cursor;
 exit when c1%notfound;
dbms_output.put_line(v_payment_cursor.TRX_DATE||' '||
v_payment_cursor.MERCHANT);
end loop;
close c1;
end;

{"headers": {"Weather": ["id", "recordDate", "temperature"]}, "rows": {"Weather":

[[1, "2000-12-14", 3], [2, "2000-12-16", 5]]}}

------------------- PL-SQl Fundamentals part-1

Course--------------------------------
-------------------------------------------------------------------------------

---PL/SQL Queries---
1. Float Variable
declare
v_var float := 2.3;
begin
dbms_output.put_line(v_var);
end;

2. (schema = cusis_common pte7)

declare
v_item_no cem_smx_su_t.item_no%type := '00513236';
v_unit_code_rcv cem_smx_su_t.unit_code_rcv%type;

cursor c1 is
select unit_code_rcv from cem_smx_su_t
where item_no = v_item_no;
begin
open c1;
loop
fetch c1 into v_unit_code_rcv;
exit when c1%notfound;
dbms_output.put_line(v_unit_code_rcv);
end loop;
exception
when others then
dbms_output.put_line('user exception');
end;

3. (schema = cusis_common pte7)[record datatype]

declare
type cem_supplier_matrix is record ( v_item_no cem_smx_su_t.item_no%type,
v_unit_type_rcv cem_smx_su_t.unit_type_rcv
%type,
v_transaction_time
cem_smx_su_t.transaction_time%type);
r_martix_supplier cem_supplier_matrix;

cursor c1 is
select item_no,unit_type_rcv,transaction_time from cem_smx_su_t;
begin
open c1;
loop
 fetch c1 into
r_martix_supplier.v_item_no,r_martix_supplier.v_unit_type_rcv,r_martix_supplier.v_t
ransaction_time;
exit when c1%notfound;
dbms_output.put_line(r_martix_supplier.v_item_no||'--'||
r_martix_supplier.v_unit_type_rcv||'--'||
r_martix_supplier.v_transaction_time);
end loop;
exception
when others then
dbms_output.put_line('user exception');
end;

4. (schema = cusis_common pte7)[%rowtype datatype]

declare
v_smx_supplier cem_smx_su_t%rowtype;
cursor c1 is
select ITEM_NO, ITEM_TYPE, UNIT_CODE_RCV, UNIT_TYPE_RCV from cem_smx_su_t;
begin
open c1;
loop
fetch c1 into v_smx_supplier.ITEM_NO, v_smx_supplier.ITEM_TYPE,
v_smx_supplier.UNIT_CODE_RCV, v_smx_supplier.UNIT_TYPE_RCV;
exit when c1%notfound;
dbms_output.put_line(v_smx_supplier.ITEM_NO||'$'||
v_smx_supplier.ITEM_TYPE||'$'||
v_smx_supplier.UNIT_CODE_RCV||'$'||v_smx_supplier.UNIT_TYPE_RCV);
end loop;
exception
when others then
dbms_output.put_line('Buffer overflow');
end;

5. Sum of first n number using simple loop

declare
v_sum number;
v_range number := &range;
v_counter number;
begin
v_counter := 1;
v_sum := 0;
loop
v_sum := v_sum + v_counter;
v_counter := v_counter + 1;
dbms_output.put_line(v_sum);
exit when v_counter = v_range;
end loop;
end;

6. Exiting the above code with IF condition and EXIT

declare
v_sum number;
v_range number := &range;
v_counter number;
begin
v_counter := 1;
v_sum := 0;
loop
v_sum := v_sum + v_counter;
 v_counter := v_counter + 1;
dbms_output.put_line(v_sum);
if v_counter = v_range then
exit;
end if;
end loop;
end;

7. Print numbers in reverse using for loop

declare
v_sum number;
v_range number := &range;
v_counter number;
begin
v_sum := 0;
for v_counter in reverse 1..v_range loop
dbms_output.put_line(v_counter);
end loop;
end;

8. printing out row count of archive_monitoring_t in cusis common

declare
v_row number;
v_iterator number;
begin
select count(1) into v_row from archive_monitoring_t;
for v_iterator in 1..v_row loop
dbms_output.put_line(v_iterator);
end loop;
end;

9. While loop
declare
v_counter number := 10;
begin
while v_counter > 1 loop
dbms_output.put_line(v_counter);
v_counter := v_counter - 1;
end loop;
end;

10. IF/ELSIF/ELSE [NOTE: While taking user input as string, use quotes('')
(Example : '&priority')
declare
ticket_priority varchar2(10) := '&priority';--the user input is case sensitive
--CRITICAL/HIGH/MEDIUM/LOW
begin
if ticket_priority = 'CRITICAL' OR ticket_priority = 'HIGH' then
dbms_output.put_line('Solve the incident right now');
elsif ticket_priority = 'MEDIUM' then
dbms_output.put_line('Solve the incident within 5 hours');
elsif ticket_priority = 'LOW' then
dbms_output.put_line('Solve the incident within 24 hours');
else
dbms_output.put_line('Cant recognize the priority');
end if;
end;

11. CASE EXPRESSIONS

declare
grade char(1) := '&grade';
begin
case grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Good');
when 'C' then dbms_output.put_line('Avergae');
when 'D' then dbms_output.put_line('Bad');
else dbms_output.put_line('Fail');
end case;
end;

12. CASE EXPRESSION WITH RETURN VALUE

[Note: here we dont put ; after every when case nor do we write END CASE, RETURNS
NULL IF NO ELSE CASE]
declare
grade char(1) := '&grade';
grade_result varchar2(20);
begin
grade_result :=
case grade
when 'A' then 'Excellent'
when 'B' then 'Good'
when 'C' then 'Avergae'
when 'D' then 'Bad'
else 'Fail'
end;
dbms_output.put_line(grade_result);
end;

13. (below code works only if one row is being fetched

declare
v_custom_inv_no customs_invoice_header_t.CUSTOMS_INVOICE_NO%type;
v_invoice_id customs_invoice_header_t.INVOICE_ID%type;

begin
select CUSTOMS_INVOICE_NO, INVOICE_ID into v_custom_inv_no,v_invoice_id from
customs_invoice_header_t
where CUSTOMS_INVOICE_NO = '101229671';
if SQL%FOUND then
dbms_output.put_line(SQL%ROWCOUNT);
end if;
end;

14. CURSOR%ROWCOUNT
declare
cursor c1 is
select CUSTOMS_INVOICE_NO, INVOICE_ID from customs_invoice_header_t;

cursor_var c1%rowtype;
begin
open c1;
loop
fetch c1 into cursor_var;
exit when c1%notfound;
dbms_output.put_line(cursor_var.CUSTOMS_INVOICE_NO);
dbms_output.put_line(cursor_var.INVOICE_ID);
dbms_output.put_line('-----------');
 end loop;
close c1;
end;

15. CURSOR WITH FOR LOOP(CURSOR NAME USED IN FOR LOOP)

declare
cursor c1 is
select CUSTOMS_INVOICE_NO, INVOICE_ID from customs_invoice_header_t;
begin
for cur_var in c1 loop
dbms_output.put_line(cur_var.CUSTOMS_INVOICE_NO);
dbms_output.put_line(cur_var.INVOICE_ID);
dbms_output.put_line('-----------');
end loop;
end;

16. CURSOR WITH FOR LOOP(SQL QUERY USED IN FOR LOOP)

declare
begin
for cur_var in (select CUSTOMS_INVOICE_NO, INVOICE_ID from
customs_invoice_header_t) loop
dbms_output.put_line(cur_var.CUSTOMS_INVOICE_NO);
dbms_output.put_line(cur_var.INVOICE_ID);
dbms_output.put_line('-----------');
end loop;
end;

17. SQLCODE and SQLERRM to print the errors

declare
v_num number;
v_sqlcode integer;
v_sqlerrm varchar2(512);
begin
v_num := 'not a number';
dbms_output.put_line('started');
exception
when others then
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
dbms_output.put_line('sqlcode: '||v_sqlcode);
dbms_output.put_line('sqlerrm: '||v_sqlerrm);
end;

18. Data Not found Exception

declare
v_num number;
v_cus_no customs_invoice_header_t.customs_invoice_no%type;
begin
select customs_invoice_no into v_cus_no from customs_invoice_header_t
where customs_invoice_no = '123';
exception
when no_data_found then
dbms_output.put_line('Data Not Found');
end;

19. User Defined Exception

declare
invalid_qty exception;
v_item_qty number := -2;
 v_sqlcode number;
v_sqlerrm varchar2(512);
begin
if v_item_qty < 0 then
raise invalid_qty;
end if;
dbms_output.put_line('started');
exception
when invalid_qty then
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
dbms_output.put_line('Quantity is invalid');
dbms_output.put_line(v_sqlcode);
dbms_output.put_line(v_sqlerrm);
end;

20. Pragma exception init (Used to handled pre-defined oracle exceptions)

declare
v_num number;
v_cus_no customs_invoice_header_t.customs_invoice_no%type;
v_sqlcode number;
v_sqlerrm varchar2(512);
v_data_absent exception;
pragma exception_init(v_data_absent,100); --100 is the oracle code of
no_data_found exception
begin
select customs_invoice_no into v_cus_no from customs_invoice_header_t
where customs_invoice_no = '123';
exception
when v_data_absent then
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
dbms_output.put_line('Data Not Found');
dbms_output.put_line(v_sqlcode);
dbms_output.put_line(v_sqlerrm);
end;

21. DEBUGGING : dbms_utility.format_call_stack

declare
v_cus_no customs_invoice_header_t.customs_invoice_no%type;
begin
select customs_invoice_no into v_cus_no from customs_invoice_header_t
where customs_invoice_no = '123';
exception
when no_data_found then
dbms_output.put_line(dbms_utility.format_call_stack);
end;

22. Collections
--The data type of index can be either a string type or PLS_INTEGER.
set SERVEROUTPUT on;
declare
type user_arr is table of varchar2(108)
index by PLS_INTEGER;
emp_arr user_arr;
begin

for i in 1..5 loop

 select email into emp_arr(i) from employees where department_id = (i*10)
and rownum = 1;
end loop;

for i in 1..5 loop

dbms_output.put_line(emp_arr(i));
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Note: The above select query runs again and again for each loop decreasing the
performace.
This can be handled by BULK COLLECT

23. BULK COLLECT

--The data type of index can be either a string type or PLS_INTEGER.
set SERVEROUTPUT on;
declare
type user_arr is table of varchar2(60)
index by PLS_INTEGER;
emp_arr user_arr;
begin
SELECT email BULK COLLECT INTO emp_arr FROM employees;
for i in emp_arr.first..emp_arr.last loop
dbms_output.put_line(emp_arr(i));
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

24. BULK BIND

---query without BULK BIND

declare
type c_emp is table of employees.emp_id%type
index by pls_integer;
v_emp c_emp;
begin
select emp_id bulk collect into v_emp from employees;

for i in v_emp.first..v_emp.last loop

update employees set emp_sal = emp_sal+1000 where emp_id = v_emp(i);
end loop;
commit;
end;

---query with BULK BIND

declare
type c_emp is table of employees.emp_id%type
index by pls_integer;
v_emp c_emp;
begin
select emp_id bulk collect into v_emp from employees;

forall i in v_emp.first..v_emp.last
update employees set emp_sal = emp_sal+1000 where emp_id = v_emp(i);
 commit;
end;

25. DYNAMIC SQL | EXECUTE IMMEDIATELY

--creating a dummy table
create table dummy_emp
(
id number primary key,
name varchar2(34),
salary number
);

--creating drop table proc using dynamic sql

create or replace procedure drop_table_proc(v_table_name varchar2) is
begin
EXECUTE IMMEDIATE 'DROP TABLE '||v_table_name;
end;
/

--calling the proc

call drop_table_proc('DUMMY_EMP');

26. Creating an Accounts table and pushing the data into Transactions table
whenever there is an update over Accounts table

--create Accounts table

create table accounts
(
acno number,
name varchar2(20),
balance number
);

--inserting rows into Accounts

insert into accounts values(10,'A',50000);
insert into accounts values(20,'B',75000);
insert into accounts values(30,'C',10000);
insert into accounts values(40,'D',92000);
insert into accounts values(50,'F',43000);
output:
10 A 50000
20 B 75000
30 C 10000
40 D 92000
50 F 43000

--GAVE GRANT FROM SYSDBA TO MY SCHEMA TO MAKE SEQUENCE

GRANT CREATE SEQUENCE TO ASSIGNMENT;

--creating sequence ac_seq

create sequence ac_seq;

--create table Transactions

create table transactions
(
tid number,
acno number,
tdate date,
amount number,
 balance number
);

--Grant Trigger privilages

GRANT CREATE TRIGGER TO ASSIGNMENT;

--CREATING A TRIGGER TO PUSH DATA INTO TRANSACTIONS

create or replace trigger account_trig1
before update on accounts for each row
begin
insert into transactions values
(
ac_seq.nextval,
:new.acno,
sysdate,
abs(:new.balance - :old.balance),
:new.balance
);
end;

--update acno = 30 with balance +50000 in accounts table

update accounts
set balance = balance + 50000
where acno = 30;
accounts table output
10 A 50000
20 B 75000
30 C 60000
40 D 92000
50 F 43000

transaction table output

1 30 19-10-22 50000 60000

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------