17,086
社区成员
发帖
与我相关
我的任务
分享简单的过程
create or replace procedure test
(jbh in varchar2)
as
str_sql varchar2(100);
r_count number;
begin
str_sql:='select count(*) from jjxxx where ssjz like '%'||jbh||'%'';
execute immediate str_sql into r_count ;
dbms_output.put_line(to_char(r_count));
end;
/我想求出模糊查询的数量放入变量中，但老说SQL语句太长，怎么办，一定要用EXEC IMMEDIATE语法，帮忙了！！
(jbh in varchar2)
as
str_sql varchar2(100);
r_count number;
begin
str_sql:='select count(*) from jjxxx where ssjz like '%'||jbh||'%'';
execute immediate str_sql into r_count ;
dbms_output.put_line(to_char(r_count));
end;
/我想求出模糊查询的数量放入变量中，但老说SQL语句太长，怎么办，一定要用EXEC IMMEDIATE语法，帮忙了！！
...全文
请发表友善的回复…
发表回复
myf7961 2003-11-19
- 打赏
- 举报
好了，三分！！
beckhambobo 2003-11-18
- 打赏
- 举报
str_sql:='select count(*) from jjxxx where instr(ssjz,''jbh'')';
pxbreeze 2003-11-18
- 打赏
- 举报
同意chanet
chanet 2003-11-18
- 打赏
- 举报
如上语句在 sql*plus 下运行通过.
SET serveroutput ON -- 设置显示输出
由于想输出某条件下的总数.可以用 select ... into ... from .. where...
就可以了.
测试:
SQL>exec test('aaa');
... Good luck ...
SET serveroutput ON -- 设置显示输出
由于想输出某条件下的总数.可以用 select ... into ... from .. where...
就可以了.
测试:
SQL>exec test('aaa');
... Good luck ...
chanet 2003-11-18
- 打赏
- 举报
SQL>SET serveroutput ON
SQL>CREATE OR REPLACE PROCEDURE test(jbh in varchar2) AS
BEGIN
DECLARE
r_count number(10);
BEGIN
select count(*) INTO r_count from jjxxx where ssjz like '%'||jbh||'%';
dbms_output.put_line(r_count);
END;
END test;
SQL>CREATE OR REPLACE PROCEDURE test(jbh in varchar2) AS
BEGIN
DECLARE
r_count number(10);
BEGIN
select count(*) INTO r_count from jjxxx where ssjz like '%'||jbh||'%';
dbms_output.put_line(r_count);
END;
END test;
