-
C#实现访问Oracle的完整示例，包含PL/SQL、存储过程和触发器，已测试确保可用且易于理解
资源介绍
C# 访问Oracle示例+PL/SQL+存储过程+触发器 完整示例 测试可用
--PL/SQL基础1
declare
begin
dbms_output.('不输出不换行');
dbms_output.put_line('输出并换行');
end;
--PL/SQL基础2
declare
dig number(20,2);
begin
select avg(price) into dig from products;
dbms_output.put_line('电子产品的平均价格是'||dig);
end;
--PL/SQL基础3 根据产品编号获得产品对象
--pname products.name%type; pname变量的类型与products.name列的类型一样
declare
pid constant products.id%type:=1; --定义常量，初值1
pname products.name%type;
pdate products.adddate%type;
begin
--pid:=1;
select name,adddate into pname,pdate from products where id=pid;
dbms_output.put_line('产品名称是：'||pname||'，日期'||pdate);
end;
--PL/SQL基础4 根据产品编号获得产品对象
--obj products%rowtype; obj与products表的单行类型一样，可以通过点运算取值obj.price
declare
obj products%rowtype;
begin
select * into obj from products where id=&编号;
dbms_output.put_line('产品名称是：'||obj.name||'，价格：'||obj.price);
end;
--PL/SQL基础5 条件if
declare
vid products.id%type;
vprice products.price%type;
begin
vid:=&编号;
select price into vprice from products where id=vid;
if vprice<=100 then
dbms_output.put_line('价格在100以下');
elsif vprice>100 and vprice<=1000 then
dbms_output.put_line('价格在100—1000之间');
else
dbms_output.put_line('价格在1000以上');
end if;
end;
--PL/SQL基础5 多条件case
begin
case '&等级'
when 'A' then dbms_output.put_line('优秀');
when 'B' then dbms_output.put_line('合格');
when 'C' then dbms_output.put_line('不合格');
end case;
end;
select id, name, typeid, price, adddate from products
create table students(
Id int primary key,
sex int
)
insert into students
select 1,1 from dual union
select 2,0 from dual union
select 3,1 from dual union
select 4,0 from dual union
select 5,1 from dual
insert into students(Id) values(6)
select * from students;
select translate(translate('1心1意 3心2意','1','一'),'3','三') from dual;
select id,nvl(translate(translate(sex,1,'女'),0,'男'),'未知') from students;
select id,case as 性别 from(
select id,case sex when 1 then '女' when 0 then '男' else '未填' end case from students) temp
--PL/SQL基础6 循环Loop
declare
n number(10):=1;
cnt number(15):=0;
begin
loop
exit when n>100; --当n的值大于100时将退出，可以使用if exit退出
cnt:=cnt+n;
dbms_output.put_line(n);
n:=n+1;
end loop;
dbms_output.put_line('loop-和是：'||cnt);
end;
--PL/SQL基础7 循环While
declare
n number(10):=1;
cnt number(15):=0;
begin
while n<=100 loop --当n的值小于100时循环
cnt:=cnt+n;
dbms_output.put_line(n);
n:=n+1;
end loop;
dbms_output.put_line('while-和是：'||cnt);
end;
--PL/SQL基础8 循环For
declare
cnt number(10):=0;
begin
for i in 1..100
loop
cnt:=cnt+i;
dbms_output.put_line(i);
end loop;
dbms_output.put_line('1加到100的和是：'||cnt);
end;
--PL/SQL基础8 循环Goto 与 null
declare
begin
GOTO A;
<>
dbms_output.put_line('A');
GOTO D;
<>
dbms_output.put_line('B');
GOTO D;
<>
dbms_output.put_line('C');
GOTO D;
<>
NULL;
end;
--PL/SQL基础9 动态SQL
declare
sql_str varchar2(1000);
tid integer:=1;
trow producttype%rowtype;
begin
execute immediate 'create table newTypes(id integer primary key,name nvarchar2(200))';
--create table ... PL/SQL中不能直接使用数据定义语言,而动态sql中允许
sql_str:='select id, name from producttype where id=:type_id';
execute immediate sql_str into trow using tid; --将查询的结果给trow,查询语句中用到的参数使用tid替换
dbms_output.put_line('编号：'||trow.id||'，名称：'||trow.name);
end;
drop table newTypes
--PL/SQL基础10 处理预定义异常
declare
pname products.name%type;
begin
--select * from abc;
RAISE_APPLICATION_ERROR(-20001,'人为引发的异常');
select name into pname from products;
exception
when too_many_rows then
dbms_output.put_line('错误'||SQLERRM);
when others then
dbms_output.put_line('错误'||SQLERRM);
end;
create table newTypes(nid integer primary key,nname nvarchar2(200))
select id, name from producttype
--PL/SQL基础 1
declare
productName nvarchar2(100);
price number(10,2);
begin
productName:='ZTE-U880';
select products.price into price from products where products.name=productName;
dbms_output.put_line('价格是：'||price);
exception
when others then
dbms_output.put_line('发生了错误：'||SQLERRM);
end;
--PL/SQL基础 2
declare
productName products.name%type; --列类型
entity products%rowtype; --行类型
begin
productName:='&商品名称';
select * into entity from products where products.name=productName;
dbms_output.put_line('价格是：'||entity.price);
dbms_output.put_line('上货时间：'||to_char(entity.addDate,'yyyy-MM-dd'));
exception
when others then
dbms_output.put_line('发生了错误：'||SQLERRM);
end;
select products.name,products.price,
(
case
when products.price<20 then '便宜'
when products.price>600 then '贵'
else '适中'
end) 价位
from products
--100内被3和5整除的数
declare
i number(10,0);
begin
i:=1;
loop
if mod(i,3)=0 and mod(i,5)=0 then
dbms_output.put_line('*'||i);
else
dbms_output.put_line('#'||i);
end if;
i:=i+1;
if i>100 then
exit; --退出循环
end if;
end loop;
end;
declare
i number(10,0):=1;
summary number(10,0):=0;
begin
while i<=100 loop
summary:=summary+i;
i:=i+1;
end loop;
dbms_output.put_line('和：'||summary);
end;
declare
summary number(10,0):=0;
begin
for i in 1..100 loop
summary:=summary+i;
dbms_output.put_line('i='||i||',sum='||summary);
end loop;
dbms_output.put_line('和：'||summary);
end;
1122=0
3344=0
6789=4
1238=2
6898=? 6
1元=100分=10分*10分=1角*1角=1角
--在过程中不能DLL
-- 如果需要使用则可以 execute immediate
declare
begin
execute immediate 'create table xyz(id number(10,0))';
end;
/*创建过程的语法：
CREATE [OR REPLACE] PROCEDURE
[()]
IS|AS
BEGIN
[EXCEPTION
]
END;
*/
create or replace procedure
proc_getproductNambyId(productId products.id%type)
as
productName products.name%type;
begin
select products.name into productName where
products.id=productId
dbms_output.put_line('商品名称：'||productName);
exception
when others then
dbms_output.put_line('错误：'||sqlerrm);
end;
--执行存储过程，测试
begin
proc_getproductnambyid(productid =>'1');
proc_getproductnambyid(1);
end;
create or replace procedure
proc_getproductNambyId
(
productId products.id%type,
productName out products.name%type;
)
as
begin
select products.name into productName where
products.id=productId
exception
when others then
dbms_output.put_line('错误：'||sqlerrm);
end;
--执行存储过程，测试
declare
productname nvarchar2(100);
begin
proc_getproductnambyid(productid =>'1',productName=>productname);
dbms_output.put_line(productname);
end;
declare
productname nvarchar2(100);
begin
proc_getproductnambyid(20,productname);
dbms_output.put_line(productname);
end;
/*函数是可以返回值的命名的 PL/SQL 子程序。
创建函数的语法：
CREATE [OR REPLACE] FUNCTION
[(param1,param2)]
RETURN IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;
*/
create or replace function fun_tc(dateinfo date)
return varchar2 as
begin
return to_char(dateinfo,'yyyy-MM-dd');
end;
select fun_tc(sysdate) from dual;
select products.id,products.name,fun_tc(adddate) from products;
1 a
1 b
1 c
2 a
2 f
2 x
3 e
3 m
1 a,b,c
2 a,f,x
3 e,m
create or replace procedure proc_alltypes is
begin
for c in (select id, name, parentid from producttype)
loop
dbms_output.put_line(c.id);
end loop;
end proc_alltypes;
begin
proc_alltypes;
end;