网站空间已到期 请尽快续费开通,公司网站建设需要什么科目,网页布局基础,猎头公司猎头存储在数据库中供所有用户程序调用的子程序叫做存储过程#xff0c;存储函数。 存储过程是在大型数据库系统中#xff0c;用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序#xff0c;它是一个命名的 PL/SQL 块#xff0c;经编译后存储在数据库中#xff… 存储在数据库中供所有用户程序调用的子程序叫做存储过程存储函数。 存储过程是在大型数据库系统中用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序它是一个命名的 PL/SQL 块经编译后存储在数据库中用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行它。 存储过程是数据库中的一个重要对象任何一个设计良好的数据库应用程序都应该用到存储过程。 存储过程的各个部分 声明部分 可执行部分 异常处理部分(可选) 存储过程的分类 带参存储过程 不带参存储过程 为保证调用多个存储过程中处在同一个事务中所以一般不在存储过程或者存储函数中,commit或rollback 语 法 无参存储过程语法带参存储过程带参数存储过程含赋值方式 create or replace procedure NoParPro as //声明 ; begin // 执行 ; exception//存储过程异常 ; end; create or replace procedure queryempname(sfindno emp.empno%type) as sName emp.ename%type; sjob emp.job%type; begin .... exception .... end; create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin select count(*) into icount from emp where salisal and jobsjob; if icount1 then .... else .... end if; exception when too_many_rows then DBMS_OUTPUT.PUT_LINE(返回值多于1行); when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出错); end; 其中参数IN表示输入参数是参数的默认模式。 OUT表示返回值参数类型可以使用任意Oracle中的合法类型。 OUT模式定义的参数只能在过程体内部赋值表示该参数可以将某个值传递回调用他的过程 IN OUT表示该参数可以向该过程中传递值也可以将某个值传出去。 存储过程参数不带取值范围in表示传入out表示输出; 变量带取值范围后面接分号; 在判断语句前最好先用count(*)函数判断是否存在该条操作记录; 用select … into … 给变量赋值; 在代码中抛异常用 raise异常名; 调用存储过程的两种方式 1、SQLexec proc_emp(‘参数1’,’参数2’);//无返回值过程调用 2、SQLvar vsal number SQL exec proc_emp (‘参数1’,:vsal);// 有返回值过程调用 或者call proc_emp (‘参数1’,:vsal);// 有返回值过程调用 begin sayHelloWorld(); sayHelloWorld(); end; 将过程的 执行权限 授予其他 用户 SQL GRANT EXECUTE ON find_emp TO MARTIN; SQL GRANT EXECUTE ON swap TO PUBLIC; 和函 数 区别 一般来讲过程和函数的区别在于函数可以有一个返回值而过程没有返回值。但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数在过程和函数中实现返回多个值 1 如果存储过程想实现有返回值的业务我们就必须使用out类型的参数。 2 即便是存储过程使用了out类型的参数起本质也不是真的有了返回值 3 而是在存储过程内部给out类型参数赋值在执行完毕后我们直接拿到输出类型参数的值。 如果只有一个返回值用存储函数否则就用存储过程 过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数在过程和函数中实现返回多个值 存储过程和存储函数都可以有out参数存储过程和存储函数都可以有多个out参数存储过程可以通过out参数来实现返回值。相同点1.创建语法结构相似都可以携带多个传入参数和传出参数。 2.都是一次编译多次执行。 不同点1.存储过程定义关键字用procedure函数定义用function。 2.存储过程中不能用return返回值但函数中可以而且函数中必须有return子句。procedur不叫返回值叫带出可以带出多个值; 3.执行方式略有不同存储过程的执行方式有两种1.使用execute2.使用begin和end函数除了存储过程的两种方式外还可以当做表达式使用例如放在select中select f1() form dual;。 4.在sql数据操纵语句中只能调用函数而不能调用存储过程,模块化 将程序分解为逻辑模块 可重用性 可以被任意数目的程序调用 可维护性 简化维护操作 安全性 通过设置权限使数据更安全 提高性能函数不能独立运行过程可作为一个独立的PL/SQL语句运行1.在oracle中数据表别名不能加as如 select a.appname from appinfo a;-- 正确 select a.appname from appinfo as a;-- 错误 2.在存储过程中select某一字段时后面必须紧跟into如果select整个记录利用游标的话就另当别论了。 3.在利用select…into…语法时必须先确保数据库中有该条记录否则会报出”no data found”异常。 4.在存储过程中别名不能和字段名称相同否则虽然编译可以通过但在运行阶段会报错 5.select sum(vcount) into fcount from A where bidxxxxxx; 如果A表中不存在bid”xxxxxx”的记录则fcountnull(即使fcount定义时设置了默认值 如fcount number(8):0依然无效fcount还是会变成null) 这样以后使用fcount时就可能有问题所以在这里最好先判断一下 if fcount is null then fcount:0; end if;触发器 触发器是一种特殊的存储过程触发器在数据库里以独立的对象存储它与存储过程和函数不同的是存储过程与函数需要用户显示调用才执行而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且触发器不能接收参数。所以运行触发器就叫触发或点火firing。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束或用来监视对数据库的各种操作实现审计的功能。 1、触发器类型 主要有DML触发器、替代触发器、系统事件触发器和DDL触发器。 DML触发器ORACLE可以在DML语句进行触发可以在DML操作前BEFORE触发器或操作后AFTER触发器进行触发并且可以对每个行或语句操作上进行触发行级触发器和语句级触发器。 INSTEAD OF触发器又称为替代触发器用于执行一个替代操作来代替触发事件的操作 由于在ORACLE里不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。 系统事件触发器在发生如数据库启动或者关闭等系统事件时触发。 DDL触发器由DDL语句触发例如CREATE、ALTER和DROP语句。可分为BEFORE触发器和AFTER触发器。 2、创建触发器 使用CREATE TRIGGER语句语法如下 CREATE [ OR REPLACE ] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { INSERT | DELETE | UPDATE [ OF column [, column … ] ] } #tigger_event [ OR { INSERT | DELETE | UPDATE [OF column [, column … ] ] } ... ] ON [ schema. ] table_name | [ schema.] view_name | [ DATAASE ] [ REFERENCING { OLD [ AS ] old | NEW [ AS ] new | PARENT as parent } ] [ FOR EACH ROW ] [ WHEN trigger_condition ] [ DECLARE declaration_statements ; ] BEGIN trigger_body ; END [ trigger_name ] PL/SQL_BLOCK | CALL procedure_name; 语法说明 trigger_name触发器名称。 BEFORE | AFTER | INSTEAD OFBEFORE和AFTER表示触发器执行的时间在触发事件的前后INSTEAD OF表示触发器中的事件代替触发事件执行。 tigger_event激活触发器的事件例如 INSERT、DELETE、UPDATE 。 ON [ schema. ] table_name | [ schema.] view_name | [ DATABASE ]table_name为DML触发器所针对的表如果是替代触发器则需要指定视图名称view_name如果是DDL触发器或者系统事件触发器则使用ON DATABASE。 REFERENCING说明相关名称在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时必须在它们之前加冒号(:)但在WHEN子句中则不能加冒号。 FOR EACH ROW表示是行级触发器如果未指定则为语句级触发器。 WHEN trigger_condition为触发的运行指定限制条件。 trigger_body触发器体包含触发器的内容。 3、DML触发器 DML触发器由DML语句触发对应的tigger_event为 { INSERT | DELEATE | UPDATE [ OF column [ , ... ] ] } 说明 DML操作主要包括INSERT、DELETE和UPDATE操作通过根据针对的事件可分为INSERT触发器、DELETE触发器和UPDATE触发器。 可以将DML操作细化到列即针对某列进行DML操作时激活触发器。 任何DML都可按照触发时间分为BEFORE和AFTER。 在行级触发器中为了获得某列在操作前后的数据提供两种特殊标识符:OLD和:NEW通过:OLD.column_name的形式可以获取该列的旧数据而通过:NEW.colum_name可以获取该列的新数据INSERT触发器只能用:NEWDELETE触发器只能用:OLDUPDATE触发器可以用:OLD和:NEW。 注意如果创建时不指定FOR EACH ROW则为与语句级触发器所有受影响的数据只处罚一次因此无法使用:NEW和:OLD获取某列的新旧数据。 4、INSTEAD OF触发器 执行一个替代操作来代替触发事件的操作而触发事件本身不会被执行。不过oracle中的INSTEAD OF触发器不能针对表只能针对视图。 5、系统事件触发器 系统事件触发器是指由数据库系统事件触发的触发器支持的系统事件如下 系统事件 说明 LOGOFF 用户从数据库注销 LOGON 用户登录数据库 SERVERERROR 服务器发生错误 SHUTDOWN 关闭数据库实例 STARTUP 打开数据库实例 注对于LOGOFF和SHUTDOWN事件只能创建BEFORE触发器对于LOGON、SERVERERROR和STARTUP事件只能创建AFTER触发器。 创建系统触发器需要使用ON DATABASE子句表示创建的触发器是数据库级触发器。创建系统事件触发器需要用户具有DBA权限。 6、DDL触发器 DDL触发器由DDL语句触发可分为BEFORE触发器和AFTER触发器针对事件包含CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATE STATISTICS和DISASSOCIATE STATISTICS。 创建DDL触发器需要用户具有DBA权限。 7、禁用与启用触发器 创建时使用ENABLE与DISABLE关键字制定触发器初始装填为启用或禁用默认为ENABLE。需要时也可使用ALTER TRIGGER语句修改触发器的状态如下 ALTER TRIGGER trigger_name ENABLE | DISABLE ; 如果修改某个表上所有触发器的状态可用如下形式 ALTER TABLE table_name ENABLE | DISABLE ALL TRIGGERS ; 8、修改与删除触发器 修改删除触发器只需要在 CREATE TRIGGER语句中添加OR REPLACE关键字。 删除触发器需要使用DROP TRIGGER语句具体如下 DROP TRIGGER trigger_name; 包 1、创建包 程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成程序包规范声明类型、变量、常量、异常、游标和子程序等元素条目不包含这些元素的实际代码程序包主体用于实现在程序包规范中定义的游标、子程序包含了元素的实际代码。包规范中的条目为共有项目可供所有的数据库用户访问而包体中创建的规范中没有提到的项目属于私有项目只能在包体中使用。 1、创建包规范 创建包规范使用CREATE PACKAGE语句如下 CREATE [ OR REPLACE ] PACKAGE package_name { IS | AS } package_specification ; END package_name; 说明 package_name创建的包名 package_specification用于列出用户可以使用的公共存储过程、函数、类型和对象。 2、创建包体 创建包体需要使用CREATE PACKAGE BODY语句并且在创建的时候需要指定已创建的包如下 CREATE [ OR REPLACE ] PACKAGE BOODY package_name { IS | AS } package_body ; END package_name ; 2、调用包 调用程序包中的元素时采用如下形式: package_name.[ element_name ] ; element_name表示元素名称可以使存储过程名、函数名、变量名和常量名等。 注程序包中可以定义公有常量和变量使用的DBMS_OUTPUT.PUT_LINE输出结果语句DBMS_OUTPUT是系统定义的包PUT_LINE是该包的存储过程。 3、删除包 使用DEOP PACKAGE语句如果程序包被删除则包体也被自动删除。如下 DROP PACKAGE package_name
