Oracle创建存储过程

SET SERVEROUTPUT  ON; --开启输出显示功能，默认是关闭的，否则执行成功，看不到输出结果
BEGIN 
 dbms_output.put_line('Hello,Welcome to PL/SQL');
 END;

输出结果：

 

CREATE OR REPLACE PROCEDURE P_TEST
IS 
BEGIN 
 dbms_output.put_line('Hello,Welcome to PL/SQL');
END;
 EXEC P_TEST;--执行存储过程

结果：

CREATE OR REPLACE PROCEDURE P_TEST
IS 
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;
View Code

 下面创建一个带参数的存储过程，将员工的姓名和薪水作为参数，去修改原来表中的数据

 --存储过程
      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 IS
BEGIN
 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;

--先定义变量，再赋值
declare
v_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属性

declare
v_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,--20160809
DATE_NAME DATE, --2016/8/9
YEAR_HALF nvarchar2(10),--2016H1
YEAR_QUARTER NVARCHAR2(10),--2016Q3
YEAR_MONTH NUMBER,--201608
QUARTER CHAR(2), --Q1
MONTH_NAME NVARCHAR2(15),--August
MONTH_NUMBER NUMBER,  --8
WEEK_NAME NVARCHAR2(10),--Tuesday
WEEK_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_getDate
AS
BEGIN
   dbms_output.put_line('Ready');
END;

2.带参数的存储过程

--2.带参数的存储过程
CREATE OR REPLACE PROCEDURE prd_getDate(name nvarchar2)
AS
BEGIN
   dbms_output.put_line(name);
END;

3.自定义变量的存储过程

--3.自定义变量的存储过程
CREATE OR REPLACE PROCEDURE prd_getDate(name nvarchar2)
AS
myName 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)
AS
myNumber 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)
AS
myNumber 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_getDate
AS
date_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;

  

  

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Jims2016/p/5505925.html