华为云用户手册

PG_AGGREGATE PG_AGGREGATE系统表存储与聚集函数有关的信息。PG_AGGREGATE里的每条记录都是一条pg_proc里面的记录的扩展。PG_PROC记录承载该聚集的名字、输入和输出数据类型，以及其它一些和普通函数类似的信息。 表1 PG_AGGREGATE字段 名字 类型 引用 描述 aggfnoid regproc PG_PROC.oid 此聚集函数的PG_PROC OID。 aggtransfn regproc PG_PROC.oid 转换函数。 aggcollectfn regproc PG_PROC.oid 收集函数。 aggfinalfn regproc PG_PROC.oid 最终处理函数（如果没有则为0）。 aggsortop oid PG_OPERATOR.oid 关联排序操作符（如果没有则为0）。 aggtranstype oid PG_TYPE.oid 此聚集函数的内部转换（状态）数据的数据类型。 agginitval text - 转换状态的初始值。这是一个文本数据域，它包含初始值的外部字符串表现形式。如果数据域是null，则转换状态值从null开始。 agginitcollect text - 收集状态的初始值。这是一个文本数据域，它包含初始值的外部字符串表现形式。如果数据域是null，则收集状态值从null开始。 父主题： 系统表

GS_WLM_USER_RESOURCE_HISTORY GS_WLM_USER_RESOURCE_HISTORY系统表存储与用户使用资源相关的信息，该表在CN和DN上均存有数据。该系统表的每条记录都是对应时间点某用户的资源使用情况，包括：内存、CPU核数、存储空间、临时空间、算子落盘空间、逻辑IO流量、逻辑IO次数和逻辑IO速率信息。其中，内存、CPU、IO相关监控项仅记录用户复杂作业的资源使用情况。 GS_WLM_USER_RESOURCE_HISTORY系统表的数据来源于PG_TOTAL_USER_RESOURCE_INFO视图。 表1 GS_WLM_USER_RESOURCE_HISTORY字段 名称 类型 描述 username text 用户名。 timestamp timestamp with time zone 时间戳。 used_memory int 用户使用的内存大小，单位：MB。 DN：显示当前DN上用户使用的内存大小。 CN：显示所有DN上用户使用内存的累积和。 total_memory int 资源池使用的内存大小，单位：MB。值为0表示未限制最大可用内存，其限制取决于数据库最大可用内存max_dynamic_memory。具体的计算公式为： total_memory = max_dynamic_memory * parent_percent * user_percent CN：显示所有DN上用户可用内存上限的累积和。 used_cpu real 正在使用的CPU核数。 total_cpu int 该机器节点上，用户关联控制组的CPU核数总和。 used_space bigint 已使用的存储空间大小，单位KB。 total_space bigint 可使用的存储空间大小，单位KB，值为-1表示未限制最大存储空间。 used_temp_space bigint 已使用的临时存储空间大小，单位KB。 total_temp_space bigint 可使用的临时存储空间大小，单位KB，值为-1表示未限制最大临时存储空间。 used_spill_space bigint 已使用的算子落盘存储空间大小，单位KB。 total_spill_space bigint 可使用的算子落盘存储空间大小，单位KB，值为-1表示未限制最大算子落盘存储空间。 read_kbytes bigint 监控周期内，读操作的字节流量，单位KB。 write_kbytes bigint 监控周期内，写操作的字节流量，单位KB。 read_counts bigint 监控周期内，读操作的次数，单位次。 write_counts bigint 监控周期内，写操作的次数，单位次。 read_speed real 监控周期内，读操作的字节速率，单位KB/s。 write_speed real 监控周期内，写操作的字节速率，单位KB/s。 send_speed double 监控周期内，网络发送速率，单位KB/s。 recv_speed double 监控周期内，网络接收速率，单位KB/s。 父主题： 系统表

