Scribd
Upload
7 views

Sql Query

The document outlines the creation and modification of database elements including a table, view, trigger, and package in PL/SQL. It includes procedures for inserting data and handling exceptions related to salary constraints. Additionally, it defines a function to retrieve salary information based on employee ID.

Uploaded by

binukp481
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
7 views

Sql Query

The document outlines the creation and modification of database elements including a table, view, trigger, and package in PL/SQL. It includes procedures for inserting data and handling exceptions related to salary constraints. Additionally, it defines a function to retrieve salary information based on employee ID.

Uploaded by

binukp481
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

XX_BI_TEST_PKG

---============================================
---Assigning sequence to column
--=============================================

Alter table xx_passenger_t MODIFY id number default xx_emp_seq.nextval;

fnd_file.put_line(fnd_file.log,'Vendor id is : '||L_VENDOR_ID);y

--=============================================
--View Creation:
---============================================
create or replace View xx_test_view as
select
column_names
from
table_names
where
conditions;
---============================================
---Creation of Trigger
--=============================================
create or replace trigger xx_test_triger
after insert on xx_test_t1
for each row

declare test_exception exception ;

begin
if :new.salary > 30000 then
update xx_grade_emp
set status ='APL'
where id = :new.id;
elsif :new.salary < 3000 then
update xx_grade_emp
set status ='BPL'
where id = :new.id;
end if;
end;
---============================================
--Creation of Package
--=============================================
----<<SPEC>>------
create or replace PACKAGE xx_test_pkg AS
PROCEDURE xx_test_prc (
p_id IN NUMBER,
p_name IN VARCHAR2,
p_salary NUMBER
);

FUNCTION xx_test_func1 ( -----Declaring function


p_id IN NUMBER
) RETURN NUMBER;

END xx_test_pkg;
---===========================================
-----<<<Body>>>---------
create or replace PACKAGE BODY xx_test_pkg AS
PROCEDURE xx_test_prc (
p_id IN NUMBER,
p_name IN VARCHAR2,
p_salary IN NUMBER
) AS

l_id xx_test_t1.id%TYPE := NULL;


l_name xx_test_t1.name%TYPE := NULL;
l_salary xx_test_t1.salary%TYPE := NULL;

test_exception EXCEPTION; -----user


exception.

---<<Cursor declaration>>

CURSOR xx_test_cur IS
SELECT
id,
name,
salary
FROM
xx_test_t1;

--<<Begin block>>

BEGIN

IF nvl(p_salary, 0) = 0 THEN
RAISE test_exception;

ELSE
BEGIN
-- UPDATE xx_test_t1
-- SET salary = p_salary,
-- name = p_name
-- WHERE id = p_id;

INSERT INTO xx_test_t1 (


id,
name,
salary
) VALUES (
p_id,
p_name,
p_salary
);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in insert statement: '|| sqlerrm);
END;
COMMIT;
END IF;
EXCEPTION
WHEN test_exception THEN
dbms_output.put_line('Salary less than 0 not allowed');
WHEN OTHERS THEN
dbms_output.put_line('Unhandled exception: ' || sqlerrm);
END xx_test_prc;
------<<ends procedure>>-------

FUNCTION xx_test_func1 (
p_id IN NUMBER
)
------<<function>>
RETURN NUMBER AS

l_salary NUMBER; ------


<<<local variable>>

BEGIN
SELECT
salary
INTO l_salary
FROM
xx_test_t1
WHERE
id = p_id;

RETURN l_salary;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error in fetching salary: ' || sqlerrm);

RETURN l_salary;
END xx_test_func1;

END xx_test_pkg;
----================================================================

You might also like