oracle存储过程函数和程序包
oracle函数存储过程教程
declare param1 number:=25; param2 number:=35; begin swap(param1, param2); dbms_output.put_line('param1 = ' || param1); dbms_output.put_line('param2 = ' || param2); end;
名称表示法: 名称表示法:
begin update_students(in_name=>'柳青 in_age=>19); 柳青', 柳青 end;
8.2.7
存储过程的参数——参数的默认值 参数的默认值 存储过程的参数
有时,存储过程的参数有很多个。对于用户来说, 有时,存储过程的参数有很多个。对于用户来说,部 分参数并非必需,那么, 分参数并非必需,那么,在定义存储过程时应该为可选参数 设定默认值,以允许用户不为该参数传值。需要注意的是, 设定默认值,以允许用户不为该参数传值。需要注意的是, 默认值是仅对IN参数而言 参数而言, 默认值是仅对 参数而言,OUT和IN OUT参数没有默认值 和 参数没有默认值 范例8-17演示了如何使用 参数的默认值。 演示了如何使用IN参数的默认值 。范例 演示了如何使用 参数的默认值。
8.1.1 函数简介
1.函数与功能的划分 . 2.函数的参数 . 3.函数的返回值 .
8.1.2
1.创建函数 .
创建函数
create or replace function get_hello_msg return varchar2 as begin return 'hello world'; end get_hello_msg;
Oracle创建存储过程、创建函数、创建包
Oracle创建存储过程、创建函数、创建包⼀、Oracle创建存储过程1、基本语法create or replace procedure update_emp_sal(Name in out type,Name in out type, ...) isbeginend update_emp_sal;2、写⼀个简单的例⼦修改emp表的ename字段create or replace procedure update_emp(v_empno varchar2,v_ename varchar2) isbeginupdate emp set ename=v_ename where empno=v_empno;end update_emp;调⽤⽅法如下:SQL>exec update_emp('7935','test');2、有返回值的存储过程就写⼀个简单的返回empno=7935的sal值create or replace procedure emp_out_sal(v_empno in varchar2,v_sal out number) isvsal number(7,2);beginselect sal into vsal from emp where empno=v_empno;v_sal:=vsal;end;调⽤有返回值的过程SQL>var vsal numberSQL>exec emp_out_sal('7935',:vsal);PL/SQL procedure successfully completedvsal---------700SQL>var vsal numberSQL> call emp_out_sal('7935',:vsal);Method calledvsal---------700⼆、Oracle创建函数(function)1、基本语法规则如下:create or replace function (Name in type, Name in type, ...) return number isResult number;beginreturn (Result);end ;2、写⼀个简单的查询例⼦查询出empno=7935的sal值create or replace function ret_emp_sal(v_ename varchar2)return numberisv_sal number(7,2);beginselect nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);return v_sal;end;调⽤此函数:SQL>var vsla numberSQL> call ret_emp_sal('7935') into :vsal;Method calledvsal---------700三、Oracle创建包包⽤于组合逻辑相关的过程和函数,它由包规范和包体两个部分组成。
Oracle存储过程函数包
运行过程
• 可以在pl/sql块中调用,执行过程 • 可以直接在sql*plus中用execute执行存储过程
• 查询指定员工记录; CREATE OR REPLACE PROCEDURE QueryEmp( v_empno IN emp.empno%TYPE, v_ename OUT emp.ename%TYPE, v_sal OUT emp.sal%TYPE) AS BEGIN SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已经查到!'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生其它错误!'); END QueryEmp;
2
子程序
• 子程序: PL/SQL的过程和函数统称为子程序 • 匿名块: 以DECLARE或BEGIN开始,每次提交都被编 译。匿名块不在数据库中存储并且不能直接从其他 PL/SQL块中调用。 • 命名块:除匿名块之外的其他块。包括过程,函数,包 和触发器。可以在数据库中存储并在适当的时候运行。 • PL/SQL 程序块、过程、函数和数据包中声明
• 计算指定部门的工资总和,并统计其中的职工数量。 CREATE OR REPLACE PROCEDURE proc_demo( Dept_no NUMBER DEFAULT 10, Sal_sum OUT NUMBER, Emp_count OUT NUMBER) IS BEGIN SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生其它错误!'); END proc_demo;
Oracle数据库系统应用开发实用教程电子课件 第8章 存储过程函数程序包与触发器-精选文档
完成了任务7, 请尝试实训7
任务8
任务8.通过触发器在视图中插入数 据。
34
任务8
替代触发器是行级触发器。替代触发器用INSTEAD OF来 规定,它执行一个替代操作来代替对视图的操作,对视图 的操作最终会转换为基本表的操作。 问题:对视图view_emp_dept进行插入数据,结果报错。 这种情况该如何解决?(视图view_emp_dept的数据来 源于emp表的字段empno,ename,job, emp.deptno,条件是emp.deptno=dept.deptno)
③ 远程数据的复制。
语句级触发器
触发器的语句中未使用FOR EACH ROW子句。
语句级触发器与INSERT、DELETE、UPDATE或者组合上 进行关联。
语句触发器都只针对指定语句激活一次。
实训5
① 掌握语句级触发器的原理。 ② 语句级触发器的编写方法。 ③ 测试语句级触发器是否生效。
触发器示例体验
触发器概述
触发器是关系数据库系统提供的一项技术,当特定对 象上特定事件出现时,由系统自动触发执行的代码块。数 据库触发器能够执行的功能包括以下几方面: ① 自动生成派生数据。 ② 实现复杂的数据完整性规则。 ③ 实施更复杂的安全性检查。 ④ 提供审计和日志记录。
⑤ 启用复杂的业务逻辑。
触发器的基本语法规则
CREATE [OR REPLACE] TRIGGER 触发器名 触发时间 触发事件 ON 对象名 [REFERENCING_CLAUSE]
[FOR EACH ROW[WHEN ROW] TRIGGER_CONDITION]] PL/SQL 语句
规则说明
触发时间:指明触发器何时执行,取值有:
Oracle数据库的函数,存储过程,程序包,游标,触发器
Oracle数据库的函数,存储过程,程序包,游标,触发器Oracle⾃定义函数函数的主要特性是它必须返回⼀个值。
创建函数时通过 RETURN ⼦句指定函数返回值的数据类型。
函数的⼀些限制:●函数只能带有 IN 参数,不能带有 IN OUT 或 OUT 参数。
●形式参数必须只使⽤数据库类型,不能使⽤ PL/SQL 类型。
●函数的返回类型必须是数据库类型Create function 函数名称 return 返回值类型 asBegin····End 函数名称;--创建不带参数函数,返回t_book中书的数量create function getBookCount return number asbegindeclare book_count number;beginselect count(*) into book_count from t_book;return book_count;end;end getBookCount;--函数调⽤set serveroutput on;begindbms_output.put_line('表t_book中有'||getBookCount()||'本书');end;--创建带参数函数,查找某个表的记录数create function getTableCount(table_name varchar2) return number asbegindeclare recore_count number;query_sql varchar2(300);--定义sql语句beginquery_sql:='select count(*) from '||table_name;--execute immediate:⽴即执⾏该SQL语句execute immediate query_sql into recore_count;return recore_count;end;end getTableCount;--函数调⽤set serveroutput on;begindbms_output.put_line('表中有'||getTableCount('t_book_log')||'条数据');end;CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)/* 参数、指定返回类型 */RETURN varchar2AS/* 定义局部变量 */min_price NUMBER;max_price NUMBER;BEGINSELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_priceFROM itemfile;IF price >= min_price AND price <= max_price THENRETURN '输⼊的单价介于最低价与最⾼价之间';ELSERETURN '超出范围';END IF;END;匿名块执⾏函数p NUMBER := 300;MSG varchar2(200);BEGINMSG := item_price_range(p);DBMS_OUTPUT.PUT_LINE(MSG);END;SELECT查询调⽤(因为函数必须有返回值)SELECT myfunction FROM dual;Oracle存储过程存储过程(Stored Procedure),就是⼀组⽤于完成特定功能的SQL语句集,该SQL语句集经过编译后存储在数据库中。
在Oracle中使用存储过程和函数
Oracle中使用存储过程和函数摘要:存储过程是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数来执行它。
存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
函数也是存储程序的一种,它建立后存储在数据库服务器中,用户可以直接调用。
熟练地使用存储过程能帮助DBA更好地管理数据库。
关键字:存储过程函数一.为什么要使用存储过程呢?因为课程设计中需要用到存储过程和函数,所以就选择了这个题目,好让自己对这个知识点更加熟悉。
在SQL*Plus中编写并执行PL/SQL块,PL/SQL块的代码存放在SQL*Plus的缓冲区中。
如果在SQL*Plus中执行其它SQL语句或者PL/SQL块,SQL*Plus的缓冲区中就会存放新的内容,原来的内容会被从缓冲区中清除出去。
如果希望PL/SQL块能被随时调用执行,并能与数据库中的其它用户共享,那就需要创建有名字的PL/SQL块,并经过编译与优化,存放在数据库中,这就是存储程序。
具体的优点如下:1.存储过程的能力大大增强了SQL语言的功能和灵活性。
存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
2.可保证数据的安全性和完整性。
3.通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。
4.通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。
5.再运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。
这种已经编译好的过程可极大地改善SQL语句的性能。
由于执行SQL语句的大部分工作已经完成,所以存储过程能以极快的速度执行。
6.可以降低网络的通信量。
7.体现企业规则的运算程序放入数据库服务器中,以便集中控制。
8.当企业规则发生变化时在服务器中改变存储过程即可,无须修改任何应用程序。
企业规则的特点是要经常变化,如果把体现企业规则的运算程序放入应用程序中,则当企业规则发生变化时,就需要修改应用程序工作量非常之大(修改、发行和安装应用程序)。
Oracle(存储过程、存储函数、用程序调用)
Oracle (存储过程、存储函数、⽤程序调⽤)指存储在数据库中的供所有⽤户程序带哦⽤的⼦程序(PL/SQL )叫存储过程(不能返回值)、存储函数(可以通过return 语句返回值)1、存储过程为了完成特定功能的SQL 语句集,经编译后存储在数据库中。
(1)新建:(2)书写存储过程的代码:(3)编译运⾏代码:(4)调⽤存储过程:2、存储函数create or replace procedure raiseSalary(eno in number)ispsal emp.sal %type;beginselect sal into psal from emp where empno=eno;update emp set sal= sal + 100 where empno = eno ;dbms_output.put_line('前:'||psal||'后:'||(psal+100));end raiseSalary;存储函数与存储过程的结构类似,但是必须有⼀个return ⼦句,⽤于返回函数值。
(1)创建⼀个存储函数:(2)书写代码:(3)右键选择test:3、存储过程和存储函数的OUT(1)创建存储过程:(2)书写程序:查询员⼯的信息(3)测试结果:create or replace function queryEmpIncome(eno in number)return numberispsal emp.sal %type;pcomm m %type;beginselect sal,comm into psal,pcomm from emp where empno=eno;return psal*12+nvl(pcomm,0);end queryEmpIncome;create or replace procedure queryEmpInfeno(eno in number,pename out varchar2,psal out number,pjob out varchar2)isbeginselect ename,sal,job into pename,psal,pjob from emp where empno=eno; end queryEmpInfeno;4、java程序调⽤存储过程和存储函数(1)先在虚拟机中找到需要导⼊的jar包并进⾏导⼊:(2)书写⼀个⼯具类:package pers.zhb.utils;import java.sql.*;public class JDBCUtils {private static String driver = "oracle.jdbc.OracleDriver";private static String url = "jdbc:oracle:thin:@192.168.125.129:1521/orcl"; private static String user = "scott";private static String password = "tiger";static{try {Class.forName(driver);} catch (ClassNotFoundException e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection(){try {return DriverManager.getConnection(url, user, password);} catch (SQLException e) {e.printStackTrace();}return null;}public static void release(Connection conn, Statement st, ResultSet rs){ if(rs != null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs = null;}}if(st != null){try {st.close();} catch (SQLException e) {e.printStackTrace();}finally{st = null;}}if(conn != null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}finally{conn = null;}}}}(3)创建测试类,调⽤存储过程和存储函数:public class Test {public void testProcedure(){String sql = "{call raiseSalary(?,?,?,?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.setInt(1,7839);call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); call.registerOutParameter(4, OracleTypes.VARCHAR); call.execute();String name = call.getString(2);double sal = call.getDouble(3);String job = call.getString(4);System.out.println(name+"\t"+sal+"\t"+job);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}public void testFunction(){String sql = "{?=call queryEmpIncome(?)}";Connection conn = null;CallableStatement call = null;try {conn = JDBCUtils.getConnection();call = conn.prepareCall(sql);call.registerOutParameter(1, OracleTypes.NUMBER); call.setInt(2, 7839);call.execute();double income = call.getDouble(1);System.out.println(income);} catch (Exception e) {e.printStackTrace();}finally{JDBCUtils.release(conn, call, null);}}public static void main(String [] args){Test test=new Test();test.testFunction();}}。
oracle存储过程函数和程序包课件
带输入参数的过程3-3
• 分别输入两组数据来执行过程 ,结果如下:
oracle存储过程函数和程序包
带输出参数的过程3-1
• 实现功能:通过输出参数count来得到dept表 中的记录数
• 创建带输出参数的过程
CREATE OR REPLACE PROCEDURE sp_getcount (o_count OUT NUMBER) AS BEGIN
带IN OUT参数的过程2-2
• 执行过程:
DECLARE l_iotest varchar2(20):='COUNTING';
BEGIN sp_dept_dname_exist(l_iotest); DBMS_OUTPUT.PUT_LINE('部门名称ACCOUNTING'||l_iotest||'!');
存在 5. 使用函数查询部门信息 6. 使用程序包封装过程和函数
oracle存储过程函数和程序包
相关实践知识
• 从开始菜单中打开SQL*Plus工具,以SCOTT 用户的身份登录到数据库
oracle存储过程函数和程序包
不带参数的过程2-1
l 输入以下代码,创建一个最简单的过程 l 功能: 显示”Hello World!”
在 法 范中不包含RETURN子句
在可 行 句部分可以有RETURN 句,但其后不能加任何表达式
在 法 范中必 包含RETURN子 句
在可 行 句部分至少 包含一 条RETURN expression 句
可以用EXECUTE 句来 行
不能用EXECUTE 句来 行
oracle存储过程函数和程序包
oracle存储过程函数和程序包
Oracle数据库_入门教程(二)_子程序(存储过程和函数)
------------------
实际例子:
(1)创建程序包规范:
CREATE OR REPLACE PACKAGE wyd_package
IS
PROCEDURE wyd_print(name varchar2);
(3)函数返回类型也必须是数据库类型。
--------------
创建一个函数:
create or replace function fun_sum(a number,b number)
return number
IS
BEGIN
return a+b;
END;
--------------
语法说明:
procedure_name 是过程名字
parameter_list 是参数列表
local_declarations 是局部声明
executable_statements 是可执行语句,
execption_handlers是 异常处理程序
-------
create or replace procedure wyd_print(times number)
[subprogram specifications]
END [package_name];
/
------------
(2) 创建 程序包主体。(也可以说成是‘实现程序包’)
基本语法:
CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
----------------------------------------------------------------
Oracle中的函数、存储过程、包
Oracle中的函数、存储过程、包1.函数①系统函数②自定义函数③使用函数④编写过程⑤过程的调用例子:一个数的两倍CREATE OR REPLACE FUNCTION f_get_double(p1 NUMBER)RETURN NUMBERISv1 NUMBER;BEGINv1:=p1*2;RETURN v1;END;调用:SELECT scott.f_get_double(100) FROM dual;SELECT ename,sal,f_get_double(sal) AS sal2 FROM emp;2.存储过程例子:删除指定编号的员工信息,如果员工所在部门的人数少于三个就不能删除。
CREATE OR REPLACE PROCEDURE sp_del(v_empno emp.empno%TYPE)ASv_count NUMBER;BEGINDELETE FROM emp WHERE empno=v_empno;SELECT COUNT(*) INTO v_count FROM empWHERE deptno=(SELECT deptno FROM emp WHERE empno=deptno);IF(v_count<3)THENROLLBACK;raise_application_error(-20045,'部门人数太少!');END IF;dbms_output.put_line('删除了'||SQL%ROWCOUNT||'行');COMMIT;END;3.函数与存储过程的区别:①函数必须有返回值②函数不能修改数据③都有in out (in out)三个参数4.常见函数举例①Ltrim与RtrimSELECT RTrim('afesafdafeg','eg') FROM dual; --这里就是把右边的eg截断SELECT LTrim('afesafdafeg','af') FROM dual; --这里就是把左边的af截断②LPad与RPadSELECT LPad('abcdefgtyi', 12, 'YY') FROM dual; --在列的左边粘贴字符,12表示在'abcdefgtyi'出现的索引值,不够就补YY上面的结果为:YYabcdefgtyiSELECT RPad('abcdefgyui', 12, 'YY') FROM dual; --同上结果为:abcdefgyuiYY5.包的使用(可以看成是JAVA里的接口)--先写包里定义函数,不写它的实现CREATE OR REPLACE PACKAGE my_fun ISFUNCTION f_get_double(a NUMBER) RETURN NUMBER; END;--包体CREATE OR REPLACE PACKAGE BODY my_fun IS FUNCTION f_get_double(a NUMBER) RETURN NUMBER ASBEGINRETURN a*2;END;END;6.什么时候函数与存储过程①不修改数据,只完成计算。
推荐下载-oracle过程、函数和程序包 精品
CREATE [OR REPLACE] PROCEDURE procedure_name(argument1[mode1] datatype1,argument2[mode2] datatype2,…)
{IS|AS}
PL/SQL Block; 其中procedure_name指定过程名,argument指定过程
PL/SQL过程-使用异常处理
CREATE OR REPLACE PROCEDURE update_sal( name empl.ename%TYPE, sal empl.salary%TYPE)
IS e_no_rows EXCEPTION; BEGIN
UPDATE empl SET salary=sal WHERE LOWER(ename)=LOWER(name); IF SQL%NOTFOUND THEN RAISE e_no_rows; END IF; EXCEPTION WHEN e_no_rows THEN DBMS_OUTPUT.PUT_LINE(‘这雇员不存在’); END; Exec update_sal(‘jarbus’,1500) 这雇员不存在 Exec update_sal(‘allen’,1500)
当定义输入输出参数时,需要指定参数模式为 IN OUT。
CREATE OR REPLACE PROCEDURE devide( num1 IN OUT NUMBER,num2 IN OUT NUMBER)
IS v1 NUMBER; v2 NUMBER;
BEGIN v1:=TRUNC(num1/num2); v2:=MOD(num1,num2); num1:=v1; num2:=v2;
IS BEGIN
ORACLE存储过程,函数,包,游标
ORACLE存储过程,函数,包,游标1、 PL/SQL语句块PL/SQL语句块只适⽤于Oracle数据库,使⽤时临时保存在客户端,⽽不是保存在数据库。
基本语法:Sql代码1. declare2. 变量声明、初始化3. begin4. 业务处理、逻辑代码5. exception6. 异常捕获7. end;变量声明:<变量名> <类型及长度> [:=<初始值>]例:v_name varchar2(20):=’张三’;2、循环语句loop循环语法:Sql代码1. loop2. exit when 表达式3. end loop;while循环语法:Sql代码1. while 表达式2. loop3. end loop;for循环语法:Sql代码1. for <变量> in <变量取值范围(⼩值..⼤值,如1..100)> loop2. end loop;for循环的变量可不做声明及初始化。
3、 if判断语句基本语法:Sql代码1. if <表达式> then2. …3. else if <表达式> then4. …5. else6. …7. end if;8. end if;例:Sql代码1. declare2. v_identity number(4):=0;3. begin4. loop5. if v_identity=1 then6. dbms_output.put_line('v_identity=1');7. else if v_identity=3 then8. dbms_output.put_line('v_identity=3');9. else if v_identity=6 then10. exit;11. else12. dbms_output.put_line('v_identity is not 1 or 3');13. end if;14. end if;15. end if; -- 注意,有多少个if就要有多少个end if结束标志。
oracle存储过程和函数.docx
Oracle存储过程和函数一、存储过程过程:将一些内部联系的命令组成一个个过程,通过参数在过程Z间传递数据。
存储过程的特点:1、存储过程里的代码都己经被编译过。
可以直接执行。
使用吋无需编译,提高工作效率2、客户端通过调用存储过程,可以减少网络流量,加快了系统执行速度。
3、可以减少SQL注入,提高系统的安全性。
4、在同时进行主、从表及多表间的数据维护及有效性验证时,使用存储过程比较方便,而且可以有效利用SQL中的事务处理的机制;5、使用存储过程,可以实现存储过程设计和编码工作分开进行存储过程包含1、声明部分:在声明部分可以对不同数据类型的数据进行声明,包括类型、游标、常量、变量、界常等。
2、执行部分:主要是为了完成或者达到一个特定的1=1的或功能3、异常处理部分:在执行过程中,对于引发错误的操作,进行异常捉示。
Oracle中创建存储过程的语法:Create or replace procedure 存储过程名(参数1 in type,参数2 out type)As声明语句Begin执行语句Exception异常处理语句End;注意:1、存储过程参数不带取值范围,in表示传入,out表示输出2、as后的语句声明变量,并且变量有取值范围,后面加上分号3、begin关键字表示PL/SQL的开始4、exception关键字用来处理异常,异常信息常用raise +异常名的方式5、end关键字表示存储过程的结束预定义异常的简单描述asuname varchar(20);beginselect name into uname from test where id = 23;dbms output.put line(uname);带输入参数存储过程create or replace procedure test_proc(uid in number) asuname varcr'iar (20);beginselect name into uname from test where id = uid; dbms cmtput.put line(uname);exceptionwhen NO DATA FOUND thenRAISE APPLICATION ERROR(-20011, 'ERROR:不存在! *);end;带输出参数存储过程create or replace procedure test_proc(num out number) asbeginselect count(*) into num from test where id = 25; dbms output•put line(num); exceptionwhen NO DATA FOUND thenRAISE APPLICATION ERROR(-20011, 'ERROR:不存在!');end;带输出和输出参数存储过程create or replace procedure test_proc (uio umber z num out number)3 Suname varchar(20);beginselect name into uname from test where id = uid;select count (*) into num from test where id = 25;dbms_output.put_line (uname);dbms output.put line (num);when NO DATA FOUND t-ienRAISE APPLICATION ERROR (-20011, •ERROR:不存在!1 );存储过程的调用通过Call存储过程名称(参数);Begin存储过程名称(参数);End;二、函数Oracle屮的函数与存储过程类似,也是将一组能够实现特定功能的SQL或者PL/SQL 语句块组合在一起的程序集,并且能够将执行结果返冋。
Oracle的函数和存储过程
Oracle的函数和存储过程【】主键就是区别这个表的唯一关键字比如一个学生表学号不能重复且唯一学号就是关键字(此时学号就可以作为主键)【】外键就是跟其他表联系的字段还是比如有一张学生表还有一张选课表这个时候要修改学生表中的学号选课表里对应的就也得变这样就需要给选课表加学号作为外键约束这样当你修改学号时所有外键关联的就都改了【】视图(VIEW)也被称作虚表,即虚拟的表,是一组数据的逻辑表示,其本质是对应于一条SELECT语句,结果集被赋予一个名字,即视图名字。
视图本身并不包含任何数据,它只包含映射到基表的一个查询语句,当基表数据发生变化,视图数据也随之变化。
【】在Java程序中设置事务处理setAutoCommit(false);不让其自动提交。
【函数:】①字符函数:lower(char):将字符串转化为小写的格式upper(char):将字符串转化为大写的格式length(char):返回字符串的长度substr(char,m,n):取字符串的子串写sql语句1分析清晰思路【replace函数】replace(char1,search_string,replace,)例子:select replace(ename,'A','我是老鼠')from emp;----->含义:把字段ename中凡是‘A’用'我是老鼠'替代。
【日期函数】sysdate 返回系统当前时间add_months 超过了指定月份的日期to_char 把其他的转换成字符串的函数【数据库的导入和导出】以.dmp的格式导出数据库到磁盘exp call_out/call_out@orcl owner=call_out file=d:\call_out.dmp导入数据库:(导入自己的表)imp userid=call_out/call_out@orcl tables=(emp) file=d:\xx.dmp【表空间】表空间是数据库的逻辑组成部分,数据库数据存放在数据文件中,表空间是由一个或多个数据文件组成的、【索引】可显著提高数据库的查询速度,比如char定长可以显著提高查询速度。
Oracle常用包、函数、存储过程
Oracle常用包、函数、存储过程Oracle常用包、存储过程、函数常用包、存储过程1 dbms_output作用:输入和输出信息,使用过程PUT和PUT_LINES可以将信息发送到缓冲区,使用过程GET_LINE和GET_LINES可以显示缓冲区信息。
1.1 enable该过程用于激活对过程PUT,PUT_LINE,GET_LINE,GET_LINES的调用。
语法如下:dbms_output.enable(buffer_size in integer default 20000);1.2 disable该过程用于禁止对过程PUT,PUT_LINE,GET_LINE,GET_LINES的调用。
语法如下:dbms_output.disable;1.3 put和put_line过程put_line用于将一个完整行的信息写入到缓冲区中,过程put则用地分块建立行信息,当使用过程put_line时,会自动在行的尾部追加行结束符;当使用过程put时,需要使用过程new_line追加行结束符。
示例如下:set serverout onbegindbms_output.put_line('伟大的中华民族');dbms_output.put('中国');dbms_output.put(',伟大的祖国');dbms_output.new_line;end;/伟大的中华民族中国,伟大的祖国1.4 new_line该过程用于在行的尾部追加行结束符。
当使用过程PUT时,必须调用NEW_LINE过程来结束行。
1.5 get_line和get_lines过程get_line用于取得缓冲区的单行信息,过程get_lines用于取得缓冲区的多行信息。
2 dbms_job作用:安排和管理作业队列,通过使用作业,可以使ORACLE数据库定期执行特定的任务。
2.1 submit用于建立一个新作业。
Oracle数据库管理应用 存储过程、函数和包
参数类型 IN OUT IN OUT
说明 定义一个输入参数变量,用于传递参数给存储过程 定义一个输出参数变量,用于从存储过程获取数据 定义一个输入、输出参数变量,兼有以上两者的功能
共一百零七页
第8章 存储过程、函数和包
参数的定义形式和作用如下: 参数名 IN 数据类型 DEFAULT 值; 定义一个输入参数变量,用于传递参数给存储(cún chǔ)过程。 在调用存储(cún chǔ)过程时,主程序的实际参数可以是常量、有 值变量或表达式等。DEFAULT 关键字为可选项,用来设定参 数的默认值。如果在调用存储(cún chǔ)过程时不指明参数,则参 数变量取默认值。在存储(cún chǔ)过程中,输入变量接收主程序 传递的值,但不能对其进行赋值。 参数名 OUT 数据类型; 定义一个输出参数变量,用于从存储过程获取数据,即变 量从存储过程中返回值给主程序。
共一百零七页
第8章 存储过程、函数和包 【训练3】 编写(biānxiě)显示雇员信息的存储过程
EMP_LIST,并引用EMP_COUNT存储过程。 步骤1:在SQL*Plus输入区中输入并编译以下存储
过程: CREATE OR REPLACE PROCEDURE EMP_LIST AS CURSOR emp_cursor IS SELECT empno,ename FROM emp; BEGIN
存储过程和函数需要进行编译,以排除语法错误, 只有编译通过才能调用。
共一百零七页
第8章 存储过程、函数和包
8.1.2 创建(chuàngjiàn)和删除存储过程 创建存储过程,需要有CREATE PROCEDURE或
CREATE ANY PROCEDURE的系统权限。该权限可 由系统管理员授予。创建一个存储过程的基本语句如 下:
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
带IN OUT参数的过程2-2
l 执行过程:
DECLARE l_iotest varchar2(20):='ACCOUNTING';
BEGIN sp_dept_dname_exist(l_iotest); DBMS_OUTPUT.PUT_LINE('部门名称ACCOUNTING'||l_iotest||'!');
l_count NUMBER;
lBES创GELIN建ECT带COINUNOT(*U) INTT参O l_数co的unt过FR程OM,dep代t W码HE为RE:
dname=io_value; IF(l_count>0) THEN
io_value:='存在'; ELSE
io_value:='不存在'; END IF; END sp_dept_dname_exist;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
l 输出结果:
带输出参数的过程3-3
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
带IN OUT参数的过程2-1
lC(ioR实_EvAa现TluEe功OINRO能RUETP:查LVAACR询ECHP某ARRO个2C)E部DU门RE名sp_称dep在t_d表namdee_pextis中t 是否 IS 已经存在
oracle存储过程函数和 程序包
学习改变命运,知 识创造未来
2021年2月17日星期三
回顾
l 游标就是指向上下文区的句柄或指针。 l 游标有两种类型:显式游标、隐式游标。 l 四个游标属性 :SQL%FOUND、SQL%
NOTFOUND、SQL%ROWCOUNT、SQL% ISOPEN l 显式游标的使用步骤 :4个 l 记录变量和%ROWTYPE l 带参数的游标 l 游标FOR循环(循环游标) l 游标中的更新和删除 l REF游标
小结:程序包的优点
l 使用程序包的优点:
Ø 信息隐藏 Ø 模块化 Ø 对多态的支持 Ø 性能更佳
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
过程返回结果集2-1
l 在Oracle中的过程不能象SQL SERVER那样 直接返回结果集,而必须借助于REF游标
l 程序包规范中的代码:
l (i_deptno NUMBER)
l RETURN VARCHAR2
l AS
l l_dname VARCHAR2(14);
l BEGIN
l SELECT dname INTO l_dname FROM dept WHERE deptno=i_deptno;
l RETURN l_dname;
l EXCEPTION
l WHEN NO_DATA_FOUND THEN
l
RETURN '错误!该编号的部门不存在!';
l END f_dept_getname_byno;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
l 函数的调用及其输出结果 :
函数4-3
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
END;
l 输出结果: l 部门名称ACCOUNTING存在!
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
函数4-1
l 实现功能: 按部门编号查询出表dept中的部门名称
l 创建一个函数,代码为:
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
函数4-2
l CREATE OR REPLACE FUNCTION f_dept_getname_byno
在 5. 使用函数查询部门信息 6. 使用程序包封装过程和函数
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
相关实践知识
l 从开始菜单中打开SQL*Plus工具,以SCOTT 用户的身份登录到数据库
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
不带参数的过程2-1
输入以下代码,创建一个最简单的过程 功能: 显示”Hello World!”
CREATE OR REPLACE PROCEDURE sp_helloWorld AS BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!'); END sp_helloWorld;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
不带参数的过程2-2
l 执行过程 l EXECUTE sp_helloWorld;
句,但其后不能加任何表达式
RETURN expression语句
可以用EXECUTE语句来执行
不能用EXECUTE语句来执行
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
程序包5-1
l 利用程序包封装过程sp_dept_insert和函数 f_dept_getname_byno
l 程序包规范部分的代码:
l 执行过程:
VARIABLE test_cur REFCURSOR; EXECUTE pkg_dept.sp_dept_getall(:test_cur); PRINT test_cur;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
OEM中管理过程、函数、程序包
l 请老师用浏览器打开OEM,演示在OEM中管
CREATE OR REPLACE PACKAGE pkg_dept AS
PROCEDURE sp_dept_insert (i_deptno NUMBER,i_dname VARCHAR2,i_loc VARCHAR2); FUNCTION f_dept_getname_byno(i_deptno NUMBER) RETURN VARCHAR2; END pkg_dept;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
教学目标
l 掌握过程的用法 l 掌握函数的用法 l 理解过程与函数的相同点和不同点 l 理解程序包的概念并能熟练应用
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
工作任务
1. 用无参过程实现“Hello World!”程序 2. 用带输入参数的过程向表中插入记录 3. 用带输出参数的过程查询表中的记录数 4. 使用带输入输出参数的过程查询记录是否存
RETURN l_dname; EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN '错误!该编号的部门不存在!';
END f_dept_getname_byno; END pkg_dept;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
程序包5-4
l 执行程序包中的过程和函数:
函数4-4
l 删除函数 DROP FUNCTION f_dept_getname_byno
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
过程与函数小结2-1
l 共同点:两者的实质都是已命名的PL/SQL程 序块,即子程序,它们是子程序的两种类型, 存储在数据库中,可以从任何数据库客户端和 前台应用程序中调用它们。
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
程序包5-3
--函数f_num_range FUNCTION f_dept_getname_byno(i_deptno NUMBER) RETURN VARCHAR2 AS
l_dname VARCHAR2(14); BEGIN
SELECT dname INTO l_dname FROM dept WHERE deptno=i_deptno;
AS
BEGIN
INSERT INTO dept VALUES(i_deptno,i_dname,i_loc);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('添加失败!原因为:'||SQLERRM);
ROLLBACK;
END sp_dept_insert;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
l 删除程序包:
程序包5-5
l 只删除程序包主体: DROP PACKAGE BODY pkg_dept;
l 删除整个程序包(规范+主体): DROP PACKAGE pkg_dept;
学习改变命运,知 识创造未来
oracle存储过程函数和程序包
oracle存储过程函数和程序包
过程返回结果集2-2
l 程序包主体中的代码:
CREATE OR REPLACE PACKAGE BODY pkg_dept AS
PROCEDURE sp_dept_getall(dept_cur OUT deptcursor) IS BEGIN OPEN dept_cur FOR SELECT * FROM dept; END sp_dept_getall; END pkg_dept;
l 执行结果:
l 删除过程 l DROP PROCEDURE sp_helloWorld;
学习改变命运,知 识创造未来
3-1
l 实现的功能:向表dept中插入一条记录 l 创建带输入参数的过程,代码为:
学习改变命运,知 识创造未来
oracle存储过程函数和程序包