开发环境

名称	版本
操作系统	Windows 10 X64
Oracle	win64_11gR2_database
PLSQL Developer	12.0.1.1814 （64 bit）

测试表

create table TEST1
(
  id               NUMBER,
  user_name        VARCHAR2(1000),
  cost_center_name VARCHAR2(150),
  amount           NUMBER,
  cost_center_code VARCHAR2(240)
)

变量赋值（单个和多个）

变量赋值（单个和多个）

赋值为空的情况

declare
   v_user_name varchar2(300);
begin
  Select user_name 
  Into v_user_name
  From  TEST1
  Where user_name = 'luoma';
  dbms_output.put_line(v_user_name);
  Exception
      When Others Then
        dbms_output.put_line('user_name 为空');
 end;

• 如果没有 Exception，查不到数据会报错

ORA-01403: 未找到任何数据
ORA-06512: 在 line 4

动态 SQL 赋值

declare
  l_data_query_sql varchar2(1000);
  v_user_name varchar2(300);
begin
end;
 l_data_query_sql := 'select user_name from test1 where id = 1';
 execute immediate l_data_query_sql
    into v_user_name;
 dbms_output.put_line(v_user_name);
 Exception
      When Others Then
        dbms_output.put_line('user_name 为空');

定时临时表

with 
  --定义临时表 t1
  t1 as (select * from test_v_hwhao1)
select * from t1

定义公共变量

CREATE OR REPLACE PACKAGE FND_API AUTHID DEFINER AS
    --  Global constants representing TRUE and FALSE.
    G_TRUE      CONSTANT  VARCHAR2(1) := 'T';
    G_FALSE     CONSTANT  VARCHAR2(1) := 'F';
END FND_API;

使用

declare
begin
  dbms_output.put_line(FND_API.G_TRUE); --输出 T
end;

%Rowtype

Oracle-集合表行类型使用

declare
  -- 这里 test1 是上面的测试表名称
  l_test_rec test1%Rowtype;
begin
   l_test_rec.id := 5; 
   l_test_rec.user_name := 'luoma5';
   l_test_rec.cost_center_name := '成本中心5';
   l_test_rec.amount := 11;
   l_test_rec.cost_center_code := 'code5';
   insert into test1
   values l_test_rec;
   dbms_output.put_line('OK');
end;

或者类型是一个视图也可以，例如

decalre
  -- 这里 test1_v 是一个视图
  test1VRow test1_v%Rowtype;
begin
  Select *
  Into test1VRow
  From test1_v 
  Where config_name = 'abc';
end;

%Type

decalre
  -- 这里和定义 user_name  VARCHAR2(1000); 相同含义
  -- TEST1 表的 user_name 字段
  user_name TEST1.user_name%Type;
begin
  user_name := '1111';
  dbms_output.put_line(l_bank_account_name);
end;

TABLE OF

Oracle-集合表行类型使用

自定义异常

自定义异常

公共变量

CREATE OR REPLACE PACKAGE FND_API AUTHID DEFINER AS
    G_EXC_ERROR        EXCEPTION;
    G_EXC_UNEXPECTED_ERROR     EXCEPTION;
    G_RET_STS_SUCCESS       CONSTANT    VARCHAR2(1)    :=  'S';
    G_RET_STS_ERROR              CONSTANT    VARCHAR2(1)    :=  'E';
    G_RET_STS_UNEXP_ERROR      CONSTANT    VARCHAR2(1)    :=  'U';
END FND_API;

异常方法

Procedure Raise_Exception(x_Return_Status Varchar2) Is
Begin
If (x_Return_Status = Fnd_Api.g_Ret_Sts_Unexp_Error) Then
    Raise Fnd_Api.g_Exc_Unexpected_Error;
Elsif (x_Return_Status = Fnd_Api.g_Ret_Sts_Error) Then
    Raise Fnd_Api.g_Exc_Error;
End If;
End Raise_Exception;

使用方法

declare
begin
    dbms_output.put_line('1');  
    Raise_Exception(Fnd_Api.G_RET_STS_UNEXP_ERROR);
    dbms_output.put_line('2');  
    Exception
     when Fnd_Api.g_Exc_Unexpected_Error Then
      dbms_output.put_line('自定义异常');
     when others then
      dbms_output.put_line('其它异常');  
end;

pragma autonomous_transaction

正常情况

create or replace procedure p_insert_test is
    --自治事务 同一个存储过程的commit才生效
    pragma autonomous_transaction;
begin
    insert into TEST1(id,user_name,Cost_Center_Name,amount,cost_center_code) 
    values(6,'luoma6','成本中心6',11.11,'Code6');
    commit;
end p_insert_test;

调用，数据添加成功

begin
  p_insert_test;
end;
select * from TEST1

异常情况

create or replace procedure p_insert_test is
    --自治事务 同一个存储过程的commit才生效
    pragma autonomous_transaction;
begin
    insert into TEST1(id,user_name,Cost_Center_Name,amount,cost_center_code) 
    values(7,'luoma7','成本中心7',11.14,'Code7');
    commit;
end p_insert_test;
create or replace procedure p_insert is 
    v_num number;
begin
    --调用
    p_insert_test;
    -- pragma autonomous_transaction; 不会生效
    --commit;
    v_num:=10/0;
    exception
      when others then
        dbms_output.put_line(sqlerrm);
        -- pragma autonomous_transaction; 不会回滚
        rollback;
end p_insert;

调用

begin
  p_insert;
end;

报错，数据添加失败

ORA-06550: 第 2 行, 第 3 列: 
PLS-00905: 对象 LUOMA.P_INSERT 无效
ORA-06550: 第 2 行, 第 3 列: 
PL/SQL: Statement ignored

指定事务回滚

