精华内容
下载资源
问答
  • oracle存储过程写法和调用方法
    2021-05-04 00:46:21

    create or replace procedure prohistoryData(startTime in VARCHAR2,endTime in VARCHAR2) is

    begin

    delete DEVICE_HISTORY where TO_CHAR(COLLECTTIME,'YYYY-MM-DD HH24:MI:SS') between startTime and endTime;

    delete SPAN_HISTORY where TO_CHAR(COLLECTTIME,'YYYY-MM-DD HH24:MI:SS') between startTime and endTime;

    delete STRAIN_HISTORY where TO_CHAR(COLLECTTIME,'YYYY-MM-DD HH24:MI:SS') between startTime and endTime;

    delete CAPACITY_HISTORY where TO_CHAR(COLLECTTIME,'YYYY-MM-DD HH24:MI:SS') between startTime and endTime;

    end prohistoryData;

    调用方法

    exec prohistorydata(startTime='2010-01-01',endTime='2010-12-30');

    调用方法在命令提示行运行,记得commit 提交操作

    经验总结

    Procedures -->prohistoryData  -->(text or 测试)

    进入另一页面

    表格里填参数

    如果存储过程写法有问题,过程是一把叉 ,红色警告,点击调试看错误

    更多相关内容
  • 公司做了一个需求,初期设想通过存储过程来初始化表数据,所以写了一个存储过程,如下: --CREATE PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE] ALTER PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE] ...

    公司做了一个需求,初期设想通过存储过程来初始化表数据,所以写了一个存储过程,如下:

    --CREATE PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE]
    ALTER PROCEDURE [dbo].[PROC_HANDLE_SHOPCAR_TXD_SR_TYPE]
    AS 
    	BEGIN
    				-------------------- 第一部分----------------------------
    				DECLARE	@SELF_XD BIGINT;
    				BEGIN
    						SELECT
    							@SELF_XD = COUNT(1)
    						FROM
    								CRM_SHOP_CAR WITH(NOLOCK) 
    						WHERE
    								STATUS = 0 
    								AND GRP != 'winretailsr' 
    								AND PRE_ORDER_NO IS NULL 
    								AND TXD_SR_TYPE IS NULL;
    				END;
    				PRINT '--------统计更新门店和伙伴自主下单购物车记录数 :' + CAST(@SELF_XD AS VARCHAR(20));
    				
    				IF @SELF_XD > 0
    						BEGIN
    							PRINT '--------更新门店和伙伴自主下单购物车记录开始...';
    							UPDATE CRM_SHOP_CAR SET TXD_SR_TYPE = 0 WHERE STATUS = 0 AND GRP != 'winretailsr' AND PRE_ORDER_NO IS NULL AND TXD_SR_TYPE IS NULL;
    							PRINT '--------更新门店和伙伴自主下单购物车记录完成...';
    						END;
    
    
    
    
    				-------------------- 第二部分----------------------------
    				DECLARE	@SR_TXD_COUNT BIGINT;
    				DECLARE @SR_TXD_CUSTOMER_COUNT BIGINT; 
    				DECLARE @PAGE_SIZE 	INT;
    				DECLARE @TOTAL_PAGE INT;
    				DECLARE @PAGE_NUM  INT;
    				
    				BEGIN
    						SET @PAGE_NUM = 1;
    						SET @PAGE_SIZE = 2000;
    						
    						SELECT 
    							@SR_TXD_COUNT = COUNT(1)
    						FROM 
    							CRM_SHOP_CAR WITH(NOLOCK)
    						WHERE 
    							STATUS = 0  
    							AND GRP = 'winretailsr'  
    							AND PRE_ORDER_NO IS NULL 	
    							AND TXD_SR_TYPE IS NULL;
    							PRINT '--------统计更新替下单购物车记录数 :' + CAST(@SR_TXD_COUNT AS VARCHAR(20));
    						
    						SELECT 
    									shopcar.ID,
    									shopcar.CUSTOMER_ID,
    									cwc.CUST_TITLE
    									INTO #CRM_SHOP_CAR_TXD_TMP 
    									FROM CRM_SHOP_CAR shopcar WITH(NOLOCK)
    									JOIN CRM_WS_CUSTOMER cwc WITH(NOLOCK) on shopcar.CUSTOMER_ID = cwc.CUSTOMER_ID
    									WHERE shopcar.STATUS = 0 
    												AND shopcar.GRP = 'winretailsr'
    												AND shopcar.PRE_ORDER_NO IS NULL
    												AND shopcar.TXD_SR_TYPE IS NULL;
    												
    						SELECT 
    							@SR_TXD_CUSTOMER_COUNT = COUNT(1) FROM (
    								SELECT DISTINCT CUSTOMER_ID,CUST_TITLE	FROM #CRM_SHOP_CAR_TXD_TMP
    							) COUNT_TMP;
    						
    						
    				END;
    				
    				
    				IF @SR_TXD_COUNT > 0	
    						BEGIN
    								PRINT '--------更新替下单购物车记录开始...';
    								
    								DECLARE @SAILER_CUSTOMER_ID BIGINT;
    								DECLARE @CUST_TITLE VARCHAR(20);				
    								DECLARE @TXD_SR_TYPE INT; -- 1.掌柜替门店下单(内购)  2.掌柜替伙伴下单  3.合伙人替伙伴下单
    								DECLARE @TXD_SR_CUSTOMER BIGINT;
    								DECLARE @SHOP_CAR_ID BIGINT;
    								
    								
    								IF (@SR_TXD_CUSTOMER_COUNT % @PAGE_SIZE) = 0
    										BEGIN
    											SET @TOTAL_PAGE = (@SR_TXD_CUSTOMER_COUNT / @PAGE_SIZE);
    										END;
    								ELSE
    										BEGIN
    											SET @TOTAL_PAGE = (@SR_TXD_CUSTOMER_COUNT / @PAGE_SIZE) + 1;
    										END;
    								PRINT '客户分组总数:' + CAST(@SR_TXD_CUSTOMER_COUNT as varchar(20));
    								PRINT '分页总数:' + CAST(@TOTAL_PAGE as varchar(20));
    
    								
    								-- 分页处理
    								WHILE  @PAGE_NUM <= @TOTAL_PAGE
    										BEGIN
    												PRINT '第' + CAST(@PAGE_NUM as varchar(20)) + '页开始...';
    												
    												DECLARE SAILER_SHOPCAR_LIST_CURSOR	CURSOR FORWARD_ONLY READ_ONLY STATIC
    												FOR	
    														SELECT TOP 2000  
    																CUSTOMER_ID,
    																CUST_TITLE
    														FROM
    																( 
    																	SELECT ROW_NUMBER() OVER ( ORDER BY CUSTOMER_ID  ASC) PAGE_ROW_NUMBER,CUSTOMER_ID,CUST_TITLE FROM (
    																					SELECT 
    																					DISTINCT CUSTOMER_ID,
    																									 CUST_TITLE 
    																					FROM #CRM_SHOP_CAR_TXD_TMP
    																			) AS PAGE_TABLE_ALIAS 
    																	) AS PAGE_TABLE_ALIAS
    															 WHERE
    																		PAGE_ROW_NUMBER > (@PAGE_NUM - 1) * @PAGE_SIZE
    															ORDER BY
    																		PAGE_ROW_NUMBER;
    													-- 打开游标					
    													OPEN SAILER_SHOPCAR_LIST_CURSOR;
    													-- 遍历游标
    													FETCH NEXT FROM SAILER_SHOPCAR_LIST_CURSOR INTO @SAILER_CUSTOMER_ID,@CUST_TITLE;
    													WHILE @@fetch_status = 0
    															BEGIN
    											
    																			PRINT '@SAILER_CUSTOMER_ID :' + CAST(@SAILER_CUSTOMER_ID as varchar(20));
    																			
    																			IF @CUST_TITLE = '业代'
    																					BEGIN
    																						SET @TXD_SR_TYPE = 0;
    																					END;
    																					
    																			ELSE IF @CUST_TITLE = '店主' 
    																					BEGIN
    																						SET @TXD_SR_TYPE = 1;
    																						
    																						SELECT 
    																								@TXD_SR_CUSTOMER = BUS_CUSTOMER_ID 
    																						FROM VIEW_SR_RETAIL_INFO  WITH(NOLOCK) 
    																						WHERE ISNULL(IS_VALID, 0) = 1 
    																						AND RETAIL_CUSTOMER_ID = @SAILER_CUSTOMER_ID;
    																					END;
    		-- 																
    																			ELSE IF @CUST_TITLE = '经销商'
    																					BEGIN
    																								SET @TXD_SR_TYPE = 3;
    																							
    																								SELECT
    																										TOP 1
    																										@TXD_SR_CUSTOMER = CBI.CUSTOMER_ID 
    																								FROM
    																										CRM_BUSINESS_INFO CBI WITH ( NOLOCK )
    																										LEFT JOIN CRM_SR_GROUP_CUSTOMER CSGCL1 WITH ( NOLOCK ) ON CBI.CUSTOMER_ID = CSGCL1.CUSTOMER_ID
    																										LEFT JOIN CRM_SR_GROUP_CUSTOMER_LOG CSGCL2 WITH ( NOLOCK ) ON CSGCL2.SR_GROUP_ID = CSGCL1.SR_GROUP_ID
    																										LEFT JOIN CRM_WS_CUSTOMER CWC WITH ( NOLOCK ) ON CSGCL2.CUSTOMER_ID = CWC.CUSTOMER_ID
    																										LEFT JOIN CRM_POI_INFO CPI WITH ( NOLOCK ) ON CPI.POI_CODE = CWC.ORG_ID
    																								WHERE
    																										CBI.BUS_TYPE = 13
    																										AND CBI.BUS_STATUS = 1
    																										AND CSGCL1.STATUS = 1
    																										AND CSGCL2.STATUS = 1
    																										AND CWC.CUST_TITLE = '经销商'
    																										AND CPI.DEALER_SOURCE_TYPE = 'X村通伙伴'
    																										AND CPI.STATUS = '1'
    																										AND CWC.CUSTOMER_ID = @SAILER_CUSTOMER_ID;
    																								
    																								IF @TXD_SR_CUSTOMER IS NULL
    																										BEGIN
    																												SET @TXD_SR_TYPE = 2;
    																												
    																												SELECT TOP 1 
    																														@TXD_SR_CUSTOMER = srRelation.SR_CUSTOMER_ID
    																												FROM CRM_DEALER_SR_RELATIONSHIP srRelation WITH(NOLOCK)
    																												INNER JOIN CRM_WS_CUSTOMER customer WITH(NOLOCK) ON  srRelation.DEALER_ID = customer.ORG_ID 
    																												AND customer.CUST_TITLE = '经销商'
    																												INNER JOIN CRM_POI_INFO poiInfo WITH(NOLOCK) ON poiInfo.POI_CODE = customer.ORG_ID 
    																												AND poiInfo.DEALER_SOURCE_TYPE = 'X村通伙伴'
    																												WHERE srRelation.STATUS = 1
    																												AND customer.CUSTOMER_ID = @SAILER_CUSTOMER_ID;
    																										END;		
    																						END;
    																						
    																						
    																					--	PRINT 'SAILER_CUSTOMER_ID= ' + CAST(@SAILER_CUSTOMER_ID as varchar(20))+ ',TXD_SR_CUSTOMER='+ CAST(@TXD_SR_CUSTOMER as varchar(20)) +',TXD_SR_TYPE = ' + CAST(@TXD_SR_TYPE as varchar(20));
    																				
    																				
    																				
    																		DECLARE SAILER_SHOPCAR_CUSTOMER_CURSOR	CURSOR FORWARD_ONLY READ_ONLY STATIC
    																		FOR			
    																				SELECT 
    																							ID 
    																							FROM #CRM_SHOP_CAR_TXD_TMP
    																				WHERE  CUSTOMER_ID = @SAILER_CUSTOMER_ID AND CUST_TITLE = @CUST_TITLE ORDER BY ID
    																		
    																		OPEN SAILER_SHOPCAR_CUSTOMER_CURSOR;
    																		FETCH NEXT FROM SAILER_SHOPCAR_CUSTOMER_CURSOR INTO @SHOP_CAR_ID;
    																		WHILE @@fetch_status = 0
    																			BEGIN
    																					
    																					UPDATE CRM_SHOP_CAR SET
    																						TXD_SR_TYPE = @TXD_SR_TYPE,
    																						TXD_SR_CUSTOMER = @TXD_SR_CUSTOMER 
    																					WHERE ID = @SHOP_CAR_ID;
    																								
    																					FETCH NEXT FROM SAILER_SHOPCAR_CUSTOMER_CURSOR INTO @SHOP_CAR_ID;				
    																			END;
    																	 CLOSE SAILER_SHOPCAR_CUSTOMER_CURSOR;
    																	 DEALLOCATE SAILER_SHOPCAR_CUSTOMER_CURSOR;	
    															
    
    																				
    																	FETCH NEXT FROM SAILER_SHOPCAR_LIST_CURSOR INTO @SAILER_CUSTOMER_ID,@CUST_TITLE;
    															END;
    															CLOSE SAILER_SHOPCAR_LIST_CURSOR;
    															DEALLOCATE SAILER_SHOPCAR_LIST_CURSOR;	
    															
    															
    															SET @PAGE_NUM = (@PAGE_NUM + 1);
    															
    										--					PRINT '@PAGE_NUM + 1 = ' + CAST(@PAGE_NUM as varchar(20));
    															
    															
    										-- WHILE 3
    										END;
    										
    										PRINT '--------更新替下单购物车记录结束...';		
    						-- IF 2
    						END;
    				
    				
    	-- 1			
    	END;

    上线的时候在预上线环境执行,考虑执行时间需要7-8分钟,会影响线上的业务,所以进行了把存储过程拆分成多个sql来进行数据初始化,经过同事和我的共同探讨,总结成了如下的sql:

    -- 自主下单  
    UPDATE  CRM_SHOP_CAR
    SET     TXD_SR_TYPE = 0
    WHERE   TXD_SR_TYPE IS NULL
            AND ID IN ( SELECT  ID
                        FROM    CRM_SHOP_CAR WITH(NOLOCK)
                        WHERE   GRP != 'winretailsr'
                                AND STATUS = 0
                                AND PRE_ORDER_NO IS NULL 
    														);
    
    -- 业代内购
    UPDATE  C
    SET     C.TXD_SR_TYPE = 0
    FROM    CRM_SHOP_CAR C WITH(NOLOCK)
            LEFT JOIN dbo.CRM_WS_CUSTOMER W  WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
    WHERE   W.CUST_TITLE = '业代'
            AND C.GRP = 'winretailsr'
            AND C.STATUS = 0
            AND C.PRE_ORDER_NO IS NULL
    		AND C.TXD_SR_TYPE IS NULL;
    				
    
    
    -- 掌柜给门店下单
    UPDATE  C
    SET     C.TXD_SR_TYPE = 1,
            C.TXD_SR_CUSTOMER = I.BUS_CUSTOMER_ID
    FROM    CRM_SHOP_CAR C 
            LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
            LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK)  ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
    WHERE   W.CUST_TITLE = '门店'
            AND C.GRP = 'winretailsr'
            AND C.STATUS = 0
            AND C.PRE_ORDER_NO IS NULL
    		AND C.TXD_SR_TYPE IS NULL;
    
    
    -- 合伙人给伙伴下单
    UPDATE  C
    SET     C.TXD_SR_TYPE = 3,
            C.TXD_SR_CUSTOMER = d.CUSTOMER_ID
    FROM    CRM_SHOP_CAR C
            LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
            LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
            LEFT JOIN ( SELECT  CBI.CUSTOMER_ID ,
                                CWC.CUSTOMER_ID C_CUSTOMER_ID
                        FROM    CRM_BUSINESS_INFO CBI WITH ( NOLOCK )
                                LEFT JOIN CRM_SR_GROUP_CUSTOMER CSGCL1 WITH ( NOLOCK ) ON CBI.CUSTOMER_ID = CSGCL1.CUSTOMER_ID
                                LEFT JOIN CRM_SR_GROUP_CUSTOMER_LOG CSGCL2 WITH ( NOLOCK ) ON CSGCL2.SR_GROUP_ID = CSGCL1.SR_GROUP_ID
                                LEFT JOIN CRM_WS_CUSTOMER CWC WITH ( NOLOCK ) ON CSGCL2.CUSTOMER_ID = CWC.CUSTOMER_ID
                                LEFT JOIN CRM_POI_INFO CPI WITH ( NOLOCK ) ON CPI.POI_CODE = CWC.ORG_ID
                        WHERE   CBI.BUS_TYPE = 13
                                AND CBI.BUS_STATUS = 1
                                AND CSGCL1.STATUS = 1
                                AND CSGCL2.STATUS = 1
                                AND CWC.CUST_TITLE = '经销商'
                                AND CPI.DEALER_SOURCE_TYPE = 'X村通伙伴'
                                AND CPI.STATUS = '1'
                      ) d ON d.C_CUSTOMER_ID = C.CUSTOMER_ID
    WHERE   W.CUST_TITLE = '经销商'
            AND C.GRP = 'winretailsr'
            AND C.STATUS = 0
            AND C.PRE_ORDER_NO IS NULL
    		AND d.CUSTOMER_ID IS NOT NULL
    		AND C.TXD_SR_TYPE IS NULL;
    
    
    
    
    -- 掌柜给伙伴下单
    UPDATE  C
    SET     C.TXD_SR_TYPE = 2,
            C.TXD_SR_CUSTOMER = d.SR_CUSTOMER_ID
    FROM    CRM_SHOP_CAR C
            LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
            LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK) ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
            LEFT JOIN ( SELECT  srRelation.SR_CUSTOMER_ID ,
                                customer.CUSTOMER_ID
                        FROM    CRM_DEALER_SR_RELATIONSHIP srRelation WITH( NOLOCK )
                                INNER JOIN CRM_WS_CUSTOMER customer WITH ( NOLOCK ) ON srRelation.DEALER_ID = customer.ORG_ID
                                AND customer.CUST_TITLE = '经销商'
                                INNER JOIN CRM_POI_INFO poiInfo WITH ( NOLOCK ) ON poiInfo.POI_CODE = customer.ORG_ID
                                AND poiInfo.DEALER_SOURCE_TYPE = 'X村通伙伴'
                        WHERE   srRelation.STATUS = 1
                      ) d ON d.CUSTOMER_ID = C.CUSTOMER_ID
    WHERE   W.CUST_TITLE = '经销商'
            AND C.GRP = 'winretailsr'
            AND C.STATUS = 0
            AND C.PRE_ORDER_NO IS NULL
            AND d.CUSTOMER_ID IS NOT NULL
    		AND C.TXD_SR_TYPE IS NULL;
    		
    		
    		
    		
    UPDATE  C
    SET     C.TXD_SR_TYPE = 1,
            C.TXD_SR_CUSTOMER = I.BUS_CUSTOMER_ID
    FROM    CRM_SHOP_CAR C 
            LEFT JOIN CRM_WS_CUSTOMER W WITH(NOLOCK) ON C.CUSTOMER_ID = W.CUSTOMER_ID
            LEFT JOIN dbo.VIEW_SR_RETAIL_INFO I WITH(NOLOCK)  ON W.CUSTOMER_ID = I.RETAIL_CUSTOMER_ID
    WHERE   W.CUST_TITLE = '店主'
            AND C.GRP = 'winretailsr'
            AND C.STATUS = 0
            AND C.PRE_ORDER_NO IS NULL
        AND C.TXD_SR_TYPE IS NULL;

    针对实际的业务及系统考虑,具体问题具体分析,只要是在合理的范围内,不影响线上业务的基础上,来做程序处理,之前遇到了更为复杂的业务场景,可能通过数据库SQL脚本解决不了,需要写程序来解决。  通过博客可以把自己遇到的内容分享出来,知识共享,共同学习进步。 

    工作中可能会遇到各种问题,各种各样的情况导致最后上线前可能会匆忙的来处理事情,程序员都会遇到,只要不断经历和总结,以后会避免越来越少的中间过程,要勇敢面对一切。 不断提升自己的能力,才是硬道理。 

    展开全文
  • 存储过程写法是什么

    2021-04-09 13:21:08
    存储过程写法是:1、创建存储过程,代码为【create proc sp_name】;2、调用存储过程,代码为【exec sp_name [参数名]】。本文操作环境:Windows7系统,Microsoft Visual C++2015版本,Dell G3电脑。存储过程写法是...

    存储过程写法是:1、创建存储过程,代码为【create proc sp_name】;2、调用存储过程,代码为【exec sp_name [参数名]】。

    a3e182f0a19509d1fdf2fc580c617dbf.png

    本文操作环境:Windows7系统,Microsoft Visual C++2015版本,Dell G3电脑。

    存储过程写法是:

    1、创建存储过程create procedure sp_name

    @[参数名] [类型],@[参数名] [类型]

    as

    begin

    .........

    end

    以上格式还可以简写成:

    create proc sp_name

    @[参数名] [类型],@[参数名] [类型]

    as

    begin

    .........

    end

    /*注:“sp_name”为需要创建的存储过程的名字,该名字不可以以阿拉伯数字开头*/

    2、调用存储过程

    存储过程可以在三种环境下被调用:command命令下,基本语法为:exec sp_name [参数名];

    SQL环境下,基本语法为:call sp_name [参数名];

    PL/SQL环境下,基本语法为:begin sp_name [参数名] end;

    3、删除存储过程

    1.基本语法:drop procedure sp_name

    2.注意事项

    (1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

    4、其他常用命令

    1.show procedure status

    显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等

    2.show create procedure sp_name

    显示某一个mysql存储过程的详细信息

    3.exec sp_helptext sp_name

    显示你这个sp_name这个对象创建文本

    更多编程相关知识,请访问:编程教学!!

    展开全文
  • 存储过程写法是什么存储过程的写作是什么,存储过程的编写如下:1 .用代码[创建进程名]创建一个存储过程;2.用[EXECSP _ NAME]代码调用存储过程。操作环境:Windows7系统,微软visual c 2015版本,戴尔G3电脑。存储...

    存储过程写法是什么存储过程的写作是什么,存储过程的编写如下:1 .用代码[创建进程名]创建一个存储过程;2.用[EXECSP _ NAME]代码调用存储过程。

    a3e182f0a19509d1fdf2fc580c617dbf.png

    操作环境:Windows7系统,微软visual c 2015版本,戴尔G3电脑。

    存储过程写法是:

    1、创建存储过程

    创建过程sp_name

    @[参数名][类型],@[参数名][类型]

    作为

    开始

    ..

    结束

    以上格式也可以缩写为:

    创建进程sp_name

    @[参数名][类型],@[参数名][类型]

    作为

    开始

    ..

    结束

    /*注意:“sp_name”是要创建的存储过程的名称,不能以阿拉伯数字开头*/2、调用存储过程

    存储过程可以在三种环境中调用:

    在命令命令下,基本语法是exec sp _ name参数名];

    在SQL环境中,基本语法是:调用sp _ name参数名];

    在PL/SQL环境下,基本语法是:begin sp _ name参数名]end;

    3、删除存储过程

    1.基本语法:

    删除过程sp_name2。预防

    (1)不能在一个存储过程中删除另一个存储过程,但只能调用另一个存储过程

    4、其他常用命令

    1 .显示程序状态

    显示数据库中所有存储过程的基本信息,包括数据库、存储过程名称、创建时间等。

    2 .显示创建过程sp_name

    显示mysql存储过程的详细信息

    3.exec sp_helptext sp_name

    显示由sp_name对象创建的文本。以上是存储过程写什么的详细内容。请多关注其他关于Lei.com PHP知识的相关文章!

    展开全文
  • 过程1:带返回值: drop procedure if exists proc_addNum; create procedure proc_addNum (in x int,in y int,out sum int) BEGIN SET sum= x + y; end 然后,执行过程,out输出返回值: call proc_addNum...
  • 自己目前在用的,记录一下,方便以后进行使用 declare @myip nvarchar(100),@mypid int DECLARE My_Cursor CURSOR --定义游标 FOR (SELECT [P_Addr] ...FROM [DbYiYang].[dbo].[Store_IP_Info]) --查出需要的...
  • 此文档对oracle存储过程做了详细讲解,游标用法 变量定义,触发器,序列,DML DCL DDL
  • ---创建存储过程,本部分全部复制粘贴至查询分析器执行即可-----createprocedureget_nxt_id_P@objtypeint,@NewSeqValuebigintoutputasbeginsetNOCOUNTONif(@objtype=0)begininsertint...---创建存储过程,本部分全部...
  • 存储过程写法

    2019-10-03 06:48:17
    --创建存储过程执行删除操作ALTER PROCEDURE [dbo].[ad_preempted_timer]ASDECLARE @pid VARCHAR(32)DECLARE @times dateDECLARE @nowtime dateDECLARE @difftime INT --定义一个游标declare preempted_timer ...
  • postgreSQL存储过程写法示例

    千次阅读 2017-09-07 16:47:25
    转载自:... PostgreSQL的存储过程简单入门 http://blog.csdn.net/rachel_luo/article/details/8073458  存储过程事物 http://www.php100.com/manual/PostgreSQL8/tutorial-transactions.html
  • sqlserver中的存储过程写法及调用

    千次阅读 2020-12-15 10:38:03
    1、创建存储过程,并开启事务控制 if exists (select * from dbo.sysobjects where name = N'PROD_NAME' ) DROP PROCEDURE dbo.PROD_NAME GO CREATE PROCEDURE [dbo].[PROD_NAME] ( @parameter1 VARCHAR(10), @...
  • pl/sql存储过程写法

    2019-07-05 09:26:56
    过程使用CREATE OR REPLACE PROCEDURE语句创建,使用CREATE OR REPLACE PROCEDURE语句简化语法如下: [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END ...
  • 此文档为入门之处到别处搜来的,发现其实这个没啥用,还不能删,大家别下了。
  • 展开全部一、多数指令是相同的,包括创建和修正存储过32313133353236313431303231363533e59b9ee7ad9431333365646262程的指令。二、很多细微的指令有不同,具体如下(不仅):1 mysql支持enum,和set类型,sql server不...
  • 游标具体用法:https://www.cnblogs.com/xiaoliu66007/p/7495753.html 和 https://www.cnblogs.com/xiongzaiqiren/p/sql-cursor.html pgsql存储过程具体学习:https://www.cnblogs.com/sunalways/p/13474440.html
  • SQLServer存储过程编写

    2021-01-30 09:39:26
    Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。存储过程Procedure是...
  • MySQL存储过程写法总结

    万次阅读 2017-08-06 21:48:53
    1、创建无参存储过程。 create procedure product() begin  select * from user; end; 一条简单的存储过程创建语句,此时调用的语句为: call procedure(); ##注意,如果是在命令行下编写的话,这样的写法会出现...
  • MySQL 存储过程写法

    千次阅读 2021-05-12 16:41:06
    最近因为公司需要做将Oracle 转换为MySQL,其中包含存储过程,所以就对MySQL存储过程写法做了一些研究。 DROP PROCEDURE IF EXISTS ch_passw; CREATE PROCEDURE ch_passw( p_c_userid varchar(6), -- 输入参数的...
  • DB2存储过程写法介绍

    2011-05-27 17:46:56
    存储过程的语法以及静态游标与动态游标的介绍
  • oracle存储过程写法

    2010-05-09 15:50:35
    存储过程中实现了游标的用法,并且返回一个数据集的方式。
  • mysql 存储过程写法以及函数说明

    千次阅读 2018-11-06 16:07:41
    (1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程 四.其他常用命令 1.show procedure status 显示 数据库 中所有存储存储过程基本信息,包括所属数据库,存储过程名称,创建时间等 2...
  • 三:mybatis调用存储过程写法 <![CDATA[ { call QUOTE_TO_AGREE_COMMIT( #{AGREE_ID,mode=IN,jdbcType=VARCHAR} ) } ]]> QUOTE_TO_AGREE_COMMIT 这个应该是 存储过程的名字。 ()这个应该存储...
  • db2存储过程简单写法

    千次阅读 2019-02-28 17:22:01
    创建存储过程: db2 " CREATE OR REPLACE PROCEDURE p_company( ) begin DECLARE v_company varchar(8); FOR V AS MYCURSOR CURSOR FOR SELECT ClaimSequenceNo, userCode FROM CALClaimStatus where COMPANYCODE...
  • MySQL存储过程MySQL中,创建存储过程的基本形式如下:CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数...
  • 文档记录了SQL Server在存储运用时的语法,需要有一定的SQL语言学习基本,本文档只是做了梳理与总结,以便查看和识记。
  • 第一种情况是返回的游标是某个具体的表或视图的数据,如: SQL-Code: 代码如下: CREATE OR REPLACE PROCEDURE P_TESTA ( PRESULT OUT SYS_REFCURSOR ) AS BEGIN OPEN PRESULT FOR SELECT * FROM USERS;...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 132,291
精华内容 52,916
关键字:

存储过程写法