Oracle开发包
Oracle开发包
建立包规范
- create or replace package emp_package is
- g_deptno number(3):=30;
- procedure add_employee(eno number, ename varchar2,sal number,dno number default g_deptno);
- procedure fire_employee(eno number);
- function get_sal(eno number)return number;
- end emp_package;---end 后面可以跟定义的名称
建立包体
- create or replace package body emp_package is
- function validate_deptno(v_deptno number)return boolean is ----validate_deptno
- v_temp int;
- begin
- select 1 into v_temp from dept where deptno=v_deptno;
- return true;
- exception
- when no_data_found then return false;
- end validate_deptno;
- procedure add_employee(eno number, ename varchar2,sal number,dno number default g_deptno)is---add_employee
- begin
- if validate_deptno(dno)then
- insert into emp(empno,ename,sal,deptno)values(eno,ename,sal,dno);
- else
- RAISE_ApPLICATION_ERROR(-20000,'不存在该部门');
- end if;
- exception
- when dup_val_on_index then RAISE_APpLICATION_ERROR(-20011,'该雇员已存在');
- end add_employee;
- procedure fire_employee(eno number)is --------fire_employee
- begin
- delete from emp where empno=eno;
- if sql%notfound then
- RAISE_APPLICATION_ERROR(-20012,'该雇员不存在');
- end if;
- end fire_employee;
- function get_sal(eno number)return number is ---get_sal
- v_sal emp.sal%type;<pre name="code" class="sql">create or replace package body emp_package is
- procedure add_employee(eno numberk,ename varchar2,salary number,dno number default g_deptno)is
- begin
- if validate_deptno(dno)then
- insert into emp(empno,ename,sal,deptno)values(eno,ename,salary,dno);
- else
- raise_application_error(-20010,'不存在该部门');
- end if;
- exception
- when dup_val_on_index then
- raise_application_error(-20011,'该雇员已存在');
- end;
调用包组件
1.在同一个包内调用
- create or replace package body emp_package is<pre name="code" class="sql">
2.调用公用变量
- declare
- begin
- emp_package.g_deptno:=21;
- end;
3.调用公用过程
- declare
- begin
- emp_package.add_employee(1212,'yang',2000,10);------部门不给值会报错
- emp_package.add_employee(2121,'tender',2000,20);
- end;
4.调用公用函数
- declare
- salary number;
- begin
- salary:=emp_package.get_sal(7788);
- dbms_output.put_line(salary);
- end;
----SCOTT.EMP_PACKAGE.。。。。
----当调用远程数据库包的公用组件是,在组件名前加包名作为前缀在组件名后需要带有数据库链名作为后缀
----EMP_PACKAGE.ADD_EMPLOYEE@TENDER(1111,'SCOTT',1233,10)
----查看源代码
----select text from user_source where name='emp_package'and type='package'
|
评论暂时关闭