Oracle学习笔记-存储过程

1、基本概念

 

存储过程
为了完成特定功能的SQL语句集，经过编译后存储在数据库。用户通过存储过程的名字并且给出对应参数来执行它。
多数情况下是通过 定时任务 来编译执行。

2、实现方式

2.1、基本方式：往dept_hs插入数据，deptno自动生成，每次在最大值基础上增加10。

 

--1、创建一个存储过程
CREATE TABLE dept_hs AS SELECT * FROM dept;
CREATE OR REPLACE PROCEDURE pro_add_dept (d_name IN VARCHAR2, loc IN VARCHAR2:='上海')
IS dept_no NUMBER;
BEGIN
	SELECT MAX(deptno) INTO dept_no FROM dept_hs;
	dept_no := dept_no + 10;
	INSERT INTO dept_hs VALUES(dept_no, d_name, loc);
	COMMIT;
	EXCEPTION
		WHEN OTHERS THEN
			dbms_output.put_line('新增部门失败');
END;
--2、调用存储过程
DECLARE
	dname VARCHAR2(10):='开发部';
	loc VARCHAR2(10):='北京';
BEGIN
	pro_add_dept(dname,loc);
END;

 

 

2.2、带输入、输出参数的存储过程：输入一个值，输出这个数的两倍。

 

CREATE OR REPLACE PROCEDURE proc_n_double(in_num IN NUMBER,out_num OUT NUMBER)
AS
BEGIN
  out_num := in_num*2;
END;
-- 调用存储过程
DECLARE
   v_num NUMBER :=24;
   v_result NUMBER;
BEGIN
  proc_n_double(v_num,v_result);
  dbms_output.put_line('原有值：' || v_num || '翻倍后为：' || v_result);
END;

 

 

 

2.3、既可以作为输入，又可以作为输出的参数：根据部门编号得到部门名称和此部门的平均工资。

 

CREATE OR REPLACE PROCEDURE pro_deptname_sal(deptinfo IN OUT VARCHAR2,avgsal OUT NUMBER)
IS 
	dept_no NUMBER;
BEGIN 
	--系统自带函数，转化为数字类型
	dept_no := to_number(deptinfo);
	SELECT dname INTO deptinfo FROM dept_hs WHERE deptno = dept_no;
	SELECT AVG(sal) INTO avgsal FROM emp_hs WHERE deptno = dept_no;
END;
--调用存储过程
DECLARE
	d_name VARCHAR(20);
	avg_sal NUMBER;
BEGIN
	d_name := '&请输入部门编号';
	pro_deptname_sal(d_name,avg_sal);
	dbms_output.put_line('部门名称：'||d_name ||'平均工资：'||avg_sal );
END;