Oracle存储过程与笔记.pdf

《Oracle存储过程与笔记.pdf》由会员分享，可在线阅读，更多相关《Oracle存储过程与笔记.pdf（29页珍藏版）》请在知学网上搜索。

1、 Oracle 存储过程存储过程 目录目录 Oracle 存储过程 .1Oracle存储过程基础知识 .1Oracle存储过程的基本语法 .2关于Oracle存储过程的若干问题备忘 .41.在Oracle中，数据表别名不能加as。.42.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。.53.在利用select.into.语法时，必须先确保数据库中有该条记录，否则会报出no data found异常。.54.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错.55.在存储过程中，关于出现null的问题

2、.56.Hibernate调用Oracle存储过程 .6用Java调用Oracle存储过程总结 .6一、无返回值的存储过程 .6二、有返回值的存储过程（非列表）.8三、返回列表 .9在存储过程中做简单动态查询 .11一、本地动态SQL .12二、使用DBMS_SQL包 .13Oracle存储过程调用Java方法 .16Oracle高效分页存储过程实例 .17 Oracle 存储过程基础知识存储过程基础知识 商业规则和业务逻辑可以通过程序存储在 Oracle 中，这个程序就是存储过程存储过程。存储过程是 SQL,PL/SQL,Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动

3、到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用代码存储一次但是能够被多个程序使用。要创建一个过程对象（procedural object），必 须 有 CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户 schema 使用，那么你必须有 CREATE ANY PROCEDURE 权限。执行 procedure 的时候，可能需要 excute 权限。或者 EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示：grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子：execute MY_P

4、ROCEDURE(ONE PARAMETER);存储过程存储过程(PROCEDURE)和和函数函数(FUNCTION)的区别。的区别。function 有返回值，并且可以直接在 Query 中引用 function 和或者使用 function 的返回值。本质上没有区别,都是 PL/SQL 程序,都可以有返回值。最根本的区别是：存储过程是命存储过程是命令令,而函数是表达式的而函数是表达式的一部分一部分。比如：select max(NAME)FROM 但是不能 exec max(NAME)如果此时 max 是函数。PACKAGE 是 function，procedure，variables 和

5、sql 语句的组合。package 允许多个procedure 使用同一个变量和游标。创建创建 procedure 的语法的语法：CREATE OR REPLACE PROCEDURE schema.procedure (argument IN|OUT|IN OUT NO COPY datatype ,argument IN|OUT|IN OUT NO COPY datatype.)authid current_user|definer is|as pl/sql_subprogram_body|language java name String|c name,name library lib_

6、name Sql 代码：CREATE PROCEDURE sam.credit(acc_no IN NUMBER,amount IN NUMBER)AS BEGIN UPDATE accounts SET balance=balance+amount WHERE account_id=acc_no;END;可以使用 create or replace procedure 语句，这个语句的用处在于，你之前赋予的excute权限都将被保留。IN,OUT,IN OUT 用来修饰参数。用来修饰参数。IN 表示这个变量必须被调用者赋值然后传入到 PROCEDURE 进行处理。OUT 表示 PRCEDUR

7、E 通过这个变量将值传回给调用者。IN OUT 则是这两种的组合。authid 代表两种权限：代表两种权限：定义者权限（difiner right 默认），执行者权限（invoker right）。定义者权限说明这个 procedure 中涉及的表，视图等对象所需要的权限只要定义者拥有权限的话就可以访问。执行者权限则需要调用这个 procedure 的用户拥有相关表和对象的权限。Oracle 存储过程的基本语法存储过程的基本语法 1.基本结构基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字(参数 1 IN NUMBER,参数 2 IN NUMBER)AS 变量 1

8、 INTEGER:=0;变量 2 DATE;BEGIN END 存储过程名字 2.SELECT INTO STATEMENT 将 select 查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出 NO_DATA_FOUND)例子：BEGIN SELECT col1,col2 into 变量 1,变量 2 FROM typestruct where xxx;EXCEPTION WHEN NO_DATA_FOUND THEN xxxx;END;.3.IF 判断判断 IF V_TEST=1 THEN BEGIN do something END;

9、END IF;4.while 循环循环 WHILE V_TEST=1 LOOP BEGIN XXXX END;END LOOP;5.变量赋值变量赋值 V_TEST:=123;6.用用 for in 使用使用 cursor.IS CURSOR cur IS SELECT*FROM xxx;BEGIN FOR cur_result in cur LOOP BEGIN V_SUM:=cur_result.列名 1+cur_result.列名 2 END;END LOOP;END;7.带参数的带参数的 cursor CURSOR C_USER(C_ID NUMBER)IS SELECT NAME FR

