Salary Sum Query

Sum of all earnings in payroll:

/* Formatted on 2011/10/27 13:36 (Formatter Plus v4.8.8) */

SELECT pee.assignment_id, pee.element_entry_id, pee.element_link_id,
pel.element_type_id, pet.element_name, pettl.reporting_name,
DECODE (pet.element_name,
'Basic_New', 'Basic Salary',
'HRA', 'House Rent Allowance',
pet.element_name
) display_name,
(SELECT MAX (screen_entry_value)
FROM pay_element_entry_values_f
WHERE element_entry_id = pee.element_entry_id) screen_entry_value
FROM pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet,
pay_element_types_f_tl pettl,
pay_element_classifications pec
WHERE pee.assignment_id = :assignment_id
AND TRUNC (SYSDATE) BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pee.element_link_id = pel.element_link_id
AND TRUNC (SYSDATE) BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND pel.element_type_id = pet.element_type_id
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.processing_type = 'R'
AND pet.element_type_id = pettl.element_type_id
AND TRUNC (SYSDATE) BETWEEN peev.effective_start_date
AND peev.effective_end_date*/
AND pet.classification_id = pec.classification_id
AND pec.classification_name = 'Earnings'
ORDER BY DECODE (pet.element_name, 'Basic_New', 1, 'HRA', 2, 3)

Use Decode and Substr

Using Decode and substr together:- 

Decode wil replace the IF-THEN-ELSE

e.g

SELECT full_name,

decode(Current_employee_flag, 'Y', 'EMP',

 'N', 'Not Emp')result

FROM per_all_people_f;

Substr will filter values to the length you specify

Eg

SELECT SUBSTR('Take the first four characters', 1, 4) FIRST_FOUR
FROM dual;

To use both:

SUM(DECODE(Substr(piv.uom, 1, 1), 'M', prrv.result_value, NULL))  amount,

Will result value will grouped for numeric values.

FOR UPDATE cursors

We can use FOR UPDATE cursors in select statement for auditing purpose:

E.g: select * from per_all_people_f for update;

Here in this case..a employee details has been changed and the updation will be done by a scheduled concurrent program. So we can check the colums which are in wait for updation form this query

Check the below link for more info...
http://www.java2s.com/Tutorial/Oracle/0500__Cursor/UsingWHERECURRENTOF.htm

REF CURSOR in PL/SQL

Cursor variable which will be assosiated with runtime queries with a result set value passing to the query.
declare


type emp_cur REF CURSOR;  /* Declare the ref cursor */
c_emp emp_cur ;  /*c_emp is the cursor variable */
v_variabe employees.ename%type;

begin
open c_emp for select ename from employees;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end

check errors in compiled code in toad

SELECT * FROM USER_ERRORS WHERE NAME='PACKAGE NAME';

Taking number form string

The below code will fecth the number in a string:

CREATE OR REPLACE FUNCTION GET_NUMBER_FROM_STRING(STR1 VARCHAR2) RETURN NUMBER

IS
lv_num number(20);
BEGIN
SELECT TO_NUMBER(TRIM(TRANSLATE(LOWER(STR1),'abcdefghijklmnopqrstuvwxyz/-+',' '))) INTO lv_num FROM dual;
RETURN(lv_num);
EXCEPTION
WHEN OTHERS THEN
RETURN(-1);
END;


e.g
select GET_NUMBER_FROM_STRING('AD223445') from dual;

Trigger to keeep track of changes made for PL/SQL Code

Always if we use Create or replace for a pl/sql code,high chances to make chages for exsiting one.So we need to track the changes made.


CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*FROM ALL_SOURCE WHERE 1=2;

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

create a trigger to capture the chages made

CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name

DECLARE
BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY',
'TYPE', 'TYPE BODY')

THEN
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, all_source.* FROM ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE -- DICTIONARY_OBJ_TYPE IN 8i
AND NAME = ORA_DICT_OBJ_NAME; -- DICTIONARY_OBJ_NAME IN 8i
END IF;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;

/

Global Variables

Global Variables remains same for the session.

But using of parameter is recommaned compared to usage global variables.

e.g:

create or replace package glob as
procedure G_var;
end;

Create or replace package body as
g1 varchar2(10):='Global Variable';--Global varable declaration
procedure g_var as
begin
dbms_output.put_line(g1);
if g1='Global Variable'
then
g1:='Executed Global Varable';
dbms_output.put_line(g1);
end;
end;

now when u execute this package as glob.g1 the output will be Global Variable and Executed Global Variable.

In forms u can call a global variable in pre form and when-new form instance trigger:

