学习笔记：18pl/sql过程

18-1:建立无参数的过程
	CREATE OR REPLACE PROCEDURE out_time
	IS
	BEGIN
	  dbms_session.set_nls('NLS_DATE_FORMAT',
	    '''YYYY-MM-DD HH24:MI:SS''');
	  dbms_output.put_line(sysdate);
	END;
	/
18-2:建立有输入参数的过程
	CREATE OR REPLACE PROCEDURE add_emp(
	  empno emp.empno%TYPE,ename emp.ename%TYPE,
	  job emp.job%TYPE,mgr emp.mgr%TYPE,
	  hiredate emp.hiredate%TYPE,sal emp.sal%TYPE,
	  comm emp.comm%TYPE,deptno emp.deptno%TYPE)
	IS
	BEGIN
	  INSERT INTO emp
	    VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno);
	END;
	/
18-3:建立有输出参数的过程
	CREATE OR REPLACE PROCEDURE update_sal
	(eno NUMBER,salary NUMBER,name OUT VARCHAR2) IS
	BEGIN
	  UPDATE emp SET sal=salary WHERE empno=eno
	  RETURNING ename INTO name;
	END;
	/
18-4:建立有输入输出参数的过程
	CREATE OR REPLACE PROCEDURE divide
	(num1 IN OUT NUMBER,num2 IN OUT NUMBER) IS
	  v1 NUMBER;
	  v2 NUMBER;
	BEGIN
	  v1:=TRUNC(num1/num2);
	  v2:=MOD(num1,num2);
	  num1:=v1;
	  num2:=v2;
	END;
	/
18-5:调用无参数的过程
	set serveroutput on
	exec out_time
18-6:调用带有输入参数的过程
	exec add_emp(1111,'MARY','CLERK',7369,SYSDATE,1200,null,30)
18-7:调用带有输出参数的过程
	DECLARE
	  v_name emp.ename%TYPE;
	BEGIN
	  update_sal(&eno,&salary,v_name);
	  dbms_output.put_line('姓名:'||v_name);
	END;
	/
18-8:调用带有输入输出参数的过程
	DECLARE
	  n1 NUMBER:=&n1;
	  n2 NUMBER:=&n2;
	BEGIN
	  divide(n1,n2);
	  dbms_output.put_line('商:'||n1||',余数:'||n2);
	END;
	/
18-9:使用位置传递为参数传递变量和数据
	CALL add_emp(2222,'MIKE',NULL,NULL,SYSDATE,800,NULL,30);
18-10:使用名称传递为参数传递变量和数据
	exec add_emp(empno=>3333,hiredate=>null,ename=>'JOHN',job=>NULL,mgr=>null,sal=>NULL,comm=>null,deptno=>null)
18-11:使用组合传递为参数传递变量和数据
	exec add_emp(4444,'AGASI','CLERK',NULL,hiredate=>SYSDATE,sal=>1200,comm=>0,deptno=>30)
18-12:为过程参数指定默认值
	CREATE OR REPLACE PROCEDURE add_emp(
	  empno emp.empno%TYPE,ename emp.ename%TYPE,
	  job emp.job%TYPE DEFAULT 'CLERK',mgr emp.mgr%TYPE,
	  hiredate emp.hiredate%TYPE DEFAULT SYSDATE,
	  sal emp.sal%TYPE DEFAULT 1000,
	  comm emp.comm%TYPE DEFAULT 0,deptno emp.deptno%TYPE)
	IS
	BEGIN
	  INSERT INTO emp VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno);
	END;
	/
	exec add_emp(5555,'BUSH',mgr=>7788,deptno=>30)
18-13:使用异常处理
	CREATE OR REPLACE PROCEDURE update_sal(
	  name emp.ename%TYPE,salary emp.sal%TYPE) IS
	  e_no_rows EXCEPTION;
	BEGIN
	  UPDATE emp SET sal=salary WHERE LOWER(ename)=LOWER(name);
	  IF SQL%NOTFOUND THEN
	    RAISE e_no_rows;
	  END IF;
	EXCEPTION
	  WHEN e_no_rows THEN
	    DBMS_OUTPUT.PUT_LINE('该雇员不存在');
	END;
	/
