Oracle主机变量锚定、游标变量

System Global varaibles.

系统全局变量，只要sqlplus不断开连接，name这个变量会一直存在，切换用户会话对其无影响。


SQL> -- 非plsql变量
SQL> 
SQL> var name varchar2(20)
SQL> 
SQL> var
variable   name
datatype   VARCHAR2(20)
SQL> 
SQL> select :name from dual;
 
:NAME
--------------------------------------------------------------------------------------------------------------------------------
 
 
SQL> select * from tab;
 
TNAME                                                                                                                            TABTYPE        CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------------- ----------
DEPARTMENTS                                                                                                                      TABLE
EMPLOYEES                                                                                                                        TABLE
 
SQL> conn scott/tiger@PDB1
Connected.
SQL> 
SQL> show user
USER is "SCOTT"
SQL> 
SQL> begin
  2  select ename into :name from emp where empno=7839;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> select :name from dual;
 
:NAME
--------------------------------------------------------------------------------------------------------------------------------
KING
 
SQL>

使用%type简化变量的声明，使用%rowtype简化record（复合变量）的声明


declare
  v_ename varchar2(20);
  v_hiredate date;
BEGIN
  select ename,hiredate into v_ename,v_hiredate from emp where empno=7788;
  dbms_output.put_line(v_ename);
end;
/
 
--锚定：
/*简化变量的声明*/
declare
  v_ename emp.ename%type;
  v_hiredate date;
BEGIN
  select ename,hiredate into v_ename,v_hiredate from emp where empno=7788;
  dbms_output.put_line(v_ename);
end;
/
 
方法一：
 
DECLARE
  /*标量*/
  v_deptno number;
  v_dname varchar2(10);
  v_loc  varchar2(13);
BEGIN
  select * into  from dept where deptno=10;
end;
/
 
 
方法二
 
DECLARE
  /*复合变量*/
  type dept_record is record
  (deptno number,
   dname varchar2(10),
   loc varchar2(13));
   r1 dept_record;
BEGIN
  select * into r1 from dept where deptno=10;
end;
/
 
方法三
/*简化记录的声明*/
DECLARE
   r1 dept%ROWTYPE;
BEGIN
  select * into r1 from dept where deptno=10;
  dbms_output.put_line(r1.deptno||' '||r1.dname);
end;
/

Cursor variables


游标变量
 
使用游标变量
 
--声明
 
声明： 初始化游标变量标示符，将游标变量标示符和一个子查询关联在一起。
 
--打开
打开： 根据子查询取数据库中表中的数据填充内存上下文
 
--获取
获取： 从游标内存上下文取值填充到record ,游标内存上下文中的值每取出一行就丢弃一行
 
--关闭
关闭： 将游标内存上下文释放
 
 
 
DECLARE
  cursor c1 is select * from dept; -- 声明
  r1 c1%ROWTYPE;
BEGIN
  open c1; --打开
  fetch c1 into r1; -- 获取
  dbms_output.put_line(r1.deptno||' '||r1.dname||' '||r1.loc);
  close c1;  -- 关闭
END;
/
 
/*游标loop循环*/
DECLARE
  cursor c1 is select * from dept; -- 声明
  r1 c1%ROWTYPE;
BEGIN
  open c1; --打开
  LOOP
    fetch c1 into r1; -- 获取
	  exit when c1%notfound;
	dbms_output.put_line(r1.deptno||' '||r1.dname||' '||r1.loc);
  end loop;
  close c1;  -- 关闭
END;
/
 
/*游标for循环*/
 
DECLARE
  cursor c1 is select * from dept;
BEGIN
  for r1 in c1 
  loop
    dbms_output.put_line(r1.deptno||' '||r1.dname||' '||r1.loc);
  end loop;
END;
/
 
/*隐式游标*/
 
 
BEGIN
  for r1 in (elect * from dept) 
  loop
    dbms_output.put_line(r1.deptno||' '||r1.dname||' '||r1.loc);
  end loop;
END;
/
 
 
 
游标属性：
游标名字 %rowcount : 从游标内存上下文获得的行的数量
游标名字 %found : 从游标内存上下文获得了行返回true
游标名字 %notfound : 从游标内存上下文取不到数据返回true
游标名字 %isopen   : 游标如果打开返回true
 
 
DECLARE
  cursor c1 is select * from dept; -- 声明
  r1 c1%ROWTYPE;
BEGIN
  open c1; --打开
  LOOP
    fetch c1 into r1; -- 获取
	  exit when c1%notfound;
	dbms_output.put_line(r1.deptno||' '||r1.dname||' '||r1.loc);
  end loop;
  close c1;  -- 关闭
