1.1. 训练描述 使用游标,打印emp中20号部门的所有员工的信息 操作步骤答案 declare cursor c_emp is select * from emp where deptno=10; v_row emp%rowtype; begin open c_emp; loop fetch c_emp into v_row; exit when c_emp%notfound; dbms_output.put_line(v_row.empno||':'||v_row.ename); end
创建存储过程 DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_test1`; CREATE PROCEDURE sp_test1(IN a int, IN b int, OUT sum int ) BEGIN DECLARE c int; if a is null then set a = 0; end if; if b is null then set b = 0; end if; set sum = a + b; END; $$ DELIMITER ; 查
通过嵌套,根据表中记录的表名与列名到指定位置取值. --声明存储过程 ProPIMS if (exists (select * from sys.objects where name = 'ProPIMS')) drop proc ProPIMS go create procedure ProPIMS ),) AS BEGIN ),),),),),@Columncount int; if exists(select * from tempdb..sysobjects where id=objec
018_Proc_UpdateTranslations.sql: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO if (exists (select * from sys.objects where name = 'UpdateTranslationsForTable')) drop proc [UpdateTranslationsForTable] go CREATE PROCEDURE [dbo].[UpdateTranslationsFo
1. 存储过程格式 /* Formatted on 2011/1/17 13:20:44 (QP5 v5.115.810.9015) */ CREATE OR REPLACE procedure proc_trade( v_tradeid in number, --交易id v_third_ip in varchar2, --第三方ip v_third_time in date ,
1.函数 create or replace function get_Destroy_no return varchar2 is Result varchar2(50);begin SELECT max(destroy_no) INTO RESULT FROM t_oms_device_destroy WHERE substr(destroy_no,0,8)= to_char( SYSDATE ,'yyyymmdd'); IF (RESULT =''OR RESULT IS NULL) THE
方法一: SELECT TOP 页大小 * FROM table1 WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) ORDER BY id 方法二: SELECT TOP 页大小 * FROM table1 WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) A ) O
有关分页 SQL 的资料很多,有的使用存储过程,有的使用游标.本人不喜欢使用游标,我觉得它耗资.效率低:使用存储过程是个不错的选择,因为存储过程是经过预编译的,执行效率高,也更灵活.先看看单条 SQL 语句的分页 SQL 吧. 方法1:适用于 SQL Server 2000/2005 SELECT TOP 页大小 *FROM table1WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER B
转自:http://www.ljf.cn/archives/2281 Access实现分页其实也可以在查询语句里面做. 除了流行的top分页法之外,其实还有一种更简单,更容易理解,并且效率也不低的分页法. 先来一段传统的top分页法. SELECT TOP 10 *FROM TestTableWHERE (ID >=(SELECT MAX(id)FROM (SELECT TOP 21 idFROM TestTableORDER BY id) AS T))ORDER BY ID 原理: SELEC