Oracle Development Kit

Establish package specifications

1. Create Or ReplacePackage emp_packageIs
2. G_deptno number (3): = 30;
3. ProcedureAdd_employee (eno number, ename varchar2, sal number, dno numberDefaultG_deptno );
4. ProcedureFire_employee (eno number );
5. FunctionGet_sal (eno number)ReturnNumber;
6. EndEmp_package;--- The end can be followed by the defined name

Create a package

1. Create Or ReplacePackage body emp_packageIs
2. FunctionValidate_deptno (v_deptno number)ReturnBooleanIs---- Validate_deptno
3. V_tempInt;
4. Begin
5. Select1IntoV_tempFromDeptWhereDeptno = v_deptno;
6. Return True;
7. Exception
8. WhenNo_data_foundThen Return False;
9. EndValidate_deptno;
10. ProcedureAdd_employee (eno number, ename varchar2, sal number, dno numberDefaultG_deptno)Is--- Add_employee
11. Begin
12. If validate_deptno (dno)Then
13. Insert IntoEmp (empno, ename, sal, deptno)Values(Eno, ename, sal, dno );
14. Else
15. RAISE_ApPLICATION_ERROR (-20000,'The team does not exist');
16. EndIf;
17. Exception
18. WhenDup_val_on_indexThenRAISE_APpLICATION_ERROR (-20011,'This employee already exists');
19. EndAdd_employee;
20. ProcedureFire_employee (eno number)Is-------- Fire_employee
21. Begin
22. Delete FromEmpWhereEmpno = eno;
23. If SQL % notfoundThen
24. RAISE_APPLICATION_ERROR (-20012,'The employee does not exist');
25. EndIf;
26. EndFire_employee;
27. FunctionGet_sal (eno number)ReturnNumberIs --- Get_sal
28. V_sal emp. sal % type; <preName="Code"Class ="SQL">Create Or ReplacePackage body emp_packageIs
29. ProcedureAdd_employee (eno numberk, ename varchar2, salary number, dno numberDefaultG_deptno)Is
30. Begin
31. If validate_deptno (dno)Then
32. Insert IntoEmp (empno, ename, sal, deptno)Values(Eno, ename, salary, dno );
33. Else
34. Raise_application_error (-20010,'The team does not exist');
35. EndIf;
36. Exception
37. WhenDup_val_on_indexThen
38. Raise_application_error (-20011,'This employee already exists');
39. End;

Begin select sal into v_sal from emp where empno = eno; return v_sal; exception when no_data_found then RAISE_ApPLICATION_ERROR (-20012, 'this employee does not exist'); end get_sal; end emp_package;

Call package components 1. Call in the same package

1. Create Or ReplacePackage body emp_packageIs<PreName="Code"Class ="SQL">

Procedure add_employee (eno numberk, ename varchar2, salary number, dno number default g_deptno) isbegin if exist (dno) then insert into emp (empno, ename, sal, deptno) values (eno, ename, salary, dno); else raise_application_error (-20010, 'this department does not exist'); end if; exception when dup_val_on_index then raise_application_error (-20011, 'this employee already exists '); end;

2. Call Public Variables

1. Declare
2. Begin
3. Emp_package.g_deptno: = 21;
4. End;

3. Call the public process

1. Declare
2. Begin
3. Emp_package.add_employee (1212,'Yang');------ An error will be reported if the Department does not give a value
4. Emp_package.add_employee (2121,'Authorization');
5. End;

4. Call Public Functions

1. Declare
2. Salary number;
3. Begin
4. Salary: = emp_package.get_sal (7788 );
5. Dbms_output.put_line (salary );
6. End;

---- When using other user identities to call a public component, the user name and package name must be prefixed before the component name
---- SCOTT. EMP_PACKAGE .....


---- When the public component that calls the remote database package is, add the package name as the prefix before the component name. After the component name, the database chain name must be followed as the suffix.
---- EMP_PACKAGE.ADD_EMPLOYEE @ Clerk (1111, 'Scott ', 1233,10)
---- View source code
---- Select text from user_source where name = 'emp_package' and type = 'package'

1 2 Next Page