oracle常见sql语句

存储过程
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;
/