----------------------------------pl/sql过程-------------------------- --使用过程参数 过程参数包括输入参数和输出参数三中类型、IN/OUT/IN OUT create [or replace] procedure procudure_name (argument1)[model1] datatype1,argument2[model2] datatype2,.....) IS(AS) PL/SQL Block; 1:建立无参的过程 create or replace procedure out_time is begin dbms_output.put_line(sysdate); end; 2:建立带有输入参数的过程 create or replace procedure add_emp (empno emp.empno%type,ename emp.ename%type, job emp.job%type,sal emp.sal%type, common emp.common%type,deptno emp.deptno%type) is begin insert into emp values(empno,ename,job,sal,common,deptno); end; 3:建立带有输出参数的过程 create or replace procedure update_sal (eno number,salary number,name out varchar2) is begin update emp set sal = salary where empno=eno returning ename into name; end; select * from emp; 4:建立带有输入输出参数的过程 create or replace procedure divide (num1 in out number,num2 in out number) is v1 number; v2 number; begin v1 :=TRUNC(num1/num2); v2 :=MOD(num1,num2); num1 :=v1; num2 :=v2; end; --调用过程 调用无参 1:declare begin out_time; end; 调用带有输入参数 2: declare begin add_emp(33333,'wangwei','manager',20000,null,30); end; select * from emp; 调用带有输出参数 3:declare v_name emp.ename%type; begin update_sal(&eno,&salary,v_name); dbms_output.put_line('姓名:'||v_name); end; 调用带有输入输出参数 4:declare n1 number :=&n1; n2 number :=&n2; begin divide(n1,n2); dbms_output.put_line('商:'||n1||',余数:'||n2); end; 5:使用位置传递为参数传递变量和数据 call add_emp(55555,'ceshi','manager',20000,null,40) 6:使用名称传递为参数传递变量和数据 exec add_emp(empno=>66666,ename=>'ceshi2',job => 'manager',sal => 20000,common => null,deptno => 50); 7:使用组合传递为参数传递变量和数据 exec add_emp(77777,'ceshi3','manager',sal => 20000,common => null,deptno=>60) --过程开发示例 1:为过程参数指定默认值 create or replace procedure add_emp (empno emp.empno%type,ename emp.ename%type, job emp.job%type default 'manager',sal emp.sal%type default 30000, common emp.common%type default 0,deptno emp.deptno%type) is begin insert into emp values(empno,ename,job,sal,common,deptno); end; 调用过程 declare begin add_emp(44444,'fujiao',deptno=>4); end; select * from EMP; 2:使用异常处理 create or replace procedure update_sal (name emp.ename%type,salary emp.sal%type) is e_no_rows EXCEPTION; BEGIN update emp set sal=salary where lower(ename) = lower(name); if sql%notfound then raise e_no_rows; end if; exception when e_no_rows then dbms_output.put_line('该雇员不存在'); end; 调用 declare begin update_sal('wangwei',30000); end; select * from emp; --自定义消息错误 create or replace procedure add_emp (empno emp.empno%type,ename emp.ename%type, job emp.job%type default 'manager',sal emp.sal%type default 30000, common emp.common%type default 0,deptno emp.deptno%type) is e_2291 exception; pragma exception_init(e_2291,-2291); begin case when job not in('manager','analyst','salesman') then RAISE_APPLICATION_ERROR(-20000,'雇员只能是manager,analyst或salesman'); when sal not between 1000 and 30000 then raise_application_error(-20001,'工资必须在1000到30000之间'); else insert into emp values(empno,ename,job,sal,common,deptno); end case; exception when dup_val_on_index then raise_application_error(-20002,'雇员号不能重复'); when e_2291 then raise_application_error(-20003,'部门好不存在'); end; declare begin add_emp(7788,'mary',deptno => 30); end; select * from emp; --使用记录变量作为输入参数 create or replace procedure add_emp (emp_record emp%rowtype) is begin insert into emp values emp_record; exception when DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR(-20012,'部门号不能重复'); end; 调用 declare emp_record emp%rowtype; begin emp_record.empno :=&no; emp_record.ename :=&ename; emp_record.job :=&job; emp_record.sal :=&sal; emp_record.common :=&common; emp_record.deptno :=&deptno; add_emp(emp_record); end; select * from emp; --调用记录变量作为输出参数 create or replace procedure get_employee (eno emp.empno%type,emp_record out emp%rowtype) is begin select * into emp_record from emp where empno=eno; exception when NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000,'雇员不存在'); end; 调用 declare emp_record emp%rowtype; begin get_employee(&eno,emp_record); dbms_output.put_line('姓名:'||emp_record.ename||',工资'||emp_record.sal); end; --使用集合变量作为输入参数 --使用集合变量作为输出参数 --维护过程 删除过程 drop procedure get_employee; 显示编译错误 show error; 确定过程状态 select object_name from user_objects where status='invalid' and object_type='procedure'; 编译过程 alter procedure add_emp compile; 查看过程代码 select text from user_source where name='add_emp';