Skip to content

Instantly share code, notes, and snippets.

@johnhonan

johnhonan/create_a_package.sql

Created November 28, 2017 20:53
Show Gist options
  • Save johnhonan/4cdff6918f7530d44436288b0fcbdcdf to your computer and use it in GitHub Desktop.
Save johnhonan/4cdff6918f7530d44436288b0fcbdcdf to your computer and use it in GitHub Desktop.
Download ZIP
Raw
create_a_package.sql
-- This is a package specification
CREATE OR REPLACE PACKAGE hr.job_pack
AUTHID CURRENT_USER
IS -- Declare package components.
-- Author: jhonan@gmail.com
-- Date: 28-Nov-2017
-- Procedures to add/update and delete
-- into the jobs table
PROCEDURE ADD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2);
PROCEDURE UPD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2);
PROCEDURE DEL_JOB (v_job_id VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY hr.job_pack
IS
-- Author: jhonan@gmail.com
-- Date: 28-Nov-2017
-- Procedures to add/update and delete
-- into the jobs table
PROCEDURE ADD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2)
IS
BEGIN
INSERT INTO HR.JOBS(JOB_ID,JOB_TITLE)
VALUES(v_job_id,v_job_title);
END;
PROCEDURE UPD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2)
IS
BEGIN
UPDATE hr.jobs
SET JOB_TITLE=v_job_title
WHERE job_id=v_job_id;
END;
PROCEDURE DEL_JOB (v_job_id VARCHAR2)
IS
BEGIN
DELETE FROM hr.jobs
WHERE job_id=v_job_id;
END;
END;
-- Invoke the package like this
EXECUTE hr.job_pack.add_job('pr_123','test package')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment