技术改变世界 阅读塑造人生! - shaogx.com

This string was altered by TechBlog\Plugins\Example.; This is an example to show the potential of an offcanvas layout pattern in Bootstrap. Try some responsive-range viewport sizes to see it in action.

SQL语法学习笔记『SQL语法学习笔记(二)』

语句4:SELECT  "栏位名"  FROM  "表格名"  WHERE  "简单条件" {[AND|OR] "简单条件"}+举例:{}+ 代表{}之内的情况会发生一或多次。在这里的意思就是AND加简单条件及OR加简单条件的情况可以发生一或多次。另外,我们可以用 () 来代表条件的先后次序。... 全文

SQL语法学习笔记 SQL学习 SQL语法 SQL 罗斌

SQL语法学习笔记『SQL语法学习笔记(一)』

SQL语法学习笔记 Store_Information表格store_nameSalesDateLos Angeles$1500Jan-05-1999San Diego$250Jan-07-1999San Francisco$300Jan-08-1999Boston$700Jan-08-1999 语句1:SELECT "栏位名" FROM "表格名"举例:若要选出所有的店名 (store_Name),我们就打入:... 全文

SQL语法学习笔记 SQL学习 SQL语法 SQL 数据库

SQL语法学习笔记『SQL语法学习笔记(三)』

语句6:SELECT  "栏位名"  FROM " 表格名"  WHERE  "栏位名" BETWEEN  '值一'  AND  '值二'  举例:这将选出栏位值包含在值一及值二之间的每一笔资料。... 全文

SQL语法学习笔记 罗斌 数据库 休闲 职场

hadoop hive sql语法解释

DDL Operations 创建表 hive> CREATE TABLE pokes (foo INT, bar STRING); 创建表并创建索引字段ds hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING); 显示所有表 hive> SHOW TABLES; 按正条件(正则表达式)显示表, hive> SHOW TABLES '.*s'; 表添加一列 hive> ALTER TABLE pokes ADD COLUMNS (new_col INT); 添加一列并增加列字段注释 hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment'); 更改表名 hive> ALTER TABLE events RENAME TO 3koobecaf; 删除列 hive> DROP TABLE pokes; 元数据存储 将文件中的数据加载到表中 hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes; 加载本地数据,同时给定分区信息 hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15'); 加载DFS数据 ,同时给定分区信息 hive> LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15'); The above command will load data from an HDFS file/directory to the table. Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous. SQL 操作 按先件查询 hive> SELECT a.foo FROM invites a WHERE a.ds='<DATE>'; 将查询数据输出至目录 hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>'; 将查询结果输出至本地目录 hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a; 选择所有列到本地目录 hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a; hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100; hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a; hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a; hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE a.ds='<DATE>'; hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a; hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a; 将一个表的统计结果插入另一个表中 hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar; hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar; JOIN hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo; 将多表数据插入到同一表中 FROM src INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100 INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200 INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300 INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300; 将文件流直接插入文件 hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09'; This streams the data in the map phase through the script /bin/cat (like hadoop streaming). Similarly - streaming can be used on the reduce side (please see the Hive Tutorial or examples) 实际示例 创建一个表 CREATE TABLE u_data ( userid INT, movieid INT, rating INT, unixtime STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE; 下载示例数据文件,并解压缩 wget http://www.grouplens.org/system/files/ml-data.tar__0.gz tar xvzf ml-data.tar__0.gz 加载数据到表中 LOAD DATA LOCAL INPATH 'ml-data/u.data' OVERWRITE INTO TABLE u_data; 统计数据总量 SELECT COUNT(1) FROM u_data; 现在做一些复杂的数据分析 创建一个 weekday_mapper.py: 文件,作为数据按周进行分割 import sys import datetimefor line in sys.stdin: line = line.strip() userid, movieid, rating, unixtime = line.split('\t') 生成数据的周信息 weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday() print '\t'.join([userid, movieid, rating, str(weekday)]) 使用映射脚本 //创建表,按分割符分割行中的字段值 CREATE TABLE u_data_new ( userid INT, movieid INT, rating INT, weekday INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; //将python文件加载到系统 add FILE weekday_mapper.py; 将数据按周进行分割 INSERT OVERWRITE TABLE u_data_new SELECT TRANSFORM (userid, movieid, rating, unixtime) USING 'python weekday_mapper.py' AS (userid, movieid, rating, weekday) FROM u_data;... 全文

