Oracle_ Stored Procedure Notes

# Refresh member tag function {color:red} Fun_refresh_code{color}
{Noformat}
CREATE OR REPLACE FUNCTION fun_refresh_code (v_code number: = 0)
RETURN VARCHAR2 is
Vsql VARCHAR2 (1000);
Outpass VARCHAR2 (100);
Outfail VARCHAR2 (100);
Refreshresult VARCHAR2 (100);
Errormgs VARCHAR2 (1000);
BEGIN
Outpass: = ' Mview_css_mbrlabel_ ' | | V_code | | ' Refresh success ';
Outfail: = ' Mview_css_mbrlabel_ ' | | V_code | | ' Refresh failed ';
Vsql: = ' call Dbms_mview.refresh (' | | ' Mview_css_mbrlabel_ ' | | V_code | | ‘)‘;
Execute immediate vsql;
Refreshresult: = Outpass;
INSERT into Css_procexeclog
(UUID, CREATED, ErrorMsg, TableName, operation, STATUS)
VALUES
(Sys_guid (),
Sysdate,
‘‘,
' Mview_css_mbrlabel_ ' | | V_code,
' Refresh ',
' Success ');
COMMIT;
--Return outpass;
Exception
When others then
Refreshresult: = Outfail;
Errormgs: = Sqlcode | | SQLERRM (Sqlcode);
INSERT into Css_procexeclog
(UUID, CREATED, ErrorMsg, TableName, operation, STATUS)
VALUES
(Sys_guid (),
Sysdate,
Errormgs,
' Mview_css_mbrlabel_ ' | | V_code,
' Refresh ',
' failed ');
COMMIT;
--Return outfail;
return refreshresult;
END;
{Noformat}
# Delete member tag function {color:red} Fun_delete_code{color}
{Noformat}
CREATE OR REPLACE FUNCTION fun_delete_code (v_code number: = 0) RETURN varchar2 is
Vsql VARCHAR2 (1000);
Outpass VARCHAR2 (100);
Outfail VARCHAR2 (100);
Deleteresult VARCHAR2 (100);
Errormgs VARCHAR2 (1000);
BEGIN
Outpass: = ' Mview_css_mbrlabel_ ' | | V_code | | ' Delete succeeded ';
Outfail: = ' Mview_css_mbrlabel_ ' | | V_code | | ' Delete failed ';
Vsql: = ' (drop materialized VIEW | | ' Mview_css_mbrlabel_ ' | | V_code | | ‘)‘;
Execute immediate vsql;
Deleteresult: = Outpass;
INSERT into Css_procexeclog
(UUID, CREATED, ErrorMsg, TableName, operation, STATUS)
VALUES
(Sys_guid (),
Sysdate,
‘‘,
' Mview_css_mbrlabel_ ' | | V_code,
' Delete ',
' Success ');
COMMIT;
--Return outpass;
Exception
When others then
Deleteresult: = Outfail;
Errormgs: = Sqlcode | | SQLERRM (Sqlcode);
INSERT into Css_procexeclog
(UUID, CREATED, ErrorMsg, TableName, operation, STATUS)
VALUES
(Sys_guid (),
Sysdate,
Errormgs,
' Mview_css_mbrlabel_ ' | | V_code,
' Delete ',
' failed ');
COMMIT;
--Return outfail;
return deleteresult;
END;
{Noformat}
# refresh View {color:red} Mbrlabelrefresh{color}
{Noformat}
CREATE OR REPLACE PROCEDURE Mbrlabelrefresh is
V_code Css_label_mview.code%type;
VSQL_MVIEW_CSS_MBR VARCHAR2 (1000);
Vsql_css_label_mview VARCHAR2 (1000);
Vsql_mbrlabelproc VARCHAR2 (1000);
Vsql_mbrlabelviewproc VARCHAR2 (1000);
Vsql_mbrlabellogproc VARCHAR2 (1000);
Vsql VARCHAR2 (1000);
--Defining variables
CURSOR Code_cursor is
Select L.code
From Css_label_mview l
Where NOT EXISTS (SELECT *
From User_mviews VL
where L.code = SUBSTR (Vl.mview_name, 20));
--Defining cursors
BEGIN
VSQL_MVIEW_CSS_MBR: = Call Dbms_mview.refresh (' mview_css_mbr ');
Vsql_css_label_mview: = Call Dbms_mview.refresh (' Css_label_mview ');
--Refresh the statements of members and TAG tables
Vsql_mbrlabellogproc: = Call Vsql_mbrlabellogproc ();
--CSS Library generation log
Vsql_mbrlabelproc: = Call Mbrlabelproc ();
--Generate Membership Tag statements
Vsql_mbrlabelviewproc: = Call Mbrlabelviewproc ();
--Refresh the membership tag Relationship statement
Execute immediate vsql_mbrlabellogproc;
--Execute the log table that generated the CSS library
Execute immediate vsql_mview_css_mbr;
Execute immediate vsql_css_label_mview;
--Execute statements that refresh Members and label tables
Execute immediate vsql_mbrlabelproc;
--Execute Generate Membership Tag statement
For code_cur in Code_cursor LOOP
Begin
V_code: = Code_cur.code;
Select Fun_refresh_code (V_code) from dual;
End
END LOOP;
--Call function to refresh
--Refresh the existing tag view, follow the cursor query code, refresh each code materialized view once, and log
Execute immediate vsql_mbrlabelviewproc;
--Execute stored procedures to refresh membership and tag relationships
END Mbrlabelrefresh;
{Noformat}
# delete View {color:red} Mviewmbrlabeldelete{color}
{Noformat}
CREATE OR REPLACE PROCEDURE Mviewmbrlabeldelete is
V_code Css_label_mview.code%type;
Vsql VARCHAR2 (1000);
--Defining variables
CURSOR Code_cursor is
Select L.code
From Css_label_mview l
Where NOT EXISTS (SELECT *
From User_mviews VL
where L.code = SUBSTR (Vl.mview_name, 20));
--Defining cursors
BEGIN
For code_cur in Code_cursor LOOP
Begin
V_code: = Code_cur.code;
Select Fun_delete_code (V_code) from dual;
END LOOP;

--Call function to refresh
--Remove the materialized view from the cursor and delete the log
END Mviewmbrlabeldelete;
{Noformat}

Oracle_ Stored Procedure Notes