Definition and call of dynamic RefCursor

1. Definition: outputs; 2. Implement the Dynamic cursor stored procedure as the out parameter. For example, use tablename to dynamically obtain outputs (I _TABLE_NAMEINVARCHAR2, outputs) ASBEGINIFUPPER (I _TABLE_NAME) T_BANK_ACCOU

1. definition: type ref_cur is ref cursor; 2. the Implementation of Dynamic cursor AS the out parameter stored PROCEDURE is the simplest example. Based on the table name, the cursor PROCEDURE P_GET_CUR (I _TABLE_NAME IN VARCHAR2, O_REF_CUR OUT REF_CUR) as begin if upper (I _TABLE_NAME) = 't_ BANK_ACCOU

1. Definition:

Type ref_cur is ref cursor;

2. Implementation of Dynamic cursor stored procedure as the out Parameter

The simplest example is to dynamically obtain cursor based on table name.

PROCEDURE P_GET_CUR (I _TABLE_NAME IN VARCHAR2, O_REF_CUR OUT REF_CUR)
BEGIN
If upper (I _TABLE_NAME) = 't_ bank_account' THEN
OPEN O_REF_CUR
SELECT BANK_ACCOUNT
FROM T_BANK_ACCOUNT
WHERE BANK_ACCOUNT is not null;

Elsif upper (I _TABLE_NAME) = 't_ FB_PAYMENT_JP 'THEN
OPEN O_REF_CUR
SELECT BANK_ACCOUNT
FROM T_FB_PAYMENT_JP
WHERE BANK_ACCOUNT is not null;

End if;

END P_GET_CUR;

3. Call the function of the mask port in the project as an example to share with you:

Procedure p_bank_acco_mask (I _table_name in varchar2,
I _coulumn in varchar,
O_error_str in out varchar2,
O_result in out number,
M_has_exception in out boolean)

V_update_ SQL varchar (800 );
V_update_sql2 varchar (800 );
I int: = 1;
V_bank_account VARCHAR2 (50 );
V_bank_account_new VARCHAR2 (50 );
V_char VARCHAR (1 );
V_bank_account_curs ref_cur;
V_select_ SQL VARCHAR2 (800 );
Begin

O_result: = PKG_LS_PUB_CODE_CST.BATCH_RESULT__SUCCESS;

BEGIN
P_get_cur (I _table_name, v_bank_account_curs );
Savepoint point;
V_update_sql2: = '';
LOOP
FETCH v_bank_account_curs INTO v_bank_account;

