Learning notes-static SQL and dynamic SQL

One: Static SQL

SQL statements have been written (determined at compile time) when writing the PL-SQL program

DDL and Session control statements cannot be used directly in PL/SQL

Two: Dynamic SQL

SQL statements are not yet deterministic when writing a PL * + program

Not compiled, dynamically determined at execution time

Need to dynamically determine SQL statements based on user input parameters

Resolving issues that do not support DDL statements in PL/SQL

Dynamic SQL Syntax:

EXECUTE IMMEDIATE ' DDL,DML statement '---SQL statement is in the form of a string, if there are parameters in the SQL statement, you can: use the parameter name

[into〈 variable sequence]--to accept the record value selected by the SELECT statement

[using〈 parameter sequence];--for binding input parameter variables

--The Execute immediate statement can only execute a row that is returned or does not have an SQL statement

You can use a ref dynamic cursor when you need to execute an SQL statement that returns multiple rows

Dynamically creating SQL statements that return multiple rows

Grammar:

Declaration is the same as a normal REF cursor, except that it is opened differently

OPEN REF CURSOR name for ' SQL statement string '

[using〈 parameter column];

Example----Dynamic SQL-------------------------------

DECLARE
V_ID Number: = &vid;
V_name VARCHAR2 (): = ' &vname ';
V_sex VARCHAR2 (): = ' &sex ';
Insert_sql VARCHAR2 (+): = ' INSERT into student values (: 1,:2,:3) '; ---: 1 is a bound variable and is a placeholder in dynamic SQL
BEGIN
EXECUTE IMMEDIATE insert_sql USING v_id,v_name,v_sex; ----EXECUTE immediate can be added directly after the ' string SQL statement '
END;

Example 2----------ref dynamic cursor---------

DECLARE
TYPE ref_cur is ref CURSOR;
RC Ref_cur;
Emp_row Dept%rowtype;
Select_sql VARCHAR2 (+): = ' select * FROM dept ';
BEGIN
OPEN RC for Select_sql;
LOOP
FETCH RC into Emp_row;
EXIT when Rc%notfound;
--dbms_output.put_line (rc%rowcount| | ' ' | | emp_row.empno| | ' ' | | emp_row.ename| | ' ' | | Emp_row.sal);
Dbms_output.put_line (emp_row.deptno| | Emp_row.dname);
END LOOP;
CLOSE RC;
END;

Learning notes-static SQL and dynamic SQL