Oracle常见SQL语句

--创建表空间--
create tablespace ts_name
datafile 'd:\test\ts_name.dbf' size 100m
autoextend on

--调整表空间大小--
alter database datafile 'd:\test\ts_name.dbf' resize 1m

--改变表空间的读写状态--
alter tablespace ts_name read only
alter tablespace ts_name read write

--删除表空间--
drop tablespace ts_name

--创建用户--
create user martin identified by martinpwd
default tablespace users
temporary tablespace temp
quota unlimited on users;

--更改口令--
alter user martin identified by martinpass

--删除用户--
drop user martin cascade

--授予权限语法--
grant connect,resource to martin;

--撤销权限语法--
revoke connect,resource from martin;

--常见系统权限--
create session :连接到数据库
create table :创建表
create view :创建视图
create sequence :创建序列

--创建数据库表--
create table stuInfo/*创建学员信息表*/
(
stuName varchar2(20) not null, --学员姓名，非空（必填）
stuNo char(6) not null, --学号，非空（必填）
stuAge number(3,0) not null, --年龄，非空（必填）
stuID numeric(18,0), --身份证号，numeric（18,0）代表18位数字，小数位数为0
stuSeat numeric(2,0) --座位号
)

--清空表
truncate table login;

--修改列，添加列，删除列--
alter table stuInfo modify (stuName varchar2(25));
alter table stuInfo add (stuTel_no varchar2(12),stuAddress varchar2(20));
alter table stuInfo drop column stuTel_no; --删除stuTel_no列
alter table stuInfo drop (stuTel_no,stuSeat); --删除两列


--删除表记录不删除表结构--
SQL> truncate stuInfo;
--查看表结构--
SQL> desc stuInfo;
--删除表及其全部数据--
SQL> drop table usert;

--在sys  dba 权限下删除表空间
SQL> drop tablespace study including contents;


--使用SQL语句创建、删除、查看约束--
--创建数据库表--
create table stuInfo/*创建学员信息表*/
(
stuName varchar2(20) not null, --学员姓名，非空（必填）
stuNo char(6) not null, --学号，非空（必填）
stuAge number(3,0) not null, --年龄，非空（必填）
stuID numeric(18,0), --身份证号，numeric（18,0）代表18位数字，小数位数为0
stuSeat numeric(2,0) --座位号
);

--创建学员成绩表
create table stuMarks(
examNo char(7) not null, --考号
stuNo char(6) not null, --学号
writtenExam numeric(3,0) not null, --笔试成绩
LabExam numeric(3,0) not null --上机成绩
);

--为stuInfo添加主键约束(stuNo作为主键)
alter table stuInfo add constraint pk_stuNo
primary key (stuNo);
--为stuMarks添加主键约束(examNo作为主键)
alter table stuMarks add constraint pk_examNo
primary key (examNo);
--添加唯一约束（身份证号唯一，因为每人的身份证号全国唯一
alter table stuInfo add constraint uq_stuID
unique(stuID);
--添加检查check约束，要求年龄只能在15-40之间
alter table stuInfo add constraint ck_stuAge
check(stuAge between 15 and 40);
--添加外键约束（stuInfo和stuMarks建立关系，关联字段为stuNo）
alter table stuMarks add constraint fk_stuNo
foreign key (stuNo) references stuInfo(stuNo);
--删除ck_stuAge约束
alter table stuInfo drop constraint ck_stuAge;
--查看表stuInfo的约束
select *from user_constraints where table_name = 'stuInfo';


--授权可以查看的表
grant select on tbl_test to david
--查看其它用户的表
select 'grant select on tbl_test to david' from tab

--查询编辑数据
select t.*, t.rowid from tbl_stu t

--设置当前会话框的日期格式
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'


--创建备份
create table stu_bak as select * from tbl_stu
--插入来自备份的记录
insert into tbl_stu select stuNo,stuName,stuAge,beginDate
from tbl_stu_bak

