Oracle package instances

The following uses SQLDeveloper to create a package using the Oracle Scott account's embedded emp table. 1. Define the record type in the package and call it in the anonymous block-createorreplacepackagemy_baoastypeemp_typeisrecord (ghemp. empno % type, xmemp. ename % type, gzemp. sal % type );

The following uses SQL Developer to create a package using Oracle SCOTT's built-in emp table. 1. Define the record type in the package and call it in the anonymous block-create or replace package my_bao as type emp_type is record (gh emp. empno % type, xm emp. ename % type, gz emp. sal % type );

The following uses SQL Developer to create a package using Oracle SCOTT's built-in emp table.

1. Define the record type in the package and call it in the anonymous block.
-- Customize the type in the package
Create or replace package my_bao
As
Type emp_type is record (
Gh emp. empno % type,
Xm emp. ename % type,
Gz emp. sal % type
);
End;

Refresh the created content in the left-side view package;

2 call,

Declare
V1 my_bao.emp_type;
Begin
Select empno, ename, sal into v1 from emp where empno = 7900;
Dbms_output.put_line ('employee id = '| v1.gh );
Dbms_output.put_line ('name = '| v1.xm );
Dbms_output.put_line ('salary = '| v1.gz );
End;

Result;

If no content exists in the DBMS output, click the small icon in the rightmost book-like page to open the DBMS output;

3. Define the process in the package: hello and function f_add (p1, p2)
-- Define package specifications
Create or replace package my_bao2
As
Type emp_type is record (
Gh emp. empno % type,
Xm emp. ename % type,
Gz emp. sal % type
);
Procedure hello (p_name varchar2 );
Function f_add (p1 number, p2 number) return number;
End;

4. Implement the package specification (package body)
Create or replace package body my_bao2
As
Type emp_type is record (
Gh emp. empno % type,
Xm emp. ename % type,
Gz emp. sal % type
);
Procedure hello (p_name varchar2)
As
Begin
Dbms_output.put_line ('hello, '| p_name );
End;


Function f_add (p1 number, p2 number) return number
As
V1 number;
Begin
V1: = p1 + p2;
Return v1;
End;
End;

5 call

Select my_bao2.f_add (1, 2) from dual;

6. Call

Call my_bao2.hello ('oracle ');

7-pack overload method
The reload key is that the names are the same, and the type or number of parameters are different.
Definition code:
Create or replace package my_bao3
As
Function f_add (p1 number, p2 number) return number;
Function f_add (p1 varchar, p2 varchar) return number;
End;

Package body:

Create or replace
Package body my_bao3
As

Function f_add (p1 number, p2 number) return number
As
V1 number;
Begin
V1: = p1 + p2;
Return v1;
End;

Ction f_add (p1 varchar, p2 varchar) return number
As
V1 number;
Begin
V1: = p1 + p2;
Return v1;
End;
End;

8 call

Select my_bao3.f_add (1, 2) from dual;

9 call

Select my_bao3.f_add ('6', '2') from dual;

Oracle automatically selects which function to call based on the parameter type;