Use Column type as the function parameter type : Function Parameters « Stored Procedure Function « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Stored Procedure Function » Function Parameters 
Use Column type as the function parameter type
    
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001'Scott', 'Lawson','Computer Science', 11);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002'Mar', 'Wells','History', 4);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003'Jone', 'Bliss','Computer Science', 8);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004'Man', 'Kyte','Economics', 8);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005'Pat', 'Poll','History', 4);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006'Tim', 'Viper','History', 4);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007'Barbara', 'Blues','Economics', 7);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008'David', 'Large','Music', 4);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009'Chris', 'Elegant','Nutrition', 8);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010'Rose', 'Bond','Music', 7);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011'Rita', 'Johnson','Nutrition', 8);

row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012'Sharon', 'Clear','Computer Science', 3);

row created.

SQL>
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );

Table created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('HIS', 101'History 101', 3011420000);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('HIS', 301'History 301', 300420004);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('CS', 101'Computer Science 101', 500420001);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('ECN', 203'Economics 203', 150320002);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('CS', 102'Computer Science 102', 353420003);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('MUS', 410'Music 410', 54320005);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('ECN', 101'Economics 101', 500420007);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('NUT', 307'Nutrition 307', 202420008);

row created.

SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ('MUS', 100'Music 100', 10003, NULL);

row created.

SQL>
SQL>
SQL> CREATE TABLE myStudent (
  2    student_id NUMBER(5NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3NOT NULL,
  5    grade      CHAR(1)
  6    );

Table created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000'CS', 102'A');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002'CS', 102'B');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003'CS', 102'C');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000'HIS', 101'A');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001'HIS', 101'B');

row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002'HIS', 101'B');

row created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION CountCredits (
  2    p_ID IN lecturer.ID%TYPE)
  3    RETURN NUMBER AS
  4
  5    v_TotalCredits NUMBER := 0;  -- Total number of credits
  6    v_CourseCredits NUMBER;      -- Credits for one course
  7    CURSOR c_RegisteredCourses IS
  8      SELECT department, course
  9        FROM myStudent
 10        WHERE student_id = p_ID;
 11  BEGIN
 12    FOR v_CourseRec IN c_RegisteredCourses LOOP
 13      SELECT num_credits
 14        INTO v_CourseCredits
 15        FROM session
 16        WHERE department = v_CourseRec.department AND course = v_CourseRec.course;
 17
 18      v_TotalCredits := v_TotalCredits + v_CourseCredits;
 19    END LOOP;
 20
 21    RETURN v_TotalCredits;
 22  END CountCredits;
 23  /

Function created.

SQL>
SQL> select CountCredits(10002from dual;

COUNTCREDITS(10002)
-------------------
                  8

row selected.

SQL>
SQL> drop table lecturer;

Table dropped.

SQL> drop table session;

Table dropped.

SQL>
SQL> drop table myStudent;

Table dropped.

SQL>
SQL>
--

   
    
    
  
Related examples in the same category
1.Pass value to function parameter
2.Boolean value function parameter
3.The FullName Function
4.A stored function with no parameters.
5.Varray type parameter
6.Clob type parameter
7.Positional Notation
8.Get circle area
9.Named Notation
10.Pass number value to function
11.Mixed Name and Position Notation Calls
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.