--插入系统时间
Insert into tbl_stu values('033','Frod',22,sysdate)

--更新记录
update tbl_stu set stuage=25,begindate='2011-1-1'
where stuno='034'

insert into users values(1002,'tom');
--提交事务
commit;


select * from es_order;
select * from es_user;
select * from es_product;
select * from es_sort;
select * from users;
select * from users_bak;
--会员插入一条记录
insert into es_user(id,username,password)
values(1001,'小李','123')
--字段全部插入
insert into es_user values(1002,'123','李洪亮','123456789','南京','200100','123@qq.com',2,'小但')
insert into es_user values(1003,'124','丽华','123456789','南京','200100','123@qq.com',2,'小请')
insert into es_user values(1004,'125','桂花','123456789','南京','200100','123@qq.com',2,'小栏')
insert into es_product values(2001,1,'ipad',3000.6,'质量优良',null,TO_DATE('2013-3-7','yyyy-mm-dd hh:mi:ss'),7)
insert into es_product values(2002,3,'ipad2',3000.6,'质量优良',null,null,7)
insert into es_product values(2003,4,'ipad3',4000.6,'质量优良',null,null,7)
insert into es_sort values(1,'电脑器材')
select * from es_user;
--数据库备份
select * from users;
insert into users values('小明','110')
select * from users_bak;
insert into users_bak(select * from users);
--数据更新
select * from es_user;
update es_user set TO_DATE='小孩',tel='113456',address='徐州',zip='21100',email='356@qq.com',usertype=2
where id=1001
--查询VIP
select * from es_user where usertype=2
select * from es_user where usertype=2 and(username='小李')or(username='小鬼')
--查询空值

select t.*,rowid from es_product t--可直接在表中修改值
select * from es_product where saledate is null
--查询会员中所有姓李的会员：模糊查询"%"表示0到多个字符 "_"只能匹配单个字符
select * from es_user
where realname like'李%'
--模糊查询 between...and ..
select t.*,rowid from es_product t
where price between 3000 and 4000
--模糊查询 in
select t.*,rowid from es_product t
where id in(2001,2002)

select t.*,rowid from es_product t
where exists(price=3000.6)
--添加别名(as)可省，列名可以省略as,表的别名前不可省略as,要用双引号(可省)
select t.name,t.description from es_product t
select t.realname as 真实名称,t.username"用户名" from es_user t
--对日期进行降序（desc）排序
select t.*,rowid from es_product t
where 1=1 order by t.saledate desc;
--1=1是true,恒等式，有where语句时先执行where再执行order by

select t.*,rowid from es_product t
select t.*,rowid from es_sort t
select  distinct t.sort_id from es_product t
-- 只有当t.sort_id,t.name,t.saledate 都相同时distinct才会生效
select  distinct t.sort_id,t.name,t.saledate from es_product t



--字符函数：concat和|| 字符拼接
select t.id,t.name,t.price||'元' from es_product t
select t.id,t.name,concat(t.price,'元') from es_product t
select t.id||'-'||t.name||'-'||t.price from es_product t

--单行函数 nvl 如果不为空则返回value,否则返回设置的默认值
select t.name,nvl(t.contents,'没有详细描述') from es_product t
--对于日期可以用以下函数
select t.name,decode(t.saledate,null,'日期不详',t.saledate)
from es_product t;

--trunc截取函数第二个参数不写默认0，
--dual表是一个虚拟表
select trunc(456.785,-1) from dual
--round数字函数，和trunc函数参数差不多
select round(354.344,-2) from dual

--日期函数
select sysdate from dual
select systimestamp from dual
select extract(year from sysdate) from dual
select t.name,extract(year from sysdate) from es_product t

--转换函数to_char
select t.name,to_char(t.saledate,'yyyy-mm-hh') from es_product t
select to_char(123.456,'999D99') from dual
SELECT TO_DATE(2454336, 'J') FROM dual;
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM dual;

--聚合函数
select sum(t.stockcount),t.sort_id
from es_product t group by t.sort_id

--查询‘李青青’的订单的id、付款方式、状态
--嵌套循环 在一个SQL语句中嵌套另一个SQL语句为子查询
  select id,payment,status from es_order where user_id=(
  select id from es_user where realname='李青青'
  )

  --明确显示内容：商品id，商品名称，商品价格
  --明确数据来源：es_product表
  --查询所需条件:大于商品的平均价格
  -- 条件值的来源:select avg(price) from es_product
  select t.id,t.name,t.price from es_product t
  where t.price>(select avg(price) from es_product)

  --多列多行的查询
  --查询下单和付款都是李青青的订单信息
  select *from es_order t where
  (t.user_id,t.realname)=
  (select id,realname from es_user where realname='李青青')

  --查询最新上架的五条信息
  select * from(select t.* from es_product t order by
   t.saledate desc) where rownum<6

  --查询内容可以通过基表表名点出来
  --不过一般用别名 t  节省带宽
  --因为rownum是伪列，所以不能用基表表名点出来
  --如果有排序，应该先对结果集排序，把这个结果集当成一个虚拟表
  --（视图）来查询，然后再使用rownum取这个虚拟表（视图）的前N条
  --rownum取值永远是从1开始，它是每一次查询过程中动态生成的
  --分页显示信息
  select *from news_detail where rownum between 1 and 5;
  --分页成四条一页，查看第二页信息
  select * from (select t.*,rownum rn from es_product t) n
  where n.rn>4 and n.rn<9
  --相应参数1---4   5---8  9---12
  --pageNumber pageSize
  --beginIndex = (pageNumber-1)*pageSize+1
  --endIndex =pageNumber*pageSize
  /*
  会写显示第一页数据的SQL语句，再写第二页。。第n页
  */

    --显示信息来自两张表，内连接
  select p.id,p.name,s.sortname from es_product p,es_sort s
  where p.sort_id=s.id

  --两张表放一起查询时，用虚拟表进行二次查询时，有同名需用别名
select *from (select p.id proid,s.id sortid
from es_product p,es_sort s where p.sort_id=s.id)

--查询没有下过订单的用户信息,用外连接（+）join
select u.realname,u.tel,o.id,o.createtime,o.status
from es_order o,es_user u where o.user_id(+)=u.id

select u.realname,u.tel,o.id,o.createtime,o.status
from es_user u left outer join es_order o on o.user_id=u.id


--商品类别id、商品id、商品名称、价格、库存
--条件高于本类产品平均价格信息
select * from es_product t,
(select p.sort_id, avg(p.price) avgprice from es_product p
group by p.sort_id) s
 where t.sort_id = s.sort_id and t.price > s.avgprice


--PL\SQL变量声明和赋值
declare
  v_name       es_product.name%type;
  v_stockcount es_product.stockcount%type;
begin
  select stockcount, name
    into v_stockcount, v_name
    from es_product
   where id = &id;
  dbms_output.put_line('库存：' || v_stockcount || ' ' || '名称:' || v_name);
end;

--if elsif then  else 结构语法
declare
  v_es_pro es_product%rowtype;
begin
  select p.stockcount into v_es_pro.stockcount
  from es_product p where p.id=&id;
  if v_es_pro.stockcount > 0 then
  update es_product set stockcount = v_es_pro.stockcount - 1
  where id= &id;
  commit;
  dbms_output.put_line('id:'||&id||'库存已更新');
  elsif v_es_pro.stockcount<0 then
  dbms_output.put_line('id:'||&id||'数据不正常');
  else
  dbms_output.put_line('id:'||&id||'已经没有库存');
  end if;
end;


--loop循环
declare
  v_id es_sort.id%type := 9;
  v_sortname es_sort.sortname%type := '种类';
  v_fatherid es_sort.fatherid%type := 0;
begin
  loop
    insert into es_sort
    values(v_id,v_sortname||v_id,v_fatherid);
    v_id := v_id +1;
    commit;
    exit when (v_id>11);
  end loop;