:GLOBAL. := null;
in when new form tigger
:Global.Application_id:='PROD';

Sample PL/SQL Code For Basics- 3

Package

create or replace package globle_constant is

mile2km number:=1.6093;
km2mile number:=.6214;
yard2m number:=.9144;
m2yard number:=1.0936;
end globle_constant;--exec dbms_output.put_line('20mile='20*globle_constant.mile2km 'km')

/
create or replace package comm_package AUTHID CURRENT_USER is
g_comm number :=0.10; --initialized to 0.10
procedure reset_comm (p_comm in number);
end comm_package;

/
create or replace package body comm_package is
function validate_comm (p_comm in number)return boolean is
v_max number;
begin
select max(commission_pct) into v_max from employees;
if p_comm>v_max then return (FALSE);
else return (TRUE);
end if;
end validate_comm;
procedure reset_comm(p_comm in number) is
begin
if validate_comm(p_comm) then g_comm :=p_comm;--reset the global variable
else
raise_application_error (-20210,'Invalid Commission');
end if;
dbms_output.put_line (g_comm);
end reset_comm;
end comm_package;
/

create or replace procedure add_job(p_id varchar2,p_title varchar2) is
v_id varchar2(10);
v_title varchar2(100);
begin
insert into jobs(job_id, job_title)
values(p_id,p_title);
select job_id,job_title into v_id,v_title from jobs where job_id=p_id;
dbms_output.put_line ('Inserted Data is 'v_idv_title);
end add_job;
/
create or replace procedure del_job(p_id varchar2) is
begin
delete from jobs where job_id=p_id;
if sql%found then
dbms_output.put_line ('Delete Completed');
end if;
exception
when no_data_found then
dbms_output.put_line ('No Such Department Avail');
end del_job;
/




Record
declare

type emp_record_type is record(employee_id employees.employee_id%type,
last_name employees.last_name%type,
job_id employees.job_id%type,
salary employees.salary%type);
emp_record emp_record_type;
begin
select employee_id, last_name, job_id, salary
into emp_record from employees where employee_id='&ID';
dbms_output.put_line (emp_record.last_name','emp_record.job_id);
end;
 /
create or replace procedure upd_job(p_id varchar2,p_title varchar2) is

v_id varchar2(10);
v_title varchar2(100);
begin
update jobs set job_id=p_id,job_title=p_title where job_id=p_id;
select job_id,job_title into v_id,v_title from jobs where job_id=p_id;
dbms_output.put_line ('Updated Data is 'v_idv_title);
end upd_job;
/


Sub Procedure

create or replace procedure prostore(p_id employees.employee_id%type) is
procedure prosuins is
begin
insert into messages values (user,sysdate);
end prosuins;
begin
update employees set salary=salary*1.10 where employee_id=p_id;
prosuins;
end prostore;

/

Sample PL/SQL Code For Basics- 2

Instead of  Trigger:

----create table 1


create table new_emps as select employee_id,last_name,salary,department_id,email,job_id,hire_date from employees;

-----create table 2

create table new_depts as select d.department_id,d.department_name,d.location_id,sum(e.salary) tot_dept_sal from departments d,employees e where d.department_id=e.employee_id group by d.department_id,d.department_name,d.location_id

-----create view

create view emp_detail as select e.employee_id,e.last_name,e.salary,e.department_id,e.email,e.j
d.department_name,d.location_id from employees e,departments d where e.department_id=d.department_id

-----create trigger

create or replace trigger new_emp_dept
instead of insert or update or delete on emp_detail for each row

begin

