【初识Oracle】⑩存储过程 PROCEDURE

 --匿名块 --存储过程 --1.无参存储过程 CREATE OR REPLACE PROCEDURE proc_1 IS BEGIN   dbms_output.put_line('欢迎你：'||USER);   dbms_output.put_line('现在是：'||to_char(SYSDATE,'yyyy-MM-dd')); END proc_1;  --在代码块中对存储过程进行调用 BEGIN    proc_1; END;  --2.有参存储过程   --带有输入参数的存储过程   --1.使用标量变量作为输入参数   CREATE OR REPLACE PROCEDURE add_dept1(deptno dept.deptno%TYPE,         dname dept.dname%TYPE,loc dept.loc%TYPE ) IS      BEGIN     INSERT INTO dept VALUES(deptno,dname,loc);       COMMIT;     EXCEPTION        WHEN dup_val_on_index THEN           dbms_output.put_line('主键冲突，请重新设置');    END add_dept1;      --测试   BEGIN     --add_dept1(70,'山科大','青岛开发区');     --add_dept1(deptno=>80,dname=>'青岛大学',loc=>'崂山区');     add_dept1(&deptno,'&dname','&loc');   END;   SELECT * FROM dept;         --2.使用记录类型作为输入参数   CREATE OR REPLACE PROCEDURE add_dept2(dept_record dept%ROWTYPE) IS      BEGIN     INSERT INTO dept VALUES dept_record;     EXCEPTION        WHEN dup_val_on_index THEN           dbms_output.put_line('主键冲突，请重新设置');   END add_dept2;      --测试   DECLARE        dept_record dept%ROWTYPE;   BEGIN     dept_record.deptno:=&deptno;     dept_record.dname:='&dname';     dept_record.loc:='&loc';          add_dept2(dept_record);   END;           --3.使用集合类型作为输入参数      CREATE TYPE deptno_table_type IS TABLE OF NUMBER(2);   CREATE TYPE dname_table_type IS TABLE OF VARCHAR2(14);   CREATE TYPE loc_table_type IS TABLE OF VARCHAR2(13);      CREATE TABLE department AS          SELECT * FROM dept;      --三个数据集合作为参数   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 department VALUES(deptno_table(i),dname_table(i),loc_table(i));     END LOOP;      COMMIT;      EXCEPTION        WHEN dup_val_on_index THEN           dbms_output.put_line('主键冲突，请重新设置');       WHEN SUBSCRIPT_BEYOND_COUNT THEN           dbms_output.put_line('部分集合的元素数值不够');   END add_dept3;      --调用测试   DECLARE       deptno_table deptno_table_type;       dname_table dname_table_type;       loc_table loc_table_type;   BEGIN     --BULK COLLECT INTO可以批量查询，提高查询效率，into后面跟的必须是集合     SELECT * BULK COLLECT INTO deptno_table,dname_table,loc_table FROM dept;     FOR i IN 1..deptno_table.count LOOP       deptno_table(i):=deptno_table(i)+2;     END LOOP;     add_dept3(deptno_table,dname_table,loc_table);        END;      SELECT * FROM department ORDER BY deptno ASC;         --带有输出参数的存储过程   --1.标量变量   CREATE OR REPLACE PROCEDURE get_dept1(p_deptno dept.deptno%TYPE,          dname OUT dept.dname%TYPE,loc OUT dept.loc%TYPE) IS      BEGIN          SELECT dname,loc INTO dname,loc  FROM dept WHERE deptno=p_deptno;          EXCEPTION             WHEN NO_DATA_FOUND THEN              dbms_output.put_line('该部门不存在');   END get_dept1;      --调用测试   DECLARE       p_deptno dept.deptno%TYPE;       dname  dept.dname%TYPE;       loc  dept.loc%TYPE;   BEGIN       p_deptno:=&deptno;       get_dept1(p_deptno,dname,loc);       dbms_output.put_line('部门号：'||p_deptno);       dbms_output.put_line('部门名称：'||dname);       dbms_output.put_line('部门地址：'||loc);   END;           --2.记录类型   CREATE OR REPLACE PROCEDURE get_dept2(p_deptno dept.deptno%TYPE, dept_record OUT dept%ROWTYPE) IS      BEGIN      SELECT * INTO dept_record  FROM dept WHERE deptno=p_deptno;     EXCEPTION             WHEN NO_DATA_FOUND THEN              dbms_output.put_line('该部门不存在');   END get_dept2;   --调用测试   DECLARE       p_deptno dept.deptno%TYPE;       dept_record dept%ROWTYPE;   BEGIN       p_deptno:=&deptno;       get_dept2(p_deptno,dept_record);       dbms_output.put_line('部门号：'||dept_record.deptno);       dbms_output.put_line('部门名称：'||dept_record.dname);       dbms_output.put_line('部门地址：'||dept_record.loc);          END;      --3.集合类型   CREATE OR REPLACE PROCEDURE get_dept3(p_loc dept.loc%TYPE,deptno_table OUT deptno_table_type,          dname_table OUT dname_table_type) IS   BEGIN          SELECT deptno,dname BULK COLLECT INTO deptno_table,dname_table FROM dept WHERE lower(loc)=p_loc;          EXCEPTION             WHEN NO_DATA_FOUND THEN              dbms_output.put_line('该部门不存在');   END get_dept3;      --调用测试   DECLARE       p_loc dept.loc%TYPE;       deptno_table  deptno_table_type;       dname_table  dname_table_type;   BEGIN      p_loc:=LOWER('&loc');      get_dept3(p_loc,deptno_table,dname_table);            --使用循环对数据进行处理      FOR i IN 1..deptno_table.count LOOP        dbms_output.put_line('部门号：'||deptno_table(i));        dbms_output.put_line('部门名称：'||dname_table(i));        dbms_output.put_line('部门地址：'||p_loc);        dbms_output.put_line('=====================');      END LOOP;         END;      --带有输入输出参数的存储过程   --计算2个数的和与差   CREATE OR REPLACE PROCEDURE add_sub(n1 IN OUT NUMBER,n2 IN OUT NUMBER) IS   BEGIN     n1:=n1+n2;     n2:=n1-2*n2;   END add_sub;   ----所谓输出参数，即是对 ‘IN OUT’修饰的参数，进行操作之后，赋值给之前传进来的那个数。   --调用测试   DECLARE       num1 NUMBER:=&num1;       num2 NUMBER:=&num2;   BEGIN       add_sub(num1,num2);       dbms_output.put_line('和：'||num1);       dbms_output.put_line('差：'||num2);   END; 

存储过程非常类似JAVA以及C语言里面的方法，只不过没有返回值，下一章的函数则是具有返回值的‘方法’