end;

--异常处理
declare
  v_es_pro es_product%rowtype;
begin
  select p.stockcount into v_es_pro.stockcount
  from es_product p where p.id = &id;
  exception
    when no_data_found then
      dbms_output.put_line('无记录');
    when too_many_rows then
      dbms_output.put_line('记录太多');
    when dup_val_on_index then
      dbms_output.put_line('主键已存在');
    when invalid_number then
      dbms_output.put_line('有数值转换异常');
    when others then
      dbms_output.put_line('其它未知异常');
end;

--自定义异常
declare
  v_id es_order.id%type := &id;
  e_no_result exception;
begin
  delete es_order where id = v_id;
  if SQL%NOTFOUND then
     raise e_no_result;--所谓的触发异常
  end if;
exception
  when e_no_result then
    dbms_output.put_line('删除数据不成功');
    rollback;
  when others then
    dbms_output.put_line('其它未知异常');
    rollback;
end;


--游标的声明、打开、提取、关闭--不关闭游标会占用系统资源
declare
  v_ename emp.ename%type;
  v_dname dept.dname%type;
  cursor emp_cur is select e.ename,d.dname
  from emp e,dept d where e.deptno=d.deptno;
begin
  open emp_cur;
  loop
  fetch emp_cur into v_ename,v_dname;
  exit when emp_cur%notfound;
  dbms_output.put_line(v_ename||'-->'||v_dname);
  end loop;
  close emp_cur;
end;
--用while循环
begin
  open emp_cur;
  fetch emp_cur into v_ename,v_dname;
  while emp_cur%found loop
    dbms_output.put_line(emp_cur%rowcount||':'||v_ename||'-->'||v_dname);
    fetch emp_cur into v_ename,v_dname;
  end loop;
  close emp_cur;
end;


--公司上市，决定给员工提供薪水，
--入职时间每超过一年涨100,1000元封顶
declare
  v_hiredate emp.hiredate%type;
  v_empno emp.empno%type;
  cursor emp_cur is select e.empno,e.hiredate from emp e;
begin
  open emp_cur;
    loop
      fetch emp_cur into v_empno,v_hiredate;
      exit when emp_cur%notfound;
      if (1990-extract(year from v_hiredate)) <10 then
         update emp e set e.sal = e.sal+100*(1990-extract(year from v_hiredate))
         where e.empno = v_empno;
      else
         update emp e set e.sal = e.sal + 1000
         where e.empno = v_empno;
      end if;
    end loop;
  close emp_cur;
end;

--客户14年度签单总额，》100万  等级加2  50--100 加1  10万以下 减1
declare
  v_customerid salerecord.customerid%type;
  v_totalmoney salerecord.totalmoney%type;
  cursor sal_cur is select s.customerid,sum(s.totalmoney)
  from salerecord s where extract(year from s.contacttime)=2014
  group by s.customerid;
begin
  open sal_cur;
  loop
    fetch sal_cur into v_customerid,v_totalmoney;
    exit when sal_cur%notfound;
    if v_totalmoney>100 then
      update custom c set c.levels = c.levels + 2
      where c.id = v_customerid;
    elsif v_totalmoney between 50 and 100 then
      update custom c set c.levels = c.levels + 1
      where c.id = v_customerid;
    elsif v_totalmoney < 10 then
      update custom c set c.levels = c.levels - 1
      where c.id = v_customerid;
    end if;
  end loop;
  commit;
  close sal_cur;
end;


--for in 简化游标
declare
  v_customerid salerecord.customerid%type;
  v_totalmoney salerecord.totalmoney%type;
  cursor sal_cur is select s.customerid,sum(s.totalmoney) stotalmoney
  from salerecord s where extract(year from s.contacttime)=2014
  group by s.customerid;
