おらくるっのストアドプロシージャで使われている
カーソル定義を解析したかったけどできなかった中途半端な内容。
レコードの中身を解析すりゃいいんじゃねって途中で思ったけど
それもまだいけてないこっちのほうがゴールに近いかも。
HOW_TO_USE.sqlのここを自由に改変のところに流したいSQLを打ちこむだけ。
データの長さ（data_length）ってテーブルで格納できる長さ（table.column.length）のことを指してて、データの長さ（data_value.length）って意味じゃない。
COLUMN TYPEとかCOLUMN MAX LENGTHとかにしたほうがいい絶対。
データの長さを追加したいね。LENBなのかLENなのかはっきりさせたいよね
ANALIZE_SYS_CURSORL.pkg.sql
create or replace PACKAGE ANALIZE_SYS_CURSORL_PKG AS
TYPE t_column_info IS RECORD (
row_id NUMBER,
col_id NUMBER,
column_name NVARCHAR2(30),
data_type NVARCHAR2(106),
data_length NUMBER,
data_value NVARCHAR2(1000)
);
TYPE t_column_info_table IS TABLE OF t_column_info INDEX BY PLS_INTEGER;
PROCEDURE GET_CURSOL (
cur_target IN OUT SYS_REFCURSOR,
v_result OUT t_column_info_table
);
END ANALIZE_SYS_CURSORL_PKG;
ANALIZE_SYS_CURSORL.bdy.sql
create or replace PACKAGE BODY ANALIZE_SYS_CURSORL_PKG AS
PROCEDURE SET_COLUMNS (
n_cur_cnt IN NUMBER
,n_col_cnt IN NUMBER
,v_desc_tab IN DBMS_SQL.DESC_TAB3
)
IS
v_col_typ NVARCHAR2(1000);
n_col_typ NUMBER;
d_col_typ DATE;
l_col_typ LONG;
BEGIN
FOR i IN 1 .. n_col_cnt LOOP
CASE v_desc_tab(i).col_type
WHEN 1 THEN
DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,v_col_typ, v_desc_tab(i).col_max_len);
WHEN 2 THEN
DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,n_col_typ);
WHEN 8 THEN
DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,l_col_typ);
WHEN 12 THEN
DBMS_SQL.DEFINE_COLUMN(n_cur_cnt,i ,d_col_typ);
END CASE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END SET_COLUMNS;
PROCEDURE get_cursol(
cur_target IN OUT NOCOPY SYS_REFCURSOR
, v_result OUT t_column_info_table
)IS
v_desc_tab DBMS_SQL.DESC_TAB3;
v_col_cnt NUMBER;
v_column_info t_column_info;
v_cursor NUMBER;
v_idx PLS_INTEGER := 1;
v_column_value VARCHAR2(50);
n_column_value NUMBER;
n_rowID NUMBER := 1;
BEGIN
-- カーソルを変換
v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(cur_target);
-- カラム情報を取得
DBMS_SQL.DESCRIBE_COLUMNS3(v_cursor, v_col_cnt, v_desc_tab);
SET_COLUMNS(v_cursor,v_col_cnt,v_desc_tab);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(v_cursor) = 0;
FOR i IN 1..v_col_cnt LOOP
v_column_info.row_id := n_rowID;
v_column_info.col_id := i;
v_column_info.column_name := v_desc_tab(i).col_name;
v_column_info.data_length := v_desc_tab(i).col_max_len;
CASE v_desc_tab(i).col_type
WHEN 1 THEN
v_column_info.data_type := 'VARCHAR2, NVARCHAR2';
DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_column_info.data_value);
WHEN 2 THEN
v_column_info.data_type := 'NUMBER, FLOAT';
DBMS_SQL.COLUMN_VALUE(v_cursor, i, n_column_value);
v_column_info.data_value := n_column_value;
WHEN 8 THEN
v_column_info.data_type := 'LONG';
DBMS_SQL.COLUMN_VALUE(v_cursor, i, n_column_value);
v_column_info.data_value := n_column_value;
WHEN 11 THEN
v_column_info.data_type := 'ROWID';
WHEN 12 THEN
v_column_info.data_type := 'DATE';
WHEN 23 THEN
v_column_info.data_type := 'RAW';
WHEN 24 THEN
v_column_info.data_type := 'LONG RAW';
WHEN 96 THEN
v_column_info.data_type := 'CHAR, NCHAR';
WHEN 100 THEN
v_column_info.data_type := 'BINARY_FLOAT';
WHEN 101 THEN
v_column_info.data_type := 'BINARY_DOUBLE';
WHEN 106 THEN
v_column_info.data_type := 'MLSLABEL';
WHEN 112 THEN
v_column_info.data_type := 'CLOB, NCLOB';
WHEN 113 THEN
v_column_info.data_type := 'BLOB';
WHEN 114 THEN
v_column_info.data_type := 'BFILE';
WHEN 180 THEN
v_column_info.data_type := 'TIMESTAMP(6)';
WHEN 181 THEN
v_column_info.data_type := 'IMESTAMP(6) WITH TIME ZONE';
WHEN 182 THEN
v_column_info.data_type := 'NTERVAL YEAR(2) TO MONTH';
WHEN 183 THEN
v_column_info.data_type := 'INTERVAL DAY(2) TO SECOND(6)';
WHEN 231 THEN
v_column_info.data_type := 'TIMESTAMP(6) WITH LOCAL TIME ZONE';
ELSE
v_column_info.data_type := 'OTHERS' || TO_CHAR(v_desc_tab(i).col_type);
END CASE;
v_result(v_idx) := v_column_info;
v_idx := v_idx + 1;
END LOOP;
n_rowID := n_rowID + 1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END get_cursol;
END ANALIZE_SYS_CURSORL_PKG;
HOW_TO_USE.bdy.sql
SET SERVEROUTPUT ON
DECLARE
v_cursor SYS_REFCURSOR;
v_column CURSOR_PKG.t_column_info;
v_meta_data CURSOR_PKG.t_column_info_table;
---ほんとはこっちを解析したかった。
CURSOR c_employee_projects IS
SELECT e.employee_id, e.first_name, e.last_name, p.project_id, p.project_name, ep.role
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id;
---ほんとはこっちを解析したかった。
BEGIN
OPEN v_cursor for
--ここを自由に改変
SELECT e.employee_id, e.first_name, e.last_name, p.project_id, p.project_name, ep.role
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id;
--ここを自由に改変
-- メタデータを取得
CURSOR_PKG.GET_CURSOL(v_cursor ,v_meta_data);
DBMS_OUTPUT.PUT_LINE('-----------------------------');
FOR i IN 1 .. v_meta_data.COUNT LOOP
v_column := v_meta_data(i);
DBMS_OUTPUT.PUT_LINE('row_id: ' || v_column.row_id);
DBMS_OUTPUT.PUT_LINE('col_id: ' || v_column.col_id);
DBMS_OUTPUT.PUT_LINE('Column Name: ' || v_column.column_name);
DBMS_OUTPUT.PUT_LINE('Data Type: ' || v_column.data_type);
DBMS_OUTPUT.PUT_LINE('Data Length: ' || v_column.data_length);
DBMS_OUTPUT.PUT_LINE('Data Value: ' || v_column.data_value);
DBMS_OUTPUT.PUT_LINE('-----------------------------');
END LOOP;
-- カーソルを閉じる
--CLOSE v_cursor;
END;
PL/SQLプロシージャが正常に完了しました。
-----------------------------
row_id: 1
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 101
-----------------------------
row_id: 1
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: John
-----------------------------
row_id: 1
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Doe
-----------------------------
row_id: 1
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1001
-----------------------------
row_id: 1
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project A
-----------------------------
row_id: 1
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: gg
-----------------------------
row_id: 2
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 102
-----------------------------
row_id: 2
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Jane
-----------------------------
row_id: 2
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Smith
-----------------------------
row_id: 2
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1001
-----------------------------
row_id: 2
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project A
-----------------------------
row_id: 2
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: cc
-----------------------------
row_id: 3
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 103
-----------------------------
row_id: 3
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Michael
-----------------------------
row_id: 3
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Brown
-----------------------------
row_id: 3
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1002
-----------------------------
row_id: 3
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project B
-----------------------------
row_id: 3
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: dd
-----------------------------
row_id: 4
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 104
-----------------------------
row_id: 4
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Emily
-----------------------------
row_id: 4
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Davis
-----------------------------
row_id: 4
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1002
-----------------------------
row_id: 4
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project B
-----------------------------
row_id: 4
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: aa
-----------------------------
row_id: 5
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 105
-----------------------------
row_id: 5
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Daniel
-----------------------------
row_id: 5
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Wilson
-----------------------------
row_id: 5
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1003
-----------------------------
row_id: 5
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project C
-----------------------------
row_id: 5
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: ee
-----------------------------
row_id: 6
col_id: 1
Column Name: EMPLOYEE_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 106
-----------------------------
row_id: 6
col_id: 2
Column Name: FIRST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Emma
-----------------------------
row_id: 6
col_id: 3
Column Name: LAST_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 50
Data Value: Taylor
-----------------------------
row_id: 6
col_id: 4
Column Name: PROJECT_ID
Data Type: NUMBER, FLOAT
Data Length: 22
Data Value: 1003
-----------------------------
row_id: 6
col_id: 5
Column Name: PROJECT_NAME
Data Type: VARCHAR2, NVARCHAR2
Data Length: 100
Data Value: Project C
-----------------------------
row_id: 6
col_id: 6
Column Name: ROLE
Data Type: VARCHAR2, NVARCHAR2
Data Length: 20
Data Value: ed
-----------------------------
PL/SQLプロシージャが正常に完了しました。