第九讲 子程序.ppt

CHAPTER 9 PL/?SQL?Procedure and Function (based Learning Outcome 3) Content 9.1 Using procedure parameters 9.2 Maintenance Procedure 9.3 Using function parameters 9.4 Maintenance function Objectives After completing this lesson, you should be able to: Use procedure parameters. Learn the development procedure. Learn management procedure. 9.1 Using procedure parameters Procedure is used to complete a specific task of the PL/SQL block. procedure parameters: IN Value used to accept the calling program. Default. OUT Used to return a value to the calling program. IN OUT Used to accept the value of the calling program, and to return the updated value to the calling program. 9.1 Using procedure parameters CREATE [OR REPLACE] PROCEDURE procedure name [(parameter list)] IS|AS local variable declaration BEGIN executable statements [EXCEPTION exception handlers] END; 1、Establish a process without parameters. SET SERVEROUTPUT ON 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; EXEC out_time 9.1 Using procedure parameters 2、Establish the input parameters of the process. SET SERVEROUTPUT ON 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 m%TYPE,deptno emp.deptno%TYPE) IS BEGIN INSERT INTO emp VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno); END; EXEC add_emp(1111,’MARY’,’CLERK’,7369,SYSDATE,1200,null,30) EXEC add_emp(empno=222,hiredate=null,ename=’MIKE’,- job=null,mgr=null,sal=null,comm=null,deptno=null) EXEC add_emp(3333,’AGAST’,’CLERK’,NULL,- hiredate=SYSDATE,sal=1200,comm=0,deptno=30) 9.1 Using procedure parameters 3、Establish the output parameters of the process. SET SERVEROUTPUT ON CR