begin
  for cus_money_record in sal_cur loop
     v_customerid :=  cus_money_record.customerid;
     v_totalmoney :=  cus_money_record.stotalmoney;
    if v_totalmoney>100 then
      update custom c set c.levels = c.levels + 2
      where c.id = v_customerid;
    elsif v_totalmoney between 50 and 100 then
      update custom c set c.levels = c.levels + 1
      where c.id = v_customerid;
    elsif v_totalmoney < 10 then
      update custom c set c.levels = c.levels - 1
      where c.id = v_customerid;
    end if;
  end loop;
  commit;
end;


--动态游标语法
declare
  type refcur_t is ref cursor
  return emp%rowtype;
  emp_refcur refcur_t;
  emp_record emp%rowtype;
begin
  open emp_refcur for
  select*from emp;
  loop
    fetch emp_refcur into emp_record;
    exit when emp_refcur%notfound;
    dbms_output.put_line(emp_refcur%rowcount||'-->'||emp_record.ename);
  end loop;
end;


--打印大于输入薪水的员工信息  
declare
  v_sal number := &sal;
  type ref_cur is ref cursor;
  emp_cur ref_cur;
  emp_rec emp%rowtype;
begin
  open emp_cur for 'select *from emp e
  where sal>:sal' using v_sal;
  loop
    fetch emp_cur into emp_rec;
    exit when emp_cur%notfound;
    dbms_output.put_line(emp_rec.ename||'-->'||emp_rec.sal);
  end loop;
  close emp_cur;
end;


--学生成绩表的行列转换  两种方法
select y.t_name,y.t_score 语文,s.t_score 数学,w.t_score 物理 from
(select *from score sc where sc.t_course='语文') y,
(select *from score sc where sc.t_course='数学') s,
(select *from score sc where sc.t_course='物理') w
where y.t_name=s.t_name and s.t_name=w.t_name


select sc.t_name,
sum(case when sc.t_course='语文' then sc.t_score end) 语文,
sum(case when sc.t_course='数学' then sc.t_score end) 数学,
sum(case when sc.t_course='物理' then sc.t_score end) 物理
from score sc group by sc.t_name



--存储过程的创建
create or replace procedure find_name (n_empno number)
is
v_ename scott.emp.ename%type;
begin
  select e.ename into v_ename from scott.emp e
  where e.empno = n_empno;
  dbms_output.put_line(v_ename);
exception
  when no_data_found then
  dbms_output.put_line('该编号信息未找到');
end;

--存储过程的调用
begin
find_name(7369);
end;
--第二种打开方式
SQL> set serveroutput on;
SQL> execute find_name(7369);


--存储过程的授权
grant execute on find_name to scott;
--授权用户后该用户还可以授权给其它用户
grant execute on find_name to scott with grant option;

--存储程序的使用
create or replace procedure emp_name
is
v_deptno scott.emp.deptno%type := 20;
begin
  for emp_rec in (select * from scott.emp) loop
  if emp_rec.deptno=v_deptno then
    dbms_output.put_line('部门:'||emp_rec.deptno||'-->'||'员工:'||emp_rec.ename);
  end if;
  end loop;
end;

--需求：创建存储过程，完成添加新雇员信息，
--包括编号、名称、薪水、工种和部门编号信息
create or replace procedure add_emp(
  eno number,--输入参数，雇员编号
  name varchar2,--输入参数，雇员名称
  salary number,--输入参数，雇员薪水
  job varchar2 default 'clerk',--输入参数，雇员工种默认‘ckerk’
  dno number--输入参数，雇员部门编号
)
is
  emp_null_error exception;--声明异常变量
  pragma exception_init(emp_null_error,-1400);
  --非预定义异常，前提：deptno列非空。插入空值会报错
  emp_no_deptno exception;--声明异常变量
  pragma exception_init(emp_no_deptno,-2291);
  --非预定义异常，前提：deptno列建立外键约束，插入部门编号不在部门表中会报错
begin
  insert into emp (empno,ename,sal,job,deptno) values(eno,name,salary,job,dno);