sql hadoop sql语法解释 hive 数据库

DB2 SQL存储过程语法“导航仪”

此文主要描述的是正确创建DB2 SQL存储过程(CREATE PROCEDURE (SQL) statement )的实际操作步骤,首先我们是从语法格式开始的,同时也有对其语法说明的描述,以下就是文章的主要内容讲述。语法格式如下:... 全文

DB2 SQL存储过程

SQL Server数据库DATEPART的语法及使用实例

SQL Server数据库DATEPART可以返回指定date的指定datepart的字符串,本文我们主要就介绍了它的语法,并给出了使用实例,接下来我们就开始介绍这部分内容,首先我们先看一下它的语法。1.语法DATEPART (datepart , date )参数:datepart是将为其返回integer的date(日期或时间值)的一部分。下表列出了所有有效的datepart参数。用户定义的变量等效项是无效的。datepart缩写yearyy ,yyyyquarterqq ,qmonthmm , mdayofyeardy ,ydaydd ,dweekwk ,ww... 全文

SQL Server DATEPART

SQL Server数据库DATEADD的语法介绍及使用实例

之前我们介绍了:SQL Server数据库ISNULL函数的应用实例,本文我们介绍一下DATEADD函数的语法介绍及使用实例,接下来就让我们一起来了解一下这部分内容。1.语法DATEADD (datepart , number , date )2.参数... 全文

SQL Server DATEADD

EditPlus支持SQL突出显示,Shane版,市面最佳

最近要编写一批存储过程,文本编辑器使用 EditPlus3 。EditPlus 本身主打文本编辑、HTML + PHP 编辑,至 2012 年中还没有提供官方对 SQL 文件的语法高亮显示支持,需要自己编写语法高亮显示文件。市面上能找到很多资料,我看了接近十份明面上来源、时间各不一样的资料,全部都是来自一个叫 KK 之人制作的高亮显示文件。此文件没有区分各种关键字的不同,而且竟然将双引号认定为字符串包括符。于是我重写了一份 sql.stx 文件,改善了发现的上述问题。 此文件后边附上内容,文件本身的下载页面地址为: http://download.csdn.net/detail/shanelooli/4565071 拿到文件之后,将文件放在一个确定的位置,不要放在桌面这些临时位置。然后在 EditPlus 的菜单栏选择 工具(T) -> 配置用户工具(C)...   点击之后会出现如下界面:... 全文

EditPlus SQL 高亮 语法 突出

sql语法:inner join on, left join on, right join on详细使用方法

1 .WHERE子句中使用的连接语句,在数据库语言中,被称为隐性连接。INNER JOIN……ON子句产生的连接称为显性连接。(其他JOIN参数也是显性连接)WHERE 和INNER JOIN产生的连接关系,没有本质区别,结果也一样。但是!隐性连接随着数据库语言的规范和发展,已经逐渐被淘汰,比较新的数据库语言基本上已经抛弃了隐性连接,全部采用显性连接了。 2 .无论怎么连接,都可以用join子句,但是连接同一个表的时候,注意要定义别名,否则产生错误! a>inner join:理解为“有效连接”,两张表中都有的数据才会显示left join:理解为“有左显示”,比如on a.field=b.field,则显示a表中存在的全部数据及a\\b中都有的数据,A中有、B没有的数据以null显示 b>right join:理解为“有右显示”,比如on a.field=b.field,则显示B表中存在的全部数据及a\\b中都有的数据,B中有、A没有的数据以null显示 c>full join:理解为“全连接”,两张表中所有数据都显示,实际就是inner +(left-inner)+(right-inner) 3 .join可以分主次表外联接有三种类型:完全外联,左联,右联. 完全外联包含两张表的所有记录.左联是以左边的表为主,右边的为辅,右联则相反  sql语句中left join、inner join中的on与where的区别 table a(id, type):... 全文