10、OM USER WHERE TYPEID=C_ID;OPEN C_USER(变量值);LOOP FETCH C_USER INTO V_NAME;EXIT FETCH C_USER%NOTFOUND;do something END LOOP;CLOSE C_USER;8.用用 pl/sql developer debug 连接数据库后建立一个 Test WINDOW 在窗口输入调用 SP 的代码,F9 开始 debug,CTRL+N 单步调试 9.Pl/Sql 中执行存储过程中执行存储过程 在 sql*plus 中：declare -必要的变量声明，视你的过程而定 begin execute

11、 yourprocudure(parameter1,parameter2,.);end /在SQL/PLUS中调用存储过程，显示结果：SQLset serveoutput on -打开输出 SQLvar info1 number;-输出1 SQLvar info2 number;-输出2 SQLdeclare var1 varchar2(20);-输入1 var2 varchar2(20);-输入2 var3 varchar2(20);-输入2 BEGIN pro(var1,var2,var3,:info1,:info2);END;/SQLprint info1;SQLprint info2;

12、注：在 EXECUTE IMMEDIATE STR 语句是 SQLPLUS 中动态执行语句，它在执行中会自动提交，类似于 DP 中 FORMS_DDL 语句，在此语句中 str 是不能换行的,只能通过连接字符|，或着在在换行时加上-连接字符。关于关于 Oracle 存储过程的若干问题备忘存储过程的若干问题备忘 1.在在 Oracle 中，数据表别名不能加中，数据表别名不能加 as。如：select a.appname from appinfo a;-正确 select a.appname from appinfo as a;-错误 也许，是怕和 Oracle 中的存储过程中的关键字 as 冲突

13、的问题吧 2.在存储过程中，在存储过程中，select 某一字段时，后面必须紧跟某一字段时，后面必须紧跟 into，如果如果 select 整个记录，利用游标的话就另当别论了。整个记录，利用游标的话就另当别论了。select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-有 into，正确编译 select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-没 有int

14、o，编 译 报 错，提 示：Compilation Error:PLS-00428:an INTO clause is expected in this SELECT statement 3.在利用在利用 select.into.语法时，必须先确保数据库中有该条语法时，必须先确保数据库中有该条记录，否则会报出记录，否则会报出no data found异常。异常。可以在该语法之前，先利用 select count(*)from 查看数据库中是否存在该记录，如果存在，再利用 select.into.4.在存储过程中，别名不能和字段名称相同，否则虽然编译在存储过程中，别名不能和字段名称相同，否则虽然

15、编译可以通过，但在运行阶段会报错可以通过，但在运行阶段会报错 select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-正确运行 select af.keynode into kn from APPFOUNDATION af where af.appid=appid and af.foundationid=foundationid;-运行阶段报错，提示：ORA-01422:exact fetch returns more than requested number of rows 5.在存储过

