--建包
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
--建函数
create or replace function get_tablecolumn_except(e_column in varchar2) return PACKAGE as--这里写is和as有什么区别呢 ,没区别?
r_column TESTPACKAGE.Test_CURSOR;
begin
select column_name into r_column from user_tab_columns where table_name=upper('student') and column_name!=e_column;
return r_column;
exception
when no_data_found then
raise_application_error(-20001, '你输入有误');
end get_tablecolumn_except;
--执行语句
select get_tablecolumn_except('SNO') from dual
SQL> CREATE OR REPLACE PACKAGE TESTPACKAGE AS
2 TYPE Test_CURSOR IS REF CURSOR;
3 end TESTPACKAGE;
4 /
Package created
SQL>
SQL> create or replace function get_tablecolumn_except(e_column in varchar2) return TESTPACKAGE.Test_CURSOR as--这里写is和as没区别
2 r_column TESTPACKAGE.Test_CURSOR;
3 begin
4 open r_column for
5 select column_name from user_tab_columns where table_name=upper('emp') and column_name!=e_column;
6 return r_column;
7 exception
8 when no_data_found then
9 raise_application_error(-20001, '你输入有误');
10 end get_tablecolumn_except;
11 /
Function created
SQL> begin
2 select get_tablecolumn_except('EMPNO') into :c_v from dual;
3 end;
4 /PL/SQL procedure successfully completed.SQL> print c_vCOLUMN_NAME
------------------------------
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO7 rows selected.SQL>
--建存储过程
create or replace function get_tablecolumn_except(e_column in varchar2) return sys_refcursor as
r_column sys_refcursor;
begin
open r_column for
select column_name into r_column from user_tab_columns where table_name=upper('student') and column_name!=e_column;
return r_column;
exception
when no_data_found then
raise_application_error(-20001, '你输入的ID无效!');
end get_tablecolumn_except;
--执行
select get_tablecolumn_except('SNO') from dual还是不行啊,还是报无效的错误提示
r_column sys_refcursor;
begin
open r_column for
select column_name from user_tab_columns where table_name=upper('student') and column_name!=e_column;
return r_column;
exception
when no_data_found then
raise_application_error(-20001, '你输入的ID无效!');
end get_tablecolumn_except;
把 into r_column 去掉
--建函数
create or replace function get_tablecolumn_except(e_column in varchar2) return PACKAGE
as--这里写is和as有什么区别呢 ,没区别?
....
return TESTPACKAGE.Test_CURSOR as
r_column TESTPACKAGE.Test_CURSOR;
begin
open r_column for select column_name from user_tab_columns where table_name=upper('student') and column_name!=upper(e_column);
return r_column;
exception
when no_data_found then
raise_application_error(-20001, '你输入有误');
end get_tablecolumn_except;