例如传一个时间v_str_date给sql1语句 sql1: select * from tb1 where dat1=v_str_date 我只想要原理,具体语句还没设置
请参考下面的代码:注意/****************/之间的,动态SQL的参数传递。CREATE OR REPLACE PROCEDURE ProcedureTest1 IS -- 定义光标,从user_tab_columns数据字典中检索列名 CURSOR c1 IS SELECT column_name FROM user_tab_columns WHERE table_name = 'TEST1'; -- 保存列名 vColumnName VARCHAR2(20); -- 保存记录个数 vNum NUMBER(4); vSQL VARCHAR2(200); BEGIN FOR r IN c1 LOOP vColumnName := r.column_name; vSQL := 'SELECT COUNT(1) FROM Test1 WHERE ' || vColumnName || '=25'; /********************************************************************************/ EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM Test1 WHERE ' || vColumnName || '=25' INTO vNum; -- 给动态sql传递参数 EXECUTE IMMEDIATE 'INSERT INTO temp_ke VALUES(:1, :2)' USING vColumnName, vNum; /***********************************************************************************/
END LOOP; COMMIT; END ProcedureTest1;
create table RAS_DAT_AUDIT_WARN_CONF ( b_audit_func_id VARCHAR2(8), t_audit_func_id VARCHAR2(8), run_sqls VARCHAR2(4000), status CHAR(1) not null, opt_name VARCHAR2(12), opt_time DATE ) CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (4000) CREATE OR REPLACE FUNCTION split (--函数定义 p_str IN VARCHAR2, p_delimiter IN VARCHAR2 default(',') --分隔符,默认逗号 ) RETURN split_type IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); my_split split_type := split_type (); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter);
WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i);
IF j = 0 THEN j := len; str := SUBSTR (p_str, i); my_split.EXTEND; my_split (my_split.COUNT) := str;
IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; my_split.EXTEND; my_split (my_split.COUNT) := str; END IF; END LOOP;
RETURN my_split; END split;--存储过程 create or replace procedure P_AUDIT_WARN_PLATFORM(v_error_code out varchar2,start_time varchar2,s_audit_id varchar2) isv_error_info varchar2(256); v_b_audit_func_id varchar2(8); v_t_audit_func_id varchar2(8); v_run_sqls varchar2(4000); v_run_sql varchar2(4000); v_exec_cur_sql varchar2(4000);type cur is ref cursor; v_audit_cur cur; v_sqls_cur cur; v_exception_cursor exception; begin open v_audit_cur for select b_audit_func_id,t_audit_func_id,run_sqls from RAS_DAT_AUDIT_WARN_CONF where status=1 and b_audit_func_id=s_audit_id;LOOP FETCH v_audit_cur INTO v_b_audit_func_id,v_t_audit_func_id,v_run_sqls; IF v_audit_cur%rowcount=0 THEN v_error_info :='从配置表RAS_DAT_AUDIT_WARN_CONF中没有读取到数据'; raise v_exception_cursor; END IF; IF v_audit_cur%notfound THEN exit; END IF; open v_sqls_cur for select * from TABLE(CAST(split(v_run_sqls,';') AS split_type)) ; LOOP FETCH v_sqls_cur INTO v_run_sql; IF v_audit_cur%rowcount=0 THEN v_error_info :='没有配置SQL语句'; raise v_exception_cursor; END IF; IF v_audit_cur%notfound THEN--这里游标失效 exit; END IF; END LOOP; close v_sqls_cur;
END LOOP; exception when v_exception_cursor then v_error_code:=v_error_info; WHEN OTHERS THEN v_error_code:='ORA'||sqlcode;end P_AUDIT_WARN_Platform; open v_sqls_cur for select * from TABLE(CAST(split(v_run_sqls,';') AS split_type)) ; LOOP FETCH v_sqls_cur INTO v_run_sql; IF v_audit_cur%rowcount=0 THEN v_error_info :='没有配置SQL语句'; raise v_exception_cursor; END IF; IF v_audit_cur%notfound THEN--这里游标失效 exit; END IF; END LOOP;游标跑到最后一行就死循环了。LuiseRADL在帮忙回答下
sql语句这么写v_sql:='select * from tb where col1=:1';在存储过程里面这样写:execute immediate v_sql using v_col1;
sql1: select * from tb1 where dat1=v_str_date
我只想要原理,具体语句还没设置
-- 定义光标,从user_tab_columns数据字典中检索列名
CURSOR c1 IS SELECT column_name FROM user_tab_columns WHERE table_name = 'TEST1';
-- 保存列名
vColumnName VARCHAR2(20);
-- 保存记录个数
vNum NUMBER(4);
vSQL VARCHAR2(200);
BEGIN
FOR r IN c1
LOOP
vColumnName := r.column_name;
vSQL := 'SELECT COUNT(1) FROM Test1 WHERE ' || vColumnName || '=25';
/********************************************************************************/
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM Test1 WHERE ' || vColumnName || '=25'
INTO vNum;
-- 给动态sql传递参数
EXECUTE IMMEDIATE 'INSERT INTO temp_ke VALUES(:1, :2)'
USING vColumnName, vNum;
/***********************************************************************************/
END LOOP;
COMMIT;
END ProcedureTest1;
(
b_audit_func_id VARCHAR2(8),
t_audit_func_id VARCHAR2(8),
run_sqls VARCHAR2(4000),
status CHAR(1) not null,
opt_name VARCHAR2(12),
opt_time DATE
)
CREATE OR REPLACE TYPE split_type IS TABLE OF VARCHAR2 (4000)
CREATE OR REPLACE FUNCTION split (--函数定义
p_str IN VARCHAR2,
p_delimiter IN VARCHAR2 default(',') --分隔符,默认逗号
)
RETURN split_type
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
my_split split_type := split_type ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);
WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);
IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
my_split.EXTEND;
my_split (my_split.COUNT) := str;
IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
my_split.EXTEND;
my_split (my_split.COUNT) := str;
END IF;
END LOOP;
RETURN my_split;
END split;--存储过程
create or replace procedure P_AUDIT_WARN_PLATFORM(v_error_code out varchar2,start_time varchar2,s_audit_id varchar2) isv_error_info varchar2(256);
v_b_audit_func_id varchar2(8);
v_t_audit_func_id varchar2(8);
v_run_sqls varchar2(4000);
v_run_sql varchar2(4000);
v_exec_cur_sql varchar2(4000);type cur is ref cursor;
v_audit_cur cur;
v_sqls_cur cur;
v_exception_cursor exception;
begin
open v_audit_cur for select b_audit_func_id,t_audit_func_id,run_sqls from RAS_DAT_AUDIT_WARN_CONF where status=1 and b_audit_func_id=s_audit_id;LOOP
FETCH v_audit_cur INTO v_b_audit_func_id,v_t_audit_func_id,v_run_sqls;
IF v_audit_cur%rowcount=0 THEN
v_error_info :='从配置表RAS_DAT_AUDIT_WARN_CONF中没有读取到数据';
raise v_exception_cursor;
END IF;
IF v_audit_cur%notfound THEN
exit;
END IF;
open v_sqls_cur for select * from TABLE(CAST(split(v_run_sqls,';') AS split_type)) ;
LOOP
FETCH v_sqls_cur INTO v_run_sql;
IF v_audit_cur%rowcount=0 THEN
v_error_info :='没有配置SQL语句';
raise v_exception_cursor;
END IF;
IF v_audit_cur%notfound THEN--这里游标失效
exit;
END IF;
END LOOP;
close v_sqls_cur;
END LOOP;
exception
when v_exception_cursor then
v_error_code:=v_error_info;
WHEN OTHERS THEN
v_error_code:='ORA'||sqlcode;end P_AUDIT_WARN_Platform;
open v_sqls_cur for select * from TABLE(CAST(split(v_run_sqls,';') AS split_type)) ;
LOOP
FETCH v_sqls_cur INTO v_run_sql;
IF v_audit_cur%rowcount=0 THEN
v_error_info :='没有配置SQL语句';
raise v_exception_cursor;
END IF;
IF v_audit_cur%notfound THEN--这里游标失效
exit;
END IF;
END LOOP;游标跑到最后一行就死循环了。LuiseRADL在帮忙回答下