-- 启用服务器输出 --------------------- set serveroutput on -- 创建测试表 --------------------- create table test_pkg_test ( id number(10) constraint pk_test_pkg_test primary key, name varchar2(30) ); -- 写入测试数据 --------------------- begin insert into test_pkg_test(id) values(1); insert into test_pkg_test(id) values(2); insert into test_pkg_test(id) values(3); insert into test_pkg_test(id) values(4); insert into test_pkg_test(id) values(5); insert into test_pkg_test(id) values(6); insert into test_pkg_test(id) values(7); insert into test_pkg_test(id) values(8); insert into test_pkg_test(id) values(9); insert into test_pkg_test(id) values(10); insert into test_pkg_test(id) values(11); insert into test_pkg_test(id) values(12); insert into test_pkg_test(id) values(13); insert into test_pkg_test(id) values(14); insert into test_pkg_test(id) values(15); insert into test_pkg_test(id) values(16); insert into test_pkg_test(id) values(17); insert into test_pkg_test(id) values(18); end; / update test_pkg_test set name='name of ' || to_char(id); commit; -- 声明程序包 --------------------- create or replace package pkg_test as type type_cursor is ref cursor; procedure read_rows (header varchar2, result out type_cursor); end pkg_test; / -- 实现程序包 --------------------- create or replace package body pkg_test as procedure read_rows (header varchar2, result out type_cursor) is sqlText varchar2(500); begin if header is null or length(header)=0 then sqlText := 'select * from test_pkg_test'; else sqlText := 'select * from test_pkg_test where substr(name,1,' || to_char(length(header)) || ')=''' || header || ''''; end if; --dbms_output.put_line(sqlText); open result for sqlText; end read_rows; end pkg_test; / -- 在 sqlplus 中测试 --------------------- var result refcursor exec pkg_test.read_rows(null,:result); print result exec pkg_test.read_rows('name of 1', :result); print result; -- 在程序中测试(c#.Net) -- *************************************** static class pkg_test { public static void Test() { using (OracleConnection conn = new OracleConnection()) { conn.ConnectionString = "Data Source=mydb;User Id=myuser;Password=mypassword"; conn.Open(); using (OracleCommand cmd = new OracleCommand("pkg_test.read_rows", conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; OracleParameter p = new OracleParameter("header", OracleType.VarChar); p.Value = "name of 1"; //p.Value = DBNull.Value; cmd.Parameters.Add(p); p = new OracleParameter("result", OracleType.Cursor); p.Direction = System.Data.ParameterDirection.Output; cmd.Parameters.Add(p); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine("{0}\t{1}", reader.GetValue(0), reader.GetValue(1)); } } } } -- *************************************** -- 删除程序包和测试表 --------------------- drop package pkg_test; drop table test_pkg_test;
一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!
二、互相尊重,对自己的言论和行为负责。