Oracle认识存储过程

第一个简单的存储过程

1.无参存储过程

[sql]  view plain   copy

1. create or replace procedure proce_1  
2. IS  
3. BEGIN  
4.   DBMS_OUTPUT.put_line('欢迎你'||USER);  
5. END ;  

调用

[sql]  view plain   copy

1. BEGIN  
2.    proce_1 ;  
3.   END ;  

                               2.带输入参数的存储过程

2.1使用标量变量作为输入参数

如下 使用标量变量作为输入参数，实现向部门表dept 增加信息

[sql]  view plain   copy

1. CREATE OR REPLACE PROCEDURE add_dept(deptno dept.deptno%TYPE,dname dept.dname%TYPE,loc dept.loc%TYPE)  
2.  IS  
3.  BEGIN  
4.    INSERT INTO DEPT VALUES(deptno,dname,loc);  
5.    EXCEPTION  
6.      WHEN DUP_VAL_ON_INDEX THEN  
7.      dbms_output.put_line('主键冲突,重新指定主键值');  
8.    END ;  

实现插入语句

[sql]  view plain   copy

1. BEGIN   
2.      add_dept(10,'zzz','zz');  
3.      commit ;  
4.      END ;  

检查 通过查看表 可以查看记录是否进去了。

2.2使用记录类型作为输入参数
如下 使用标量变量作为输入参数，实现向部门表dept 增加信息

 

[sql]  view plain   copy

1. CREATE OR REPLACE PROCEDURE add_dept2(dept_record dept%ROWTYPE)  
2.    IS  
3.    BEGIN  
4.      INSERT INTO dept  
5.      VALUES(dept_record.deptno,dept_record.dname,dept_record.loc);  
6.    EXCEPTION  
7.    WHEN DUP_VAL_ON_INDEX THEN  
8.    dbms_output.put_line('主键冲突,重新指定主键值');  
9.      END ;    

调用:

[sql]  view plain   copy

1. DECLARE  
2.      dept_record dept%ROWTYPE;   
3.     BEGIN  
4.       dept_record.deptno:=&deptno;  
5.       dept_record.dname:='&dname';  
6.       dept_record.loc:='&loc';  
7.       add_dept2(dept_record);  
8.       END ;  

2.3 使用集合类型作为输入参数

第一步  

  基于部门dept表创建嵌套表类型

[sql]  view plain   copy

1. --基于部门表dept创建嵌套表类型  
2. --基于deptno列创建嵌套表类型  
3. CREATE TYPE deptno_table_type IS TABLE OF NUMBER(2);  
4. --基于dname列创建嵌套表类型  
5. CREATE TYPE dname_table_type IS TABLE OF VARCHAR2(20);  
6. --基于loc列创建嵌套表类型  
7. CREATE TYPE loc_table_type IS TABLE OF VARCHAR2(20);  

第二步 

基于嵌套表类型创建存储过程

[sql]  view plain   copy

1. CREATE OR REPLACE PROCEDURE add_dept3(deptno_table deptno_table_type,dname_table dname_table_type,loc_table loc_table_type)  
2.  IS  
3.  BEGIN  
4.    FOR i IN 1..deptno_table.COUNT LOOP  
5.      INSERT INTO dept  
6.      VALUES(deptno_table(i),dname_table(i),loc_table(i));  
7.      END LOOP ;  
8.     EXCEPTION  
9.       WHEN DUP_VAL_ON_INDEX THEN  
10.      dbms_output.put_line('主键冲突,重新指定主键值');  
11.      WHEN SUBSCRIPT_BEYOND_COUNT THEN  
12.         dbms_output.put_line('部分集合的元素值不够');  
13.    END ;  

第三步

[sql]