1
.动态SQL传入字符串
CREATE
OR
REPLACE
PROCEDURE
p_test
(
v_str
VARCHAR2
)
AS
TYPE
cur_type
IS
REF
CURSOR
;
c_fzt cur_type
;
v_sql
VARCHAR2
(
2000
);
v_jrh
VARCHAR2
(
20
);
BEGIN
v_sql
:=
' SELECT jrh FROM fzt_dd WHERE rownum<10 and prod_name IN ('
||
v_str
||
')'
;
OPEN
c_fzt
FOR
v_sql
;
LOOP
FETCH
c_fzt
INTO
v_jrh
;
EXIT
WHEN
c_fzt
%
NOTFOUND
;
dbms_output.put_line
(
v_jrh
);
END
LOOP
;
END
;
BEGIN
p_test
(
'''普通电话'',''宽带'''
);
END
;
2
.动态SQL传入数字
CREATE
OR
REPLACE
PROCEDURE
p_test
(
v_str
VARCHAR2
)
AS
TYPE
cur_type
IS
REF
CURSOR
;
c_fzt cur_type
;
v_sql
VARCHAR2
(
2000
);
v_jrh
VARCHAR2
(
20
);
BEGIN
v_sql
:=
' SELECT jrh FROM fzt_dd WHERE rownum<10 and prod_spec_id IN ('
||
v_str
||
')'
;
OPEN
c_fzt
FOR
v_sql
;
LOOP
FETCH
c_fzt
INTO
v_jrh
;
EXIT
WHEN
c_fzt
%
NOTFOUND
;
dbms_output.put_line
(
v_jrh
);
END
LOOP
;
END
;
BEGIN
p_test
(
'9,2'
);
END
;
3
.动态SQL传入SQL表
CREATE
OR
REPLACE
PROCEDURE
p_test
(
v_str
VARCHAR2
)
AS
TYPE
cur_type
IS
REF
CURSOR
;
c_fzt cur_type
;
v_sql
VARCHAR2
(
2000
);
v_jrh
VARCHAR2
(
20
);
BEGIN
v_sql
:=
' SELECT jrh FROM fzt_dd WHERE rownum<10 and prod_spec_id IN ('
||
v_str
||
')'
;
OPEN
c_fzt
FOR
v_sql
;
LOOP
FETCH
c_fzt
INTO
v_jrh
;
EXIT
WHEN
c_fzt
%
NOTFOUND
;
dbms_output.put_line
(
v_jrh
);
END
LOOP
;
END
;
BEGIN
p_test
(
'select prod_spec_id from fzt_dd where prod_name=''普通电话'''
);
END
;