SET SERVEROUTPUT ON; --开启输出显示功能，默认是关闭的，否则执行成功，看不到输出结果BEGIN dbms_output.put_line('Hello,Welcome to PL/SQL'); END; 输出结果：
CREATE OR REPLACE PROCEDURE P_TESTIS BEGIN dbms_output.put_line('Hello,Welcome to PL/SQL');END;
EXEC P_TEST;--执行存储过程
结果：
CREATE OR REPLACE PROCEDURE P_TESTIS BEGIN dbms_output.put_line(2/0); EXCEPTION --这是异常处理语句WHEN OTHERS THEN --必须知道什么异常，这里指定其他 dbms_output.put_line('0不能作为被除数');END; 执行结果：
这样一个带有执行块的和异常的存储过程就创建完整了。
| 标识符 | 命名规则 | 例子 |
| 程序变量 | V_name | V_name |
| 程序常量 | C_Name | C_Name |
| 游标变量 | Name_cursor | Emp_cursor |
| 异常标识 | E_name | E_too_many |
| 表类型 | Name_table_type | Emp_record_type |
| 表 | Name_table | Emp |
| 记录类型 | Name_record | Emp_record |
| SQL*PLUS替代变量 | P_name | P_sal |
| 绑定变量 | G_name | G_year_sal |
CREATE OR REPLACE PROCEDURE P_TEST(V_name varchar2,V_sal number)IS BEGIN UPDATE emp SET sal = V_sal WHERE ename=V_name; dbms_output.put_line('更新成功');EXCEPTION --这是异常处理语句WHEN OTHERS THEN --必须知道什么异常，这里指定其他 dbms_output.put_line('更新失败');END; 执行存储过程
EXEC P_TEST('SMITH',10); 
CREATE OR REPLACE PROCEDURE P_TEST(V_name varchar2,V_sal number)IS v_yearSal number; --定义年薪变量BEGIN UPDATE emp SET sal = V_sal WHERE ename=V_name; v_yearSal:=12*V_sal; --给变量赋值 用：= dbms_output.put_line('更新成功'); dbms_output.put_line('12个月的总工资为：'); dbms_output.put_line(v_yearSal); --输出年薪EXCEPTION --这是异常处理语句WHEN OTHERS THEN --必须知道什么异常，这里指定其他 dbms_output.put_line('更新失败');END; 执行
EXEC P_TEST('SMITH',10); 结果：
存储过程：
先来个最简单的存储过程，往表中添加一条数据
--1.创建测试表 create table myTest ( name varchar2(30), passwd varchar2(30)); --2编写存储过程，向测试表添加数据 create or replace procedure p_insert is begin insert into myTest values('Jim','changeit'); end; --3.调用存储过程 exec p_insert; 下面创建一个带参数的存储过程，将员工的姓名和薪水作为参数，去修改原来表中的数据
--存储过程 create or replace procedure p_update (spName varchar2, newSal number) is begin update emp set sal=newSal where ename = spName; end;
创建一个存储过程，可以直接调用插入表的数据
执行存储过程：
查询：
存储过程
一.最简单的存储过程架构：
CREATE OR REPLACE PROCEDURE 存储过程名 --创建或者覆盖一个存储过程
IS --关键词，表面后面跟随一个PL/SQL体
BEGIN --表示PL/SQL体开始
NULL; --这里NULL不能删除，因为PL/SQL体必须有一行
END; --代表结束
--创建空的存储过程CREATE OR REPLACE PROCEDURE SP_Upadte ISBEGIN NULL; END;
二.带参数和变量的存储过程
CREATE OR REPLACE PROCEDURE SP_Update （参数1 类型1，参数2 类型2，. . . ）
AS (这里IS也可以)
变量1 类型1； --自己定义的变量 和类型
变量2 类型2；
begin
null； --这里是代码
end；
--带参数和自定义变量的存储过程CREATE OR REPLACE PROCEDURE SP_Upadte( Id in number, Name out nvarchar2 ) AS Sex char(2); --定义的变量 Class varchar2(20);BEGIN NULL; --代码块 END;
根据年月日，利用存储过程，自动更新其他字段
| DATE_ID | YEAR_MONTH | YEAR | YEAR_QUARTER | YEAR_HALF | HALF_NUMBER | HALF_NAME | QUARTER_NAME | QUARTER_NUMBER | MONTH | MONTH_NUMBER | MONTH_NAME | MONTH_DEDAK | WEEK | WEEK_NUMBER | WEEK_NAME |
| 20160702 | 201607 | 2016 | 2016Q3 | 2016H2 | 2 | H2 | Q3 | 3 | JUL | 7 | JULY | 07D1 | SAT | 7 | SATURDAY |
现在只需要输入DATE_ID,调用存储过程，后面的值可以自动更新，正确补上
废话少说，直接来代码
--创建更新date的存储过程CREATE OR REPLACE PROCEDURE SP_Update_Date --此存储过程不带参数 AS v_Half number; --定义数字，1，2代表上半年，下半年 v_Quarter number; --定义季度 1，2，3，4 v_Month VARCHAR2(10); --定义月份 1~12 v_Dedak number; --定义上中下旬，1，2，3 v_Week VARCHAR2(10); --定义星期几 CURSOR c_date IS --定义游标 SELECT DATE_ID,YEAR_MONTH FROM DIM_TIME; c_row c_date%rowtype; --new 一个实例（对比编程语言，创建好类后，就可以new 一个对象） BEGIN for c_row in c_date loop --循环游标 v_Month:=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MONTH'); --月份名称 v_Week:=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'DAY'); --星期名称 v_Dedak:=to_number(Substr(c_row.date_id,7,2));--（日期最后几号）日 if(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<7) then v_Half:=1; --上半年 else v_Half:=2; --下半年 end if; if(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<4) then v_Quarter:=1; --季度 elsif(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<7) then v_Quarter:=2; elsif(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<10) then v_Quarter:=3; else v_Quarter:=4; end if; if v_Dedak<11 then v_Dedak:=1; elsif v_Dedak<21 then v_Dedak:=2; else v_Dedak:=3; end if; --这里也可以直接调用写好的函数 -- v_Half:=getYearHalf(to_Date(c_row.DATE_ID,'YYYYMMDD')); update DIM_TIME set year_month =Substr(c_row.date_id,1,6), --根据Date_ID得到year_Month year=Substr(c_row.date_id,1,4), --得到年份 year_quarter=Substr(c_row.date_id,1,4)||'Q'||v_Quarter, --得到年和季度 year_half=Substr(c_row.date_id,1,4)||'H'||v_Half, half_number=v_Half, half_name='H'||v_Half, quarter_name= 'Q'||v_Quarter, quarter_number= v_Quarter, month=Substr(v_Month,1,3), month_number=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'), month_name=v_Month, MONTH_DEDAK=Substr(c_row.date_id,5,2)||'D'||v_Dedak, week=Substr(v_Week,1,3), week_number=to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'D')), week_name=v_Week where DIM_TIME.DATE_ID=c_row.DATE_ID; end loop; END;
几点注意：
- if else分支与java，C 语言有区别 通常我们是 ：
编程语言： if（条件1）
代码1
else if（条件2）
代码2
else
代码3
*************************华丽的分割线************************************
Oracle ：
if 条件1 then
代码1
elsif 条件2 then
代码2
elsif 条件3 then
代码3
else
代码4
end if;
嗯，看到红色的elsif没？没错，就是这样的，不习惯也没办法，慢慢习惯就好了！
再来个升级版的存储过程，直接创建好时间，还没提供参数，所以时间定了从1900-1-1至2019-12-31日，要是能提供两个日期参数，那就绝了
好吧，没有参数的先这么写吧：
--创建更新date的存储过程（利用游标）--还可以升级，输入初始日期和最后日期CREATE OR REPLACE PROCEDURE SP_Update_Date AS v_Half number; v_Quarter number; v_Month VARCHAR2(10); v_Dedak number; v_Week VARCHAR2(10); CURSOR c_date IS SELECT DATE_ID,YEAR_MONTH FROM DIM_TIME; c_row c_date%rowtype; BEGIN -----以下----- --这里是插入DATE_ID MERGE INTO DIM_TIME T1 USING (SELECT DATE'1900-1-1'+(ROWNUM-1) as Date_Name FROM dual CONNECT BY rownum <= (date'2020-1-1'-date'1900-1-1')) temp_Date on (T1.Date_Name =temp_Date.Date_Name) when matched then update set T1.Date_ID=to_char(temp_Date.Date_Name,'YYYYMMDD') when not matched then insert (DATE_ID) values(to_char(temp_Date.Date_Name,'YYYYMMDD')); ----以上---- for c_row in c_date loop --循环游标 v_Month:=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MONTH'); --月份名称 v_Week:=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'DAY'); --星期名称 v_Dedak:=to_number(Substr(c_row.date_id,7,2));--（日期最后几号）日 if(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<7) then v_Half:=1; --上半年 else v_Half:=2; --下半年 end if; if(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<4) then v_Quarter:=1; --季度 elsif(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<7) then v_Quarter:=2; elsif(to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'))<10) then v_Quarter:=3; else v_Quarter:=4; end if; if v_Dedak<11 then v_Dedak:=1; elsif v_Dedak<21 then v_Dedak:=2; else v_Dedak:=3; end if; --这里也可以直接调用写好的函数 -- v_Half:=getYearHalf(to_Date(c_row.DATE_ID,'YYYYMMDD')); update DIM_TIME set year_month =Substr(c_row.date_id,1,6), --根据Date_ID得到year_Month year=Substr(c_row.date_id,1,4), --得到年份 year_quarter=Substr(c_row.date_id,1,4)||'Q'||v_Quarter, --得到年和季度 year_half=Substr(c_row.date_id,1,4)||'H'||v_Half, half_number=v_Half, half_name='H'||v_Half, quarter_name= 'Q'||v_Quarter, quarter_number= v_Quarter, month=Substr(v_Month,1,3), month_number=to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'MM'), month_name=v_Month, MONTH_DEDAK=Substr(c_row.date_id,5,2)||'D'||v_Dedak, week=Substr(v_Week,1,3), week_number=to_number(to_char(to_Date(c_row.DATE_ID,'YYYYMMDD'),'D')), week_name=v_Week, DIM_TIME.DATE_NAME=to_date(c_row.DATE_ID,'YYYYMMDD') where DIM_TIME.DATE_ID=c_row.DATE_ID; end loop; END;
查询的时候，坑爹了，足足让我等了六分多钟。
--最简单的pl/sql语句块set serveroutput on ;--(默认是关闭的)begin dbms_output.put_line('HelloWorld'); end;--先定义变量，再赋值declarev_name varchar2(20);begin v_name:='My name'; dbms_output.put_line(v_name); end; --加异常捕获declare v_name number :=4;begin v_name := 2/v_name; dbms_output.put_line(v_name); exception when others then dbms_output.put_line('error'); end;--变量赋值和声明declare v_date nvarchar2(10) :='20160808'; v_week nvarchar2(10); begin v_week := to_char(to_date(v_date,'YYYYMMDD'),'Q'); dbms_output.put_line(v_week); end; --变量声明，使用%type属性declarev_sal number(4);v_empname emp.ename%type;v_empno emp.empno%type;begin v_empname:= 'What'; dbms_output.put_line(v_empname); end; 我们先来创建些日期，比如创建2015年1月1日到2015年12月31日这一年的日期，我们该怎么做呢？
肯定不会去一天一天的输入吧，好了，来个很洋气的查日期的方法：
SELECT to_date('20150101','YYYYMMDD')+rownum-1 AS tempdate FROM dual connect by rownum<= to_date('20160101','YYYYMMDD')-to_date('20150101','YYYYMMDD'); 这样2015年一整年的没一天都有啦，我要10年呢?我要任何一段时间都没问题，有了这些日期，利用我们oracle强大的日期函数，立马就
知道了这一天是星期几，是那个月，是哪个季度等等
下面来写个存储过程，直接一步到位，将我们要的那段日期导入我们的日期表中，
当然是创建我们的日期维度表：
--创建时间维度表CREATE TABLE DIM_DATE(DATE_ID NUMBER,--20160809DATE_NAME DATE, --2016/8/9YEAR_HALF nvarchar2(10),--2016H1YEAR_QUARTER NVARCHAR2(10),--2016Q3YEAR_MONTH NUMBER,--201608QUARTER CHAR(2), --Q1MONTH_NAME NVARCHAR2(15),--AugustMONTH_NUMBER NUMBER, --8WEEK_NAME NVARCHAR2(10),--TuesdayWEEK_NUMBER NUMBER); --2
--创建存储过程CREATE OR REPLACE PROCEDURE PRD_UPDATE(startDate number,endDate number) AS v_Date_Name date; v_year number; v_half number; v_quarter_number number; v_month_name varchar2(10); v_month_number number; v_week_name varchar2(10); v_week_number number; Idate_Id number; CURSOR c_date IS SELECT DATE_ID FROM DIM_DATE; c_row c_date%rowtype; BEGIN MERGE INTO DIM_DATE t1 USING(SELECT to_date(startDate,'YYYYMMDD')+rownum-1 AS d FROM dual connect by rownum<= to_date(endDate,'YYYYMMDD')-to_date(startDate,'YYYYMMDD')) t2 ON (t1.DATE_NAME=t2.d) when not matched then insert(DATE_ID,CREATEDATE) values(to_char(t2.d,'YYYYMMDD'),sysdate); for c_row in c_date loop Idate_Id:=c_row.DATE_ID; v_year:=substr(Idate_Id,1,4); v_Date_Name:=to_date(Idate_Id,'YYYY/MM/DD'); v_quarter_number:=to_char(v_Date_Name,'Q'); v_month_name:=to_char(v_Date_Name,'MONTH'); v_month_number:=to_char(v_Date_Name,'MM'); v_week_name:=to_char(v_Date_Name,'DAY'); v_week_number:=to_char(v_Date_Name,'D'); dbms_output.put_line(v_Date_Name); if(v_month_number>6) then v_half:=2; else v_half:=1; end if; dbms_output.put_line(v_half); UPDATE DIM_DATE SET DATE_NAME=v_Date_Name, YEAR_HALF=v_year||'H'||v_half, YEAR_QUARTER=v_year||'Q'||v_quarter_number, YEAR_MONTH=substr(Idate_Id,1,6), QUARTER = 'Q'||v_quarter_number, MONTH_NAME=v_month_name, MONTH_NUMBER=v_month_number, WEEK_NAME=v_week_name, WEEK_NUMBER=v_week_number, UPDATEDATE=SYSDATE WHERE DATE_ID = Idate_Id; END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('错误'); END; 看看最终效果是这样的：
| DATE_ID |
| 20160809 |
| 20160810 |
| DATE_ID | DATE_NAME | YEAR_HALF | YEAR_QUARTER | YEAR_MONTH | QUARTER | MONTH_NAME | MONTH_NUMBER | WEEK_NAME | WEEK_NUMBER | ||
| 20160809 | 2016/8/9 | 2016H2 | 2016Q3 | 201608 | Q3 | August | 8 | Tuesday | 3 | ||
| 20160810 | 2016/8/10 | 2016H2 | 2016Q3 | 201608 | Q3 | August | 8 | Wednesday | 4 |
1.不带参数的存储过程
--1.不带参数的存储过程CREATE OR REPLACE PROCEDURE prd_getDateASBEGIN dbms_output.put_line('Ready');END; 2.带参数的存储过程
--2.带参数的存储过程CREATE OR REPLACE PROCEDURE prd_getDate(name nvarchar2)ASBEGIN dbms_output.put_line(name);END;
3.自定义变量的存储过程
--3.自定义变量的存储过程CREATE OR REPLACE PROCEDURE prd_getDate(name nvarchar2)ASmyName nvarchar2(100);BEGIN myName:='My name is '||name; dbms_output.put_line(name); dbms_output.put_line(myName);END;
4.判断语句的存储过程
CREATE OR REPLACE PROCEDURE prd_getDate( x number)ASmyNumber number;BEGIN IF x>0 then dbms_output.put_line('大于0'); myNumber := -x; dbms_output.put_line(myNumber); end if; if x=0 then dbms_output.put_line('等于0'); myNumber := x; dbms_output.put_line(myNumber); end if; if x<0 then dbms_output.put_line('小于0'); myNumber := x; dbms_output.put_line(myNumber); end if;END; 或者
CREATE OR REPLACE PROCEDURE prd_getDate( x number)ASmyNumber number;BEGIN IF x>0 then dbms_output.put_line('大于0'); myNumber := -x; dbms_output.put_line(myNumber); elsif x=0 then dbms_output.put_line('等于0'); myNumber := x; dbms_output.put_line(myNumber); else dbms_output.put_line('小于0'); myNumber := x; dbms_output.put_line(myNumber); end if;END; 5.for循环游标
CREATE OR REPLACE PROCEDURE prd_getDateASdate_str nvarchar2(30);CURSOR c_date IS SELECT t1.week_name,t1.date_name FROM DIM_DATE t1;c_row c_date%rowtype;BEGIN FOR c_row IN c_date LOOP date_str:=c_row.week_name; dbms_output.put_line(date_str); dbms_output.put_line(to_char(c_row.date_name,'YYYY/MM/DD')); END LOOP;END;