Oracle存储过程_1

--创建存储过程
/*
命名规范,
输入参数vi_xx,输出参数vo_xx,输入输出参数vio_xx;
此处参数默认为输入参数.参数类型in可省略.若为其他类型,在参数名称之后需
说明,参数值类型只需要生命类型,不需要生命长度;
变量,v_xx;赋值v_xx:=xx;此处需要生命变量的类型及长度;
语法中is/as关键字,is关键字表明后面将跟随一个pl/sql体;
select ...into....;变量赋值,在使用此方法之前.建议先使用
select count(*) into xx from xx[where xx=xx] 判断是否存在该条操作记录;
若不存在,在代码中抛异常用raise+异常名[no_data_found];

*/

CREATE OR REPLACE PROCEDURE USP_ADD_EMP(V_ID NUMBER, --员工编号
V_NAME VARCHAR2, --员工姓名
V_SAL NUMBER， --员工薪资
V_JOB VARCHAR2 DEFAULT'CLERK', --工种 ,默认'CLERK’
V_DNO NUMBER, --部门编号
)
AS
emp_null_error EXCEPTION; --声明异常变量
PRAGMA EXCEPTION_INIT(emp_null_error, -1400);
emp_no_deptno EXCEPTION; --声明异常变量
PRAGMA EXCEPTION_INIT(emp_no_deptno, -2291); --非预定义异常，前提：

BEGIN
INSERT INTO EMP VALUES ();
EXCEPTION
WHEN DUP_VAL_INDEX THEN --预定义异常，雇员编号列如果输入已有雇员编号，违背Oracle唯一约束；
RAISE_APPLICATION_ERROR(-20000，'该雇员已存在');
WHEN EMP_NULL_ERROR THEN --非预定义异常，前提：deptno列非空，插入空值会报错；
RAISE_APPLICATION_ERROR(-20001,'部门编号不能为空');
WHEN EMP_NO_DEPTNO THEN --非预定义异常，前提：deptno列建立外键约束，插入部门编号不在部门表中会报错；
RAISE_APPLICATION_ERROR(-20002，'不存在该部门编号');
END;
-- 调用存储过程
--1 用命令调用
--语法：
EXEC[UTE] PROCEDURE_NAME{PROCEDURE_LIST};
--传递参数的方式3种
--按位置传递参数
EXEC USP_ADD_EMP(EMPNO,ENAME,SAL,JOB,DEPTNO); --值与位置对应
--按名称传递参数 (名称的对应关系是最重要的，次序并不重要)
EXEC USP_ADD_EMP(EMPNO=>10，ENAME=>'',SAL=> ,JOB=>'',DEPTNO=>'');
--混合方式传递参数 (使用位置表示法所传递的参数必须放在所有名称表示法所传递的参数前面)；
EXEC USP_ADD_EMP(EMPNO,SAL,ENAME=>'',JOB=>'',DEPTNO= );
--2 在PL/SQL块中调用
--传递方式的3 种同样 适用
BEGIN
USP_ADD_EMP(EMPNO,ENAME,SAL,JOB,DEPTNO);
END;

--存储过程和游标的用法（调用一个存储过程，返回一个结果集）；
--游标参数为OUT类型；、
--返回员工薪水结果集 (创建存储过程)
CREATE OR REPLACE PROCEDURE USP_PROC_EMP(
EMPSALARY OUT SYS_REFCURSOR) --声明系统游标类型
AS
BEGIN
OPEN EMPSALARY FOR --打开游标
SELECT EMPNO,SAL FROM EMP;
END USP_PROC_EMP;
--调用USP_PROC_EMP存储过程，显示员工薪水；
DECLARE
V_EMPNO EMP.EMPNO%TYPE; --创建变量，存储游标数据
V_SAL EMP.SAL%TYPE;
EMP_SALARY SYS_REFCURSOR; --游标参数
BEGIN
USP_PROC_EMP(EMP_SALARY);
LOOP
FETCH EMP_SALARY INTO V_EMPNO,V_SAL; --提取游标
EXIT WHEN EMP_SALARY%NOTFOUND;
DBMS_OUTPUT.put_line(V_EMPNO||'的薪水是'||V_SAL);
END LOOP;
CLOSE EMP_SALARY;
END;