GS_WLM_SESSION_INFO GS_WLM_SESSION_INFO系统表显示所有CN执行作业结束后的负载管理记录。此数据是从内核中转储到系统表中的数据。当设置GUC参数enable_resource_record为on时，系统会定时将GS_WLM_SESSION_HISTORY中的记录导入此系统表，开启此功能会占用系统存储空间并对性能有一定影响，建议在性能定位及监控任务完成后及时关闭。 GS_WLM_SESSION_INFO系统表的schema为dbms_om。 GS_WLM_SESSION_INFO系统表仅支持在postgres数据库中查询，其它数据库中查询会直接报错。 GS_WLM_SESSION_INFO系统表的字段同表1相同，具体字段内容如下： 表1 GS_WLM_SESSION_HISTORY字段 名称 类型 描述 datid oid 连接后端的数据库OID。 dbname text 连接后端的数据库名称。 schemaname text 模式名。 nodename text 语句执行的CN名称。 username text 连接到后端的用户名。 application_name text 连接到后端的应用名。 client_addr inet 连接到后端的客户端的IP地址。 如果此字段是null，它表明通过服务器机器上UNIX套接字连接客户端或者这是内部进程，如autovacuum。 client_hostname text 客户端的主机名，这个字段是通过client_addr的反向DNS查找得到。这个字段只有在启动log_hostname且使用IP连接时才非空。 client_port integer 客户端用于与后端通讯的TCP端口号，如果使用Unix套接字，则为-1。 query_band text 用于标识作业类型，可通过GUC参数query_band进行设置，默认为空字符串。 block_time bigint 语句执行前的阻塞时间，包含语句解析和优化时间，单位ms。 start_time timestamp with time zone 语句执行的开始时间。 finish_time timestamp with time zone 语句执行的结束时间。 duration bigint 语句实际执行的时间，单位ms。 estimate_total_time bigint 语句预估执行时间，单位ms。 status text 语句执行结束状态：正常为finished，异常为aborted。该处记录的语句状态应为数据库服务端执行状态，当服务器端执行成功，结果集返回时报错，该语句应为finished。 abort_info text 语句执行结束状态为aborted时显示异常信息。 resource_pool text 用户使用的资源池。 control_group text 语句所使用的Cgroup。 estimate_memory integer 语句在单个实例上预估使用的内存，单位MB。 min_peak_memory integer 语句在所有DN上的最小内存峰值，单位MB。 max_peak_memory integer 语句在所有DN上的最大内存峰值，单位MB。 average_peak_memory integer 语句执行过程中的内存使用平均值，单位MB。 memory_skew_percent integer 语句各DN间的内存使用倾斜率。 spill_info text 语句在所有DN上的下盘信息： None：所有DN均未下盘。 All：所有DN均下盘。 [a:b]：数量为b个DN中有a个DN下盘。 min_spill_size integer 若发生下盘，所有下盘DN的最小下盘数据量(MB)，默认为0。 max_spill_size integer 若发生下盘，所有下盘DN的最大下盘数据量(MB)，默认为0。 average_spill_size integer 若发生下盘，所有下盘DN的平均下盘数据量(MB)，默认为0。 spill_skew_percent integer 若发生下盘，DN间下盘倾斜率。 min_dn_time bigint 语句在所有DN上的最小执行时间，单位ms。 max_dn_time bigint 语句在所有DN上的最大执行时间，单位ms。 average_dn_time bigint 语句在所有DN上的平均执行时间，单位ms。 dntime_skew_percent integer 语句在各DN间的执行时间倾斜率。 min_cpu_time bigint 语句在所有DN上的最小CPU时间，单位ms。 max_cpu_time bigint 语句在所有DN上的最大CPU时间，单位ms。 total_cpu_time bigint 语句在所有DN上的CPU总时间，单位ms。 cpu_skew_percent integer 语句在DN间的CPU时间倾斜率。 min_peak_iops integer 语句在所有DN上的每秒最小IO峰值（列存单位是次/s，行存单位是万次/s）。 max_peak_iops integer 语句在所有DN上的每秒最大IO峰值（列存单位是次/s，行存单位是万次/s）。 average_peak_iops integer 语句在所有DN上的每秒平均IO峰值（列存单位是次/s，行存单位是万次/s）。 iops_skew_percent integer 语句在DN间的IO倾斜率。 warning text 主要显示如下几类告警信息以及SQL自诊断调优相关告警： Spill file size large than 256MB Broadcast size large than 100MB Early spill Spill times is greater than 3 Spill on memory adaptive Hash table conflict queryid bigint 语句执行使用的内部query id。 query text 执行的语句，最多可保留64KB长度的字符串。 query_plan text 语句的执行计划。 规格限制： DML语句都会显示执行计划，DDL语句不显示执行计划。 当用户下发PBE(Parse Bind Execute)批处理语句时，为了便于分析语句情况，自8.2.1.100集群版本开始，为批处理的PBE语句的执行计划添加数据绑定次数，显示为“PBE bind times：次数”格式。 node_group text 语句所属用户对应的逻辑集群。 pid bigint 语句的后端线程的pid。 lane text 语句执行时所在的快慢车道。 unique_sql_id bigint 归一化的Unique SQL ID。 session_id text 在数据库系统中唯一标记一个session，格式：session_start_time.tid.node_name。 min_read_bytes bigint 语句在所有DN上的最小IO读字节数，单位Bytes。 max_read_bytes bigint 语句在所有DN上的最大IO读字节数，单位Bytes。 average_read_bytes bigint 语句在所有DN上的平均IO读字节数，单位Bytes。 min_write_bytes bigint 语句在所有DN上的最小IO写字节数，单位Bytes。 max_write_bytes bigint 语句在所有DN上的最大IO写字节数，单位Bytes。 average_write_bytes bigint 语句在所有DN上的平均IO写字节数，单位Bytes。 recv_pkg bigint 语句在所有DN上的通信包接收总量，单位packages。 send_pkg bigint 语句在所有DN上的通信包发送总量，单位packages。 recv_bytes bigint 语句在所有DN上的通信流接收数据总量，单位Byte。 send_bytes bigint 语句在所有DN上的通信流发送数据总量，单位Byte。 stmt_type text 语句对应的查询类型。 except_info text 语句触发的异常规则信息。 unique_plan_id bigint 归一化的Unique plan id。 sql_hash text 归一化的sql hash。 plan_hash text 归一化的plan hash。 use_plan_baseline text 当前语句执行是否使用了绑定的计划。如果使用了，则显示pg_plan_baseline中的plan_baseline列的名字。 outline_name text 该语句计划对应的outline的名字。 loader_status text 保存导入导出类业务信息的json串具体如下。 address：互联互通对端集群的地址，源集群会显示端口号。 direction：导入导出业务类型，取值包括gds to file、gds from file、gds to pipe、gds from pipe、copy from、copy to。 min/max/total_lines/bytes：导入导出语句在所有DN上字节数的行数/最小值/最大值/总和。 parse_time bigint 语句排队前的解析总时间（包含词法语法解析，优化重写和计划生成时间），单位ms。该字段仅8.3.0.100及以上集群版本支持。 disk_cache_hit_ratio numeric(5,2) 磁盘缓存命中率 。该字段仅对存算分离3.0表及外表生效。 disk_cache_disk_read_size bigint 读取磁盘缓存数据的总大小，单位MB。该字段仅对存算分离3.0表及外表生效。 disk_cache_disk_write_size bigint 写入磁盘缓存的数据总大小，单位MB。该字段仅对存算分离3.0表及外表生效。 disk_cache_remote_read_size bigint 读取磁盘缓存失败，远程直读OBS的总大小，单位MB。该字段仅对存算分离3.0表及外表生效。 disk_cache_remote_read_time bigint 读取磁盘缓存失败，远程直读OBS的次数。该字段仅对存算分离3.0表及外表生效。 vfs_scan_bytes bigint OBS虚拟文件系统接收到上层请求的扫描的字节数，单位Bytes。该字段仅对存算分离3.0表及外表生效。 vfs_remote_read_bytes bigint OBS虚拟文件系统实际从OBS读取的字节数，单位Bytes。该字段仅对存算分离3.0表及外表生效。 preload_submit_time bigint 预读流程提交IO请求的总时间，单位μs。该字段仅对存算分离3.0表生效。 preload_wait_time bigint 预读流程等待IO请求的总时间，单位μs。该字段仅对存算分离3.0表生效。 preload_wait_count bigint 预读流程等待IO请求的总次数。该字段仅对存算分离3.0表生效。 disk_cache_load_time bigint 读取磁盘缓存的总时间，单位μs。该字段仅对存算分离3.0表及外表生效。 disk_cache_conflict_count bigint 读取磁盘缓存中block产生哈希冲突的次数。该字段仅对存算分离3.0表及外表生效。 disk_cache_error_count bigint 读取磁盘缓存失败的次数。该字段仅对存算分离3.0表及外表生效。 disk_cache_error_code bigint 读取磁盘缓存失败的错误码，可能产生多个错误码，读取磁盘缓存失败会发起OBS远程读并重写缓存块，错误码类型如下。该字段仅对3.0表及外表生效。 1：磁盘缓存块产生哈希冲突。 2：磁盘缓存块的生成时间晚于OldestXmin事务。 4：磁盘缓存读取缓存文件调用pread系统调用失败。 8：磁盘缓存块的数据版本不匹配。 16：写缓存写入的数据版本与最新版本不匹配。 32：打开缓存块对应的缓存文件失败。 64：磁盘缓存读取数据大小不匹配。 128：磁盘缓存块中记录的csn不匹配。 obs_io_req_avg_rtt bigint OBS IO请求的平均RTT(Round Trip Time，IO请求往返时间)，单位为μs。该字段仅对存算分离3.0表及外表生效。 obs_io_req_avg_latency bigint OBS IO请求的平均延迟，单位为μs。该字段仅对存算分离3.0表及外表生效。 obs_io_req_latency_gt_1s bigint OBS IO请求延迟超过1s的数量。该字段仅对存算分离3.0表及外表生效。 obs_io_req_latency_gt_10s bigint OBS IO请求延迟超过10s的数量。该字段仅对存算分离3.0表及外表生效。 obs_io_req_count bigint OBS IO请求的总数量。该字段仅对存算分离3.0表及外表生效。 obs_io_req_retry_count bigint OBS IO请求重试的总次数。该字段仅对存算分离3.0表及外表生效。 obs_io_req_rate_limit_count bigint OBS IO请求被流控的总次数。该字段仅对存算分离3.0表及外表生效。 父主题： 系统表

