表是scott中的emp表。。create or replace package type_pack
is
type cur_type is ref cursor;
end;
--------
create or replace procedure sel_insert_tab_emp2(p_empno number, p_ename varchar2,
p_job varchar2, p_mgr number, p_hiredate varchar2,
p_sal number, p_comm number, p_deptno number,v_page_data out type_pack.cur_type)
as
l_sql_com varchar2(100);
begin l_sql_com := 'select * from emp where';
if p_empno is not null then
l_sql_com := l_sql_com || 'empno := ' || p_empno ;
end if; if p_ename is not null then
l_sql_com := l_sql_com || 'and ename := ' || p_ename;
end if;
if p_job is not null then
l_sql_com := l_sql_com || 'and job := ' || p_job;
end if;
if p_mgr is not null then
l_sql_com := l_sql_com + 'and mgr := ' || p_mgr ;
end if;
if p_hiredate is not null then
l_sql_com := l_sql_com || 'and hiredate := ' || to_date(p_hiredate,'yyyy-mm-dd');
end if;
if p_sal is not null then
l_sql_com := l_sql_com || 'and sal := ' || p_sal;
end if;
if p_comm is not null then
l_sql_com := l_sql_com || 'and comm := ' || p_comm;
end if;
if p_deptno is not null then
l_sql_com := l_sql_com || 'and deptno := ' || p_deptno;
end if; open v_page_data for l_sql_com; --! 这报错。。end;
----------------
--测试调用(注意,抓取游标中数据,类型的问题)
declare
v_page_data type_pack.cur_type;
v_data_row emp%rowtype;
begin
dbms_output.put_line('diaoyong');
pro_sel_tab_emp2(7788, null, null, null, null, null, null,null, v_page_data);
fetch v_page_data into v_data_row;
dbms_output.put_line('员工编号' || ' 员工姓名' ||' 部门名称');
while v_page_data%found loop
dbms_output.put_line(v_data_row.empno || ' ' || v_data_row.ename || ' ' ||v_data_row.deptno);
fetch v_page_data into v_data_row;
end loop;
close v_page_data;
end; 存储过程编译不出错。。
测试程序报错如下。
ORA-00933: SQL 命令未正确结束
ORA-06512: 在 "SDD.PRO_INSERT_TAB_EMP2", line 42
ORA-06512: 在 line 6
我这个存储过程逻辑还是有点问题的。不过我想先测试通过后再改的。。
求报错原因。
DBMS_OUTPUT.PUT_LINE(l_sql_com);当断点停在此处时,l_sql_com的值为:select * from emp whereempno := 7788可以看到whereempno连在一起了,缺少空格。
拼接SQL的时候注意空格。一般使用chr(32)比较直观明了
*2). 在拼接SQL的时候,建议涉及到数值类型的参数,加个to_char()函数加以转换一下!
*3). 建议在where 条件中传入的参数用“绑定变量”,这样会避免你的SQL语句执行前的一些软解析,以提高执行速度!
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
sqlstr VARCHAR2(200); -- 定义变量,用以存放SQL语句
BEGIN
sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值,其中 :i_address 是绑定变量,以提高执行效率!
OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值
END;
/set serveroutput on;
var c_cur refcursor;
exec userinfo_proc('北京',:c_cur);
print c_cur;
谢谢楼上各位。。存储过程可以实现了。。如下。create or replace procedure pro_insert_tab_emp2(p_empno number, p_ename varchar2,
p_job varchar2, p_mgr number, p_hiredate varchar2,
p_sal number, p_comm number, p_deptno number,v_page_data out type_pack.cur_type)
as
l_sql_com varchar2(100);
l_num number;
--l_count number;
begin l_sql_com := 'select * from emp';
l_num := 0;
if p_empno is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where empno = ' || p_empno ;
l_num := l_num + 1;
end if; end if; if p_ename is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where ename = ' || p_ename;
l_num := l_num + 1;
else
l_sql_com := l_sql_com || ' and ename = ' || p_ename;
end if;
end if;
if p_job is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where job = ' || p_job;
l_num := l_num + 1;
else
l_sql_com := l_sql_com || ' and job = ' || p_job;
end if;
end if;
if p_mgr is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where mgr = ' || p_mgr;
l_num := l_num + 1;
else
l_sql_com := l_sql_com + ' and mgr = ' || p_mgr ;
end if;
end if;
if p_hiredate is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where hiredate = ' || to_date(p_hiredate,'yyyy-mm-dd');
l_num := l_num + 1;
else
l_sql_com := l_sql_com || ' and hiredate = ' || to_date(p_hiredate,'yyyy-mm-dd');
end if;
end if;
if p_sal is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where sal = ' || p_sal;
l_num := l_num + 1;
else
l_sql_com := l_sql_com || ' and sal = ' || p_sal;
end if;
end if;
if p_comm is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where comm = ' || p_comm;
l_num := l_num + 1;
else
l_sql_com := l_sql_com || ' and comm = ' || p_comm;
end if;
end if;
if p_deptno is not null then
if l_num = 0 then
l_sql_com := l_sql_com || ' where deptno = ' || p_deptno;
l_num := l_num + 1;
else
l_sql_com := l_sql_com || ' and deptno = ' || p_deptno;
end if;
end if; open v_page_data for l_sql_com;
--execute immediate 'select count(*) from emp' into l_count;
--dbms_output.put_line('l_count = ' || l_count);
end;
declare
v_page_data type_pack.cur_type;
v_data_row emp%rowtype;
begin
dbms_output.put_line('diaoyong');
pro_insert_tab_emp2(null, null, null, null, null, null, null,null, v_page_data);
fetch v_page_data into v_data_row;
dbms_output.put_line('empno' || ' ename' ||' job' || ' mgr' || ' hiredate' || ' sal'
|| ' comm'
|| ' deptno');
while v_page_data%found loop
dbms_output.put_line(v_data_row.empno || ' ' || v_data_row.ename || ' ' ||v_data_row.job
|| ' ' ||v_data_row.mgr || ' ' ||v_data_row.hiredate || ' ' ||v_data_row.sal
|| ' ' ||v_data_row.comm || ' ' ||v_data_row.deptno);
fetch v_page_data into v_data_row;
end loop;
close v_page_data;
end;