Oracle子程序(存储过程、函数)

时间：2021-01-16 05:15:48
1.子程序是有名字的PL/SQL语句块,包含存储过程和函数,其优点(模块化  可重用性   可维护性  安全性)。 2.存储过程: 
create [or replace] procedure_name
(argument1 [mode1] datatype1,
argument2 [mode2] datatype2..)
is[as]
PL/SQL Block;
 
3.存储过程的调用:a.
SQL>set serveroutput on
SQL>exec procedure_name;(有参数时要带括号)
b.
SQL>set serveroutput on
SQL>call procedure_name();
c.
declare
begin
  procedure_name;(有参数时要带括号)
end;
例子：--过程中带有in参数
create or replace procedure add_employee
(eno number,ename varchar2,sal number,job varchar2 default 'CLERK',
dno number)
       is 
       e_integrity exception;
       pragma exception_init(e_integrity,-2291);       
begin
       insert into scott.emp(empno,ename,sal,job,deptno) 
       values(eno,ename,sal,job,dno);
       exception
              when dup_val_on_index then
                   raise_application_error(-20000,'雇员号不能重复');
              when e_integrity then
                   raise_application_error(-20001,'部门号不存在');    
end;      
declare
begin
      add_employee(1112,'CLERK',2000,'MANAGER',11);
end;
--过程带有out参数
create or replace procedure query_employee(eno number,sname out varchar2,
salary out number)
       is
begin
       select ename,sal into sname,salary from scott.emp
       where empno=eno;
       exception
              when no_data_found then
                   raise_application_error(-20000,'该雇员不存在');
end;       

declare
      dname scott.emp.ename%type;
      salary scott.emp.sal%type;
begin
      query_employee(7788,dname,salary);
      dbms_output.put_line(dname||'  '||salary);
end;

SQL> var name varchar2(10)
SQL> var salary number
SQL> exec query_employee(7788,:name,:salary);
(:name代表会话全局,exec :name:='aa')。
--存储过程中带有in out 参数
create or replace procedure computer(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;       


declare
       num1 number;
       num2 number;
begin
       num1:=4;
       num2:=2;
       computer(num1,num2);
       dbms_output.put_line(num1);
       dbms_output.put_line(num2);
end;
4.输入参数(in)在过程中不能被赋值,输出参数可以被赋值(在外面可以赋值,但是在过程里面不会接收)。
5.参数传递变量和数据:位置传递  名称传递  组合传递(可以支持in,out参数,dname=>'SALES')。--procedure 参数传递
create or replace procedure add_dept(dno number,dname varchar2 default null,
loc varchar2 default null)
       is 
begin
     insert into scott.dept values(dno,dname,loc);
     exception
            when dup_val_on_index then
                 raise_application_error('-20000','部门号不能重复');        
end;   

declare

begin
     --add_dept(50,'SALES','NEW YORK');  
    -- add_dept(dname=>'SALES',dno=>70);  
      add_dept(76,dname=>'SALES',loc=>'NEW YORK');      
end;
备注:当指定参数数据类型时,不能指定该参数类型的长度(number,scott.dept.deptno%type可以)。6.删除过程:drop procedure procedure_name。7.函数:
create [or replace] function function_name(arugment1 [mode1] datatype1,
argument2 [mode2] datatype2..)
return datatype(必须返回值)
is|as
PL/SQL Block;
--function 不带参数
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; 
--调用
declare
       v1 varchar2(100);
begin
       --v1:=get_user;
       --v1:=get_user();
       dbms_output.put_line(get_user());
end;

select get_user from dual;
--function in参数
create or replace function get_sal(name1 in varchar2) 
return number
is
     v_sal scott.emp.sal%type;
begin
     select sal into v_sal from scott.emp where upper(ename)=upper(name1);
     return v_sal;
     exception 
            when no_data_found then
                 raise_application_error(-20000,'该雇员不存在');
end;       

select get_sal('scott') from dual;
--function out参数(将返回多个值)
create or replace function get_info(name1 varchar2,title out varchar2)
return varchar2
is
       deptname scott.dept.dname%type;
begin
       select a.job,b.dname into title,deptname
       from scott.emp a,scott.dept b where a.deptno=b.deptno and 
       upper(a.ename)=upper(name1);
       return deptname;
       exception
              when no_data_found then
                   raise_application_error(-20000,'该雇员不存在');
end;
--function in out参数
create or replace function result1(num1 number,num2 in out number)
       return number
    as
    v_result number(6);
    v_remainder number;
begin
    v_result:=num1/num2;
    v_remainder:=mod(num1,num2);
    num2:=v_remainder;
    return v_result;
    exception
                when zero_divide then
                     raise_application_error(-20000,'不能除0');
end;    

declare
    num2 number;   
begin
    num2:=2;
   dbms_output.put_line(result1(100,num2)||'    '||num2);
end;                    

select * from user_source where name='RESULT1';(查看源代码)

select * from user_source where name='ADD_DEPT';
注:过程和函数都是对象,它们在定义的时候名字不能相同,函数调用是有限制的,只能作为表达式的一部分调用。在SQL语句中调用的函数只能带有输入参数,其他的不行,且只能使用SQL所支持的标准数据类型,而不能使用PL/SQL中的特有数据类型(boolean,table,record等)。select * from user_errors;(查出错误原因和位置或者show errors procedure procedure_name)。   


标签：
相关文章