Exit when v_bank_account_curs % NOTFOUND;
BEGIN
For I in 1 .. length (v_bank_account) LOOP
V_select_ SQL: = 'select SUBSTR ('| I _coulumn |', '| I |', 1) from '| I _table_name | 'where' | I _coulumn |' = ''' | v_bank_account | ''' and rownum <= 1 ';
V_char: = f_get_char (v_select_ SQL );
IF v_char IS NULL THEN
RETURN;
End if;
IF v_char = '2' OR v_char = '3' THEN--1
V_char: = '1 ';
ELSIF v_char = '5' OR v_char = '6' THEN-4
V_char: = '4 ';
ELSIF v_char = '8' OR v_char = '9' THEN--7
V_char: = '7 ';
End if;
V_bank_account_new: = v_bank_account_new | v_char;
End loop;
-- Shocould support bank_code = '20140901'
If I _table_name = 't_ DT_ACCOUNT_JP 'then
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (Head_Bank_Code' | ',' |
''' 9900 ''' | ',' | ''' 12340-01111281 ''' | ',' |
V_bank_account_new | ') where' | I _coulumn | '= ''' | v_bank_account | '''';
Elsif I _table_name ='t _ CASH_BANK 'then
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (CASH_BANK' | ',' |
''' 9900 ''' | ',' | ''' 12340-01111281 ''' | ',' |
V_bank_account_new | ') where' | I _coulumn | '= ''' | v_bank_account | '''';
Elsif I _table_name = 't_ CASH_BANK_LOG 'then
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (CASH_BANK' | ',' |
''' 9900 ''' | ',' | ''' 12340-01111281 ''' | ',' |
V_bank_account_new | ') where' | I _coulumn | '= ''' | v_bank_account | '''';
Elsif I _table_name = 't_agm_agent 'then
V_update_ SQL: = 'update' | I _table_name |
'Set COMM_ACCOUNT = '| v_bank_account_new | ''|
'Where COMM_PAY_BANK_CODE <> '000000' and '| I _coulumn |' = ''' | v_bank_account | '''';
V_update_sql2: = 'update' | I _table_name |
'Set COMM_PAY_BRANCH_CODE = '000000', '|
'Comm_account = ''01111281 ''' |
'Where COMM_PAY_BANK_CODE = ''9900 ''';
Elsif I _table_name ='t _ SHOSHIHARAIRIREKIJOUHOU_TBL 'then
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (substr (BANK_ACCOUNT_NO, 2, 4)' |
',' | ''' 9900 ''' | ',' | ''' 12340-01111281 ''' |
',' | V_bank_account_new | ') where' | I _coulumn |' = ''' | v_bank_account | '''';
Elsif I _table_name = 't_ nyushuukinrirekijouhou 'then
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (substr (KOUZA_NO, 2, 4)' |
',' | ''' 9900 ''' | ',' | ''' 12340-01111281 ''' |
',' | V_bank_account_new | ') where' | I _coulumn |' = ''' | v_bank_account | '''';
Elsif I _table_name = 't_ ACCOUNT_APPLY_TBL 'then
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (substr (ACCOUNT_NO, 1, 4)' |
',' | ''' 9900 ''' | ',' | ''' 12340-01111281 ''' |
',' | V_bank_account_new | ') where' | I _coulumn |' = ''' | v_bank_account | '''';
Elsif I _table_name = 't_ LSIF_OTHERS 'then
V_update_ SQL: = 'update' | I _table_name | 'set KOUZAINO = '|
V_bank_account_new | ''| 'where BANKCN <>'' 9900 ''and '| I _coulumn |' = ''' | v_bank_account | '''';
V_update_sql2: = 'update' | I _table_name |
'Set SITENCNJYO = '000000', '|
'Sitencnka = ''40'', '|
'Kouzaino = ''01111281 ''' |
'Where BANKCN = ''9900 ''';
Elsif I _table_name = 't_ LSIF_GETUMATU_NENMATU_MASTER 'then
V_update_ SQL: = 'update' | I _table_name | 'set KOUZAINO = '|
V_bank_account_new | ''| 'where BANKCN <>'' 9900 ''and '| I _coulumn |' = ''' | v_bank_account | '''';
V_update_sql2: = 'update' | I _table_name |
'Set SITENCN = '000000', '|
'Kouzaino = ''01111281 ''' |
'Where BANKCN = ''9900 ''';
Elsif I _table_name ='t _ LSIF_KAIKEI 'then
V_update_ SQL: = 'update' | I _table_name | 'set WUZANO = '|
V_bank_account_new | ''|
'Where GANKOWUMEYISHOUWUCN <> '000000' and '| I _coulumn |' = ''' | v_bank_account | '''';
V_update_sql2: = 'update' | I _table_name |
'Set SHITEYINMEYISYOUWUCN = '000000', '|
'Wuzano = ''01111281 ''' |
'Where GANKOWUMEYISHOUWUCN = '000000 ''';
Elsif I _table_name = 't_ LSIF_IDOUEXTR 'and I _coulumn = 'kouzaino _ zn' then
V_update_ SQL: = 'update' | I _table_name |
'Set KOUZAINO_ZEN = '| v_bank_account_new | ''|
'Where GINKOUCN_ZEN <> ''9900'' and '| I _coulumn |' = ''' | v_bank_account | '''';
V_update_sql2: = 'update' | I _table_name |
'Set shitencn_zen = '000000', '|
'Shitencnge_zen = '40', '|
'Kouzaino_zen = '000000''' |
'Where GINKOUCN_ZEN = ''9900 ''';
Elsif I _table_name = 't_ LSIF_IDOUEXTR 'and I _coulumn = 'kouzaino _ go' then
V_update_ SQL: = 'update' | I _table_name | 'set KOUZAINO_GO = '|
V_bank_account_new | ''|
'Where GINKOUCN_GO <> ''9900'' and '| I _coulumn |' = ''' | v_bank_account | '''';
V_update_sql2: = 'update' | I _table_name |
'Set shitencn_go = '000000', '|
'Shitencnge_go = '40', '|
'Kouzaino_go = '000000''' |
'Where GINKOUCN_GO = '000000 ''';
Elsif I _table_name = 't_ DIAGNOSIS_CHARGE_DATA 'then
V_update_ SQL: = 'update' | I _table_name | 'set ACCOUNT_NO = '|
V_bank_account_new | ''| 'where BANK_CN <>'' 9900 ''and '| I _coulumn |' = ''' | v_bank_account | '''';
V_update_sql2: = 'update' | I _table_name |
'Set BRANCH_CN = '000000', '|
'Account_no = ''01111281 ''' |
'Where BANK_CN = '000000 ''';
Elsif I _table_name = 't_ife_monthly_getumatu_master 'then
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (BANKCN' | ',' |
''' 9900 ''' | ',' | ''' 12340-01111281 ''' | ',' |
V_bank_account_new | ') where' | I _coulumn | '= ''' | v_bank_account | '''';
Else
V_update_ SQL: = 'update' | I _table_name | 'set' | I _coulumn |
'= Decode (bank_code' | ',' |
''' 9900 ''' | ',' | ''' 12340-01111281 ''' | ',' |
V_bank_account_new | ') where' | I _coulumn | '= ''' | v_bank_account | '''';
End if;
V_bank_account_new: = ''; -- clear data
Execute immediate v_update_ SQL;
If v_update_sql2 is not null then
Execute immediate v_update_sql2;
End if;
Pkg_pub_scd_ci.p_batch_commit ();
EXCEPTION
When others then
Rollback to point;
O_result: = pkg_ls_pub_code_cst.batch_result1_fail;
O_error_str: = I _table_name | ',';
Pkg_pub_scd_ci.p_log_error ('P _ bank_acco_mask, failed to mask table: '|
I _table_name | ', error info:' |
Sqlerrm | '------- update SQL =' |
V_update_ SQL );
M_has_exception: = true;
End;
End loop;
CLOSE v_bank_account_curs;
END;
End p_bank_acco_mask;

The principle of dynamically creating a cursor function is the same as that of creating a cursor. For details about other cursor with parameters, refer to the following:

-- Procedure:
-------------------- Declare a Package --------------
Create or replace package pkg_test
AS
TYPEmyrctypeIS ref cursor;

PROCEDURE get_r (p_id NUMBER, p_rc OUT myrctype); -- declare Procedure named get in Package (only the interface has no content)
END pkg_test;

----------------- Declare the Package Body, that is, the content in the Package above, including Procedure get ---------------------
Create or replace package body pkg_test
AS
PROCEDURE get_r (p_id NUMBER, p_rc OUT myrctype)
IS
Sqlstr VARCHAR2 (500 );
BEGIN
IF p_id = 0 THEN
OPEN p_rc
Select id, NAME, sex, address, postcode, birthday
FROM student;
ELSE
Sqlstr: =
'Select id, name, sex, address, postcode, birthday
From student where id =: w_id '; -- w_id is a parameter,
-- The following p_rc is a ref cursor type and is an OUT parameter. A record set is returned. USING p_id is used to replace the w_id value in the preceding SQL :)
OPEN p_rc FOR sqlstr USING p_id;
End if;
END get;
END pkg_test;

-- Function returns the record set in the same principle as above. Instead, the return value of the function is used to return the record set.

Function return record set:
Create a package and package body and function defined with ref cursor:
The Code is as follows:
CREATE OR REPLACE
Package pkg_test

Type myrctype is ref cursor;
Function get_r (intID number) return myrctype;
End pkg_test;
/
CREATE OR REPLACE
Package body pkg_test
-- Function body
Function get_r (intID number) return myrctype is
Rc myrctype; -- defines the ref cursor variable
Sqlstr varchar2 (500 );
Begin
If intID = 0 then

-- The static test directly returns the result using the select statement.
Open rc for select id, name, sex, address, postcode, birthday from student;
Else
-- Assign a value to a dynamic SQL statement. Use w_id to declare that the variable is obtained from the outside.
Sqlstr: = 'select id, name, sex, address, postcode, birthday from student where id =: w_id ';
-- Dynamic Test: return results using sqlstr strings and PASS Parameters using keywords
Open rc for sqlstr using intid;
End if;
Return rc;
End get;
End pkg_test;