oracle 过程，函数 语法等

 

/*select * from temp_tab;*/

--创建过程
/*create or replace procedure ShowInfo
       (Major in number) as
       cursor s_info is
       select * from temp_tab where id = Major;
begin
   for s_id in s_info loop
     dbms_output.put_line(s_id.id);
   end loop;
end;*/

--调用过程
/*begin
  ShowInfo(2);
 end;*/
 
 
 --创建无参过程
/* create or replace procedure ShowInfo2
   as
   cursor S_info2 is select * from temp_tab;
begin
   for S in S_info2 loop
     dbms_output.put_line(s.id);
     end loop;
end;*/
--调用无参过程
/*begin
  ShowInfo2();
  end;*/
 
 
--删除过程 ddl 数据定义语言
/*drop procedure ShowInfo2;*/
/*
select count(1)  from temp_tab*/

--9.2.1函数定义------------------------------------------------------
/*create or replace function GETCOUNT(temp_id in number)
       return number
       as
       f_count number;
begin
      select count(1) into f_count from temp_tab where id>temp_id;
      return f_count;
end;   */

--调用函数
/*declare
  v_count number;
begin
  v_count :=GETCOUNT(1);
  dbms_output.put_line('总条数:'||v_count);
end;   */
/*declare
  v_count number:=GETCOUNT(1);
begin
  --v_count :=GETCOUNT(1);
  dbms_output.put_line('总条数:'||v_count);
end;  */

--删除函数
/*drop function 函数名*/

--创建包-------------------------------------------------------------
--1、创建包头
/*create or replace package temp_tab_pac
as
       --这里定义  变量 函数  过程等
       procedure  my_proc(
                  id number
         ); 
end temp_tab_pac;  */  

--2 创建包体   如果包头中没有说明任何过程或者函数（只有变量，游标，类型等） 那么包体就不必存在

--body
/*create or replace package body temp_tab_pac
as
       --
       procedure  my_proc(
         --多个用逗号分隔
                 id number
         ) is
         begin
           insert into temp_tab values(id);
      end my_proc;
end temp_tab_pac;*/

--包的调用
/*begin
  temp_tab_pac.my_proc(12);
end;*/

/*select * from temp_tab where id = 12;*/

 

--上机练习
--动态执行创建表
/*begin
  execute immediate 'create table car (
          carname varchar2(64),
          carout number(11),
          carprice number(11),
          carnum number(11)
  )';
end;

select * from car;*/

--1.  编写过实现数据表中插入数据

/*create or replace procedure addcar
       (
         carname varchar2,
          carout number,
          carprice number,
          carnum number         
       )
       as
begin
   insert into car values(carname,carout,carprice,carnum);
end;*/ 

--调用存储过程
/*declare
   v_count number;
begin
   addcar('宝马x7',123.11,11,12);
   v_count:=SQL%ROWCOUNT;--行数
   dbms_output.put_line(v_count);
end;    */

--2. 编写函数实现向数据表中插入数据
/*create or replace function GETCOUNTCAR
       return number
       as
       f_count number;
begin
      select count(1) into f_count from car;
      return f_count;
end;*/ 

/*declare
      f_count number:= GETCOUNTCAR();
begin
      dbms_output.put_line(f_count);
  end;*/

--版本1
/*create or replace function addcarbyfunction
          (
           carname in varchar2,
           carout in number,
           carprice in number,
           carnum in number
          )
       return number
       as
       f_count number;
       f_count1 number:= GETCOUNTCAR();
       f_count2 number;
begin
       insert into car values(carname,carout,carprice,carnum);
       f_count2:=GETCOUNTCAR();
       f_count := f_count2-f_count1;
       return f_count;
      
      -- SQL%ROWCOUNT
end;*/

/*declare
      f_count number;
begin
      f_count := addcarbyfunction(carname => '宝马x6',carout => 12,carprice => 12,carnum => 1 );
                        
      dbms_output.put_line(f_count);
end;*/

--版本2
/*create or replace function addcarbyfunctionbysqlrowcount
          (
           carname in varchar2,
           carout in number,
           carprice in number,
           carnum in number
          )
       return number
       as
       f_count number;
begin
       insert into car values(carname,carout,carprice,carnum);
       f_count := SQL%ROWCOUNT;
       return f_count;
      
      -- SQL%ROWCOUNT
end;*/

/*declare
      f_count number;
begin
      f_count := addcarbyfunctionbysqlrowcount(carname => '宝马x6',carout => 12,carprice => 12,carnum => 1 );
                        
      dbms_output.put_line(f_count);
end;*/
-- 扩展实例
/* declare
 v_name varchar2(24);
 begin
 insert into test_return(name) values('333') returning name into v_name;
 dbms_output.put_line('已经更新的name为：'||v_name);
 end;*/

