过程函数-SYS_PROCEDURES
📄字数 1.3K
👁️阅读量 加载中...
功能描述
SYS_PROCEDURES系统表用于存储、管理系统库和用户库中所有创建的存储函数和过程信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | SCHEMA_ID | INTEGER | 模式ID | √ | √ |
| 2 | USER_ID | INTEGER | 属主ID | √ | √ |
| 3 | PROC_ID | INTEGER | 过程ID | √ | √ |
| 4 | PROC_NAME | VARCHAR | 过程或方法名 | √ | √ |
| 5 | LANGUAGE | CHAR | 语言 | √ | √ |
| 6 | RET_TYPE | CHAR | 返回类型 | √ | √ |
| 7 | DEFINE | CLOB | 过程体的文本定义 | √ | √ |
| 8 | CREATE_TIME | DATETIME | 创建时间 | √ | √ |
| 9 | VALID | BOOLEAN | 是否有效 | √ | √ |
| 10 | COMMENTS | VARCHAR | 注释信息 | √ | √ |
| 11 | IS_SYS | BOOLEAN | 是否系统内建 | √ | √ |
| 12 | PIPELINED | BOOLEAN | 是否为管道函数 | × | √ |
| 13 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 14 | RESERVED2 | VARCHAR | 保留字段 | √ | √ |
应用举例
- 创建过程函数，查询相关信息
sql
-- 创建存储过程
SQL> CREATE OR REPLACE PROCEDURE proc_info IS
BEGIN
SEND_MSG(1);
END;
-- 创建存储函数
SQL> CREATE OR REPLACE FUNCTION func_test(a INT)
RETURN INT AS
BEGIN
RETURN a+1;
END;
SQL> SELECT PROC_ID,PROC_NAME,RET_TYPE,TO_CHAR(DEFINE) DEFINE,IS_SYS,PIPELINED FROM SYS_PROCEDURES;
+---------+-----------+----------+---------------------------------------------+--------+-----------+
| PROC_ID | PROC_NAME | RET_TYPE | DEFINE | IS_SYS | PIPELINED |
+---------+-----------+----------+---------------------------------------------+--------+-----------+
| 1048589 | PROC_INFO | <NULL> | CREATE OR REPLACE PROCEDURE proc_info IS +| F | F |
| | | | BEGIN +| | |
| | | | SEND_MSG(1); +| | |
| | | | END; | | |
| 1048590 | FUNC_TEST | INTEGER | CREATE OR REPLACE FUNCTION func_test(a INT)+| F | F |
| | | | RETURN INT AS +| | |
| | | | BEGIN +| | |
| | | | RETURN a+1; +| | |
| | | | END; | | |
+---------+-----------+----------+---------------------------------------------+--------+-----------+- 自定义函数中使用PIPELINED函数
sql
SQL> CREATE OR REPLACE TYPE type_tab AS TABLE OF VARCHAR(100);
SQL> CREATE OR REPLACE FUNCTION fun_pipe(a INT)
RETURN type_tab PIPELINED
IS
j VARCHAR:='test';
BEGIN
FOR i IN 1..10 LOOP
PIPE ROW('abc'||j);
j:= j||(a+i);
END LOOP;
PIPE ROW('pipe row end');
RETURN;
END;
SQL> SELECT PROC_ID,PROC_NAME,RET_TYPE,TO_CHAR(DEFINE) DEFINE,IS_SYS,PIPELINED FROM SYS_PROCEDURES WHERE PROC_NAME = 'fun_pipe';
+---------+-----------+----------+--------------------------------------------+--------+-----------+
| PROC_ID | PROC_NAME | RET_TYPE | DEFINE | IS_SYS | PIPELINED |
+---------+-----------+----------+--------------------------------------------+--------+-----------+
| 1048594 | FUN_PIPE | TYPE_TAB | CREATE OR REPLACE FUNCTION fun_pipe(a INT)+| F | T |
| | | | RETURN type_tab PIPELINED +| | |
| | | | IS +| | |
| | | | j VARCHAR:='test'; +| | |
| | | | BEGIN +| | |
| | | | FOR i IN 1..10 LOOP +| | |
| | | | PIPE ROW('abc'||j); +| | |
| | | | j:= j||(a+i); +| | |
| | | | END LOOP; +| | |
| | | | PIPE ROW('pipe row end'); +| | |
| | | | RETURN; +| | |
| | | | END; | | |
+---------+-----------+----------+--------------------------------------------+--------+-----------+