MODUL PERKULIAHAN
PEMROGRAMAN
SISTEM BASIS DATA
DAN SQL
Universitas Mercu Buana
2016
Fakultas Program Studi Tatap Muka Kode MK Disusun Oleh
Ilmu Komputer Teknik Informatika 87036 Abdul Khaliq Arrachman,S.Kom.,M.Kom
11
Abstract Kompetensi
Apex adalah salah satu software tool Mahasiswa dapat menjelaskan isi pokok
yang dikembangkan oleh Oracle inc. dari mata kuliah teori dan aplikasi dari
Dalam pembuatan web aplication Apex & SQL serta membuat Aplikasi
yang sudah include di dalam oracle dari tool Apex.
10Gexpress
.
Silabus :
Penggunaan Package
In this chapter, we will discuss the Packages in PL/SQL. 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
Package Specification
The specification is the interface to the package. It just DECLARES the
types, variables, constants, exceptions, cursors, and subprograms that
can be referenced from outside the package. In other words, it contains
all information about the content of the package, but excludes the code
for the subprograms.
All objects placed in the specification are called public objects. Any
subprogram not in the package specification but coded in the package
body is called a private object.
The following code snippet shows a package specification having a single
procedure. You can have many global variables defined and multiple
procedures or functions inside a package.
CREATE PACKAGE emp_sal AS
PROCEDURE find_sal(c_id EMPLOYEES.EMPLOYEE_ID%type);
END emp_sal; /
When the above code is executed at the SQL prompt, it produces the
following result −
Package created.
Package Body
The package body has the codes for various methods declared in the
package specification and other private declarations, which are hidden
from the code outside the package.
The CREATE PACKAGE BODY Statement is used for creating the
package body. The following code snippet shows the package body
declaration for the cust_sal package created above. I assumed that we
already have CUSTOMERS table created in our database as mentioned in
the PL/SQL - Variables chapter.
create or replace PACKAGE BODY emp_sal AS
PROCEDURE find_sal(c_id EMPLOYEES.EMPLOYEE_ID%TYPE) IS
c_sal EMPLOYEES.SALARY%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM EMPLOYEES
WHERE EMPLOYEE_ID = c_id;
dbms_output.put_line('Salary: '|| c_sal);
END find_sal;
END emp_sal;
When the above code is executed at the SQL prompt, it produces the
following result −
Package body created.
Using the Package Elements
The package elements (variables, procedures or functions) are accessed
with the following syntax −
package_name.element_name;
Consider, we already have created the above package in our database
schema, the following program uses the find_sal method of
the cust_salpackage –
2016 Programan Sistem Database dan SQL Pusat Bahan Ajar dan eLearning
10 Abdul Khaliq Arrachman,S.Kom,M.Kom. http://www.mercubuana.ac.id
DECLARE
code EMPLOYEES.EMPLOYEE_ID%type := &cc_id;
BEGIN
EMP_SAL.FIND_SAL(code);
END;
When the above code is executed at the SQL prompt, it prompts to enter
the customer ID and when you enter an ID, it displays the corresponding
salary as follows −
Enter value for cc_id: 1
Salary: 3000
PL/SQL procedure successfully completed.
Example
The following program provides a more complete package. We will use
the CUSTOMERS table stored in our database with the following records
−
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 3000.00 |
| 2 | Khilan | 25 | Delhi | 3000.00 |
| 3 | kaushik | 23 | Kota | 3000.00 |
| 4 | Chaitali | 25 | Mumbai | 7500.00 |
| 5 | Hardik | 27 | Bhopal | 9500.00 |
| 6 | Komal | 22 | MP | 5500.00 |
+----+----------+-----+-----------+----------+
The Package Specification
CREATE OR REPLACE PACKAGE c_package AS
-- Adds a customer
PROCEDURE addCustomer(c_id customers.id%type,
c_name customerS.No.ame%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);
2016 Programan Sistem Database dan SQL Pusat Bahan Ajar dan eLearning
10 Abdul Khaliq Arrachman,S.Kom,M.Kom. http://www.mercubuana.ac.id
-- Removes a customer
PROCEDURE delCustomer(c_id customers.id%TYPE);
--Lists all customers
PROCEDURE listCustomer;
END c_package;
When the above code is executed at the SQL prompt, it creates the
above package and displays the following result −
Package created.
Creating the Package Body
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customerS.No.ame%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type)
IS
BEGIN
INSERT INTO customers (id,name,age,address,salary)
VALUES(c_id, c_name, c_age, c_addr, c_sal);
END addCustomer;
PROCEDURE delCustomer(c_id customers.id%type) IS
BEGIN
DELETE FROM customers
WHERE id = c_id;
END delCustomer;
2016 Programan Sistem Database dan SQL Pusat Bahan Ajar dan eLearning
10 Abdul Khaliq Arrachman,S.Kom,M.Kom. http://www.mercubuana.ac.id
PROCEDURE listCustomer IS
CURSOR c_customers is
SELECT name FROM customers;
TYPE c_list is TABLE OF customerS.No.ame%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list.extend;
name_list(counter) := n.name;
dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
END LOOP;
END listCustomer;
END c_package;
The above example makes use of the nested table. We will discuss the
concept of nested table in the next chapter.
When the above code is executed at the SQL prompt, it produces the
following result −
Package body created.
Using The Package
The following program uses the methods declared and defined in the
package c_package.
DECLARE
code customers.id%type:= 8;
BEGIN
c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
2016 Programan Sistem Database dan SQL Pusat Bahan Ajar dan eLearning
10 Abdul Khaliq Arrachman,S.Kom,M.Kom. http://www.mercubuana.ac.id
c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
c_package.listcustomer;
c_package.delcustomer(code);
c_package.listcustomer;
END;
When the above code is executed at the SQL prompt, it produces the
following result −
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
PL/SQL procedure successfully completed
DAFTAR PUSTAKA
1.abdkhaliq.com
2. www.tutorialspoint.com
3.www.plsqltutorial.com
Ak9
2016 Programan Sistem Database dan SQL Pusat Bahan Ajar dan eLearning
10 Abdul Khaliq Arrachman,S.Kom,M.Kom. http://www.mercubuana.ac.id
