CREATE OR REPLACE PROCEDURE DynamicPLSQL (
/* Executes a PL/SQL block dynamically. The block
selects from students, and uses p_StudentID as an
input placeholder. */
p_StudentID IN students.ID%TYPE) IS v_CursorID INTEGER;
v_BlockStr VARCHAR2(500);
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
v_Dummy INTEGER;BEGIN
-- Open the cursor for processing.
v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Create the string containing the PL/SQL block.
-- In this string, the :first_name and :last_name
-- placeholders are output variables, and :ID is an
-- input variable.
v_BlockStr :=
'BEGIN
SELECT first_name, last_name
INTO :first_name, :last_name
FROM students
WHERE ID = :ID;
END;'; -- Parse the statement.
DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7); -- Bind the placeholders to the variables. Note that we
-- do this for both the input and output variables.
-- We pass the maximum length for :first_name and
-- :last_name.
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':first_name', v_FirstName, 20);
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':last_name', v_LastName, 20);
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':ID', p_StudentID); -- Execute the statement. We don't care about the return
-- value, but we do need to declare a variable for it.
v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); -- Retrieve the values for the output variables.
DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':first_name', v_FirstName);
DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':last_name', v_LastName); -- Insert them into temp_table.
INSERT INTO temp_table (num_col, char_col)
VALUES (p_StudentID, v_FirstName || ' ' || v_LastName); -- Close the cursor.
DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Commit our work.
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Close the cursor, then raise the error again.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;
END DynamicPLSQL;
/
/* Executes a PL/SQL block dynamically. The block
selects from students, and uses p_StudentID as an
input placeholder. */
p_StudentID IN students.ID%TYPE) IS v_CursorID INTEGER;
v_BlockStr VARCHAR2(500);
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
v_Dummy INTEGER;BEGIN
-- Open the cursor for processing.
v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Create the string containing the PL/SQL block.
-- In this string, the :first_name and :last_name
-- placeholders are output variables, and :ID is an
-- input variable.
v_BlockStr :=
'BEGIN
SELECT first_name, last_name
INTO :first_name, :last_name
FROM students
WHERE ID = :ID;
END;'; -- Parse the statement.
DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7); -- Bind the placeholders to the variables. Note that we
-- do this for both the input and output variables.
-- We pass the maximum length for :first_name and
-- :last_name.
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':first_name', v_FirstName, 20);
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':last_name', v_LastName, 20);
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':ID', p_StudentID); -- Execute the statement. We don't care about the return
-- value, but we do need to declare a variable for it.
v_Dummy := DBMS_SQL.EXECUTE(v_CursorID); -- Retrieve the values for the output variables.
DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':first_name', v_FirstName);
DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':last_name', v_LastName); -- Insert them into temp_table.
INSERT INTO temp_table (num_col, char_col)
VALUES (p_StudentID, v_FirstName || ' ' || v_LastName); -- Close the cursor.
DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Commit our work.
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Close the cursor, then raise the error again.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;
END DynamicPLSQL;
/
解决方案 »
- 重装oracle后的问题!高手帮帮忙!
- ORACLE9.2.7,用D:\oracle\oradata目录下的文件,如何恢复数据库?请高手指点
- ORA-00406: COMPATIBILITY 参数需要是 8.1.0.0.0 或更大
- 求助:这个sql语句怎么写?
- 我分组搜索每组的个数,但如果个数是0的话为什么就不显示出来的了
- 帮帮忙,“超出打开游标的最大数”的异常是怎么回事,怎么改?
- 建数据库前好像要建实例吗,请问实例在那里建,建了实例就可以建数据库了没吗
- 请问oracle9i中有没有象8i中的DBA Studio的部件?(刚用9i)
- 请教,使用VC和OLEDB for Oracle 调用存储过程,不能返回字符串值,如何解决?
- application server???
- 关于客户端连接Oracle服务器!急,在线等
- sql里怎么得到一个date类型的值,它等于当月的第一天
Function GetValue (tableName in varchar2, fieldName in varchar2, fid in number)
return varchar2 is
v_Value varchar2(1000) := '';
sqlstr varchar2(1000)
begin
sqlstr :='select '||fieldName||' from '||tableName||' where MI_PRINX ='|| to_char(fid);
execute immediate sqlstr into v_Value;
return v_Value;
end;
/
若MI_PRINX 是数字型:
sqlstr :='select '||fieldName||' from '||tableName||' where MI_PRINX ='||fid;