if inserting then insert into new_emps values(:new.employee_id,:new.last_name,:new.salary,:new.department_id,:new.email,:new.job_id,sy update new_depts set tot_dept_sal=tot_dept_sal+:new.salary where department_id=:new.department_id;
elsif deleting then delete from new_emps where employee_id=:old.employee_id;
update new_depts set tot_dept_sal=tot_dept_sal-:old.salary
where department_id=:old.department_id;
elsif updating ('salary') then
update new_emps set salary=:new.salary where department_id=:old.department_id;
update new_depts set tot_dept_sal=tot_dept_sal+(:new.salary-:old.salary) where department_id=:old.department_id;

elsif updating ('department_id') then
update new_emps set department_id=:new.department_id where employee_id=:old.employee_id;
end if;
end new_emp_dept;

/
Trigger Statement Level

create or replace trigger trigsar

before insert on employees
begin
if (to_char(sysdate,'DY') in ('SAT','SUN')) or(to_char(sysdate,'HH24:MI') not between '09:00' and '18:00') then raise_application_error (-20500,'You Can''t Insert in EMP during work Hrs...');
end if;
end trigsar;

Loop

declare

v_value number;
v_counter number:=1;
begin
loop
if v_counter=6 or v_counter=8 then null;
else
insert into messages(result)
values(v_counter);
v_counter:=v_counter+1;
end if;
exit when v_counter>10;
end loop;
end;

/
Variable Scope

<>

declare
v_sal number(7,2) :=60000;
v_comm number(7,2) :=v_sal*.20;
v_message varchar(250) := ' eligible for commission';

begin
<>
declare
v_sal number(7,2) :=50000;
v_comm number(7,2) :=0;
v_total number(7,2) :=v_sal+v_comm;
begin
v_message := 'Cleark Not'
v_message;
v_comm :=outer.v_sal*.30;
dbms_output.put_line (V_message);
dbms_output.put_line (v_comm);
end;
end;

/
UTL File:
 
create or replace procedure sal_status (p_filedir in varchar2,p_filename in varchar2)


is
v_filehandle utl_file.file_type;
cursor emp_info is
select last_name,salary,department_id from employees order by department_id;
v_newdept employees.department%type;
v_olddept employees.department%type :=0;

begin

v_filehandle :=utl_file.fopen(p_filedir,p_filename,'w');
utl_file.putf(v_filehandle,'SALARY REPORT: GENERATED ON %S\N',SYSDATE);
utl_file.put_line(v_filehandle);
for v_emp_rec in emp_info loop
v_newdept :=v_emp_rec.department_id;
if v_newdept<>v_olddept then
utl_file.putf(v_filehandle,'Department: %s\n',v_emp_rec.department_id);
end if;
utl_file.putf(v_filehandle,'Employee:%s earns:%s\n',v_emp_rec.last_name,v_emp_rec.salary);
v_olddept:=v_newdept;
end loop;
utl_file.put_line(v_filehandle,'****** End of Report******');
utl_file.fclose(v_filehandle);
exception
when utl_file.invalid_filehandle then
raise_application_error (-20001,'Invalid File');
when utl_file.write_error then
raise_application_error (-20002,'Unable to write');
end sal_status;
/

Sample PL/SQL Code For Basics- 1

Sample PL/SQL Code For Basics

declare

v_depno departments.department_id%type;
v_location departments.location_id%type;
begin
select department_id, location_id into v_depno, v_location from departments where department_id=&depno;
dbms_output.put_line ('The Deptno is' ( v_depno));
dbms_output.put_line (v_location);
end;
/

Cursor

declare

v_id employees.employee_id%type;
v_name employees.last_name%type;
cursor emp_cursor is select employee_id,last_name from employees;
begin
open emp_cursor;
for i in 1..10 loop
fetch emp_cursor into v_id,v_name;
dbms_output.put_line (to_char(v_id)' 'v_name);
end loop;
close emp_cursor;
end;
/

Cursor Parameter

DECLARE
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS SELECT employee_id, last_name FROM employees WHERE department_id = p_deptno AND job_id = p_job;
i emp_cursor%rowtype;
begin
open emp_cursor('&Depno','&Jobno');
fetch emp_cursor into i;
dbms_output.put_line (i.employee_id' 'i.last_name);
close emp_cursor;
end;
/

Exceptions:

/* Prdefined Exception*/


DECLARE
v_num number;
begin
select result into v_num from messages;
exception
when no_data_found then insert into messages values(100);
end;
/

*********

/* Non-Prdefined Exception*/

DECLARE

err_num NUMBER;
err_msg VARCHAR2(100);
emp_remaining exception;
pragma exception_init (emp_remaining, -2292);

BEGIN
delete from employees where department_id=&deptno;
EXCEPTION
when emp_remaining then
dbms_output.put_line ('Cannot Remove dept' to_char(&deptno) 'Employee Exist');
WHEN others THEN err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO error VALUES (err_num, err_msg);
END;
/

***********

/* User defined Exception*/

DECLARE
e_exp exception;
begin
update employees set employee_id=&id where employee_id=&ent;
IF sql%notfound then raise e_exp;
end if;
exception
when e_exp then dbms_output.put_line ('Employee not exist');
end;
/

Index By Table

declare

type emp_table_type is table of employees%rowtype
index by binary_integer;
emp_table emp_table_type;
v_count number(3):=104;
begin
for i in 100..v_count
loop
select * into emp_table(i) from employees where employee_id=i;
end loop;
for i in emp_table.first..emp_table.last
loop
dbms_output.put_line (emp_table(i).last_name);
end loop;
end;

/