plsql小记

1.
查看oracle版本
select * from product_component_version;


2.
create or replace function get_sal(no in number) return number is
 salary number(6,2);
begin
  select sal into salary from emp where empno =no;
  return salary;
end get_sal;


3.
--事务
create procedure insert_dept(no number ,name varchar2)
is 
begin
insert into dept(deptno,dname) values(no,name);
end;


4.
create or replace function get_sal(name varchar2)
return number is 
v_sal number(6,2);
begin
select sal into v_sal from emp where upper(ename) = upper(name);
return v_sal;
end;

var salary number

exec :salary := get_sal('scott')

print salary

5.
show errors

6.
delcare 
begin 
exception
后面没有分号
而
end
后面则必须带有分号

7.
 set serveroutput on
 begin
 dbms_output.put_line('hello, everyone');
 end;
 /

8.
declare
v_ename varchar2(50);
begin
select ename into v_ename from emp where empno =&no;
dbms_output.put_line('name :' ||v_ename);
end;

9.
declare
v_ename varchar2(50);
begin
select ename into v_ename from emp where empno = &no;
dbms_output.put_line('雇员名:' ||v_ename);
exception
  when No_Data_Found then
       dbms_output.put_line('请输入正确的雇员号!');
end;
/

9.
  <<outer>>

   declare 
    v_deptno number(12);
    v_dname  varchar2(10);

   begin
   <<inner>>
    begin 
       select deptno into v_deptno from emp
          where lower(ename) = lower('&name');
    end;--<<inner>>
    select dname into v_dname from dept where deptno = v_deptno;
    dbms_output.put_line('部门名：' ||v_dname);

    end; --<<outer>>
10.
create or replace procedure update_sal(name varchar2,newsal number)
is
 begin
 update emp set sal = newsal where lower(ename) = lower(name);
 end;

var income number;
call annual_income('scott') into :income;

11.
create package emp_pkg is
  procedure update_sal(name varchar2,newsal number);
  function annual_income(name  varchar2) return number;

  end;


create package body emp_pkg is 
procedure update_sal(name varchar2,newsal number)
is 
begin
update emp set sal = newsal where lower(ename) = lower(name);
end;
function annual_income(name varchar2) return number
is
annual_salary number(7,2);

begin
select sal*12+nvl(comm,0) into annual_salary 
from emp where lower(ename) = lower(name);
return annual_salary;
end;

end;

12.
declare
v_ename varchar2(5);
v_sal number(6,2);
c_tax_rate constant number(3,2):=0.03;
v_tax_sal number(6,2);
begin
select ename,sal into v_ename,v_sal
from emp where empno =&eno;
v_tax_sal :=v_sal*c_tax_rate;
dbms_output.put_line('雇员名：'||v_ename);
dbms_output.put_line('雇员工资：'||v_sal);
dbms_output.put_line('所得税:'||v_tax_sal);
end;
/

declare
v_ename emp.ename%type;
v_sal emp.sal%type;
c_tax_rate constant number(3,2) :=0.03;
v_tax_sal v_sal%type;
begin
select ename,sal into v_ename,v_sal
from emp where empno =&eno;
v_tax_sal := v_sal*c_tax_rate;
dbms_output.put_line('雇员名:'||v_ename);
dbms_output.put_line('雇员工资:'||v_sal);
dbms_output.put_line('所得税:'||v_tax_sal);
end;

13.
declare
       type ename_table_type is table of emp.ename%type
            index by binary_integer;
            ename_table ename_table_type;
begin
            select ename into ename_table(-1) from emp where empno =7788;
       dbms_output.put_line('雇员姓名:'||ename_table(-1));
end;

14.
declare 
type c1 is ref cursor;
emp_cursor c1;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor for
   select ename,sal from emp where deptno =10;
loop
 fetch emp_cursor into v_ename,v_sal;
 exit when emp_cursor%NOTFOUND;
 dbms_output.put_line(v_ename);
end loop;
close emp_cursor;
end;

15.
desc
16.
set pagesize 30;
17.
 select ename,to_char(hiredate,'YYYY-MM-DD') from emp;

18.
select distinct deptno,job from emp;
19.
select ename as "姓名",sal*12 as "年收入" from emp;

20.
select ename,sal ,hiredate from emp  where hiredate >'01-1月-82';
21.
declare
  type emp_record_type is record(
       name emp.ename%type,
       salary  emp.sal%type,
       dno emp.deptno%type);
  emp_record emp_record_type;
begin
   select ename,sal, deptno into emp_record
   from emp where empno =&no;
   dbms_output.put_line(emp_record.name);
end;
/

22.
declare 
 dept_record dept%rowtype;
begin
     dept_record.deptno :=55;
     dept_record.dname := 'administrator';
     dept_record.loc := 'beijing';
   insert into dept values dept_record;
   commit;
   end;
23.
declare
type ename_table_type is table of emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
  select ename into ename_table(-1) from emp
  where empno =&no;
  dbms_output.put_line('雇员名:'||ename_table(-1));
  end;

24.
declare
type area_table_type is table of number index by varchar2(10);
area_table area_table_type;
begin
area_table('北京') :=1;
area_table('上海') :=2;
area_table('广州') :=3;
dbms_output.put_line(area_table.first);
dbms_output.put_line(area_table.last);
end;

25.
declare
cursor emp_cursor is
       select ename,sal from emp where deptno =10;
       v_ename emp.ename%type;
       v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename||':'||v_sal);
end loop;
close emp_cursor;
end;

26.
declare
  cursor emp_cursor is
    select ename, sal from emp;
  emp_record emp_cursor%rowtype;
begin
  open emp_cursor;
  loop
    fetch emp_cursor
      into emp_record;
    exit when emp_cursor%notfound;
    dbms_output.put_line('雇员名:' || emp_record.ename || ',雇员工资:' ||
                         emp_record.sal);
  end loop;
  close emp_cursor;
end;
27.
declare
  cursor emp_cursor(no number) is
    select ename from emp where deptno = no;
  v_ename emp.ename%type;
begin
  open emp_cursor(10);
  loop
    fetch emp_cursor
      into v_ename;
    exit when emp_cursor%notfound;
    dbms_output.put_line(v_ename);
  end loop;
  close emp_cursor;
end;

28.
declare
  cursor emp_cursor is select ename,sal from emp;
begin
     for emp_record in emp_cursor loop
         dbms_output.put_line(''||emp_cursor%rowcount||''||emp_record.ename);
         end loop;
         end;

29.seleect ename,sal from emp where ename like '%a_%' escape 'a'