Les09(创建存储过程和函数).pdf

创建存储过程和函数 Copyright © 2009, Oracle. All rights reserved. 目标 本课学习: • 匿名块和子过程的区别 • 创建一个简单的存储过程并从另外一个匿名块调用它 • 创建一个简单接受一个参数的函数 • 存储过程和函数之间的区别 9 - 2 Copyright © 2009, Oracle. All rights reserved. 存储过程和函数 • 有名字的PL/SQL块 • 被称为PL/SQL 子程序 • 与匿名块一样拥有块结构: – 可选的申明区(without DECLARE keyword) – 强制的执行部分 – 可选的异常处理部分 9 - 3 Copyright © 2009, Oracle. All rights reserved. 匿名块和子程序的区别 Anonymous Blocks Subprograms Unnamed PL/SQL blocks Named PL/SQL blocks Compiled every time Compiled only once Not stored in the database Stored in the database Cannot be invoked by other Named and, therefore, can be invoked by applications other applications Do not return values Subprograms called functions must return values. Cannot take parameters Can take parameters 9 - 4 Copyright © 2009, Oracle. All rights reserved. Procedure: Syntax CREATE [OR REPLACE] PROCEDURE procedure_name [(argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .)] IS|AS procedure_body; 9 - 5 Copyright © 2009, Oracle. All rights reserved. Procedure: Example CREATE TABLE dept AS SELECT * FROM departments; CREATE PROCEDURE add_dept IS dept_id dept.department_id%TYPE; dept_name dept.department_name%TYPE; BEGIN dept_id:=280; dept_name:=ST-Curriculum; INSERT INTO dept(department_id,department_name) VALUES(dept_id,dept_name); DBMS_OUTPUT.PUT_LINE( Inserted || SQL%ROWCOUNT || row );