GS_WLM_OPERATOR_INFO GS_WLM_OPERATOR_INFO系统表显示执行作业结束后的算子相关的记录。此数据是从内核中转储到系统表中的数据。当设置GUC参数enable_resource_record为on时，系统会定时将GS_WLM_OPERATOR_HISTORY中的记录导入此系统表，开启此功能会占用系统存储空间并对性能有一定影响，建议在性能定位及监控任务完成后及时关闭。 GS_WLM_OPERATOR_INFO系统表的schema为dbms_om。 GS_WLM_OPERATOR_INFO系统表仅支持在postgres数据库中查询，其它数据库中查询会直接报错。 表1 GS_WLM_OPERATOR_INFO的字段 名称 类型 描述 nodename text 执行语句的CN实例名称。 queryid bigint 语句执行使用的内部query_id。 pid bigint 后端线程ID。 plan_node_id integer 查询对应的执行计划的plan node id。 plan_node_name text 对应于plan_node_id的算子的名称。 start_time timestamp with time zone 该算子处理第一条数据的开始时间。 duration bigint 该算子到结束时候总的执行时间(ms)。 query_dop integer 当前算子执行时的并行度。 estimated_rows bigint 优化器估算的行数信息。 tuple_processed bigint 当前算子返回的元素个数。 min_peak_memory integer 当前算子在所有DN上的最小内存峰值(MB)。 max_peak_memory integer 当前算子在所有DN上的最大内存峰值(MB)。 average_peak_memory integer 当前算子在所有DN上的平均内存峰值(MB)。 memory_skew_percent integer 当前算子在各DN间的内存使用倾斜率。 min_spill_size integer 若发生下盘，所有下盘DN的最小下盘数据量(MB)，默认为0。 max_spill_size integer 若发生下盘，所有下盘DN的最大下盘数据量(MB)，默认为0。 average_spill_size integer 若发生下盘，所有下盘DN的平均下盘数据量(MB)，默认为0。 spill_skew_percent integer 若发生下盘，DN间下盘倾斜率。 min_cpu_time bigint 该算子在所有DN上的最小执行时间(ms)。 max_cpu_time bigint 该算子在所有DN上的最大执行时间(ms)。 total_cpu_time bigint 该算子在所有DN上的总执行时间(ms)。 cpu_skew_percent integer DN间执行时间的倾斜率。 warning text 主要显示如下几类告警信息： Sort/SetOp/HashAgg/HashJoin spill Spill file size large than 256MB Broadcast size large than 100MB Early spill Spill times is greater than 3 Spill on memory adaptive Hash table conflict 父主题： 系统表

GS_WLM_INSTANCE_HISTORY GS_WLM_INSTANCE_HISTORY系统表存储与实例(CN或DN)相关的资源使用相关信息。该系统表里每条记录都是对应时间点某实例资源使用情况，包括：内存、CPU核数、磁盘IO、进程物理IO和进程逻辑IO信息。 表1 GS_WLM_INSTANCE_HISTORY字段 名称 类型 描述 instancename text 实例名称。 timestamp timestamp with time zone 时间戳。 used_cpu int 实例使用CPU所占用的百分比。 free_mem int 实例未使用的内存大小，单位MB。 used_mem int 实例已使用的内存大小，单位MB。 io_await real 实例所使用磁盘的io_wait值（10秒均值）。 io_util real 实例所使用磁盘的io_util值（10秒均值）。 disk_read real 实例所使用磁盘的读速率（10秒均值），单位KB/s。 disk_write real 实例所使用磁盘的写速率（10秒均值），单位KB/s。 process_read bigint 实例对应进程从磁盘读数据的读速率(不包括从磁盘pagecache中读取的字节数，10秒均值)，单位KB/s。 process_write bigint 实例对应进程向磁盘写数据的写速率(不包括向磁盘pagecache中写入的字节数，10秒均值)，单位KB/s。 logical_read bigint CN实例：不统计。 DN实例：该实例在本次统计间隙（10秒）内逻辑读字节速率，单位KB/s。 logical_write bigint CN实例：不统计。 DN实例：该实例在本次统计间隙（10秒）内逻辑写字节速率，单位KB/s。 read_counts bigint CN实例：不统计。 DN实例：该实例在本次统计间隙（10秒）内逻辑读操作次数之和，单位次。 write_counts bigint CN实例：不统计。 DN实例：该实例在本次统计间隙（10秒）内逻辑写操作次数之和，单位次。 父主题： 系统表

GS_OBSSCANINFO GS_OBSSCANINFO系统表定义了在云上加速场景中，使用加速集群时扫描OBS数据的运行时信息，每条记录对应一个query中单个OBS外表的运行时信息。 表1 GS_OBSSCANINFO字段 名字 类型 引用 描述 query_id bigint - 查询标识。 user_id text - 执行该查询的数据库用户。 table_name text - OBS外表的表名。 file_type text - 底层数据保存的文件格式。 time_stamp time_stam - 扫描操作开始的时间。 actual_time double - 扫描操作执行时间，单位为秒。 file_scanned bigint - 扫描的文件数量。 data_size double - 扫描的数据量，单位为字节。 billing_info text - 保留字段。 父主题： 系统表

系统表和系统视图概述 系统表是 GaussDB (DWS)存放结构元数据，是GaussDB(DWS)数据库系统运行控制信息的来源，也是数据库系统的核心组成部分。系统表包含集群安装信息以及GaussDB(DWS)上运行的各种查询和进程的信息。可以通过查询系统表来收集有关数据库的信息。 系统视图提供了查询系统表和访问数据库内部状态的方法。当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时，用户就可以定义一个视图来解决这个问题。视图与基本表不同，不是物理上实际存在的，是一个虚表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。视图每次被引用的时候都会运行一次。 三权分立下，非管理员无权查看系统表和视图。非三权分立下，系统表和系统视图要么只对管理员可见，要么对所有用户可见。下面的系统表和视图有些标识了需要管理员权限，这些系统表和视图只有管理员可以查询。 禁止对系统表或系统视图进行增删改等操作，手动对系统表或系统视图的修改或破坏可能会导致系统信息不一致，造成系统控制异常甚至出现集群不可用的情况。 系统表不支持toast，无法跨页存储，一个页面大小为8K，系统表各个字段长度需小于8K。 父主题： GaussDB(DWS)系统表和系统视图