declare
     v_num number;
    p_savepoint_name Constant Varchar2(30) := 'process_request01';
begin 
    insert into TEST1(id,user_name,Cost_Center_Name,amount,cost_center_code) 
    values(7,'luoma7','成本中心7',7,'Code7');     
    commit;
    dbms_output.put_line('luoma7');
    --回滚标志
    dbms_transaction.savepoint(p_savepoint_name);     
    insert into TEST1(id,user_name,Cost_Center_Name,amount,cost_center_code) 
    values(8,'luoma8','成本中心8',8,'Code8');
    dbms_output.put_line('luoma8');
    v_num:=10/0;
    --提交
    COMMIT WORK;
    exception
      when others then
           --回滚
           dbms_transaction.rollback_savepoint(p_savepoint_name);
           dbms_output.put_line('回滚');
end;

输出

luoma7
luoma8
回滚

执行成功

insert into TEST1(id,user_name,Cost_Center_Name,amount,cost_center_code) 
values(7,'luoma7','成本中心7',7,'Code7');

执行失败

insert into TEST1(id,user_name,Cost_Center_Name,amount,cost_center_code) 
values(8,'luoma8','成本中心8',8,'Code8');

去除

--v_num:=10/0;

两条都执行成功

动态执行 SQL

CREATE OR REPLACE PROCEDURE PRO_Delete_table(tableName IN VARCHAR2, condition IN VARCHAR2,msg out VARCHAR2) 
IS
  SQL_excute varchar2(4000);
BEGIN
   IF LENGTH(condition)=0 THEN
    SQL_excute := 'DELETE FROM '|| tableName;
   ELSE
    SQL_excute := 'DELETE FROM '|| tableName || ' WHERE 1=1 AND ' || condition;
   END IF;
   EXECUTE IMMEDIATE SQL_excute;
   msg := '执行成功';
   DBMS_OUTPUT.PUT_LINE('执行成功,'||SQL_excute);
   --添加一个异常处理当没有数据的时候赋一个默认值
   exception  
    when others then
      dbms_output.put_line('执行报错,'||SQL_excute);
      msg:='执行报错';
END PRO_Delete_table;

调用

DECLARE
  tableName VARCHAR2(100) := 'TPS_TAX_EXCEL_PROVISION';
  condition      VARCHAR2(2000) := 'period between ''2022-01'' and ''2022-12'' and BALANCING_SEG in (''101'')';
  msg varchar2(1000);
BEGIN
     PRO_Delete_table(
      tableName        => tableName,       
      condition       => condition,
      msg => msg);    
     dbms_output.put_line(msg);        
END;

输出

执行成功,DELETE FROM TPS_TAX_EXCEL_PROVISION WHERE 1=1 AND period between '2022-01' and '2022-12' and BALANCING_SEG in ('101')
执行成功

带参数的游标

Oracle-包的创建和使用（游标的简单使用，临时表，动态 sql）

declare
   CURSOR csr_api(p_id NUMBER) IS
       select * from test1 
       where id > p_id;
begin 
   FOR rec_c IN csr_api(3) 
   LOOP
       dbms_output.put_line(rec_c.id  || '-' || rec_c.user_name);
   END LOOP;
end;

输出

7-luoma7
5-luoma5
4-luoma4

游标是使用时才会查询并赋值的

• 表 test_v_1

ID	NO	NAME	PARENTNO
1	0	NAME-1	2
2	0	NAME-0	2
3	4	luoma_4	PNO-004
4	4	luoma_5	PNO-005

• 表 test_v_2

ID	NO	NAME	PARENTNO
			-

declare 
   -- 这里只是定义了游标，并没有执行并返回结果赋值
   CURSOR data_cur IS
     select * from test_v_1 t1 where exists (select 1 from test_v_2 t2 where t1.ID = t2.ID);
begin
   delete test_v_2;
   insert Into test_v_2(ID,NO,NAME,PARENTNO)
   select ID,NO,NAME,PARENTNO from test_v_1 where ID in('1','2','3','4');
   commit;
   -- 这里使用了游标，才去执行游标定义查询结果并赋值
   FOR item_cur IN data_cur LOOP
       dbms_output.put_line('ID:' || item_cur.ID ||  ',NAME:' || item_cur.NAME);
   END Loop;
end;

• 执行结果

ID:1,NAME:NAME-1
ID:2,NAME:NAME-0
ID:3,NAME:luoma_4
ID:4,NAME:luoma_5

For Update 和 For Update Nowait

For Update Nowait

对查询到的结果进行加锁，如果其它线程操作结果集中的数据，For Update Nowait 不会进行资源等待，会立即报错。

● 开启第一个 SQL Windows，执行下面的 SQL，不提交

select t.ID from cux.cux_ce_config t
where config_name = 'task-error-mail-title'
for update of t.id nowait;

返回

ID
18

● 开启第二个 SQL Windows，执行下面的 SQL，不提交

select t.ID from cux.cux_ce_config t
where config_name = 'task-error-mail-title'
for update of t.id nowait;

报错

ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

● 提交第一个 SQL Windows
● 再次执行第二个 SQL Windows 中的 SQL，返回结果

ID
18

For Update

对查询到的结果进行加锁，如果其它线程操作结果集中的数据，For Update 会进行资源等待，不会立即报错。

● 开启第一个 SQL Windows，执行下面的 SQL，不提交

select t.ID from cux.cux_ce_config t
where config_name = 'task-error-mail-title'
for update;

返回

ID
18

● 开启第二个 SQL Windows，执行下面的 SQL，不提交

select t.ID from cux.cux_ce_config t
where config_name = 'task-error-mail-title'

不报错，一直在转圈等待中

● 提交第一个 SQL Windows
● 返回第二个 SQL Windows，自动返回结果

ID
18