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; <<A>> dbms_output.put_line('A'); GOTO D; <<B>> dbms_output.put_line('B'); GOTO D; <<C>> dbms_output.put_line('C'); GOTO D; <<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 <procedure name> [(<parameter list>)] IS|AS <local variable declaration> BEGIN <executable statements> [EXCEPTION <exception handlers>] 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 <function name> [(param1,param2)] RETURN <datatype> 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;