1. In the PL / SQL, the conditions in the Order By clause can use variables!
DECLARE v_orderbystr VARCHAR2 (30); v_userid VARCHAR2 (30); v_username VARCHAR2 (30); v_gender NUMBER; v_rownum NUMBER; TYPE tcur IS REF CURSOR; results tcur; BEGIN v_rownum: = 0; v_orderbystr: = 'username'; OPEN results FOR select userId, userName, gender from (select rOWNUM AS rowno, a * from (select * from home_user order by v_orderbystr) a where rownum. <10) where rowno> = 1; LOOP FETCH results INTO v_userid, v_username, v_gender; EXIT WHEN Results% Notfound; DBMS_OUTPUT.PUT_LINE (v_userid || '' || V_Username ||; v_rownum: = v_rownum 1; end loop; close result; dbms_output.put_line (v_rownum);
2, and during the stored procedure of writing dynamic SQL, it is found that when using the USING clause, it is found that the table name can be used as a placeholder! It can only be replaced by the following ways, ie direct the table name with the string, and other variables can be replaced by placeholders; ------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- --------------------------------------- Sorry, I actually discovered that the content of the ORDER BY sentence is also You need to use a string connection to implement ORDERBY's function. When you feel through placeholders, the system will treat the contents of the placeholder as a string, not the field name; so the procedure for the next Make changes ----------------------------------------------- -------------------------------------------------- ----------- v_sqlstr: = 'SELECT * FROM (SELECT ROWNUM ROWNO, T. * from' || '(Select Userid, Username' || 'from' || TableName || 'Where Themeid =: a2 ORDER BY '|| v_OrderByStr ||') t WHERE rownum <: a4 '||') WHERE rowno> =: a5 '; dbms_output.put_line (v_sqlStr); OPEN o_results FOR v_sqlStr USING p_themeId, v_endRow, v_startRow;
