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'
	        ||'MANAGERANALYSTSALESMAN');
	    WHEN sal NOT BETWEEN 1000 AND 5000 THEN
	      RAISE_APPLICATION_ERROR(-20001,'ʱ10005000֮');
	    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';










