oracle 函數自學總結

SELECT ASCII('A') FROM DUAL
SELECT CHR('65') FROM DUAL
select sysdate from dual
SELECT ADD_MONTHS(SYSDATE,12) S FROM   DUAL --加月份
SELECT LAST_DAY(SYSDATE) D FROM DUAL         --日期的最後一天
SELECT TRUNC(SYSDATE,'mm') FROM DUAL         --本月的第一天比如現在是7月份那日期就是01-7月-10
SELECT TRUNC(SYSDATE,'yy') FROM DUAL         --本年的第一天比如現在是2010年那日期就是01-1月-10
SELECT MONTHS_BETWEEN('19-3月-2000','19-12月-1999') FROM DUAL --計算相隔月份
SELECT TO_CHAR(SYSDATE,'DAY DDSPTH,MONTH,YYYY','NLS_date_LANGUAGE=ENGLISH') DAY FROM DUAL --把日期轉換成英文格式的
select to_char(sysdate,'ddd') from dual --今天本年中的第多少天
select floor(sysdate - to_date('20100701','yyyymmdd')) from dual --兩個日期中間的間隔天數
SELECT SUBSTR('duchenggang',3,5) from dual              --截取字符串
select replace('duchenggang','cheng','') FROM DUAL      --字符串替換
SELECT UPPER('duchenggang') from dual
SELECT lower('DUCHENGGANG') from dual
SELECT INITCAP('duchenggang') FROM DUAL            --把字符串的第一個字母大寫
SELECT instr('duchenggang','en',1,1) FROM DUAL --搜索指定字符串的中字符的位置返回數值   select instr('abcfdgfdhd','d') from dual 查找字符串的位置
select TRIM('s' from 'string') from dual
SELECT TRIM('           duchenggang       ') FROM DUAL --去掉左右兩邊的空格
select LTRIM('        gao qian jing') from dual
select RTRIM('gao qian jing                          ') from dual
SELECT CONCAT('021-','1141')||'FDFDFDF' FROM DUAL --拼接字符串
SELECT POWER(2,2) FROM DUAL   --N的n次方
SELECT LENGTH('gang') FROM DUAL   ---select vsize('HELLO') from dual 返回字符的長度
select lpad('duchenggang',12,'?') from dual --用？填充左邊的值
select rpad('duchenggang',14,'?') from dual
SELECT lpad(rpad('gao',10,'*'),17,'*') from dual
SELECT ceil(3.5415927) from dual    --進一向上取整
SELECT ROUND(3.5415926) from dual   --四捨五入進一
select floor(3.5415926) from dual   --去尾不進一向下取整
select trunc(3.5415926) from dual   --去尾〞直接截斷，可以指定小數的位數，不指定即為整數
select NEXT_DAY('19-7月-2010','星期一 ') from dual --給出date和星期x算出下星期的時間
select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual --trunc(date,fmt)按照給定的要求給日期截斷如果fmt='mi'表示保留分
select to_char(sysdate) s1,to_char(sysdate,'yyyy-mm-dd') s2,to_char(sysdate,'yyyy') s3, to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,to_char(sysdate, 'hh24:mi:ss') s5,to_char(sysdate,'DAY') s6 from dual --將日期數字轉換成字符型
select to_number(to_char(sysdate,'hh12')) from dual --以數字顯示小時數
select greatest('11','12','13') from dual   --返回一組運算式中的最大值即比較字源的編碼大小
select least('11','12','13') from dual;     --返回一組運算式中的最中最小值
select soundex('DAWES') DAWES, SOUNDEX('DAWS') DAWS ,SOUNDEX('DAWSON') DAWSON FROM DUAL --返回一個與給定的字符串讀音相同的字符串
select min(all sal) from tableName    --表示對所有值求最小值
select min(distinct sal) from tableName --表示對不同的值求最小值，即相同的只取一次
select user from dual            --返回當前登錄用戶的名字
select userenv('isdba') from dual --查看當前用戶是否是dba 是就返回true
select userenv('language') from dual ---當前用環境變量數 select userenv('terminal') from dual ---返回終端或者機器的標誌
TRUNCATE table tableName --快速清除一個表中的數據
select trim(leading 9 from 9998767999) s1,trim(trailing 9 from 9998767999) s2,trim(9 from 9998767999) s3 from dual --去掉字符串的前導和後綴 s1是去點前倒，s2去掉後綴 s3去掉前導和後綴
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month FROM dual --查找月的第一天和最後一天
select translate('abc','b','xx') from dual   ---把b替換成x 注意xx是一位
select dept_no,decode(dept_no,1,'1',2,'2',30,'3','其他') from dept --decode[實現if ..then邏輯] 注:第一個是表達式,最后一個是不滿足任何一個條件的值
select sign(-32),sign(293) from dual --整數返回1 負數返回-1

