PL/SQL学习(四)存储过程和函数

PL/SQL存储过程
存储过程相当于一个有名字的PL/SQL块,经过第一次编译后再次调用时不需要再次编译

创建格式:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] 
IS    
   Declaration section 
BEGIN    
   Execution section  
EXCEPTION    
  Exception section 
END; 

返回值:

可有可没有

例子:

1> CREATE OR REPLACE PROCEDURE employer_details
2> IS 
3>  CURSOR emp_cur IS 
4>  SELECT first_name, last_name, salary FROM emp_tbl;
5>  emp_rec emp_cur%rowtype;
6> BEGIN 
7>  FOR emp_rec in sales_cur 
8>  LOOP 
9>  dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name
10>    || ' ' ||emp_cur.salary);
11> END LOOP;
12>END;
13> /

执行:

1)  EXECUTE [or EXEC] procedure_name; 

2) 在另一个存储过程里面时:  procedure_name;


 

PL/SQL函数

函数和存储过程最大的区别是:函数必须有返回值。
 
创建语法:
CREATE [OR REPLACE] FUNCTION function_name [parameters] 
RETURN return_datatype;  
IS  
Declaration_section  
BEGIN  
Execution_section 
Return return_variable;  
EXCEPTION  
exception section  
Return return_variable;  
END; 
例子:
1> CREATE OR REPLACE FUNCTION employer_details_func
2>    RETURN VARCHAR(20);
3> IS 
5>    emp_name VARCHAR(20); 
6> BEGIN 
7>	SELECT first_name INTO emp_name
8>	FROM emp_tbl WHERE empID = '100';
9>	RETURN emp_name;
10> END;
11> / 

执行:

1) employee_name := employer_details_func;(给变量赋值)

2) SELECT employer_details_func FROM dual;(作为查询语句一部分)

3) dbms_output.put_line(employer_details_func);(PL/SQL里使用)


 

 PL/SQL存储过程和函数中的参数

1) IN-parameters 
    默认参数类型,相当于一般编程语言里的函数参数,只读,不能改变其值。 
用法: 
CREATE [OR REPLACE] PROCEDURE procedure_name(param_name1 [IN] datatype) 
2) OUT-parameters 
    只写,不可以用其值,但是可为其指定值。 
用法: 
CREATE [OR REPLACE] PROCEDURE procedure_name(param_name OUT datatype) 
3) IN OUT-parameters
    可读可写 
用法: 
CREATE [OR REPLACE] PROCEDURE procedure_name(param_name IN OUT datatype)
例1:

创建一个有IN和OUT参数的存储过程

1> CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT NUMBER)               
2> IS               
3> BEGIN             
4>    SELECT first_name INTO emp_name             
5>    FROM emp_tbl WHERE empID = id;             
6> END;               
7> /             

在PL/SQL块中调用emp_name存储过程.

1> DECLARE               
2>  empName varchar(20);               
3>  CURSOR id_cur SELECT id FROM emp_ids;               
4> BEGIN               
5> FOR emp_rec in id_cur               
6> LOOP               
7>   emp_name(emp_rec.id, empName);               
8>   dbms_output.putline('The employee ' || empName || ' has id ' || emp-rec.id);               
9> END LOOP;              
10> END;               
11> /

例2:

创建一个有IN OUT参数的存储过程
1> CREATE OR REPLACE PROCEDURE emp_salary_increase                
2> (emp_id IN emptbl.empID%type, salary_inout IN OUT emptbl.salary%type)                 
3> IS                
4>    tmp_sal number;                
5> BEGIN                
6>    SELECT salary                
7>    INTO tmp_sal                
8>    FROM emp_tbl               
9>    WHERE empID = emp_id;                
10>   IF tmp_sal between 10000 and 20000 THEN                
11>      salary_inout := tmp_sal * 1.2;                
12>   ELSIF tmp_sal between 20000 and 30000 THEN                
13>      salary_inout := tmp_sal * 1.3;                
14>   ELSIF tmp_sal > 30000 THEN                
15>      salary_inout := tmp_sal * 1.4;                
16>   END IF;               
17> END;               
18> / 
在PL/SQL块中调用emp_salary_increase存储过程.
1> DECLARE               
2>    CURSOR updated_sal is               
3>    SELECT empID,salary               
4>    FROM emp_tbl;               
5>    pre_sal number;               
6> BEGIN               
7>   FOR emp_rec IN updated_sal LOOP                
8>       pre_sal := emp_rec.salary;                
9>       emp_salary_increase(emp_rec.empID, emp_rec.salary);               
10>       dbms_output.put_line('The salary of ' || emp_rec.empID || 		   
11>                ' increased from '|| pre_sal || ' to '||emp_rec.salary);               
12>   END LOOP;                
13> END;                
14> /




 

posted @ 2016-09-06 17:08  JillWen  阅读(4169)  评论(0编辑  收藏  举报