经常在论坛上看到有人问怎么通过一个存储过程来查询某个表中的某列 想写个通用些的过程 可是没写出来 呵呵 小尴尬一下…… 所以来求助一下 希望能完成它--这是个匿名块 功能倒是可以实现 不过不太好
SQL> set serveroutput on
SQL> declare
2 cursor v_cur is select &&v_colname from &&v_tablename;
3 begin
4 for v_rec in v_cur loop
5 dbms_output.put_line(v_rec.&&v_colname);
6 end loop;
7 end;
8 /
Enter value for v_colname: dname
Enter value for v_tablename: dept
old 2: cursor v_cur is select &&v_colname from &&v_tablename;
new 2: cursor v_cur is select dname from dept;
old 5: dbms_output.put_line(v_rec.&&v_colname);
new 5: dbms_output.put_line(v_rec.dname);
ACCOUNTING
RESEARCH
SALES
OPERATIONSPL/SQL procedure successfully completed.--下面是存储过程 我想在里面通过查询两个数据字典 达到判断用户输入的表名是否存在 以及该表中的列是否存在
SQL> create or replace procedure tab_col(p_tablename in varchar2,p_colname in va
rchar2)
2 is
3 tab_flag number default 0;
4 col_flag number default 0;
5 no_table exception;
6 no_col exception;
7 type t_curtype is ref cursor;
8 v_cur t_curtype;
9 v_tablename varchar2(20);
10 v_colname varchar2(20);
11 begin
12 v_tablename:=p_tablename;
13 v_colname:=p_colname;
14 select count(*) into tab_flag from user_tables where table_name=v_tablename
;
15 if tab_flag=0 then
16 raise no_table;
17 end if;
18 select count(*) into col_flag from user_tab_cols where table_name=v_tablena
me and column_name=v_colname;
19 if col_flag=0 then
20 raise no_col;
21 end if;
22 open v_cur for select v_colname from v_tablename;
23 for v_rec in v_cur loop
24 dbms_output.put_line(v_rec.v_colname);
25 end loop;
26 exception
27 when no_table then
28 dbms_output.put_line('the table is not exist');
29 when no_col then
30 dbms_output.put_line('the col is not exist');
31 when others then
32 dbms_output.put_line('some other errors occur');
33 end;
34 /Warning: Procedure created with compilation errors.SQL> show errors
Errors for PROCEDURE TAB_COL:LINE/COL ERROR
-------- -----------------------------------------------------------------
22/16 PL/SQL: SQL Statement ignored
22/38 PL/SQL: ORA-00942: table or view does not exist
23/1 PL/SQL: Statement ignored
23/14 PLS-00221: 'V_CUR' is not a procedure or is undefined
SQL>亲们不要骂我懒 虽然错误提示我看得懂 可是我已经改过很多次了 就是改不对 才来求助的 呵呵 PL/SQL不太好 见谅哈~
SQL> set serveroutput on
SQL> declare
2 cursor v_cur is select &&v_colname from &&v_tablename;
3 begin
4 for v_rec in v_cur loop
5 dbms_output.put_line(v_rec.&&v_colname);
6 end loop;
7 end;
8 /
Enter value for v_colname: dname
Enter value for v_tablename: dept
old 2: cursor v_cur is select &&v_colname from &&v_tablename;
new 2: cursor v_cur is select dname from dept;
old 5: dbms_output.put_line(v_rec.&&v_colname);
new 5: dbms_output.put_line(v_rec.dname);
ACCOUNTING
RESEARCH
SALES
OPERATIONSPL/SQL procedure successfully completed.--下面是存储过程 我想在里面通过查询两个数据字典 达到判断用户输入的表名是否存在 以及该表中的列是否存在
SQL> create or replace procedure tab_col(p_tablename in varchar2,p_colname in va
rchar2)
2 is
3 tab_flag number default 0;
4 col_flag number default 0;
5 no_table exception;
6 no_col exception;
7 type t_curtype is ref cursor;
8 v_cur t_curtype;
9 v_tablename varchar2(20);
10 v_colname varchar2(20);
11 begin
12 v_tablename:=p_tablename;
13 v_colname:=p_colname;
14 select count(*) into tab_flag from user_tables where table_name=v_tablename
;
15 if tab_flag=0 then
16 raise no_table;
17 end if;
18 select count(*) into col_flag from user_tab_cols where table_name=v_tablena
me and column_name=v_colname;
19 if col_flag=0 then
20 raise no_col;
21 end if;
22 open v_cur for select v_colname from v_tablename;
23 for v_rec in v_cur loop
24 dbms_output.put_line(v_rec.v_colname);
25 end loop;
26 exception
27 when no_table then
28 dbms_output.put_line('the table is not exist');
29 when no_col then
30 dbms_output.put_line('the col is not exist');
31 when others then
32 dbms_output.put_line('some other errors occur');
33 end;
34 /Warning: Procedure created with compilation errors.SQL> show errors
Errors for PROCEDURE TAB_COL:LINE/COL ERROR
-------- -----------------------------------------------------------------
22/16 PL/SQL: SQL Statement ignored
22/38 PL/SQL: ORA-00942: table or view does not exist
23/1 PL/SQL: Statement ignored
23/14 PLS-00221: 'V_CUR' is not a procedure or is undefined
SQL>亲们不要骂我懒 虽然错误提示我看得懂 可是我已经改过很多次了 就是改不对 才来求助的 呵呵 PL/SQL不太好 见谅哈~
解决方案 »
- 如何将数据库表中记录放入内存中?在线等,急啊!
- 用JSP怎样对ORACLE存取图片.
- 触发器问题 insert的when语句单独可以执行 但是放入触发器中就报错 高手指点一下
- 性能问题:ORACLE 9i2,我们的数据库数据和客户的是相同的(IMPORT过来的),但是相同的PROCEDURE用T_CURSOR返回记录做查询,我们这边只需要1,2
- 不同数据库不同表结构的表之间数据转移
- [求教]一个比较麻烦的sql语句
- java的sql.ResultSet无法转换成Oracle的.OracleResultSet,急救!!!
- 一个怪问题,请大侠们帮忙
- 新手教程Oracle
- 求救:oracle10g删除表时出现错误
- 结果集内再有条件求和
- 没有分,可怜一下吧
这里可能要用动态sql来fetch到游标。
var_sql varchar2(200);
var_sql := 'select '||v_colname||' from '|| v_tablename;
open v_cur for var_sql;想不到兔子也会犯错误噢,呵呵
前一个直接select COLNAME from TABNAME;
后一个select * from user_tab_cols where ..查询。都要比写过程方便得多啊,不知道写过程的原因是什么...
8 v_cur t_curtype;游标定义貌似也不用这么复杂啊
v_cur SYS_REFCURSOR;
像上面crazy说的那样使用动态SQL?
如果在客户端中或其他第三方工具中,过程还是没有必要,表不存在或列不存在也会有提示,而且用dbms_output输出也不太方便
如果在前台程序端调用的话,可以将错误提示和查询结果分别替换为传出字符串和传出游标
因为是在SQL*PLUS里执行么 所以想要使用dbms_output来打印出错误信息
rchar2)
2 is
3 tab_flag number default 0;
4 col_flag number default 0;
5 no_table exception;
6 no_col exception;
7 v_cur sys_refcursor;
8 v_tablename varchar2(20);
9 v_colname varchar2(20);
10 v_sql varchar2(1000);
11 begin
12 v_tablename:=p_tablename;
13 v_colname:=p_colname;
14 select count(*) into tab_flag from user_tables where table_name=v_tablename
;
15 if tab_flag=0 then
16 raise no_table;
17 end if;
18 select count(*) into col_flag from user_tab_cols where table_name=v_tablena
me and column_name=v_colname;
19 if col_flag=0 then
20 raise no_col;
21 end if;
22 v_sql:='select '||v_colname||' from '||v_tablename;
23 open v_cur for v_sql;
24 for v_rec in v_cur loop
25 dbms_output.put_line(v_rec.v_colname);
26 end loop;
27 exception
28 when no_table then
29 dbms_output.put_line('the table is not exist');
30 when no_col then
31 dbms_output.put_line('the col is not exist');
32 when others then
33 dbms_output.put_line('some other errors occur');
34 end;
35 /Warning: Procedure created with compilation errors.SQL> show errors
Errors for PROCEDURE TAB_COL:LINE/COL ERROR
-------- -----------------------------------------------------------------
24/1 PL/SQL: Statement ignored
24/14 PLS-00221: 'V_CUR' is not a procedure or is undefined这个错误是什么原因
游标使用方法错了吗
refcursor不能用for .. in形式
改成
loop
fetch .. into ...;
exit when ...;
end loop;
procedure tab_col(p_tablename in varchar2,p_colname in varchar2)
is
tab_flag number default 0;
col_flag number default 0;
no_table exception;
no_col exception;
--type t_curtype is sys_refcursor;
v_cur sys_refcursor;
v_tablename varchar2(20);
v_colname varchar2(20);
v_sql varchar2(200);
begin
v_tablename:=p_tablename;
v_colname:=p_colname;
v_sql := 'select'|| v_colname|| ' from'|| v_tablename;
select count(*) into tab_flag from user_tables where table_name=v_tablename;
if tab_flag=0 then
raise no_table;
end if;
select count(*) into col_flag from user_tab_cols where table_name=v_tablename and column_name=v_colname;
if col_flag=0 then
raise no_col;
end if;
open v_cur for v_sql;
loop
fetch v_cur into v_colname;--用fetch into
exit when v_cur%notfound;
dbms_output.put_line(v_colname);
end loop;
exception
when no_table then
dbms_output.put_line('the table is not exist');
when no_col then
dbms_output.put_line('the col is not exist');
when others then
dbms_output.put_line('some other errors occur');
end;
其实我当时写的时候就想用fetch的
可是我怎么fetch?不知道该列的类型 没办法定义该列类型的变量啊
汗,兔子你搞出个问题来了。。
列名当然是字符类型(varchar2)的了呀!
反正你fetch后要打印,用to_char转了吧,fetch到varchar型变量中
to_char的format部分,可以根据user_tab_cols里查到的字段类型来写
不管什么类型 都fetch到一个字符型变量中好像也可以
SQL> create or replace procedure tab_col(p_tablename in varchar2,p_colname in va
rchar2)
2 is
3 tab_flag number default 0;
4 col_flag number default 0;
5 no_table exception;
6 no_col exception;
7 v_cur sys_refcursor;
8 v_tablename varchar2(20);
9 v_colname varchar2(20);
10 v_sql varchar2(1000);
11 v_value varchar2(200);
12 begin
13 v_tablename:=p_tablename;
14 v_colname:=p_colname;
15 select count(*) into tab_flag from user_tables where table_name=v_tablename
;
16 if tab_flag=0 then
17 raise no_table;
18 end if;
19 select count(*) into col_flag from user_tab_cols where table_name=v_tablena
me and column_name=v_colname;
20 if col_flag=0 then
21 raise no_col;
22 end if;
23 v_sql:='select '||v_colname||' from '||v_tablename;
24 open v_cur for v_sql;
25 loop
26 fetch v_cur into v_value;
27 exit when v_cur%notfound;
28 dbms_output.put_line(v_value);
29 end loop;
30 exception
31 when no_table then
32 dbms_output.put_line('the table is not exist');
33 when no_col then
34 dbms_output.put_line('the col is not exist');
35 when others then
36 dbms_output.put_line('some other errors occur');
37 end;
38 /Procedure created.
SQL> exec tab_col('DEPT','DNAME'); --DNAME是varchar2的
ACCOUNTING
RESEARCH
SALES
OPERATIONSPL/SQL procedure successfully completed.SQL> exec tab_col('DEPTNO','DNAME');
the table is not existPL/SQL procedure successfully completed.SQL> exec tab_col('DEPT','DEPTNO'); --DEPTNO是NUMBER的
10
20
30
40PL/SQL procedure successfully completed.SQL> desc dept
Name Null? Type
----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
这样会不会太麻烦了
查询user_tab_cols肯能会有那么多种数据类型
我还要进行判断
根据每种不同的类型来给出相应的格式字符串进行转换呵呵 上面那样直接fetch到varchar2偷个懒好像也可以哦??我又试了下date类型
SQL> create table t1(time date);Table created.SQL> insert into t1 values(sysdate);1 row created.SQL> select * from t1;TIME
---------------
10-JAN-10SQL> exec tab_col('T1','TIME');
10-JAN-10PL/SQL procedure successfully completed.SQL> exec tab_col('T1','TIME1');
the col is not existPL/SQL procedure successfully completed.
上面的语句可以是因为进行了隐式转换。这样也可以,如果是date或timestamp型等,隐式转换的结果可能会不是你想看到的,总体差不多吧,只是为了显示的话。其他类型例如long,clob等就不用考虑了..