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;

/