数据库 sql

DB2 SQL存储过程语法官方权威指南(翻译)

DB2 SQL存储过程语法官方权威指南(翻译)  作者:熔岩时间:2006-12-21类别:翻译原文:在本文最后给出MSN :leizhimin@126.com声明:本人发表此文的目的是希望更多的读者和我交流,译著未经授权拒绝转载,对翻译中的错误本人不承担任何责任。原著作权归IBM公司所有!  背景:DB2的数据库性能很牛X,但是其文档却很差,尤其是开发参考文档,都是英文的,浏览的时候还很不好找,需要上IBM的网站看,网站也出奇的慢,极不方便,让开发人员举步维艰,这也许是IBM DB2的用户少,书少,资料少的原因。本人为了搞明白DB2存SQL的完整语法介绍,找遍了WWW,没有发现一篇完整介绍DB2语法的文章,现我根据自己的理解,将IBM对DB2 SQL存储过程的英文翻译下来,与各位网友进行交流,由于时间的限制,没有一字一句的去翻译,只对最核心的语法说明做了简单的翻译工作。我的E文也很差,错误在所难免,欢迎各位给予斧正! 译著------------------创建SQL存储过程(CREATE PROCEDURE (SQL) statement ) 语法格式如下: >>-CREATE PROCEDURE--procedure-name----------------------------->>--+----------------------------------------------------+--*---->   '-(--+------------------------------------------+--)-'        | .-,------------------------------------. |        | V .-IN----.                            | |        '---+-------+--parameter-name--data-type-+-'            +-OUT---+            '-INOUT-'>--+-------------------------+--*------------------------------->   '-SPECIFIC--specific-name-'   .-DYNAMIC RESULT SETS 0--------.     .-MODIFIES SQL DATA-.>--+------------------------------+--*--+-------------------+--->   '-DYNAMIC RESULT SETS--integer-'     +-CONTAINS SQL------+                                        '-READS SQL DATA----'      .-NOT DETERMINISTIC-.     .-CALLED ON NULL INPUT-.>--*--+-------------------+--*--+----------------------+--*----->      '-DETERMINISTIC-----'   .-INHERIT SPECIAL REGISTERS-.     .-7 OLD SAVEPOINT LEVEL-.>--+---------------------------+--*--+---------------------+---->                                     '-7 NEW SAVEPOINT LEVEL-'      .-LANGUAGE SQL-.     .-7 EXTERNAL ACTION----.>--7 *--+--------------+--*--+--------------------+--*------------>                           '-7 NO EXTERNAL ACTION-'>--+------------------------------+--3 *-------------------------->   '-3 PARAMETER CCSID--+-3 ASCII---+-'                      '-3 UNICODE-'>--| SQL-procedure-body |--------------------------------------><SQL-procedure-body:|--SQL-procedure-statement--------------------------------------| 语法说明1、procedure-name: 存储过程的名字,在同一个数据库的同一模式下,不能存在存储过程名相同参数数目相同的存储过程,即使参数的类型不同也不行。  2、(IN | OUT | INOUT parameter-name data-type,...) :传入参数    IN:输入参数 OUT:输出参数 INOUT:作为输入输出参数    parameter-name:参数名字,在此存储过程中唯一的标识符。 data-type:参数类型,可以接收SQL类型和创建的表。不支持LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。3、SPECIFIC specific-name:唯一的特定名称(别名),可以用存储过程名代替,这个特定名称用于dorp存储过程,或者给存储过程添加注视用,但不能调用存储过程。如果不指定,则数据库会自动生成一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。4、DYNAMIC RESULT SETS integer:指定存储过程返回结果的最大数量。存储过程中虽然没有return语句,但是却能返回结果集。5、CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA: 指定存储过程中的SQL访问级别    CONTAINS SQL: 表示存储过程可以执行中,既不可读取 SQL 数据,也不可修改 SQL 数据。    READS SQL DATA: 表示存储过程可以执行中,可读取SQL,但不可修改 SQL 数据。    MODIFIES SQL DATA: 表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改。  6、DETERMINISTIC or NOT DETERMINISTIC:表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回的值是相同的。7、CALLED ON NULL INPUT:表示可以调用存储过程而不管任何的输入参数是否为NULL,并且,任何的OUT或者INOUT参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。8、INHERIT SPECIAL REGISTERS:表示继承专用寄存器。9、OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL:建立存储点。OLD SAVEPOINT LEVEL是默认的存储点。10、LANGUAGE SQL:指定程序的主体用的是SQL语言。11、EXTERNAL ACTION or NO EXTERNAL ACTION:表示存储过程是否执行一些改变理数据库状态的活动,而不通过数据库管理器管。默认是 EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案。12、PARAMETER CCSID:指定所有输出字符串数据的编码,默认为UNICODE编码数据库为PARAMETER CCSID UNICODE ,其他的数据库默认为PARAMETER CCSID 3 ASCII。13、SQL-procedure-body:存储过程的主体例子1:产生一个SQL存储过程,返回员工的平均薪水. 返回所有员工超过平均薪水的数额,结果集包括name, position, and salary字段(参考数据库为db2的示例数据库sample)。    CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)     RESULT SETS 1     LANGUAGE SQL   BEGIN     DECLARE v_numRecords INT DEFAULT 1;     DECLARE v_counter INT DEFAULT 0;     DECLARE c1 CURSOR FOR       SELECT CAST(salary AS DOUBLE)         FROM staff         ORDER BY salary;     DECLARE c2 CURSOR WITH RETURN FOR       SELECT name, job, CAST(salary AS INTEGER)         FROM staff         WHERE salary > medianSalary         ORDER BY salary;     DECLARE EXIT HANDLER FOR NOT FOUND       SET medianSalary = 6666;     SET medianSalary = 0;     SELECT COUNT(*) INTO v_numRecords       FROM STAFF;     OPEN c1;     WHILE v_counter < (v_numRecords / 2 + 1)     DO       FETCH c1 INTO medianSalary;       SET v_counter = v_counter + 1;     END WHILE;     CLOSE c1;     OPEN c2;   END  --------------------原文如下: CREATE PROCEDURE (SQL) statement The CREATE PROCEDURE (SQL) statement defines an SQL procedure at the current server. Invocation This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509). Authorization The privileges held by the authorization ID of the statement must include at least one of the following: 2 BINDADD privilege on the database, and one of the 2 following: 2 2 2 IMPLICIT_SCHEMA privilege on the database, if the implicit 2 or explicit schema name of the procedure does not exist 2 CREATEIN privilege on the schema, if the schema name of the 2 procedure refers to an existing schema SYSADM or DBADM authority If the authorization ID of the statement does not have SYSADM or DBADM authority, the privileges held by the authorization ID of the statement must also include all of the privileges necessary to invoke the SQL statements that are specified in the procedure body. Syntax >>-CREATE PROCEDURE--procedure-name----------------------------->>--+----------------------------------------------------+--*---->   '-(--+------------------------------------------+--)-'        | .-,------------------------------------. |        | V .-IN----.                            | |        '---+-------+--parameter-name--data-type-+-'            +-OUT---+            '-INOUT-'>--+-------------------------+--*------------------------------->   '-SPECIFIC--specific-name-'   .-DYNAMIC RESULT SETS 0--------.     .-MODIFIES SQL DATA-.>--+------------------------------+--*--+-------------------+--->   '-DYNAMIC RESULT SETS--integer-'     +-CONTAINS SQL------+                                        '-READS SQL DATA----'      .-NOT DETERMINISTIC-.     .-CALLED ON NULL INPUT-.>--*--+-------------------+--*--+----------------------+--*----->      '-DETERMINISTIC-----'   .-INHERIT SPECIAL REGISTERS-.     .-7 OLD SAVEPOINT LEVEL-.>--+---------------------------+--*--+---------------------+---->                                     '-7 NEW SAVEPOINT LEVEL-'      .-LANGUAGE SQL-.     .-7 EXTERNAL ACTION----.>--7 *--+--------------+--*--+--------------------+--*------------>                           '-7 NO EXTERNAL ACTION-'>--+------------------------------+--3 *-------------------------->   '-3 PARAMETER CCSID--+-3 ASCII---+-'                      '-3 UNICODE-'>--| SQL-procedure-body |--------------------------------------><SQL-procedure-body:|--SQL-procedure-statement--------------------------------------|Description procedure-name Names the procedure being defined. It is a qualified or unqualified name that designates a procedure. The unqualified form of procedure-name is an SQL identifier (with a maximum length of 128). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifiers, together with the number of parameters, must not identify a procedure described in the catalog (SQLSTATE 42723). The unqualified name, together with the number of parameters, is unique within its schema, but does not need to be unique across schemas. If a two-part name is specified, the schema-name cannot begin with 'SYS'; otherwise, an error is returned (SQLSTATE 42939). (IN | OUT | INOUT parameter-name data-type,...) Identifies the parameters of the procedure, and specifies the mode, name, and data type of each parameter. One entry in the list must be specified for each parameter that the procedure will expect. It is possible to register a procedure that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example:    CREATE PROCEDURE SUBWOOFER() ... No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. A duplicate signature raises an SQL error (SQLSTATE 42723). For example, given the statements:    CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...   CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ... the second statement will fail because the number of parameters in the procedure is the same, even if the data types are not. IN | OUT | INOUT Specifies the mode of the parameter. If an error is returned by the procedure, OUT parameters are undefined and INOUT parameters are unchanged. IN Identifies the parameter as an input parameter to the procedure. Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned. The default is IN. OUT Identifies the parameter as an output parameter for the procedure. INOUT Identifies the parameter as both an input and output parameter for the procedure. parameter-name Specifies the name of the parameter. The parameter name must be unique for the procedure (SQLSTATE 42734). data-type Specifies the data type of the parameter. SQL data type specifications and abbreviations that can be specified in the data-type definition of a CREATE TABLE statement, and that have a correspondence in the language that is being used to write the procedure, may be specified. 1 LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE, and 1 user-defined structured types are not supported (SQLSTATE 429BB). SPECIFIC specific-name Provides a unique name for the instance of the procedure that is being defined. This specific name can be used when dropping the procedure or commenting on the procedure. It can never be used to invoke the procedure. The unqualified form of specific-name is an SQL identifier (with a maximum length of 18). The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another procedure instance that exists at the application server; otherwise an error (SQLSTATE 42710) is raised. The specific-name can be the same as an existing procedure-name. If no qualifier is specified, the qualifier that was used for procedure-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier for procedure-name, or an error (SQLSTATE 42882) is raised. If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmsshhn. DYNAMIC RESULT SETS integer Indicates the estimated upper bound of returned result sets for the procedure. CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA Indicates the level of data access for SQL statements included in the procedure. CONTAINS SQL Indicates that SQL statements that neither read nor modify SQL data can be executed by the procedure (SQLSTATE 38004 or 42985). Statements that are not supported in any procedure return a different error (SQLSTATE 38003 or 42985). READS SQL DATA Indicates that some SQL statements that do not modify SQL data can be included in the procedure (SQLSTATE 38002 or 42985). Statements that are not supported in any procedure return a different error (SQLSTATE 38003 or 42985). MODIFIES SQL DATA Indicates that the procedure can execute any SQL statement except statements that are not supported in procedures (SQLSTATE 38003 or 42985). DETERMINISTIC or NOT DETERMINISTIC This clause specifies whether the procedure always returns the same results for given argument values (DETERMINISTIC) or whether the procedure depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC procedure must always return the same result from successive invocations with identical inputs. This clause currently does not impact processing of the procedure. CALLED ON NULL INPUT CALLED ON NULL INPUT always applies to procedures. This means that the procedure is called regardless of whether any arguments are null. Any OUT or INOUT parameter can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the procedure. INHERIT SPECIAL REGISTERS This optional clause specifies that updatable special registers in the procedure will inherit their initial values from the environment of the invoking statement. For a routine invoked in a nested object (for example a trigger or view), the initial values are inherited from the runtime environment (not inherited from the object definition). No changes to the special registers are passed back to the caller of the procedure. Non-updatable special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore set to their default values. 7 7 OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL 7 Specifies whether or not this procedure establishes a 7 new savepoint level for savepoint names and effects. 7 OLD SAVEPOINT LEVEL is the default behavior. 7 For more information about savepoint levels, see the "Rules" 7 section in the description of the SAVEPOINT statement. 7 LANGUAGE SQL This clause is used to specify that the procedure body is written in the SQL language. 7 7 EXTERNAL ACTION or NO EXTERNAL ACTION 7 Specifies whether the procedure takes some action that changes 7 the state of an object not managed by the database manager (EXTERNAL 7 ACTION), or not (NO EXTERNAL ACTION). 7 The default is EXTERNAL ACTION. 7 If NO EXTERNAL ACTION is specified, the system can use certain 7 optimizations that assume the procedure has no external impact. 7 3 3 PARAMETER CCSID 3 Specifies the encoding scheme to use for all string data 3 passed into and out of the procedure. 3 If the PARAMETER CCSID clause is not specified, the default is 3 PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID 3 ASCII for all other databases. 3 3 3 ASCII 3 Specifies that string data is encoded in the database 3 code page. 3 If the database is a Unicode database, PARAMETER CCSID ASCII cannot 3 be specified (SQLSTATE 56031). 3 3 UNICODE 3 Specifies that character data is in UTF-8, and that graphic 3 data is in UCS-2. 3 If the database is not a Unicode database, PARAMETER CCSID UNICODE 3 cannot be specified (SQLSTATE 56031). 3 3 3 SQL-procedure-body Specifies the SQL statement that is the body of the SQL procedure. Multiple SQL-procedure-statements can be specified within a procedure-compound-statement. See SQL-procedure-statement in the description of the Compound SQL (Procedure) statement. 7 7 Rules 7 7 A procedure that is called from within a dynamic compound 7 statement will execute as if it were created specifying NEW SAVEPOINT 7 LEVEL, even if OLD SAVEPOINT LEVEL was specified or defaulted to when 7 the procedure was created. Notes Creating a procedure with a schema name that does not already exist will result in the implicit creation of that schema, provided that the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC. Privileges The definer of a procedure always receives the EXECUTE privilege WITH GRANT OPTION on the procedure, as well as the right to drop the procedure. Compatibilities For compatibility with DB2 UDB for OS/390 and z/OS: The following syntax is accepted as the default behavior: ASUTIME NO LIMIT COMMIT ON RETURN NO NO COLLID STAY RESIDENT NO For compatibility with previous versions of DB2: RESULT SETS can be specified in place of DYNAMIC RESULT SETS. NULL CALL can be specified in place of CALLED ON NULL INPUT. Examples Example 1: Create an SQL procedure that returns the median staff salary. Return a result set containing the name, position, and salary of all employees who earn more than the median salary.    CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)     RESULT SETS 1     LANGUAGE SQL   BEGIN     DECLARE v_numRecords INT DEFAULT 1;     DECLARE v_counter INT DEFAULT 0;     DECLARE c1 CURSOR FOR       SELECT CAST(salary AS DOUBLE)         FROM staff         ORDER BY salary;     DECLARE c2 CURSOR WITH RETURN FOR       SELECT name, job, CAST(salary AS INTEGER)         FROM staff         WHERE salary > medianSalary         ORDER BY salary;     DECLARE EXIT HANDLER FOR NOT FOUND       SET medianSalary = 6666;     SET medianSalary = 0;     SELECT COUNT(*) INTO v_numRecords       FROM STAFF;     OPEN c1;     WHILE v_counter < (v_numRecords / 2 + 1)     DO       FETCH c1 INTO medianSalary;       SET v_counter = v_counter + 1;     END WHILE;     CLOSE c1;     OPEN c2;   END Related reference SAVEPOINT statement Compound SQL (Procedure) statement SQL statements allowed in routines Special registers Related samples SQL procedures basecase.db2 -- To create the UPDATE_SALARY SQL procedure nestcase.db2 -- To create the BUMP_SALARY SQL procedure nestedsp.db2 -- To create the OUT_AVERAGE, OUT_MEDIAN and MAX_SALARY SQL procedures rsultset.db2 -- To register and create the MEDIAN_RESULT_SET SQL procedure This topic can be found in: SQL Reference, Volume 2.  ... 全文