END;
/

The cursor with parameter:


SQL> 
SQL> 
SQL> 
SQL> declare
  2    cursor c1 is select deptno from dept;
  3    cursor c2 (p_deptno number) is select ename,sal,deptno from emp where deptno=p_deptno order by sal desc;
  4  begin
  5    for r1 in c1 loop
  6      for r2 in c2 (r1.deptno) loop
  7        exit when c2%rowcount>2;
  8        dbms_output.put_line(chr(10)||r2.ename||' '||r2.sal||' '||r2.deptno);
  9      end loop;
 10    end loop;
 11  end;
 12  /
 
KING 5000 10
 
CLARK 2450 10
 
SCOTT 3000 20
 
FORD 3000 20
 
BLAKE 2850 30
 
ALLEN 1600 30
 
PL/SQL procedure successfully completed.
 
SQL>

exception process：

1.deal with predefine exception


[root@oracle-db-19c ~]# su - oracle
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 19 13:43:23 2022
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> alter session set container=PDB1;
 
Session altered.
 
SQL> set pagesize 200
SQL> set linesize 200
SQL> 
SQL> conn scot/tiger@PDB1
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Warning: You are no longer connected to ORACLE.
SQL> conn scott/tiger@PDB1;
Connected.
SQL> 
SQL> 
SQL> set serveroutput on
SQL> 
SQL> declare
  2    v_ename varchar2(10);
  3  begin
  4    select ename into v_ename from emp where empno=&p_empno;
  5    dbms_output.put_line(v_ename);
  6  exception
  7    when no_data_found then
  8      dbms_output.put_line('check out no this person');
  9  end;
 10  /
Enter value for p_empno: 7788
old   4:   select ename into v_ename from emp where empno=&p_empno;
new   4:   select ename into v_ename from emp where empno=7788;
SCOTT
 
PL/SQL procedure successfully completed.
 
SQL> /
Enter value for p_empno: 111111
old   4:   select ename into v_ename from emp where empno=&p_empno;
new   4:   select ename into v_ename from emp where empno=111111;
check out no this person
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> declare
  2    v_ename varchar2(10);
  3  begin
  4    select ename into v_ename from emp where deptno=10;
  5    dbms_output.put_line(v_ename);
  6  exception
  7    when no_data_found then
  8      dbms_output.put_line('check out no this person');
  9    when too_many_rows then
 10      dbms_output.put_line('put into too many variables into scalar!');
 11  end;
 12  /
put into too many variables into scalar!
 
PL/SQL procedure successfully completed.
 
SQL>


SQL> declare
  2    v_flag char(1);
  3    v_deptno number:=&p_deptno;
  4  begin
  5    select 'A' into v_flag from dept where deptno = v_deptno;
  6    dbms_output.put_line(v_deptno||' department exists');
  7  exception
  8    when no_data_found then 
  9      dbms_output.put_line(v_deptno||' department not exists');
 10  end;
 11  /
Enter value for p_deptno: 10
old   3:   v_deptno number:=&p_deptno;
new   3:   v_deptno number:=10;
10 department exists
 
PL/SQL procedure successfully completed.
 
SQL> /
Enter value for p_deptno: 1
old   3:   v_deptno number:=&p_deptno;
new   3:   v_deptno number:=1;
1 department not exists
 
PL/SQL procedure successfully completed.
 
SQL>

2.catch oracle error


SQL> declare
  2    my_err exception;
  3    pragma exception_init(my_err,-2291);
  4  begin
  5    update emp set deptno=80 where empno=7788;
  6  exception
  7    when my_err then
  8    dbms_output.put_line('parent key not found');
  9  end;
 10  /
parent key not found
 
PL/SQL procedure successfully completed.
 
SQL>

2.2 use others to catch all of the EXCEPTIONS what we not considered

DECLARE
v_flag char(1);
v_deptno number:=&p_deptno;
BEGIN
select 'A' into v_flag from dept where deptno=v_deptno;
EXCEPTION
when others THEN
dbms_output.put_line(sqlcode||' ; '||SQLERRM);
END;
/


SQL> 
SQL> 
SQL> DECLARE
  2    v_flag char(1);
  3    v_deptno number:=&p_deptno;
  4  BEGIN
  5    select 'A' into v_flag from dept where deptno=v_deptno;
  6  EXCEPTION
  7    when others THEN
  8      dbms_output.put_line(sqlcode||' ; '||SQLERRM);
  9  END;
 10  /
