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.idinner join SM_ShipDocType as c on c.id = b.DocumentTypeinner join base_organization as d on d.id = b.orgwhere d.code = '506' and c.code = 'SMZ'   and  b.Status = 3  )SMleft join(select isnull(sum(a.RcvQtySU),0) as sumRcvQty from PM_RcvLine as a inner join PM_Receivement as b on a.receivement = b.IDinner join PM_RcvDocType as c on c.ID = b.RcvDocTypeinner join base_organization as d on d.ID = b.orgwhere d.Code = '506' and c.code = 'RCC'   and b.Status = 5  ) as RCV on 1=1END

调用：

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 ISSELECT column1, column2, column3FROM 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
) ASv_code orgnameuserid.code%TYPE;v_name orgnameuserid.ORGANIZATION_NAME%TYPE;
BEGINOPEN p_result FORSELECT code, ORGANIZATION_NAMEFROM orgnameuseridWHERE code = p_code;LOOPFETCH 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;

调用：

DECLAREv_result SYS_REFCURSOR;
BEGINget_orgnameuserid_by_code('502', v_result);
END;

例子二：

CREATE OR REPLACE procedure update_orgname_userid AS
BEGIN
DECLAREmy_data  VARCHAR2(4000);
BEGINfor 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 nulland au.organization_id <> 'NULL'and au.channel_customer_id is nulland au.dr = 0
GROUP BY a.name,a.code)loop UPDATE orgnameuseridSET login_name = cur.login_name,organization_name = cur.nameWHERE code = cur.code;IF cur.name = 'xxx控股有限公司' THENmy_data := '123456';ELSESELECT RTRIM(XMLAGG(XMLELEMENT(E, au.login_name || ',')).EXTRACT('//text()').getclobval(), ',')INTO my_dataFROM app_user auLEFT JOIN BASE_ORGANIZATION a ON au.organization_id = a.idWHERE au.organization_id IS NOT NULLAND au.organization_id <> 'NULL'AND au.channel_customer_id IS NULLAND au.dr = 0AND a.name = 'xxx控股有限公司'GROUP BY a.name, a.code;END IF;UPDATE orgnameuseridSET login_name = cur.login_name || ',001002,' || my_data,organization_name = cur.nameWHERE code = cur.code;-- 打印更新消息DBMS_OUTPUT.PUT_LINE('Updated login_name for organization: ' || cur.name);end loop;END;END;