📄 plgcgml.spb
字号:
PLGgen.nodisplay;
/* Move from list to these five strings. */
string1_inout := NULL;
string2_inout := NULL;
string3_inout := NULL;
string4_inout := NULL;
string5_inout := NULL;
onstring := 1;
PLGgen.list_init;
LOOP
BEGIN
IF PLGgen.end_of_list
THEN
RAISE VALUE_ERROR;
END IF;
l_next := PLGgen.next_list_entry;
IF l_string IS NULL
THEN
l_string := l_next;
ELSE
IF maxlen IS NOT NULL
THEN
IF LENGTH (l_string) + LENGTH (l_next) >
maxlen
THEN
RAISE VALUE_ERROR;
END IF;
END IF;
/* Patch for problem in 8.1 of strings exceeding
32767... */
IF LENGTH (l_string) > 32000
THEN
RAISE VALUE_ERROR;
END IF;
l_string := l_string || CHR (10) || l_next;
END IF;
--p.l ('l_string = ' || l_string);
EXCEPTION
WHEN VALUE_ERROR
THEN
/* Move to next string. */
IF onstring = 1
THEN
string1_inout := l_string;
-- p.l ('string1_inout = ' || string1_inout);
onstring := 2;
ELSIF onstring = 2
THEN
string2_inout := l_string;
-- p.l ('string2_inout = ' || string2_inout);
onstring := 3;
ELSIF onstring = 3
THEN
string3_inout := l_string;
-- p.l ('string3_inout = ' || string3_inout);
onstring := 4;
ELSIF onstring = 4
THEN
string4_inout := l_string;
-- p.l ('string4_inout = ' || string4_inout);
onstring := 5;
ELSIF onstring = 5
THEN
string5_inout := l_string;
onstring := NULL;
-- p.l ('string5_inout = ' || string5_inout);
ELSE
DBMS_OUTPUT.put_line ('Generated text is too long! ' ||
'Partial text has been returned.'
);
EXIT;
END IF;
EXIT WHEN PLGgen.end_of_list;
l_string := l_next;
END;
END LOOP;
/* Uncomment for production
PLGgen.list_clear;
PLGgen.usemultioutlists;
*/
END;
---------------- Generate cgml Scripts
/* Generate cgml text to then be used in genString and genFile. */
PROCEDURE define_array (
pkgname IN VARCHAR2,
arrayname IN VARCHAR2,
fieldlist IN VARCHAR2,
coldelim IN VARCHAR2 := ',',
typedelim IN VARCHAR2 := ' ',
sch IN VARCHAR2 := NULL,
drvr IN VARCHAR2 := NULL
)
/* cgml to generate the code to create the array package. */
IS
v_sch PLGadmin.identifier := NVL (sch, USER);
dyncur1 PLS_INTEGER := DBMS_SQL.open_cursor;
dyncur2 PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk PLS_INTEGER;
stmt PLGadmin.maxvc2;
seglist PLGiseg.seglist_rectype;
dtype PLGadmin.identifier;
stringval PLGadmin.dbmaxvc2;
numberval NUMBER;
dateval DATE;
BEGIN
stmt :=
'CREATE OR REPLACE PACKAGE ' || v_sch || '.' ||
pkgname ||
' IS
TYPE rectype IS RECORD (' ||
fieldlist ||
');
TYPE tabtype IS TABLE OF rectype INDEX BY BINARY_INTEGER; ' ||
arrayname ||
' tabtype;
END;';
/* Create the package. */
DBMS_SQL.parse (dyncur1, stmt, DBMS_SQL.native);
/* Grant access to the package. */
stmt :=
'GRANT EXECUTE ON ' || v_sch || '.' || pkgname ||
' TO PUBLIC';
DBMS_SQL.parse (dyncur1, stmt, DBMS_SQL.native);
DBMS_SQL.close_cursor (dyncur1);
/* Now define as an array to PLG_DOIR */
defarray (pkgname, arrayname, sch, drvr);
EXCEPTION
WHEN OTHERS
THEN
/* Display error and close cursors. */
pl (SQLERRM);
pl (stmt);
DBMS_SQL.close_cursor (dyncur1);
RAISE;
END;
/* May not be current - check over before exposing in CGML */
PROCEDURE array_from_query (
pkgname IN VARCHAR2,
arrayname IN VARCHAR2,
query IN VARCHAR2,
fieldlist IN VARCHAR2 := NULL,
/* Format 'COL TYPE,COL TYPE' */
coldelim IN VARCHAR2 := ',',
typedelim IN VARCHAR2 := ' ',
sch IN VARCHAR2 := NULL,
drvr IN VARCHAR2 := NULL,
def_array_in_oir IN BOOLEAN := TRUE
)
/* Generate the cgml text necessary to define an array
based on a query. */
IS
/* cgml to generate the code to create the array package
based on the query and column list. */
dyncur1 PLS_INTEGER := DBMS_SQL.open_cursor;
dyncur2 PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk PLS_INTEGER;
stmt PLGadmin.maxvc2;
array_row PLS_INTEGER;
seglist PLGiseg.seglist_rectype;
dtype PLGadmin.identifier;
stringval PLGadmin.dbmaxvc2;
numberval NUMBER;
dateval DATE;
-- Comment out for Oracle7
--cols DBMS_SQL.DESC_TAB;
BEGIN
IF def_array_in_oir
THEN
define_array (pkgname,
arrayname,
fieldlist,
coldelim,
typedelim,
sch,
drvr
);
END IF;
/* Fill the array from the query. */
DBMS_SQL.parse (dyncur1, query, DBMS_SQL.native);
IF fieldlist IS NULL
THEN
DBMS_OUTPUT.put_line ('Currently, you must provide a list of fields and datatypes ' ||
' when using PLGcgml.array_from_query.'
);
/* Should be able to use DESCRIBE_COLUMNS to extract
the necessary information...need to implement
DBMS_SQL.DESCRIBE_COLUMNS (
dyncur1,
fdbk,
cols);
FOR indx IN cols.FIRST .. cols.LAST
LOOP
END LOOP;
*/
ELSE
/* Execute DEFINE_COLUMN for each column in the fieldlist. */
PLGiseg.loadstg (fieldlist,
coldelim,
typedelim,
2,
seglist
);
FOR indx IN 1 .. PLGiseg.numitems (seglist)
LOOP
dtype := UPPER (PLGiseg.val (seglist, indx, 2));
IF PLGtype.isstring (dtype)
THEN
DBMS_SQL.define_column (dyncur1,
indx,
stringval,
2000
);
ELSIF PLGtype.isnumber (dtype)
THEN
DBMS_SQL.define_column (dyncur1,
indx,
numberval
);
ELSIF PLGtype.isdate (dtype)
THEN
DBMS_SQL.define_column (dyncur1,
indx,
dateval
);
END IF;
END LOOP;
END IF;
fdbk := DBMS_SQL.execute (dyncur1);
/* Fetch each row and put into the appropriate field. */
LOOP
EXIT WHEN DBMS_SQL.fetch_rows (dyncur1) = 0;
array_row := DBMS_SQL.last_row_count;
FOR indx IN 1 .. PLGiseg.numitems (seglist)
LOOP
stmt :=
'BEGIN ' || USER || '.' || pkgname || '.' ||
arrayname ||
'(' ||
array_row ||
').' ||
PLGiseg.val (seglist, indx, 1) ||
' := :val; END;';
DBMS_SQL.parse (dyncur2, stmt, DBMS_SQL.native);
/* Extract and bind the value, exec the SQL. */
dtype := UPPER (PLGiseg.val (seglist, indx, 2));
IF PLGtype.isstring (dtype)
THEN
DBMS_SQL.column_value (dyncur1,
indx,
stringval
);
DBMS_SQL.bind_variable (dyncur2,
'val',
stringval,
2000
);
ELSIF PLGtype.isnumber (dtype)
THEN
DBMS_SQL.column_value (dyncur1,
indx,
numberval
);
DBMS_SQL.bind_variable (dyncur2,
'val',
numberval
);
ELSIF PLGtype.isdate (dtype)
THEN
DBMS_SQL.column_value (dyncur1,
indx,
dateval
);
DBMS_SQL.bind_variable (dyncur2,
'val',
dateval
);
END IF;
fdbk := DBMS_SQL.execute (dyncur2);
END LOOP;
END LOOP;
DBMS_SQL.close_cursor (dyncur1);
DBMS_SQL.close_cursor (dyncur2);
/* Now define as an array to PLG_DOIR */
defarray (pkgname, arrayname, sch, drvr);
EXCEPTION
WHEN OTHERS
THEN
/* Display error and close cursors. */
pl (SQLERRM);
pl (stmt);
DBMS_SQL.close_cursor (dyncur1);
DBMS_SQL.close_cursor (dyncur2);
END;
/* May not be current - check over before exposing in CGML */
PROCEDURE array_from_table (
pkgname IN VARCHAR2,
arrayname IN VARCHAR2,
tab IN VARCHAR2,
uselist IN VARCHAR2 := NULL,
ignorelist IN VARCHAR2 := NULL,
whr IN VARCHAR2 := NULL,
delim IN VARCHAR2 := ',',
sch IN VARCHAR2 := NULL,
drvr IN VARCHAR2 := NULL,
def_array_in_oir IN BOOLEAN := TRUE
)
IS
v_tab PLGadmin.identifier := UPPER (tab);
v_uselist PLGadmin.dbmaxvc2;
v_ignorelist PLGadmin.dbmaxvc2;
v_allcols BOOLEAN;
v_len INTEGER;
v_addcol BOOLEAN;
query PLGadmin.maxvc2;
fieldlist PLGadmin.maxvc2;
coltab PLGcols.col_tabtype;
PROCEDURE rstrct (
split_in IN VARCHAR2,
olist IN VARCHAR2,
nlist IN OUT VARCHAR2
)
IS
BEGIN
IF olist IS NOT NULL
THEN
nlist := UPPER (delim || olist || delim);
ELSE
v_len := INSTR (tab, split_in);
IF v_len = LENGTH (tab)
THEN
nlist := NULL;
v_tab := SUBSTR (tab, 1, v_len - 1);
ELSIF v_len > 0
THEN
nlist :=
UPPER (delim || SUBSTR (tab, v_len + 1) ||
delim
);
v_tab := SUBSTR (v_tab, 1, v_len - 1);
END IF;
END IF;
END;
BEGIN
/* Construct query and field list */
rstrct (c_include_col, uselist, v_uselist);
rstrct (c_exclude_col, ignorelist, v_ignorelist);
v_allcols :=
v_uselist IS NULL
AND v_ignorelist IS NULL;
coltab := PLGcols.fortab (v_tab, sch);
IF coltab.COUNT > 0
THEN
FOR colind IN coltab.FIRST .. coltab.LAST
LOOP
v_addcol := v_allcols;
IF NOT v_addcol
THEN
v_addcol :=
NVL (INSTR (v_uselist,
delim ||
coltab (colind).column_name ||
delim
),
0
) > 0
OR NVL (INSTR (v_ignorelist,
delim ||
coltab (colind).column_name ||
delim
),
1
) = 0;
END IF;
IF v_addcol
THEN
IF PLGtype.isstring (coltab (colind).data_type
)
THEN
coltab (colind).data_type :=
coltab (colind).data_type || '(2000)';
END IF;
fieldlist :=
fieldlist || delim ||
coltab (colind).column_name ||
' ' ||
coltab (colind).data_type;
query :=
query || delim ||
coltab (colind).column_name;
END IF;
END LOOP;
fieldlist := LTRIM (fieldlist, delim);
query :=
'SELECT ' || LTRIM (query, delim) || ' FROM ' || tab ||
' WHERE ' ||
NVL (whr, '1=1');
/* Use underlying engine. */
array_from_query (pkgname,
arrayname,
query,
fieldlist,
sch => sch,
drvr => drvr,
def_array_in_oir => def_array_in_oir
);
ELSE
pl ('Build array from table: no columns found for "' ||
tab ||
'".'
);
END IF;
END;
PROCEDURE define_table_array (
tab IN VARCHAR2,
whr IN VARCHAR2 := NULL,
tabsch IN VARCHAR2 := NULL,
pkgsch IN VARCHAR2 := NULL,
drvr IN VARCHAR2 := NULL
)
IS
v_tabsch PLGadmin.identifier := NVL (tabsch, USER);
v_pkgsch PLGadmin.identifier
:= NVL (pkgsch, PLGadmin.genxowner);
v_tab PLGadmin.identifier
:= v_tabsch || '.' || tab;
v_array PLGadmin.identifier
:= PLGdoir.c_array_prefix ||
SUBSTR (tab,
1,
30 - PLGdoir.c_array_prefix_len
);
v_pkg VARCHAR2 (2000)
:= PLGdoir.c_array_package_prefix ||
SUBSTR (tab,
1,
30 - PLGdoir.c_array_prefix_len
);
dyncur1 PLS_INTEGER := DBMS_SQL.open_cursor;
fdbk PLS_INTEGER;
stmt PLGadmin.maxvc2;
BEGIN
stmt :=
'CREATE OR REPLACE PACKAGE ' ||
/* 99.2.8 Leave it to default v_pkgsch || '.' || */
v_pkg ||
' IS
TYPE tabtype IS TABLE OF ' ||
v_tab ||
'%ROWTYPE INDEX BY BINARY_INTEGER; ' ||
v_array ||
' tabtype;
END;';
/* Create the package. */
DBMS_SQL.parse (dyncur1, stmt, DBMS_SQL.native);
stmt :=
'CREATE OR REPLACE PACKAGE BODY ' ||
/* 99.2.8 Leave it to default v_pkgsch || '.' || */
v_pkg ||
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -