存储过程实验报告

存储过程实验报告 《数据库系统原理》 实 验 报 告 实验名称: 存储过程和 函数 excel方差函数excelsd函数已知函数     2 f x m x mx m      2 1 4 2拉格朗日函数pdf函数公式下载 实验 任课教师: 学号: 姓名: 完成日期 2012年11月17日 一、实验目的 1、熟悉ORACLE存储过程和函数的定义及使用 2、了解其在数据库中的存储 二、实验步骤与实验结果 1、创建存储过程 create [or replace] procedure [模式名] 过程名 [(参数[IN/OUT/IN OUT] 参数类型)] {is/as} [语句序列] [说明部分] begin [exception 例外处理] end [过程名]; 说明:IN参数:输入参数，主程序向过程传递参数; OUT参数:输出参数，过程向主程序传递参数; IN OUT参数:可以实现过程与主过程双向交流数据。 如果存储过程有OUT或者IN OUT类型的参数，那么存储过程就只能在PL/SQL中被 调用，不能使用EXECUTE或者CALL调用。 set serveroutput on 打开oracle的输出 图1打开oracle的输出 例:不带参数的存储过程 obstacles, correcting misunderstandings, advocate good cadre style. 2, adhere to the scientific decision-making and democratic decision-making decision-making law. Correctly handle to ensure that government decrees and based on the actual creative work, giving full play to subjective initiative, put an end to implement the conference meeting, to document the implementation of documents etc. Improve the scientific and democratic decision-making mechanism, improve and implement the decision to solicit the views of experts, the public hearing system. Improve the important decision of risk assessment mechanism and legal review mechanism. 3, to promote team unity. Uphold and improve the system of annual party book talk heart to heart, mind and Party members, Party members to exchange each other not less than 2 times. The implementation of sound team democratic life system, consolidate and enhance the party's mass line educational practice will promote the achievement of the topic of democratic life, criticism and self-criticism normalization, a long-term. Adhere to the party on the basis of the principle of unity, enhance internal communication, strengthen internal supervision, self Sleep maintenance team collective image and authority. (three) to ensure the execution is not strong or disguised to resist government decrees issues. Individual Bureau leadership team members lack of learning political theory system, not from the objective reality, not with the party's interests above all the principles to deal with personal problems. In the specific work safety supervision in the execution is not strong, in the implementation of the decision to deploy the discount, make choices, engage in work, the implementation of the agreement, uncomfortable is not performed, open or disguised to resist, the lack of subject consciousness of the party organization, the lack of resist in society all kinds of bad habits and unwholesome tendencies of courage Gas and courage. Such as the liability insurance of safe production work, individual leaders that have someone arrested, and no funding, not in the global chess perspective to the countryside to carry out daily inspection, found none of my business acceptance of the resumption of production in an armchair, with a pen, indifferent. Corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, to strengthen the sense of responsibility, work to solve the lack of decent occupation explain away, conduct problems. To establish the overall concept, eliminate departmentalism. Strict assessment and accountability, to solve the spiritual slack, nianqingpazhong, status quo, To establish and perfect the muddle along and other issues. Bureau staff conduct work regulations, standardize the behavior of personnel. 2, strengthen the responsibility system. One is the in-depth study and implement the "Hunan provincial Party and government leading cadres Interim Provisions on the work safety of a pair of > (Hunan Office issued 2013 No. 图2不带参数的存储过程 例:带有输入参数的存储过程 图3带有输入参数的存储过程 例:既有输入参数又有输出参数的存储过程 e and enhance the party's mass line educational practice will promote the achievement of the topic of democratic life, criticolidatrs, Party members to exchange each other not less than 2 times. The implementation of sound team democratic life system, consanism. 3, to promote team unity. Uphold and improve the system of annual party book talk heart to heart, mind and Party membeimportant decision of risk assessment mechanism and legal review mechmaking mechanism, improve and implement the decision to solicit the views of experts, the public hearing system. Improve the -documents etc. Improve the scientific and democratic decisionjective initiative, put an end to implement the conference meeting, to document the implementation of making law. Correctly handle to ensure that government decrees and based on the actual creative work, giving full play to sub-making decision-tic decisionmaking and democra-obstacles, correcting misunderstandings, advocate good cadre style. 2, adhere to the scientific decision2Party and government leading cadres Interim Provisions on the work safety of a pair of > (Hunan Office issued 2013 No.depth study and implement the "Hunan provincial -the behavior of personnel. 2, strengthen the responsibility system. One is the in g, status quo, To establish and perfect the muddle along and other issues. Bureau staff conduct work regulations, standardizeazhonthe overall concept, eliminate departmentalism. Strict assessment and accountability, to solve the spiritual slack, nianqingp engthen the sense of responsibility, work to solve the lack of decent occupation explain away, conduct problems. To establisho strt. Corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, tdaily inspection, found none of my business acceptance of the resumption of production in an armchair, with a pen, indifferen y outindividual leaders that have someone arrested, and no funding, not in the global chess perspective to the countryside to carr of bad habits and unwholesome tendencies of courage Gas and courage. Such as the liability insurance of safe production work,inds open or disguised to resist, the lack of subject consciousness of the party organization, the lack of resist in society all k on to deploy the discount, make choices, engage in work, the implementation of the agreement, uncomfortable is not performed,decisiwith personal problems. In the specific work safety supervision in the execution is not strong, in the implementation of the ing political theory system, not from the objective reality, not with the party's interests above all the principles to deal learn e execution is not strong or disguised to resist government decrees issues. Individual Bureau leadership team members lack ofmunication, strengthen internal supervision, self Sleep maintenance team collective image and authority. (three) to ensure thterm. Adhere to the party on the basis of the principle of unity, enhance internal com-criticism normalization, a long-ism and self 图4既有输入参数又有输出参数的存储过程 例:PL/SQL环境下存储过程的定义与执行 图5 PL/SQL环境下存储过程的定义 图6 PL/SQL环境下存储过程的执行 2、创建函数 CREATE [OR REPLACE] FUNCTION [模式名.] 函数名 [(参数名[IN] 数据类型……)] RETURN 数据类型 {IS/AS} [说明部分] BEGIN criticism normalization, a long-and self nd enhance the party's mass line educational practice will promote the achievement of the topic of democratic life, criticismate aParty members to exchange each other not less than 2 times. The implementation of sound team democratic life system, consolid sm. 3, to promote team unity. Uphold and improve the system of annual party book talk heart to heart, mind and Party members,important decision of risk assessment mechanism and legal review mechanimaking mechanism, improve and implement the decision to solicit the views of experts, the public hearing system. Improve the -uments etc. Improve the scientific and democratic decisionjective initiative, put an end to implement the conference meeting, to document the implementation of docmaking law. Correctly handle to ensure that government decrees and based on the actual creative work, giving full play to sub-making decision-decision making and democratic-obstacles, correcting misunderstandings, advocate good cadre style. 2, adhere to the scientific decision3ty and government leading cadres Interim Provisions on the work safety of a pair of > (Hunan Office issued 2013 No.depth study and implement the "Hunan provincial Par-e behavior of personnel. 2, strengthen the responsibility system. One is the instatus quo, To establish and perfect the muddle along and other issues. Bureau staff conduct work regulations, standardize thhong, e overall concept, eliminate departmentalism. Strict assessment and accountability, to solve the spiritual slack, nianqingpazthen the sense of responsibility, work to solve the lack of decent occupation explain away, conduct problems. To establish thtrengCorrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, to s ily inspection, found none of my business acceptance of the resumption of production in an armchair, with a pen, indifferent.out dadividual leaders that have someone arrested, and no funding, not in the global chess perspective to the countryside to carry bad habits and unwholesome tendencies of courage Gas and courage. Such as the liability insurance of safe production work, inds of en or disguised to resist, the lack of subject consciousness of the party organization, the lack of resist in society all kinto deploy the discount, make choices, engage in work, the implementation of the agreement, uncomfortable is not performed, opion personal problems. In the specific work safety supervision in the execution is not strong, in the implementation of the decis political theory system, not from the objective reality, not with the party's interests above all the principles to deal with arningxecution is not strong or disguised to resist government decrees issues. Individual Bureau leadership team members lack of leication, strengthen internal supervision, self Sleep maintenance team collective image and authority. (three) to ensure the eterm. Adhere to the party on the basis of the principle of unity, enhance internal commun- 语句序列 RETURN ( 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 达式) [EXCEPTION 例外处理程序] END [函数名]; 说明:函数只能有输入参数，不能有输出参数，函数有且只有一个返回值。 例:带有两个参数的函数，函数返回两个参数的和 图7带有两个参数的函数，函数返回两个参数的和 图8带有两个参数的函数，函数返回两个参数的和 4、 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 、实现上述例题并完成如下功能要求 设有基于图书馆数据库的4个基本表: 图书(书号，书名，作者，出版社，单价) 读者(读者号，姓名，性别，办公电话，部门，读者类别编号) 借阅(读者号，书号，借出日期，归还日期) 读者类别(读者类别编号，读者类别，允许借阅册数，允许借阅天数) 编写存储过程实现: 1)根据读者号查询该读者的图书借阅的册数 e execution is not strong or disguised to resist government decrees issues. Individual Bureau leadership team members lack ofmunication, strengthen internal supervision, self Sleep maintenance team collective image and authority. (three) to ensure thterm. Adhere to the party on the basis of the principle of unity, enhance internal com-criticism normalization, a long-ism and selfe and enhance the party's mass line educational practice will promote the achievement of the topic of democratic life, criticolidatrs, Party members to exchange each other not less than 2 times. The implementation of sound team democratic life system, consanism. 3, to promote team unity. Uphold and improve the system of annual party book talk heart to heart, mind and Party membeimportant decision of risk assessment mechanism and legal review mechmaking mechanism, improve and implement the decision to solicit the views of experts, the public hearing system. Improve the -documents etc. Improve the scientific and democratic decisionjective initiative, put an end to implement the conference meeting, to document the implementation of making law. Correctly handle to ensure that government decrees and based on the actual creative work, giving full play to sub-making decision-tic decisionmaking and democra-obstacles, correcting misunderstandings, advocate good cadre style. 2, adhere to the scientific decision4Party and government leading cadres Interim Provisions on the work safety of a pair of > (Hunan Office issued 2013 No.depth study and implement the "Hunan provincial -the behavior of personnel. 2, strengthen the responsibility system. One is the in g, status quo, To establish and perfect the muddle along and other issues. Bureau staff conduct work regulations, standardizeazhonthe overall concept, eliminate departmentalism. Strict assessment and accountability, to solve the spiritual slack, nianqingp engthen the sense of responsibility, work to solve the lack of decent occupation explain away, conduct problems. To establisho strt. Corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, tdaily inspection, found none of my business acceptance of the resumption of production in an armchair, with a pen, indifferen y outindividual leaders that have someone arrested, and no funding, not in the global chess perspective to the countryside to carr of bad habits and unwholesome tendencies of courage Gas and courage. Such as the liability insurance of safe production work,inds open or disguised to resist, the lack of subject consciousness of the party organization, the lack of resist in society all k on to deploy the discount, make choices, engage in work, the implementation of the agreement, uncomfortable is not performed,decisiwith personal problems. In the specific work safety supervision in the execution is not strong, in the implementation of the ing political theory system, not from the objective reality, not with the party's interests above all the principles to deal learn 图9 查询该读者的图书借阅的册数 2)根据书名和作者查询相应的出版社和单价 图10 查询相应的出版社和单价 3)读者借阅图书时，需要查询是否超出允许借阅册数，如果超出则不能继续借阅图书，试创建一存储过程完成此查询功能。(提示:输入参数:读者号;输出:能否借阅的结论) Party members to exchange each other not less than 2 times. The implementation of sound team democratic life system, consolid sm. 3, to promote team unity. Uphold and improve the system of annual party book talk heart to heart, mind and Party members,important decision of risk assessment mechanism and legal review mechanimaking mechanism, improve and implement the decision to solicit the views of experts, the public hearing system. Improve the -uments etc. Improve the scientific and democratic decisionjective initiative, put an end to implement the conference meeting, to document the implementation of docmaking law. Correctly handle to ensure that government decrees and based on the actual creative work, giving full play to sub-making decision-decision making and democratic-obstacles, correcting misunderstandings, advocate good cadre style. 2, adhere to the scientific decision5ty and government leading cadres Interim Provisions on the work safety of a pair of > (Hunan Office issued 2013 No.depth study and implement the "Hunan provincial Par-e behavior of personnel. 2, strengthen the responsibility system. One is the instatus quo, To establish and perfect the muddle along and other issues. Bureau staff conduct work regulations, standardize thhong, e overall concept, eliminate departmentalism. Strict assessment and accountability, to solve the spiritual slack, nianqingpazthen the sense of responsibility, work to solve the lack of decent occupation explain away, conduct problems. To establish thtrengCorrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, to s ily inspection, found none of my business acceptance of the resumption of production in an armchair, with a pen, indifferent.out dadividual leaders that have someone arrested, and no funding, not in the global chess perspective to the countryside to carry bad habits and unwholesome tendencies of courage Gas and courage. Such as the liability insurance of safe production work, inds of en or disguised to resist, the lack of subject consciousness of the party organization, the lack of resist in society all kinto deploy the discount, make choices, engage in work, the implementation of the agreement, uncomfortable is not performed, opion personal problems. In the specific work safety supervision in the execution is not strong, in the implementation of the decis political theory system, not from the objective reality, not with the party's interests above all the principles to deal with arningxecution is not strong or disguised to resist government decrees issues. Individual Bureau leadership team members lack of leication, strengthen internal supervision, self Sleep maintenance team collective image and authority. (three) to ensure the eterm. Adhere to the party on the basis of the principle of unity, enhance internal commun-criticism normalization, a long-and self nd enhance the party's mass line educational practice will promote the achievement of the topic of democratic life, criticismate a 图11 查询是否超出允许借阅册数 三、过程分析与建议 通过前面的例子了解了存储过程的编程方法，再加上上网查阅，做出了第四题。我之前也不知道为什么要用存储过程，编写麻烦，后来才意识到运用存储过程，在每次执行时只要更换执行参数即可，不用去改里面每句SQL的where子句。假设要循环处理某些数据，就要用到存储过程。 存储过程的优点: 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。 e execution is not strong or disguised to resist government decrees issues. Individual Bureau leadership team members lack ofmunication, strengthen internal supervision, self Sleep maintenance team collective image and authority. (three) to ensure thterm. Adhere to the party on the basis of the principle of unity, enhance internal com-criticism normalization, a long-ism and selfe and enhance the party's mass line educational practice will promote the achievement of the topic of democratic life, criticolidatrs, Party members to exchange each other not less than 2 times. The implementation of sound team democratic life system, consanism. 3, to promote team unity. Uphold and improve the system of annual party book talk heart to heart, mind and Party membeimportant decision of risk assessment mechanism and legal review mechmaking mechanism, improve and implement the decision to solicit the views of experts, the public hearing system. Improve the -documents etc. Improve the scientific and democratic decisionjective initiative, put an end to implement the conference meeting, to document the implementation of making law. Correctly handle to ensure that government decrees and based on the actual creative work, giving full play to sub-making decision-tic decisionmaking and democra-obstacles, correcting misunderstandings, advocate good cadre style. 2, adhere to the scientific decision6Party and government leading cadres Interim Provisions on the work safety of a pair of > (Hunan Office issued 2013 No.depth study and implement the "Hunan provincial -the behavior of personnel. 2, strengthen the responsibility system. One is the in g, status quo, To establish and perfect the muddle along and other issues. Bureau staff conduct work regulations, standardizeazhonthe overall concept, eliminate departmentalism. Strict assessment and accountability, to solve the spiritual slack, nianqingp engthen the sense of responsibility, work to solve the lack of decent occupation explain away, conduct problems. To establisho strt. Corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, tdaily inspection, found none of my business acceptance of the resumption of production in an armchair, with a pen, indifferen y outindividual leaders that have someone arrested, and no funding, not in the global chess perspective to the countryside to carr of bad habits and unwholesome tendencies of courage Gas and courage. Such as the liability insurance of safe production work,inds open or disguised to resist, the lack of subject consciousness of the party organization, the lack of resist in society all k on to deploy the discount, make choices, engage in work, the implementation of the agreement, uncomfortable is not performed,decisiwith personal problems. In the specific work safety supervision in the execution is not strong, in the implementation of the ing political theory system, not from the objective reality, not with the party's interests above all the principles to deal learn