DB2 SQL 存储 存储过程 数据库

SELECT 语句基本语法简介

  从 SQL Server 数据库中提取所需的数据,这是用户日常的重要操作。   SELECT语句是 T-SQL 中最重要的查询语句。SELECT 语句可能返回一行记录(record),也可能返回一个结果集(result set)。   SELECT语句可以包含多个子句,从而构成复杂的查询。常用的基本子句有以下几个: 1. SELECT 子句,定义返回哪些列。 2. FROM 子句,定义查询哪些表。 3. WHERE 子句,使用谓词对行进行过滤。 4. GROUP BY 子句,对行进行分组。 5. HAVING 子句,使用谓词对分组后的行进行过滤。 6. ORDER BY 子句,对输出进行排序。... 全文

SQL SELECT

教你如何利用MySQL学习MongoDB之SQL语法

在上文中,我们了解了教你如何利用MySQL学习MongoDB之数据存储结构,本文中我们继续我们的学习之旅,学习两者的命令行工具及SQL语法。... 全文

MySQL MongoDB

SQL语句教程(07) LIKE

 SQL语句教程(07) LIKELIKE 是另一个在 WHERE 子句中会用到的指令。基本上,LIKE 能让我们依据一个套式 (pattern) 来找出 我们要的资料。相对来说,在运用 IN 的时候,我们完全地知道我们需要的条件;在运用 BETWEEN 的时 候,我们则是列出一个范围。 LIKE 的语法如下: SELECT "栏位名" FROM "表格名" WHERE "栏位名" LIKE {套式}{套式} 经常包括野卡 (wildcard). 以下是几个例子:... 全文

