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;
/
Oracle Technical Experience and Test Cases Shared from different resource and through self leanings.
Subscribe to:
Post Comments (Atom)
COALESCE-SQL
Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expr...
-
In HRMS we do use date with timestamp for EIT ans SIT segments. To change the format od the date we can use fnd_date . canonical_to_date ...
-
Converstion API Table Organization hr_organization_api.create_hr_organization hr_all_organization...
-
WIP Migration: Create a job in Work in Process 1. Create a job with item which is billable on and define routing sequence for the ...
No comments:
Post a Comment
Thanks for your comments submitted.,will review and Post soon! by admin.