16、程中，关于出现在存储过程中，关于出现 null 的问题的问题 假设有一个表 A，定义如下：create table A(id varchar2(50)primary key not null,vcount number(8)not null,bid varchar2(50)not null-外键 );如果在存储过程中，使用如下语句：select sum(vcount)into fcount from A where bid=xxxxxx;如果 A表中不存在 bid=xxxxxx的记录，则 fcount=null(即使 fcount 定义时设置了默认值，如：fcount number(8):=0

17、 依然无效，fcount 还是会变成 null)，这样以后使用 fcount 时就可能有问题，所以在这里最好先判断一下：if fcount is null then fcount:=0;end if;这样就一切 ok 了。6.Hibernate 调用调用 Oracle 存储过程存储过程 this.pnumberManager.getHibernateTemplate().execute(new HibernateCallback().public Object doInHibernate(Session session)throws HibernateException,SQLExceptio

18、n.CallableStatement cs=session .connection().prepareCall(call modifyapppnumber_remain(?);cs.setString(1,foundationid);cs.execute();return null;);用用 Java 调用调用 Oracle 存储过程总结存储过程总结 一、一、无返回值的存储过程无返回值的存储过程 测试表:-Create table create table TESTTB(ID VARCHAR2(30),NAME VARCHAR2(30)tablespace BOM pctfree 10 in

19、itrans 1 maxtrans 255 storage (initial 64K minextents 1 maxextents unlimited );例:存储过程为(当然了，这就先要求要建张表 TESTTB,里面两个字段（I_ID，I_NAME）。)：CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)AS BEGIN INSERT INTO BOM.TESTTB(ID,NAME)VALUES(PARA1,PARA2);END TESTA;在 Java 里调用时就用下面的代码：package com

20、.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestProcedureDemo1 public TestProcedureDemo1()public static void main(String args)String dri

21、ver=Oracle.jdbc.driver.OracleDriver;String strUrl=jdbc:Oracle:thin:10.20.30.30:1521:vasms;Statement stmt=null;ResultSet rs=null;Connection conn=null;CallableStatement proc=null;try Class.forName(driver);conn=DriverManager.getConnection(strUrl,bom,bom);proc=conn.prepareCall(call BOM.TESTA(?,?);proc.s

22、etString(1,100);proc.setString(2,TestOne);proc.execute();catch(SQLException ex2)ex2.printStackTrace();catch(Exception ex2)ex2.printStackTrace();finally try if(rs!=null)rs.close();if(stmt!=null)stmt.close();if(conn!=null)conn.close();catch(SQLException ex1)二、二、有返回值的存储过程（非列表）有返回值的存储过程（非列表）例：存储过程为：CREA

23、TE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2)AS BEGIN SELECT NAME INTO PARA2 FROM TESTTB WHERE ID=PARA1;END TESTB;在 Java 里调用时就用下面的代码：package com.yiming.procedure.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Resu

24、ltSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;public class TestProcedureDemo2 public static void main(String args)String driver=Oracle.jdbc.driver.OracleDriver;String strUrl=jdbc:Oracle:thin:10.20.30.30:1521:vasms;Statement stmt=null;ResultSet rs=null;Connection

25、conn=null;CallableStatement proc=null;try Class.forName(driver);conn=DriverManager.getConnection(strUrl,bom,bom);proc=conn.prepareCall(call BOM.TESTB(?,?);proc.setString(1,100);proc.registerOutParameter(2,Types.VARCHAR);proc.execute();String testPrint=proc.getString(2);System.out.println(=testPrint=

26、is=+testPrint);catch(SQLException ex2)ex2.printStackTrace();catch(Exception ex2)ex2.printStackTrace();finally try if(rs!=null)rs.close();if(stmt!=null)stmt.close();if(conn!=null)conn.close();catch(SQLException ex1)注意，这里的 proc.getString(2)中的数值 2 并非任意的，而是和存储过程中的 out 列对应的，如果 out 是在第一个位置，那就是 proc.getStr

27、ing(1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个 out 参数了。三、三、返回列表返回列表 由于由于 Oracle 存储过程没有返回值，它的所有返回值都是通过存储过程没有返回值，它的所有返回值都是通过 out 参数来替代的参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用 pagkage 了.所以要分两部分，1.建一个程序包建一个程序包。如下：CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE TEST_CURSOR IS REF CURSOR;end TEST

28、PACKAGE;2.建立存储过程建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC(P_CURSOR out TESTPACKAGE.TEST_CURSOR)IS BEGIN OPEN P_CURSOR FOR SELECT*FROM BOM.TESTTB;END TESTC;可以看到，它是把游标（可以理解为一个指针），作为一个 out 参数来返回值的。在 Java 里调用时就用下面的代码：在这里要注意，在执行前一定要先把 Oracle 的驱动包放到 class 路径里，否则会报错的。package com.yiming.procedure.test;

29、import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestProcedureDemo3 public static void main(String args)String driver=Oracle.jdbc.driver.OracleDriver;String strUrl=

30、jdbc:Oracle:thin:10.20.30.30:1521:vasms;Statement stmt=null;ResultSet rs=null;Connection conn=null;CallableStatement proc=null;try Class.forName(driver);conn=DriverManager.getConnection(strUrl,bom,bom);proc=conn.prepareCall(call bom.testc(?);proc.registerOutParameter(1,Oracle.jdbc.OracleTypes.CURSOR

31、);proc.execute();rs=(ResultSet)proc.getObject(1);while(rs.next()System.out.println(+rs.getString(1)+rs.getString(2)+);catch(SQLException ex2)ex2.printStackTrace();catch(Exception ex2)ex2.printStackTrace();finally try if(rs!=null)rs.close();if(stmt!=null)stmt.close();if(conn!=null)conn.close();catch(

32、SQLException ex1)在存储过程中做简单动态查询在存储过程中做简单动态查询 在存储过程中做简单动态查询代码,例如:CREATE OR REPLACE procedure ZXM_SB_GZ_GET (p_table in varchar2,p_name in varchar2,p_value in varchar2,outpara out lntxdba.zxm_pag_cs_power.c_type )as begin declare wherevalue varchar2(200);begin wherevalue:=select*from|p_table|where|p_na

33、me|=|p_value;open outpara for wherevalue;end;end;一般的 PL/SQL 程序设计中，在 DML 和事务控制的语句中可以直接使用 SQL，但是 DDL语句及系统控制语句却不能在 PL/SQL 中直接使用，要想实现在 PL/SQL 中使用 DDL 语句及系 统控制语句，可以通过使用动态 SQL 来实现。首先我们应该了解什么是动态 SQL，在 Oracle 数据库开发 PL/SQL 块中我们使用的 SQL分为：静态 SQL 语句和动态 SQL 语句。所谓静态 SQL 指在 PL/SQL 块中使用的 SQL 语句在编译时是明确的，执行的是确定对象。而动态

34、 SQL 是指在 PL/SQL 块编译时 SQL 语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。Oracle 中动态 SQL 可以通过本地动态 SQL 来执行，也可以通过 DBMS_SQL 包来执行。下面就这两种情况分别进行说明：一、一、本地动态本地动态 SQL 本地动态 SQL 是使用 EXECUTE IMMEDIATE 语句来实现的。1、本地动态 SQL 执行 DDL 语句：需求：根据用户输入的表名及字段名等参数动态建表。create or replace procedure p

35、roc_test(table_name in varchar2,-表名 field1 in varchar2,-字段名 datatype1 in varchar2,-字段类型 field2 in varchar2,-字段名 datatype2 in varchar2 -字段类型)as str_sql varchar2(500);begin str_sql:=create table|table_name|(|field1|datatype1|,|field2|datatype2|);execute immediate str_sql;-动态执行 DDL 语句 exception when ot

36、hers then null;end;以上是编译通过的存储过程代码。下面执行存储过程动态建表。SQL execute proc_test(dinya_test,id,number(8)not null,name,varchar2(100);PL/SQL procedure successfully completed SQL desc dinya_test;Name Type Nullable Default Comments -ID NUMBER(8)NAME VARCHAR2(100)Y SQL 到这里，就实现了我们的需求，使用本地动态 SQL 根据用户输入的表名及字段名、字段类型等参数来

37、实现动态执行 DDL 语句。2、本地动态 SQL 执行 DML 语句。需求：将用户输入的值插入到上例中建好的 dinya_test 表中。create or replace procedure proc_insert(id in number,-输入序号 name in varchar2 -输入姓名)as str_sql varchar2(500);begin str_sql:=insert into dinya_test values(:1,:2);execute immediate str_sql using id,name;-动态执行插入操作 exception when others

38、then null;end;执行存储过程，插入数据到测试表中。SQL execute proc_insert(1,dinya);PL/SQL procedure successfully completed SQL select*from dinya_test;ID NAME 1 dinya 在上例中，本地动态 SQL 执行 DML 语句时使用了 using 子句，按顺序将输入的值绑定到变量，如果需要输出参数，可以在执行动态 SQL 的时候，使用 RETURNING INTO 子句，如：declare p_id number:=1;v_count number;begin v_string:=

39、select count(*)from table_name a where a.id=:id;execute immediate v_string into v_count using p_id;end;二、二、使用使用 DBMS_SQL 包包 使用 DBMS_SQL 包实现动态 SQL 的步骤如下：A、先将要执行的 SQL 语句或一个语句块放到一个字符串变量中。B、使用 DBMS_SQL 包的 parse 过程来分析该字符串。C、使用 DBMS_SQL 包的 bind_variable 过程来绑定变量。D、使用 DBMS_SQL 包的 execute 函数来执行语句。1、使用 DBMS_S

40、QL 包执行 DDL 语句 需求：使用 DBMS_SQL 包根据用户输入的表名、字段名及字段类型建表。create or replace procedure proc_dbms_sql(table_name in varchar2,-表名 field_name1 in varchar2,-字段名 datatype1 in varchar2,-字段类型 field_name2 in varchar2,-字段名 datatype2 in varchar2 -字段类型)as v_cursor number;-定义光标 v_string varchar2(200);-定义字符串变量 v_row num

41、ber;-行数 begin v_cursor:=dbms_sql.open_cursor;-为处理打开光标 v_string:=create table|table_name|(|field_name1|datatype1|,|field_name2|datatype2|);dbms_sql.parse(v_cursor,v_string,dbms_sql.native);-分析语句 v_row:=dbms_sql.execute(v_cursor);-执行语句 dbms_sql.close_cursor(v_cursor);-关闭光标 exception when others then d

42、bms_sql.close_cursor(v_cursor);-关闭光标 raise;end;以上过程编译通过后，执行过程创建表结构：SQL execute proc_dbms_sql(dinya_test2,id,number(8)not null,name,varchar2(100);PL/SQL procedure successfully completed SQL desc dinya_test2;Name Type Nullable Default Comments -ID NUMBER(8)NAME VARCHAR2(100)Y SQL 2、使用 DBMS_SQL 包执行 DML

43、 语句 需求：使用 DBMS_SQL 包根据用户输入的值更新表中相对应的记录。查看表中已有记录：SQL select*from dinya_test2;ID NAME 1 Oracle 2 CSDN 3 ERP SQL 建存储过程，并编译通过：create or replace procedure proc_dbms_sql_update(id number,name varchar2)as v_cursor number;-定义光标 v_string varchar2(200);-字符串变量 v_row number;-行数 begin v_cursor:=dbms_sql.open_cur

44、sor;-为处理打开光标 v_string:=update dinya_test2 a set a.name=:p_name where a.id=:p_id;dbms_sql.parse(v_cursor,v_string,dbms_sql.native);-分析语句 dbms_sql.bind_variable(v_cursor,:p_name,name);-绑定变量 dbms_sql.bind_variable(v_cursor,:p_id,id);-绑定变量 v_row:=dbms_sql.execute(v_cursor);-执行动态 dbms_sql.close_cursor(v_

45、cursor);-关闭光标 exception when others then dbms_sql.close_cursor(v_cursor);-关闭光标 raise;end;执行过程，根据用户输入的参数更新表中的数据：SQL execute proc_dbms_sql_update(2,csdn_dinya);PL/SQL procedure successfully completed SQL select*from dinya_test2;ID NAME 1 Oracle 2 csdn_dinya 3 ERP SQL 执行过程后将第二条的 name 字段的数据更新为新值 csdn_di

46、nya。这样就完成了使用dbms_sql 包来执行 DML 语句的功能。使用 DBMS_SQL 中，如果要执行的动态语句不是查询语句，使用 DBMS_SQL.Execute 或DBMS_SQL.Variable_Value 来 执 行，如 果 要 执 行 动 态 语 句 是 查 询 语 句，则 要 使 用DBMS_SQL.define_column定 义 输 出 变 量，然 后 使 用DBMS_SQL.Execute,DBMS_SQL.Fetch_Rows,DBMS_SQL.Column_Value 及 DBMS_SQL.Variable_Value 来执行查询并得到结果。总结说明：在 Ora

47、cle 开发过程中，我们可以使用动态 SQL 来执行 DDL 语句、DML 语句、事务控制语句及系统控制语句。但是需要注意的是，PL/SQL 块中使用动态 SQL 执行 DDL 语句的时候与别的不同，在 DDL 中使用绑定变量是非法的（bind_variable(v_cursor,:p_name,name)），分析后不需要执行 DBMS_SQL.Bind_Variable，直接将输入的变量加到字符串中即可。另外，DDL是在调用 DBMS_SQL.PARSE 时执行的，所以 DBMS_SQL.EXECUTE 也可以不用，即在上例中的v_row:=dbms_sql.execute(v_cursor

48、)部分可以不要。Oracle 存储过程调用存储过程调用 Java 方法方法 存储过程中调用存储过程中调用 Java 程序段程序段 软件环境：软件环境：1、操作系统：Windows 2000 Server 2、数 据 库：Oracle 8i R2(8.1.7)for NT 企业版 3、安装路径：C:ORACLE 实现方法：实现方法：1、创建一个文件为 Test.java public class Test public static void main(String args)System.out.println(HELLO THIS iS A Java PROCEDURE);2、javac T

49、est.java 3、java Test 4、SQL conn system/manager SQL grant create any directory to scott;SQL conn scott/tiger SQL create or replace directory test_dir as d:;目录已创建。SQL create or replace java class using bfile(test_dir,TEST.CLASS)2/Java 已创建。SQL select object_name,object_type,STATUS from user_objects;SQL

50、 create or replace procedure test_java as language java name TEST.main(java.lang.String);/过程已创建。SQL set serveroutput on size 5000 SQL call dbms_java.set_output(5000);调用完成。SQL execute test_java;HELLO THIS iS A Java PROCEDURE PL/SQL 过程已成功完成。SQL call test_java();HELLO THIS iS A Java PROCEDURE 调用完成。Orac

51、le 8I 9I 都测试通过。Oracle 高效分页存储过程高效分页存储过程实例实例 create or replace package p_page is -Author :PHARAOHS -Created:2006-4-30 14:14:14 -Purpose:分页过程 TYPE type_cur IS REF CURSOR;-定义游标变量用于返回记录集 PROCEDURE Pagination(Pindex in number,-分页索引 Psql in varchar2,-产生 dataset 的 sql 语句 Psize in number,-页面大小 Pcount out num

52、ber,-返回分页总数 v_cur out type_cur -返回当前页数据记录 );procedure PageRecordsCount(Psqlcount in varchar2,-产生 dataset 的 sql 语句 Prcount out number -返回记录总数 );end p_page;/create or replace package body p_page is PROCEDURE Pagination(Pindex in number,Psql in varchar2,Psize in number,Pcount out number,v_cur out type_

53、cur )AS v_sql VARCHAR2(1000);v_count number;v_Plow number;v_Phei number;Begin -取分页总数 v_sql:=select count(*)from(|Psql|);execute immediate v_sql into v_count;Pcount:=ceil(v_count/Psize);-显示任意页内容 v_Phei:=Pindex*Psize+Psize;v_Plow:=v_Phei-Psize+1;-Psql:=select rownum rn,t.*from zzda t;-要求必须包含 rownum 字段

54、 v_sql:=select*from(|Psql|)where rn between|v_Plow|and|v_Phei;open v_cur for v_sql;End Pagination;-*procedure PageRecordsCount(Psqlcount in varchar2,Prcount out number )as v_sql varchar2(1000);v_prcount number;begin v_sql:=select count(*)from(|Psqlcount|);execute immediate v_sql into v_prcount;Prcou

55、nt:=v_prcount;-返回记录总数 end PageRecordsCount;-*end p_page;/oracle 的使用笔记的使用笔记 2009-07-27 10:45:37 标签：推送到技术圈 版权声明：版权声明：原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处、作者信息和本声明。否则将追究法律责任。http:/ 最多只會用到 1GB RAM。這個限制是很合理的，既然 XE 設計的目標是取代 Access 小 型桌上型資料分享型資料庫，那麼有超過 1GB RAM 的存取能力既不必要，也會壓縮企業版本的獲利空間。2.只能使用單 CPU。這裡並不是說多 CPU 的電腦

56、就不能安裝 XE，而是說如果你在多 CPU 電腦上安裝 XE，它利用這些多 CPU 的方式是仰賴作業系統的分配，而不是像企業版一樣，資料庫本身就可以支配 CPU 的使用。3.一台機器上只能跑一個 Database Instant。4.資料量大小有 4GB 的最大限制。(2)oracleXE 的启动:首先,要在服务中,设置 OracleXETNSListener,OracleServiceXE 为手动,或自动.启动 oracle 时，需要启动 oracle 的服务，以及它的监听 listener.oracleXE 的启动:D:oraclexeapporacleproduct10.2.0serve

57、rBINnet start OracleXETNSListener D:oraclexeapporacleproduct10.2.0serverBINnet start OracleServiceXE oracleXE 的关闭:D:oraclexeapporacleproduct10.2.0serverBINnet stop OracleXETNSListener D:oraclexeapporacleproduct10.2.0serverBINnet stop OracleServiceXE 登录方式一:http:/127.0.0.1:8080/apex 登录方式二:D:oraclexeap

58、poracleproduct10.2.0serverBINsqlplus-可以用command line打开.username:system /pnet password:weblogic/pnet (3)3.1 一套 ORACLE 数据库系统中可以有多个 ORACLE 数据库及其相对应的实例.每个 ORACLE 数据库拥有一个数据库实例（INSTANCE）数据库是一间房间，表空间是一个柜子，表就是一个抽屉；一个数据库中有很多表空间，一个表空间里 又有很多 表；一个表只能属于一个表空间，一个表空间只能属于一个数据库.每个数据库最少有一个表空间（SYSTEM 表空间）,建立 SYSTEM 表空间

59、的目的是尽量将目的相同的表存放在 一起，以提高使用效率，只应存放数据字典.3.2 类型标识符 说明 Number 数字型 Int 整数型 Pls_integer 整数型，产生溢出时出现错误 Binary_integer 整数型，表示带符号的整数 Char 定长字符型，最大 255 个字符 Varchar2 变长字符型，最大 2000 个字符 Long 变长字符型，最长 2GB Date 日期型 Boolean 布尔型（TRUE、FALSE、NULL 三者取一）3.3 将 oracle 的启动路径放于环境变量中的 PATH 中.以后,在任何地方用 net start.都可以了.D:oraclex

60、eapporacleproduct10.2.0serverBIN 数据文件的后辍是:*.DBF.dba_data_files:关联 datafile 与 tablespace dba_tablespaces:只有其自身 dba_tables:关联 talbe 与 tablespace,及 owner.dba_users:user_id,username,password,default tablespace (4)-oracle 结构-一个真正的 oracle 系统,可以有多个数据库;每个数据库对应一个数据库实例(instance);一个数据库实例 中可以有多个表空间(相当于 mysql 中的

61、各个数据库);每个表空间中可以有多个数据表.=oracle 数据以大写保存.所以在 oracle 中,最好全都用大写去操作.-数据文件-(1)数据文件,是独立于表空间的.只是其创建时,由创建空间时生成.其修改,不用用到表空间.如下:alter database datafile.resize 5m;(2)查看系统所有的数据文件:select*from dba_data_files;-表空间-（须指定 datafile 的位置，名称，以及大小）create tablespace jasonspace01 datafile d:oraclexeoradataxejasonspace01size 1

62、0m;（可以指定表空间的自动扩展的大小，以及表空间的最大值。）create tablespace jasonspace02 datafile d:oraclexeoradataxejasonspace02 size 1m autoextend on next 1m maxsize 10m;-创建表空间时,注意 datafile 中,路径的最后要写表空间的名.-创建后的表空间,为大写.无论是在创建时是输入大写与小写.为表空间增加数据文件 alter tablespace jasonspace02 add datafile d:oraclexeoradataxejasonspace size 10

63、m autoextend on next 10m maxsize 20m;修改数据文件的大小:重改 datafile 的大小要小于原来 datafile 可用的大小 alter database datafile D:ORACLEXEORADATAXEJASONSPACE resize 5m;查看表空间的名称，大小，datafile 的存放位置-查看表空间的大小,即是查看数据文件的大小 select tablespace_name,bytes/1024/1024 MB,file_name from dba_data_files;查看表空间剩余的大小：select tablespace_name

64、,sum(bytes)/1024/1024 MB from dba_free_space group by tablespace_name;删除表空间：drop tablespace jasonspace01 including contents;删除表空间的同时，删除数据文件：drop tablespace sales including contents and datafiles;查看系统所有的表空间:select*from dba_tablespaces;-用户管理-创建用户，指定默认表空间，磁盘配额:create user jason identified by weblogic d

65、efault tablespace jasonspace02 quota 1m on jasonspace02;用户授权与销权：*Connect：最低级，仅允许用户访问数据库中的表和索引，但不能创建和删除它们;*Resource:建立在 Connect 之上，允许用户在数据库中创建、删除表和索引;*DBA：即数据库管理员，拥有数据库管理的全部权限，包括访问数据库表、创建和删除索引、修改表结 构、授予数据库权限给其他用户等。grant dba,resource,connect to jason;revoke dba,resource,connect from jason;如何查看权限:1.ora

66、cle 用户查看自己的权限和角色 select*from user_tab_privs;select*from user_role_privs;2.sys 用户查看任一用户的权限和角色 select*from dba_tab_privs;-表的权限?select*from dba_role_privs;如:select*from dba_role_privs where grantee=JASON;GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE JASON RESOURCE NO YES JASON CONNECT NO YES JASON DBA NO YES 修改用户的密码：alter user jason identified by martinpass;删除用户：DROP USER jason;删除用户及此用户下面的所有对象:drop user userName cascade;查看系统所有的用户:select*from dba_users;-表-创建表，并指定表空间：create table jasontable(id int n