SET SERVEROUTPUT ON
SQL> DECLARE CNUM varchar2(25);

CREATE OR REPLACE FUNCTION get_item_no(p_item_id varchar2,
P_ORG_ID varchar2) -- 取物料编码

RETURN VARCHAR2 IS

v_item_no varchar2(25);
BEGIN
IF p_item_id is not null then
select i.segment1 into v_item_no
from mtl_system_items_b i
where i.inventory_item_id = p_item_id
and i.organization_id = P_ORG_ID;
RETURN(v_item_no);
ELSE
RETURN(NULL);
END IF;
END ;

SQL> SET SERVEROUTPUT ON
SQL> DECLARE CNUM varchar2(25);
2 BEGIN
3 CNUM :=get_item_no('25334','41');
4
4 DBMS_OUTPUT.PUT_LINE('CNUM='||CNUM);
5 END;
6 /
CNUM=1001001000253

PL/SQL 过程被成功完成

1.创建过程SP_STUDENT
---------------------
管理器-方案-过程-创建-相关代码的编写
或者直接在SQL Plus中编写
相关代码如下：
SQL> CREATE PROCEDURE SP_STUDENT
2 (
3 SID IN VARCHAR2,
4 SNAME IN VARCHAR2
5 )
6 AS
7 BEGIN
8 INSERT INTO STUDENT VALUES(SID,SNAME);
9 END SP_STUDENT;
10 /

过程已创建。

SQL>

2.在SQL Plus中调用过程SP_STUDENT
------------------------------
相关代码如下：
SQL> EXECUTE USER1_ADB.SP_STUDENT ('12','BB');

PL/SQL 过程已成功完成。

SQL>

3.在VS程序中调用过程SP_STUDENT
------------------------------
protected void btn_CallSP_Click(object sender, EventArgs e)
{
//ADB为数据库名
string strConn = "data source=ADB;user id=USER1_ADB;password=user1test";
try
{
using (OracleConnection myConn = new OracleConnection(strConn))
{
OracleCommand myComm = new OracleCommand("SP_STUDENT", myConn);
myComm.CommandType = CommandType.StoredProcedure;
myComm.Parameters.Add("SID", "88");
myComm.Parameters.Add("SNAME", "AA");
myConn.Open();
myComm.ExecuteNonQuery();
Response.Write("Insert OK");
}
}
catch (OracleException ex)
{
ex.ToString();
}
}