Dynamic SQL and exceptions of pl/sql

dynamic sql

static state sql It can be understood that all contents of this statement have been determined before the program is compiled , There are no unknown factors , dynamic sql Cannot determine at compile time , Only when the program is running can it really be determined

• stay SQL If some parameters are not determined , Such as "select * from emp where empno=?", This statement is static SQL

dynamic sql Scope of application （ At present, users only learn here ）

• DDL Statement and with parameters DML sentence

dynamic sql Grammar format

execute immediate 'select sentence /ddl sentence '	[into  Variable name ][using  Filling data ];

• into The variable name indicates that select Statement to the corresponding variable
• using Filling data means that the data is passed to the front select Query results after statement

dynamic sql A simple example

（1）select sentence

-- The output employee number is 7369 Name and job of the employee 
declare
	-- Declare variables 
	b emp%rowtype;
begin
	-- dynamic sql sentence , Name =:q Users can understand it as placeholders , Eventually it will be using Post data filling 
	execute immediate 'select * from emp where empno=:q' into b using 7369;
	-- Output statement 
	dbms_output.put_line(b.ename||', '||b.job);
end;

One thing to note here , If the user will select Assign to a variable , Then the relevant format will change slightly , The key codes are as follows ：

b:='select sentence ';
execute immediate b into  Variable name  using  Filling data ;

（2）ddl sentence

-- Create table 
begin
execute immediate 'create table b( bid number, bname varchar2(210))';
end;

abnormal

An error in program operation is called an exception , Go to exception part

The program framework is as follows ：

declare
	 Declaration part ;
begin
	pl/sql Code block ;
exception 
	 Exception handling part ;
end;

exception You can connect more when then Statement but when it appears when others when when others then Statements must be placed at the end

System exception

（1） Predefined exceptions （ There is an error code ）

• Common predefined exceptions
  （1）ACCESS_INTO_NULL: Corresponding ORA-06530 To reference object properties , The object must be initialized first . When referencing uninitialized object properties directly , Exceptions will occur
  （2）CASE_NOT_FOUND: Corresponding ORA-06592, When CASE Of the statement WHEN Clause does not contain a required conditional branch or ELSE When clause , Will trigger
  （3）COLLECTION_IS_NULL: Corresponding ORA-06531, Give nested table variables or varrary Before variable assignment , The collection must be initialized first
  （4）CURSOR_ALREADY_OPEN:ORA-06511, When executed on an open cursor OPEN Operation will trigger
  （5）INVALID_CURSOR:ORA-01001, When the view is never opened, the cursor , Extract the data , Or it will trigger when the cursor is closed
  （6）INVALID_NUMBER:ORA-01722, When embedded SQL A statement that does not convert a character to a number triggers
  （7）LOGIN_DENIED:ORA-01017, Connect Oracle Database time , If an incorrect user name and password are provided, it will trigger
  （8）NO_DATA_FOUND:ORA-01403 perform SELECT INTO No rows returned or uninitialized... Referenced PL/SQL Table elements will trigger
  （9）NOT_LOGGED_ON:ORA-01012 No connection to database execution SQL Will trigger
  （10）PROGRAM_ERROR:ORA-06501 There is PL/SQL Internal problems , In this case, you need to reinstall the data dictionary view and PL/SQL package
  （11）ROWTYPE_MISMATCH:ORA-016504 When the assignment operation is performed , If the return type of the host variable and the cursor variable are not compatible , Will trigger
  （12）SELF_IS_NULL: ORA-30625, When using object types , If in null When an instance calls a member method , Will trigger
  （13）STORAGE_ERROR:ORA-06500 When executed PL/SQL When a block , If the memory space is exceeded or damaged , Will trigger
  （14）SUBSCRIPT_BEYOND_COUNT:ORA-06533 When using nesting or varray Element range will trigger
  （15）SUBSCRIPT_OUTSIDE_LIMIT:ORA-06532, Use nested tables or varray Element time , If the element subscript is negative , Will trigger
  （16）SYS-INVALID_ROWID:ORA-01410 When the string changes to ROWID If an invalid string is used , Will trigger
  （17）TIMEOUT_ON_RESOURCE:ORA-00051 If a timeout occurs while waiting for resources, it will trigger
  （18）TOO_MANY_ROWS:ORA-01422 When executed SELECT INTO when , If you return more than one line 、 Will trigger
  （19）VALUE_ERROR:ORA-06502, When the assignment is performed , If the variable length is not enough , Will trigger
  （20）ZERO_DIVIDE：ORA-01476 If you divide by a number 0, Will trigger

