ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE PL/SQ入门

ORACLE PL/SQ入门

原创 Linux操作系统 作者:gjm008 时间:2008-12-25 22:08:39 0 删除 编辑
ORACLE PL/SQ入门
 
六、存储过程

1.命令格式
  存储过程是一个PL/SQL程序块,接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT),与函数不同, 存储过程没有返回值,存储过程不能由SQL语句直接使用,只能通过EXECUT命令或PL/SQL程序块内部调用,定义存储过程的语法如下:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->PROCEDURE Name [(Parameter[,Parameter,])]
IS|AS
  
[Local Declarations]
BEGIN
  
Execute statements;
  
[EXCEPTION Exception Handlers]
END [Name];



2.调用
  存储过程可以直接用EXECUT命令调用或PL/SQL程序块内部调用。用EXECUT命令调用存储过程的格式如下:
set serveroutput on --用于显示结果
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SQL>EXCUTE  Proc_Name(par1, par2…);

  存储过程也可以被另外的PL/SQL块调用,调用的语句是:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->DECLARE par1, par2;
BEGIN
  Proc_Name(par1, par2…);
END;


3.释放
  当某个存储过程不再需要时,应将其从内存中删除,以释放它占用的内存资源。释放过程的语句格式如下:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SQL>DROP PROCEDURE Proc_Name;

4.实例:
  编写存储过程,显示所指定雇员名所在的部门名和位置。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,
                                     pdname OUT dept.dname
%TYPE,
                                     ploc   OUT dept.loc
%TYPE) AS
BEGIN
  
SELECT dname, loc
    
INTO pdname, ploc
    
FROM emp, dept
   
WHERE emp.deptno = dept.deptno
     
AND emp.ename = pename;
END;

  调用:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->VARIABLE vdname VARCHAR2(14);
VARIABLE vloc 
VARCHAR2(13);
EXECUTE DeptMesg('SMITH', :vdname£¬ :vloc);
PRINT vdname vloc; 

七、函数

1.命令格式
  函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数,有一个返回值,返回值的数据类型在创建函数时定义。定义函数的语法如下:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->FUNCTION Name [{Parameter[,Parameter,])]
RETURN DataTypes
IS
[Local Declarations]
BEGIN
  
Execute Statements;
  
[EXCEPTION Exception Handlers]
END [Name];

2.调用
  无论在命令行还是在程序语句中,函数都可以通过函数名称直接在表达式中调用。例如:将函数Count_Num(‘女’)的返回值赋予变量Man_Num。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SQL>EXECUTE Man_Num := Count_Num('');

3.释放
  当函数不再使用时,要用DROP命令将其从内存中删除,例如:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SQL>DROP FUNCTION Count_Num;

4.实例
  编写一个函数以显示该雇员在此组织中的工作天数。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE OR REPLACE FUNCTION Hire_Day(no emp.empno%TYPE) RETURN NUMBER AS
  vhiredate emp.hiredate
%TYPE;
  vday      
NUMBER;
BEGIN
  
SELECT hiredate INTO vhiredate FROM emp WHERE empno = no;
  vday :
= CEIL(SYSDATE - vhiredate);
  
RETURN vday;
END;

八、触发器

1.触发器的创建规则:
  ①作用范围清晰;
  ②不要让触发器去完成Oracle后台已经能够完成的功能;
  ③限制触发器代码的行数;
  ④不要创建递归的触发器;
  ⑤触发器仅在被触发语句触发时进行集中的,全局的操作,同用户和数据库应用无关。

2.可以创建被如下语句所触发的触发器:
  ①DML语句(DELETE,INSERT,UPDATE);
  ②DDL语句(CREATE,ALTER, DROP);
  ③数据库操作(SERVERERROR,LOGON,LOGOFF,STARTUP,SHUTDOWN)。

3.注意事项
  ①触发器可以声明为在对记录进行操作之前,在之前(检查约束之前和 INSERT,UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT, UPDATE 或 DELETE 操作)触发;
  ②一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次;
  ③SELECT 并不更改任何行,因此不能创建 SELECT 触发器.这种场合下规则和视图更适合;
  ④触发器和某一指定的表格有关,当该表格备删除时,任何与该表有关的触发器同样会被删除;
  ⑤在一个表上的每一个动作只能有一个触发器与之关联;
  ⑥在一个单独的表上,最多只能创建三个触发器与之关联,一个INSERT触发器,一个DELETE触发器和一个UPDATE触发器;

4.删除触发器的语句格式为:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->DROP TRIGGER name ON table;

  一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句,可为对一指定表的INSERT、UNPDATE或DELETE语句。触发限制是指定一个布尔表达式,当触发器激发时该布尔表达式是必须为真。触发器作为过程,是PL/SQL块,当触发语句发出、触发限制计算为真时该过程被执行。

5.实例
  编写一个数据库触发器,当任何时候某个部门从dept表中删除时,该触发器将从emp表中删除该部门的所有雇员。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE OR REPLACE TRIGGER del_emp_deptno
  BEFORE 
DELETE ON dept
  
FOR EACH ROW
BEGIN
  
DELETE FROM emp WHERE deptno = :OLD.deptno;
END;

九、包

1.包头
  创建包头的语句格式如下:
  CREATE PACKAGE<包名> IS
    变量、常量及数据类型定义;
    游标定义;
    函数、过程定义和参数列表及返回类型;
  END<包名>;

2.包体
  创建包主体部分的语句格式如下:
  CREATE PACKAGE BODY<包名>
  AS
    游标、函数、过程的具体定义;
  END<包名>;

3.实例

  包头代码:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->--创建包头
CREATE PACKAGE test_package IS
  
--定义变量
  man_num   NUMBER;
  woman_num 
NUMBER;
  
--定义游标
  CURSOR学生;

  
--定义函数
  CREATE FUNCTION f_count(in sex IN 学生.sex%TYPE)
  
--定义返回值类型
  RETURN NUMBER;

  
--定义过程
  CREATE PROCEDURE p_count(in_sex IN 学生.sex%TYPE, out_num OUT NUMBER);

--包头结束
END test_package;


 包体代码:
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->--创建包体
CREATE PACKAGE BODY test_package AS
  
--游标具体定义
  CURSOR 学生IS
    
SELECT 学号,姓名 FROM 学生 WHERE 学号 < 50;

  
--函数具体定义
  FUNCTION f_count(in_sex IN学生.sex%TYPE)
  
--定义返回值类型
   RETURN NUMBER IS
    out_num 
NUMBER;
    
--函数体
  BEGIN
    
IF in_sex = '' THEN
      
SELECT count(sex) INTO out_num FROM 学生 WHERE性别='';
    
ELSE
      
SELECT count(sex) INTO out_num FROM 学生 WHERE 性别='';
    
END IF;
    
--返回函数值
    RETURN(out_num);
    
--函数定义结束
  END f_count;

  
--过程具体定义
  PROCEDURE p_count(in_sex IN学生.sex%TYPE, out_num OUT NUMBERAS
    
--过程体
  BEGIN
    
IF in_sex = '' THEN
      
SELECT count(sex) INTO out_num FROM 学生 WHERE性别 = '';
    
ELSE
      
SELECT count(sex) INTO out_num FROM 学生 WHERE 性别= '';
    
END IF;
    
--过程定义结束
  END P_count;

--包体定义结束
END test_package;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16486600/viewspace-521264/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-25

  • 博文量
    41
  • 访问量
    187208