语法 数据库 资料 休闲 职场

如何快速获知需要的SPS Sql Search语法

当我们在SPS中使用QueryProvider、或者调用SPS Search Web Service接口,在自己的代码里面调用SPS的检索功能的时候,通常会头疼如何写出正确语法的SPS Search Sql语法。其实一个简单好用的方法是,首先直接在SPS页面上使用自带的高级搜索页面按照自己想要的条件进行搜索,然后在结果页面上,查看页面的HTML源码,然后搜索一下一个名字叫做“schspssSQPH”的hidden field,这个field里面的值就是你需要的正确的检索语句。当然,你还需要把一些编码后的特殊字符重新替换过来,比如将“"”重新换成双引号。 另,想在SPS 2003服务器上安装Windows 2003 SP1的朋友注意了。如果在您的部署模式中,SqlServer 2000和SPS 2003并非部署在一台服务器上,那么如果在安装了SPS 2003的Windows 2003服务器上安装Windows 2003 SP1后,SP1默认会阻止SPS 2003访问SqlServer 2000服务器。关于这个问题,已经有一个SPS 2003 hotfix(kb:885263)被发布出来了。本文出自 “Kaneboy的博客” 博客,请务必保留此出处http://kaneboy.blog.51cto.com/1308893/281324... 全文

SPS 数据库 休闲 职场

1