Oracle认识存储过程

最新推荐文章于 2021-04-14 16:29:22 发布

神奇的种子

最新推荐文章于 2021-04-14 16:29:22 发布

阅读量356

点赞数 2

分类专栏： SQL

本文链接：https://blog.csdn.net/qq_29272491/article/details/79200756

版权

本文详细介绍了Oracle中的存储过程，包括无参、带输入参数、带输出参数的存储过程，以及如何使用记录类型、嵌套表类型和光标。还讨论了存储过程的维护、IS与AS的区别，并提供了多个示例代码。

摘要由CSDN通过智能技术生成

第一个简单的存储过程

1.无参存储过程

[sql]view plaincopy 
   
 create or replace procedure proce_1  
 IS  
 BEGIN  
   DBMS_OUTPUT.put_line('欢迎你'||USER);  
 END ;  

调用

[sql]view plaincopy 
   
 BEGIN  
    proce_1 ;  
   END ;  

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

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

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

[sql]view plaincopy 
   
 CREATE OR REPLACE PROCEDURE add_dept(deptno dept.deptno%TYPE,dname dept.dname%TYPE,loc dept.loc%TYPE)  
  IS  
  BEGIN  
    INSERT INTO DEPT VALUES(deptno,dname,loc);  
    EXCEPTION  
      WHEN DUP_VAL_ON_INDEX THEN  
      dbms_output.put_line('主键冲突,重新指定主键值');  
    END ;  

实现插入语句

[sql]view plaincopy 
   
 BEGIN   
      add_dept(10,'zzz','zz');  
      commit ;  
      END ;  

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

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

[sql]view plaincopy 
   
 CREATE OR REPLACE PROCEDURE add_dept2(dept_record dept%ROWTYPE)  
    IS  
    BEGIN  
      INSERT INTO dept  
      VALUES(dept_record.deptno,dept_record.dname,dept_record.loc);  
    EXCEPTION  
    WHEN DUP_VAL_ON_INDEX THEN  
    dbms_output.put_line('主键冲突,重新指定主键值');  
      END ;    

调用:

[sql]view plaincopy 
   
 DECLARE  
      dept_record dept%ROWTYPE;   
     BEGIN  
       dept_record.deptno:=&deptno;  
       dept_record.dname:='&dname';  
       dept_record.loc:='&loc';  
       add_dept2(dept_record);  
       END ;  

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

第一步

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

[sql]view plaincopy 
   
 --基于部门表dept创建嵌套表类型  
 --基于deptno列创建嵌套表类型  
 CREATE TYPE deptno_table_type IS TABLE OF NUMBER(2);  
 --基于dname列创建嵌套表类型  
 CREATE TYPE dname_table_type IS TABLE OF VARCHAR2(20);  
 --基于loc列创建嵌套表类型  
 CREATE TYPE loc_table_type IS TABLE OF VARCHAR2(20);  

第二步

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

[sql]view plaincopy 
   
 CREATE OR REPLACE PROCEDURE add_dept3(deptno_table deptno_table_type,dname_table dname_table_type,loc_table loc_table_type)  
  IS  
  BEGIN  
    FOR i IN 1..deptno_table.COUNT LOOP  
      INSERT INTO dept  
      VALUES(deptno_table(i),dname_table(i),loc_table(i));  
      END LOOP ;  
     EXCEPTION  
       WHEN DUP_VAL_ON_INDEX THEN  
      dbms_output.put_line('主键冲突,重新指定主键值');  
      WHEN SUBSCRIPT_BEYOND_COUNT THEN  
         dbms_output.put_line('部分集合的元素值不够');  
    END ;  