语法 RAISE有以下五种语法格式： 图1 raise_format::= 图2 raise_condition::= 图3 raise_sqlstate::= 图4 raise_option::= 图5 raise::= 参数说明： level选项用于指定错误级别，有DEBUG， LOG ，INFO，NOTICE，WARNING以及EXCEPTION（默认值）。EXCEPTION上报一个正常终止当前事务的异常，其他的仅产生不同异常级别的信息。特殊级别的错误信息是否报告到客户端、写到服务器日志由log_min_messages和client_min_messages这两个配置参数控制。 format：格式字符串，指定要报告的错误消息文本。格式字符串后可跟表达式，用于向消息文本中插入。在格式字符串中，%由format后面跟着的参数的值替换，%%用于打印出%。例如： --v_job_id 将替换字符串中的 %： RAISE NOTICE 'Calling cs_create_job(%)',v_job_id; option = expression：向错误报告中添加另外的信息。关键字option可以是MESSAGE、DETAIL、HINT以及ERRCODE，并且每一个expression可以是任意的字符串。 MESSAGE，指定错误消息文本，这个选项不能用于在USING前包含一个格式字符串的RAISE语句中。 DETAIL，说明错误的详细信息。 HINT，用于打印出提示信息。 ERRCODE，向报告中指定错误码（SQLSTATE）。可以使用条件名称或者直接用五位字符的SQLSTATE错误码。 condition_name：错误码对应的条件名。 sqlstate：错误码。 如果在RAISE EXCEPTION命令中既没有指定条件名也没有指定SQLSTATE，默认用RAISE EXCEPTION (P0001)。如果没有指定消息文本，默认用条件名或者SQLSTATE作为消息文本。 当由SQLSTATE指定了错误码，则不局限于已定义的错误码，可以选择任意包含五个数字或者大写的ASCII字母的错误码，而不是00000。建议避免使用以三个0结尾的错误码，因为这种错误码是类别码，会被整个种类捕获。 图5所示的语法不接任何参数。这种形式仅用于一个BEGIN块中的EXCEPTION语句，它使得错误重新被处理。

约束说明 创建一个新job后，该job从属于当前coordinator（即：该job仅在当前coordinator上调度和执行），其他coordinator不会调度和执行该job。所有coordinator都可以查看、修改、删除其他CN创建的job。 job只能通过dbms_job高级包提供的接口进行创建、更新、删除操作，因为高级包的接口中会考虑所有CN间job信息的同步和pg_jobs表主键的关联操作，如果通过DML语句对pg_jobs表进行增删改，会导致job信息在CN间不一致和系统表无法关联变更的混乱问题，会严重影响job内部的管理。 由于用户创建的每个任务和CN绑定，若不开启CN故障自动迁移功能，当任务运行过程中，该CN故障，则该任务的状态无法实时刷新。如果在任务未执行时CN故障，则该CN上的任务都得不到正常的调度和执行。建议开启CN故障自动迁移功能，故障CN上的作业会迁移至其他CN继续调度。 job在定时执行过程中，需要在当前job所属的CN上实时更新该job的运行状态、最近执行开始时间、最近执行结束时间、下次开始时间、失败次数（如果job执行失败）等相关参数信息到pg_jobs系统表中，并同步到其他CN，保证job信息的一致性。如果其他CN存在节点故障，那么job所属CN会同步超时重发的处理，导致job执行时间变长，但CN间同步超时失败后，原CN上pg_jobs表中job的相关信息仍然能正常更新，且job能正常执行成功。当故障CN恢复正常后，可能出现该CN上pg_jobs表中当前job的执行时间、运行状态等参数与原CN上不一致的情况，需要原CN上再次执行该job后才能保证job信息的同步。 对于并发同时有多个job到达执行时间的场景，由于会为每个job创建一个线程来执行job，由于系统内部启动每个线程的时间会有延迟，因此会导致同时并发执行的job的开始时间有延迟，每个job的延迟时间在0.1ms左右。 job中待执行SQL语句有长度限制，最长为8K。

