-
编辑 删除
[PL/SQL]Something about authid for procedures
Oracle8i Database introduced the AUTHID clause for procedures, functions and packages. When set to AUTHID DEFINER (the default), then your program runs under "definer rights." This means that any references to data objects (such as tables and views) are resolved at compile time, based on the directly granted privileges of the definer or owner of the program. Roles are ignored. If, on the other hand, you set the clause to AUTHID CURRENT_USER, then any references to data objects are reso
-
编辑 删除
[PL/SQL]10g PL/SQL学习笔记（三）
Declaring the CursorCURSOR cursor_name ISselect_statement;Open, Fetch&CloseOPEN emp_cursor;FETCH emp_cursor INTO empno, lname;CLOSE emp_cursor;Cursors and Recordsemp_record emp_cursor%ROWTYPE;……FETCH emp_cursor INTO emp_record;Cursor FOR LoopsFOR record_name IN cursor_name LOOPstatement1;statement2;. . .END LOOP;说明：无须显式地打开、fetch、关闭游标。无须定义record_name。Explicit Cursor Attributes%ISOPEN, %NOTFOUND, %FOUND, %ROWCOUNTCursor FOR Loops Using SubqueriesFOR emp_record IN (SELEC
-
编辑 删除
[PL/SQL]10g PL/SQL学习笔记（二）
SELECT Statements in PL/SQL必须使用INTO，只能选出一条记录Naming Conventions避免变量名与列名相同，如相同，优先选择列名。在where语句中会导致歧义。DECLAREhire_date employees.hire_date%TYPE;sysdate hire_date%TYPE;employee_id employees.employee_id%TYPE := 176;BEGINSELECT hire_date, sysdateINTO hire_date, sysdateFROM employeesWHERE employee_id = employee_id;END;/SQL Cursor• A cursor is a pointer to the private memory areaallocated by the Oracle server.• There are two types of cursors:– Im
-
编辑 删除
[PL/SQL]10g PL/SQL学习笔记（一）
PL/SQL block structureDeclare（可选）BeginExecutable（必须）sql、pl/sqlException（可选，嵌在executable部分）end;block types1. Anonymous2. procedure3. fuction（必须返回值）在isql*plus中输出Set serveroutput onDelimiters in String Literalsevent VARCHAR2(15):='Father''s day';You can specify any character that is not present in the string as delimiter. The slide shows how to use the q' notation to specify the delimiter. The examples use ‘!’ and ‘[‘ as delimiters. Consider the following example:ev
