oracle

--8.27
   sqlplus system/sias
   alter user hr account unlock;
   alter user hr identified by hrpwd;
   conn hr/hrpwd
   /*create table sal_history
   as select employee_id,hire_date,salary
   from employees;*/
set pagesize 100
 select department_id,job_id,sum(salary),
    rank() over(order by sum(salary) desc) as 次序
      from employees
         group by department_id,job_id;
-- 8.30
    select rownum as 次序,last_name,salary
      from (select last_name,salary
                   from employees
                          order by  salary desc)
      where ROWNUM<=5;

--9.1
  alter user scott account unlock;
  alter user scott identified by tiger;
  conn scott/tiger
alter session set nls_date_format='YYYY-MM-DD';
DECLARE
       v_bonus number(8,2);
       v_name  varchar2(30):='SCOTT';
       v_hiredate DATE:='13-4月-09';
       v_valid BOOLEAN:=TRUE;
BEGIN
        select sal*0.10
        into v_bonus
        from emp
        where ename=v_name;
END;
  /
--9.2

   set serveroutput on
   define p_empno=7934
   variable g_salary NUMBER
   set VERIFY on
   begin
        select sal
        into  :g_salary
        from emp
        where empno='&p_empno';
        DBMS_output.put_line('工资值已取出至绑定变量 g_salary');
end;
/
print g_salary

--9.3

 set serveroutput on
 declare
        v_eno emp.empno%type;
        v_str varchar2(20);
        v_str v_str%type;
 begin
       select empno,job into v_eno,v_str
       from emp;
       WHERE empno=7369;
     v_str1 :=v_str;
 DBMS_output.put_line(v_eno||'is a'||v_str);
 end;
 /

--9.4
 
declare
   type worker_record_type is RECORD(
        id number(3),
        name varchar2(20)
   );
   worker_record worker_record_type;
 begin
   worker_record.id:=10;
   worker_record.name='Jake';
  DBMS_output.put_line(worker_record.id||':'worker_record.name);
 end;
  /

--9.5

  declare
  TYPE my_table_type is TABLE of varchar2(20)
  index by binary_integer;
  my_table my_table_type;
 begin
    my_table(1):='百川东到海';
    my_table(3):='何时复西归';
    my_table(4):='少壮不努力';
    my_table(-100):='老大徒伤悲';
    DBMS_output.put_line(my_table(1));
    DBMS_output.put_line(my_table(3));
    DBMS_output.put_line(my_table(4));
    DBMS_output.put_line(my_table(-100));
    DBMS_output.put_line(my_table.COUNT);
 end;
  /