接口介绍 高级功能包DBMS_JOB支持的所有接口请参见表1。 表1 DBMS_JOB 接口名称 描述 DBMS_JOB.SUBMIT 提交一个定时任务。作业号由系统自动生成。 DBMS_JOB.SUBMIT_NODE 提交一个定时任务。执行节点由用户指定，作业号由系统自动生成。 DBMS_JOB.ISUBMIT 提交一个定时任务。作业号由用户指定。 DBMS_JOB.REMOVE 通过作业号来删除定时任务。 DBMS_JOB.BROKEN 禁用或者启用定时任务。 DBMS_JOB.CHANGE 修改定时任务的属性，包括任务内容、下次执行时间、执行间隔。 DBMS_JOB.WHAT 修改定时任务的任务内容属性。 DBMS_JOB.NEXT_DATE 修改定时任务的下次执行时间属性。 DBMS_JOB.INTERVAL 修改定时任务的执行间隔属性。 DBMS_JOB.CHANGE_OWNER 修改定时任务的属主。 DBMS_JOB.CHANGE_NODE 修改定时任务的执行节点。 DBMS_JOB.SUBMIT 存储过程SUBMIT提交一个系统提供的定时任务。 DBMS_JOB.SUBMIT函数原型为： 1 2 3 4 5 DMBS_JOB.SUBMIT( what IN TEXT, next_date IN TIMESTAMP DEFAULT sysdate, job_interval IN TEXT DEFAULT 'null', job OUT INTEGER); 当创建一个定时任务（DBMS_JOB）时，系统默认将当前数据库和用户名与当前创建的定时任务（DBMS_JOB）绑定起来。该接口函数可以通过call或select调用，如果通过select调用，可以不填写出参。如果在存储过程中则需要用通过perform调用该接口函数。 表2 DBMS_JOB.SUBMIT接口参数说明 参数 类型 入参/出参 是否可以为空 描述 what text IN 否 要执行的SQL语句。支持一个或多个‘DML’，‘匿名块’，‘调用存储过程的语句’或3种混合的场景。 next_date timestamp IN 否 下次作业运行时间。默认值为当前系统时间（sysdate）。如果是过去时间，在提交作业时表示立即执行。 interval text IN 是 用来计算下次作业运行时间的时间表达式，可以是interval表达式，也可以是sysdate加上一个numeric值（例如：sysdate+1.0/24）。如果为空值或字符串"null"表示只执行一次，执行后JOB状态STATUS变成'd' 不再执行。 job integer OUT 否 作业号。范围为1～32767。当使用select调用dbms.submit时，该参数可以省略。 示例： 1 2 3 4 5 select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1'); select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24'); CALL DBMS_JOB.SUBMIT('INSERT INTO T_JOB VALUES(1); call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid); DBMS_JOB.SUBMIT_NODE 存储过程SUBMIT提交一个系统提供的定时任务。执行节点由用户指定。该接口仅8.3.0及以上集群版本支持。 DBMS_JOB.SUBMIT_NODE函数原型为： 1 2 3 4 5 6 DMBS_JOB.SUBMIT_NODE( what IN TEXT, next_date IN TIMESTAMP DEFAULT sysdate, job_interval IN TEXT DEFAULT 'null', job_node IN TEXT DEFAULT NULL, job OUT INTEGER); 表3 DBMS_JOB.SUBMIT_NODE接口参数说明 参数 类型 入参/出参 是否可以为空 描述 what text IN 否 要执行的SQL语句。支持一个或多个DML、匿名块、调用存储过程的语句或3种混合的场景。 next_date timestamp IN 否 下次作业运行时间。默认值为当前系统时间（sysdate）。如果是过去时间，在提交作业时表示立即执行。 interval text IN 是 用来计算下次作业运行时间的时间表达式，可以是interval表达式，也可以是sysdate加上一个numeric值（例如：sysdate+1.0/24）。如果为空值或字符串"null"则表示只执行一次，执行后JOB状态STATUS变成'd'则不再执行。 node text IN 是 作业执行节点名称。 job integer OUT 否 作业号。范围为1～32767。当使用select调用dbms.submit时，该参数可以省略。 示例： 1 2 3 4 5 select DBMS_JOB.SUBMIT_NODE('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1','coordinator1'); select DBMS_JOB.SUBMIT_NODE('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24'); CALL DBMS_JOB.SUBMIT('INSERT INTO T_JOB VALUES(1); call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)', 'coordinator1', :jobid); DBMS_JOB.ISUBMIT ISUBMIT与SUBMIT语法功能相同，但其第一个参数是入参，即指定的作业号，SUBMIT最后一个参数是出参，表示系统自动生成的作业号。 示例： 1 CALL dbms_job.isubmit(101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24'); GaussDB(DWS)的pgstats持久化功能将内存中的统计信息写入pg_stat_object系统表，如果新安装的9.1.0.100及以上集群版本，会占用1为job_id。如果为低版本升级到9.1.0.100及以上集群版本，并且之前pg_job中有任务，那么会找到一个未被占用的job_id作为持久化任务的ID。所以在使用dbms_job.isubmit接口时需注意，不能和已经存在的pgstats持久化任务的id重复，否则会导致任务注册失败。 DBMS_JOB.REMOVE 存储过程REMOVE删除指定的定时任务。 DBMS_JOB.REMOVE函数原型为： 1 REMOVE(job IN INTEGER); 表4 DBMS_JOB.REMOVE接口参数说明 参数 类型 入参/出参 是否可以为空 描述 job integer IN 否 指定的作业号。 示例： CALL dbms_job.remove(101); DBMS_JOB.BROKEN 存储过程BROKEN禁用或者启用定时任务。 DBMS_JOB.BROKEN函数原型为： 1 2 3 4 DMBS_JOB.BROKEN( job IN INTEGER, broken IN BOOLEAN, next_date IN TIMESTAMP DEFAULT sysdate); 表5 DBMS_JOB.BROKEN接口参数说明 参数 类型 入参/出参 是否可以为空 描述 job integer IN 否 指定的作业号。 broken boolean IN 否 状态标志位，true代表禁用，false代表启用。具体true或false值更新当前job；如果为空值，则不改变原有job的状态。 next_date timestamp IN 是 下次运行时间，默认为当前系统时间。如果参数broken状态为true，则更新该参数为'4000-1-1'；如果参数broken状态为false，且如果参数next_date不为空值，则更新指定job的next_date值，如果next_date为空值，则不更新next_date值。该参数可以省略，为默认值。 示例： 1 2 CALL dbms_job.broken(101,true); CALL dbms_job.broken(101,false,sysdate); DBMS_JOB.CHANGE 存储过程CHANGE修改定时任务的属性，包括任务内容、下次执行时间、执行间隔。 DBMS_JOB.CHANGE函数原型为： 1 2 3 4 5 DMBS_JOB.CHANGE( job IN INTEGER, what IN TEXT, next_date IN TIMESTAMP, interval IN TEXT); 表6 DBMS_JOB.CHANGE接口参数说明 参数 类型 入参/出参 是否可以为空 描述 job integer IN 否 指定的作业号。 what text IN 是 执行的存储过程名或者sql语句块。如果该参数为空值，则不更新指定job的what值，否则更新指定job的what值。 next_date timestamp IN 是 下次运行时间。如果该参数为空值，则不更新指定job的next_date值，否则更新指定job的next_date值。 interval text IN 是 用来计算下次作业运行时间的时间表达式。如果该参数为空值，则不更新指定job的interval值；如果该参数不为空值，会校验interval是否为有效的时间类型或interval类型，则更新指定job的interval值。如果为字符串"null"表示只执行一次，执行后JOB状态STATUS变成'd' 不再执行。 示例： 1 2 CALL dbms_job.change(101, 'call userproc();', sysdate, 'sysdate + 1.0/1440'); CALL dbms_job.change(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440'); DBMS_JOB.WHAT 存储过程WHAT修改定时任务的任务内容属性。 DBMS_JOB.WHAT函数原型为： 1 2 3 DMBS_JOB.WHAT( job IN INTEGER, what IN TEXT); 表7 DBMS_JOB.WHAT接口参数说明 参数 类型 入参/出参 是否可以为空 描述 job integer IN 否 指定的作业号。 what text IN 否 执行的存储过程调用或者sql语句块。 当what参数是一个或多个可以执行成功的sql语句/程序块/调用存储过程时，该接口函数才能被执行成功，否则会执行失败。 若what参数为一个简单的insert、update等语句，需要在表前加模式名。 示例： 1 2 CALL dbms_job.what(101, 'call userproc();'); CALL dbms_job.what(101, 'insert into tbl_a values(sysdate);'); DBMS_JOB.NEXT_DATE 存储过程NEXT_DATE修改定时任务的下次执行时间属性。 DBMS_JOB.NEXT_DATE函数原型为： 1 2 3 DMBS_JOB.NEXT_DATE( job IN INTEGER, next_date IN TIMESTAMP); 表8 DBMS_JOB.NEXT_DATE接口参数说明 参数 类型 入参/出参 是否可以为空 描述 job integer IN 否 指定的作业号。 next_date timestamp IN 否 下次运行时间。 如果输入的next_date的值小于当前日期值，该job会立即执行一次。 示例： 1 CALL dbms_job.next_date(101,sysdate); DBMS_JOB.INTERVAL 存储过程INTERVAL修改定时任务的执行间隔属性。 DBMS_JOB.INTERVAL函数原型为： 1 2 3 DMBS_JOB.INTERVAL( job IN INTEGER, interval IN TEXT); 表9 DBMS_JOB.INTERVAL接口参数说明 参数 类型 入参/出参 是否可以为空 描述 job integer IN 否 指定的作业号。 interval text IN 是 用来计算下次作业运行时间的时间表达式。如果为空值或字符串"null"表示只执行一次，执行后JOB状态STATUS变成'd' 不再执行。interval是否为有效的时间类型或interval类型。 示例： 1 CALL dbms_job.interval(101, 'sysdate + 1.0/1440'); 对于指定job正在运行状态（即job_status为'r'）时，不允许通过remove、change、next_date、what、interval等接口删除或修改job的参数信息。

示例 在存储过程中操作RAW数据： 1 2 3 4 5 6 7 8 9 10 11 CREATE OR REPLACE PROCEDURE proc_raw AS str varchar2(100) := 'abcdef'; source raw(100); amount integer; BEGIN source := utl_raw.cast_to_raw(str);--类型转换 amount := utl_raw.length(source);--获取长度 dbms_output.put_line(amount); END; / 调用存储过程： 1 CALL proc_raw();

接口介绍 高级功能包UTL_RAW支持的所有接口请参见表1。 表1 UTL_RAW 接口名称 描述 UTL_RAW.CAST_FROM_BINARY_INTEGER 将INTEGER类型值转换为二进制表示形式（即RAW类型）。 UTL_RAW.CAST_TO_BINARY_INTEGER 将二进制表示形式的整型值（即RAW类型）转换为INTEGER类型。 UTL_RAW.LENGTH 获取RAW类型对象的长度。 UTL_RAW.CAST_TO_RAW 将VARCHAR2类型值转化为二进制表示形式（即RAW类型）。 RAW类型的外部表现形式是十六进制，内部存储形式是二进制。例如一个RAW类型的数据11001011的表现形式为‘CB’，即在实际的类型转换中输入的是‘CB’。 UTL_RAW.CAST_FROM_BINARY_INTEGER 存储过程CAST_FROM_BINARY_INTEGER将INTEGER类型值转换为二进制表示形式（即RAW类型）。 UTL_RAW.CAST_FROM_BINARY_INTEGER函数原型为： 1 2 3 4 UTL_RAW.CAST_FROM_BINARY_INTEGER ( n IN INTEGER, endianess IN INTEGER) RETURN RAW; 表2 UTL_RAW.CAST_FROM_BINARY_INTEGER接口参数说明 参数 描述 n 待转成RAW类型的整型数值。 endianess 表示字节序的整型值1或2（1代表BIG_ENDIAN，2代表LITTLE-ENDIAN）。 UTL_RAW.CAST_TO_BINARY_INTEGER 存储过程CAST_TO_BINARY_INTEGER将二进制表示形式的整型值（即RAW类型）转换为INTEGER类型。 UTL_RAW.CAST_TO_BINARY_INTEGER函数原型为： 1 2 3 4 UTL_RAW.CAST_TO_BINARY_INTEGER ( r IN RAW, endianess IN INTEGER) RETURN BINARY_INTEGER; 表3 UTL_RAW.CAST_TO_BINARY_INTEGER接口参数说明 参数 描述 r 二进制表示形式的整型值（即RAW类型）。 endianess 表示字节序的整型值1或2（1代表BIG_ENDIAN，2代表LITTLE-ENDIAN）。 UTL_RAW.LENGTH 存储过程LENGTH返回RAW类型对象的长度。 UTL_RAW.LENGTH函数原型为： 1 2 3 UTL_RAW.LENGTH( r IN RAW) RETURN INTEGER; 表4 UTL_RAW.LENGTH接口参数说明 参数 描述 r RAW类型对象 UTL_RAW.CAST_TO_RAW 存储过程CAST_TO_RAW将VARCHAR2类型的对象转换成RAW类型。 UTL_RAW.CAST_TO_RAW函数原型为： 1 2 3 UTL_RAW.CAST_TO_RAW( c IN VARCHAR2) RETURN RAW; 表5 UTL_RAW.CAST_TO_RAW接口参数说明 参数 描述 c 待转换的VARCHAR2类型对象

接口介绍 高级功能包DBMS_OUTPUT支持的所有接口请参见表1。 表1 DBMS_OUTPUT 接口名称 描述 DBMS_OUTPUT.PUT_LINE 输出指定的文本，文本长度不能超过32767字节。 DBMS_OUTPUT.PUT 将指定的文本输出到指定文本的前面，不添加换行符，文本长度不能超过32767字节。 DBMS_OUTPUT.ENABLE 设置输出缓冲区的大小。若不指定，缓冲区最大只能容纳20000字节，缓冲区最小可设置为2000字节，若设置小于2000字节将按2000字节处理。 DBMS_OUTPUT.PUT_LINE 存储过程PUT_LINE向消息缓冲区写入一行带有行结束符的文本。DBMS_OUTPUT.PUT_LINE函数原型为： 1 2 DBMS_OUTPUT.PUT_LINE ( item IN VARCHAR2); 表2 DBMS_OUTPUT.PUT_LINE接口参数说明 参数 描述 item 写入消息缓冲区的文本。 DBMS_OUTPUT.PUT 存储过程PUT将指定的文本输出到指定文本的前面，不添加换行符。DBMS_OUTPUT.PUT函数原型为： 1 2 DBMS_OUTPUT.PUT ( item IN VARCHAR2); 表3 DBMS_OUTPUT.PUT接口参数说明 参数 描述 item 写入指定文本前的文本。 DBMS_OUTPUT.ENABLE 存储过程ENABLE设置输出缓冲区的大小，如果不指定的话缓冲区最大只能容纳20000字节。DBMS_OUTPUT.ENABLE函数原型为： 1 2 DBMS_OUTPUT.ENABLE ( buf IN INTEGER); 表4 DBMS_OUTPUT.ENABLE接口参数说明 参数 描述 buf 设置输出缓冲区的大小。

接口介绍 高级功能包DBMS_RANDOM支持的所有接口请参见表1。 表1 DBMS_RANDOM接口参数说明 接口名称 描述 DBMS_RANDOM.SEED 设置一个随机数的种子。 DBMS_RANDOM.VALUE 生成一个大小介于指定的low及high之间的随机数。 DBMS_RANDOM.SEED 存储过程SEED用于设置一个随机数的种子。DBMS_RANDOM.SEED函数原型为： 1 DBMS_RANDOM.SEED (seed IN INTEGER); 表2 DBMS_RANDOM.SEED接口参数说明 参数 描述 seed 用于产生一个随机数的种子。 DBMS_RANDOM.VALUE 存储过程VALUE生成一个大小介于指定的low及high之间的随机数。DBMS_RANDOM.VALUE函数原型为： 1 2 3 4 DBMS_RANDOM.VALUE( low IN NUMBER, high IN NUMBER) RETURN NUMBER; 表3 DBMS_RANDOM.VALUE接口参数说明 参数 描述 low 指定随机数大小的下边界，生成的随机数大于或等于low。 high 指定随机数大小的上边界，生成的随机数小于high。 实际上，只要求这里的参数类型是NUMERIC即可，对于左右边界的大小并没有要求。

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 --获取字符串的长度 SELECT DBMS_LOB.GETLENGTH('12345678'); DECLARE myraw RAW(100); amount INTEGER :=2; buffer INTEGER :=1; begin DBMS_LOB.READ('123456789012345',amount,buffer,myraw); dbms_output.put_line(myraw); end; / CREATE TABLE blob_Table (t1 blob) DISTRIBUTE BY REPLICATION; CREATE TABLE blob_Table_bak (t2 blob) DISTRIBUTE BY REPLICATION; INSERT INTO blob_Table VALUES('abcdef'); INSERT INTO blob_Table_bak VALUES('22222'); DECLARE str varchar2(100) := 'abcdef'; source raw(100); dest blob; copyto blob; amount int; PSV_SQL varchar2(100); PSV_SQL1 varchar2(100); a int :=1; len int; BEGIN source := utl_raw.cast_to_raw(str); amount := utl_raw.length(source); PSV_SQL :='select * from blob_Table for update'; PSV_SQL1 := 'select * from blob_Table_bak for update'; EXECUTE IMMEDIATE PSV_SQL into dest; EXECUTE IMMEDIATE PSV_SQL1 into copyto; DBMS_LOB.WRITE(dest, amount, 1, source); DBMS_LOB.WRITEAPPEND(dest, amount, source); DBMS_LOB.ERASE(dest, a, 1); DBMS_OUTPUT.PUT_LINE(a); DBMS_LOB.COPY(copyto, dest, amount, 10, 1); DBMS_LOB.CLOSE(dest); RETURN; END; / --删除表 DROP TABLE blob_Table; DROP TABLE blob_Table_bak;

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 --删除EMP表中某部门的所有员工，如果该部门中已没有员工，则在DEPT表中删除该部门。 CREATE TABLE staffs_t1 AS TABLE staffs; CREATE TABLE sections_t1 AS TABLE sections; CREATE OR REPLACE PROCEDURE proc_cursor3() AS DECLARE V_DEPTNO NUMBER(4) := 100; BEGIN DELETE FROM staffs WHERE section_ID = V_DEPTNO; --根据游标状态做进一步处理 IF SQL%NOTFOUND THEN DELETE FROM sections_t1 WHERE section_ID = V_DEPTNO; END IF; END; / CALL proc_cursor3(); --删除存储过程和临时表 DROP PROCEDURE proc_cursor3; DROP TABLE staffs_t1; DROP TABLE sections_t1;

处理步骤 显式游标处理需六个PL/SQL步骤： 定义静态游标：就是定义一个游标名，以及与其相对应的SELECT语句。 定义静态游标的语法图，请参见图1。 图1 static_cursor_define::= 参数说明： cursor_name：定义的游标名。 parameter：游标参数，只能为输入参数，其格式为： parameter_name datatype select_statement：查询语句。 根据执行计划的不同，系统会自动判断该游标是否可以用于以倒序的方式检索数据行。 定义动态游标：指ref游标，可以通过一组静态的SQL语句动态的打开游标。首先定义ref游标类型，然后定义该游标类型的游标变量，在打开游标时通过OPEN FOR动态绑定SELECT语句。 定义动态游标的语法图，请参见图2和图3。 图2 cursor_typename::= GaussDB(DWS)支持sys_refcursor动态游标类型，函数或存储过程可以通过sys_refcursor参数传入或传出游标结果集合，函数也可以通过返回sys_refcursor来返回游标结果集合。 图3 dynamic_cursor_define::= 打开静态游标：就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。 打开静态游标的语法图，请参见图4。 图4 open_static_cursor::= 打开动态游标：可以通过OPEN FOR语句打开动态游标，动态绑定SQL语句。 打开动态游标的语法图，请参见图5。 图5 open_dynamic_cursor::= PL/SQL程序不能用OPEN语句重复打开一个游标。 提取游标数据：检索结果集合中的数据行，放入指定的输出变量中。 提取游标数据的语法图，请参见图6。 图6 fetch_cursor::= 对该记录进行处理。 继续处理，直到活动集合中没有记录。 关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH语句获取其中数据。关闭后的游标可以使用OPEN语句重新打开。 关闭游标的语法图，请参见图7。 图7 close_cursor::=

属性 游标的属性用于控制程序流程或者了解程序的状态。当运行DML语句时，PL/SQL打开一个内建游标并处理结果，游标是维护查询结果的内存中的一个区域，游标在运行DML语句时打开，完成后关闭。显式游标的属性为： %FOUND布尔型属性：当最近一次读记录时成功返回，则值为TRUE。 %NOTFOUND布尔型属性：与%FOUND相反。 %ISOPEN布尔型属性：当游标已打开时返回TRUE。 %ROWCOUNT数值型属性：返回已从游标中读取的记录数。

游标概述 为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。 当游标作为存储过程的返回值时，如果使用JDBC调用该存储过程，返回的游标将不可用。 游标的使用分为显式游标和隐式游标。对于不同的SQL语句，游标的使用情况不同，详细信息请参见表1。 表1 游标使用情况 SQL语句 游标 非查询语句 隐式的 结果是单行的查询语句 隐式的或显式的 结果是多行的查询语句 显式的 父主题： GaussDB(DWS)存储过程游标

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer) AS BEGIN CASE pi_result WHEN 1 THEN pi_return := 111; WHEN 2 THEN pi_return := 222; WHEN 3 THEN pi_return := 333; WHEN 6 THEN pi_return := 444; WHEN 7 THEN pi_return := 555; WHEN 8 THEN pi_return := 666; WHEN 9 THEN pi_return := 777; WHEN 10 THEN pi_return := 888; ELSE pi_return := 999; END CASE; raise info 'pi_return : %',pi_return ; END; / CALL proc_case_branch(3,0); --删除存储过程 DROP PROCEDURE proc_case_branch;

