--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;
/