---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 := ⦥
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 := ⦥
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 := ⦥
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;