FORALL批量查询语句 语法图 图5 forall::= 变量index会自动定义为integer类型并且只在此循环里存在。index的取值介于low_bound和upper_bound之间。 示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 CREATE TABLE hdfs_t1 ( title NUMBER(6), did VARCHAR2(20), data_period VARCHAR2(25), kind VARCHAR2(25), interval VARCHAR2(20), time DATE, isModified VARCHAR2(10) ) DISTRIBUTE BY hash(did); INSERT INTO hdfs_t1 VALUES( 8, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK' ); CREATE OR REPLACE PROCEDURE proc_forall() AS BEGIN FORALL i IN 100..120 insert into hdfs_t1(title) values(i); END; / --调用函数 CALL proc_forall(); --查询存储过程调用结果 SELECT * FROM hdfs_t1 WHERE title BETWEEN 100 AND 120; --删除存储过程和表 DROP PROCEDURE proc_forall; DROP TABLE hdfs_t1;

FOR_LOOP（integer变量）语句 语法图 图3 for_loop::= 变量name会自动定义为integer类型并且只在此循环里存在。变量name介于lower_bound和upper_bound之间。 当使用REVERSE关键字时，lower_bound必须大于等于upper_bound，否则循环体不会被执行。 示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 --从0到5进行循环 CREATE OR REPLACE PROCEDURE proc_for_loop() AS BEGIN FOR I IN 0..5 LOOP DBMS_OUTPUT.PUT_LINE('It is '||to_char(I) || ' time;') ; END LOOP; END; / --调用函数 CALL proc_for_loop(); --删除存储过程 DROP PROCEDURE proc_for_loop;

FOR_LOOP查询语句 语法图 图4 for_loop_query::= 变量target会自动定义，类型和query的查询结果的类型一致，并且只在此循环中有效。target的取值就是query的查询结果。 示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 --循环输出查询结果。 CREATE OR REPLACE PROCEDURE proc_for_loop_query() AS record VARCHAR2(50); BEGIN FOR record IN SELECT spcname FROM pg_tablespace LOOP dbms_output.put_line(record); END LOOP; END; / --调用函数 CALL proc_for_loop_query(); --删除存储过程 DROP PROCEDURE proc_for_loop_query;

RETURN 语法 返回语句的语法请参见图1。 图1 return_clause::= 对以上语法的解释如下： 用于将控制从存储过程或函数返回给调用者。 示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 --创建存储过程proc_staffs CREATE OR REPLACE PROCEDURE proc_staffs ( section NUMBER(6), salary_sum out NUMBER(8,2), staffs_count out INTEGER ) IS BEGIN SELECT sum(salary), count(*) INTO salary_sum, staffs_count FROM staffs where section_id = section; END; / --创建存储过程proc_return. CREATE OR REPLACE PROCEDURE proc_return AS v_num NUMBER(8,2); v_sum INTEGER; BEGIN proc_staffs(30, v_sum, v_num); --调用语句 dbms_output.put_line(v_sum||'#'||v_num); RETURN; --返回语句 END; / --调用存储过程proc_return. CALL proc_return(); --清除存储过程 DROP PROCEDURE proc_staffs; DROP PROCEDURE proc_return; --创建函数func_return. CREATE OR REPLACE FUNCTION func_return returns void language plpgsql AS $$ DECLARE v_num INTEGER := 1; BEGIN dbms_output.put_line(v_num); RETURN; --返回语句 END $$; -- 调用函数func_return CALL func_return(); 1 -- 清除函数 DROP FUNCTION func_return;

RETURN NEXT及RETURN QUERY 语法 创建函数时需要指定返回值SETOF datatype。 return_next_clause::= return_query_clause::= 对以上语法的解释如下： 当需要函数返回一个集合时，使用RETURN NEXT或者RETURN QUERY向结果集追加结果，然后继续执行函数的下一条语句。随着后续的RETURN NEXT或RETURN QUERY命令的执行，结果集中会有多个结果。函数执行完成后会一起返回所有结果。 RETURN NEXT可用于标量和复合数据类型。 RETURN QUERY有一种变体RETURN QUERY EXECUTE，后面还可以增加动态查询，通过USING向查询插入参数。 示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 CREATE TABLE t1(a int); INSERT INTO t1 VALUES(1),(10); --RETURN NEXT CREATE OR REPLACE FUNCTION fun_for_return_next() RETURNS SETOF t1 AS $$ DECLARE r t1%ROWTYPE; BEGIN FOR r IN select * from t1 LOOP RETURN NEXT r; END LOOP; RETURN; END; $$ LANGUAGE PLPGSQL; call fun_for_return_next(); a --- 1 10 (2 rows) -- RETURN QUERY CREATE OR REPLACE FUNCTION fun_for_return_query() RETURNS SETOF t1 AS $$ DECLARE r t1%ROWTYPE; BEGIN RETURN QUERY select * from t1; END; $$ language plpgsql; call fun_for_return_next(); a --- 1 10 (2 rows)

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 --创建存储过程dynamic_proc CREATE OR REPLACE PROCEDURE dynamic_proc AS staff_id NUMBER(6) := 200; first_name VARCHAR2(20); salary NUMBER(8,2); BEGIN --执行匿名块 EXECUTE IMMEDIATE 'begin select first_name, salary into :first_name, :salary from staffs where staff_id= :dno; end;' USING OUT first_name, OUT salary, IN staff_id; dbms_output.put_line(first_name|| ' ' || salary); END; / --调用存储过程 CALL dynamic_proc(); --删除存储过程 DROP PROCEDURE dynamic_proc;

语法 语法请参见图1。 图1 call_anonymous_block::= using_clause子句的语法参见图2。 图2 using_clause-4 对以上语法格式的解释如下： 匿名块程序实施部分，以BEGIN语句开始，以END语句停顿，以一个分号结束。 USING [IN|OUT|IN OUT] bind_argument，用于指定存放传递给存储过程参数值的变量。bind_argument前的修饰符与对应参数的修饰符一致。 匿名块中间的输入输出参数使用占位符来指明，要求占位符个数与参数个数相同，并且占位符所对应参数的顺序和USING中参数的顺序一致。 目前GaussDB(DWS)在动态语句调用匿名块时，EXCEPTION语句中暂不支持使用占位符进行输入输出参数的传递。

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 --创建存储过程proc_add。 CREATE OR REPLACE PROCEDURE proc_add ( param1 in INTEGER, param2 out INTEGER, param3 in INTEGER ) AS BEGIN param2:= param1 + param3; END; / DECLARE input1 INTEGER:=1; input2 INTEGER:=2; statement VARCHAR2(200); param2 INTEGER; BEGIN --声明调用语句 statement := 'call proc_add(:col_1, :col_2, :col_3)';（或者statement := 'call proc_add($1, $2, $3)';） --执行语句 EXECUTE IMMEDIATE statement USING IN input1, OUT param2, IN input2; dbms_output.put_line('result is: '||to_char(param2)); END; / --删除存储过程 DROP PROCEDURE proc_add;

语法 语法请参见图1。 图1 call_procedure::= using_clause子句的语法参见图2。 图2 using_clause-3 对以上语法格式的解释如下： CALL procedure_name，调用存储过程。 [:placeholder1，:placeholder2，…]，存储过程参数占位符列表，占位符个数与参数个数相同。占位符命名以“:”或“$”开始，“:”后面可跟数字、字符或字符串（不能使用带引号的数字、字符或字符串），“$”后面仅可跟数字。占位符与USING子句的bind_argument一一对应。 USING [IN|OUT|IN OUT] bind_argument，用于指定存放传递给存储过程参数值的变量。bind_argument前的修饰符与对应参数的修饰符一致。

示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 --创建表 CREATE TABLE sections_t1 ( section NUMBER(4) , section_name VARCHAR2(30), manager_id NUMBER(6), place_id NUMBER(4) ) DISTRIBUTE BY hash(manager_id); --声明变量 DECLARE section NUMBER(4) := 280; section_name VARCHAR2(30) := 'Info support'; manager_id NUMBER(6) := 103; place_id NUMBER(4) := 1400; new_colname VARCHAR2(10) := 'sec_name'; BEGIN --执行查询 EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :4)' USING section, section_name, manager_id,place_id; --执行查询（重复占位符） EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :1)' USING section, section_name, manager_id; --执行ALTER语句（建议采用“||”拼接数据库对象构造DDL语句） EXECUTE IMMEDIATE 'alter table sections_t1 rename section_name to ' || new_colname; END; / --查询数据 SELECT * FROM sections_t1; --删除表 DROP TABLE sections_t1;