1 开发过程
1) 建立过程：不带参数
create or replace procedure out_time
is
begin
dbms_output.put_line(systimestamp);
end;
a 使用execute 命令调用过程
set serveroutput on
exec out_time;
b 使用call 命令调用过程
set serveroutput on
call out_time();
2) 建立过程：带有in参数 默认为输入参数，另外也可以使用in 关键子显示的定义
create or replace procedure add_employee
(eno number,name varchar2,sal number,
job varchar default 'clerk',dno number)
is
e_integrity exception;
pragma exception_init(e_intgegrity,-2291);
begin
insert into emp(empno,ename,sal,job.deptno)
values(eno.name,sal,job,dno);
exception
when dup_val_on_index then
raise_application_error(-20000,'雇员号不能重复');
when e_integrity then
raise_application_error(-20001,'部门号不存在');
调用
exec add_employee(111,'clark',200,'manager',10);
3 建立过程，带有out 参数
create or replcace procedure query_employee
(eno number,name out varchar2,salary out number)
is
begin
select ename,sal,into name,salary from emp where empno=eno;
exception
when no_data_found then
raise_application_error(-20000,'G该雇员不存在');
end;
调用
var name varchar2(10)
var salary number
exec query_employee(7788,:name,:salary);
print name,salary;
4 建立过程，带有in out 参数
create or replace procedure compute
(num1,in out number,num2 in out number)
is
v1 number;
v2 number;
begin
v1:=num1/num2;
v2:=mod(num1,num2);
num1:=v1;
num2:=v2;
end;
调用
var n1 number
var n2 number
exec :n1:=100;
exec :n2:=30
exec compute(:n1,:n2)
print n1 n2
5) 为参数传递变量和数据 位置传递，名称传递，组合传递
create or replace procedure add_dept
(dno number,dname varchar2 default null,loc varchar default null)
is
begin
insert into dept values(dno.dname,loc);
end;
-- 位置传递
exec add_dept(50,'sales','new york');
exec add_dept(60);
exec add_dept(70,'admin');
-- 名称传递
exec add_dept(50,loc=>'new york');
exec add_dept(60,dname=>'sales',loc=>'new york');
6) 显示当前用户的所有程序及其源代码
select text from user_source where name='add_dept';
7) 删除过程
drop procedure add_dept;
2 开发函数
可以在sql语句和其他子过程中执行。
1 建立函数，不带任何参数
create or replace function get_user
return varchar2
is
v_user varchar2(100);
begin
select username into v_user from user_users;
return v_user;
end;
--使用变量接收函数返回值
var v2 varchar2(100)
exec :v1:=get_user
print v1;
-- 在sql 语句中直接调用函数
select get_user from dual;
-- 使用dbms_output 调用函数
set serveroutput on
exec dbms_output.put_line('get_user');
2) 建立函数，带有in 参数
create or replace function get_sal(name in varchar2)
reutnr number
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where upper(ename)=upper(name);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,'employee does not exist');
end;
3) 建立函数，带有out 参数
create or replace function get_info
(name varchar2,title out varchar2)
return varchar2
as
deptname dept.dname%type;
begin
select a,job,b,dname into title,deptname from emp a,dept b
where a.deptno=b.deptno
and upper(a.ename)=uppder(name);
return deptname;
end;
var job varchar2(20)
var dname varchar2(20)
exec :dname:=get_info('scott',job);
print dname job
4) 带有 in out 参数
create or replace function result
(num1,number,num2 in out nu8mber)
return number
as
v_result number(6);
v_remainder number;
begin
v_result:=num1/num2;
v_remainder:=mod(num1,num2);
num2:=v_remainder;
retrun v_result;
exception
when zero_divide then
raise_application_error(-20000,'zero divied');
end;
5) 函数调用的限制
-- 在sql 语句中只能调用存储函数
-- 在sql 语句中只能调用带有输入参数in ，而不能有输出参数out 和in out 参数的函数
-- 在sql 语句中调用的函数的参数必须是标准的sql 数据类型，不能是pl/sql 所特有的数据类型
-- 在sql 语句中调用的函数不能包含insert，update 和delete
6) 查看源代码
set pagesize 40
select text form user_source where name='result';
7) 删除函数
drop function result;
3 管理子程序
1) 列出当前用户的子程序
select object_name,created,status form user_objects
where object_type in ('procedure','function');
2)列出子程序源代码
select text from user_sorce where name='raise_salary'
3)类出子程序编译错误
-- show errors
--使用数据字典user_errors 确定错误原因和位置
select line||'/'||position as "line/col",text error
from user_errors where name='raise_salary';
4) 列出对象的依赖关系
-- 使用 user_dependenciess 确定直接依赖关系
select name,type from user_dependencies
where referenced_name='emp'
-- 使用 deptree 和 ideptree 确定依赖和见解依赖关系
select nested_level,name,type from deptree;
5) 重新编译子程序
当被应用对象的结构被修改以后，就会将相关依赖对象转变为无效invalid ，需要重新编译
1
alter table emp add remark varchar2(100);
2
select object_name,object_type from user_objects
where status='invalid'
3
alter procedure add_employee compile;
alter view dept10 compile;
alter function get_info compile;