oracle 笔记整理

SQL> select length('啊'),lengthb('啊') from dual;

LENGTH('啊')  LENGTHB('啊')
------------      -------------
           1             2

SQL> select lpad(sal,10,0) from emp;

LPAD(SAL,10,0)
--------------------
0000000800

 

SQL> select rpad(sal,10,0) from emp;

RPAD(SAL,10,0)
--------------------
8000000000

 

SQL> select trim('  aaaa'),trim('H' from 'Hello') from dual;

TRIM('AAAA') TRIM('H'FROM'HELLO')
------------ --------------------
aaaa         ello

 

SQL> select round(123.456,2) from dual;

ROUND(123.456,2)
----------------
          123.46

 

SQL> select trunc(123.456) from dual;

TRUNC(123.456)
--------------
           123

SQL> select trunc(123.456,2) from dual;

TRUNC(123.456,2)
----------------
          123.45

 

SQL> select mod(1600,300) from dual;

MOD(1600,300)
-------------
          100

 

SQL> select next_day(sysdate,5) from dual;

NEXT_DAY(SYSDATE,5)
-------------------
2008-11-27 11:23:01

SQL> select next_day(sysdate,6) from dual;

NEXT_DAY(SYSDATE,6)
-------------------
2008-11-28 11:23:32

 

SQL> select to_char(sysdate,'YYYY-MM-DD') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DD')
-----------------------------
2008-11-24

 

 

select * from scott.emp;

/*declare
r_dept scott.DEPT%ROWTYPE;\*表的记录行变量*\
v_ename scott.EMP.ENAME%TYPE;
v_salary scott.EMP.SAL%TYPE;
v_tot_salary scott.EMP.SAL%TYPE;
cursor c_dept is select * from scott.dept order by deptno;
CURSOR c_emp(p_dept VARCHAR2) IS SELECT ename, sal FROM scott.emp WHERE deptno = p_dept ORDER BY ename;
BEGIN OPEN c_dept;
LOOP  
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department:' || r_dept.deptno || '-' ||r_dept.dname);
v_tot_salary := 0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename, v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name:' || v_ename || ' salary:' || v_salary);
v_tot_salary := v_tot_salary + v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:' || v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
运行结果：
Department:10-ACCOUNTING
Name:CLARK salary:2450
Name:KING salary:5000
Name:MILLER salary:1300
Toltal Salary for dept:8750
Department:20-RESEARCH
Name:ADAMS salary:1100
Name:FORD salary:3000
Name:JONES salary:2975
Name:SCOTT salary:3000
Name:SMITH salary:800
Toltal Salary for dept:10875
Department:30-SALES
Name:ALLEN salary:1600
Name:BLAKE salary:2850
Name:JAMES salary:950
Name:MARTIN salary:1250
Name:TURNER salary:1500
Name:WARD salary:1250
Toltal Salary for dept:9400
Department:40-OPERATIONS
Toltal Salary for dept:0*/

/*create or replace procedure getallmessage(str in out varchar2) as
  CURSOR curtype is
    select iid, iname from shopitem;
  str2 varchar2(1024);
begin
  str := 'select (select spname from shop where shop.spid=sellrecord.spid) srname,';
  for iteminfo in curtype loop
    str := str || 'sum(decode(iid,' || to_char(iteminfo.iid) || ',snum)) ' ||
           iteminfo.iname || '数量,';
    str := str || 'sum(decode(iid,' || to_char(iteminfo.iid) ||
           ',snum))/sum(snum) ' || iteminfo.iname || '百分比,';
  end loop;
  select substr(str, 1, length(str) - 1) into str2 from dual;
  str := str2 || ' from sellrecord group by spid order by spid';
  str2:='Create table mytable tablespace account2 as '|| str;
  execute immediate 'drop table mytable';
  execute immediate str2;
end;以上是动态存储过程*/