18-14:自定义错误信息
	CREATE OR REPLACE PROCEDURE add_emp(
	  empno emp.empno%TYPE,ename emp.ename%TYPE,
	  job emp.job%TYPE DEFAULT 'CLERK',mgr emp.mgr%TYPE,
	  hiredate emp.hiredate%TYPE DEFAULT SYSDATE,
	  sal emp.sal%TYPE DEFAULT 1000,
	  comm emp.comm%TYPE DEFAULT 0,deptno emp.deptno%TYPE)
	IS
	  e_2291 EXCEPTION;
	  PRAGMA EXCEPTION_INIT(e_2291,-2291);
	BEGIN
	  CASE
	    WHEN job NOT IN ('CLERK','MANAGER','ANALYST','SALESMAN') THEN
	      RAISE_APPLICATION_ERROR(-20000,'雇员岗位只能是CLERK'
	        ||'、MANAGER、ANALYST或者SALESMAN');
	    WHEN sal NOT BETWEEN 1000 AND 5000 THEN
	      RAISE_APPLICATION_ERROR(-20001,'工资必须在1000到5000之间');
	    ELSE
	      INSERT INTO emp
	        VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno);
	  END CASE;
	EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    RAISE_APPLICATION_ERROR(-20002,'雇员号不能重复');
	  WHEN e_2291 THEN
	    RAISE_APPLICATION_ERROR(-20003,'部门号不存在');
	END;
	/
18-15:使用纪录变量作为输入参数
	CREATE OR REPLACE PROCEDURE add_dept(
	  dept_record dept%ROWTYPE) IS
	BEGIN
	  INSERT INTO dept VALUES dept_record;
	EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    RAISE_APPLICATION_ERROR(-20012,'部门号不能重复');
	END;
	/
	DECLARE
	  dept_record dept%ROWTYPE;
	BEGIN
	  dept_record.deptno:=&dno;
	  dept_record.dname:='&name';
	  dept_record.loc:='&loc';
	  add_dept(dept_record);
	END;
	/
18-16:使用纪录变量作为输出参数
	CREATE OR REPLACE PROCEDURE get_employee(
	  eno emp.empno%TYPE,emp_record OUT emp%ROWTYPE) IS
	BEGIN
	  SELECT * INTO emp_record FROM emp WHERE empno=eno;
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    RAISE_APPLICATION_ERROR(-20000,'雇员不存在');
	END;
	/
	DECLARE
	  emp_record emp%ROWTYPE;
	BEGIN
	  get_employee(&eno,emp_record);
	  dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal);
	END;
	/
18-17:使用集合变量作为输入参数
	CREATE TYPE deptno_table_type IS TABLE OF NUMBER(2);
	/
	CREATE TYPE dname_table_type IS TABLE OF VARCHAR2(10);
	/
	CREATE TYPE loc_table_type IS TABLE OF VARCHAR2(20);
	/
	CREATE OR REPLACE PROCEDURE add_department(
	  deptno_table deptno_table_type,
	  dname_table dname_table_type,loc_table loc_table_type)
	IS
	BEGIN
	  FORALL i IN 1..deptno_table.COUNT
	    INSERT INTO dept VALUES
	      (deptno_table(i),dname_table(i),loc_table(i));
	EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    RAISE_APPLICATION_ERROR(-20012,'部门号不能重复');
	END;
	/
	DECLARE
	  deptno_table deptno_table_type:=deptno_table_type(60,70,80);
	  dname_table dname_table_type :=dname_table_type('计划处','质量处','技术处');
	  loc_table loc_table_type:=loc_table_type('呼和浩特','包头','乌海');
	BEGIN
	  add_department(deptno_table,dname_table,loc_table);
	END;
	/
18-18:使用集合变量作为输出参数
	CREATE TYPE ename_table_type IS TABLE OF VARCHAR2(10);
	/
	CREATE TYPE job_table_type IS TABLE OF VARCHAR2(10);
	/
	CREATE OR REPLACE PROCEDURE get_emp(
	  dno NUMBER,ename_table OUT ename_table_type,
	  job_table OUT job_table_type) IS
	BEGIN
	  SELECT ename,job BULK COLLECT INTO ename_table,job_table
	  FROM emp WHERE deptno=dno;
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    RAISE_APPLICATION_ERROR(-20010,'该部门不存在');
	END;
	/
	DECLARE
	  ename_table ename_table_type;
	  job_table job_table_type;
	BEGIN
	  get_emp(&dno,ename_table,job_table);
	  FOR i IN 1..ename_table.COUNT LOOP
	    dbms_output.put_line('姓名:'||ename_table(i)
	     ||',岗位:'||job_table(i));
	  END LOOP;
	END;
	/
18-19:删除过程
	DROP PROCEDURE add_department;
18-20:显示编译错误
	SHOW ERRORS
18-21:确定过程状态
	SELECT object_name FROM user_objects WHERE status='INVALID' AND object_type='PROCEDURE';
18-22:编译过程
	ALTER PROCEDURE get_emp COMPILE;
18-23:查看过程代码
	SELECT text FROM user_source WHERE name='GET_EMP';