oracle sql

select s.name,s.birth from student s;
select s.name as 姓名,s.birth as 生日 from student s; //按字段查询
select distinct s.name,s.age from student s; //去重
select * from student where id^=1
select * from student order by age,id desc;
decode //分支判断
select nvl(birth,to_date('1990-01-01','yyyy-mm-dd')) from student
select t.name,s.name,s.birth,s.age from student s inner join teacher t on s.teacherId=t.id;
select t.name,s.name,s.birth,s.age from student s left outer join teacher t on s.teacherId=t.id;
alter table teacher rename column id to teacherId//更改字段
select * from student natural join teacher//自然连接
select nvl(t.name,'无老师'),nvl(s.name,'无学生'),nvl(s.age,0) from student s full join teacher t on s.teacherId=t.teacherId;
select * from student where id in(select age from teacher)
select * from student where id>any(select teacherId from teacher)
select * from student where id > all(select teacherId from teacher)
select * from student where exists (select teacherId from teacher)
select * from student where id>1 union select * from student where id=1;
select * from student where id > 0 intersect select * from student where id=1;
select * from student where id>0 minus select * from student where id=1

SELECT * FROM USER_ROLE_PRIVS;
select * from dba_sys_privs where grantee='DBA';
GRANT DBA TO SCOTT;

--***************************************************触发器*************************************************
create or replace trigger tr_emp
before insert
on emp
begin
  if user!='SCOTT' then
    raise_application_error(-20001,user||'权限不足');
  end if;
end;

create or replace trigger tr_emp_log
after insert or delete or update
on emp
begin
  if INSERTING then
    insert into emp_log values(user,'insert',sysdate);
  else if updating then
    insert into emp_log values(user,'update',sysdate);
  else if deleting then
    insert into emp_log values(user,'delete',sysdate);
  end if;
  end if;
  end if;
end;

create or replace trigger tr_book_add
after insert
on t_book
for each row
  begin
    update t_bookType set num=num+1 where id=:new.bookTypeId;
  end;
  
  create or replace trigger tr_book_delete
after delete
on t_book
for each row
begin
  update t_bookType set num=num-1 where id=:old.bookTypeId;
end;

--***************************************************禁用/启用触发器*************************************************

alter trigger tr_book disable;
alter trigger tr_book enable;

--***************************************************条件语句*************************************************
declare emp_count number;
begin
  select count(*) into emp_count from emp where sal>=3000;
  case emp_count
    when 0 then dbms_output.put_line('没有大于等于3000');
    when 1 then dbms_output.put_line('有'||emp_count||'位大于等于3000');
    when 2 then dbms_output.put_line('有'||emp_count||'位大于等于3000');
    when 3 then dbms_output.put_line('有'||emp_count||'位大于等于3000');
    else dbms_output.put_line('有超过3位于等于3000');
   end case;
end;

declare emp_count number;
begin
  select count(*) into emp_count from emp where sal>=3000;
  case emp_count
    when 0 then dbms_output.put_line('没有大于等于3000');
    when 1 then dbms_output.put_line('有1位大于等于3000');
    when 2 then dbms_output.put_line('有2位大于等于3000');
    when 3 then dbms_output.put_line('有3位大于等于3000');
    else dbms_output.put_line('有超过3位于等于3000');
   end case;
end;
--***************************************************循环语句*************************************************
declare g_id number:=2;
        g_losal number;
        g_hisal number;
begin
  loop
    if(g_id>4) then
      exit;
    end if;
    select losal,hisal into g_losal,g_hisal from salgrade where grade=g_id;
    dbms_output.put_line(g_id||'等级的最低薪资：'||g_losal||' 最高薪资：'||g_hisal);
    g_id:=g_id+1;
  end loop;
end;

declare g_id number:=2;
        g_losal number;
        g_hisal number;
begin
  while g_id<5 loop
    select losal,hisal into g_losal,g_hisal from salgrade where grade=g_id;
    dbms_output.put_line(g_id||'等级的最低薪资：'||g_losal||' 最高薪资：'||g_hisal);
    g_id:=g_id+1;
  end loop;
end;

declare
        g_losal number;
        g_hisal number;
begin
  for g_id in 2..4 loop
    select losal,hisal into g_losal,g_hisal from salgrade where grade=g_id;
    dbms_output.put_line(g_id||'等级的最低薪资：'||g_losal||' 最高薪资：'||g_hisal);
  end loop;
end;

--***************************************************显式游标*************************************************

set serverout on;
declare cursor cu_emp is 
select empno,ename,sal from emp;
e_no emp.empno%type;
e_name emp.ename%type;
e_sal emp.sal%type;
begin
  open cu_emp;
  fetch cu_emp into e_no,e_name,e_sal;
  while cu_emp%found loop
    dbms_output.put_line('编号：'||e_no||' 姓名：'||e_name||' 薪资：'||e_sal);
    fetch cu_emp into e_no,e_name,e_sal;
  end loop;
  close cu_emp;
end;
--
set serverout on;
declare cursor cu_emp is 
select * from emp where sal>2000 and sal<3000;
e emp%rowtype;
begin
  open cu_emp;
  fetch cu_emp into e;
  while cu_emp%found loop
    dbms_output.put_line('编号：'||e.empno||' 姓名：'||e.ename||' 薪资：'||e.sal);
    fetch cu_emp into e;
  end loop;
  close cu_emp;