• A simple example is as follows

/*exception Some key code formats are as follows ： when  Exception names  then  Output statement ; */
begin
	dbms_output.put_line(2/0);
exception
	when ZERO_DIVIDE then
		dbms_output.put_line('0 Can't do divisor ');
end;

（2） Non predefined exception （ Nameless has an error code , You need to manually bind an exception name ）

A simple example is as follows ：

declare
	-- Declare an exception name 
	a exception;
	-- Bind the error code to the declared exception name 
	pragma exception_init(a,-00001);
begin
	insert into dept values(10,' Cleaning department ',' London ');
exception
	when a then
		dbms_output.put_line(' Violation of a unique constraint ');
end;

Custom exception

Programmers from a business perspective , Some rules and restrictions specified

• The exception name is specified by the programmer
• The key code is as follows ：

-- Entry exception 
raise  Exception names ;

• A simple example is as follows ：

declare
	-- Declare an exception 
	e exception;
	-- Declare a variable 
	n number:=& Please enter a number ;
begin
	-- If n by 0 Enter exception handling , On the contrary, output 
	if n=0 then
		raise e;
	else
		dbms_output.put_line('n The value of is ：'||n);
	end if;
exception
	-- When the exception name is e when , Output the following statement  
	when e then
		dbms_output.put_line('n Not for 0');
end;

Raises an application error

• RAISE_APPLICATION_ERROR Procedure is used to create user-defined error messages
• It can be used in the executable part and exception handling part
• The error number must be between –20000 and –20999 Between

The syntax format of the key part is as follows ：

-- Bind name and error code. Note that the error code range is -20000 and -20999
pragma exception_init( Exception names ,-20001);
-- User defined error message returned 
dbms_standard.raise_application_error(-20001,' My abnormality ');

The complete example is as follows ：

--1
declare
	-- Declare an exception name 
	e exception;
	-- Bind the exception name to the user-defined error code 
	pragma exception_init(e,-20001);
	-- Define a variable 
	a number :=& Please enter a number ;
begin
	-- Define exception reminders 
	dbms_standard.raise_application_error(-20001,' The value can not be 3');
	if a=3 then
		raise e;
	end if;
end;
--2
declare
	-- Declare an exception name 
	e exception;
	-- Bind the exception name to the user-defined error code 
	pragma exception_init(e,-20001);
	-- Define a variable 
	a number :=& Please enter a number ;
begin
	
	if a<3 then
		-- Throw an exception and the prompt message is set by the user 
		raise_application_error(-20001,' Value cannot be less than 3');
	end if;
	
exception
	when e then
		dbms_output.put_line(SQLCODE||','||SQLERRM);
end;

SQLCODE And SQLERRM

• sqlcode Indicates the error code of the exception
• sqlerrm Indicates the error message of exception

A simple example is as follows ：

DECLARE
a emp%ROWTYPE;
BEGIN
a.EMPNO:=& Please enter employee number ;
SELECT ENAME INTO a.ENAME FROM EMP WHERE EMPNO=a.EMPNO;
EXCEPTION
WHEN no_data_found THEN
	dbms_output.put_line('SQLCODE:'||SQLCODE||', SQLERRM:'||SQLERRM);
END;

notes

• Custom exception 、 The first picture under non predefined exception 、 The exception handling flow charts are from the following website https://www.cnblogs.com/thescentedpath/p/errordeal.html