oracle 存储过程的基本语法zz

oracle 存储过程的基本语法
来源： 作者： 出处：巧巧读书 2007-07-12 进入讨论组
关 键 词：oracle  os  存储过程  
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) IS
变量1 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;
  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 FROM 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;

评论

3 楼 heimuad 2007-09-15  

一个简单的oracle分页存储过程的实现和调用
相关标签:
调用 存储过程 存储 调用 存储过程 存储
在看了众多的分页存储过程以后发现都是针对sqlserver的，而没有ORACLE的，因此想写一个关于oracle的存储过程，因为我用到的数据库是oracle.
------------------------------------------------------------
oracle分页存储过程的思路于sqlserver的思路是一样的，但是我这里做了点改动，在因为oracle的语法和规则的不同所以，oracle分页
存储过程看上去有点不一样。见笑，见笑！

在oracle的存储过程中返回记录集，需要用到游标变量，oracle不能像sqlserver那样可以直接返回一个记录集。
由于设想在.net中把复杂的sql语句生成，所以在存储过程中没有去考虑生成sql语句的问题。
--------------------------------------------------------
以下是在oracle中实现的分页存储过程。

create or replace package dotnet is

  -- Author  : good_hy
  -- Created : 2004-12-13 13:30:30
  -- Purpose :
 
  TYPE type_cur IS REF CURSOR;     --定义游标变量用于返回记录集
   
  PROCEDURE DotNetPagination(     
  Pindex in number,                --分页索引  
  Psql in varchar2,                --产生dataset的sql语句
  Psize in number,                 --页面大小
  Pcount out number,               --返回分页总数
  v_cur out type_cur               --返回当前页数据记录
  ); 
 
  procedure DotNetPageRecordSCOunt(
  Psqlcount in varchar2,           --产生dataset的sql语句                          
  Prcount   out number             --返回记录总数
  );
 
end DotNot;

---------------------------------------------------

create or replace package body DotNet is

--*****************************************************

PROCEDURE DotNetPagination(
  Pindex in number,
  Psql in varchar2,
  Psize in number, 
  Pcount out number,
  v_cur out type_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 cd_ssxl t' ;            --要求必须包含rownum字段
  v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;

  open v_cur for v_sql;
 
End DotNetPagination;

--*******************************************************

procedure DotNetPageRecordsCount(
  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;
   Prcount := v_prcount;                  --返回记录总数                                                     
  
  end DotNetPageRecordsCount;
 
--*******************************************************

end DotNot;

-----------------------------------------------------------------
以下是在.net中调用oracle分页存储过程的步骤。(vb.net)
在.net调用返回记录集的存储过程，需要用到datareader，但是datareader不支持在datagrid中的分页，因此需要利用datagrid
自定义分页功能。


    Protected WithEvents DataGrid1 As System.web.UI.WebControls.DataGrid

    Dim conn As New OracleClient.OracleConnection()
    Dim cmd As New OracleClient.OracleCommand()
    Dim dr As OracleClient.OracleDataReader

Private Sub gridbind(ByVal pindex As Integer, ByVal psql As String, Optional ByVal psize As Integer = 10)

        conn.ConnectionString = "Password=gzdlgis;User ID=gzdlgis;Data source=gzgis"
        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
        conn.Open()

        '-------------------------------------------------------
        cmd.CommandText = "DotNot.DotNetPageRecordsCount"
        '-----------------------------------------------------------
        cmd.Parameters.Add("psqlcount", OracleType.VarChar).Value = psql
        cmd.Parameters.Add("prcount", OracleType.Number).Direction = ParameterDirection.Output

        cmd.ExecuteNonQuery()

        Me.DataGrid1.AllowPaging = True
        Me.DataGrid1.AllowCustomPaging = True
        Me.DataGrid1.PageSize = psize
        Me.DataGrid1.VirtualItemCount = cmd.Parameters("prcount").Value

        cmd.Parameters.Clear()
        '-----------------------------------------------------
        cmd.CommandText = "DotNot.DotNetPagination"
        '------------------------------------------------------
        cmd.Parameters.Add("pindex", Data.OracleClient.OracleType.Number).Value = pindex
        cmd.Parameters.Add("psql", Data.OracleClient.OracleType.VarChar).Value = psql '"select rownum rn,t.* from cd_ssxl t"
        cmd.Parameters.Add("psize", Data.OracleClient.OracleType.Number).Value = psize
        cmd.Parameters.Add("v_cur", Data.OracleClient.OracleType.Cursor).Direction = ParameterDirection.Output
        cmd.Parameters.Add("pcount", Data.OracleClient.OracleType.Number).Direction = ParameterDirection.Output

        dr = cmd.ExecuteReader()

        Me.DataGrid1.DataSource = dr
        Me.DataGrid1.DataBind()

        dr.Close()
        conn.Close()

        Response.Write("总计页数 " & cmd.Parameters("pcount").Value)
    End Sub

------------------------------------------------------------------

    Private Sub Page_Load(ByVal sender As System.object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Page.IsPostBack Then
            Dim psql As String = "select rownum rn,t.* from cd_ssxl t"
            gridbind(0, psql, 20)
           
        End If

    End Sub

-----------------------------------------------------------------------

    Private Sub DataGrid1_PageIndExchanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
        Dim psql As String = "select rownum rn,t.* from cd_ssxl t"

        Me.DataGrid1.CurrentPageIndex = e.NewPageIndex
        gridbind(e.NewPageIndex, psql, 20)
    End Sub

2 楼 heimuad 2007-09-15  

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_name||'='||p_value;
open outpara for
wherevalue;
end;
end;

1 楼 heimuad 2007-09-15  

*执行一条insert语句并传参数*/
create or replace procedure p_test2(i in number) as
begin
insert into t values (i,'x'||to_char(i));
commit;
end;
/

<%@ page language="java" contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*"%>
<html>
<body>
<table>
<tr><td>aaa</td><td>bbb</td></tr>
<%
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@192.168.1.6:1521:db";

Class.forName(driver);
Connection conn = DriverManager.getConnection(strUrl, "scott", "tiger");

String procedure = "{call p_test2 (?) }";
CallableStatement cstmt = conn.prepareCall(procedure);
cstmt.setInt(1,33);
cstmt.executeUpdate();

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from t");

while(rs.next())
{
out.println("<tr><td>" + rs.getString(1) + "</td><td>" + rs.getString(2) + "</td></tr>");
}

rs.close();
stmt.close();
conn.close();
%>
</table>
<p>
<%
out.print(conn.isClosed());
%>

</body>
</html>
</body>
</html>