oracle 动态sql 逗号,oracle 动态SQL语句

查询单行结果

CREATE OR REPLACE FUNCTION get_num_of_employees

(p_loc VARCHAR2, p_job VARCHAR2)

RETURN NUMBER

IS

v_query_str VARCHAR2(1000);

v_num_of_employees NUMBER;

BEGIN

-- Use concatenation to form the table name in the SELECT statement

v_query_str := 'SELECT COUNT(*) FROM emp_'

|| p_loc

|| ' WHERE job = :1';

-- Execute the query and put the result row in a variable

EXECUTE IMMEDIATE v_query_str

INTO v_num_of_employees

USING p_job;

RETURN v_num_of_employees;

END;

查询多行结果 多行时用游标

CREATE OR REPLACE PROCEDURE list_employees

(p_loc VARCHAR2, p_job VARCHAR2)

IS

TYPE cur_typ IS REF CURSOR;

-- Define a cursor variable

v_emp_cursor cur_typ;

v_query_str VARCHAR2(1000);

v_emp_name VARCHAR2(20);

v_emp_num NUMBER;

BEGIN

-- Use concatenation to form the SELECT statement

v_query_str := 'SELECT ename, empno FROM emp_'

|| p_loc

|| ' WHERE job = :g_job_title';

-- Open a cursor variable for the query

OPEN v_emp_cursor FOR v_query_str USING p_job;

-- Loop through each row to find employees who perform the specified job

LOOP

-- Fetch the employee name and ID into variables

FETCH v_emp_cursor INTO v_emp_name, v_emp_num;

EXIT WHEN v_emp_cursor%NOTFOUND;

-- Process row here

END LOOP;

CLOSE v_emp_cursor;

END;

获取一个记录

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

v_emp_cursor EmpCurTyp;

emp_record emp%ROWTYPE;

v_stmt_str VARCHAR2(200);

v_e_job emp.job%TYPE;

BEGIN

v_stmt_str := 'SELECT * FROM emp WHERE job = :1';

-- in a multi-row query

OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

LOOP

FETCH v_emp_cursor INTO emp_record;

EXIT WHEN v_emp_cursor%NOTFOUND;

END LOOP;

CLOSE v_emp_cursor;

-- in a single-row query

EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';

END;

DBMS_SQL Query Operation Native Dynamic SQL Query Operation

DECLARE

v_stmt_str VARCHAR2(200);

v_cur_hdl INT;

v_rows_processed INT;

v_name VARCHAR2(10);

v_salary INT;

BEGIN

v_cur_hdl := DBMS_SQL.OPEN_CURSOR; -- open cursor

v_stmt_str := 'SELECT ename, sal

FROM emp

WHERE job = :g_jobname';

DBMS_SQL.PARSE(v_cur_hdl,v_stmt_str,DBMS_SQL.NATIVE);

-- Supply binds (bind by name)

DBMS_SQL.BIND_VARIABLE(v_cur_hdl, 'g_jobname',

'SALESMAN');

-- Describe defines

DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 1, v_name, 200);

DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 2, v_salary);

-- Execute

v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);

LOOP

-- Fetch a row

IF DBMS_SQL.FETCH_ROWS(v_cur_hdl) > 0 THEN

-- Fetch columns from the row

DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 1, v_name);

DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 2, v_salary);

-- Process

ELSE

EXIT;

END IF;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(v_cur_hdl); -- close cursor

END;