Enter value for p_deptno: 10
old   3:   v_deptno number:=&p_deptno;
new   3:   v_deptno number:=10;
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> /
Enter value for p_deptno: 11
old   3:   v_deptno number:=&p_deptno;
new   3:   v_deptno number:=11;
100 ; ORA-01403: no data found
 
PL/SQL procedure successfully completed.
 
SQL>

3.custom exception ora-20000 ~ ora-20999

DECLARE
my_err EXCEPTION;
PRAGMA exception_init(my_err,-20000);
BEGIN
if to_char(sysdate,'DY') IN ('SAT','SUN') THEN
    RAISE_APPLICATION_ERROR(-20000,'');
ELSE
    UPDATE EMP SET SAL=SAL+100;
END IF;
EXCEPTION
WHEN MY_ERR THEN
    dbms_output.put_line('salary can not be modified!');
END;
/


SQL> 
SQL> 
SQL> 
SQL> DECLARE
  2    my_err EXCEPTION;
  3    PRAGMA exception_init(my_err,-20000);
  4  BEGIN
  5    if to_char(sysdate,'DY') IN ('SAT','SUN') THEN
  6      RAISE_APPLICATION_ERROR(-20000,'');
  7    ELSE
  8      UPDATE EMP SET SAL=SAL+100;
  9    END IF;
 10  EXCEPTION
 11    WHEN MY_ERR THEN
 12      dbms_output.put_line('salary can not be modified!');
 13  END;
 14  /
salary can not be modified!
 
PL/SQL procedure successfully completed.
 
SQL>

4. Anonymous blocks translate into Name the block(procedure, function, package,trigger)


SQL> 
SQL> create or replace procedure add_sal
  2  is 
  3  begin
  4    update emp set sal=sal*1.1;
  5  end;
  6  /
 
Procedure created.
 
SQL> select * from emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
 
14 rows selected.
 
SQL> exec add_sal;
 
PL/SQL procedure successfully completed.
 
SQL> select * from emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        880                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1760        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1375        500         30
      7566 JONES      MANAGER         7839 02-APR-81     3272.5                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1375       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       3135                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2695                    10
      7788 SCOTT      ANALYST         7566 24-JAN-87       3300                    20
      7839 KING       PRESIDENT            17-NOV-81       5500                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1650          0         30
      7876 ADAMS      CLERK           7788 02-APR-87       1210                    20
      7900 JAMES      CLERK           7698 03-DEC-81       1045                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3300                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1430                    10
 
14 rows selected.
 
SQL> roll
Rollback complete.
SQL> select * from emp;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 24-JAN-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 02-APR-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
 
14 rows selected.
 
SQL>


SQL> 
SQL> create or replace procedure add_sal2(p_empno in number, p_sal number) is
  2  begin
  3    update emp set sal = p_sal where empno = p_empno;
  4  end;
  5  /
 
Procedure created.
 
SQL> desc add_sal
PROCEDURE add_sal
 
SQL> desc add_sal2
PROCEDURE add_sal2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_EMPNO                        NUMBER                  IN
 P_SAL                          NUMBER                  IN
 
SQL> 
SQL> exec add_sal2(7369,1500);
 
PL/SQL procedure successfully completed.
 
SQL> select * from emp where empno=7369;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80       1500                    20
 
SQL> roll
Rollback complete.
SQL> select * from emp where empno=7369;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
 
SQL>


SQL> create or replace procedure get_emp(p_empno in number,
  2                                      v_ename  out varchar2,
  3                                      v_sal   out number) is
  4  begin
  5    select ename, sal into v_ename, v_sal from emp where empno = p_empno;
  6  end;
  7  /
 
Procedure created.
 
SQL> desc get_emp;
PROCEDURE get_emp
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_EMPNO                        NUMBER                  IN
 V_ENAME                        VARCHAR2                OUT
 V_SAL                          NUMBER                  OUT
 
SQL> 
SQL> 
SQL> declare
  2    g_ename varchar2(20);
  3    g_sal number;
  4  begin
  5    get_emp(7499,g_ename,g_sal);
  6    dbms_output.put_line(g_ename||' '||g_sal);
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> set serveroutput on
SQL> /
ALLEN 1600
 
PL/SQL procedure successfully completed.
 
SQL>


 
SQL> create or replace procedure get_emp2(p_test in out number) is
  2  begin
  3    select sal into p_test from emp where empno = p_test;
  4  end;
  5  /
 
Procedure created.
 
SQL> var g_test number
SQL> exec :g_test:=7566;
 
PL/SQL procedure successfully completed.
 
SQL> exec get_emp2(:g_test);
 
PL/SQL procedure successfully completed.
 
SQL>