sql: Oracle 11g create procedure

妖狐艹你老母 2021-12-13 00:53 235阅读 0赞

CREATE OR REPLACE PROCEDURE proc_Insert_BookKindList 
    (
    temTypeName nvarchar2,
    temParent int
    )
    AS
    ncount number;
    begin
    --SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where  EXISTS (SELECT BookKindName from BookKindList fm2 where  fm2.BookKindName=temTypeName);--判斷是否存
    SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;
    if ncount<=0 then
    begin
    INSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);
    commit;
    end;
    else
    begin
      SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName;
      dbms_output.put_line('存在相同的记录,添加不成功!'||ncount);
    end;
    end if;
    Exception 
        When others then 
          dbms_output.put_line('存在问题,添加不成功!'||ncount);
           Rollback; 
    end proc_Insert_BookKindList;
    
    --测试 oracle 11g 涂聚文 20150526
    exec proc_Insert_BookKindList ('油彩画',3);
    
    drop PROCEDURE proc_Insert_BookKindOut;
    
    
    CREATE OR REPLACE PROCEDURE procInsertBookKindOut --添加返回ID
    (
    temTypeName nvarchar2,
    temParent number,
    temId out number   
    )
    AS
    ncount number;
    reid number;
    begin
    --SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where  EXISTS (SELECT BookKindName from BookKindList fm2 where  fm2.BookKindName=temTypeName);--判斷是否存
    SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;
    if ncount<=0 then
    begin
    INSERT INTO BookKindList (BookKindID,BookKindName,BookKindParent) VALUES(BookKindList_SEQ.nextval,temTypeName,temParent);
    select BookKindList_SEQ.currval into reid from dual;
    temId:=reid;
    dbms_output.put_line('添加成功!'||temId);
    commit;
    end;
    else
    begin
      SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName;
      dbms_output.put_line('存在相同的记录,添加不成功!'||ncount);
      temId:=0;
    end;
    end if;
    Exception 
        When others then 
        begin
          dbms_output.put_line('存在问题,添加不成功!'||ncount);
          temId:=0;
           Rollback; 
        end;
    end procInsertBookKindOut;
    
    --测试 oracle 11g 涂聚文 20150526
    declare
    mid  number:=0;
    nam  nvarchar2(100):='黑白画';
    par  number:=3;
    begin
    --proc_Insert_BookKindOut(nam in nvarchar2,par in int,mid in out int);
    procInsertBookKindOut(nam,par ,mid);
    if mid>0 then
    dbms_output.put_line('添加成功!输出参数:'||mid);
    else
    dbms_output.put_line('存在相同的记录,添加不成功!输出参数:'||mid);
    end if;
    end;

csharp 调用：

///<summary>
            /// 追加记录
            ///</summary>
            ///<param name="BookKindListInfo"></param>
            ///<returns></returns>
            public int InsertBookKindList(BookKindListInfo bookKindList)
            {
                int ret = 0;
                try
                {
                    OracleParameter[] par = new OracleParameter[]{
    				new OracleParameter("temTypeName",OracleType.NVarChar,1000),
    				new OracleParameter("temParent",OracleType.Number,4),
    				};
                    par[0].Value = bookKindList.BookKindName;
                    par[1].Value = bookKindList.BookKindParent;
                    ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);
                }
                catch (OracleException ex)
                {
                    throw ex;
                }
                return ret;
            }
            /// <summary>
            /// 追加记录返回
            /// </summary>
            /// <param name="authorList"></param>
            /// <param name="authorID"></param>
            /// <returns></returns>
            public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)
            {
                bookKindLID = 0;
                int ret = 0;
                try
                {
                    OracleParameter[] par = new OracleParameter[]{
    				new OracleParameter("temTypeName",OracleType.NVarChar,1000),
                    new OracleParameter("temParent",OracleType.Number,4),
                    new OracleParameter("temId",OracleType.Number,4),
    				};
                    par[0].Value = bookKindList.BookKindName;
                    par[1].Value = bookKindList.BookKindParent;
                    par[2].Direction = ParameterDirection.Output;
                    ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);
                    if (ret > 0)
                    {
                        bookKindLID =int.Parse(par[2].Value.ToString());
                    }
                }
                catch (OracleException ex)
                {
                    throw ex;
                }
                return ret;
            }

/// <summary>
            /// 
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button1_Click(object sender, EventArgs e)
            {
                BookKindListInfo bookKindListInfo = new BookKindListInfo();
                BookKindListBLL bookKindListBLL = new BookKindListBLL();
                bookKindListInfo.BookKindParent =(int)this.numericUpDownBookKindParent.Value;
                bookKindListInfo.BookKindName = this.textBoxBookKindName.Text.Trim();
    
                int k = 0;
                k = bookKindListBLL.InsertBookKindList(bookKindListInfo);
                if (k > 0)
                {
                    MessageBox.Show("ok");
                }
                else
                {
                    MessageBox.Show("no");
                }
    
    
    
            }
            /// <summary>
            /// 
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button2_Click(object sender, EventArgs e)
            {
                BookKindListInfo bookKindListInfo = new BookKindListInfo();
                BookKindListBLL bookKindListBLL = new BookKindListBLL();
                bookKindListInfo.BookKindParent = (int)this.numericUpDownBookKindParent.Value;
                bookKindListInfo.BookKindName = this.textBoxBookKindName.Text.Trim();
                int ou = 0;
                int k = 0;
                k = bookKindListBLL.InsertBookKindOutput(bookKindListInfo,out ou);
                if (k > 0)
                {
                    MessageBox.Show("ok:id"+ou.ToString());
                }
                else
                {
                    MessageBox.Show("no");
                }
            }

转载于:https://www.cnblogs.com/geovindu/p/4531952.html