end;
--***************************************************隐式游标*************************************************
begin
  if sql%isopen then
     dbms_output.put_line('sql游标已打开');
  else
    dbms_output.put_line('sql游标未打开');
  end if;
end;
--
declare e_count number;
begin
  select count(*) into e_count from emp;
  dbms_output.put_line('游标捕获的记录数：'||sql%rowcount);
end;
--
begin
  update emp set ename='sb3' where empno=123;
  if sql%rowcount=1 then
    dbms_output.put_line('已更新');
  else
    dbms_output.put_line('未更新');
  end if;
end;

--强类型

declare type emptype is ref cursor return emp%rowtype;
cu_emp emptype;
e_count number;
e emp%rowtype;
begin
  select count(*) into e_count from emp where job='PRESIDENT1'; 
  if e_count=0 then
    open cu_emp for select * from emp;
  else
    open cu_emp for select * from emp where job='PRESIDENT';
  end if;
  fetch cu_emp into e;
  while cu_emp%found loop
    dbms_output.put_line('编号：'||e.empno||' 姓名：'||e.ename||' 薪资：'||e.sal);
    fetch cu_emp into e;
  end loop;
end;

--弱类型

declare type customType is ref cursor;
e_count number;
e emp%rowtype;
s salgrade%rowtype;
cType customType;
begin
  select count(*) into e_count from emp where job='PRESIDENT1';
  if e_count=0 then
    open cType for select * from salgrade;
    fetch cType into s;
    while cType%found loop
      dbms_output.put_line('等级：'||s.grade||' 最低：'||s.losal||' 最高：'||s.hisal);
      fetch cType into s;
    end loop;
    close cType;
  else
    open cType for select * from emp where job='PRESIDENT';
    fetch cType into e;
    while cType%found loop
      dbms_output.put_line('编号：'||e.empno||' 姓名：'||e.ename||' 薪资：'||e.sal);
      fetch cType into e;
    end loop;
    close cType;
  end if;
end;
--***************************************************方法*************************************************
--创建（不带参数）
create or replace function getEmpCount return number as
begin
  declare emp_count number;
  begin
    select count(*) into emp_count from emp;
    return emp_count;
  end;
end getEmpCount;
--调用
begin
  dbms_output.put_line('表emp有：'||getEmpCount||'条数据');
end;
--创建（带参数）
create or replace function getTableCount(table_name varchar2) return number as
begin
  declare table_count number;
  query_sql varchar2(300);
  begin
    query_sql:='select count(*) from '||table_name;
    execute immediate query_sql into table_count;
    return table_count;
  end;
end getTableCount;
--调用
begin
  dbms_output.put_line('表dept有：'||getTableCount('dept')||'条数据');
end;
--***************************************************存储过程*************************************************
create or replace procedure addBook(bookName in varchar2,typeId in number) as 
begin
  declare maxId number;
  begin
    select count(*) into maxId from t_book;
    insert into t_book values(maxId+1,bookName,typeId);
    commit;
  end;
end;
--调用
execute addBook('java',1);
--
create or replace procedure addBook2(bookName in varchar2,typeId in number) as 
begin
  declare maxId number;
  n number;
  query_sql varchar2(300);
  begin
    query_sql:='select count(*) from t_book where BOOKNAME='||''''||bookName||'''';
    execute immediate query_sql into n;
    if n>0 then
      dbms_output.put_line('查询到的数目：'||n||',未更新');
      return;
    end if;
    select count(*) into maxId from t_book;
    insert into t_book values(maxId+1,bookName,typeId);
    dbms_output.put_line('插入了'||sql%rowcount||'条数据');
    commit;
  end;
end addBook2;
--
execute addBook2('java',1);
--
create or replace procedure addBook3(bookName in varchar2,typeId in number,n1 out number,n2 out number) as 
begin
  declare maxId number;
  n number;
  query_sql varchar2(300);
  begin
    select count(*) into n1 from t_book;
    query_sql:='select count(*) from t_book where BOOKNAME='||''''||bookName||'''';
    execute immediate query_sql into n;
    if n>0 then
      dbms_output.put_line('查询到的数目：'||n||',未更新');
      select count(*) into n2 from t_book;
      return;
    end if;
    select count(*) into maxId from t_book;
    insert into t_book values(maxId+1,bookName,typeId);
    dbms_output.put_line('插入了：'||sql%rowcount||'条数据');
    commit;
    select count(*) into n2 from t_book;
  end;
end addBook3;
--调用
declare n1 number;
        n2 number;
begin
  addBook3('hehe',1,n1,n2);
  dbms_output.put_line('n1='||n1);
  dbms_output.put_line('n2='||n2);
end;
--***************************************************创建用户*************************************************
create user TEST identified by 1234 default tablespace users;
--授权
grant create session to TEST;
--锁定用户
alter user TEST account lock;

driverClass: oracle.jdbc.driver.OracleDriver
url:jdbc:oracle:thin@localhost:1521:orcl
userName:sys as dba