/*DECLARE
CURSOR c_dept IS SELECT deptno, dname FROM scott.dept ORDER BY deptno;
CURSOR c_emp(p_dept VARCHAR2) IS SELECT ename, sal FROM scott.emp WHERE deptno = p_dept ORDER BY ename;
v_tot_salary scott.EMP.SAL%TYPE;
BEGIN
FOR r_dept IN c_dept
LOOP DBMS_OUTPUT.PUT_LINE('Department: ' || r_dept.deptno || '-' ||r_dept.dname);
v_tot_salary := 0;
FOR r_emp IN c_emp(r_dept.deptno)
LOOP DBMS_OUTPUT.PUT_LINE('Name: ' || r_emp.ename || ' salary: ' ||r_emp.sal);
v_tot_salary := v_tot_salary + r_emp.sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept: ' || v_tot_salary);
END LOOP;
END;
运行结果：
Department: 10-ACCOUNTING
Name: CLARK salary: 2450
Name: KING salary: 5000
Name: MILLER salary: 1300
Toltal Salary for dept: 8750
Department: 20-RESEARCH
Name: ADAMS salary: 1100
Name: FORD salary: 3000
Name: JONES salary: 2975
Name: SCOTT salary: 3000
Name: SMITH salary: 800
Toltal Salary for dept: 10875
Department: 30-SALES
Name: ALLEN salary: 1600
Name: BLAKE salary: 2850
Name: JAMES salary: 950
Name: MARTIN salary: 1250
Name: TURNER salary: 1500
Name: WARD salary: 1250
Toltal Salary for dept: 9400
Department: 40-OPERATIONS
Toltal Salary for dept: 0*/

/*CREATE TABLE W_TEST1
(BH VARCHAR2(2),
N NUMBER(10));*/
/*select * from w_test1;

DECLARE
 n number(10);
BEGIN
 n:=1;
insert into w_test1(bh,n) values('a',n);
DECLARE
n number(10);
BEGIN
insert into w_test1(bh,n) values('b',n);
n:=100;
insert into w_test1(bh,n) values('b',n);
END;
insert into w_test1(bh,n) values('a',n);
END;*/

/*declare
v1 number(4);
begin
v1:=1;
loop
v1:=v1+1;
if v1<=99 then
dbms_output.put_line(v1);
else exit;
end if;
end loop;
end;    //注意结尾要分号！！！*/

/*declare
v1 number(2):=2;
begin
if v1=1 then dbms_output.put_line(v1);
elsif (v1>1)then dbms_output.put_line(v1||'>1');
else dbms_output.put_line(v1||'<1');
end if;
end;*/

/*declare
      x  number;
      begin
      x:= 0;
      loop
      x:=x+1;
      dbms_output.put_line(to_char(x));
      exit  when x=10;
      end loop;
  end;*/

/*select * from w_test1;

declare
v1 number default 1;
begin
loop
insert into w_test1 values('in',v1);
v1:=v1+1;
exit when v1>20;
end loop;
end;*/

/*declare
v1 number default 1;
begin
loop
insert into w_test1 values('in',v1);
v1:=v1+1;
if v1>20 then exit;
end if;
end loop;
end;
select * from w_test1;*/

/*declare
  x  number;
   begin
   x:= 1;
   while  x<10  loop
   dbms_output.put_line(x||'还小于10');
   x:= x+1;
  end loop;
end;
运行结果：
1还小于10
2还小于10
3还小于10
4还小于10
5还小于10
6还小于10
7还小于10
8还小于10
9还小于10*/

/*begin
for a in 1 .. 10 loop
dbms_output.put_line('in=' || a);
end loop;
end;
运行结果：
in=1
in=2
in=3
in=4
in=5
in=6
in=7
in=8
in=9
in=10*/
select * from score；
create or replace procedure Dp_add_gz(v_grid varchar2)
as n_rec number;
begin. select count(1) into n_rec from dual
where exists (select 1 from emp where empno = v_grid);if n_rec > 0 then.    update emp set sal = sal + 100 where empno = v_grid;.  end if;.exception.  when others then.    null;.end;