PL/SQL重点\难点

--编写一个触发器, 在对 my_emp 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录;
create or replace trigger delete_emp_trigger
before
delete on my_emp
for each row
begin
  insert into my_emp_bak 
  values(:old.employee_id,:old.salary);
end;

create or replace function sum_sal(dept_id number, total_count out number)
       return number
       is
       cursor sal_cursor is select salary from employees where department_id = dept_id;
       v_sum_sal number(8) := 0;   
begin
       total_count := 0;
       for c in sal_cursor loop
           v_sum_sal := v_sum_sal + c.salary;
           total_count := total_count + 1;
       end loop;       
       --dbms_output.put_line(‘sum salary: ‘ || v_sum_sal);
       return v_sum_sal;
end;   

declare
  e_too_high_exception exception;
  v_sal employees.salary%type;
begin
  select salary into v_sal from employees where employee_id = 100;
  if v_sal > 10000 then raise e_too_high_exception;
  end if;
exception
  when e_too_high_exception then dbms_output.put_line(‘工资太高了‘);
end;

declare
     --声明记录类型
     type emp_record is record(
     v_emp_id employees.employee_id%type,
     v_emp_sal employees.salary%type
     );
     --声明一个记录类型的变量
     v_emp_record emp_record;
     --声明游标
     cursor v_emp_cursor is select employee_id,salary from employees where department_id = 80;
begin
     --打开游标
     open v_emp_cursor;
     --提取游标
     fetch v_emp_cursor into v_emp_record;
     while v_emp_cursor%found loop  
           dbms_output.put_line(v_emp_record.v_emp_id||‘,‘||v_emp_record.v_emp_sal);
     fetch v_emp_cursor into v_emp_record;
     end loop;
     --关闭游标
     close v_emp_cursor;
--exception
end;

declare
     cursor v_emp_cursor is select employee_id,salary from employees where department_id = 80;
begin
    for i in v_emp_cursor loop
        dbms_output.put_line(i.employee_id||‘,‘||i.salary);
    end loop;
end;

--输出2-100之间的素数；
declare
    v_i number(3):=2;
    v_j number(3):=2;
    v_flag number(1):=1;
begin  
    while v_i <= 100 loop
          while v_j <= sqrt(v_i) loop
                if v_i mod v_j = 0  then v_flag := 0;
                end if;
                v_j := v_j +1;
          end loop;
          if v_flag = 1 then dbms_output.put_line(v_i);
            end if;
          v_j := 2 ;
          v_i := v_i +1;
          v_flag := 1;
    end loop;
end;

set serveroutput on
/*输出helloworld*/
declare --声明变量、类型。游标
begin --执行体(相当于main()函数)
dbms_output.put_line(‘helloworld‘);
exception --异常处理
end;

create user atuser identified by password --创建用户
alter user atuser quota unlimited on users --创建表空间
alter user scott  identified by tiger --修改密码
GRANT create session TO scott  --授予权限
create role my_role --创建角色
grant create session,create table,create view to my_role --授予角色权限
grant select,update on scott.employees to atuser --授予对象权限

select rn,employee_id,last_name,salary
from( select rownum rn,employee_id,last_name,salary
      from(
            select employee_id,last_name,salary
            from employees
            order by salary desc)     
)
where rn<=50 and rn>40

create sequence empseq
increment by 10 --每次增长10个；
start with 10 --从10增长；
maxvalue 100 -- 提供的最大值；
cycle --需要循环；
nocache --不需要缓存登录；

1. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间；

PL/SQL重点\难点

标签：title   cal   打开   base   请求   with   ref   管理   height