Oracle Study Notes 4

// Function: query the annual salary based on the employee name
Create function sp_fun1 (sp_name varchar2) return number is yearSal number (7,2 );
Begin
-- Execution part
Select sal * 12 + nvl (comm, 0) * 12 into yearSal from emp where ename = spName;
Return yearSal;
End;
// Call the function. Define the variable first.
Var abc number;
Call sp_fun1 ('Scott ') into: abc;
// Create a package
Create package sp_package is
Procedure update_sal (name varchar2, newSal number );
Function annual_income (name varchar2) return number;
End;
// Create a package
Create or replace package body sp_package is
Procedure update_sal (name varchar2, newSal number)
Is
Begin
Update emp set sal = newSal where ename = name;
End;
Function annual_income (name varchar2)
Return number is
Begin
Select sal * 12 + nvl (comm, 0) * 12 into yearSal from emp where ename = spName;
Return yearSal;
End;
End;
// Call the process or function in the package
Exec sp_package.update_sal ('Scott, 120 );
Call

Scalar
Var_sal number (6, 2): = 5.4 -- Initial Value

Composite

Records that store multiple values: pl/SQL records,
Reference
Lob (large object)
// Calculate the Personal Income Tax of an employee.
Declare
C_tax_rate number (0.03): = 0.03; -- the tax rate is.
V_ename emp. ename % type; -- % type is used to define the type
V_sal number (7,2 );
V_tax_sal number (7,2 );
Begin
Select ename, sal into v_ename, v_sal from emp where empno = & no; -- & input on behalf of the customer
-- Calculate income tax
V_tax_sal: = v_sal * c_tax_rate;
-- Output
Dbms_output.put_line ('name: '| v_name | 'income tax:' | v_tax_sal );
End;
// Pl/SQL record type (composite type)
Declare
Type emp_record_type is record {
Name emp. ename % type,
Salary emp. sal % type,
Title emp. job % type );
Sp_record emp_record_type; -- defines a variable sp_record of the emp_record_type type.
}
Begin
Select ename, sal, job into sp_record from emp where empno = 7788;
Dbms_output.put_line ('name: '| sp_record.name | 'salary:' | sp_record.sal );
End;
// Pl/SQL table instance
Declare
-- Defines a pl/SQL table type sp_table_type, used to store emp. ename % type
-- By binary_integer indicates that the subscript is an integer.
Type sp_table is table of emp. ename % type index by binary_integer;
Sp_table sp_table_type;
Begin
Select nameinto sp_table (0) from emp where empno = 7788;
End;
// Reference variable
Cursor variable, reference type variable
-- Enter the Department number to display the names and salaries of all employees
Declare
-- Define the cursor variable type
Type sp_emp_cursor is ref cursor;
-- Define the cursor variable
Test_cursor sp_emp_cursor;
-- Define program variables
V_ename emp. ename % type;
V_sal emp. sal % type;
Begin
-- Execute
-- Combines cursor with select
Open test_cursor for select ename, sal from emp where deptno = & no;
-- Cyclically retrieve
Loop
Fetch test_cursor into v_ename, v_sal;
-- Determines whether the cursor ends.
Exit when test_cursor % notfound;
Dbms_output.put_line (''| v_ename |'' | v_sal );
End loop;
End;
Bytes ----------------------------------------------------------------------------------------
// Create a stored procedure
Create or replace precedure proc_name is
Begin
-- Execution part
Insert into mytable values ('XX', 'yy ');
End;
/-- (A slash indicates that the creation process starts)
Show error; // view compilation errors

Execute the stored procedure:
Exec proc_name para1, para2 ..;
Call proc_name para1, para2 ...;

You can write stored procedures, functions, triggers, and
Cursor suffix _ cursor
Exception e _
Variable v _
Constant c _

Block:
Definition (optional): declare
Execution part (required): begin
Exception (optional): exception

Set serveroutput on -- open input options
Begin
Dbms_output.put_line ('Hello World'); -- dbms_output is an oracle package
End
Declare
V_ename vchar2 (5 );
V_sal number (7,2 );
Begin
Select ename, sal into v_ename, v_sal from empno where empno = $ no; -- & indicates entering
Dbms_output.put_line ("username:" | v_ename | "Salary:" | e_sal); -- | represents the Connection Symbol
-- Exception Handling
Exception
When no_data_found then -- no data found
Dbms_output.put_line ("input data not exit, please input again! ");
End
Create procedure my_proc_name (my_name varchar (20), my_sal number) is -- defines the stored procedure with Parameters
Begin
Update emp set sal = my_sal where ename = my_name;
End;
/
Call my_proc_name ('Scott ', 4788); -- Update SCOTT's salary
// Call the stored procedure in java
// Load the driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
// Obtain the connection
Connection ct = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521 @ myOra1", "SCOTT", "TIGER ");
// Create collablestatement
CallableStatement ct = ct. prepareCall ("{call my_proc_name (?,?)} ");
Ct. setString (1, "SMITH ");
Ct. setString (2, 10 );
Cs.exe cute ();
Cs. close ();
Ct. close ();

Development Tools
Sqlplus
Pl/SQL developer

Advantages:

Performance

Modular

Reduce network transmission volume

Security: the internal data structure is not exposed, and the security of oracle itself

Disadvantages of pl/SQL

Poor portability