pl/sql package

By ukmodak | June 6th 2022 12:23:03 PM | viewed 185 times

Package

Packages are schema objects that groups logically related PL/SQL types, variables, and subprograms.

A package will have two mandatory parts −

  • Package specification
  • Package body or definition

Example


// package specification   -- done by grant connect to ukaccount

CREATE OR REPLACE PACKAGE ukaccount.uk_course AS 
    PROCEDURE addUkcourse(c_id ukaccount.ukcourse.id%type,c_name ukaccount.ukcourse.name%type);  
	PROCEDURE delUkcourse(c_id ukaccount.ukcourse.id%type);
	PROCEDURE listUkcourse;   
END uk_course; 
/

show errors

// package body creation

CREATE OR REPLACE PACKAGE BODY ukaccount.uk_course AS 
   PROCEDURE addUkcourse(
      c_id  ukaccount.ukcourse.id%type, 
      c_name ukaccount.ukcourse.Name%type
	  ) 
   IS 
   BEGIN 
      INSERT INTO ukaccount.ukcourse (id,name) 
         VALUES(c_id, c_name); 
   END addUkcourse; 
   
   PROCEDURE delUkcourse(c_id ukaccount.ukcourse.id%type) IS 
   BEGIN 
      DELETE FROM ukaccount.ukcourse 
      WHERE id = c_id; 
   END delUkcourse;  
   
   PROCEDURE listUkcourse IS 
	   CURSOR course_cur is 
		  SELECT id, name 
		  FROM ukaccount.ukcourse;
	   course_record course_cur%rowtype;	     
		BEGIN 
		   OPEN course_cur; 
		   LOOP 
			  FETCH course_cur into course_record; 
			  EXIT WHEN course_cur%notfound; 
			  DBMS_OUTPUT.put_line(course_record.id || ' ' || course_record.name); 
		   END LOOP; 
		END listUkcourse; 
   
END uk_course; 
/
// calling the package 

set serveroutput on size 30000; 
BEGIN 
   ukaccount.uk_course.addUkcourse(5, 'Potitish'); 
   ukaccount.uk_course.addUkcourse(6, 'krishna'); 
   commit;
   ukaccount.uk_course.listUkcourse;
END; 
/
bONEandALL
Visitor

Total : 18979

Today :8

Today Visit Country :

  • Germany
  • Singapore
  • United States
  • Russia