CREATE TABLE person
(
pid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR(30)NOT NULL ,
tel VARCHAR(50)NOT NULL UNIQUE ,
age NUMBER CHECK(age BETWEEN 0 AND 150)
) ;
-- 插入数据
INSERT INTO person(pid,name,tel,age) VALUES (11,'张三','1234567',30);
-- 年龄的输入错误
INSERT INTO person(pid,name,tel,age) VALUES (12,'李四','2345678',-100);

DELETE FROM person WHERE pid=11;

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
name VARCHAR(50) ,
-- 书应该属于一个人
pid NUMBER ,
-- 建立约束：book_pid_fk，与 person 中的 pid 为主-外键关系
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE 
CASCADE
) ;

CREATE SEQUENCE [schema.]sequence_name
[ START WITH i ] [ INCREMENT BY j ]
[ MAXVALUE m | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]

创建一个myseq序列，从1开始，每次递增1
Create sequence myseq
Start with 1 
Increment by 1 
Order
cache 20
Nocycle;

declare
    cursor  stus  is  select * from student; -- 定义一个静态游标
    stu student%rowtype; -- 定义一个变量，是行类型，用来接收返回值
begin
    for stu in  stus loop  -- for循环的开启游标方式
        dbms_output.put_line(stu.sno||':'||stu.sname);
    end loop;
end;

declare
    type my_type is ref cursor;-- 定义一个游标类型
    stus my_type; -- 定义一个游标变量
    str varchar(200);
    myrow student%rowtype;-- 用整行去接收返回值
begin
    str:='select * from student where sage between :x and :y';
    　　　　　　　　　　　　　　　　　　　　　　  -- :x 和:y 在这里是占位符　　　　　　　
    open stus for str using 23,28;-- 开启游标并传入参数
    fetch stus into myrow; -- 将获取到的每一行返回到定义好的myrow中
    while(stus%found)loop -- 循环判断游标是否取到
        dbms_output.put_line(myrow.sno||':'||myrow.sname);
        fetch stus into myrow;
    end loop;
    close stus;-- 关闭游标
end;

declare
    type mytype is ref cursor;
    stus mytype;
    str varchar(200);
    myno varcahr(20);-- 定义接收sno的变量
    myname varchar(20);-- 定义接收sname的变量
begin
    str:='select sno,sname from student where sage between :x and :y';
    
    open stus for str using 23,28;
    fetch stus into myno,myname;-- 将返回值传入定义好的变量中
    while(stus%found)loop
        dbms_output.put_line(myno||':'||myname);
        fetch stus into myno,myname;
  end loop;
  close stus;
end;

declare
  stus sys_refcursor;-- 系统游标
  str varchar(200);-- 存放SQL字符串
  myrow student%rowtype;-- 一行一行的去接收
   
begin
  str:='select * from student where sage between :x and :y';

  -- 将using后的值传给str语句中的占位符，并执行语句，并把执行结果放入前面的游标中
  open stus for str using 23,25; 
  fetch stus into myrow;
  while (stus%found) loop
        dbms_output.put_line(myrow.sno||':'||myrow.sname);
        fetch stus into myrow;
  end loop;
  close stus;
end;

方法一：while循环，静态游标
declare
  cursor dept2 is select * from dept;
  myrow dept%rowtype;
begin
  open dept2;
  fetch dept2 into myrow;
  while(dept2%found)loop
      dbms_output.put_line(myrow.deptno||':'||myrow.loc);
      fetch dept2 into myrow;
  end loop;
　close dept2;
end;

方法二：while循环，动态系统游标
declare
   dept1 sys_refcursor;
   myrow dept%rowtype;
   str varchar(200);
begin
  str:='select * from dept';
  open dept1 for str;
  fetch dept1 into myrow;
  while(dept1%found)loop
        dbms_output.put_line(myrow.deptno||':'||myrow.loc);
        fetch dept1 into myrow;
  end loop;
end;

方法三：for循环，静态游标
declare
  cursor dept2 is select * from dept;
  myrow dept%rowtype;
begin
  for myrow in dept2 loop
      dbms_output.put_line(myrow.deptno||':'||myrow.loc);
  end loop;
end;

create or replace Function 函数名字（参数  参数类型）return  返回值类型
as
    定义变量来接收返回值
begin
    执行语句
end；

-- 定义一个函数：传参（雇员的编号）返回（查询到的雇员的年薪）
CREATE OR REPLACE FUNCTION myfunc(eno emp.empno%TYPE) RETURN NUMBER
AS
　　rsal NUMBER ;
BEGIN
　　SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;
　　RETURN rsal ;
END ;

SELECT myfun(7369) FROM dual ;