exception
  when dup_val_on_index then
       raise_application_error(-20000,'该雇员已存在');
  when emp_null_error then
       raise_application_error(-20001,'部门编号不能为空');
  when emp_no_deptno then
       raise_application_error(-20002,'不存在该部门编号');
end;

--在命令行调用时参数的传递方式
--按位置传递参数
exec add_emp(1111,'bill',3500,'manager',10);
--按名称传递参数
exec add_emp(dno=>10,name => 'MARY',salary => 5000,eno => 1112,job=>'manager');
--混合传递参数，若出现一个名称传参，其后必须名称传参
exec add_emp(1113，dno=>10,name => 'MARY',salary => 5000,job=>'manager');
--默认值的调用方法 两种
exec add_emp(dno=>10,name => 'MARY',salary => 5000,eno => 1112);
exec add_emp(1113，dno=>10,name => 'MARY',salary => 5000);


--在PL\SQL块中调用
--在这里传参也是上面三种方式
declare
  emp_20000 exception;
  pragma exception_init(emp_20000,-20000);
  emp_20001 exception;
  pragma exception_init(emp_20001,-20001);
  emp_20002 exception;
  pragma exception_init(emp_20002,-20002);
begin
  --异常，部门不存在
  add_emp(2111,'MARY',2000,'MANAGER',66);
  --异常，部门为空
  add_emp(2111,'MARY',2000,'MANAGER',null);
  --正确，雇员编号重复
  add_emp(2111,'MARY',2000,'MANAGER',10);
  --异常,雇员编号重复
  add_emp(2111,'MARY',2000,'MANAGER',10);
exception
  when emp_20000 then
       dbms_output.put_line('emp_20000雇员编码不能重复');
  when emp_20001 then
       dbms_output.put_line('emp_20001雇员编码不能为空');
  when emp_20002 then
       dbms_output.put_line('emp_20002不存在该部门编号');
  when others then
       dbms_output.put_line('出现了其它异常错误');
end;


--计算平均值
create or replace procedure sal_name
is
v_sal scott.emp.sal%type;
begin
  select avg(e.sal) into v_sal from scott.emp e;
  for emp_cur in (select *from scott.emp) loop
    if emp_cur.sal>v_sal then
    dbms_output.put_line(emp_cur.ename||'-->'||'优秀');
    elsif emp_cur.sal<v_sal then
    dbms_output.put_line(emp_cur.ename||'-->'||'加油');
    elsif emp_cur.sal=v_sal then
    dbms_output.put_line(emp_cur.ename||'-->'||'良好');
    end if;
  end loop;
end;

--查询某个地区的销售总额  带参数
create or replace procedure emp_demo2(area_no number) as
totalmoney number;
begin
  select sum(t.totalmoney) into totalmoney
  from scott.salerecord t where t.customerid in
  (select c.id from scott.custom c where c.location=area_no);
  dbms_output.put_line(totalmoney);
end;


--统计产品研发部，高于部门平均工资（部门经理除外）的人数
--out的使用
create or replace procedure count_person(totalcount out number) is
v_deptno scott.emp.deptno%type;
v_sal scott.emp.sal%type;
v_count number := 0;
begin
  select e.deptno,avg(e.sal) into v_deptno,v_sal from scott.emp e
  where e.deptno=20 and e.job<>'MANAGER' group by e.deptno;
  for emp_rec in (select *from scott.emp e
  where e.deptno=20 and e.job<>'manager') loop
  if emp_rec.sal>v_sal then
  v_count := v_count+1;
  end if;
  end loop;
  totalcount := v_count;
end;

--out模式的调用
declare
num number;
begin
num := 0;
count_person(num);
dbms_output.put_line(num);
end;


--存储过程的参数模式
--in、out和in out 输入、输出和输入/输出
--需求：编写存储过程。根据雇员编号，查询该雇员
--的姓名和薪水，并通过输出参数输出