/*declare
 v_name varchar2(24);
 begin
   update car set carname='宝马x61'  where carname = '宝马x6-01' returning carout into v_name;

 dbms_output.put_line('已经更新的name为：'||v_name);
 end;*/
/*select * from car;*/

---上机三

--创建包  声明过程和函数
/*
create or replace package pack_name
       as
       procedure procedure_addcar(
          carname varchar2,
          carout number,
          carprice number,
          carnum number
       );
       function function_addcar(
              carname varchar2,
              carout number,
              carprice number,
              carnum number   
       )
       return number;
end pack_name;

--创建包体
create or replace package body pack_name as
       --过程的实现
        procedure procedure_addcar(
          carname varchar2,
          carout number,
          carprice number,
          carnum number
       ) is
       begin
            insert into car values(carname,carout,carprice,carnum);
       end procedure_addcar;      
       --函数的实现
       function function_addcar(
              carname varchar2,
              carout number,
              carprice number,
              carnum number   
       )
       return number
       as
              f_count number;
       begin
              insert into car values(carname,carout,carprice,carnum);
               f_count := SQL%ROWCOUNT;
               return f_count;
       end function_addcar;
end pack_name;*/

--调用包体内的属性 
/*declare
      f_count number;
begin
    --pack_name.procedure_addcar('奥迪a8',300,20,111223); 
    f_count:= pack_name.function_addcar('奔驰1',122,23,12313);
    dbms_output.put_line(f_count);
end;
select * from car;*/

---视图------------------------------------------------------------------------------------------
/*select * from filmtype t,filminfo f where t.typeid=f.typeid;--

create or replace view filmtype_film as
       select f.*,t.typename from filminfo f inner join filmtype t on f.typeid=t.typeid;

select * from filmtype_film */

-- 序列
/*create sequence car_name
       increment by 1
       start with 1      
;
--序列修改 [start with 不能更改]
alter sequence car_name
      increment by 2
      cache 20;
--序列删除
drop sequence car_name;*/

--创建数据链
/*create database link link_goods
       connect to film identified by film
       using '(description =
                 (address_list =
                   ( address = (protocol = tcp)(host = 10.0.0.11)(port = 1521) )
                 )
                 (connect_data =
                   (service_name=orcl)
                 )
              )';
             
--使用
select * from goods@link_goods;*/

 

--上机4
/*create table supplier(
       supplierno number(11) primary key,
       supplierinfo varchar2(128)
);

create table goods(
       id number(11) primary key,
       goodsname varchar2(32),
       goodaddress varchar2(64),
       goodsprice number(8,2),
       supplierno number(11) references supplier(supplierno)     
);
*/

/*create or replace view goods_info as select g.*,s.supplierinfo from goods g inner join supplier s on g.supplierno=s.supplierno;

select * from goods_info;*/

--上机5
/*create sequence sql_supplier_id;
create sequence sql_goods_id;
insert into supplier values(sql_supplier_id.nextval,'上海第一家');
select * from supplier;
insert into goods values(sql_goods_id.nextval,'电冰箱','望江',11.11,1);
select * from goods;*/

 

--求一百内的所有能被三整除的整数之和
/*create or replace function count_sum
       return number
       as
       s_count number:=0;
       s_result number:=0;
       begin
          loop                    
               s_count := s_count + 1;
               if s_count>100 then
                 exit;
               end if;   
               if s_count mod 3 = 0 then
                  s_result := s_result+s_count;
               end if;                              
          end loop;
       return s_result; 
end;

declare
      f_count number:= count_sum();
begin
      dbms_output.put_line(f_count);
end;*/

--2求1-100中能被三整除的整数之和
/*create or replace function count_sum2
       return number
       as
       s_count number:=0;
       begin
         for s in 1..100 loop
               if s mod 3 = 0 then
                  s_count := s_count+s;
               end if;                              
         end loop;
       return s_count; 
end;

declare
      f_count number:= count_sum2();
begin
      dbms_output.put_line(f_count);
end;*/

--求一个正整数的阶乘
-- 6！=6 *（6-1）*（6-2）。。。。1 n*  n! = n*(n-1)!  n*(n-1)(n-2)..(1)
-- 0!=1
-- 1!=1
-- 2!=2*(2-1)
-- 3! = 2!*3
-- 4！ = 3！*4
/*create or replace function count_sum2(n in number)
       return number
       as
       s_count number:=1;--不能赋值为0
       begin
         if n < 0 then
            dbms_output.put_line('负数不能求阶乘！');
         elsif n=0 or n=1 then
            return 1;
         else
           for i in 2..n loop              
              --开始计算
               s_count:=i*s_count;                              
           end loop;
         end if;
       return s_count; 
end;

declare
      f_count number:= count_sum2(-1);
begin
      dbms_output.put_line(f_count);
end;*/