ORACLE PL/SQ入门 存储过程

  1. ORACLE PL/SQ入门(六-九、存储过程、函数、触发器、包)
  2. ORACLE PL/SQ入门 
  3. 六、存储过程
  4. 1.命令格式
  5.   存储过程是一个PL/SQL程序块,接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT),与函数不同, 存储过程没有返回值,存储过程不能由SQL语句直接使用,只能通过EXECUT命令或PL/SQL程序块内部调用,定义存储过程的语法如下:
  6. PROCEDURE Name [(Parameter[,Parameter,])]
  7. IS|AS
  8.   [Local Declarations]
  9. BEGIN
  10.   Execute statements;
  11.   [EXCEPTION Exception Handlers]
  12. END [Name];
  13. 2.调用
  14.   存储过程可以直接用EXECUT命令调用或PL/SQL程序块内部调用。用EXECUT命令调用存储过程的格式如下:
  15. SQL>EXCUTE  Proc_Name(par1, par2…);
  16.   存储过程也可以被另外的PL/SQL块调用,调用的语句是:
  17. DECLARE par1, par2;
  18. BEGIN
  19.   Proc_Name(par1, par2…);
  20. END;
  21. 3.释放
  22.   当某个存储过程不再需要时,应将其从内存中删除,以释放它占用的内存资源。释放过程的语句格式如下:
  23. SQL>DROP PROCEDURE Proc_Name;
  24. 4.实例:
  25.   编写存储过程,显示所指定雇员名所在的部门名和位置。
  26. CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,
  27.                                      pdname OUT dept.dname%TYPE,
  28.                                      ploc   OUT dept.loc%TYPE) AS
  29. BEGIN
  30.   SELECT dname, loc
  31.     INTO pdname, ploc
  32.     FROM emp, dept
  33.    WHERE emp.deptno = dept.deptno
  34.      AND emp.ename = pename;
  35. END;
  36.   调用:
  37. VARIABLE vdname VARCHAR2(14);
  38. VARIABLE vloc VARCHAR2(13);
  39. EXECUTE DeptMesg('SMITH', :vdname£? :vloc);
  40. PRINT vdname vloc; 
  41. 七、函数
  42. 1.命令格式
  43.   函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数,有一个返回值,返回值的数据类型在创建函数时定义。定义函数的语法如下:
  44. FUNCTION Name [{Parameter[,Parameter,])]
  45. RETURN DataTypes
  46. IS
  47. [Local Declarations]
  48. BEGIN
  49.   Execute Statements;
  50.   [EXCEPTION Exception Handlers]
  51. END [Name];
  52. 2.调用
  53.   无论在命令行还是在程序语句中,函数都可以通过函数名称直接在表达式中调用。例如:将函数Count_Num(‘女’)的返回值赋予变量Man_Num。
  54. SQL>EXECUTE Man_Num := Count_Num('女');
  55. 3.释放
  56.   当函数不再使用时,要用DROP命令将其从内存中删除,例如:
  57. SQL>DROP FUNCTION Count_Num;
  58. 4.实例
  59.   编写一个函数以显示该雇员在此组织中的工作天数。
  60. CREATE OR REPLACE FUNCTION Hire_Day(no emp.empno%TYPE) RETURN NUMBER AS
  61.   vhiredate emp.hiredate%TYPE;
  62.   vday      NUMBER;
  63. BEGIN
  64.   SELECT hiredate INTO vhiredate FROM emp WHERE empno = no;
  65.   vday := CEIL(SYSDATE - vhiredate);
  66.   RETURN vday;
  67. END;
  68. 八、触发器
  69. 1.触发器的创建规则:
  70.   ①作用范围清晰;
  71.   ②不要让触发器去完成Oracle后台已经能够完成的功能;
  72.   ③限制触发器代码的行数;
  73.   ④不要创建递归的触发器;
  74.   ⑤触发器仅在被触发语句触发时进行集中的,全局的操作,同用户和数据库应用无关。
  75. 2.可以创建被如下语句所触发的触发器:
  76.   ①DML语句(DELETE,INSERT,UPDATE);
  77.   ②DDL语句(CREATE,ALTER, DROP);
  78.   ③数据库操作(SERVERERROR,LOGON,LOGOFF,STARTUP,SHUTDOWN)。 
  79. 3.注意事项
  80.   ①触发器可以声明为在对记录进行操作之前,在之前(检查约束之前和 INSERT,UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT, UPDATE 或 DELETE 操作)触发;
  81.   ②一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次;
  82.   ③SELECT 并不更改任何行,因此不能创建 SELECT 触发器.这种场合下规则和视图更适合;
  83.   ④触发器和某一指定的表格有关,当该表格备删除时,任何与该表有关的触发器同样会被删除;
  84.   ⑤在一个表上的每一个动作只能有一个触发器与之关联;
  85.   ⑥在一个单独的表上,最多只能创建三个触发器与之关联,一个INSERT触发器,一个DELETE触发器和一个UPDATE触发器;
  86. 4.删除触发器的语句格式为:
  87. DROP TRIGGER name ON table;
  88.   一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句,可为对一指定表的INSERT、UNPDATE或DELETE语句。触发限制是指定一个布尔表达式,当触发器激发时该布尔表达式是必须为真。触发器作为过程,是PL/SQL块,当触发语句发出、触发限制计算为真时该过程被执行。
  89. 5.实例
  90.   编写一个数据库触发器,当任何时候某个部门从dept表中删除时,该触发器将从emp表中删除该部门的所有雇员。
  91. CREATE OR REPLACE TRIGGER del_emp_deptno
  92.   BEFORE DELETE ON dept
  93.   FOR EACH ROW
  94. BEGIN
  95.   DELETE FROM emp WHERE deptno = :OLD.deptno;
  96. END;
  97. 九、包
  98. 1.包头
  99.   创建包头的语句格式如下:
  100.   CREATE PACKAGE<包名> IS
  101.     变量、常量及数据类型定义;
  102.     游标定义;
  103.     函数、过程定义和参数列表及返回类型;
  104.   END<包名>;
  105. 2.包体
  106.   创建包主体部分的语句格式如下:
  107.   CREATE PACKAGE BODY<包名>
  108.   AS
  109.     游标、函数、过程的具体定义;
  110.   END<包名>;
  111. 3.实例
  112.   包头代码:
  113. --创建包头
  114. CREATE PACKAGE test_package IS
  115.   --定义变量
  116.   man_num   NUMBER;
  117.   woman_num NUMBER;
  118.   --定义游标
  119.   CURSOR学生;
  120.   --定义函数
  121.   CREATE FUNCTION f_count(in sex IN 学生.sex%TYPE)
  122.   --定义返回值类型
  123.   RETURN NUMBER;
  124.   --定义过程
  125.   CREATE PROCEDURE p_count(in_sex IN 学生.sex%TYPE, out_num OUT NUMBER);
  126. --包头结束
  127. END test_package;
  128.   包体代码:
  129. --创建包体
  130. CREATE PACKAGE BODY test_package AS
  131.   --游标具体定义
  132.   CURSOR 学生IS
  133.     SELECT 学号,姓名 FROM 学生 WHERE 学号 < 50;
  134.   --函数具体定义
  135.   FUNCTION f_count(in_sex IN学生.sex%TYPE)
  136.   --定义返回值类型
  137.    RETURN NUMBER IS
  138.     out_num NUMBER;
  139.     --函数体
  140.   BEGIN
  141.     IF in_sex = '男' THEN
  142.       SELECT count(sex) INTO out_num FROM 学生 WHERE性别='男';
  143.     ELSE
  144.       SELECT count(sex) INTO out_num FROM 学生 WHERE 性别='女';
  145.     END IF;
  146.     --返回函数值
  147.     RETURN(out_num);
  148.     --函数定义结束
  149.   END f_count;
  150.   --过程具体定义
  151.   PROCEDURE p_count(in_sex IN学生.sex%TYPE, out_num OUT NUMBER) AS
  152.     --过程体
  153.   BEGIN
  154.     IF in_sex = '男' THEN
  155.       SELECT count(sex) INTO out_num FROM 学生 WHERE性别 = '男';
  156.     ELSE
  157.       SELECT count(sex) INTO out_num FROM 学生 WHERE 性别= '女';
  158.     END IF;
  159.     --过程定义结束
  160.   END P_count;
  161. --包体定义结束
  162. END test_package; 
  163. oracle 分页存储过程
  164. 包头:
  165. create or replace package JT_P_page is
  166.   -- Author : ujnjt
  167.   -- Created : 2007-8-5 16:43:50
  168.   -- Purpose : 分页功能
  169.   -- Public type declarations
  170.   type type_cur is ref cursor; --定义游标变量用于返回记录集
  171.   procedure Pagination(Pindex in number, --要显示的页数索引,从0开始
  172.                        Psql in varchar2, --产生分页数据的查询语句
  173.                        Psize in number, --每页显示记录数
  174.                        Pcount out number, --返回的分页数
  175.                        Prowcount out number, --返回的记录数
  176.                        v_cur out type_cur --返回分页数据的游标
  177.                        );
  178. end JT_P_page;
  179. 包体:
  180. create or replace package body JT_P_page is
  181.   procedure Pagination(Pindex in number, --要显示的页数索引,从0开始
  182.                        Psql in varchar2, --产生分页数据的查询语句
  183.                        Psize in number, --每页显示记录数
  184.                        Pcount out number, --返回的分页数
  185.                        Prowcount out number, --返回的记录数
  186.                        v_cur out type_cur --返回分页数据的游标
  187.                        ) AS
  188.     v_sql VARCHAR2(1000);
  189.     v_Pbegin number;
  190.     v_Pend number;
  191.   begin
  192.     v_sql := 'select count(*) from (' || Psql || ')';
  193.     execute immediate v_sql into Prowcount; --计算记录总数
  194.     Pcount := ceil(Prowcount / Psize); --计算分页总数
  195.     --显示任意页内容
  196.     v_Pend := Pindex * Psize + Psize;
  197.     v_Pbegin := v_Pend - Psize + 1;
  198.   
  199.     --Psql := 'select rownum as rn , t.* from pay_en_voucher t'; --要求必须包含rownum字段
  200.     v_sql := 'select * from (' || Psql || ') where rn between ' || v_Pbegin || ' and ' || v_Pend;
  201.              
  202.     open v_cur for v_sql;
  203.   end Pagination;
  204.   
  205. end JT_P_page;
  206. 调用方法:
  207. -- Created on 2007-8-5 by ujnjt 
  208. declare
  209.   psqlContent varchar2(1000);
  210.   pageIndex number;
  211.   pageCount number; --返回的分页数
  212.   pageSize number; --每一页的记录数
  213.   rowscount number; -- 返回的记录总数
  214.   return_cur JT_P_PAGE.type_cur;
  215.   curr_id pay_en_voucher.id%type;
  216.   curr_rn number;
  217. begin
  218.   psqlContent := 'select rownum as rn , t.id from pay_en_voucher t';
  219.   pageIndex := 0;
  220.   pageSize := 20;
  221.   JT_P_PAGE.Pagination(pageIndex,
  222.                        psqlContent,
  223.                        pageSize,
  224.                        pageCount,
  225.                        rowscount,
  226.                        return_cur);
  227.   if return_cur%isopen then
  228.     close return_cur;
  229.   end if;
  230.   
  231.   for Counter in 0 .. pageCount - 1 loop
  232.   pageIndex := Counter;
  233.   JT_P_PAGE.Pagination(pageIndex, psqlContent, pageSize, pageCount, rowscount, return_cur);
  234.   if return_cur%isopen then
  235.     dbms_output.put_line('第' || (pageIndex + 1) || '页数据开始');
  236.     loop
  237.       fetch return_cur
  238.         into curr_rn, curr_id;
  239.       if (return_cur%notfound) then
  240.         exit;
  241.       end if;
  242.       dbms_output.put_line(curr_rn);
  243.       dbms_output.put_line(curr_id);
  244.       dbms_output.put_line('--------');
  245.     end loop;
  246.     dbms_output.put_line('第' || (pageIndex + 1) || '页数据结束');
  247.     close return_cur;
  248.     dbms_output.put_line('=========================================');
  249.   end if;
  250. end loop;
  251. end;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值