--查询指定员工记录
create or replace procedure QueryEmp(
v_empno IN emp.empno%type,
v_ename OUT emp.ename%type,
v_sal OUT emp.sal%type)
as
begin
  select ename,sal into v_ename,v_sal from emp
  where empno = v_empno;
  dbms_output.put_line('温馨提示：编码为'||v_empno||'的员工已经查到！');
exception
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('温馨提示：雇员不存在！');
  when others then
    dbms_output.put_line('出现其它异常');
end QueryEmp;

--调用
declare
    v1 emp.ename%type;
    v2 emp.sal%type;
begin
    QueryEmp(7788,v1,v2);
    dbms_output.put_line('姓名:'||v1);
    dbms_output.put_line('工资:'||v2);
    QueryEmp(7900,v1,v2);
    dbms_output.put_line('姓名:'||v1);
    dbms_output.put_line('工资:'||v2);
    QueryEmp(1111,v1,v2);
    dbms_output.put_line('姓名:'||v1);
    dbms_output.put_line('工资:'||v2);
end;

--创建带IN OUT参数的过程
--需求：创建存储过程swap，对传入的两个参数在存储过程中
--进行交换，调用程序中显示交换后的结果
create or replace procedure swap(
  p1 in out number,
  p2 in out number)
is
  v_temp number;
begin
  v_temp := p1;
  p1 := p2;
  p2 := v_temp;
end;

--调用
declare
  num1 number := 100;
  num2 number := 200;
begin
  swap(num1,num2);
  dbms_output.put_line('num1 = '||num1);
  dbms_output.put_line('num2 = '||num2);
end;

--查错的命令
SQL> set serveroutput on;
SQL> show errors procedure emp_demo;

--debug 权限的授予
grant debug on scott.emp_demo to scott;
grant debug connect session to scott;


--结果集的存储过程的创建
create or replace procedure emp_demo1(
empsalary out sys_refcursor) as
begin
  open empsalary for select s.employeeid,s.totalmoney from salerecord s;
end;
--调用
declare
cur sys_refcursor;
v_id salerecord.id%type;
v_money salerecord.totalmoney%type;
begin
emp_demo1(cur);
loop
fetch cur into v_id,v_money;
exit when cur%notfound;
dbms_output.put_line(v_id||'-->'||v_money);
end loop;
end;


--日期函数的创建与调用
create or replace function func_datetime
return varchar2
is
begin
return to_char(sysdate,'yyyy"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"');
end;


begin
dbms_output.put_line(func_datetime);
end;

--创建查询函数  判断输入日期是否为周末
create or replace function func_isHoliday(p_date date)
return integer
as
  v_weekday integer := -1;
begin
  select to_char(p_date,'d') into v_weekday from dual;
  if v_weekday = 1 or v_weekday = 7 then
     return 1;
  else
     return 0;
  end if;
end;
--调用
declare
vday date := date '2014-9-9';
begin
dbms_output.put_line(func_isHoliday(vday));
end;

--创建带输出参数的函数及调用
create or replace function func_getinfo
(eno number,v_dname out varchar2) return varchar2
as
v_name dept.dname%type;
begin
  select e.ename,d.dname into v_name,v_dname
  from emp e,dept d where e.deptno=d.deptno and e.empno=eno;
  return v_name;
end;

--------
declare
v_name dept.dname%type;
v_dname dept.dname%type;
begin
v_name := func_getinfo(7369,v_dname);
dbms_output.put_line('姓名:'||v_name||'--->'||v_dname);
end;



--创建序列
create sequence SEQ_NEWSDETAIL
start with 1
minvalue 1
maxvalue 999999999
increment by 1
cache 10

--序列查询
select SEQ_NEWSDETAIL.NEXTVAL from dual
select SEQ_NEWSDETAIL.CURRVAL from dual


--创建触发器为表article添加自动增长
CREATE TRIGGER article BEFORE
insert ON  article FOR EACH ROW
begin
select SEQ_BBS.nextval into:New.id from dual;
end;