SQLserver和ORACLE存储过程

SQLserver：

ALTER PROCEDURE [dbo].[dp_p_GetSaleData_test]
  @sss AS nvarchar //传入参数，这个参数未被使用
AS
BEGIN
	-- routine body goes here, e.g.
	-- SELECT 'Navicat for SQL Server'
						select convert(decimal(19,0),isnull(SM.sumShipQty-RCV.sumRcvQty,0)) as sumQty_year  from 
				(
				select sum(a.ShipQtyTUAmount) as sumShipQty from sm_shipline as a 
				inner join sm_ship as b on a.ship = b.id
				inner join SM_ShipDocType as c on c.id = b.DocumentType
				inner join base_organization as d on d.id = b.org
				where d.code = '506' and c.code = 'SMZ'   and  b.Status = 3  
				)SM
			   left join
				(
				select isnull(sum(a.RcvQtySU),0) as sumRcvQty from PM_RcvLine as a 
				inner join PM_Receivement as b on a.receivement = b.ID
				inner join PM_RcvDocType as c on c.ID = b.RcvDocType
				inner join base_organization as d on d.ID = b.org
				where d.Code = '506' and c.code = 'RCC'   and b.Status = 5  
				) as RCV on 1=1
	
END

调用：

exec [dbo].[dp_p_GetSaleData_test] 'd'  --参数未被使用，所随便传

oracle：

CREATE OR REPLACE procedure testprocedure
(
--定义输入、输出参数--
-- num_A in integer,
-- num_B in integer,
-- numType in integer,
-- num_C out integer
namecc in VARCHAR

)

as
--定义变量--
 -- numCount integer;
 -- numStr varchar(20); 
v_addr varchar2(2000);
begin   
     --判断计算类型--
--      if numType=1 then
--         num_C := num_A + num_B;
--      elsif numType=2 then
--         num_C := num_A - num_B;
--      elsif numType=3 then
--         num_C := num_A * num_B; 
--      elsif numType=4 then
--         num_C := num_A / num_B; 
--      else
--      --其它处理
--        dbms_output.put_line('其它处理');
--      end if;
--      dbms_output.put_line(num_C);
		 
		 SELECT CLIENT_NAME INTO v_addr from LK_REPAIR_ORDER  WHERE CLIENT_NAME = namecc AND ROWNUM <2;
		 dbms_output.put_line(v_addr);
		 
end;

调用：

call TESTPROCEDURE('陈xx');

模版二：

CREATE OR REPLACE PROCEDURE my_procedure AS
    -- 声明游标变量
    CURSOR my_cursor IS
        SELECT column1, column2, column3
        FROM my_table;

    -- 声明游标结果集的类型
    TYPE my_cursor_type IS REF CURSOR;

    -- 声明游标变量
    my_result my_cursor_type;

    -- 声明变量用于接收查询结果
    my_column1 my_table.column1%TYPE;
    my_column2 my_table.column2%TYPE;
    my_column3 my_table.column3%TYPE;

BEGIN
    -- 打开游标
    OPEN my_cursor;

    -- 将游标赋给游标变量
    my_result := my_cursor;

    -- 循环遍历游标结果集
    LOOP
        -- 从游标变量中获取下一行数据
        FETCH my_result INTO my_column1, my_column2, my_column3;

        -- 判断是否还有数据
        EXIT WHEN my_result%NOTFOUND;

        -- 在这里可以对查询结果进行处理或输出
        DBMS_OUTPUT.PUT_LINE('Column1: ' || my_column1 || ', Column2: ' || my_column2 || ', Column3: ' || my_column3);
    END LOOP;

    -- 关闭游标
    CLOSE my_cursor;

END;
/

例子：

CREATE OR REPLACE PROCEDURE get_orgnameuserid_by_code (
  p_code IN orgnameuserid.code%TYPE,
  p_result OUT SYS_REFCURSOR
) AS
  v_code orgnameuserid.code%TYPE;
  v_name orgnameuserid.ORGANIZATION_NAME%TYPE;
BEGIN
  OPEN p_result FOR
    SELECT code, ORGANIZATION_NAME
    FROM orgnameuserid
    WHERE code = p_code;
  LOOP

    FETCH p_result INTO v_code, v_name;
    EXIT WHEN p_result%NOTFOUND;

    -- 这里可以对获取的数据进行处理
    -- 可以输出、存储或使用数据
    DBMS_OUTPUT.PUT_LINE('Code: ' || v_code || ', Organization Name: ' || v_name);
  END LOOP;

  CLOSE p_result;
END;

调用：

DECLARE
  v_result SYS_REFCURSOR;
BEGIN
  get_orgnameuserid_by_code('502', v_result);
END;

例子二：

CREATE OR REPLACE procedure update_orgname_userid AS
BEGIN
DECLARE
   my_data  VARCHAR2(4000);
BEGIN


   for cur in (
	 
SELECT a.code,a.name,
       --使用 XMLAGG 和 XMLPARSE 函数来执行字符串连接：
       RTRIM(XMLAGG(XMLELEMENT(E, au.login_name || ',')).EXTRACT('//text()').getclobval(), ',') AS login_name
FROM app_user au
LEFT JOIN BASE_ORGANIZATION a ON au.organization_id = a.id
WHERE 
   au.organization_id is not null
   and au.organization_id <> 'NULL'
   and au.channel_customer_id is null
   and au.dr = 0
GROUP BY a.name,a.code

   )
   loop 
    UPDATE orgnameuserid
      SET login_name = cur.login_name,
					organization_name = cur.name
      WHERE code = cur.code;
			
		IF cur.name = 'xxx控股有限公司' THEN
        my_data := '123456';
    ELSE
        SELECT RTRIM(XMLAGG(XMLELEMENT(E, au.login_name || ',')).EXTRACT('//text()').getclobval(), ',')
        INTO my_data
        FROM app_user au
        LEFT JOIN BASE_ORGANIZATION a ON au.organization_id = a.id
        WHERE 
            au.organization_id IS NOT NULL
            AND au.organization_id <> 'NULL'
            AND au.channel_customer_id IS NULL
            AND au.dr = 0
            AND a.name = 'xxx控股有限公司'
        GROUP BY a.name, a.code;
    END IF;
		
			UPDATE orgnameuserid
      SET login_name = cur.login_name || ',001002,' || my_data,
					organization_name = cur.name
      WHERE code = cur.code;
			
   -- 打印更新消息
      DBMS_OUTPUT.PUT_LINE('Updated login_name for organization: ' || cur.name);

   end loop;
 END;
 END;