oracle常见sql语句

妖狐艹你老母 2023-03-13 06:39 13阅读 0赞

**存储过程**  
1.向表中添加数据的存储过程

create or replace procedure gxs_add_2(g_id in number,g_name in varchar,g_sex in varchar,g_address in varchar) as
    begin
      insert into gxs_stu_info (id,name,sex,address) values(g_id,g_name,g_sex,g_address);
    end gxs_add_2;

2.查询的存储过程

create or replace procedure gxs_select_by_name(g_name in varchar,g_sex out varchar)as
    begin
      select sex into g_sex from gxs_stu_info where name = g_name;
    end;

3.修改的存储过程

create or replace procedure gxs_update(g_id in number,g_name in varchar,g_sex in varchar,g_address in varchar) as
    begin
      update gxs_stu_info set name = g_name, sex=g_sex ,address=g_address where id = g_id;
    end;

4.删除的存储过程

create or replace procedure gxs_delete(g_id in number,g_name in varchar) as
    begin
      delete from gxs_stu_info where id = g_id or name = g_name;
    end;

**集合函数**  
遍历输出

--创建集合并遍历输出
    declare
    type list_country is table of varchar2(100) not null;
    list_all list_country := list_country('中国','美国','德国','英国','西班牙','澳大利亚');
    begin
      for x in list_all.first .. list_all.last loop
        dbms_output.put_line(list_all(x));
        end loop;
    end;

删除

declare
    type list_people is table of varchar2(100) not null;
    list_all list_people := list_people('皮皮 ','芳芳','菲菲','红红','健健','亮亮');
    begin
      --删除指定位置的数据
      list_all.delete(1);
      --删除指定范围的数据
      list_all.delete(1,4);
      for x in list_all.first .. list_all.last loop
        dbms_output.put_line(x||'---'||list_all(x));
      end loop;
    end;

根据索引判断相应数据是否存在

declare
    type list_foods is table of varchar2(100) not null;
    list_all list_foods :=list_foods('苹果','香蕉','梨子','菠萝','冬瓜','西瓜','南瓜');
    begin
      --list_all.delete(1);
      if list_all.exists(1) then
        dbms_output.put_line('索引为1的数据存在');
        end if;
        if not list_all.exists(10) then
          dbms_output.put_line('索引为10的数据不存在');
        end if;
    end;
    /