--IF..THEN..ELSE語法
IF condition THEN
STATEMENTS 1;
STATEMENTS 2;
............
ELSE
STATEMENTS 3;
STATEMENTS 4;
............
END IF;
--例子1.1
CREATE OR REPLACE FUNCTION test_1(A NUMBER,B NUMBER)
RETURN NUMBER IS G NUMBER;
BEGIN
IF A>B then
g:=A;
ELSE
G:=B;
END IF;
RETURN G;
END test_1;
--執行過程
SELECT test_1(2,4) FROM DUAL

--LOOP循環語法即do while循環
--把1-50循環插入到數據庫
DECLARE
V_loop BINARY_INTEGER:=1;
BEGIN
LOOP
INSERT INTO tb1(first_name) VALUES(V_loop);
V_loop:=V_loop+1;
EXIT WHEN V_loop>50; --當道v_loop插到50時退出循環
END LOOP;
END;

--FOR循環語法例子
DECLARE
BEGIN
FOR bianliang in reverse tiaojian LOOP;
END LOOP;
END;
--1.2把全年的日期循環插入到數據庫中的某一列
CREATE TABLE inc_date(year DATE)
DECLARE
BEGIN
FOR i IN REVERSE 0..364 LOOP
INSERT INTO inc_date SELECT TRUNC(SYSDATE,'YYYY')+i FROM dual;
END LOOP;
END ;
SELECT * FROM inc_date
DELETE FROM inc_date
--1.3 把1-50插入到數據庫中的某一列
declare
begin
for i in reverse 1..50 loop
insert into tb1(first_name) values(i);
end loop;
end;

--複製表結構
create table y as select * from emp where 1=2
--複製表結構和表的數據
create table y as select * from emp where 1=1
--給表添加列
alter table tb1 add v_date date
--刪除列
alter table tb1 drop column vs_date
--修改列把v_date 列的date型修改為varchar2()
alter table tb1 modify(v_date VARCHAR2(20))
select * from tb1

--創建過程
CREATE OR REPLACE PROCEDURE pro_p1( V_test VARCHAR2)
IS
BEGIN

EXCEPTION

END
--創建函數 --注意函數有返回值
CREATE OR REPLACE FUNCTION fun_f1( V_test VARCHAR2) RETURN VARCHAR2 IS V_test2 varchar2(20);
IS
BEGIN

EXCEPTION

END

--創建包
CREATE OR REPLACE PACKAGE sp_package1 is
PROCEDURE pro_update_sal(name varchar2,newSal number);
FUNCTION fun_annual_income(name varchar2) return number;
end;

--創建包體
create or replace PACKAGE body sp_package1 is
PROCEDURE pro_update_sal(name varchar2,newSal number)
is
begin
update emp set sal=newSal where ename=name;
end;
function fun_annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;
--調用包裏面的過程和函數
cell sp_package1.pro_update_sal('SCOTT',120);
cell sp_package1.fun_annual_income('SCOTT');

------------------------------------

--創建觸發器(觸發器有12種 )
--1.1
CREATE OR REPLACE TRIGGER tri_t1
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN

END;

--練習
--函數
CREATE OR REPLACE FUNCTION find_area(vlength in number,vwidth in number)
    return number
as
declare
    varea number;
BEGIN
    varea:=vlength * vwidth;
    return varea;
end;
drop function find_area
select find_area(10,30) from dual

DECLARE
v_in number:=1;
v_out number:=2;
v_inOut number:=3;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before calling modeTest:');
dbms_output.put_line('v_in='||v_in||'v_out='||v_out||'v_inOut='||v_inOut);
end;

CREATE OR REPLACE PROCEDURE ParameterLength(
       p_parameter1 in out varchar2,
       p_parameter2 in out number) AS
BEGIN
       p_parameter1:='abcdefghijklm';
       p_parameter2:=12.3;
END ParameterLength;

declare
v_Variable1 varchar2(40);
v_Variable2 number(7,3);
BEGIN
ParameterLength(v_Variable1,v_Variable2);
END;

truncate table tb1
create sequence sq_q1
start with 1
increment by 1
ALTER TABLE TB1 modify(first_name number)

SELECT * FROM TB1
declare
v_loop number:=1;
begin
loop
insert into tb1(first_NAME) values(v_loop);
v_loop:=v_loop+1;
exit when v_loop>20;
end loop;
end;
truncate table tb1
select * from tb1

begin
for i in REVERSE 21..50 loop
insert into tb1(first_name) values(i);
end loop;
end;

--分頁存儲過
--創建一個包
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;

