postgrelsql 存储过程参数默认值 plsql存储过程怎么写

存储过程与函数 

1、存储过程 
存储过程的参数 
形式参数和实际参数，例如，有如下一个存储过程，该过程接收一个作家代码和一个工资值，将该作家的工资改为接收到的工资值。 

Java代码  

1. create or replace procedure updateauths(  
2.   p_authscode auths.author_code%type,  
3.   p_authssalary auths.salary%type)  
4. as  
5. begin  
6.   update auths set salary=p_authssalary where author_code=p_authscode;  
7.   commit;  
8. end updateauths;

下面的PL/SQl块调用updateauths存储过程，将代码为A00011的作家的工资改为350元。 

Java代码  

1. declare  
2. 'A00011';  
3. 350;  
4. begin  
5.   updateauths(v_authorcode,v_salary);  
6. end;

v_authorcode、v_salary作为参数传递到存储过程updateauths中，这些参数是实际参数，简称实参。 

p_authscode、p_authssalary就是形式参数，简称形参。 

参数定义中，IN、OUT和IN OUT代表参数的三种不同模式： 

IN：当调用存储过程时，该模式的形参接收对应实参的值，并且该是只读的，即不能被修改。默认为IN。 

OUT：该形参被认为只能写，既只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的实参。 

IN OUT：都允许。 

Java代码  

1. create or replace procedure updateauthssalary(  
2.   p_author_code in out auths.author_code%type,  
3.   p_salary in number,  
4.   p_name out auths.name%type) is  
5.   v_salary_temp number; --定义存储过程中的局部变量  
6. begin  
7.   select salary into v_salary_temp from auths where author_code=p_author_code;  
8. if v_salary_temp<300
9.     update auths set salary=p_salary where author_code=p_author_code;  
10. if;  
11.   select name into p_name from auths where author code=p_author_code;  
12. end updateauthssalary;

(1)参数的数据类型 

在定义一个存储过程参数时，不能指定CHAR类型和VARCHAR2类型形参的长度，也不能指定NUMBER形参的精度和标度。这些约束由实参来传递。 

例如，下面的存储过程定义不合法，将产生一个编译错误： 

Java代码  

1. create or replace procedure proc_auths(  
2.   --参数定义了类型长度，将产生编译错误。  
3. 6),  
4. 8,2)) as  
5. begin  
6.   select salary into p_salary from auths where author_code=p_code;  
7. end proc_auths;

修改上面存储过程的定义为： 

Java代码  

1. create or replace procedure proc_auths(  
2.   --参数定义了类型长度，将产生编译错误。  
3.   p_code in out varchar2,  
4.   p_salary out number) as  
5. begin  
6.   select salary into p_salary from auths where author_code=p_code;  
7. end proc_auths;

p_code的长度约束和p_salary的精度，标度约束由实参传递。 

Java代码  

1. delcare  
2. 6);  
3. 8,2);  
4. begin  
5. 'A00001';  
6.   proc_auths(v_code,v_salary);  
7. end;

注意，如果使用%type为参数定义类型，那么该参数具有定义在形参上而不是通过实参传递的数据长度。 

Java代码  

1. create or replace procedure query_salary(  
2.   p_code in out auths.author_code%type,  
3.   p_salary out auths.salary%type) as

--那么由于author_code的长度为6，因此p_code的长度也为6。 

(2)参数的传值方式 

位置表示法、名称表示法 

如有这样的存储过程 

Java代码  

1. create or replace procedure insert_auths(  
2.   p_code auths.author_code%type,  
3.   p_name auths.name%type,  
4.   p_sex auths.sex%type,  
5.   p_birthdate auths.birthdate%type) as

下面进行两种方法的调用： 

Java代码  

1. declare  
2. 6);  
3. 12);  
4. 1);  
5.   v_birthdate date;  
6. begin  
7. 'A00021';  
8. '张';  
9. 1;  
10. '5-seq-70';  
11.   --实参的位置顺序与形参的位置顺序相对应。---位置表示法  
12.   insert_auths(v_code,v_name,v_sex,v_birthdate);  
13.   --实参名与形参名对应，这样就可以重新排列参数的先后顺序。---命名表示法  
14. end;

注意，位置表示法和命名表示法在一些调用中也可以混合使用。但是，如果出现第一个用命名表示法的参数时，后面的参数也必须使用命名表示法传值。 

(3)参数的缺省值 

如可以这样： 

p_entry_date_time auths.entry_date_time%type:sysdate,  
  
p_sex auths.sex%type default 1

2、创建函数 

函数与存储过程非常类似，都有三种模式的参数。它们都可以存储在数据库中(当然过程与函数也可以不在于数据库中)，并且在块中调用。 

与存储过程不同，存储过程只能作为一个PL/SQL语句调用，而函数作为表达式的一部分调用。并且它们的定义、可执行、异常处理部分是不同的。 

例如，如作家表中男作家或女作家的工资在200元以上的人数大于百分之七十，则下面的函数返回TRUE，否则返回FALSE： 

Java代码  

1. create or replace function salarystat(  
2.   p_sex auths.sex%type)  
3. return boolean
4.   v_currentsexauthors number;  
5.   v_maxauthors number;  
6. boolean;  
7. 70;  
8. begin  
9.   --获得满足条件的作家的最大数。  
10. 200;  
11.   select count(author_code) into v_currentsexauthors from auths where sex=p_sex;  
12. if(v_maxauthors/v_currentsexauthors*100)>v_percent then  
13. true;  
14. else
15. false;  
16. if;  
17. return
18. end salarystat;

下面进行调用： 

Java代码  

1. declare  
2.   cursor c_auths is  
3.     select distinct sex from auths;  
4. begin  
5. for
6. if
7. 50
8. if;  
9.   end loop;  
10. end;

return也可以用在存储过程中。在这种情况下，它没有参数。当执行了不带参数的return语句后，立刻将控制返回到调用环境，并将OUT和IN OUT模式的形参的当前值传给实参，然后继续执行调用存储过程后的语句。 

在使用函数与存储过程时，一般情况下，如果只有一个返回值，则使用函数；如果有多个返回值则使用存储过程。尽管函数的参数可以是OUT模式，但是一般不这样使用。 

3、删除过程与函数 

drop procedure procedurename; 

drop function functionname; 

4、库存子程序和局部子程序 

前面的子程序都是存储在数据库中的子程序，即库存子程序。这些子程序是由ORACLE命令创建的，并可在其它的PL/SQL块中调用。它们在创建时要进行编译，并将编译后的代码存储在数据库中。当子程序被调用时，编译后的代码从数据库中读出并执行。 

一个子程序也可以在块的定义部分创建，这样的子程序被叫作局部子程序。 

下面定义了一个局部函数formatname： 

Java代码  

1. declare  
2.   cursor c_allauthors is  
3.     select name,sex from auths;  
4. 60);  
5.   function formatname(p_name in varchar2,p_sex in number)  
6. return
7. 16);  
8.   begin  
9. if p_sex=1
10. '男';  
11. else
12. '女';  
13. if;  
14. return p_name||'('||v_sex||')';  
15.   end formatname;  
16. begin  
17. for
18.     v_formattedname:=  
19.       formatname(v_authsrecord.name,v_authsrecord.sex);  
20.     dbms_output.put_line(v_formattedname);  
21.   end loop;  
22. end;

如上例，在无名块的定义部分创建了formatname函数。这个函数只在创建它的块中可用，它的作用域从创建它开始到结束。 

局部子程序只能在定义部分的最后被创建，如果将formatname函数移到上面，将会出现编译错误。子程序必须先定义再引用。