oracle存储过程

1.sql语句

CREATE OR REPLACE PROCEDURE NS_WST_SaveATL
(/**要传入的参数**/
ID                         IN OUT  	INT,
UID                        IN      	INT DEFAULT NULL,
UName                      IN      	VARCHAR2 DEFAULT NULL,
No                         IN      	VARCHAR2 DEFAULT NULL,
EftDate                    IN      	DATE DEFAULT NULL,
AuthNo                     IN      	VARCHAR2 DEFAULT NULL,
AuthTx                     IN      	INT DEFAULT NULL,
RefNo                      IN      	VARCHAR2 DEFAULT NULL
)
/** 声明变量 ，可以自己定义**/
AS
	Action                     INT;
	OpText                     VARCHAR2(30);
	LogText                    VARCHAR2(2048);
BEGIN
	 /***********************************
	   * 1.插入或更新WST_Account_ATL表内容
	   ***********************************/
	IF NVL(ID, 0) = 0 THEN
	 /** 新增 **/
	BEGIN

		INSERT INTO  WST_Account_ATL
				( No ,
				EftDate ,
				AuthNo ,
				AuthTx ,
				RefNo ,
				Action ,
				Active ,
				State )
		 VALUES 		( NS_WST_SaveATL.No ,
				NS_WST_SaveATL.EftDate ,
				NS_WST_SaveATL.AuthNo ,
				NS_WST_SaveATL.AuthTx ,
				NS_WST_SaveATL.RefNo ,
				1 ,
				0 ,
				0 );
     /** select  into给变量赋值**/
		SELECT 10 INTO Action FROM DUAL;
		SELECT '新增' INTO OpText FROM DUAL;
		NS_WST_SaveATL.ID  :=  GLOBALPKG.IDENTITY;
	END;
	ELSE
	BEGIN

		UPDATE  WST_Account_ATL
		SET	No = NS_WST_SaveATL.No,
		    EftDate = NS_WST_SaveATL.EftDate,
		    AuthNo = NS_WST_SaveATL.AuthNo,
		    AuthTx = NS_WST_SaveATL.AuthTx,
		    RefNo = NS_WST_SaveATL.RefNo
		WHERE  ATId  = NS_WST_SaveATL.ID;

		SELECT '修改' INTO OpText FROM DUAL;
		SELECT  11  INTO  NS_WST_SaveATL.Action  FROM DUAL;
	END;
	END IF;
  /****************************
   * 2.插入WST_OpLog表内容
   ****************************/

	SELECT  '生效日期=' || TO_CHAR(NS_WST_SaveATL.EftDate, 'YYYY.MM.DD') || ';结算账号=' || NS_WST_SaveATL.No || ';' || '被授权人账号=' || NVL(NS_WST_SaveATL.AuthNo, '') || ',行为=' || NVL(TO_CHAR(NS_WST_SaveATL.AuthTx), '') || '},' || '第三方帐号=' || NVL(NS_WST_SaveATL.RefNo, '')  INTO  NS_WST_SaveATL.LogText  FROM DUAL;

	INSERT INTO  WST_OpLog
			( SourceType ,
			SourceId ,
			OpType ,
			Action ,
			OpText ,
			"UID" ,
			UName ,
			"COMMENT" ,
			LogText ,
			OpTime ,
			SignId )
	 VALUES 		( 'ATL' ,
			NS_WST_SaveATL.ID ,
			1 ,
			NS_WST_SaveATL.Action ,
			NS_WST_SaveATL.OpText ,
			NS_WST_SaveATL.UID ,
			NS_WST_SaveATL.UName ,
			null ,
			NS_WST_SaveATL.LogText ,
			(SYSDATE) ,
			null );
END;

2.java配置

<procedure id="procedureInsertIns" parameterMap="insMap">
		{call NS_WST_SaveIns(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
	</procedure>

3.调用（用update）

sqlMapClientTemplate.update("DBMS_Querys.procedureInsertIns", insMap);