create OR REPLACE PROCEDURE sp_fengye
(
tableName in varchar2,--參數1表名
pagesize in number,
pagenow in number,
myrows out number,--總記錄數
mypagecount out number --總頁數
p_cursor out testpackage.test_cursor; --返回記錄集
)
is
--定義部份
--定義sql語句字符串
v_sql varchar(2000);
--定義兩個整數
v_begin number:=(pagenow-1)*pagesize+1;--從哪裡開始取值
v_end number:=pagenow*pagesize
BEGIN
v_sql:='select * from (select t1.ename,rownum rn from(select * from '||tableName||' ordey by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;
--把遊標和sql語句關聯起來
open p_cursor for sql;
--計算myrows 和mypagecount的值
--組織一個sql
v_sql:='select count(*) from '||tableName
--執行sql 并把返回值付給myrows；
execute immediate v_sql into myrows;
--計算mypagecount
if mod(myrows,mypagesize)=0 then
mypagecount=myrows/pagesize;
else
mypagecount=myrows/pagesize+1;
end if;
--關閉遊標
close p_cursor;
end;

select to_char (12.356,'$99,999,9999') from dual
select * FROM EMP
select count( DISTINCT ename)from emp

select deportno, avg(salary)
from emp
where salary > 1200
group by deportno
having avg(salary) > 3000
order by avg(salary) asc

select * from dept
select * from emp
where salary in (select max(salary) from emp group by deportno)

select v.* from(select rownum r, ename,dept_name
from emp e
full join dept d
on e.deportno=d.dept_no) v where r in(1,2,3) and ename='Mark'

select salary
from emp
where salary not in
(select distinct (e1.salary) from emp e1, emp e2 where e1.salary < e2.salary)
select distinct (e1.salary) from emp e1, emp e2 where e1.salary < e2.salary
select distinct (e1.salary) from emp e1

select TABLESPACE_NAME from all_tables where table_name='EMP'

--遊標返回查詢的多條語句
TRUNCATE TABLE TB1
select * from tb1
create OR REPLACE PROCEDURE pro_test10 is
CURSOR c IS
    SELECT * FROM EMP;
V_EMP c%rowtype;
begin
open c;
loop
    fetch c into v_emp;
    exit when(c%notfound);
    INSERT INTO tb1(first_name,NAME) VALUES(1,v_emp.ename);
end loop;
close c;
end pro_test10;
--執行過程
begin
pro_test10;
end;

--通過遞規寫樹
--創建表并插入數據
create table article (
id number primary key,
cont varchar2(4000),
pid number,
isleaf number(1),
alevel number(2)
)

insert into article values(1,'螞蟻大象大戰',0,0,0);
insert into article values(2,'螞蟻被打趴下',1,0,1);
insert into article values(3,'螞蟻也不好過',2,1,2);
insert into article values(4,'瞎說',2,0,2);
insert into article values(5,'么有有瞎說',4,1,3);
insert into article values(6,'怎么可能',1,0,1);
insert into article values(7,'怎么沒有可能',6,1,2);
insert into article values(8,'可能行很大的',6,1,2);
insert into article values(9,'大象進醫院了',2,0,2);
insert into article values(10,'螞蟻是護士',9,1,3);

SELECT * FROM EMP
SELECT * FROM DEPT
insert into dept(dept_no,dept_name) values(3,'C')
insert into emp(empno,ename,salary,comm,deportno) values(7562,'gangge',2000,25000,3)

--創建觸發器
CREATE OR REPLACE TRIGGER tri_dept
BEFORE DELETE ON dept
FOR EACH ROW
BEGIN
DELETE FROM EMP WHERE deportno=:OLD.dept_no;
END;

DELETE FROM DEPT WHERE dept_no=3

select * from v$version

send_email (p_From IN VARCHAR2
,p_To IN VARCHAR2
,p_Subject IN VARCHAR2
,p_Body IN VARCHAR2
,p_Cc IN VARCHAR2 := NULL
,p_Bcc IN VARCHAR2 := NULL
,p_ContentType IN VARCHAR2 := 'text/plain;charset=big5'
,p_MailIp IN VARCHAR2 := '10.98.0.211' --'10.98.0.28'
CREATE OR REPLACE PROCEDURE SEND_MAI_TESTS
IS
begin
send_email('cg.du@mic.com.tw','cg.du@mic.com.tw','測試','剛哥');
end;
--執行過程
BEGIN
SEND_MAI_TESTS;
END;

select trim(leading 9 from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 9998767999) s3 from dual

posted @ 2010-07-21 14:51 你妹的sb 阅读(353) 评论(0) 编辑收藏举报

刷新页面返回顶部

夏楚枫

oracle 函數自學總結

公告